Oracle-L: RE: low buffer hit ratio

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Thu, 14 Nov 2002 18:23:37 -0800
Message-ID: <F001.00503F0C.20021114182337@fatcity.com>

Gurelei - What do you mean by a low buffer hit ratio for a SQL statement? BHR is measured for the system, not for an individual query.

Why have you singled this query out for attention? Are there complaints about its performance? Does it hit more blocks that other queries on your system?

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Thursday, November 14, 2002 4:04 PM To: Multiple recipients of list ORACLE-L

Hi.

I found a query with a buffer hit ratio about 60%: SELECT b.Name, a.Racf, c.Manager, < fileds from STATS table>
FROM BT.Stats a, BT.Employees b, BT.Employees c WHERE a.Stats_Date Between '13-Nov-02' and '13-Nov-02' and a.Manager = c.Manager and
c.racf = 'RLEWI01' and b.Racf = a.Racf
and ( Product='RTI' OR Product = 'RIM' ... or Product = 'WEB')
Group by b.Name, a.Racf, c.Manager Order by b.Name

Here is the plan under oracle 7.3.3 rbo:

1.0 SELECT STATEMENT   (, , )
  2.1 SORT GROUP BY  (, , )
    3.1 NESTED LOOPS   (, , )
      4.1 NESTED LOOPS   (, , )
        5.1 TABLE ACCESS BY ROWID EMPLOYEES (, , )
          6.1 INDEX UNIQUE SCAN EMPLOYEES_PK (, , )
        5.2 TABLE ACCESS BY ROWID STATS (, , )
          6.1 INDEX RANGE SCAN STATS_FK2 (, , )
      4.2 TABLE ACCESS BY ROWID EMPLOYEES (, , )
        5.1 INDEX UNIQUE SCAN EMPLOYEES_PK (, , )

The largest table is STATS - 1.6 mil rows. STATS_FK2 is
an index on MANAGER field. We have only 115 different managers so the index is not very selective. The stats_date field is more selective. Does it make sence for me to force Oracle to use that index in order to reduce the number of disk reads?

thanks

Gene

Do you Yahoo!?
Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Gurelei
INET: gurelei_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.com
--

Author: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------