We often encounter situations when a SQL runs optimally when it is hinted but sub-optimally otherwise. We can use hints to get the desired plan but it is not desirable to use hints in production code as the use of hints involves extra code that must be managed, checked, and controlled with every Oracle patch or upgrade. Moreover, hints freeze the execution plan so that you will not be able to benefit from a possibly better plan in future.
So how can we make such queries use optimal plan until a provably better plan comes along without adding hints?
Well, the answer is to use SQL Plan Management which ensures that you get the desirable plan which will evolve over time as optimizer discovers better ones.
To demonstrate the procedure, I have created two tables CUSTOMER and PRODUCT having CUST_ID and PROD_ID respectively as primary keys. PROD_ID column in CUSTOMER table is the foreign key and is indexed.
SQL>onn hr/hr drop table customer purge; drop table product purge; create table product(prod_id number primary key, prod_name char(100)); create table customer(cust_id number primary key, cust_name char(100), prod_id number references product(prod_id)); create index cust_idx on customer(prod_id); insert into product select rownum, 'prod'||rownum from all_objects; insert into customer select rownum, 'cust'||rownum, prod_id from product; update customer set prod_id = 1000 where prod_id > 1000; exec dbms_stats.gather_table_stats (USER, 'customer', cascade=> true); exec dbms_stats.gather_table_stats (USER, 'product', cascade=> true);
– First, let’s have a look at the undesirable plan which does not use the index on PROD_ID column of CUSTOMER table.
SQL>conn / as sysdba
alter system flush shared_pool;
conn hr/hr
variable prod_id number
exec :prod_id := 1000
select cust_name, prod_name
from customer c, product p
where c.prod_id = p.prod_id
and c.prod_id = :prod_id;
select * from table (dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------SQL_ID b257apghf1a8h, child number 0
-------------------------------------
select cust_name, prod_name from customer c, product p where c.prod_id
= p.prod_id and c.prod_id = :prod_id
Plan hash value: 3134146364
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 412 (100)| |
| 1 | NESTED LOOPS | | 88734 | 17M| 412 (1)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| PRODUCT | 1 | 106 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | SYS_C0010600 | 1 | | 1 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | CUSTOMER | 88734 | 9098K| 410 (1)| 00:00:01 |
----------------------------------------------------------------------
– Load undesirable plan into baseline to establish a SQL plan baseline for this query into which the desired plan will be loaded later
SQL>variable cnt number
exec :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => 'b257apghf1a8h');
col sql_text for a35 word_wrapped
col enabled for a15
select sql_text, sql_handle, plan_name, enabled
from dba_sql_plan_baselines
where sql_text like 'select cust_name, prod_name%';
SQL_TEXT SQL_HANDLE PLAN_NAME ENABLED
----------------------------------- ----------------------------------------------------------------------
select cust_name, prod_name SQL_7d3369334b24a117 SQL_PLAN_7ucv96d5k988rfe19664b YES
– Disable undesirable plan so that this plan will not be used
SQL>variable cnt number
exec :cnt := dbms_spm.alter_sql_plan_baseline (-
SQL_HANDLE => 'SQL_7d3369334b24a117',-
PLAN_NAME => 'SQL_PLAN_7ucv96d5k988rfe19664b',-
ATTRIBUTE_NAME => 'enabled',-
ATTRIBUTE_VALUE => 'NO');
col sql_text for a35 word_wrapped
col enabled for a15
select sql_text, sql_handle, plan_name, enabled
from dba_sql_plan_baselines
where sql_text like 'select cust_name, prod_name%';
SQL_TEXT SQL_HANDLE PLAN_NAME ENABLED
----------------------------------------------------------------------select cust_name, prod_name SQL_7d3369334b24a117 SQL_PLAN_7ucv96d5k988rfe19664b NO
– Now we use hint in the above SQL to generate the optimal plan which uses index on PROD_ID column of CUSTOMER table
SQL>conn hr/hr variable prod_id number exec :prod_id := 1000 select /*+ index(c)*/ cust_name, prod_name from customer c, product p where c.prod_id = p.prod_id and c.prod_id = :prod_id; select * from table (dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 5x2y12dzacv7w, child number 0 ------------------------------------- select /*+ index(c)*/ cust_name, prod_name from customer c, product p where c.prod_id = p.prod_id and c.prod_id = :prod_id Plan hash value: 4263155932 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1618 (100)| | | 1 | NESTED LOOPS | | 88734 | 17M| 1618 (1)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | PRODUCT | 1 | 106 | 2 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | SYS_C0010600 | 1 | | 1 (0)| 00:00:01 | |* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER | 88734 | 9098K| 1616 (1)| 00:00:01 | | 5 | INDEX FULL SCAN | SYS_C0010601 | 89769 | | 169 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------------
– Now we will load the hinted plan into baseline –
– Note that we have SQL_ID and PLAN_HASH_VALUE of the hinted statement and SQL_HANDLE for the unhinted statement i.e. we are associating hinted plan with unhinted statement.
SQL>variable cnt number exec :cnt := dbms_spm.load_plans_from_cursor_cache(- sql_id => '5x2y12dzacv7w', - plan_hash_value => 4263155932, - sql_handle => 'SQL_7d3369334b24a117');
– Verify that there are now two plans loaded for that SQL statement:
- Unhinted sub-optimal plan is disabled
- Hinted optimal plan which even though is for a “different query,” can work with earlier unhinted query (SQL_HANDLE is same) is enabled.
SQL>col sql_text for a35 word_wrapped col enabled for a15 select sql_text, sql_handle, plan_name, enabled from dba_sql_plan_baselines where sql_text like 'select cust_name, prod_name%'; SQL_TEXT SQL_HANDLE PLAN_NAME ENABLED ---------------------------------------------------------------------- select cust_name, prod_name SQL_7d3369334b24a117 SQL_PLAN_7ucv96d5k988rea320380 YES select cust_name, prod_name SQL_7d3369334b24a117 SQL_PLAN_7ucv96d5k988rfe19664b NO
– Verify that hinted plan is used even though we do not use hint in the query –
– The note confirms that baseline has been used for this statement
SQL>variable prod_id number exec :prod_id := 1000 select cust_name, prod_name from customer c, product p where c.prod_id = p.prod_id and c.prod_id = :prod_id; select * from table (dbms_xplan.display_cursor()); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID b257apghf1a8h, child number 0 ------------------------------------- select cust_name, prod_name from customer c, product p where c.prod_id = p.prod_id and c.prod_id = :prod_id Plan hash value: 4263155932 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1618 (100)| | | 1 | NESTED LOOPS | | 88734 | 17M| 1618 (1)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | PRODUCT | 1 | 106 | 2 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | SYS_C0010600 | 1 | | 1 (0)| 00:00:01 | |* 4 | TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER | 88734 | 9098K| 1616 (1)| 00:00:01 | | 5 | INDEX FULL SCAN | SYS_C0010601 | 89769 | | 169 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("P"."PROD_ID"=:PROD_ID) 4 - filter("C"."PROD_ID"=:PROD_ID) Note ----- - SQL plan baseline SQL_PLAN_7ucv96d5k988rea320380 used for this statement
With this baseline solution, you need not employ permanent hints the production code and hence no upgrade issues. Moreover, the plan will evolve with time as optimizer discovers better ones.
Note: Using this method, you can swap the plan for only a query which is fundamentally same i.e. you should get the desirable plan by adding hints, modifying an optimizer setting, playing around with statistics etc. and then associate sub-optimally performing statement with the optimal plan.
I hope this post was useful.
Your comments and suggestions are always welcome!
References:
http://www.oracle.com/technetwork/issue-archive/2014/14-jul/o44asktom-2196080.html
—————————————————————————————————————————————–
Related links: