Sunday, March 29, 2009

Plan stability in 10g - using existing cursors to create Stored Outlines and SQL profiles

Update Jan 2011: Since this post is still among the most popular ones of this blog although being almost two years old it is probably worth an update.

- The most important information that you need to be aware of if you plan to use SQL Profiles is that you need an Enterprise Edition + Diagnostic Pack + Tuning Pack license. If you don't have these licenses you are not allowed to use SQL Profiles, or the other way around Oracle can claim you need to pay these licenses if you're going to use SQL Profiles.

Therefore if you're already on Oracle 11g you might want to use SQL Baselines instead for the same purpose - they seem to be available in all Editions and don't require any further licenses.

Update August 2012: SQL Baselines are only available with Enterprise Edition.

Jonathan Lewis for example recently wrote a short note on how to apply a baseline from a hinted statement to a non-hinted, which is what you usually want to achieve when dealing with third-party applications where you can't modify the source code.

More details about SQL Baselines can be found in the documentation, Kerry Osborne for example has some more examples and quirks he found summarized in his post about SQL Baselines.

If you're not yet on 11g and therefore can't use SQL Baselines you can still use Stored Outlines instead of SQL Profiles if you don't have the licences mentioned - as shown below the DBMS_OUTLN.CREATE_OUTLINE procedure unfortunately doesn't always work as expected. Therefore you can try to "hack" a Stored Outline as for example demonstrated by Charles Hooper here. His post also contains references to other sources on My Oracle Support and by Jonathan Lewis that describe that technique in more detail.

- I'm a bit puzzled that this post is such popular. I spend a significant amount of my time on performance related issues but I rarely resort to the techniques described here and the other mentioned posts about "Plan Stability". So I'm a bit curious and would like to encourage readers to leave a comment here why they think they need to use "Plan Stability" - there are usually a lot of others options I would evaluate first before thinking about using some kind of Plan Stability.

Original post: If you have the need for plan stability - that is telling the database to use a particular execution plan no matter what the optimizer thinks otherwise - then you might be in the situation that the "good" execution plan is already available in the shared pool or in the AWR, so it would be handy if you could simply tell Oracle to use that particular execution plan to create a Stored Outline.

Note that in 11g this is all possible using the new SQL Plan Management framework (SPM), but that is not available in 10g, so we need to think differently.

In 10g the DBMS_OUTLN package has been enhanced with the CREATE_OUTLINE procedure to create an outline from an existing child cursor in the shared pool.

Please note that in releases prior to 10.2.0.4 there was a severe bug that caused your session to crash when using DBMS_OUTLN.CREATE_OUTLINE (Bug 5454975 which has been fixed in 10.2.0.4). The workaround is to enable the creation of stored outlines by issuing "alter session set create_stored_outlines = true;" before using DBMS_OUTLN.CREATE_OUTLINE. For more information see the Metalink Notes 463288.1 and 445126.1.

Note that from 10g on the hints required to create an outline are stored as part of the plan table in the OTHER_XML column as part of the XML detail information.

You can use the ADVANCED or OUTLINE option of the DBMS_XPLAN.DISPLAY* functions to display that OUTLINE information. For more information see e.g. here.

So let's try DBMS_OUTLN.CREATE_OUTLINE in 10.2.0.4:

