SQL Plan Fixing with SPM and SQLPatch

In databases, sometimes there may be corruptions in the plans of SQLs that are running smoothly. Corruptions such as expected indexes not being used or full table scan. That tires the source by reading more blocks than necessary and causes low performance resulting in long running queries. In this writing, we'll examine SQL fixing in Oracle with 2 different methods: SPM and SQLPatch. Enjoy the post.

1- SQL Fixing with SPM

Let's start with the first method SPM (SQL Plan Management). I'll work with a user who is the DBA on editor during the process to make it more understandable visually. You can also do the same on SQLPlus with SYS.

In the example query above, all records in the employees table are wanted to be retrieved. Normally, it's natural for the Optimizer that this query is full table scan. However; let's accept this query as a full table scan wrong query plan rather than using the index. Fix that the query runs using the index on employee_id.

select * from hr.employees;

Select the sql_id and plan_hash_value of the wrong plan.

select sql_id, child_number, hash_value, plan_hash_value, sql_plan_baseline, sql_patch, sql_text from (g)v$sql where sql_text='select * from hr.employees';

When we examine the query plan causing the complaint, we see Table Access Full. With this query, we can examine plans of the SQLs of which we know sql_id and child_number.

select plan_table_output from table ( dbms_xplan.display_cursor('7jk33n4f4mpy9', 1, 'ALL +OUTLINE') );

Upon detecting the problem, run the query again with index hint help and parse it to have a good running plan.

select /*+ INDEX(employees, EMP_EMP_ID_PK) */ * from hr.employees;

Catch sql_id and plan_hash_value of the hint added query from the v$sql view. Attention! Sql_id of the hint added query changes.

select sql_id, child_number, hash_value, plan_hash_value, sql_plan_baseline, sql_patch from (g)v$sql
where sql_text='select /*+ INDEX(employees, EMP_EMP_ID_PK) */ * from hr.employees';

Check if the query plan we've got with hint help uses index or not. At this stage, if we can't have a good plan, we can't go on to fixing process; that is we can't continue. That's why if there are problems like type clash or index clash, hindering use of index, first they must be handled.

select plan_table_output from table ( dbms_xplan.display_cursor('a2n9kdch2ywg9', 0, 'ALL +OUTLINE') );

First, load the wrong SQL plan to SPM with plan_hash_value.

DECLARE
    result NUMBER;
BEGIN
    result := DBMS_SPM.load_plans_from_cursor_cache (sql_id => '7jk33n4f4mpy9', plan_hash_value => 1445457117);
END;

See that the loaded wrong plan has come to the dba_sql_plan_baselines view.

select created, sql_handle, plan_name, enabled, accepted, fixed, autopurge from dba_sql_plan_baselines where created > sysdate-1 order by created desc;

Sql_handle value of the loaded wrong plan is linked with sql_id and plan_hash_value of the good plan.

DECLARE
    result NUMBER;
BEGIN
    result := DBMS_SPM.load_plans_from_cursor_cache (
    sql_id          => 'a2n9kdch2ywg9', 
    plan_hash_value => 1361983096,
    sql_handle      => 'SQL_9966171d4d89b8e4'
    );
END;

See that the loaded good plan has come to dba_sql_plan_baselines view.

select created, sql_handle, plan_name, enabled, accepted, fixed, autopurge from dba_sql_plan_baselines where created > sysdate-1 order by created desc;

Fix the good plan.

DECLARE
    result NUMBER;
BEGIN
    result := DBMS_SPM.alter_sql_plan_baseline (
    sql_handle      => 'SQL_9966171d4d89b8e4',
    plan_name       => 'SQL_PLAN_9kthr3p6smf748b1782f2',
    attribute_name  => 'fixed', 
    attribute_value => 'yes'
    );
END;

Set autopurge=no to hinder automatically deleting of the good plan.

DECLARE
    result NUMBER;
BEGIN
    result := DBMS_SPM.alter_sql_plan_baseline (
    sql_handle      => 'SQL_9966171d4d89b8e4',
    plan_name       => 'SQL_PLAN_9kthr3p6smf748b1782f2',
    attribute_name  => 'autopurge', 
    attribute_value => 'no'
    );
END;

Delete the first wrong plan we've loaded to SPM to hinder using later.

DECLARE
    result NUMBER;
BEGIN
    result := DBMS_SPM.drop_sql_plan_baseline (
    sql_handle      => 'SQL_9966171d4d89b8e4',
    plan_name       => 'SQL_PLAN_9kthr3p6smf74cf314e9e'
    );
END;

The other plan is just the way we want it to be.

select created, sql_handle, plan_name, enabled, accepted, fixed, autopurge from dba_sql_plan_baselines where created > sysdate-1 order by created desc;

Before running the query again, delete related SQL, address and hash_value from the Shared Pool and make them ready as if they were to be parsed for the first time. In RAC (Real Application Cluster) systems, you must delete the plans from memory of every instance because those instances produce their plan themselves!

