Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Slow SQL, too many logical reads ?

Re: Slow SQL, too many logical reads ?

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 8 May 2007 04:35:12 -0700
Message-ID: <1178624112.038988.189110@w5g2000hsg.googlegroups.com>


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

Original text of this message

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