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).
Received on Tue Jun 05 2001 - 07:26:14 CDT
