| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Slow SQL, too many logical reads ?
On May 8, 1:00 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk>
wrote:
> "Charles Hooper" <hooperc2..._at_yahoo.com> wrote in message
>
> news:1178574518.539544.105860_at_u30g2000hsc.googlegroups.com...
> > Create a three column table, with the first column being the primary
> > key:
> > CREATE TABLE T1 (
> > INVOICE_ID VARCHAR2(15),
> > INVOICE_DATE DATE,
> > ENTITY_ID VARCHAR2(5) NOT NULL,
> > PRIMARY KEY(INVOICE_ID));
>
> > Create an index on the third column:
> > CREATE INDEX IND_T1_ENTITY_ID ON T1(ENTITY_ID);
>
> > Insert 3000 rows into the table, all with the same value for
> > ENTITY_ID:
> > INSERT INTO
> > T1
> > SELECT
> > TRIM(TO_CHAR(ROWNUM,'00000')) INVOICE_ID,
> > TRUNC((SYSDATE-3000)+ROWNUM) INVOICE_DATE,
> > '1' ENTITY_ID
> > FROM
> > DUAL
> > CONNECT BY
> > LEVEL<=3000;
>
> > COMMIT;
>
> > Make certain that the statistics are up to date:
> > EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'table owner
> > here',TABNAME=>'T1',CASCADE=>TRUE)
>
> > The first test:
> > SELECT
> > INVOICE_ID,
> > INVOICE_DATE,
> > ENTITY_ID
> > FROM
> > T1
> > WHERE
> > ENTITY_ID='1'
> > AND INVOICE_ID='00010'
> > AND INVOICE_DATE<(SYSDATE-100);
>
> > The ENTITY_ID column is very unselective, as there is only one
> > distinct value in that column, so Oracle should not use that index,
> > but should use the index on the primary key (INVOICE_ID column). The
> > DBMS XPLAN:
> > ------------------------------------------------------------------------------------------------------
> > | Id | Operation | Name | Starts | E-Rows |
> > A-Rows | A-Time | Buffers |
> > ------------------------------------------------------------------------------------------------------
> > |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1
> > | 1 |00:00:00.01 | 3 |
> > |* 2 | INDEX UNIQUE SCAN | SYS_C0014551 | 1 | 1
> > | 1 |00:00:00.01 | 2 |
> > ------------------------------------------------------------------------------------------------------
>
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
> > 1 - filter(("INVOICE_DATE"<SYSDATE@!-100 AND "ENTITY_ID"='1'))
> > 2 - access("INVOICE_ID"='00010')
>
> > The above DBMS XPLAN shows that it behaved as expected. Now,
> > introduce bind variable values and the unexpected happens:
> > SELECT
> > INVOICE_ID,
> > INVOICE_DATE,
> > ENTITY_ID
> > FROM
> > T1
> > WHERE
> > ENTITY_ID= :1
> > AND INVOICE_ID= :2
> > AND INVOICE_DATE<(SYSDATE-100);
>
> > The DBMS XPLAN - note that the access at step #2 has changed to the
> > very unselective index:
> > ----------------------------------------------------------------------------------------------------------
> > | Id | Operation | Name | Starts | E-
> > Rows | A-Rows | A-Time | Buffers |
> > ----------------------------------------------------------------------------------------------------------
> > |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 |
> > 1 | 1 |00:00:00.01 | 15 |
> > |* 2 | INDEX RANGE SCAN | IND_T1_ENTITY_ID | 1 |
> > 1 | 3000 |00:00:00.01 | 6 |
> > ----------------------------------------------------------------------------------------------------------
>
> > Predicate Information (identified by operation id):
> > ---------------------------------------------------
> > 1 - filter(("INVOICE_ID"=:2 AND "INVOICE_DATE"<SYSDATE@!-100))
> > 2 - access("ENTITY_ID"=:1)
>
> > Just to make certain that I did not submit the bind variables
> > incorrectly, from the 10046 trace:
> > Bind#0
> > oacdty=96 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00
> > oacflg=00 fl2=1000010 frm=01 csi=178 siz=64 off=0
> > kxsbbbfp=0ed45e3c bln=32 avl=01 flg=05
> > value="1"
> > Bind#1
> > oacdty=96 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00
> > oacflg=00 fl2=1000010 frm=01 csi=178 siz=0 off=32
> > kxsbbbfp=0ed45e5c bln=32 avl=05 flg=01
> > value="00010"
>
> > It appears that the cost based optimizer becomes confused when
> > examining the selectivity of the IND_T1_ENTITY_ID index when bind
> > variable values are used.
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> Charles,
>
> I can't get the test case you sent to reproduce
> on either 10.2.0.1 or 10.2.0.3; I use the unique
> index in both cases.
>
> I can send you a 10053 if you want to do a point
> by point comparison to see where the difference
> occurs.
>
> Do you end up with a histogram on the ENTITY_ID
> when you do the test ? (I don't - and it does look like
> a histogram-related bug).
>
> What are your setting for system stats, and do you have
> any special settings for any of the optimizer parameters.
>
> (You could cut the parameter listing from the 10053
> and post it).
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html- Hide
I was able to produce the problem on a test box with Oracle 10.2.0.2 with Oct 2006 patch running on 32 bit Windows. I could not reproduce the problem on Oracle 10.2.0.2 with Oct 2006 patch running on 64 bit Windows, nor could I reproduce the problem on a test box (running Vista) on Oracle 10.2.0.3. The Oracle 10.2.0.2 test box (32 bit Windows) has query_rewrite_enabled set to false, optimizer_index_cost_adj set to 100, and optimizer_index_caching set to 0 - Oracle 10.2.0.2 on 64 bit Windows has parameters that are much more carefully tuned. I will be comparing the 10053 trace files from the three runs to see if anything jumps out.
The same histograms appear both in the 32 bit and 64 bit versions of
Oracle 10.2.0.2:
SELECT
TABLE_NAME,
SUBSTR(COLUMN_NAME,1,15) COLUMN_NAME,
ENDPOINT_NUMBER,
ENDPOINT_VALUE
FROM
DBA_TAB_HISTOGRAMS
WHERE
OWNER='table owner here'
AND TABLE_NAME='T1'
ORDER BY
COLUMN_NAME,
ENDPOINT_NUMBER;
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
============================== =============== =============== ============== T1 ENTITY_ID 0 2.54422546 T1 ENTITY_ID 1 2.54422546 T1 INVOICE_DATE 0 2451230 T1 INVOICE_DATE 1 2454229 T1 INVOICE_ID 0 2.50207622 T1 INVOICE_ID 1 2.50268469
It does appear that there is a histogram on the ENTITY_ID column.
Parameters from the 10053 trace on Oracle 10.2.0.2 (32 bit Windows): PARAMETERS WITH ALTERED VALUES
optimizer_features_enable = 10.2.0.1 sort_area_size = 5242880 sort_area_retained_size = 262144 sqlstat_enabled = true query_rewrite_enabled = false query_rewrite_integrity = trusted statistics_level = all
PARAMETERS WITH DEFAULT VALUES
optimizer_mode_hinted = false optimizer_features_hinted = 0.0.0 parallel_execution_enabled = false 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_search_limit = 5 cpu_count = 4 active_instance_count = 1 parallel_threads_per_cpu = 2 hash_area_size = 10485760 bitmap_merge_area_size = 1048576 _sort_elimination_cost_ratio = 0 _optimizer_block_size = 8192 _sort_multiblock_read_count = 2 _hash_multiblock_io_count = 0 _db_file_optimizer_read_count = 16 _optimizer_max_permutations = 2000 pga_aggregate_target = 307200 KB _pga_max_size = 204800 KB _query_rewrite_maxdisjunct = 257 _smm_auto_min_io_size = 56 KB _smm_auto_max_io_size = 248 KB _smm_min_size = 307 KB _smm_max_size = 61440 KB _smm_px_max_size = 153600 KB _cpu_to_io = 0 _optimizer_undo_cost_change = 10.2.0.1 parallel_query_mode = enabled parallel_dml_mode = disabled parallel_ddl_mode = enabled optimizer_mode = all_rows _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 star_transformation_enabled = false _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 optimizer_index_cost_adj = 100 optimizer_index_caching = 0 _system_index_caching = 0 _disable_datalayer_sampling = false _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 optimizer_dynamic_sampling = 2 _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 _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 = true _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_cartesian_enabled = true _optimizer_starplan_enabled = true _extended_pruning_enabled = true _optimizer_push_pred_cost_based = true _sql_model_unfold_forloops = run_time _enable_dml_lock_escalation = false _bloom_filter_enabled = true _update_bji_ipdml_enabled = 0 _optimizer_extended_cursor_sharing = udo _dm_max_shared_pool_pct = 1 _optimizer_cost_hjsmj_multimatch = true _optimizer_transitivity_retain = true _px_pwg_enabled = true optimizer_secure_view_merging = true _optimizer_join_elimination_enabled = true flashback_table_rpi = non_fbt _optimizer_cbqt_no_size_restriction = true _optimizer_enhanced_filter_push = true _optimizer_filter_pred_pullup = true _rowsrc_trace_level = 0 _simple_view_merging = true _optimizer_rownum_pred_based_fkr = true _optimizer_better_inlist_costing = all _optimizer_self_induced_cache_cost = false _optimizer_min_cache_blocks = 10 _optimizer_or_expansion = depth _optimizer_order_by_elimination_enabled = true _optimizer_outer_to_anti_enabled = true _selfjoin_mv_duplicates = true _dimension_skip_null = true _force_rewrite_enable = false _optimizer_star_tran_in_with_clause = true _optimizer_complex_pred_selectivity = true _optimizer_connect_by_cost_based = false _gby_hash_aggregation_enabled = true _globalindex_pnum_filter_enabled = false _fix_control_key = 0 _optimizer_skip_scan_guess = false _enable_row_shipping = false
statistics_level is actually set at TYPICAL, but is adjusted to ALL for the run.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Tue May 08 2007 - 06:35:12 CDT
 |
 |