Return-Path: Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g78NV0L25715 for ; Thu, 8 Aug 2002 19:31:00 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id QAA64515; Thu, 8 Aug 2002 16:21:51 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 004B0121; Thu, 08 Aug 2002 15:48:24 -0800 Message-ID: Date: Thu, 08 Aug 2002 15:48:24 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "MacGregor, Ian A." Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "MacGregor, Ian A." Subject: RE: missed Anjo's webcast.. Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-type: multipart/alternative; boundary="Boundary_(ID_OrdVqewf38fzENfeYi5NLQ)" --Boundary_(ID_OrdVqewf38fzENfeYi5NLQ) Content-type: text/plain; charset=iso-8859-1 Content-transfer-encoding: 7BIT Sorry hit the return too quickly, Resuming and select a.aid, a.chnum, f from rollup a where a.effdt=(select max(b.effdt) from rollup b where a.chnum=b.chnum) Admittedly they are differ by the aggregate function (sum). The one with the sum was killed by the user, but statspack captured the following EXECUTIONS DISK_READS ROWS_PROCESSED SORTS PARSE_CALLS BUFFER_GETS -------------------- ------------------------- ---------------------------------- ---------- ------------------------ ----------------------- 1 2922 0 -7833 1 9262161 for the first query 1 1498 60 130 1 11450 for the second. It is obvious that the first query is more expensive than the second. However the first query has a better BHR than the second. So it should run faster :) I trap v$session_waits for active sessions every 5 seconds. Admittedly not as good as a trace, but it does give me some idea of what was going on when the query was run. I trapped 48 wait events for the first query and only one for the second. The one wait trapped for the second query was not significant, and nearly all the waits trapped for the second query were not significant either, except perhaps in their number. I did see a few "interesting" 'direct path read waits' interspersed with 'db file sequential read' waits indicating I/0 contention. The query plans for the two statements were identical save the extra group by for the summation. ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- In conclusion, If I just looked at the BHR for the first state and were of the opinion. "the higher the better", there should be no need to proceed further; .999684622 is pretty darn good. ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ I don't pay daily attention to the BHR. Most of my time is spent ferreting out bad SQL. So every morning I look for statements involved with significant waits, look for high disk reads, buffer gets, and check to make sure the full table scans are legitimate. If there are problems less expensive transitive queries are written or the developer is instructed to "materialize" a complex join as part of the process. Drop table followed by CTAS can solve innumerable problems. Ian MacGregor Stanford Linear Accelerator Center ian@SLAC.Stanford.edu [MacGregor, Ian A.] -----Original Message----- Sent: Thursday, August 08, 2002 2:44 PM To: Multiple recipients of list ORACLE-L First, I didn't see the broadcast. I believe the claim that a high BHR may not indicate a healthy system, has become it is always indicative of an unhealthy system. Egad, by BHR is over 90% and no users are are complaining, I better start tinkering! Isn't the idea that BHR is an unreliable indicator . Here are two statements : select a.chnum, sum(a.f) f from rollup a where a.effdt=(select max(b.effdt) from rollup b where a.chnum=b.chnum) group by a.chnum -----Original Message----- Sent: Thursday, August 08, 2002 1:31 PM To: Multiple recipients of list ORACLE-L Well, I guess that I disagree. Buffer hit radio does matter as one of the performance indicators, but certainly not the only one. Your and Mr. Milsap thesis is that LIO also is very expensive and its cost is far from being negligible, so having gazillion of LIOs instead of 100 times smaller number of PIOs will not make our system run faster. BHR alone cannot be used to judge to overall health of the system, but thebn again, there is no such thing as the "overall health of the system". It's the users of the system who will say whether the performance is satisfactory or not, and I'm usually tuning an application, not an imaginary "overall system". Low cache hit ratio usually tells me that I do have a hog who is using lots of PIOs. By my experience, it usually is a very good indicator that something is wrong, at least on an OLTP system. So, after all, I do find BHR a useful indicator, but by no means the only one or the most important one. Event 10046, SQL_TRACE (level 1 of 10046), explain plan and v$session_event still are the tools I need most, but I still do need BHR as an indicator. Mladen Gogala Oracle DBA Phone: (203) 459-6855 Email: mgogala@oxhp.com -----Original Message----- Sent: Thursday, August 08, 2002 1:05 PM To: Multiple recipients of list ORACLE-L Moi wrong ;-) Jeeh, human after all To summarize the webcast: db-block-buffers do mattter. Too many LIO do matter. Too many PIO do matter. But Buffer Cache Hit ratio doesn't matter ....... End user satisfaction does matter. I am always willing to clarify any points that I made, you just have to ask me l .... Anjo. ----- Original Message ----- To: Multiple recipients of list ORACLE-L Sent: Thursday, August 08, 2002 5:43 PM Guys, I had this dream that I missed the webcast - which I did. However, someone said it wasn't very interesting but the conversation of the people (gurus) left over was very interesting as there was good solid evidence that he was incorrect and db_block_buffers do matter. Kind of inline with the discussion about redos yesterday and my indexing/partition issues - hmmm. -----Original Message----- Sent: Thursday, August 08, 2002 1:38 AM To: Multiple recipients of list ORACLE-L Www.precise.com, go to Events->webcasts... On 2002.08.08 00:53 Madhusudhanan Sampath wrote: > Are transcript documents available anywhere? > > Regards > Madhusudhanan S > > > > _________________________________________________________________ > MSN Photos is the easiest way to share and print your photos: > http://photos.msn.com/support/worldwide.aspx > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Madhusudhanan Sampath > INET: madhulist@hotmail.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@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). > -- Mladen Gogala -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mladen Gogala INET: mgogala@adelphia.net 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@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). --Boundary_(ID_OrdVqewf38fzENfeYi5NLQ) Content-type: text/html; charset=iso-8859-1 Content-transfer-encoding: 7BIT RE: missed Anjo's webcast..
Sorry hit the return too quickly,  Resuming
 
and
 
                   select a.aid, a.chnum, f
                    from rollup a
                    where a.effdt=(select   max(b.effdt) from rollup b where a.chnum=b.chnum)
 
 
 
Admittedly they are  differ by the aggregate function (sum).    The one with the sum was killed by the user, but statspack captured the following
 
EXECUTIONS DISK_READS      ROWS_PROCESSED      SORTS PARSE_CALLS BUFFER_GETS
-------------------- ------------------------- ----------------------------------      ---------- ------------------------ -----------------------
                   1                 2922                                  0        -7833                        1            9262161     for the first query
                   1                 1498                                 60          130                        1                11450     for the second.
 
 
It is obvious that the first query is more  expensive than the  second.  However the first query  has a  better BHR than the  second.    So it should run faster :)   I trap v$session_waits for active sessions every 5 seconds.  Admittedly not as good as a trace, but it does give me some idea of what was going on when the query was run.
I trapped 48 wait events for the first query and only one for the second.  The one wait trapped for the second query was not significant, and nearly all the waits trapped for the second query were not significant either, except perhaps in their number.  I did see a few  "interesting"  'direct path read waits'  interspersed with   'db file sequential  read' waits  indicating I/0 contention.
 
The query plans for the two statements were identical save the extra group by for the  summation.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
In conclusion, If I just looked at the BHR for the first state and were of the opinion. "the higher the better",  there should be no need to proceed further; .999684622 is pretty darn good.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
I don't pay daily attention to the BHR.  Most of my time is spent ferreting out bad SQL.    So every morning I look for statements involved with significant waits,  look for high disk reads, buffer gets, and check to make sure  the full table scans are legitimate.   If there are problems less expensive transitive queries are written  or the developer is instructed to "materialize" a complex join as part of  the process.  Drop table followed by CTAS can solve innumerable problems.
 
 
Ian MacGregor
Stanford Linear Accelerator Center
 
 
 
 
 

