Monday, April 2, 2018

how to fix when a query performed well earlier but suddenly started giving slow response time

how to fix when a query performed well earlier but suddenly started giving slow response time :


how to fix when a query performed well earlier but suddenly started giving slow response time ??
There could be several direct ,indirect causes but most likely change in explain plan.

the first fix one should try is to understand change in explain plan and see if that is the problem or any other changes that are being impacted on sql query performance.

what if we want query to use the old explain plan that gave good response time?
the answer is to use sql plan base line.

what is sql plan base line?
ASQL plan baseline of SQL plan management (SPM) help us to capture a explain plan in the SQL Management Base and attach it to the SQL query.

there are multiple ways to create a sql plan  baseline for a query. (using sql_id, or enabling optimizer_capture_sql_plan_baselines parameter to capture all the sql been executed in enabling period, etc). It is mandatory to have optimized execution plan stored in sql management base so that same execution plan can be forced to be used when we want query to use a particular explain plan for its execution.

dba_sql_plan_baselines give us query sql plan baselines information.
to extract explain plan of query from SQL Management Base:
 
select * from table(dbms_xplan.display_sql_plan_baseline(plan_name => 'SQL_PLAN_name'));
 

if we have sql plan baseline created for a particular SQL id, when similar sql query (need not be identical) get executed and based on exact_matching_signature of a query , the stored sql plan base line execution plan will be used for query execution automatically. so we can make sql query to use stored explain plan so that query response time will be same as what it was earlier.

Note section of query explain plan tells us if it is used sql plan baseline.

How to force a SQL id to use a particular sql id explain plan:

To make SQL_id X to user another SQL id explain plan which is stored in SQL Management Base:

 
  declare

      l_bl pls_integer;

    begin

      l_bl := dbms_spm.load_plans_from_cursor_cache

     (sql_id  => 'X'  

     ,plan_hash_value => 99999999999

     ,sql_handle=> 'SQL_' -- sql handle of the desired sql_id exection plan.

      );

    end;
  /

dbms_spm can be used to capture disable, enable, drop sql plan base lines.