SQL> SQL> drop table t_fetch_first_rows purge; Table dropped. SQL> SQL> create table t_fetch_first_rows ( 2 id number not null, 3 name varchar2(30) not null, 4 type varchar2(30) not null, 5 measure number 6 ); Table created. SQL> SQL> create index idx_fetch_first_rows on t_fetch_first_rows (type, id); Index created. SQL> SQL> -- create an empty table SQL> -- and gather statistics on it SQL> exec dbms_stats.gather_table_stats(null, 't_fetch_first_rows', no_invalidate=>true) PL/SQL procedure successfully completed. SQL> SQL> -- now put in some data SQL> insert /*+ append */ into t_fetch_first_rows ( 2 id, 3 name, 4 type, 5 measure) 6 select object_id, object_name, object_type, object_id as measure 7 from all_objects, (select level as id from dual connect by level <= 1000) dup 8 where object_type in ('VIEW', 'SCHEDULE') 9 and rownum <= 1000; 1000 rows created. SQL> SQL> commit; Commit complete. SQL> SQL> -- This is going to use SQL> -- the wrong plan SQL> -- that we - only for demonstration purposes - SQL> -- attempt to keep now SQL> select sum(measure), count(*) from ( 2 select * from t_fetch_first_rows 3 where type = 'VIEW' 4 order by id 5 ); SUM(MEASURE) COUNT(*) ------------ ---------- 900000 1000 SQL> SQL> -- uses index SQL> select * from table(dbms_xplan.display_cursor(null, null, 'OUTLINE')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------- SQL_ID c2trqja6wh561, child number 0 ------------------------------------- select sum(measure), count(*) from ( select * from t_fetch_first_rows where type = 'VIEW' order by id ) Plan hash value: 1903859112 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 1 (100)| | 1 | SORT AGGREGATE | | 1 | 43 | | | 2 | TABLE ACCESS BY INDEX ROWID| T_FETCH_FIRST_ROWS | 1 | 43 | 0 (0)| |* 3 | INDEX RANGE SCAN | IDX_FETCH_FIRST_ROWS | 1 | | 0 (0)| ------------------------------------------------------------------------------------------ Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.4') OPT_PARAM('query_rewrite_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$51F12574") MERGE(@"SEL$73523A42") OUTLINE(@"SEL$1") OUTLINE(@"SEL$73523A42") ELIMINATE_OBY(@"SEL$2") OUTLINE(@"SEL$2") INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2" ("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRST_ROWS"."ID")) END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("TYPE"='VIEW') 41 rows selected. SQL> SQL> -- now gather statistics again SQL> -- on table with data SQL> exec dbms_stats.gather_table_stats(null, 't_fetch_first_rows', no_invalidate=>true) PL/SQL procedure successfully completed. SQL> SQL> explain plan 2 for 3 select sum(measure), count(*) from ( 4 select * from t_fetch_first_rows 5 where type = 'VIEW' 6 order by id 7 ); Explained. SQL> SQL> -- now the EXPLAIN PLAN tells us SQL> -- full table scan SQL> select * from table(dbms_xplan.display(null, null, 'OUTLINE')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2125410158 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 11 | | | |* 2 | TABLE ACCESS FULL| T_FETCH_FIRST_ROWS | 1000 | 11000 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2") OUTLINE(@"SEL$2") ELIMINATE_OBY(@"SEL$2") OUTLINE(@"SEL$73523A42") OUTLINE(@"SEL$1") MERGE(@"SEL$73523A42") OUTLINE_LEAF(@"SEL$51F12574") ALL_ROWS OPT_PARAM('query_rewrite_enabled' 'false') OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("TYPE"='VIEW') 33 rows selected. SQL> SQL> -- These are the hints SQL> -- stored in the child cursor SQL> -- in the shared pool SQL> -- It clearly shows an index access SQL> select 2 substr(extractvalue(value(d), '/hint'), 1, 100) as outline_hints 3 from 4 xmltable('/*/outline_data/hint' 5 passing ( 6 select 7 xmltype(other_xml) as xmlval 8 from 9 v$sql_plan 10 where 11 hash_value = 2378699969 12 and child_number = 0 13 and other_xml is not null 14 ) 15 ) d; OUTLINE_HINTS ---------------------------------------------------------------------------------------------------- IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.4') OPT_PARAM('query_rewrite_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$51F12574") MERGE(@"SEL$73523A42") OUTLINE(@"SEL$1") OUTLINE(@"SEL$73523A42") ELIMINATE_OBY(@"SEL$2") OUTLINE(@"SEL$2") INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2" ("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRS 11 rows selected. SQL> SQL> -- Create the outline based on that cursor SQL> exec dbms_outln.create_outline(2378699969, 0, 'TEST') PL/SQL procedure successfully completed. SQL> SQL> -- Oops, where is my index scan gone? SQL> select substr(hint, 1, 100) as hint from user_outline_hints; HINT -------------------------------------------------------------------------------- FULL(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2") OUTLINE(@"SEL$2") ELIMINATE_OBY(@"SEL$2") OUTLINE(@"SEL$73523A42") OUTLINE(@"SEL$1") MERGE(@"SEL$73523A42") OUTLINE_LEAF(@"SEL$51F12574") ALL_ROWS OPT_PARAM('query_rewrite_enabled' 'false') OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS 11 rows selected. SQL> SQL> -- Use the outline SQL> alter session set use_stored_outlines = TEST; Session altered. SQL> SQL> explain plan 2 for 3 select sum(measure), count(*) from ( 4 select * from t_fetch_first_rows 5 where type = 'VIEW' 6 order by id 7 ); Explained. SQL> SQL> -- Uses outline (see Note section) SQL> -- but full table scan SQL> -- So that didn't work as expected SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2125410158 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 11 | | | |* 2 | TABLE ACCESS FULL| T_FETCH_FIRST_ROWS | 1000 | 11000 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("TYPE"='VIEW') Note ----- - outline "SYS_OUTLINE_09032900314557403" used for this statement 18 rows selected. SQL> SQL> alter session set use_stored_outlines = false; Session altered. SQL> SQL> -- drop the outline SQL> declare 2 outline_name varchar2(30); 3 begin 4 select 5 name 6 into 7 outline_name 8 from 9 user_outlines 10 where 11 category = 'TEST'; 12 13 execute immediate 'drop outline ' || outline_name; 14 end; 15 / PL/SQL procedure successfully completed. SQL> SQL> -- This is the plan SQL> -- we get based on the present statistics SQL> explain plan 2 for 3 select sum(measure), count(*) from ( 4 select * from t_fetch_first_rows 5 where type = 'VIEW' 6 order by id 7 ); Explained. SQL> SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2125410158 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 11 | | | |* 2 | TABLE ACCESS FULL| T_FETCH_FIRST_ROWS | 1000 | 11000 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("TYPE"='VIEW') 14 rows selected. SQL> SQL> spool off

So that didn't work as expected. Although we were able to create an outline from the child cursor, it obviously didn't use the plan associated with the child cursor. Tracing the session didn't reveal why the CREATE_OUTLINE didn't use the outline information available from the shared pool.

Running the same test case in a slightly different order so that the outline is created before the statistics change corroborates the theory that the DBMS_OUTLN.CREATE_OUTLINE procedure might take the SQL from the cursor and internally execute an CREATE OUTLINE ... ON ..., and for whatever reason doesn't use the already available outline information.

SQL> SQL> drop table t_fetch_first_rows purge; Table dropped. SQL> SQL> create table t_fetch_first_rows ( 2 id number not null, 3 name varchar2(30) not null, 4 type varchar2(30) not null, 5 measure number 6 ); Table created. SQL> SQL> create index idx_fetch_first_rows on t_fetch_first_rows (type, id); Index created. SQL> SQL> -- create an empty table SQL> -- and gather statistics on it SQL> exec dbms_stats.gather_table_stats(null, 't_fetch_first_rows', no_invalidate=>true) PL/SQL procedure successfully completed. SQL> SQL> -- now put in some data SQL> insert /*+ append */ into t_fetch_first_rows ( 2 id, 3 name, 4 type, 5 measure) 6 select object_id, object_name, object_type, object_id as measure 7 from all_objects, (select level as id from dual connect by level <= 1000) dup 8 where object_type in ('VIEW', 'SCHEDULE') 9 and rownum <= 1000; 1000 rows created. SQL> SQL> commit; Commit complete. SQL> SQL> -- This is going to use SQL> -- the wrong plan SQL> -- that we - only for demonstration purposes - SQL> -- attempt to keep now SQL> select sum(measure), count(*) from ( 2 select * from t_fetch_first_rows 3 where type = 'VIEW' 4 order by id 5 ); SUM(MEASURE) COUNT(*) ------------ ---------- 900000 1000 SQL> SQL> -- uses index SQL> select * from table(dbms_xplan.display_cursor(null, null, 'OUTLINE')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------- SQL_ID c2trqja6wh561, child number 0 ------------------------------------- select sum(measure), count(*) from ( select * from t_fetch_first_rows where type = 'VIEW' order by id ) Plan hash value: 1903859112 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 1 (100)| | 1 | SORT AGGREGATE | | 1 | 43 | | | 2 | TABLE ACCESS BY INDEX ROWID| T_FETCH_FIRST_ROWS | 1 | 43 | 0 (0)| |* 3 | INDEX RANGE SCAN | IDX_FETCH_FIRST_ROWS | 1 | | 0 (0)| ------------------------------------------------------------------------------------------ Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.4') OPT_PARAM('query_rewrite_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$51F12574") MERGE(@"SEL$73523A42") OUTLINE(@"SEL$1") OUTLINE(@"SEL$73523A42") ELIMINATE_OBY(@"SEL$2") OUTLINE(@"SEL$2") INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2" ("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRST_ROWS"."ID")) END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("TYPE"='VIEW') 41 rows selected. SQL> SQL> -- Create the outline based on that cursor SQL> exec dbms_outln.create_outline(2378699969, 0, 'TEST') PL/SQL procedure successfully completed. SQL> SQL> -- Now we have the index scan in the outline SQL> select substr(hint, 1, 100) as hint from user_outline_hints; HINT -------------------------------------------------------------------------------- INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2" ("T_FETCH_FIRST_ROWS". OUTLINE(@"SEL$2") ELIMINATE_OBY(@"SEL$2") OUTLINE(@"SEL$73523A42") OUTLINE(@"SEL$1") MERGE(@"SEL$73523A42") OUTLINE_LEAF(@"SEL$51F12574") ALL_ROWS OPT_PARAM('query_rewrite_enabled' 'false') OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS 11 rows selected. SQL> SQL> -- now gather statistics again SQL> -- on table with data SQL> exec dbms_stats.gather_table_stats(null, 't_fetch_first_rows', no_invalidate=>true) PL/SQL procedure successfully completed. SQL> SQL> explain plan 2 for 3 select sum(measure), count(*) from ( 4 select * from t_fetch_first_rows 5 where type = 'VIEW' 6 order by id 7 ); Explained. SQL> SQL> -- now the EXPLAIN PLAN tells us SQL> -- full table scan SQL> select * from table(dbms_xplan.display(null, null, 'OUTLINE')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2125410158 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 11 | | | |* 2 | TABLE ACCESS FULL| T_FETCH_FIRST_ROWS | 1000 | 11000 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2") OUTLINE(@"SEL$2") ELIMINATE_OBY(@"SEL$2") OUTLINE(@"SEL$73523A42") OUTLINE(@"SEL$1") MERGE(@"SEL$73523A42") OUTLINE_LEAF(@"SEL$51F12574") ALL_ROWS OPT_PARAM('query_rewrite_enabled' 'false') OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("TYPE"='VIEW') 33 rows selected. SQL> SQL> -- These are the hints SQL> -- stored in the child cursor SQL> -- in the shared pool SQL> -- It clearly shows an index access SQL> select 2 substr(extractvalue(value(d), '/hint'), 1, 100) as outline_hints 3 from 4 xmltable('/*/outline_data/hint' 5 passing ( 6 select 7 xmltype(other_xml) as xmlval 8 from 9 v$sql_plan 10 where 11 hash_value = 2378699969 12 and child_number = 0 13 and other_xml is not null 14 ) 15 ) d; OUTLINE_HINTS ---------------------------------------------------------------------------------------------------- IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.4') OPT_PARAM('query_rewrite_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$51F12574") MERGE(@"SEL$73523A42") OUTLINE(@"SEL$1") OUTLINE(@"SEL$73523A42") ELIMINATE_OBY(@"SEL$2") OUTLINE(@"SEL$2") INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2" ("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRS 11 rows selected. SQL> SQL> -- Use the outline SQL> alter session set use_stored_outlines = TEST; Session altered. SQL> SQL> explain plan 2 for 3 select sum(measure), count(*) from ( 4 select * from t_fetch_first_rows 5 where type = 'VIEW' 6 order by id 7 ); Explained. SQL> SQL> -- Uses outline (see Note section) SQL> -- this time correctly SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1903859112 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 9 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 11 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T_FETCH_FIRST_ROWS | 1000 | 11000 | 9 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX_FETCH_FIRST_ROWS | 1000 | | 4 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("TYPE"='VIEW') Note ----- - outline "SYS_OUTLINE_09032900320095604" used for this statement 19 rows selected. SQL> SQL> alter session set use_stored_outlines = false; Session altered. SQL> SQL> -- drop the outline SQL> declare 2 outline_name varchar2(30); 3 begin 4 select 5 name 6 into 7 outline_name 8 from 9 user_outlines 10 where 11 category = 'TEST'; 12 13 execute immediate 'drop outline ' || outline_name; 14 end; 15 / PL/SQL procedure successfully completed. SQL> SQL> -- This is the plan SQL> -- we get based on the present statistics SQL> explain plan 2 for 3 select sum(measure), count(*) from ( 4 select * from t_fetch_first_rows 5 where type = 'VIEW' 6 order by id 7 ); Explained. SQL> SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2125410158 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 11 | | | |* 2 | TABLE ACCESS FULL| T_FETCH_FIRST_ROWS | 1000 | 11000 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("TYPE"='VIEW') 14 rows selected. SQL> SQL> spool off

So that worked, but still the question remains why DBMS_OUTLN.CREATE_OUTLINE doesn't use the available outline information in the shared pool.

Now let's turn to a different approach to achieve the same. 10g introduced SQL profiles that are primarily used to amend information that is not available to the cost based optimizer, e.g. in case of correlated column values the SQL Tuning Advisor of 10g can suggest to accept a SQL profile that scales the cardinality estimate so that the cardinality estimate is in the right ballpark.

A good explanation of SQL profiles can be found in Christian Antognini's publications.

But since SQL profiles internally consist of a set of hints, it could be possible to use SQL profiles instead of Stored Outlines to achieve the same.

There are two interesting aspects regarding this approach:

- We could use different sources to get the outline, e.g. instead of the shared pool we could get the hints from the AWR tables.

- SQL profiles support a "FORCE_MATCH" option that works similar to the CURSOR_SHARING literal replacement logic, i.e. SQL profiles can be forced to apply to multiple SQL statements that differ only by the literals used (i.e. no usage of bind variables).

So we are faced with two challenges in this regard:

1. Get the outline information, i.e. the full set of hints to provide plan stability 2. Create a SQL profile that consists of these hints

Get the outline information

There are two ways how the outline information could be obtained:

a) Use the DBMS_XPLAN.DISPLAY* functions with the ADVANCED or OUTLINE option and parse the this output to get the set of hints

