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.
[EMAIL PROTECTED]
-----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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
