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 <raj.gopalan_at_netdecisions.co.uk>
> 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: raj.gopalan_at_netdecisions.co.uk
> >
> > 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: ListGuru_at_fatcity.com (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: cspence_at_FuelSpot.com
> >
> > 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: ListGuru_at_fatcity.com (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: raj.gopalan_at_netdecisions.co.uk
> >
> > 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: ListGuru_at_fatcity.com (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).
>
> __________________________________________________
> Do You Yahoo!?
> Get personalized email addresses from Yahoo! Mail - only $35
> a year! http://personal.mail.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: A. Bardeen
> INET: abardeen1_at_yahoo.com
>
> 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: ListGuru_at_fatcity.com (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).

--
Venlig hilsen

Mogens Nørgaard

Technical Director
Miracle A/S, Denmark
Web: http://MiracleAS.dk
Mobile: +45 2527 7100




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mogens =?iso-8859-1?Q?N=F8rgaard?=
  INET: mno_at_MiracleAS.dk

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: ListGuru_at_fatcity.com (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).