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: db file scattered read

Re: db file scattered read

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sun, 15 Aug 2004 12:17:42 -0400
Message-ID: <20040815161742.GA16992@medo.noip.com>

On 08/07/2004 11:27:17 AM, Wolfgang Breitling wrote:  Nowhere in this list is there any indication that all tables will be
> sampled. Only unanalyzed tables and "tables for which standard selectivity
> estimation used a guess for some predicate" (whatever that means) and at
> level 4 for tables with more than one predicate in an attempt to detect
> attribute dependence. In the latter case the CBO will only sample the
> combined selectivity of those predicates and in my experience is rarely
> satisfied with the sampling results from 32 blocks. I usually have to go
> much higher to the point where it practically scans the entire table.
> Admittedly I haven't tested it on multi-million row tables, only on tables
> where the 1024 or 4096 sampled blocks at level 8 or 9 are more than 50% of
> the total blocks and thus a full scan is done instead of a sample even
> before level 10.

Wolfgang, here is a little bit more detailed study of OPTIMIZER_DYNAMIC_SAMPLING. First, I dropped statistics on the scott user and executed simple query. Just as you have shown before, the database resorted to RBO and the trace was not generated. When I forced CBO with the optimizer mode, dynamic sampling kicked in. The generated trace file is a bit longer, but please, bear with me.

/oracle/product/10g/admin/oracle/udump/10g_ora_16916.trc Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /oracle/product/10g

System name:	Linux
Node name:	medo.noip.com
Release:	2.4.27
Version:	#1 Sun Aug 8 01:31:49 EDT 2004
Machine:	i686

Instance name: 10g
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 16916, image: oracle10g_at_medo.noip.com
-- 
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
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 Sun Aug 15 2004 - 11:13:54 CDT

Original text of this message

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