Re: see higher CPU usage after increase SGA
- From: "zhu chao" <chao_ping@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 Jun 2004 08:13:42 +0800
Thanks , Jonathan.
2 Years ago, you also explained the same concept to me, But I did not
understand it quite clearly. Now I do understand it.
Your explanation is quite clear and reasonable.
That poor SQL I said was because of very poor index clustering factor. To
tune the most frequently used SQL that access that table ( by primary key
user_id and msg_id, a user_id can have many msg_id), I created that table as
IOT. The cost of this SQL dropped greatly. But later a new SQL was released
which access the table via the date column. As the table was physically
ordered by user_id, the clustering factor was very poor. So it used a lot of
system buffer_gets.
Same Buffer_gets can have significant performance difference, like CPU usage
or response time. Now I get it.
Thanks .
Zhu Chao.
----- Original Message -----
From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, June 09, 2004 11:17 PM
Subject: Re: see higher CPU usage after increase SGA
>
> One note in line
>
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/seminar.html
> Optimising Oracle Seminar - schedule updated May 1st
>
>
> ----- Original Message -----
> From: "zhu chao" <chao_ping@xxxxxxxxxxx>
>
> : There was a HUGE SQL(which used 30% of total system buffer_gets
> : according to statspack report). I changed the SQL, and later it used
less
> : than 0.5% of total system buffer_gets(it just dissappear from statspack
> : report), but system CPU usage just drop by less than 5% from statspack
> : report(compare the CPU used by this session before/after change)!.
> :
>
> Bear in mind that not all visits to buffered blocks are
> performed as 'buffer gets' - some are performed as
> 'buffer is pinned count'. Also, if you visit a block to
> collect one row through an index, you may do a few
> comparisons to acquire and confirm that row. If you
> visit the block through a table scan, you have to
> check every row in the block to identify the one row -
> and the comparisons may use a lot of CPU.
>
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
