Continuing from the previous post, here is one more case when adjusting optimizer_index_cost_adj may hurt you.
Setup:
drop table t1 cascade constraints purge; create table t1 (id, x, pad, constraint t1_pk primary key(id, x)) as select trunc(rownum/10) , mod(rownum, 10) , s1.text from all_source s1, all_source s2 where rownum <= 1e6; exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'for all columns size 1', cascade=>true, no_invalidate=>false) alter session set optimizer_index_cost_adj = 100; alter session set optimizer_index_caching = 0; explain plan for select * from t1 where x = :1; @xp
Here’s the plan:
Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100K| 7324K| 3076 (1)| 00:00:37 | |* 1 | TABLE ACCESS FULL| T1 | 100K| 7324K| 3076 (1)| 00:00:37 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("X"=TO_NUMBER(:1))
Let’s now do same thing as previously (i.e. capture query cost depending on different OICA/OIC settings using OPT_PARAM hint) but using a very unusual execution path – index skip scan of T1_PK:
delete plan_table; commit; set serveroutput on declare l_query varchar2(4000); begin for oica in 0..10 loop for oic in 0..10 loop l_query := 'explain plan set statement_id = ''' || oica*10 || ',' || oic*10 || ''' for ' || 'select /*+ index_ss(t1 t1_pk) ' || ' opt_param(''optimizer_index_cost_adj'' ' || oica*10 || ') ' || ' opt_param(''optimizer_index_caching'' ' || oic*10 || ') */ * ' || ' from t1 ' || ' where x = :1'; -- dbms_output.put_line(l_query); execute immediate l_query; end loop; end loop; end; / set numwidth 7 prompt Query cost in case of heap-organized table depending on OICA/OIC select * from (select to_number(substr(statement_id, 1, instr(statement_id, ',') - 1)) oica ,to_number(substr(statement_id, instr(statement_id, ',') + 1)) oic ,io_cost from plan_table where id = 0) pivot(max(io_cost) for oic in (0 as oic_0 ,10 as oic_10 ,20 as oic_20 ,30 as oic_30 ,40 as oic_40 ,50 as oic_50 ,60 as oic_60 ,70 as oic_70 ,80 as oic_80 ,90 as oic_90 ,100 as oic_100)) order by oica desc;
This sounds like a very unusual plan. After all, I have 100K distinct values out of total of 1M rows, meaning skip scan would need to run 100K range scans – and this is just too much. Here is a table of query cost depending on the OICA/OIC:
OICA OIC_0 OIC_10 OIC_20 OIC_30 OIC_40 OIC_50 OIC_60 OIC_70 OIC_80 OIC_90 OIC_100 ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- 100 3598 3598 3598 3598 3598 3598 3598 3598 3598 3598 3598 90 3238 3238 3238 3238 3238 3238 3238 3238 3238 3238 3238 80 2878 2878 2878 2878 2878 2878 2878 2878 2878 2878 2878 70 2519 2519 2519 2519 2519 2519 2519 2519 2519 2519 2519 60 2159 2159 2159 2159 2159 2159 2159 2159 2159 2159 2159 50 1799 1799 1799 1799 1799 1799 1799 1799 1799 1799 1799 40 1439 1439 1439 1439 1439 1439 1439 1439 1439 1439 1439 30 1079 1079 1079 1079 1079 1079 1079 1079 1079 1079 1079 20 720 720 720 720 720 720 720 720 720 720 720 10 360 360 360 360 360 360 360 360 360 360 360 0 1 1 1 1 1 1 1 1 1 1 1 11 rows selected.
As long as OPTIMIZER_INDEX_COST_ADJ is changing, index skip scan cost is also changing – unexpectedly.
This feature was introduced in 10.2.0.5; I was unable to turn it off with _fix_control though:
SQL> @bug "skip scan" Opt BUGNO VALUE SQL_FEATURE DESCRIPTION features ---------- ----- ---------------------------------------- ---------------------------------------------------------------- ---------- 6070954 1 QKSFM_ACCESS_PATH_6070954 No skip scan with contiguous leading equality index keys 10.2.0.4 5714944 1 QKSFM_ACCESS_PATH_5714944 set IO cost for index skip scan to at least 1.0 10.2.0.5 7272039 1 QKSFM_ACCESS_PATH_7272039 use index cost adj when comparing skip scan with full table scan 10.2.0.5 6086930 1 QKSFM_ACCESS_PATH_6086930 correct skip scan selectivity evaluation for BETWEEN predicate 11.2.0.2 7277732 1 QKSFM_CBO_7277732 allow skip scan costing for NL with non-join predicate 11.2.0.2 8855396 1 QKSFM_ACCESS_PATH_8855396 sanity check for skip scan costing 11.2.0.2 8893626 1 QKSFM_ACCESS_PATH_8893626 apply index filter selectivity during skip scan costing 11.2.0.2 9195582 1 QKSFM_ACCESS_PATH_9195582 leaf blocks as upper limit for skip scan blocks 11.2.0.2 4904838 1 QKSFM_CBO_4904838 allow index skip scan with no index keys 9.2.0.8 9 rows selected.
12.1.0.1 reports similar numbers:
OICA OIC_0 OIC_10 OIC_20 OIC_30 OIC_40 OIC_50 OIC_60 OIC_70 OIC_80 OIC_90 OIC_100 ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- 100 3205 3205 3205 3205 3205 3205 3205 3205 3205 3205 3205 90 2885 2885 2885 2885 2885 2885 2885 2885 2885 2885 2885 80 2564 2564 2564 2564 2564 2564 2564 2564 2564 2564 2564 70 2244 2244 2244 2244 2244 2244 2244 2244 2244 2244 2244 60 1923 1923 1923 1923 1923 1923 1923 1923 1923 1923 1923 50 1603 1603 1603 1603 1603 1603 1603 1603 1603 1603 1603 40 1282 1282 1282 1282 1282 1282 1282 1282 1282 1282 1282 30 962 962 962 962 962 962 962 962 962 962 962 20 641 641 641 641 641 641 641 641 641 641 641 10 321 321 321 321 321 321 321 321 321 321 321 0 1 1 1 1 1 1 1 1 1 1 1
Recent comments
1 year 45 weeks ago
2 years 5 weeks ago
2 years 9 weeks ago
2 years 10 weeks ago
2 years 15 weeks ago
2 years 36 weeks ago
3 years 4 weeks ago
3 years 34 weeks ago
4 years 18 weeks ago
4 years 18 weeks ago