Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> re:see higher CPu usage after increase SGA.

re:see higher CPu usage after increase SGA.

From: zhu chao <>
Date: Thu, 10 Jun 2004 14:57:17 +0800
Message-ID: <01d401c44eb8$2c012cc0$>

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.

(seems I did not receive this email, so re-post).

> One note in line
> Regards
> Jonathan Lewis
> The Co-operative Oracle Users' FAQ
> Optimising Oracle Seminar - schedule updated May 1st
> ----- Original Message -----
> From: "zhu chao" <>
> : 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
> : 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:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Thu Jun 10 2004 - 01:54:18 CDT

Original text of this message