Database Reference
In-Depth Information
Access Path: TableScan
Cost: 3.01 Resp: 3.01 Degree: 0
Cost_io: 3.00 Cost_cpu: 38837
Resp_io: 3.00 Resp_cpu: 38837
Best:: AccessPath: TableScan
Cost: 3.01 Degree: 1 Resp: 3.01 Card: 19.00 Bytes: 0
***************************************
According to Jonathan Lewis ([Lewi 2005]), the CBO calculates the cost of a B-tree index
access with this formula:
LVLS
+
ceiling #LB
ix_sel
ceiling CLUF
+
ix_sel_with_filters
The values for ix_sel (index selectivity) and ix_sel_with_filters (effective table selectivity)
are found in the trace file. The values for LVLS (B-tree level), #LB (number of leaf blocks), and
CLUF (clustering factor) are in the section entitled “BASE STATISTICAL INFORMATION”
presented earlier. When the formula is applied to the index
LOC_CITY_IX
, it does yield the same
result as in the optimizer trace.
0
+
ceiling 1
0.043478
ceiling 1
+
0.043478
=
2
The formula also gives correct results when applied to more complicated cases.
Dynamic Sampling
Dynamic sampling is a feature of the CBO introduced with Oracle9
i
. Dynamic sampling is the
capability of the CBO to calculate statistics based on a small sample of rows as it optimizes a
query. The feature is controlled by the parameter
OPTIMIZER_DYNAMIC_SAMPLING
and is enabled
by default in Oracle9
i
Release 2 and subsequent versions. The following excerpt depicts the
SELECT
statement the optimizer used to dynamically sample the table
LOCATIONS
, after object
statistics for the table had been deleted:
** Generated dynamic sampling query:
query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param('parallel_execution_enabled',
'false')
NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB)
NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0), NVL(SUM(C3),0)
FROM (SELECT /*+ NO_PARALLEL("L") INDEX("L" LOC_CITY_IX)
NO_PARALLEL_INDEX("L") */ 1 AS C1, 1 AS C2, 1 AS C3
FROM "LOCATIONS" "L" WHERE "L"."CITY"=:B1
AND ROWNUM <= 2500) SAMPLESUB
*** 2007-11-30 16:44:25.703
** Executed dynamic sampling query:
level : 2
sample pct. : 100.000000
actual sample size : 23
filtered sample card. : 1