[MacGregor, Ian A.]  -----Original Message-----
From: MacGregor, Ian A.
Sent: Thursday, August 08, 2002 2:44 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: missed Anjo's webcast..
First, I didn't see the broadcast.  I believe the claim that a  high BHR may not indicate a healthy system, has  become it is always indicative of  an unhealthy system.
Egad, by BHR is over 90% and  no users are are complaining,  I better start tinkering!  Isn't the idea  that BHR is an unreliable indicator
.  
 
Here are two statements :
 
 
 
 
 
                 select a.chnum, sum(a.f) f from rollup a where a.effdt=(select max(b.effdt)
                 from rollup b where a.chnum=b.chnum) 
                 group by a.chnum
 
 
   
 
-----Original Message-----
From: Gogala, Mladen [mailto:MGogala@oxhp.com]
Sent: Thursday, August 08, 2002 1:31 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: missed Anjo's webcast..

Well, I guess that I disagree. Buffer hit radio does matter as one of the performance indicators, but
certainly not the only one. Your and Mr. Milsap thesis is that LIO also is very expensive and its cost
is far from being negligible, so having gazillion of LIOs instead of 100 times smaller number of PIOs will
not make our system run faster. BHR alone cannot be used to judge to overall health of the system, but
thebn again, there is no such thing as the "overall health of the system". It's the users of the system who
will say whether the performance is satisfactory or not, and I'm usually tuning an application, not an
imaginary "overall system". Low cache hit ratio usually tells me that I do have a hog who is using lots
of PIOs. By my experience, it usually is a very good indicator that something is wrong, at least on an OLTP
 system. So, after all, I do find BHR a useful indicator, but by no means the only one or the most important
one. Event 10046, SQL_TRACE (level 1 of 10046), explain plan and v$session_event still are the tools
I need most, but I still do need BHR  as an indicator.

Mladen Gogala
Oracle DBA
Phone: (203) 459-6855
Email:  mgogala@oxhp.com

-----Original Message-----
From: Anjo Kolk [mailto:anjo@oraperf.com]
Sent: Thursday, August 08, 2002 1:05 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: missed Anjo's webcast..

Moi wrong ;-) Jeeh, human after all
 
To summarize the webcast:
db-block-buffers do mattter. Too many LIO do matter. Too many PIO do matter. But Buffer Cache Hit ratio doesn't matter ....... End user satisfaction does matter.
 
I am always willing to clarify any points that I made, you just have to ask me l ....
 
Anjo.
 
 
----- Original Message -----
Sent: Thursday, August 08, 2002 5:43 PM
Subject: RE: missed Anjo's webcast..

Guys,

I had this dream that I missed the webcast - which I did.  However, someone said it wasn't very interesting but the conversation of the people (gurus) left over was very interesting as there was good solid evidence that he was incorrect and db_block_buffers do matter.  Kind of inline with the discussion about redos yesterday and my indexing/partition issues - hmmm.

-----Original Message-----
From: Mladen Gogala [mailto:mgogala@adelphia.net]
Sent: Thursday, August 08, 2002 1:38 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: missed Anjo's webcast..


Www.precise.com, go to Events->webcasts...
On 2002.08.08 00:53 Madhusudhanan Sampath wrote:
> Are transcript documents available anywhere?
>
> Regards
> Madhusudhanan S
>
>
>
> _________________________________________________________________
> MSN Photos is the easiest way to share and print your photos:
> http://photos.msn.com/support/worldwide.aspx
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Madhusudhanan Sampath
>   INET: madhulist@hotmail.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@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).
>

--
Mladen Gogala
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mladen Gogala
  INET: mgogala@adelphia.net

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@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).

--Boundary_(ID_OrdVqewf38fzENfeYi5NLQ)-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: ian@SLAC.Stanford.EDU 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@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).