Re: db file scattered read
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 15 Aug 2004 12:17:42 -0400
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@xxxxxxxxxxxxx
*** 2004-08-15 12:01:01.054
*** ACTION NAME:() 2004-08-15 12:01:01.054
*** MODULE NAME:(SQL*Plus) 2004-08-15 12:01:01.054
*** SERVICE NAME:(oracle.noip.com) 2004-08-15 12:01:01.054
*** SESSION ID:(91.43) 2004-08-15 12:01:01.054
QUERY
select ename,job,dname,loc
from emp e, dept d
where e.deptno=d.deptno
order by e.empno
QUERY
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
*** 2004-08-15 12:08:06.773
QUERY
alter session set optimizer_mode=first_rows
QUERY
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
*******************************************
Peeked values of the binds in SQL statement
*******************************************
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
hash_area_size = 1048576
sort_area_size = 1048576
db_file_multiblock_read_count = 64
optimizer_mode = first_rows
star_transformation_enabled = true
optimizer_index_cost_adj = 30
optimizer_index_caching = 70
optimizer_dynamic_sampling = 4
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true
parallel_query_forced_dop = 0
parallel_dml_forced_dop = 0
parallel_ddl_forced_degree = 0
parallel_ddl_forced_instances = 0
_query_rewrite_fudge = 90
optimizer_features_enable = 10.1.0.3
_optimizer_search_limit = 5
cpu_count = 1
active_instance_count = 1
parallel_threads_per_cpu = 2
bitmap_merge_area_size = 1048576
sort_area_retained_size = 0
_sort_elimination_cost_ratio = 0
_optimizer_block_size = 8192
_sort_multiblock_read_count = 2
_hash_multiblock_io_count = 0
_optimizer_max_permutations = 2000
pga_aggregate_target = 394743 KB
_pga_max_size = 204800 KB
_sort_space_for_write_buffers = 1
_query_rewrite_maxdisjunct = 257
_smm_auto_min_io_size = 56 KB
_smm_auto_max_io_size = 248 KB
_smm_min_size = 394 KB
_smm_max_size = 19737 KB
_smm_px_max_size = 118422 KB
_cpu_to_io = 0
_optimizer_undo_cost_change = 10.1.0.3
parallel_query_mode = enabled
parallel_dml_mode = disabled
parallel_ddl_mode = enabled
sqlstat_enabled = false
_optimizer_percent_parallel = 101
_always_anti_join = choose
_always_semi_join = choose
_optimizer_mode_force = true
_partition_view_enabled = true
_always_star_transformation = false
_query_rewrite_or_error = false
_hash_join_enabled = true
cursor_sharing = exact
_b_tree_bitmap_plans = true
_optimizer_cost_model = choose
_new_sort_cost_estimate = true
_complex_view_merging = true
_unnest_subquery = true
_eliminate_common_subexpr = true
_pred_move_around = true
_convert_set_to_join = false
_push_join_predicate = true
_push_join_union_view = true
_fast_full_scan_enabled = true
_optim_enhance_nnull_detection = true
_parallel_broadcast_enabled = true
_px_broadcast_fudge_factor = 100
_ordered_nested_loop = true
_no_or_expansion = false
_system_index_caching = 0
_disable_datalayer_sampling = false
query_rewrite_enabled = true
query_rewrite_integrity = enforced
_query_cost_rewrite = true
_query_rewrite_2 = true
_query_rewrite_1 = true
_query_rewrite_expression = true
_query_rewrite_jgmigrate = true
_query_rewrite_fpc = true
_query_rewrite_drj = true
_full_pwise_join_enabled = true
_partial_pwise_join_enabled = true
_left_nested_loops_random = true
_improved_row_length_enabled = true
_index_join_enabled = true
_enable_type_dep_selectivity = true
_improved_outerjoin_card = true
_optimizer_adjust_for_nulls = true
_optimizer_degree = 0
_use_column_stats_for_function = true
_subquery_pruning_enabled = true
_subquery_pruning_mv_enabled = false
_or_expand_nvl_predicate = true
_like_with_bind_as_equality = false
_table_scan_cost_plus_one = true
_cost_equality_semi_join = true
_default_non_equality_sel_check = true
_new_initial_join_orders = true
_oneside_colstat_for_equijoins = true
_optim_peek_user_binds = true
_minimal_stats_aggregation = true
_force_temptables_for_gsets = false
workarea_size_policy = auto
_smm_auto_cost_enabled = true
_gs_anti_semi_join_allowed = true
_optim_new_default_join_sel = true
_pre_rewrite_push_pred = true
_optimizer_new_join_card_computation = true
_union_rewrite_for_gs = yes_gset_mvs
_generalized_pruning_enabled = true
_optim_adjust_for_part_skews = true
_force_datefold_trunc = false
statistics_level = typical
_optimizer_system_stats_usage = true
skip_unusable_indexes = true
_remove_aggr_subquery = true
_optimizer_push_down_distinct = 0
_dml_monitoring_enabled = true
_optimizer_undo_changes = false
_predicate_elimination_enabled = true
_nested_loop_fudge = 100
_project_view_columns = true
_local_communication_costing_enabled = true
_local_communication_ratio = 50
_query_rewrite_vop_cleanup = true
_slave_mapping_enabled = true
_optimizer_cost_based_transformation = linear
_optimizer_mjc_enabled = true
_right_outer_hash_enable = true
_spr_push_pred_refspr = true
_optimizer_cache_stats = false
_optimizer_cbqt_factor = 50
_optimizer_squ_bottomup = true
_fic_area_size = 131072
_optimizer_skip_scan_enabled = true
_optimizer_cost_filter_pred = false
_optimizer_sortmerge_join_enabled = true
_optimizer_join_sel_sanity_check = true
_mmv_query_rewrite_enabled = false
_bt_mmv_query_rewrite_enabled = true
_add_stale_mv_to_dependency_list = true
_distinct_view_unnesting = false
_optimizer_dim_subq_join_sel = true
_optimizer_disable_strans_sanity_checks = 0
_optimizer_compute_index_stats = true
_push_join_union_view2 = true
_optimizer_ignore_hints = false
_optimizer_random_plan = 0
_query_rewrite_setopgrw_enable = true
_optimizer_correct_sq_selectivity = true
_disable_function_based_index = false
_optimizer_join_order_control = 3
_optimizer_push_pred_cost_based = true
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=0 objn=45599 hint_alias="D"@"SEL$1"
fro(1): flg=0 objn=45601 hint_alias="E"@"SEL$1"
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats Table: DEPT Alias: D
TOTAL :: (NOT ANALYZED) CDN: 409 NBLKS: 5 AVG_ROW_LEN: 100
COLUMN: DEPTNO(NUMBER) Col#: 1 Table: DEPT Alias: D
NO STATISTICS (using defaults)
Size: 22 NDV: 13 Nulls: 0 Density: 7.8240e-02
No Histogram: #BKT: 0
(0 uncompressed buckets and 0 endpoint values)
Index stats
Index: PK_DEPT COL#: 1
TOTAL :: LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF: 800
***********************
Table stats Table: EMP Alias: E
TOTAL :: (NOT ANALYZED) CDN: 409 NBLKS: 5 AVG_ROW_LEN: 100
COLUMN: DEPTNO(NUMBER) Col#: 8 Table: EMP Alias: E
NO STATISTICS (using defaults)
Size: 22 NDV: 13 Nulls: 0 Density: 7.8240e-02
No Histogram: #BKT: 0
(0 uncompressed buckets and 0 endpoint values)
Index stats
Index: PK_EMP COL#: 1
TOTAL :: LVLS: 1 #LB: 25 #DK: 100 LB/K: 1 DB/K: 1 CLUF: 800
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
*** 2004-08-15 12:08:14.530
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 4).
** Dynamic sampling updated table stats.: blocks=5
*** 2004-08-15 12:08:14.531
** Generated dynamic sampling query:
query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) */ NVL(SUM(C1),0),
NVL(SUM(C2),0), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN 1 ELSE 0
END),0) FROM (SELECT /*+ NO_PARALLEL("E") FULL("E") NO_PARALLEL_INDEX("E") */ 1
AS C1, 1 AS C2, "E"."DEPTNO" AS C3 FROM "EMP" "E") SAMPLESUB
*** 2004-08-15 12:08:14.568
** Executed dynamic sampling query:
level : 4
sample pct. : 100.000000
actual sample size : 14
filtered sample card. : 14
orig. card. : 409
block cnt. table stat. : 5
block cnt. for sampling: 5
max. sample block cnt. : 64
sample block cnt. : 5
ndv C3 : 3
scaled : 3.00
nulls C4 : 0
scaled : 0.00
min. sel. est. : -1.00000000
** Dynamic sampling col. stats.:
COLUMN: DEPTNO(NUMBER) Col#: 8 Part#: 0 Table: EMP Alias: E
Size: 22 NDV: 3 Nulls: 0 Density: 3.3333e-01
No Histogram: #BKT: 1
(1 uncompressed buckets and 0 endpoint values)
** Using dynamic sampling NULLs estimates.
** Using dynamic sampling NDV estimates.
Scaled NDVs using cardinality = 14.
** Using dynamic sampling card. : 14
** Dynamic sampling updated table card.
TABLE: EMP Alias: E
Original Card: 14 Rounded: 14 Computed: 14.00 Non Adjusted: 14.00
Access Path: table-scan Resc: 3 Resp: 3
BEST_CST: 3.01 PATH: 2 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
*** 2004-08-15 12:08:14.569
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning TRUE (level = 4).
** Dynamic sampling updated index stats.: PK_DEPT, blocks=1
** Dynamic sampling updated table stats.: blocks=5
*** 2004-08-15 12:08:14.573
** Generated dynamic sampling query:
query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) */ NVL(SUM(C1),0),
NVL(SUM(C2),0), COUNT(DISTINCT C3), NVL(SUM(CASE WHEN C3 IS NULL THEN 1 ELSE 0
END),0) FROM (SELECT /*+ NO_PARALLEL("D") FULL("D") NO_PARALLEL_INDEX("D") */ 1
AS C1, 1 AS C2, "D"."DEPTNO" AS C3 FROM "DEPT" "D") SAMPLESUB
*** 2004-08-15 12:08:14.577
** Executed dynamic sampling query:
level : 4
sample pct. : 100.000000
actual sample size : 4
filtered sample card. : 4
orig. card. : 409
block cnt. table stat. : 5
block cnt. for sampling: 5
max. sample block cnt. : 64
sample block cnt. : 5
ndv C3 : 4
scaled : 4.00
nulls C4 : 0
scaled : 0.00
min. sel. est. : -1.00000000
** Dynamic sampling col. stats.:
COLUMN: DEPTNO(NUMBER) Col#: 1 Part#: 0 Table: DEPT Alias: D
Size: 22 NDV: 4 Nulls: 0 Density: 2.5000e-01
No Histogram: #BKT: 1
(1 uncompressed buckets and 0 endpoint values)
** Using dynamic sampling NULLs estimates.
** Using dynamic sampling NDV estimates.
Scaled NDVs using cardinality = 4.
** Using dynamic sampling card. : 4
** Dynamic sampling updated table card.
TABLE: DEPT Alias: D
Original Card: 4 Rounded: 4 Computed: 4.00 Non Adjusted: 4.00
Access Path: table-scan Resc: 3 Resp: 3
BEST_CST: 3.01 PATH: 2 Degree: 1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]: DEPT[D]#0 EMP[E]#1
Now joining: EMP[E]#1 *******
NL Join
Outer table: cost: 3 cdn: 4 rcz: 30 resp: 3
Inner table: EMP Alias: E
Access Path: table-scan Resc: 2
Join: Resc: 9 Resp: 9
Best NL cost: 9 resp: 9
Join Card: 14.00 = outer (4.00) * inner (14.00) * sel (2.5000e-01)
SM Join
Outer table:
resc: 3 cdn: 4 rcz: 30 deg: 1 resp: 3
Inner table: EMP Alias: E
resc: 3 cdn: 14 rcz: 39 deg: 1 resp: 3
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 113 Area size: 403456 Max Area size: 20210688
Degree: 1
Blocks to Sort: 1 Row size: 43 Total Rows: 4
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 3587400
Total Temp space used: 0
SORT resource Sort statistics
Sort width: 113 Area size: 403456 Max Area size: 20210688
Degree: 1
Blocks to Sort: 1 Row size: 53 Total Rows: 14
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 3589442
Total Temp space used: 0
Merge join Cost: 8 Resp: 8
SM Join (with index on outer)
Access Path: index (no start/stop keys)
Index: PK_DEPT
rsc_cpu: 6536395 rsc_io: 802
ix_sel: 1.0000e+00 ix_sel_with_filters: 1.0000e+00
Outer table:
resc: 241 cdn: 4 rcz: 30 deg: 1 resp: 241
Inner table: EMP Alias: E
resc: 3 cdn: 14 rcz: 39 deg: 1 resp: 3
using join:1 distribution:2 #groups:1
SORT resource Sort statistics
Sort width: 113 Area size: 403456 Max Area size: 20210688
Degree: 1
Blocks to Sort: 1 Row size: 53 Total Rows: 14
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 3589442
Total Temp space used: 0
Merge join Cost: 245 Resp: 245
HA Join
Outer table:
resc: 3 cdn: 4 rcz: 30 deg: 1 resp: 3
Inner table: EMP Alias: E
resc: 3 cdn: 14 rcz: 39 deg: 1 resp: 3
using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 1 Deg: 1
hash_area: 124 (max=4935) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join Resc: 7 Resp: 7
ORDER BY sort
SORT resource Sort statistics
Sort width: 113 Area size: 403456 Max Area size: 20210688
Degree: 1
Blocks to Sort: 1 Row size: 86 Total Rows: 14
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 3589442
Total Temp space used: 0
Join result: cost: 8 cdn: 14 rcz: 69
Best so far: TABLE#: 0 CST: 3 CDN: 4 BYTES: 120
Best so far: TABLE#: 1 CST: 8 CDN: 14 BYTES: 966
***********************
Join order[2]: EMP[E]#1 DEPT[D]#0
Now joining: DEPT[D]#0 *******
NL Join
Outer table: cost: 3 cdn: 14 rcz: 39 resp: 3
Inner table: DEPT Alias: D
Access Path: table-scan Resc: 1
Join: Resc: 20 Resp: 20
OPTIMIZER PERCENT INDEX CACHING = 70
Access Path: index (unique)
Index: PK_DEPT
rsc_cpu: 15483 rsc_io: 1
ix_sel: 2.5000e-01 ix_sel_with_filters: 2.5000e-01
NL Join: resc: 7 resp: 7
OPTIMIZER PERCENT INDEX CACHING = 70
Access Path: index (eq-unique)
Index: PK_DEPT
rsc_cpu: 15683 rsc_io: 1
ix_sel: 0.0000e+00 ix_sel_with_filters: 0.0000e+00
NL Join: resc: 7 resp: 7
Best NL cost: 7 resp: 7
Join Card: 14.00 = outer (14.00) * inner (4.00) * sel (2.5000e-01)
ORDER BY sort
SORT resource Sort statistics
Sort width: 113 Area size: 403456 Max Area size: 20210688
Degree: 1
Blocks to Sort: 1 Row size: 86 Total Rows: 14
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 3589442
Total Temp space used: 0
Join result: cost: 8 cdn: 14 rcz: 69
Best so far: TABLE#: 1 CST: 3 CDN: 14 BYTES: 546
Best so far: TABLE#: 0 CST: 8 CDN: 14 BYTES: 966
****** Recost for ORDER BY (using index) ************
***************************************
SINGLE TABLE ACCESS PATH
*** 2004-08-15 12:08:14.580
** Performing dynamic sampling initial checks. **
** TABLE EMP Alias: E : reused cached dynamic sampling result (success).
cardinality : 14
TABLE: EMP Alias: E
Original Card: 14 Rounded: 14 Computed: 14.00 Non Adjusted: 14.00
Access Path: table-scan Resc: 3 Resp: 3
Access Path: index (no start/stop keys)
Index: PK_EMP
rsc_cpu: 6707309 rsc_io: 826
ix_sel: 1.0000e+00 ix_sel_with_filters: 1.0000e+00
BEST_CST: 248.36 PATH: 4 Degree: 1
***********************
Join order[2]: EMP[E]#1 DEPT[D]#0
Now joining: DEPT[D]#0 *******
NL Join
Outer table: cost: 248 cdn: 14 rcz: 39 resp: 248
Inner table: DEPT Alias: D
Access Path: table-scan Resc: 1
Join: Resc: 266 Resp: 266
OPTIMIZER PERCENT INDEX CACHING = 70
Access Path: index (unique)
Index: PK_DEPT
rsc_cpu: 15483 rsc_io: 1
ix_sel: 2.5000e-01 ix_sel_with_filters: 2.5000e-01
NL Join: resc: 253 resp: 253
OPTIMIZER PERCENT INDEX CACHING = 70
Access Path: index (eq-unique)
Index: PK_DEPT
rsc_cpu: 15683 rsc_io: 1
ix_sel: 0.0000e+00 ix_sel_with_filters: 0.0000e+00
NL Join: resc: 253 resp: 253
Best NL cost: 253 resp: 253
Join Card: 14.00 = outer (14.00) * inner (4.00) * sel (2.5000e-01)
Join result: cost: 253 cdn: 14 rcz: 69
Best so far: TABLE#: 1 CST: 248 CDN: 14 BYTES: 546
Best so far: TABLE#: 0 CST: 253 CDN: 14 BYTES: 966
(newjo-save) [1 0 ]
SORT resource Sort statistics
Sort width: 113 Area size: 403456 Max Area size: 20210688
Degree: 1
Blocks to Sort: 1 Row size: 86 Total Rows: 14
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 3589442
Total Temp space used: 0
Final - First Rows Plan:
JOIN ORDER: 2
CST: 253 CDN: 14 RSC: 253 RSP: 253 BYTES: 966
IO-RSC: 252 IO-RSP: 252 CPU-RSC: 2077221 CPU-RSP: 2077221
QUERY
select ename,job,dname,loc
from emp e, dept d
where e.deptno=d.deptno
order by e.empno
QUERY
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
--
Mladen Gogala
Oracle DBA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
