Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: CBO irregularity

Re: CBO irregularity

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 8 Jun 2004 07:54:23 +0100
Message-ID: <027901c44d25$6e423d20$7102a8c0@Primary>

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

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



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US