RE: low buffer hit ratio
Dennis: OEM calculates the BHR for all the queries. I presume it is being calculated using the same formula but only using the disk reads and buffer gets for the single query. I started with this query because it has the lowest bhr in the database. --- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: > 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? > 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).
Re: low buffer hit ratio
--- Stephane Faroult <[EMAIL PROTECTED]> wrote: > > - 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. It returns 8 rows in about 2-2.5 seconds. > - 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 */). I did something like that. I have prevented Oracle from using the index STATS_FK2 (on a manager field) by comcatinating ||'' to the manager field name. It forced Oracle to use the index on the date field (more selective) and reduced the time to 0.5 sec and I think increased the bhr to 95%. Thanks for your help 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).
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: [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).
RE: low buffer hit ratio
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).
low buffer hit ratio
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).
