Header

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 PlanNote 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.

Re-run the sql with an Index HintNote 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.

Switch the execution plan for the original, unhinted sqlWe 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.

#Check that a new plan has been added to the baselineSQL> 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_iSQL_20yt3803a09c8ew1 select p.prod_name, SQL_PLAN_09v9s0fh9t3z1c47yptu8 YES sum(s.amount_sold) amt from sales s,products p where s.prod_i

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

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.