Changing Execution Plan For SQL Query in Oracle 11G

Changing Execution Plan For SQL Query in Oracle 11G

There are times while we want to use some particular execution plan( which we think is the best plan) every time for a sql query.
We can choose our best plan from AWR snapshots and reuse the plan by setting up baseline.

Here are the steps for loading SQL Plans into SPM(SQL Plan Management) using AWR by implementing SQL Baselines for the bad query.

Step 1: Set up a SQL Baseline using best plan, (Plan Source- from AWR snapshots)

First condition to set up SQL Baseline: SQL Plan Management must be active.
Check whether optimizer_use_sql_plan_baselines parameter is true which needs to be TRUE.


SQL>show parameter optimizer_

SQL>select count(*) from dba_sql_plan_baselines;

Check the particular sql id is currently stored in memory:

SQL>select * from TABLE(dbms_xplan.display_cursor('SQL_ID'));

Step 2: Create SQL Tuning Set (STS).

A SQL tuning set (STS) is a database object. 

An STS includes:

–          A set of SQL statements

–          Associated execution context, such as user schema, application module name and action, list of bind values, and the cursor compilation environment

–          Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, the number of executions, the number of complete executions, optimizer cost, and the command type

–          Associated execution plans and row source statistics for each SQL statement (optional)

Oracle 11g makes use of SQL tuning sets with the SQL Performance Analyzer, which compares the performance of the statements in a tuning set before and after a database change. The database changes can be as follows:

 *   Database, operating system, or hardware upgrades.
 *   Database, operating system, or hardware configuration changes.
 *   Database initialization parameter changes.
 *   Schema changes, such as adding indexes or materialized views.
 *   Refreshing optimizer statistics.
 *   Creating or changing SQL profiles.

Now we create a SQL Tuning Set based on the slow query with a SQL_ID of 1dy987n2d6and.

SQL>begin
DBMS_SQLTUNE.CREATE_SQLSET(sqlset_name => 'SQL_ID_name',
description => 'SQL tuning set for loading plan into sql plan baseline');
end;
/

SQL>select dbms_sqltune.report_sql_monitor(type=>'TEXT',report_level=>'ALL',sql_id=>'******') as report from dual;


Or

Instead of creating SQL tuning set, we can directly create baseline from cursor cache using dbms_spm.load_plans_from_cursor_cache
We can also use DBMS_SPM.evolve_sql_plan_baseline to verify new baseline and finally dbms_spm.alter_sql_plan_baseline to disable old baseline

Step 3: Lets Populate the STS from AWR.

Now we will identify the AWR snapshots required to populate the STS, and load the STS based on those snapshot ID’s and the SQL_ID.

SQL>select snap_id,begin_interval_time,end_interval_time from dba_hist_snapshot order by end_interval_time desc;

SQL>declare
cursor1 sys_refcursor
begin 
open cursor1  for
select value(P)
from table(
dbms_sqltune.select_workload_repository(begin_snao=>snapno,end_snap=>snapno,
basic_filter=>'sql_id=''*******''',attribute_list=>'ALL')P;
dbms_sqltune.load_sqlset(sqlset_name=>'*****',populate_cursor=>'cursor1')
close cursor1;
end;
/

Step 4: Verify SQL Tuning Set Contents.

Now I can query the STS to verify it contains the expected data.

SQL>select * from TABLE(dbms_sqltune.select_sqlset(sqlset_name=>'*******'));

Step 5: Create Baseline:

Now we will create Baseline.






Step 6: Load desired plan from STS as SQL Plan Baseline

Now I will load the known good plan that uses the newly created index into the Baseline.

declare
best_plans pls_integer;
begin
best_plans :=dbms_spm.load_plans_from_sqlset(
sqlset_name=>'******',
basic_filter=>'plan_hash_value  = ' ' ********' ' ');
end;
/

Step 7: Verify the Baselines:

Now verify the Baseline contains the desired plan.

SQL>select count(*) from dba_sql_plan_baselines;

Step 8. Flush the current poor performing SQL Plan.

After loading the baseline, the current cursor must be flushed from the cache to make sure the new plan will be used on next execution of the sql_id.

select required information from v$sqlarea.

then

SQL>exec dbms_shared_pool.purge('address','old_hash_value','C');