Re: Shared Pool info - V$sqlarea.
He also gave the presentation at the IOUG-A in Orlando recently, so it should be available from their CD's/archives or whatever. It's a neat presentation. Way too many people and monitoring products want you do keep increasing db_block_buffers until your BCHR is about 99.9% or so. It's pure nonsense, of course. Once I worked on a large SAP site, where the SAP Early Watch kept saying the problems in the system were due to the BCHR being too low. That went on for a few months while performance became progressivly worse. When the customer passed the 1G mark (for the buffer cache alone) they called me/Premium Services and the wait interface showed all the wait time to be with the free buffer waits event, meaning the DBWR couldn't keep up (no async IO, therefor making the buffer cache bigger made the problem bigger, etc.). We set up multiple database writers to simulate async IO (long story) and it all worked nicely... This is not to say anything bad about Early Watch, but to say something bad about the ratio-based/checklist tuning method :-))). A. Bardeen wrote: Raj, One statement caught my eye: The buffer cache hit ratio is 99% My bet is you've got some very inefficent SQL. As is typically the case, application tuning has the biggest effect on performance. I'd suggest checking out Cary Millsap's excellent paper Why 99% Database Buffer Cache Hit Ratio is NOT Ok (available with free registration): http://www.hotsos.com/catalog/mtime.html HTH, -- Anita --- Raj Gopalan [EMAIL PROTECTED] wrote: Thanks Chris. The problem I am facing is 100% CPU usage and memory paging out at times. I tought the starting point is v$sqlarea. Purchasing of Precise SQL or SQL Vision can not happen here immediately. The RAM size is 512MB and SGA is 210MB. The buffer cache hit ratio is 99%. I was just wondering is there any way to find out the cause of this problem? TIA Cheers Raj -Original Message- Sent: 01 June 2001 19:51 To: Multiple recipients of list ORACLE-L Oracle has no guarentee how long statistics and plans will be available for statements, depending on the activity of the database they may be there for 2 seconds they may be there for 2 weeks. Products like Precise SQL and SQL Vision Lab help in that manor where they capture transactions and activity continuously in a non-intrusive manor. This is the only guarenteed way to get 99.999% of the transactions. Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot -Original Message- Sent: Friday, June 01, 2001 12:16 PM To: Multiple recipients of list ORACLE-L DBAs The statistics in v$sqlarea is getting flushed very often. In the morning I found the a query which has more than 10,000 disk reads as the top one. But now the top most query with disk reads has not more than 100 disk reads. I have not bouncd the DB or flushed the shared pool. The overall library cache hit ratio is 97%.where us SQLAREA hit ratio is 60%. Any Idea what could be reason? TIA, Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raj Gopalan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raj Gopalan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail
RE: Shared Pool info - V$sqlarea.
Thanks Chris. The problem I am facing is 100% CPU usage and memory paging out at times. I tought the starting point is v$sqlarea. Purchasing of Precise SQL or SQL Vision can not happen here immediately. The RAM size is 512MB and SGA is 210MB. The buffer cache hit ratio is 99%. I was just wondering is there any way to find out the cause of this problem? TIA Cheers Raj -Original Message- Sent: 01 June 2001 19:51 To: Multiple recipients of list ORACLE-L Oracle has no guarentee how long statistics and plans will be available for statements, depending on the activity of the database they may be there for 2 seconds they may be there for 2 weeks. Products like Precise SQL and SQL Vision Lab help in that manor where they capture transactions and activity continuously in a non-intrusive manor. This is the only guarenteed way to get 99.999% of the transactions. Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot -Original Message- Sent: Friday, June 01, 2001 12:16 PM To: Multiple recipients of list ORACLE-L DBAs The statistics in v$sqlarea is getting flushed very often. In the morning I found the a query which has more than 10,000 disk reads as the top one. But now the top most query with disk reads has not more than 100 disk reads. I have not bouncd the DB or flushed the shared pool. The overall library cache hit ratio is 97%.where us SQLAREA hit ratio is 60%. Any Idea what could be reason? TIA, Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raj Gopalan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raj Gopalan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Shared Pool info - V$sqlarea.
Did you say 99% hit ratio? Contrary to a once popular opinion, that is not really a good thing. It often means there's a cartesian product in a join, caused by not fully specifying the join key. At least that's the reason I've usually seen for it. Keeps disk access down though. :) Jared On Tuesday 05 June 2001 05:25, Raj Gopalan wrote: Thanks Chris. The problem I am facing is 100% CPU usage and memory paging out at times. I tought the starting point is v$sqlarea. Purchasing of Precise SQL or SQL Vision can not happen here immediately. The RAM size is 512MB and SGA is 210MB. The buffer cache hit ratio is 99%. I was just wondering is there any way to find out the cause of this problem? TIA Cheers Raj -Original Message- Sent: 01 June 2001 19:51 To: Multiple recipients of list ORACLE-L Oracle has no guarentee how long statistics and plans will be available for statements, depending on the activity of the database they may be there for 2 seconds they may be there for 2 weeks. Products like Precise SQL and SQL Vision Lab help in that manor where they capture transactions and activity continuously in a non-intrusive manor. This is the only guarenteed way to get 99.999% of the transactions. Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot -Original Message- Sent: Friday, June 01, 2001 12:16 PM To: Multiple recipients of list ORACLE-L DBAs The statistics in v$sqlarea is getting flushed very often. In the morning I found the a query which has more than 10,000 disk reads as the top one. But now the top most query with disk reads has not more than 100 disk reads. I have not bouncd the DB or flushed the shared pool. The overall library cache hit ratio is 97%.where us SQLAREA hit ratio is 60%. Any Idea what could be reason? TIA, Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Shared Pool info - V$sqlarea.
Particularly, this can be caused by a huge amount of logical IO (proportionately dwarfing the amount of disk io and resulting in a falsely 'positive' hit ratio). Take a look at your sqlarea again and look at buffer_gets/executions - that may kick up some queries doing more logical io than you want. George On Tuesday, June 5, 2001, at 10:40 PM, Jared Still wrote: Did you say 99% hit ratio? Contrary to a once popular opinion, that is not really a good thing. It often means there's a cartesian product in a join, caused by not fully specifying the join key. At least that's the reason I've usually seen for it. Keeps disk access down though. :) Jared On Tuesday 05 June 2001 05:25, Raj Gopalan wrote: Thanks Chris. The problem I am facing is 100% CPU usage and memory paging out at times. I tought the starting point is v$sqlarea. Purchasing of Precise SQL or SQL Vision can not happen here immediately. The RAM size is 512MB and SGA is 210MB. The buffer cache hit ratio is 99%. I was just wondering is there any way to find out the cause of this problem? TIA Cheers Raj -Original Message- Sent: 01 June 2001 19:51 To: Multiple recipients of list ORACLE-L Oracle has no guarentee how long statistics and plans will be available for statements, depending on the activity of the database they may be there for 2 seconds they may be there for 2 weeks. Products like Precise SQL and SQL Vision Lab help in that manor where they capture transactions and activity continuously in a non-intrusive manor. This is the only guarenteed way to get 99.999% of the transactions. Walking on water and developing software from a specification are easy if both are frozen. Christopher R. Spence Oracle DBA Fuelspot -Original Message- Sent: Friday, June 01, 2001 12:16 PM To: Multiple recipients of list ORACLE-L DBAs The statistics in v$sqlarea is getting flushed very often. In the morning I found the a query which has more than 10,000 disk reads as the top one. But now the top most query with disk reads has not more than 100 disk reads. I have not bouncd the DB or flushed the shared pool. The overall library cache hit ratio is 97%.where us SQLAREA hit ratio is 60%. Any Idea what could be reason? TIA, Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: George Schlossnagle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Shared Pool info - V$sqlarea.
DBAs The statistics in v$sqlarea is getting flushed very often. In the morning I found the a query which has more than 10,000 disk reads as the top one. But now the top most query with disk reads has not more than 100 disk reads. I have not bouncd the DB or flushed the shared pool. The overall library cache hit ratio is 97%.where us SQLAREA hit ratio is 60%. Any Idea what could be reason? TIA, Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raj Gopalan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
