Database Reference
In-Depth Information
Missing Statistics
As soon as one of the objects referenced in a SQL statement has object statistics, the CBO is
used to calculate the best execution plan. When statistics for one or more tables, indexes, or
partitions thereof are missing, the calculation may go awry. The 10053 trace file points out
which objects lack statistics, and contains the default values CBO uses instead of actual values
computed with
DBMS_STATS
.
Table Stats::
Table: LOCATIONS Alias: L (NOT ANALYZED)
#Rows: 409 #Blks: 5 AvgRowLen: 100.00
Column (#1): LOCATION_ID(NUMBER) NO STATISTICS (using defaults)
AvgLen: 22.00 NDV: 13 Nulls: 0 Density: 0.07824
Index Stats::
Index: LOC_CITY_IX Col#: 4 (NOT ANALYZED)
LVLS: 1 #LB: 25 #DK: 100 LB/K: 1.00 DB/K: 1.00 CLUF: 800.00
Single Table Access Path and Cost
The optimizer ignores join conditions in the single table access path section. Solely access
predicates, which were supplied a value with a literal or bind variable, are considered. In the
absence of such predicates, merely a full table scan or index fast full scan are considered for
a table. The optimizer estimates how many rows will be retrieved and calculates the cheapest
alternative for getting them. In the following excerpt, the optimizer determines that it is cheaper
to use the index
LOC_CITY_IX
than to perform a full table scan:
SINGLE TABLE ACCESS PATH
Column (#4): CITY(VARCHAR2)
AvgLen: 9.00 NDV: 23 Nulls: 0 Density: 0.043478
Table: LOCATIONS Alias: L
Card: Original: 23 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
Access Path: TableScan
Cost: 3.01 Resp: 3.01 Degree: 0
Cost_io: 3.00 Cost_cpu: 41607
Resp_io: 3.00 Resp_cpu: 41607
Access Path: index (AllEqRange)
Index: LOC_CITY_IX
resc_io: 2.00 resc_cpu: 14673
ix_sel: 0.043478 ix_sel_with_filters: 0.043478
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange Index: LOC_CITY_IX
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 1.00 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
Table: JOBS Alias: J
Card: Original: 19 Rounded: 19 Computed: 19.00 Non Adjusted: 19.00