b) Directly query the underlying tables/views to get the XML stored in the OTHER_XML column and extract the hints from that XML

a) Use the DBMS_XPLAN.DISPLAY* functions

Let me digress a little bit. Looking at the (already parsed a bit) output we get from the official DBMS_XPLAN function:

SQL> SQL> with a as ( 2 select 3 rownum as r_no 4 , a.* 5 from 6 table( 7 dbms_xplan.display_cursor( 8 'c2trqja6wh561' 9 , 0 10 , 'OUTLINE' 11 ) 12 ) a 13 ), 14 b as ( 15 select 16 min(r_no) as start_r_no 17 from 18 a 19 where 20 a.plan_table_output = 'Outline Data' 21 ), 22 c as ( 23 select 24 min(r_no) as end_r_no 25 from 26 a 27 , b 28 where 29 a.r_no > b.start_r_no 30 and a.plan_table_output = ' */' 31 ), 32 d as ( 33 select 34 instr(a.plan_table_output, 'BEGIN_OUTLINE_DATA') as start_col 35 from 36 a 37 , b 38 where 39 r_no = b.start_r_no + 4 40 ) 41 select 42 substr(a.plan_table_output, d.start_col) as outline_hints 43 from 44 a 45 , b 46 , c 47 , d 48 where 49 a.r_no >= b.start_r_no + 4 50 and a.r_no <= c.end_r_no - 1 51 order by 52 a.r_no; OUTLINE_HINTS -------------------------------------------------------------------------- BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.4') OPT_PARAM('query_rewrite_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$51F12574") MERGE(@"SEL$73523A42") OUTLINE(@"SEL$1") OUTLINE(@"SEL$73523A42") ELIMINATE_OBY(@"SEL$2") OUTLINE(@"SEL$2") INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2" ("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRST_ROWS"."ID")) END_OUTLINE_DATA 14 rows selected. SQL>

You'll notice that the INDEX_RS_ASC hint is split across two lines, so we can't simply use that query output to construct the hints because these hints would be potentially illegal and therefore we need to merge/concatenate these split lines.

This is a variation of the well known "columns-to-rows" aka. STRAGG/CONCAT issue and there are multiple ways how to deal with that using plain SQL.

For more information about this particular issue, see e.g. the SQL snippets site.

Here are two ways how to achieve that concatenation using hierarchical queries or the SQL MODEL clause introduced in 10g:

SQL> SQL> with a as ( 2 select 3 rownum as r_no 4 , a.* 5 from 6 table( 7 dbms_xplan.display_cursor( 8 'c2trqja6wh561' 9 , 0 10 , 'OUTLINE' 11 ) 12 ) a 13 ), 14 b as ( 15 select 16 min(r_no) as start_r_no 17 from 18 a 19 where 20 a.plan_table_output = 'Outline Data' 21 ), 22 c as ( 23 select 24 min(r_no) as end_r_no 25 from 26 a 27 , b 28 where 29 a.r_no > b.start_r_no 30 and a.plan_table_output = ' */' 31 ), 32 d as ( 33 select 34 instr(a.plan_table_output, 'BEGIN_OUTLINE_DATA') as start_col 35 from 36 a 37 , b 38 where 39 r_no = b.start_r_no + 4 40 ), 41 e as ( 42 select a.r_no 43 , substr(a.plan_table_output, d.start_col) as outline_hints 44 from 45 a 46 , b 47 , c 48 , d 49 where 50 a.r_no >= b.start_r_no + 4 51 and a.r_no <= c.end_r_no - 1 52 order by 53 a.r_no 54 ), 55 f as ( 56 select 57 case substr(e.outline_hints, 1, 1) 58 when ' ' 59 then r_no - 1 60 else null 61 end as par_id, 62 e.* 63 from 64 e 65 ) 66 select 67 replace(aggr,'|', '') as aggr 68 from ( 69 select 70 par_id 71 , sys_connect_by_path(trim(outline_hints), '|') as aggr 72 , level as lvl 73 from 74 f 75 where 76 connect_by_isleaf = 1 77 start with 78 par_id is null 79 connect by 80 prior r_no = par_id 81 order siblings by 82 r_no 83 ); AGGR ------------------------------------------------------------------------------------------------------------- BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.4') OPT_PARAM('query_rewrite_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$51F12574") MERGE(@"SEL$73523A42") OUTLINE(@"SEL$1") OUTLINE(@"SEL$73523A42") ELIMINATE_OBY(@"SEL$2") OUTLINE(@"SEL$2") INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2"("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRST_ROWS"."ID")) END_OUTLINE_DATA 13 rows selected. SQL>

And here using the MODEL clause:

SQL> SQL> with a as ( 2 select 3 rownum as r_no 4 , a.* 5 from 6 table( 7 dbms_xplan.display_cursor( 8 'c2trqja6wh561' 9 , 0 10 , 'OUTLINE' 11 ) 12 ) a 13 ), 14 b as ( 15 select 16 min(r_no) as start_r_no 17 from 18 a 19 where 20 a.plan_table_output = 'Outline Data' 21 ), 22 c as ( 23 select 24 min(r_no) as end_r_no 25 from 26 a 27 , b 28 where 29 a.r_no > b.start_r_no 30 and a.plan_table_output = ' */' 31 ), 32 d as ( 33 select 34 instr(a.plan_table_output, 'BEGIN_OUTLINE_DATA') as start_col 35 from 36 a 37 , b 38 where 39 r_no = b.start_r_no + 4 40 ), 41 e as ( 42 select 43 a.r_no 44 , substr(a.plan_table_output, d.start_col) as outline_hints 45 from 46 a 47 , b 48 , c 49 , d 50 where 51 a.r_no >= b.start_r_no + 4 52 and a.r_no <= c.end_r_no - 1 53 ), 54 f as ( 55 select 56 case substr(e.outline_hints, 1, 1) 57 when ' ' 58 then null 59 else r_no 60 end as grp_id 61 , e.* 62 from 63 e 64 ), 65 g as ( 66 select 67 case 68 when grp_id is null 69 then last_value(grp_id ignore nulls) over (order by r_no) 70 else null 71 end as par_id 72 , f.* 73 from 74 f 75 ) 76 select 77 aggr 78 from 79 g 80 model 81 return updated rows 82 partition by ( 83 nvl(grp_id, par_id) as grp 84 ) 85 dimension by ( 86 row_number() over ( 87 partition by 88 nvl(grp_id, par_id) 89 order by 90 r_no 91 ) as rn 92 ) 93 measures ( 94 cast(outline_hints as varchar2(4000)) as aggr 95 , r_no 96 ) 97 rules 98 iterate (1000) 99 until presentv(aggr[ITERATION_NUMBER+3],1,2)=2 ( 100 aggr[1] = aggr[1] || 101 trim(aggr[ITERATION_NUMBER+2]) 102 ) 103 order by r_no 104 ; AGGR ---------------------------------------------------------------------------------------------------------------------------------- BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.4') OPT_PARAM('query_rewrite_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$51F12574") MERGE(@"SEL$73523A42") OUTLINE(@"SEL$1") OUTLINE(@"SEL$73523A42") ELIMINATE_OBY(@"SEL$2") OUTLINE(@"SEL$2") INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2" ("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRST_ROWS"."ID")) END_OUTLINE_DATA 13 rows selected. SQL>

b) Directly query the underlying tables/views to get the XML

The other option would be to query the respective tables/views directly to obtain the hints from the XML stored in the OTHER_XML column of execution plans.

Here we can use the powerful XML functions of Oracle 10g:

SQL> SQL> select 2 extractvalue(value(d), '/hint') as outline_hints 3 from 4 xmltable('/*/outline_data/hint' 5 passing ( 6 select 7 xmltype(other_xml) as xmlval 8 from 9 v$sql_plan 10 where 11 sql_id = 'c2trqja6wh561' 12 and child_number = 0 13 and other_xml is not null 14 ) 15 ) d; OUTLINE_HINTS ------------------------------------------------------------------------------------------------------------- IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.4') OPT_PARAM('query_rewrite_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$51F12574") MERGE(@"SEL$73523A42") OUTLINE(@"SEL$1") OUTLINE(@"SEL$73523A42") ELIMINATE_OBY(@"SEL$2") OUTLINE(@"SEL$2") INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2" ("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRST_ROWS"."ID")) 11 rows selected. SQL>

Instead of V$SQL_PLAN/V$SQL we could e.g. use DBA_HIST_SQL_PLAN/DBA_HIST_SQLTEXT to obtain the outline information from the AWR.

