| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: DB Block Buffers - Too Much ???
Gaja,
Great point. I feel like Cache hit ratios really misused. All the Oracle books provide the scripts and give a basic rule of thumb such as keep it at 95%+. Seems like this is something easy for the DBA to get their hands around.
I suggest DBA's look into Wait-Base performance tuning by checking out Craig Shallahamer's Paper "Direct Contention Identification Using Oracle's Session Wait Virtual Views" at http://www.orapub.com
............................................................................ .................................
Database Administrator office: 336 878-7268 LifeStyle Furnishings International fax : 336 878-7038 ............................................................................ .................................
> -----Original Message-----
> From: Gaja Krishna Vaidyanatha [SMTP:gajav_at_yahoo.com]
> Sent: Wednesday, June 21, 2000 3:57 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: DB Block Buffers - Too Much ???
>
> Deepak,
>
> At the outset, it seems like you may have measured your cache
> hit ratios too soon (before the cache got a chance to ramp up).
> Further, I want to clarify something regarding your quote from
> Rich Niemiec's book.
>
> You quoted him saying that "too much db_block_buffers is not
> good too, as it may lead to swapping". While that may be a true
> statement, I am hoping that you did not infer "not good" to be a
> reduction in your cache hit ratios. When you overallocate more
> memory that what you can afford, the cache hit ratios do not get
> negatively affected. In fact you might even see a very high
> ratio, but that in itself does not mean anything.
>
> If overallocation is done, and if a significant portion of your
> SGA is getting paged/swapped out, then you will experience a
> system-wide degradation in performance. Goes to prove that
> cache hit ratios by themselves cannot validate that Oracle is
> running and functioning optimally. They are just one of the
> indicators for performance. You really have to look at the
> "amount of work done" or "throughput" on the system and measure
> the success of your tuning efforts.
>
> I have been involved with Oracle sites, who have had 90+ % cache
> hit ratios across the board, but whose systems were just
> crawling and wilting under the pressure of memory overallocation
> and horrendous SQL. When the bad SQL was fixed, the cache hit
> ratios dropped to 70%, but the amount of work done was
> significantly higher.
>
> To quote an example, when the db buffer cache hit ratio was 94%,
> one of their reports which was using a "correlated sub-query"
> was running for 45 minutes running away with 1 CPU. When the
> query was re-written, the db buffer cache hit ratio dropped to
> 70%, but the report ran in 45 seconds using 65% of 1 CPU.
>
> Moral of the story: Cache hit ratios by themselves do not any
> way, shape or form prove Oracle's or the system's good health.
> The ratios should be combined with the O-S level statistics to
> arrive at any rational conclusion.
>
> Best Regards,
>
> Gaja.
>
> --- Deepak Sharma <sharmakdeep_at_yahoo.com> wrote:
> > Recently on one of our test systems the Buffer Cache
> > Hit Ratio was showing 80%. I increased
> > db_block_buffers from 6000 to 16000, and the after
> > that the hit ratio has dropped to 55%. It seemed
> > strange to me that increasing db_block_buffers should
> > actually decrease performance, until I read Oracle
> > tuning tips from Richard Niemiec, where he mentions
> > too much db_block_buffers is not good too, as it may
> > lead to swapping. My question is how do you determine
> > whether the buffers are too low or too high ?
> >
> > SQL> select state, count(*) from x$bh group by state;
> >
> > STATE COUNT(*)
> > ---------- ----------
> > 1 13047
> > 3 2953
> >
> > Does state = '1' mean this memory is not being used
> > and if so, should I decrease the buffers ?
> >
> > -- Deepak
> >
> > =====
> > Oracle DBA,
> > Minneapolis, MN
> > USA
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Send instant messages with Yahoo! Messenger.
> > http://im.yahoo.com/
> > --
> > Author: Deepak Sharma
> > INET: sharmakdeep_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).
>
>
> =====
> Gaja Krishna Vaidyanatha | gajav_at_yahoo.com
> Brio Technology | (972)-304-1170
>
> "Opinions and views expressed are my own and not of Brio Technology"
>
> __________________________________________________
> Do You Yahoo!?
> Send instant messages with Yahoo! Messenger.
> http://im.yahoo.com/
> --
> Author: Gaja Krishna Vaidyanatha
> INET: gajav_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
Received on Thu Jun 22 2000 - 07:25:01 CDT
 |
 |