Note: Upon running the Shared Pool purge command, if there are other active sessions running with the same sql_id, plans won't be deleted from the Shared Pool before killing the sessions.

select inst_id, sql_id, child_number, address, hash_value, sql_plan_baseline,sql_patch from (g)v$sql where sql_id='7jk33n4f4mpy9';

exec sys.dbms_shared_pool.purge('000000006F655248,474601417','C');

To test the query, run it again and make it parse.

select * from hr.employees;

Upon running the query again, look at v$sql view and see that the sql_plan_baseline is used.

select sql_id, child_number, hash_value, plan_hash_value, sql_plan_baseline, sql_patch from (g)v$sql where sql_text='select * from hr.employees';

When the query plan is examined again with the dbms_xplan.display_cursor, use of index is seen. We've successfully completed SQL Fixing with SPM.

select plan_table_output from table ( dbms_xplan.display_cursor('7jk33n4f4mpy9', 2, 'ALL +OUTLINE') );

If you want to delete sql_plan_baseline, it's done with sql_handle and plan_name. Now, delete the plan you've fixed and get ready for fixing with SQLPatch.

DECLARE
    result NUMBER;
BEGIN
    result := DBMS_SPM.drop_sql_plan_baseline (
    sql_handle      => 'SQL_9966171d4d89b8e4',
    plan_name       => 'SQL_PLAN_9kthr3p6smf748b1782f2'
    );
END;

Before continuing to the other method, delete this query plan from the Shared Pool too.

select inst_id, sql_id, child_number, address, hash_value, sql_plan_baseline,sql_patch from gv$sql where sql_id='7jk33n4f4mpy9';

exec sys.dbms_shared_pool.purge('000000006F655248,474601417','C');

2- SQL Fixing with SQLPatch

In the second method, we'll give the index hint we want it to use to a query by writing SQLPatch. Again, we accept this query as a full wrong plan.

Continue with the same SQL, we'll use SQLPatch method this time. Run the query once and create a wrong plan.

select * from hr.employees;

Select the sql_id and plan_hash_value of the wrong plan.

select sql_id, child_number, hash_value, plan_hash_value, sql_plan_baseline, sql_patch from (g)v$sql where sql_text='select * from hr.employees';

Examine plan and outline data with the help of dbms_xplan.display_cursor. See that the employees table is full in the outline data.

select plan_table_output from table ( dbms_xplan.display_cursor('7jk33n4f4mpy9', 1, 'ALL +OUTLINE') );

Run the hint added query which will create the good plan.

select /*+ INDEX(employees, EMP_EMP_ID_PK) */ * from hr.employees;

Select sql_id and plan_hash_value of the good plan.

select sql_id, child_number, hash_value, plan_hash_value, sql_plan_baseline, sql_patch from (g)v$sql
where sql_text='select /*+ INDEX(employees, EMP_EMP_ID_PK) */ * from hr.employees';

When we examine the plan, we see that the query is run on index. 2 lines signaling the index use in the Outline data are copied to be used in SQLPatch.

select plan_table_output from table ( dbms_xplan.display_cursor('a2n9kdch2ywg9', 0, 'ALL +OUTLINE') );

We can go on to readying SQLPatch. Write SQLPatch by using sql_id running with the wrong plan and outline data we've copied before.

DECLARE
    patch_name varchar2(200);
BEGIN
    patch_name := sys.dbms_sqldiag.create_sql_patch( 
        sql_id => '7jk33n4f4mpy9',
        hint_text => ' INDEX(@"SEL$1" "EMPLOYEES"@"SEL$1"                         
                       ("EMPLOYEES"."EMPLOYEE_ID")) '
        );
END;

Delete the plans from the Shared Pool before running the query. If there are any active sessions running with the same sql_id, kill them.

select inst_id, sql_id, child_number, address, hash_value, sql_plan_baseline,sql_patch from (g)v$sql where sql_id='7jk33n4f4mpy9';

exec sys.dbms_shared_pool.purge('000000006F655248,474601417','C');

To test the query, run it again.

select * from hr.employees;

Upon running the query again, look at v$sql view and see that the sql_patch is used.

select sql_id, child_number, hash_value, plan_hash_value, sql_plan_baseline, sql_patch from (g)v$sql where sql_id='7jk33n4f4mpy9';

When the query plan is examined again with the dbms_xplan.display_cursor, use of index is seen. We've successfully completed SQL Fixing with SQLPatch.

select plan_table_output from table ( dbms_xplan.display_cursor('7jk33n4f4mpy9', 1, 'ALL +OUTLINE') );

If you want to delete a written SQLPatch, delete it with the patch name as below.

select sql_id, child_number, hash_value, plan_hash_value, sql_plan_baseline, sql_patch from (g)v$sql where sql_id='7jk33n4f4mpy9';

exec sys.dbms_sqldiag.drop_sql_patch('SYS_SQLPTCH_0184af01c0c80001');

We've learned how to correct and fix wrong or corrupted plans with SPM and SQLPatch methods in Oracle with the help of hints.

---

Hope to see you in new posts, take care.