Oracle-L: RE: Daramtically improve BCHR with a single statement

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Mon, 13 Jan 2003 09:16:15 -0800
Message-ID: <F001.0052DD49.20030113091615@fatcity.com>

"How can you tell that you need more space in the buffer pool?"

...You can notice when this is true by observing summarized extended SQL trace (event 10046) data. If the LIO count for a session is small, but the PIO count is large, then you have the problem.

So, sure... Checking a session's BCHR is okay. Just don't try to maximize the BCHR before trying to minimize whatever it is that's consuming the majority of the session's response time.

I have learned that paying much attention to a *system*'s BCHR (or any other system-wide statistic for that matter) is a step that is prone to causing bad decisions.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Steve Adams's Miracle Master Class, Jan 13-15 Copenhagen - 2003 Hotsos Symposium, Feb 9-12 Dallas

-----Original Message-----
Adar
Sent: Monday, January 13, 2003 10:09 AM
To: Multiple recipients of list ORACLE-L

Hello All

I saw that piece of code a few times and still think that even if you can
bump up the BCHR it has it's place. How can you tell that you need more space in the buffer pool? Bad BCHR is an indication that you need to check
this. It is also an indication that you do a lot of FTS or missing an index
and you SQL reads too many blocks.

Use the BCHR as an indication. When it goes down it is time to check.

Yechiel Adar
Mehish
----- Original Message -----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Monday, January 13, 2003 5:08 AM

> To add an example of what Anjo, Morgens and everyone else is talking
about,
> here is a perfect illustration of why focusing on BCHR is like
> concentrating intensely on how fast your tyres rotate in a Tour de
France,
> instead of looking of where you are going (probably a lot more
useful).
> Another example:
> If I raced (100M sprint) against Maurice Green, and he went off in the
> wrong direction, despite the fact that he is so much faster than me
(duh
!)
> , I could lightly jog (as if I have anything else to offer) the 100M
in
the
> right direction and beat him. Well, focusing on BCHR alone is like
going
at
> full tilt with no direction.
> Also, I have realized that cars have been around for > 100 years now,
so
> why exactly would I want to sprint again ? :-)

> Check out this example:

>
> run any number of scripts to look at BCHR. Then run the following
anonymous
> PL/SQL block:

> declare
> jackass number;
> begin
> for jackass in 1..10000000 loop
> execute immediate 'select count (*) from solvit.solvit_lic ' ; --
> replace this table with any single row table you like.
> end loop;
> end;
> /

>
> Check your BCHR again. Wow, amazing ! How much better your BCHR looks
now.
> This must be magic. If you would like to purchase other such tools,
please
> feel free to drop me a line, I could also sell you a large iceberg,
which
> would end your personal water restriction problems.
>
> Another advantage to the above code is that it eliminates idle
capacity
> from my CPU's (I paid for the thing, it should be put to work, right ?
)
as
> my laptop has been at 100% CPU utilization for the last 8 minutes as I
let
> this piece of crap run before I killed it (Oracle 9 on XP with 512 MB
RAM
> [SGA 120 MB], with a bunch of other starved stuff running
concurrently).
>
> Reduction of logical I/O : Now THERE is the holy grail worth pursuing
! I
> am sure we could have a VERY interesting discussion on that one !

> Feel free to use the above example to prove for once and for all that
> concetration on tuning BCHR alone is a fruitless exercise.

> Regards :

> Ferenc Mantfeld

> -----Original Message-----
> From: Rachel Carmichael [SMTP:wisernet100_at_yahoo.com]
> Sent: Monday, January 13, 2003 11:24 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: BCHR Tuning

> and those people "sell a tuning tool".... hm, I hadn't noticed any
> selling going on here. Or perhaps it's been subliminal?

>
>

> --- Jared Still <jkstill_at_cybcon.com> wrote:
> > On Friday 10 January 2003 14:48, Mogens Norgaard wrote:
> > > Obviously, we don't know what we're talking about. I can see
> > there's a
> > > presentation by Rich Niemich at IOUG-A where he'll address all
> > those
> > > idiots who are saying you should ignore the Cash Hit Ratio (and
who
> > are
> > > all just after making big money on their products - I loved that
> > one).
> > > > Or modify the set up of these tools to take action when BCHR
> > falls......
> > > >
> >
> > Here's the session info:
> >
> > Date: Mon, Apr 28, 2003 @ 11:45 AM - 12:15 PM
> > Venue: Southern Hemisphere 2, Walt Disney World
> > Dolphin, Lake Buena Vista, FL
> >
> > Abstract: Lately, there has been a big push to ignore your
> > hit ratio with claims that it is meaningless. This shallow
> > minded view (usually by people who sell a tuning tool) ignores
> > why people look at hit ratios and what they are looking for.
> > This quick tip talk will show you what to look for and why.
> > You will definitely know when, where & why to look at your
> > hit ratio in the future.
> >
> > Show you why your hit ratio matters. How to analyze the
> > hit ratio. Fallacies by those who want to sell you products
> > and tools instead.
> >
> >
> > Shallow Minded ?!
> >
> > Jared
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Jared Still
> > INET: jkstill_at_cybcon.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting
services
> >

> > 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!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Rachel Carmichael
> INET: wisernet100_at_yahoo.com

>


> Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

> San Diego, California        -- Mailing list and web hosting services

> ---------------------------------------------------------------------

> 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.net
> --
> Author: mantfield
> INET: mantfield_at_CONNEXUS.NET.AU

>


> Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

> San Diego, California        -- Mailing list and web hosting services

> ---------------------------------------------------------------------

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Yechiel Adar
  INET: adar76_at_inter.net.il

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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.net
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Mon Jan 13 2003 - 11:16:15 CST