Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: CBO irregularity
Not sure if my reply is attached to the correct original, but:
Over an interval, you can examine v$filestat which has columns
phyrds -- "physical" read requests
phyblksrd -- total blocks read
singleblkrds -- single block reads
The average multiblock read for the interval then ought to be:
(phyblksrd - singleblkrds) / phyrds
This seems to be the way Oracle works it out when you use dbms_stats.gather_system_stats. (Oracle then rounds the result, but you can use dbms_stats.set_system_stats to set a non-rounded value, which I think would be perfectly reasonable)
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st
: - Make sure that your system statistics reasonably represent the
: operational characteristics of your system. For example, if your system
: really averages 3.7 blocks per multi-block read, then CBO will make dumb
: decisions about whether to do full-table scans on systems were
: db_file_multiblock_read_count=128.
:
: [Naveen Nahata] Can you please elaborate on how I can find, how many
blocks
: my system reads on an average per oracle multi-block read request?
:
:
:
: Regards
: Naveen
:
:
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Jun 08 2004 - 01:51:08 CDT