It is likely that all of us have encountered the following situation. Your company runs a packaged application, you get some poorly performing sql, root cause happens to be the bad execution plan. You could battle statistics and see if you can get the optimizer to pick a better plan. One of the options, is to put this sql through the sql tuning advisor and see if it comes up with a sql profile that improves the execution plan, and if it does, to accept the profile.
Some of these performance issues could be critical, and require urgent resolution. In such urgent situations, you might catch yourself thinking, “I wish i could hint this query to get it to pick a better execution plan”. However, this being a packaged application, your hands are tied.
This year at oracle openworld 2011, Maria Colgan and Mohamed Zait, presented a session “Oracle Database Optimizer : Tips for preventing suboptimal execution plans”. In there, towards the end, was a gem, which illustrated how to get a sql to pick a different execution plan (preferably generated by a modified version of the sql, hinted to pick a better plan), without actually modifying the application sql. This technique uses sql plan management.
This blog post is just reproducing the exact same method, with the exact same example they used, with a little bit more illustration of the execution plan, hopefully to benefit folks, who have not used this procedure before, and did not attend the openworld session.
The original sql
SQL> connect sh/sh
Connected.
SQL> variable sup_id number;
SQL> exec :sup_id := 1;
select p.prod_name,sum(s.amount_sold) amt
from sales s,products p
where s.prod_id = p.prod_id
and p.supplier_id = :sup_id
group by p.prod_name
/
Envoy External 6X CD-ROM 645586.12
Model SM26273 Black Ink Cartridge 617732.28
Model K8822S Cordless Phone Battery 582640.54
Bounce 244595.65
Smash up Boxing 260436.75
Comic Book Heroes 101214.6
The original Plan
Note the full table scan on the products table
SQL> select * from table (dbms_xplan.display_cursor(null,null,'TYPICAL'))
/
2
SQL_ID fmjmws8askq3j, child number 0
-------------------------------------
select p.prod_name,sum(s.amount_sold) amt from sales s,products p where
s.prod_id = p.prod_id and p.supplier_id = :sup_id group by p.prod_name
Plan hash value: 504757596
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 587 (100)| | | |
| 1 | HASH GROUP BY | | 71 | 3550 | 587 (12)| 00:00:08 | | |
|* 2 | HASH JOIN | | 72 | 3600 | 586 (12)| 00:00:08 | | |
| 3 | VIEW | VW_GBC_5 | 72 | 1224 | 583 (12)| 00:00:07 | | |
| 4 | HASH GROUP BY | | 72 | 648 | 583 (12)| 00:00:07 | | |
| 5 | PARTITION RANGE ALL| | 918K| 8075K| 533 (3)| 00:00:07 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 8075K| 533 (3)| 00:00:07 | 1 | 28 |
|* 7 | TABLE ACCESS FULL | PRODUCTS | 72 | 2376 | 3 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="P"."PROD_ID")
7 - filter("P"."SUPPLIER_ID"=:SUP_ID)
26 rows selected.
Create the plan baseline for this sql
Note that we are using the sql_id we got from the output of dbms_xplan in the previous step.
SQL> variable cnt number;
SQL> execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id=>'fmjmws8askq3j');
PL/SQL procedure successfully completed.
Verify that the plan baseline was created by checking dba_sql_plan_baselines (Notice that the plan is enabled by default when you created it)
SQL> select sql_handle,sql_text,plan_name,enabled
from
dba_sql_plan_baselines
where
sql_text like '%select p.prod_name%' 2 3 4 5
6 /
SQL_10ed3803a09c8fe1 select p.prod_name,sum(s.amount_sold) amt SQL_PLAN_11v9s0fh9t3z1c47b6be0 YES
from sales s,products p
where s.prod_i
Disable the original plan baseline from being used. (Since we’d be substituting this with a new hinted plan)
Note that we are using the plan_name and sql_handle that we got from the previous query.
SQL> exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_10ed3803a09c8fe1',-
plan_name => 'SQL_PLAN_11v9s0fh9t3z1c47b6be0',-
attribute_name=>'enabled',-
attribute_value=>'NO');
/
Check dba_sql_plan_baselines to ensure the baseline is now disabled.
1 select sql_handle,sql_text,plan_name,enabled
2 from
3 dba_sql_plan_baselines
4 where
5* sql_text like '%select p.prod_name%'
SQL> /
SQL_10ed3803a09c8fe1 select p.prod_name,sum(s.amount_sold) amt SQL_PLAN_11v9s0fh9t3z1c47b6be0 NO
from sales s,products p
where s.prod_i
Rerun the sql with an Index Hint
Note that we are using a index hint (/*+ index(p) */ , to illustrate the point that, the execution plan has changed, and now it picks an index as opposed to a full table scan from the original query. In this specific case, there is no index on the supplier_id on the table products, so it picks the primary key on the table and does a full index scan. But you can see how, hinting your queries, with the proper index names can help your query.
SQL> select /*+ index(p) */ p.prod_name,sum(s.amount_sold) amt
from sales s,products p
where s.prod_id = p.prod_id
and p.supplier_id = :sup_id
group by p.prod_name
/ 2 3 4 5 6
Envoy External 6X CD-ROM 645586.12
Model SM26273 Black Ink Cartridge 617732.28
Model K8822S Cordless Phone Battery 582640.54
Bounce 244595.65
Smash up Boxing 260436.75
Comic Book Heroes 101214.6
Finding Fido 78881.08
Check the new execution plan
Notice that the new plan uses the index products_pk on the table products.
SQL> select * from table (dbms_xplan.display_cursor(null,null,'TYPICAL'))
/ 2
SQL_ID gtdunv6qmpqqw, child number 0
-------------------------------------
select /*+ index(p) */ p.prod_name,sum(s.amount_sold) amt from sales
s,products p where s.prod_id = p.prod_id and p.supplier_id = :sup_id
group by p.prod_name
Plan hash value: 4089802669
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 587 (100)| | | |
| 1 | HASH GROUP BY | | 71 | 3550 | 587 (12)| 00:00:08 | | |
|* 2 | HASH JOIN | | 72 | 3600 | 586 (12)| 00:00:08 | | |
| 3 | VIEW | VW_GBC_5 | 72 | 1224 | 583 (12)| 00:00:07 | | |
| 4 | HASH GROUP BY | | 72 | 648 | 583 (12)| 00:00:07 | | |
| 5 | PARTITION RANGE ALL | | 918K| 8075K| 533 (3)| 00:00:07 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 8075K| 533 (3)| 00:00:07 | 1 | 28 |
|* 7 | TABLE ACCESS BY INDEX ROWID| PRODUCTS | 72 | 2376 | 3 (0)| 00:00:01 | | |
| 8 | INDEX FULL SCAN | PRODUCTS_PK | 72 | | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="P"."PROD_ID")
7 - filter("P"."SUPPLIER_ID"=:SUP_ID)
28 rows selected.
Switch the execution plan for the original, unhinted sql
From the dbms_xplan output above we know the new sql_id (gtdunv6qmpqqw) and the new plan_hash_value (4089802669) (For the plan that is using an index).
We can then use dbms_spm to associate this new execution plan, to the sql_handle we created, for the original un-hinted sql, in the sql plan baseline. We use the new sql_id and plan_hash_value, from our hinted plan and we associate it to the sql_handle from the original query.
Note that the sql_handle we are using here, is the sql_handle for the original unhinted sql (We get this value from the step “Verify that the plan base line was created”) .
SQL> execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id => 'gtdunv6qmpqqw',-
plan_hash_value => 4089802669,-
sql_handle=>'SQL_10ed3803a09c8fe1');
/
PL/SQL procedure successfully completed.
Check that a new plan has been added to the baseline
Note that the new plan is enabled by default.
SQL> select sql_handle,sql_text,plan_name,enabled
from
dba_sql_plan_baselines
where
sql_text like '%select p.prod_name%'
/
SQL_10ed3803a09c8fe1 select p.prod_name,sum(s.amount_sold) amt SQL_PLAN_11v9s0fh9t3z1c47b6be0 NO
from sales s,products p
where s.prod_i
SQL_10ed3803a09c8fe1 select p.prod_name,sum(s.amount_sold) amt SQL_PLAN_11v9s0fh9t3z1d20e849e YES
from sales s,products p
where s.prod_i
Re-Run the original sql
select p.prod_name,sum(s.amount_sold) amt
from sales s,products p
where s.prod_id = p.prod_id
and p.supplier_id = :sup_id
group by p.prod_name
/
Envoy External 6X CD-ROM 645586.12
Model SM26273 Black Ink Cartridge 617732.28
Model K8822S Cordless Phone Battery 582640.54
Bounce 244595.65
Smash up Boxing 260436.75
Comic Book Heroes 101214.6
Check the new execution plan
You can see that the original unhinted sql statement is now using the plan hash value of the hinted query and hence is using the primary key index on the products table (As opposed to the full table scan on the original table).
SQL> select * from table (dbms_xplan.display_cursor(null,null,'TYPICAL'));
SQL_ID fmjmws8askq3j, child number 1
-------------------------------------
select p.prod_name,sum(s.amount_sold) amt from sales s,products p where
s.prod_id = p.prod_id and p.supplier_id = :sup_id group by p.prod_name
Plan hash value: 4089802669
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 587 (100)| | | |
| 1 | HASH GROUP BY | | 71 | 3550 | 587 (12)| 00:00:08 | | |
|* 2 | HASH JOIN | | 72 | 3600 | 586 (12)| 00:00:08 | | |
| 3 | VIEW | VW_GBC_5 | 72 | 1224 | 583 (12)| 00:00:07 | | |
| 4 | HASH GROUP BY | | 72 | 648 | 583 (12)| 00:00:07 | | |
| 5 | PARTITION RANGE ALL | | 918K| 8075K| 533 (3)| 00:00:07 | 1 | 28 |
| 6 | TABLE ACCESS FULL | SALES | 918K| 8075K| 533 (3)| 00:00:07 | 1 | 28 |
|* 7 | TABLE ACCESS BY INDEX ROWID| PRODUCTS | 72 | 2376 | 3 (0)| 00:00:01 | | |
| 8 | INDEX FULL SCAN | PRODUCTS_PK | 72 | | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="P"."PROD_ID")
7 - filter("P"."SUPPLIER_ID"=:SUP_ID)
Note
-----
- SQL plan baseline SQL_PLAN_11v9s0fh9t3z1d20e849e used for this statement