low buffer hit ratio

From: Gurelei <gurelei_at_yahoo.com>
Date: Thu, 14 Nov 2002 14:04:09 -0800
Message-ID: <F001.00503CDE.20021114140409@fatcity.com>

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). Received on Thu Nov 14 2002 - 16:04:09 CST