Sunday, December 17, 2017

How To Change Execution Plan Of Query Without Modifying SQL Code

All of us have encountered the situation of performance regression in production environment where your company runs a packaged application or you are not allowed to make any changes in SQL code of application.

You get some poorly performing SQL here and root cause happens to be the bad execution 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 be thinking, “I wish i could hint this query to get it to pick a better execution plan”. However, this being a packaged application or critical production environment, So your hands are tied. Let's figure out some other ways to tackle this situation. In this post we'll use sql plan management.

The original Plan
Note the full table scan on the products table and query is not doing well. As a DBA we are recommending index hint but we are not allowed to make any changes in application code.
SQL> select * from table (dbms_xplan.display_cursor(null,null,'TYPICAL'));
SQL_ID nfitk8askq3j, 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: 896757596
----------------------------------------------------------------------------------------------------
| 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
SQL> variable spb number;
SQL> execute :spb := dbms_spm.load_plans_from_cursor_cache(sql_id=>'nfitk8askq3j');
PL/SQL procedure successfully completed.

SQL> select sql_handle,sql_text,plan_name,enabled from dba_sql_plan_baselines where sql_text like '%select p.prod_name%' ;
SQL_20yt3803a09c8ew1    select p.prod_name,          SQL_PLAN_09v9s0fh9t3z1c47b6be0 YES
                                             sum(s.amount_sold) amt  
                                             from sales s,products p
                                            where s.prod_i

Disable the original plan baseline from being used
SQL> exec :spb := dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_20yt3803a09c8ew1',plan_name => 'SQL_PLAN_09v9s0fh9t3z1c47b6be0',attribute_name=>'enabled',attribute_value=>'NO');

SQL> select sql_handle,sql_text,plan_name,enabled from dba_sql_plan_baselines where sql_text like '%select p.prod_name%' ;
SQL_20yt3803a09c8ew1    select p.prod_name,           SQL_PLAN_09v9s0fh9t3z1c47b6be0  NO
                                             sum(s.amount_sold) amt  
                                             from sales s,products p
                                            where s.prod_i

Re-run 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.
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
/
SQL> select * from table (dbms_xplan.display_cursor(null,null,'TYPICAL'))
/
SQL_ID utp5ov6qmpqqw, 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: 567802669
-------------------------------------------------------------------------------------------------------------
| 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
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.
SQL> execute :spb := dbms_spm.load_plans_from_cursor_cache(sql_id => 'utp5ov6qmpqqw',plan_hash_value =>567802669,sql_handle=>'SQL_20yt3803a09c8ew1');
PL/SQL procedure successfully completed.

#Check that a new plan has been added to the baseline
SQL> select sql_handle,sql_text,plan_name,enabled from dba_sql_plan_baselines where sql_text like '%select p.prod_name%' ;
SQL_20yt3803a09c8ew1        select p.prod_name,     SQL_PLAN_09v9s0fh9t3z1c47b6be0 NO
                                                 sum(s.amount_sold) amt
                                                 from sales s,products p
                                                 where s.prod_i
SQL_20yt3803a09c8ew1        select p.prod_name,     SQL_PLAN_09v9s0fh9t3z1c47yptu8 YES
                                                 sum(s.amount_sold) amt 
                                                 from sales s,products p
                                                  where s.prod_i
   
Re-Run the original sql and check the execution Plan
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
/
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: 567802669
-------------------------------------------------------------------------------------------------------------
| 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_09v9s0fh9t3z1c47yptu8 used for this statement

So it looks a very good approach to fix the performance regression issues without changing any sql in application code.


4 comments:

  1. What is the requirement of creating the first sql baseline?
    Can we directly use the second step?

    ReplyDelete
  2. Hi Bikash,

    We need to create sql baseline for original sql to get the SQL-HANDLE. Later we attached modified plan with sql-handle of original sql.

    ReplyDelete
  3. Hello there... I believe the goal is same using the below link too. Any pros/cons please let me know. Thanks in advance !!
    http://oradba11g.blogspot.com/2015/01/pinning-execution-plan-for-sqlid-using.html?m=1

    ReplyDelete
  4. Team,

    It will be great to know why execution plan got changed? What was the reason for plan change apart from updated stats, data growth , dynamic sampling , extended stats . If you will get the root cause of changing sql execution plans then any kid having 1yr of true experience in DBA can fix it.

    ReplyDelete