Create a SQL profile that consists of these hints

Now the second challenge is how to generate a SQL profile once we have identified the hints to use.

Here comes the DBMS_SQLTUNE package into the picture. It offers an (not officially documented) procedure IMPORT_SQL_PROFILE that is obviously used by the import facilities to create SQL profiles.

-- NAME: import_sql_profile - import a SQL profile -- PURPOSE: This procedure is only used by import. -- INPUTS: (see accept_sql_profile) -- REQUIRES: "CREATE ANY SQL PROFILE" privilege -- PROCEDURE import_sql_profile( sql_text IN CLOB, profile IN sqlprof_attr, name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, category IN VARCHAR2 := NULL, validate IN BOOLEAN := TRUE, replace IN BOOLEAN := FALSE, force_match IN BOOLEAN := FALSE);

It simply takes a collection of varchar2(500) strings that make up the profile.

So we can combine the two things into a procedure that generates us a SQL profile from either the shared pool or the AWR. Here's one for the shared pool. It takes four parameters: The SQL_ID, the child_number, the SQL profile category and whether to force a match or not.

declare ar_profile_hints sys.sqlprof_attr; cl_sql_text clob; begin select extractvalue(value(d), '/hint') as outline_hints bulk collect into ar_profile_hints from xmltable('/*/outline_data/hint' passing ( select xmltype(other_xml) as xmlval from v$sql_plan where sql_id = '&&1' and child_number = &&2 and other_xml is not null ) ) d; select sql_fulltext into cl_sql_text from v$sql where sql_id = '&&1' and child_number = &&2; dbms_sqltune.import_sql_profile( sql_text => cl_sql_text , profile => ar_profile_hints , category => '&&3' , name => 'PROFILE_&&1' -- use force_match => true -- to use CURSOR_SHARING=SIMILAR -- behaviour, i.e. match even with -- differing literals , force_match => &&4 ); end; /

Here's the one for the AWR. It takes as parameter the SQL_ID, the PLAN_HASH_VALUE and like the first one the SQL profile category and the FORCE_MATCH option.

declare ar_profile_hints sys.sqlprof_attr; cl_sql_text clob; begin select extractvalue(value(d), '/hint') as outline_hints bulk collect into ar_profile_hints from xmltable('/*/outline_data/hint' passing ( select xmltype(other_xml) as xmlval from dba_hist_sql_plan where sql_id = '&&1' and plan_hash_value = &&2 and other_xml is not null ) ) d; select sql_text into cl_sql_text from dba_hist_sqltext where sql_id = '&&1'; dbms_sqltune.import_sql_profile( sql_text => cl_sql_text , profile => ar_profile_hints , category => '&&3' , name => 'PROFILE_&&1' -- use force_match => true -- to use CURSOR_SHARING=SIMILAR -- behaviour, i.e. match even with -- differing literals , force_match => &&4 ); end; /

So let's try all the stuff in one shot:

