Re: low buffer hit ratio

Gurelei wrote:
>
> 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
>

Gene,

   First reducing the number of disk reads doesn't necessarily means that it will run faster. Except in the case of fast index scans, index blocks are usually read one-at-a-time, while in a table scan you will read batches of several blocks. If you have for instance a large number of db_file_sequential_reads, then you may find that your query will perform better with a lesser index usage. It's then a matter of knowing where the data you want is. If it happens to be physically clustered, fine (I am using 'clustered' in the general sense here, not referring to Oracle clusters); if it scattered all over the place your query is likely to be painful to run ...
  Not knowing your data it's difficult to be specific but here are some general guide-lines:

• How many rows does your query return ? If it's a huge number I would feel more comfortable with table scans than index accesses in the plan.
• You seem to feed three things into your query, racf (any relationship to the IBM product?), product (your query doesn't say from which table it comes) and the date (BTW implicit conversions like here always make me feel nervous). Which one is the most selective ? Try to have your query start with the corresponding table (I am a big fan of /*+ ORDERED */). If you have paid for the partition option,this is something to consider too for your STATS table - a good way to cluster data. A brute scan of the suitable partition is usually extremely efficient.
  • If you have to join tables with not-so-significant criteria, USE_HASH deserves consideration.

Try different things.  

-- 
HTH,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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).