RE: CBO irregularity

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Tue, 8 Jun 2004 01:51:51 -0500
Message-ID: <021001c44d25$171ad660$6801a8c0@CVMLAP02>

There are at least three reasons:

Different Oracle kernel ports restrict the max allowable multi-block read count size to different values.
The Oracle kernel will not issue a read call across an extent boundary.
The Oracle kernel will not issue a physical read call for a block if that block is already in the database buffer cache.

Check your trace data. You'll probably find that many of your multi-block read calls are nowhere near the size that you've specified in your db_file_multiblock_read_count parameter.

For more details, see Jeff Holt's "Predicting multi-block read call sizes" at www.hotsos.com/e-library.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Nullius in verba

Upcoming events:
- Performance <http://www.hotsos.com/courses/PD101.php> Diagnosis 101: 6/22
Pittsburgh, 7/20 Cleveland, 8/10 Boston
- SQL Optimization <http://www.hotsos.com/courses/OP101.php> 101: 5/24 San
Diego, 6/14 Chicago, 6/28 Denver
- Hotsos Symposium 2005: March 6-10 Dallas

Visit www.hotsos.com for schedule details...

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Naveen, Nahata (IE10)
Sent: Tuesday, June 08, 2004 1:39 AM
To: oracle-l_at_freelists.org
Subject: RE: CBO irregularity

Thanx Cary and Satheesh,

Why wud the no. of blocks read be less than DB_FILE_MULTBLOCK_READ_COUNT?

Is it dependent on the I/O sub-system?

Regards

Naveen

True. The Oracle kernel also collects this information when you use dbms_stats.gather_database_stats.

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:49:53 CDT