SQL> SQL> drop table t_fetch_first_rows purge; Table dropped. SQL> SQL> create table t_fetch_first_rows ( 2 id number not null, 3 name varchar2(30) not null, 4 type varchar2(30) not null, 5 measure number 6 ); Table created. SQL> SQL> create index idx_fetch_first_rows on t_fetch_first_rows (type, id); Index created. SQL> SQL> -- create an empty table SQL> -- and gather statistics on it SQL> exec dbms_stats.gather_table_stats(null, 't_fetch_first_rows', no_invalidate=>true) PL/SQL procedure successfully completed. SQL> SQL> -- now put in some data SQL> insert /*+ append */ into t_fetch_first_rows ( 2 id, 3 name, 4 type, 5 measure) 6 select object_id, object_name, object_type, object_id as measure 7 from all_objects, (select level as id from dual connect by level <= 1000) dup 8 where object_type in ('VIEW', 'SCHEDULE') 9 and rownum <= 1000; 1000 rows created. SQL> SQL> commit; Commit complete. SQL> SQL> -- This is going to use SQL> -- the wrong plan SQL> -- that we - only for demonstration purposes - SQL> -- attempt to keep now SQL> select sum(measure), count(*) from ( 2 select * from t_fetch_first_rows 3 where type = 'VIEW' 4 order by id 5 ); SUM(MEASURE) COUNT(*) ------------ ---------- 900000 1000 SQL> SQL> -- uses index SQL> select * from table(dbms_xplan.display_cursor(null, null, 'OUTLINE')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------- SQL_ID c2trqja6wh561, child number 0 ------------------------------------- select sum(measure), count(*) from ( select * from t_fetch_first_rows where type = 'VIEW' order by id ) Plan hash value: 1903859112 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 1 (100)| | 1 | SORT AGGREGATE | | 1 | 43 | | | 2 | TABLE ACCESS BY INDEX ROWID| T_FETCH_FIRST_ROWS | 1 | 43 | 0 (0)| |* 3 | INDEX RANGE SCAN | IDX_FETCH_FIRST_ROWS | 1 | | 0 (0)| ------------------------------------------------------------------------------------------ Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.4') OPT_PARAM('query_rewrite_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$51F12574") MERGE(@"SEL$73523A42") OUTLINE(@"SEL$1") OUTLINE(@"SEL$73523A42") ELIMINATE_OBY(@"SEL$2") OUTLINE(@"SEL$2") INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2" ("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRST_ROWS"."ID")) END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("TYPE"='VIEW') 41 rows selected. SQL> SQL> -- now gather statistics again SQL> -- on table with data SQL> exec dbms_stats.gather_table_stats(null, 't_fetch_first_rows', no_invalidate=>true) PL/SQL procedure successfully completed. SQL> SQL> explain plan 2 for 3 select sum(measure), count(*) from ( 4 select * from t_fetch_first_rows 5 where type = 'VIEW' 6 order by id 7 ); Explained. SQL> SQL> -- now the EXPLAIN PLAN tells us SQL> -- full table scan SQL> select * from table(dbms_xplan.display(null, null, 'OUTLINE')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2125410158 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 11 | | | |* 2 | TABLE ACCESS FULL| T_FETCH_FIRST_ROWS | 1000 | 11000 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Outline Data ------------- /*+ BEGIN_OUTLINE_DATA FULL(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2") OUTLINE(@"SEL$2") ELIMINATE_OBY(@"SEL$2") OUTLINE(@"SEL$73523A42") OUTLINE(@"SEL$1") MERGE(@"SEL$73523A42") OUTLINE_LEAF(@"SEL$51F12574") ALL_ROWS OPT_PARAM('query_rewrite_enabled' 'false') OPTIMIZER_FEATURES_ENABLE('10.2.0.4') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("TYPE"='VIEW') 33 rows selected. SQL> SQL> -- These are the hints SQL> -- stored in the child cursor SQL> -- in the shared pool SQL> -- It clearly shows an index access SQL> select 2 substr(extractvalue(value(d), '/hint'), 1, 100) as outline_hints 3 from 4 xmltable('/*/outline_data/hint' 5 passing ( 6 select 7 xmltype(other_xml) as xmlval 8 from 9 v$sql_plan 10 where 11 sql_id = 'c2trqja6wh561' 12 and child_number = 0 13 and other_xml is not null 14 ) 15 ) d; OUTLINE_HINTS ---------------------------------------------------------------------------------------------------- IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('10.2.0.4') OPT_PARAM('query_rewrite_enabled' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$51F12574") MERGE(@"SEL$73523A42") OUTLINE(@"SEL$1") OUTLINE(@"SEL$73523A42") ELIMINATE_OBY(@"SEL$2") OUTLINE(@"SEL$2") INDEX_RS_ASC(@"SEL$51F12574" "T_FETCH_FIRST_ROWS"@"SEL$2" ("T_FETCH_FIRST_ROWS"."TYPE" "T_FETCH_FIRS 11 rows selected. SQL> SQL> -- Create the SQL profile based on that cursor SQL> @create_profile_from_shared_pool c2trqja6wh561 0 TEST true SQL> declare 2 ar_profile_hints sys.sqlprof_attr; 3 cl_sql_text clob; 4 begin 5 select 6 extractvalue(value(d), '/hint') as outline_hints 7 bulk collect 8 into 9 ar_profile_hints 10 from 11 xmltable('/*/outline_data/hint' 12 passing ( 13 select 14 xmltype(other_xml) as xmlval 15 from 16 v$sql_plan 17 where 18 sql_id = '&&1' 19 and child_number = &&2 20 and other_xml is not null 21 ) 22 ) d; 23 24 select 25 sql_text 26 into 27 cl_sql_text 28 from 29 -- replace with dba_hist_sqltext 30 -- if required for AWR based 31 -- execution 32 v$sql 33 -- sys.dba_hist_sqltext 34 where 35 sql_id = '&&1' 36 and child_number = &&2; 37 -- plan_hash_value = &&2; 38 39 dbms_sqltune.import_sql_profile( 40 sql_text => cl_sql_text 41 , profile => ar_profile_hints 42 , category => '&&3' 43 , name => 'PROFILE_&&1' 44 -- use force_match => true 45 -- to use CURSOR_SHARING=SIMILAR 46 -- behaviour, i.e. match even with 47 -- differing literals 48 , force_match => &&4 49 ); 50 end; 51 / old 18: sql_id = '&&1' new 18: sql_id = 'c2trqja6wh561' old 19: and child_number = &&2 new 19: and child_number = 0 old 35: sql_id = '&&1' new 35: sql_id = 'c2trqja6wh561' old 36: and child_number = &&2; new 36: and child_number = 0; old 37: -- plan_hash_value = &&2; new 37: -- plan_hash_value = 0; old 42: , category => '&&3' new 42: , category => 'TEST' old 43: , name => 'PROFILE_&&1' new 43: , name => 'PROFILE_c2trqja6wh561' old 48: , force_match => &&4 new 48: , force_match => true PL/SQL procedure successfully completed. SQL> SQL> alter session set sqltune_category = 'TEST'; Session altered. SQL> SQL> explain plan 2 for 3 select sum(measure), count(*) from ( 4 select * from t_fetch_first_rows 5 where type = 'VIEW' 6 order by id 7 ); Explained. SQL> SQL> -- Uses SQL profile (see Note section) SQL> -- and uses index SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1903859112 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 9 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 11 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T_FETCH_FIRST_ROWS | 1000 | 11000 | 9 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX_FETCH_FIRST_ROWS | 1000 | | 4 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("TYPE"='VIEW') Note ----- - SQL profile "PROFILE_c2trqja6wh561" used for this statement 19 rows selected. SQL> SQL> explain plan 2 for 3 select sum(measure), count(*) from ( 4 select * from t_fetch_first_rows 5 where type = 'VIEW2' 6 order by id 7 ); Explained. SQL> SQL> -- Very cool: Still uses SQL profile (see Note section) SQL> -- although no exact text match SQL> -- this is not possible using Stored Outlines SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1903859112 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 11 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T_FETCH_FIRST_ROWS | 1 | 11 | 3 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | IDX_FETCH_FIRST_ROWS | 1 | | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("TYPE"='VIEW2') Note ----- - SQL profile "PROFILE_c2trqja6wh561" used for this statement 19 rows selected. SQL> SQL> alter session set sqltune_category = 'DEFAULT'; Session altered. SQL> SQL> -- drop the SQL profile SQL> exec dbms_sqltune.drop_sql_profile('PROFILE_c2trqja6wh561') PL/SQL procedure successfully completed. SQL> SQL> -- This is the plan SQL> -- we get based on the present statistics SQL> explain plan 2 for 3 select sum(measure), count(*) from ( 4 select * from t_fetch_first_rows 5 where type = 'VIEW' 6 order by id 7 ); Explained. SQL> SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2125410158 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 11 | | | |* 2 | TABLE ACCESS FULL| T_FETCH_FIRST_ROWS | 1000 | 11000 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("TYPE"='VIEW') 14 rows selected. SQL>

You can see two things here:

1. The SQL profile created forces the plan we wanted, so it seems to work as expected
2. The FORCE_MATCH option of the SQL profiles allows to use this profile even for SQLs that are not an exact text match of the original statement. This is something that is as far as I know not possible using Stored Outlines.

So if you have the need to fix the execution plan, and you have that plan already in the shared pool or the AWR, using above procedures allow you to generate a SQL profile which seems to do exactly what we want.

Given the fact that the SQL profile even allows to share the plan for SQLs that differ only by literals I definitely favor the SQL profiles over the Stored Outlines approach.

Tuesday, March 24, 2009

Optimizer partition oddities, part 2: List partitioning

Back to part 1

Some time ago on the OTN forum the following table layout was part of a discussion regarding performance issues and it revealed an interesting anomaly regarding list partition pruning:

If you're using list partitioning with partitions that use multiple values that map to a single list partition then the optimizer obviously uses a questionable approach when you're using multiple values on the partition key to prune to a single partition.

Consider the following table layout:


CREATE TABLE XYZ
(
TICKER VARCHAR2(22 BYTE) NOT NULL,
EXCH_CODE VARCHAR2(25 BYTE) NOT NULL,
ID_ISIN VARCHAR2(12 BYTE),
HIGH_52WEEK NUMBER(28,10),
LOW_52WEEK NUMBER(28,10),
PX_OPEN NUMBER(28,10),
PX_HIGH NUMBER(28,10),
BLOOMBERG_FILE_SOURCE VARCHAR2(100 BYTE),
LATEST_VERSION_FLAG CHAR(1 BYTE)
)
PARTITION BY LIST (EXCH_CODE)
(
PARTITION BBO_ASIA VALUES ('SL','IS','SP','JF','JN','PK','KP','VM','JS','IN','TB','KQ','JP','NV','JJ','MK','HK','IJ','JT','TT','PA','CS','JX',
'IB','AU','FS','VN','NZ','KS','PM','CH','BD','JQ','VH','CG','JO')
,
PARTITION BBO_NAMR VALUES ('UO','US','UN','PQ','TR','UD','UP','TX','UL','UB','UU','UX','UT','TN','UQ','UR','UW','UV','TA','CT','CV','UC','CJ','UA','UM','CN','UF','CF')
,
PARTITION BBO_LAMR VALUES ('AR','BS','AC','CR','EG','EK','VB','BN','EQ','PE','AF','CX','KY','CC','MM','BM','TP','BV','BH','UY','BZ','ED','VC','VS','BO','CI','CB','PP','BA','JA','CE')
,
PARTITION BBO_EURO VALUES (DEFAULT)
);


I'm now going to populate that table using this sample data:


declare
a sys.DBMS_DEBUG_VC2COLL := sys.DBMS_DEBUG_VC2COLL('SL','IS','SP','JF','JN','PK','KP','VM','JS','IN','TB','KQ','JP','NV','JJ','MK','HK','IJ','JT','TT','PA','CS','JX','IB','AU','FS','VN','NZ','KS','PM','CH','BD','JQ','VH','CG','JO', 'UO','US','UN','PQ','TR','UD','UP','TX','UL','UB','UU','UX','UT','TN','UQ','UR','UW','UV','TA','CT','CV','UC','CJ','UA','UM','CN','UF','CF', 'AR','BS','AC','CR','EG','EK','VB','BN','EQ','PE','AF','CX','KY','CC','MM','BM','TP','BV','BH','UY','BZ','ED','VC','VS','BO','CI','CB','PP','BA','JA','CE');
n number;
begin
dbms_random.seed(0);
n := a.count;
insert into XYZ (
ticker
, exch_code
, id_isin
, high_52week
, low_52week
, px_open
, px_high
, bloomberg_file_source
, latest_version_flag
)
with rand as (
select /*+ materialize */
level as id
, ceil(dbms_random.value(0, n)) as rand_val
from
dual
connect by
level <= 10000
)
select
dbms_random.string('A', 22) as ticker
, case when mod(rownum, 2) = 1 then 'AA' else v.exch_code end as exch_code
, dbms_random.string('A', 12) as id_isin
, dbms_random.value(0, 1000000) as high_52week
, dbms_random.value(0, 1000000) as low_52week
, dbms_random.value(0, 1000000) as px_open
, dbms_random.value(0, 1000000) as px_high
, dbms_random.string('A', 40) as bloomberg_file_source
, case when rownum >= 9960 then 'Y' else 'N' end as latest_version_flag
from
(
select
level as id
from
dual
connect by
level <= 10000
) x
, (
select
rownum as id
, value(y) as exch_code
from
table(a) y
) v
, rand
where
rand.id = x.id
and v.id = rand.rand_val
;
end;
/


The result is the following distribution:


EXCH_CODE COUNT(*)
------------------------- ----------
BN 51
BS 44
KY 57
TP 47
...
...
CR 54
JA 62
PP 37
AA 5000


As you can see the data is evenly distributed except for the default partition which holds 5,000 rows for the value 'AA'.

I'm now going to gather statistics, but deliberately only on partition level:


exec dbms_stats.gather_table_stats(null, 'XYZ', granularity=>'PARTITION')


So I'm simulating here an approach where I assume that I'm going to prune to a single partition and therefore don't need high quality global level table statistics, but gather only statistics on partition level. The global level statistics are aggregated/derived statistics.

Of course the whole approach regarding partition statistics changes with 11g and its incremental partition statistics features (see e.g. http://structureddata.org/2008/07/16/oracle-11g-incremental-global-statistics-on-partitioned-tables/), and therefore in 11g you don't suffer from the usual overhead caused by maintaining high quality global level statistics.

I'm getting the following statistics using above DBMS_STATS call:


TABLE_NAME PARTITION_NAME NUM_ROWS BLOCKS
---------- --------------- ---------- ----------
XYZ 10000 185
XYZ BBO_ASIA 1952 35
XYZ BBO_NAMR 1480 30
XYZ BBO_LAMR 1568 30
XYZ BBO_EURO 5000 90


Everything works fine if I only use a single value to prune to one of the list partitions, in this case the skewed default partition:


SQL>
SQL> explain plan for
2 SELECT
3 * from XYZ
4 WHERE exch_code IN ('AA');

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3795892923

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 590K| 26 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE| | 5000 | 590K| 26 (0)| 00:00:01 | KEY | KEY |
|* 2 | TABLE ACCESS FULL | XYZ | 5000 | 590K| 26 (0)| 00:00:01 | 4 | 4 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("EXCH_CODE"='AA')

14 rows selected.

SQL> -- non-existent value in default partition
SQL> explain plan for
2 SELECT
3 * from XYZ
4 WHERE exch_code IN ('AB');

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3795892923

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 121 | 26 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE| | 1 | 121 | 26 (0)| 00:00:01 | KEY | KEY |
|* 2 | TABLE ACCESS FULL | XYZ | 1 | 121 | 26 (0)| 00:00:01 | 4 | 4 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("EXCH_CODE"='AB')

14 rows selected.


But look what happens if I use multiple values that still prune to a single list partition, again the default partition:


SQL> -- mixture of existent and non-existent values
SQL> explain plan for
2 SELECT
3 * from XYZ
4 WHERE exch_code IN ('AA', 'AB');

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3676732184

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 556 | 67276 | 26 (0)| 00:00:01 | | |
| 1 | PARTITION LIST INLIST| | 556 | 67276 | 26 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS FULL | XYZ | 556 | 67276 | 26 (0)| 00:00:01 |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("EXCH_CODE"='AA' OR "EXCH_CODE"='AB')

14 rows selected.

SQL> -- non-existent values
SQL> explain plan for
2 SELECT
3 * from XYZ
4 WHERE exch_code IN ('AB', 'BC');

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3676732184

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 556 | 67276 | 26 (0)| 00:00:01 | | |
| 1 | PARTITION LIST INLIST| | 556 | 67276 | 26 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS FULL | XYZ | 556 | 67276 | 26 (0)| 00:00:01 |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("EXCH_CODE"='AB' OR "EXCH_CODE"='BC')

14 rows selected.


There are at least two noteworthy points to read from these plans: The cardinality estimate has changed significantly, but the cost has not.

This seems to be odd, the partition operation has changed to PARTITION LIST INLIST and it shows a KEY(I) operation for the partitions pruned. This could suggest that the optimizer is now using the global level statistics but then the cost should change, too.

Looking at the corresponding 10053 optimizer trace files reveals some interesting details. This is what we get for when specifying a single value for the default partition:


***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: XYZ Alias: XYZ Partition [3]
#Rows: 5000 #Blks: 90 AvgRowLen: 121.00
#Rows: 5000 #Blks: 90 AvgRowLen: 121.00
Access path analysis for XYZ
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for XYZ[XYZ]
Table: XYZ Alias: XYZ
Card: Original: 5000.000000 Rounded: 5000 Computed: 4999.50 Non Adjusted: 4999.50
Access Path: TableScan
Cost: 26.47 Resp: 26.47 Degree: 0
Cost_io: 26.00 Cost_cpu: 2440930
Resp_io: 26.00 Resp_cpu: 2440930
Best:: AccessPath: TableScan
Cost: 26.47 Degree: 1 Resp: 26.47 Card: 4999.50 Bytes: 0

***************************************


and this is what we get if we use multiple values for the default partition:


***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: XYZ Alias: XYZ (Using composite stats)
(making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 10000 #Blks: 185 AvgRowLen: 121.00
PARTITIONS::
PRUNED: 1
ANALYZED: 1 UNANALYZED: 0
#Rows: 10000 #Blks: 90 AvgRowLen: 121.00
Access path analysis for XYZ
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for XYZ[XYZ]
Table: XYZ Alias: XYZ
Card: Original: 10000.000000 Rounded: 556 Computed: 555.56 Non Adjusted: 555.56
Access Path: TableScan
Cost: 26.39 Resp: 26.39 Degree: 0
Cost_io: 26.00 Cost_cpu: 2025549
Resp_io: 26.00 Resp_cpu: 2025549
Best:: AccessPath: TableScan
Cost: 26.39 Degree: 1 Resp: 26.39 Card: 555.56 Bytes: 0

***************************************


So the oddity that shows up here is, that for the cardinality estimate the global level statistics are used ("(Using composite stats)"), but on the other hand the optimizer is clearly able to work out the pruning information to come to the conclusion that 90 blocks from the default partition need to be read, so the cost estimate is the same 26 as in the first case.

This looks like an odd mixture of global level and partition level statistics and the obvious question is why the optimizer doesn't use the partition level statistics for the cardinality estimate if it's possible to use the same statistics for the cost estimate.

The same happens for non-default partitions:


SQL>
SQL> explain plan for
2 SELECT
3 * from XYZ
4 WHERE exch_code IN ('UO');

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3795892923

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 60 | 7260 | 10 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE| | 60 | 7260 | 10 (0)| 00:00:01 | KEY | KEY |
|* 2 | TABLE ACCESS FULL | XYZ | 60 | 7260 | 10 (0)| 00:00:01 | 2 | 2 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("EXCH_CODE"='UO')

14 rows selected.

SQL>
SQL> explain plan for
2 SELECT
3 * from XYZ
4 WHERE exch_code IN ('UO', 'US');

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3676732184

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 556 | 67276 | 10 (0)| 00:00:01 | | |
| 1 | PARTITION LIST INLIST| | 556 | 67276 | 10 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS FULL | XYZ | 556 | 67276 | 10 (0)| 00:00:01 |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("EXCH_CODE"='UO' OR "EXCH_CODE"='US')

14 rows selected.


In order to see what the estimates would look like if the partition level statistics were used, we can make use of the explicit partition pruning by name:


SQL>
SQL> explain plan for
2 SELECT
3 * from XYZ partition (BBO_EURO)
4 WHERE exch_code IN ('AA', 'AB');

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3795892923

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 590K| 26 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE| | 5000 | 590K| 26 (0)| 00:00:01 | KEY | KEY |
|* 2 | TABLE ACCESS FULL | XYZ | 5000 | 590K| 26 (0)| 00:00:01 | 4 | 4 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("EXCH_CODE"='AA' OR "EXCH_CODE"='AB')

14 rows selected.

SQL>
SQL> explain plan for
2 SELECT
3 * from XYZ partition (BBO_EURO)
4 WHERE exch_code IN ('AB', 'BC');

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3795892923

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 121 | 26 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE| | 1 | 121 | 26 (0)| 00:00:01 | KEY | KEY |
|* 2 | TABLE ACCESS FULL | XYZ | 1 | 121 | 26 (0)| 00:00:01 | 4 | 4 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("EXCH_CODE"='AB' OR "EXCH_CODE"='BC')

14 rows selected.

SQL>
SQL> explain plan for
2 SELECT
3 * from XYZ partition (BBO_NAMR)
4 WHERE exch_code IN ('UO', 'US');

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3795892923

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 114 | 13794 | 10 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE| | 114 | 13794 | 10 (0)| 00:00:01 | KEY | KEY |
|* 2 | TABLE ACCESS FULL | XYZ | 114 | 13794 | 10 (0)| 00:00:01 | 2 | 2 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("EXCH_CODE"='UO' OR "EXCH_CODE"='US')

14 rows selected.


You can see that in all cases the cardinality estimates are quite different (and very accurate by the way since I have a histogram on the EXCH_CODE column on partition level), and the cost estimate is still the same.

Just for completeness, dynamic sampling can help in this case, but only if used explicitly on table level, since the predicate is not considered as "guess", therefore with statistics in place the dynamic sampling doesn't get used.


SQL> -- no guess, therefore dynamic sampling on cursor level is not used
SQL> explain plan for
2 SELECT /*+ dynamic_sampling(4) */
3 * from XYZ
4 WHERE exch_code IN ('UO', 'US');

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3676732184

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 556 | 67276 | 10 (0)| 00:00:01 | | |
| 1 | PARTITION LIST INLIST| | 556 | 67276 | 10 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS FULL | XYZ | 556 | 67276 | 10 (0)| 00:00:01 |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("EXCH_CODE"='UO' OR "EXCH_CODE"='US')

14 rows selected.

SQL> -- dynamic sampling explicitly specified for table
SQL> explain plan for
2 SELECT /*+ dynamic_sampling(xyz, 4) */
3 * from XYZ
4 WHERE exch_code IN ('UO', 'US');

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3676732184

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 114 | 13794 | 10 (0)| 00:00:01 | | |
| 1 | PARTITION LIST INLIST| | 114 | 13794 | 10 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS FULL | XYZ | 114 | 13794 | 10 (0)| 00:00:01 |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("EXCH_CODE"='UO' OR "EXCH_CODE"='US')

Note
-----
- dynamic sampling used for this statement

18 rows selected.

SQL>


So this odd mixture of global and partition level statistics requires to have high-quality global level statistics including histograms where necessary to get accurate cardinality estimates:


exec dbms_stats.gather_table_stats(null, 'XYZ', granularity=>'ALL')


Now the results look different:


SQL>
SQL> explain plan for
2 SELECT
3 * from XYZ
4 WHERE exch_code IN ('AA', 'AB');

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3676732184

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5018 | 592K| 26 (0)| 00:00:01 | | |
| 1 | PARTITION LIST INLIST| | 5018 | 592K| 26 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS FULL | XYZ | 5018 | 592K| 26 (0)| 00:00:01 |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("EXCH_CODE"='AA' OR "EXCH_CODE"='AB')

14 rows selected.

SQL>
SQL> explain plan for
2 SELECT
3 * from XYZ
4 WHERE exch_code IN ('AB', 'BC');

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3676732184

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 35 | 4235 | 26 (0)| 00:00:01 | | |
| 1 | PARTITION LIST INLIST| | 35 | 4235 | 26 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS FULL | XYZ | 35 | 4235 | 26 (0)| 00:00:01 |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("EXCH_CODE"='AB' OR "EXCH_CODE"='BC')

14 rows selected.

SQL>
SQL> explain plan for
2 SELECT
3 * from XYZ
4 WHERE exch_code IN ('UO', 'US');

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3676732184

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 114 | 13794 | 10 (0)| 00:00:01 | | |
| 1 | PARTITION LIST INLIST| | 114 | 13794 | 10 (0)| 00:00:01 |KEY(I) |KEY(I) |
|* 2 | TABLE ACCESS FULL | XYZ | 114 | 13794 | 10 (0)| 00:00:01 |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("EXCH_CODE"='UO' OR "EXCH_CODE"='US')

14 rows selected.


But this comes at the price of the overhead to maintain global level statistics, which wasn't necessary if the pruning would be handled consistently in case the process is designed to prune to a single list partition.

I haven't tested yet in its entirety if the new incremental statistics approach of 11g or its corresponding 10.2.0.4 feature "APPROX_GLOBAL AND PARTITION" (see e.g. here) maintain histograms on global level without the need to gather global level statistics, but the present approach of the optimizer towards list partition pruning to a single partition seems to be questionable.

What I can confirm is that the traditional statistics collection on partition level including histograms doesn't generate histograms on aggregate levels, so with aggregate statistics you don't get histograms on global or partition level (in case of composite partitioning).

This test case result applies to 9.2.0.8, 10.2.0.4 and 11.1.0.7, tests were run on Windows XP 32bit.

Sunday, March 22, 2009

Getting accurate cardinality estimates for the LIKE pattern matching - basic column statistics, histograms and dynamic sampling

If you need to get accurate cardinality/selectivity estimates from the cost based optimizer for the LIKE operator used with pattern operations (e.g. 'A%'), there are significant differences between basic column statistics and histograms.

The following test case run against 10.2.0.4 compares the cardinality estimates you get with basic column statistics and histograms.

It also demonstrates that pattern searches that use a pattern as leading character (e.g. '%A') don't benefit from histograms but can only be alleviated by using dynamic sampling which of course comes at the price of additional work performed at optimization time.


SQL>
SQL> create table like_test
2 as
3 select * from all_objects
4 where rownum <= 1000;

Table created.

SQL>
SQL> -- basic column statistics, no histograms
SQL> exec dbms_stats.gather_table_stats(null, 'LIKE_TEST', method_opt=>'FOR ALL COLUMNS SIZE 1')

PL/SQL procedure successfully completed.

SQL>
SQL> select
2 num_distinct
3 from
4 user_tab_cols
5 where
6 table_name = 'LIKE_TEST'
7 and column_name = 'OBJECT_NAME';

NUM_DISTINCT
------------
992

SQL>
SQL> set autotrace traceonly
SQL>
SQL> -- different but inaccurate estimates based on LOW_VALUE/HIGH_VALUE
SQL> select * from like_test where object_name like 'A%';

38 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 2686 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 34 | 2686 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE 'A%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
3028 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
38 rows processed

SQL>
SQL> select * from like_test where object_name like 'B%';

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46 | 3634 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 46 | 3634 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE 'B%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
1376 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>
SQL> select * from like_test where object_name like 'N%';

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46 | 3634 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 46 | 3634 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE 'N%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
1456 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>
SQL> select * from like_test where object_name like 'X%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 79 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE 'X%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select * from like_test where object_name like 'AB%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 79 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE 'AB%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select * from like_test where object_name like 'V$%';

94 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 79 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE 'V$%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4896 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
94 rows processed

SQL>
SQL> -- 5% guess for unprefixed wildcard searches like this
SQL> select * from like_test where object_name like '%V$%';

96 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 3950 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE '%V$%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4999 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96 rows processed

SQL>
SQL> select * from like_test where object_name like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 3950 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE '%AA%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> -- use dynamic sampling to get accurate estimates in this case
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%V$%';

96 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96 | 7584 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 96 | 7584 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE '%V$%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
36 consistent gets
0 physical reads
0 redo size
4999 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 79 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE '%AA%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
29 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> set autotrace off
SQL>
SQL> -- regather the statistics
SQL> -- could generate a histogram on object_name now
SQL> -- based on above column workload
SQL> exec dbms_stats.gather_table_stats(null, 'LIKE_TEST', method_opt=>'FOR ALL COLUMNS SIZE AUTO')

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly
SQL>
SQL> -- more accurate estimates based on histogram
SQL> select * from like_test where object_name like 'A%';

38 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 38 | 3002 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 38 | 3002 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE 'A%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
3028 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
38 rows processed

SQL>
SQL> select * from like_test where object_name like 'B%';

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 632 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 8 | 632 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE 'B%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
1376 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>
SQL> select * from like_test where object_name like 'N%';

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 316 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 4 | 316 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE 'N%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
1456 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>
SQL> select * from like_test where object_name like 'X%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 79 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE 'X%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select * from like_test where object_name like 'AB%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 79 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE 'AB%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select * from like_test where object_name like 'V$%';

94 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 91 | 7189 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 91 | 7189 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE 'V$%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4896 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
94 rows processed

SQL>
SQL> -- 5% guess for unprefixed wildcard searches like this
SQL> -- histogram is if no use in this case
SQL> select * from like_test where object_name like '%V$%';

96 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 3950 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE '%V$%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4999 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96 rows processed

SQL>
SQL> select * from like_test where object_name like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 3950 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE '%AA%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> -- use dynamic sampling to get accurate estimates in this case
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%V$%';

96 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96 | 7584 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 96 | 7584 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE '%V$%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4999 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 79 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE '%AA%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> set autotrace off
SQL>


Basic column statistics can only use the recorded NUM_DISTINCT, LOW and HIGH VALUE for the selectivity estimate, therefore values that fall within the recorded range all get similar cardinality estimates, it does a bad job however when using multi-character search patterns (e.g. 'AB%').

Using histograms shows quite accurate estimates, even in case of the multi-character seach patterns.

In case of wildcards as leading characters of the search pattern (e.g. '%AA%') the optimizer falls back to a default 5% guess. In this case dynamic sampling can be used to get accurate estimates. If you're using the optimizer_dynamic_sampling parameter or the dynamic_sampling hint without specifying the table it needs to be set to level 3 at least to apply dynamic sampling to predicates that are based on guesses.

A slightly different variant is the following where a function-based index is used for an expression. The test case demonstrates another important point to keep in mind when adding function-based indexes: Creating the index adds a hidden/virtual column to the table that initially doesn't have any column statistics, although the index itself is analyzed by default from 10g on.

Therefore you should always make sure that the hidden/virtual columns added to a table do have statistics populated, otherwise you might be in for a surprise.


SQL> -- create function based index, but it's missing the column statistics
SQL> create index like_test_idx1 on like_test (lpad(object_name, 5, '0'));

Index created.

SQL>
SQL> select
2 num_distinct
3 from
4 user_tab_cols
5 where
6 table_name = 'LIKE_TEST'
7 and hidden_column = 'YES';

NUM_DISTINCT
------------


SQL>
SQL> set autotrace traceonly
SQL>
SQL> -- no difference between estimates
SQL> -- due to missing column statistics
SQL> -- of hidden column
SQL> -- 5% default guess is being used
SQL> select * from like_test where lpad(object_name, 5, '0') like 'A%';

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 50 | 3950 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 9 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'A%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'A%')


Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
19 consistent gets
1 physical reads
0 redo size
3137 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'B%';


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 50 | 3950 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 9 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'B%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'B%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1361 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'N%';

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 50 | 3950 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 9 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'N%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'N%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
1 physical reads
0 redo size
1498 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'X%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 50 | 3950 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 9 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'X%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'X%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
1 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'AB%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 50 | 3950 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 9 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'AB%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'AB%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'V$%';

93 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 50 | 3950 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 9 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'V$%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'V$%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
6041 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
93 rows processed

SQL>
SQL> -- 5% guess for unprefixed wildcard searches like this
SQL> select * from like_test where lpad(object_name, 5, '0') like '%V$%';

95 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 3950 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE '%V$%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4932 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
95 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 3950 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 3950 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE '%AA%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> -- use dynamic sampling to get accurate estimates in this case
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%V$%';

96 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96 | 7584 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 96 | 7584 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE '%V$%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
36 consistent gets
0 physical reads
0 redo size
4999 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 79 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE '%AA%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
29 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> set autotrace off
SQL>
SQL> -- basic column statistics
SQL> exec dbms_stats.gather_table_stats(null, 'LIKE_TEST', method_opt=>'FOR ALL HIDDEN COLUMNS SIZE 1')

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly
SQL>
SQL> -- different but inaccurate estimates based on LOW_VALUE/HIGH_VALUE
SQL> select * from like_test where lpad(object_name, 5, '0') like 'A%';

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28 | 2380 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 28 | 2380 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE 'A%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
3137 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'B%';


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28 | 2380 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 28 | 2380 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE 'B%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1361 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'N%';

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28 | 2380 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 28 | 2380 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE 'N%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
1498 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'X%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 170 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 2 | 170 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 2 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'X%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'X%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'AB%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 170 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 2 | 170 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 2 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'AB%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'AB%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'V$%';

93 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 170 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 2 | 170 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 2 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'V$%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'V$%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
6041 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
93 rows processed

SQL>
SQL> -- 5% guess for unprefixed wildcard searches like this
SQL> select * from like_test where lpad(object_name, 5, '0') like '%V$%';

95 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 4250 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 4250 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE '%V$%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4932 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
95 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 4250 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 4250 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE '%AA%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> -- use dynamic sampling to get accurate estimates in this case
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%V$%';

96 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96 | 8160 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 96 | 8160 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE '%V$%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4999 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 85 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE '%AA%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> -- generate a histogram
SQL> exec dbms_stats.gather_table_stats(null, 'LIKE_TEST', method_opt=>'FOR ALL HIDDEN COLUMNS SIZE 254')

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly
SQL>
SQL> -- more accurate estimates based on histogram
SQL> select * from like_test where lpad(object_name, 5, '0') like 'A%';

36 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 36 | 3060 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 36 | 3060 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE 'A%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
3137 bytes sent via SQL*Net to client
418 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'B%';


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 340 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 4 | 340 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 4 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'B%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'B%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1361 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'N%';

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 340 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 4 | 340 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 4 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'N%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'N%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
1498 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'X%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 170 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 2 | 170 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 2 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'X%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'X%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'AB%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1078239294

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 170 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 2 | 170 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | LIKE_TEST_IDX1 | 2 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(LPAD("OBJECT_NAME",5,'0') LIKE 'AB%')
filter(LPAD("OBJECT_NAME",5,'0') LIKE 'AB%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like 'V$%';

93 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 87 | 7395 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 87 | 7395 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE 'V$%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads
0 redo size
6041 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
93 rows processed

SQL>
SQL> -- 5% guess for unprefixed wildcard searches like this
SQL> -- histogram is if no use in this case
SQL> select * from like_test where lpad(object_name, 5, '0') like '%V$%';

95 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 4250 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 4250 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE '%V$%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4932 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
95 rows processed

SQL>
SQL> select * from like_test where lpad(object_name, 5, '0') like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 4250 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 4250 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(LPAD("OBJECT_NAME",5,'0') LIKE '%AA%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> -- use dynamic sampling to get accurate estimates in this case
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%V$%';

96 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 96 | 8160 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 96 | 8160 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE '%V$%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
4999 bytes sent via SQL*Net to client
462 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
96 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 85 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE '%AA%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed



The final test case shows that even dynamic sampling not always gets it right. Although it shows the correct estimate for the table it doesn't correct the estimate used for the index, and the overall cost estimate is therefore incorrect, too, since it is based on the uncorrected index selectivity.

The AUTOTRACE output clearly shows that using the index in this case requires actually only the index related I/O.


SQL>
SQL> select /*+ index(like_test, like_test_idx1) */
2 * from like_test where lpad(object_name, 5, '0') like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1731345218

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 4250 | 23 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 50 | 4250 | 23 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | LIKE_TEST_IDX1 | 50 | | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(LPAD("OBJECT_NAME",5,'0') LIKE '%AA%')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) index(like_test, like_test_idx1) */
2 * from like_test where lpad(object_name, 5, '0') like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1731345218

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | 23 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 1 | 85 | 23 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | LIKE_TEST_IDX1 | 50 | | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(LPAD("OBJECT_NAME",5,'0') LIKE '%AA%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> -- once again without recursive overhead
SQL> select /*+ dynamic_sampling(3) index(like_test, like_test_idx1) */
2 * from like_test where lpad(object_name, 5, '0') like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1731345218

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 85 | 23 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LIKE_TEST | 1 | 85 | 23 (0)| 00:00:01 |
|* 2 | INDEX FULL SCAN | LIKE_TEST_IDX1 | 50 | | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(LPAD("OBJECT_NAME",5,'0') LIKE '%AA%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
995 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> set autotrace off
SQL>


Running this test case against 9.2.0.8 shows similar results.

11.1.0.7 showed an interesting oddity: When using default "NLS_SORT = binary" NLS sort setting, the LIKE '%pattern%' predicate was not considered as "guess" and therefore the dynamic sampling wasn't performed. When switching to non-default "NLS_SORT" settings, like 'german' or 'french' dynamic sampling took place.

Note that other expressions, e.g. SUBSTR functions, obviously are not affected and still are considered as guess in both cases.


SQL>
SQL> set autotrace traceonly
SQL>
SQL> alter session set nls_sort = binary;

Session altered.

SQL>
SQL> alter session set tracefile_identifier = 'binary_sort';

Session altered.

SQL>
SQL> alter session set events '10053 trace name context forever, level 1';

Session altered.

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 4900 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 50 | 4900 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE '%AA%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
1123 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like 'AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 98 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE 'AA%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
1123 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where substr(object_name, 4, 2) = 'AA';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 98 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(SUBSTR("OBJECT_NAME",4,2)='AA')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
33 consistent gets
0 physical reads
0 redo size
1123 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> alter session set nls_sort = german;

Session altered.

SQL>
SQL> alter session set tracefile_identifier = 'non_binary_sort';

Session altered.

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like '%AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 98 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE '%AA%')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
1123 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where object_name like 'AA%';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 98 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME" LIKE 'AA%')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
1123 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> select /*+ dynamic_sampling(3) */
2 * from like_test where substr(object_name, 4, 2) = 'AA';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 452082961

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| LIKE_TEST | 1 | 98 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(SUBSTR("OBJECT_NAME",4,2)='AA')

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
1123 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> set autotrace off
SQL>
SQL> spool off


I'm not sure if this is intended behaviour, and it could lead to significant changes in the execution plans when upgrading to 11g.

The corresponding 10053 trace files snippets look like this:

Binary sort order:


***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for LIKE_TEST[LIKE_TEST]

*** 2009-03-22 14:30:23.422
** Performing dynamic sampling initial checks. **
** Dynamic sampling initial checks returning FALSE.
Column (#2):
NewDensity:0.001015, OldDensity:0.001034 BktCnt:254, PopBktCnt:0, PopValCnt:0, NDV:985
Table: LIKE_TEST Alias: LIKE_TEST
Card: Original: 1000.000000 Rounded: 50 Computed: 50.00 Non Adjusted: 50.00
Access Path: TableScan
Cost: 6.07 Resp: 6.07 Degree: 0
Cost_io: 6.00 Cost_cpu: 382700
Resp_io: 6.00 Resp_cpu: 382700
Best:: AccessPath: TableScan
Cost: 6.07 Degree: 1 Resp: 6.07 Card: 50.00 Bytes: 0

***************************************


Non-binary sort order:


***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for LIKE_TEST[LIKE_TEST]

*** 2009-03-22 14:30:23.762
** Performing dynamic sampling initial checks. **
Column (#2):
NewDensity:0.001015, OldDensity:0.001034 BktCnt:254, PopBktCnt:0, PopValCnt:0, NDV:985
** Dynamic sampling initial checks returning TRUE (level = 3).

*** 2009-03-22 14:30:23.762
** Generated dynamic sampling query:
query text :
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param('parallel_execution_enabled', 'false') NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("LIKE_TEST") FULL("LIKE_TEST") NO_PARALLEL_INDEX("LIKE_TEST") */ 1 AS C1, 1 AS C2 FROM "LIKE_TEST" "LIKE_TEST" WHERE "LIKE_TEST"."OBJECT_NAME" LIKE '%AA%') SAMPLESUB

*** 2009-03-22 14:30:23.762
** Executed dynamic sampling query:
level : 3
sample pct. : 100.000000
actual sample size : 1000
filtered sample card. : 0
orig. card. : 1000
block cnt. table stat. : 14
block cnt. for sampling: 14
max. sample block cnt. : 32
sample block cnt. : 14
min. sel. est. : 0.05000000
** Using single table dynamic sel. est. : 0.00000000
Table: LIKE_TEST Alias: LIKE_TEST
Card: Original: 1000.000000 Rounded: 1 Computed: 0.00 Non Adjusted: 0.00
Access Path: TableScan
Cost: 6.07 Resp: 6.07 Degree: 0
Cost_io: 6.00 Cost_cpu: 369960
Resp_io: 6.00 Resp_cpu: 369960
Best:: AccessPath: TableScan
Cost: 6.07 Degree: 1 Resp: 6.07 Card: 0.00 Bytes: 0

***************************************


So if you're relying on dynamic sampling and upgrade to 11g there might surprises waiting for you.