From Oracle 11g, there exists a new feature -
SQL Plan Management, using which you can alter the execution plan of a query. This post aims to offer a simpler explanation of this works. To begin, let us create a simple table in a schema and pump millions of rows into it. This will provide us a test case that we can work upon. Use the SQL statement below for the purpose:Create a test table and find good / bad SQL plans
-- drop table if already exists
DROP TABLE T1
-- Create a table with some test rows
CREATE TABLE T1 AS SELECT * FROM ALL_OBJECTS
-- Create two indexes
CREATE INDEX T_IDX_ID ON T1(object_id)
CREATE INDEX T_IDX_TEMP ON T1(TEMPORARY)
-- Gather statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'T1' )
The need to alter the execution plan for a SQL can arise for different reasons (bad plan, wrong hint). In this post, let us assume that the developer wrote a query on the application that forces a wrong index. The bad query with the plan is provided below:
SELECT /*+ INDEX(A T_IDX_ID) */ TEMPORARY,COUNT(*) FROM T1 A WHERE A.TEMPORARY IN ('Y','N')GROUP BY TEMPORARY
Plan hash value: 1128521145
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4 | 895K (1)| 02:59:02 |
| 1 | HASH GROUP BY | | 2 | 4 | 895K (1)| 02:59:02 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 892K| 1743K| 895K (1)| 02:59:01 |
| 3 | INDEX FULL SCAN | T_IDX_ID | 892K| | 2033 (1)| 00:00:25 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."TEMPORARY"='N' OR "A"."TEMPORARY"='Y')
To optimize, we would need the hint to be removed or replaced - the good query (or plan) we need is shown below:
SELECT /*+ INDEX(A T_IDX_TEMP) */ TEMPORARY,COUNT(*) FROM T1 A WHERE A.TEMPORARY IN ('Y','N') GROUP BY TEMPORARY
Plan hash value: 4053788979
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4 | 1423 (1)| 00:00:18 |
| 1 | SORT GROUP BY NOSORT| | 2 | 4 | 1423 (1)| 00:00:18 |
| 2 | INLIST ITERATOR | | | | | |
|* 3 | INDEX RANGE SCAN | T_IDX_TEMP | 892K| 1743K| 1423 (1)| 00:00:18 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."TEMPORARY"='N' OR "A"."TEMPORARY"='Y')
Let us see how to get this execution plan swap done without modifying the application, and through SQL Plan Management. The step by step guide is shown below:
Step -1: Fetch Bad Execution Plan Details
Find the SQL for which you want to change the execution plan from
V$SQL view. We find the bad SQL and get the SQL_ID and PLAN_HASH_VALUE for it. See SQLs below for an example;/* Find the bad SQL */
SELECT * FROM V$SQL where SQL_TEXT LIKE '%T_IDX_ID%'
/* Get SQL_ID, PLAN_HASH_VALUE */
SELECT SQL_ID,PLAN_HASH_VALUE,SQL_TEXT FROM V$SQL WHERE SQL_ID='91rv7hc4qp95s'
Step-2: Baseline the Bad SQL Explain Plan
We need to baseline this plan in order to change it. We use the method
load_plans_from_cursor_cache method in the package DBMS_SPM for this purpose.You have to pass the SQL_ID from the above step to this call.SQL> variable cnt number;
SQL> EXEC :cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'91rv7hc4qp95s');
PL/SQL procedure successfully completed
cnt
---------
1
You can query
DBA_SQL_PLAN_BASELINE to check the status of the load. List of columns on this view is provided below:SQL> desc dba_sql_plan_baselines
Name Type Nullable Default Comments
------------------- ------------- -------- ------- --------------------------------------------------------
SIGNATURE NUMBER unique SQL identifier generated from normalized SQL text
SQL_HANDLE VARCHAR2(30) unique SQL identifier in string form as a search key
SQL_TEXT CLOB un-normalized SQL text
PLAN_NAME VARCHAR2(30) unique plan identifier in string form as a search key
CREATOR VARCHAR2(30) Y user who created the plan baseline
ORIGIN VARCHAR2(14) Y how plan baseline was created
PARSING_SCHEMA_NAME VARCHAR2(30) Y name of parsing schema
DESCRIPTION VARCHAR2(500) Y text description provided for plan baseline
VERSION VARCHAR2(64) Y database version at time of plan baseline creation
CREATED TIMESTAMP(6) time when plan baseline was created
LAST_MODIFIED TIMESTAMP(6) Y time when plan baseline was last modified
LAST_EXECUTED TIMESTAMP(6) Y time when plan baseline was last executed
LAST_VERIFIED TIMESTAMP(6) Y time when plan baseline was last verified
ENABLED VARCHAR2(3) Y enabled status of plan baseline
ACCEPTED VARCHAR2(3) Y accepted status of plan baseline
FIXED VARCHAR2(3) Y fixed status of plan baseline
AUTOPURGE VARCHAR2(3) Y auto-purge status of plan baseline
OPTIMIZER_COST NUMBER Y plan baseline optimizer cost
MODULE VARCHAR2(48) Y application module name
ACTION VARCHAR2(32) Y application action
EXECUTIONS NUMBER Y number of plan baseline executions
ELAPSED_TIME NUMBER Y total elapse time
CPU_TIME NUMBER Y total CPU time
BUFFER_GETS NUMBER Y total buffer gets
DISK_READS NUMBER Y total disk reads
DIRECT_WRITES NUMBER Y total direct writes
ROWS_PROCESSED NUMBER Y total rows processed
FETCHES NUMBER Y total number of fetches
END_OF_FETCH_COUNT NUMBER Y total number of full fetches
Step-3: Disable the bad SQL Plan
We know for sure that the plan this query uses is a really bad one and we are looking for alternatives. So, we need to disable this plan first. The column
ENABLED needs to be set to NO for this purpose. You can get the baseline plan disabled by using ALTER_SQL_PLAN_BASELINE method in DBMS_SPM package. You would have to pass SQL_HANDLE for this, which you can obtain from DBA_SQL_PLAN_BASELINE view.var t number;
exec :t := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_6b9b1f88682ce2c7',attribute_name=>'enabled',attribute_value=>'NO');
Step-4: Fetch Modified SQL Plan Details
Now, we have to run the good query once and get the
SQL_ID and PLAN_HASH_VALUE for the it. This is shown below:/* This is the good SQL */
SELECT /*+ INDEX(A T_IDX_TEMP) */ TEMPORARY,COUNT(*) FROM T1 A WHERE A.TEMPORARY IN ('Y','N') GROUP BY TEMPORARY
/* Find the Good SQL */
SELECT * FROM V$SQL where SQL_TEXT LIKE '%T_IDX_TEMP%'
/* Get SQL_ID, PLAN_HASH_VALUE */
SELECT SQL_ID,PLAN_HASH_VALUE,SQL_TEXT FROM V$SQL WHERE SQL_ID='8awzxfgbu92y3'
--8awzxfgbu92y3,4053788979
Step-5: Replace Execution Plan with Modified version
Once you retrieve the details of the modified plan, you can add this to
DBA_SQL_PLAN_BASELINE and attach the old SQL_HANDLE value to it. This way, the execution plan for the old query will refer to the new plan.SQL> exec :t := dbms_spm.load_plans_from_cursor_cache( sql_id => '8awzxfgbu92y3',plan_hash_value => 4053788979,sql_handle => 'SYS_SQL_6b9b1f88682ce2c7');
PL/SQL procedure successfully completed
t
---------
1
If you query the view for SQL Handle, you will hit two rows. One with a disabled plan and another with the replaced plan. And that is all..you have now tweaked the SQL to use the right execution plan. Let us see this in action now.
Test the Plan Swap:
Test results for the bad SQL following the plan change is provided below:
EXPLAIN PLAN FOR
SELECT /*+ INDEX(A T_IDX_ID) */ TEMPORARY,COUNT(*) FROM T1 A WHERE A.TEMPORARY IN ('Y','N')GROUP BY TEMPORARY
Plan hash value: 4053788979
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4 | 1423 (1)| 00:00:18 |
| 1 | SORT GROUP BY NOSORT| | 2 | 4 | 1423 (1)| 00:00:18 |
| 2 | INLIST ITERATOR | | | | | |
|* 3 | INDEX RANGE SCAN | T_IDX_TEMP | 892K| 1743K| 1423 (1)| 00:00:18 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."TEMPORARY"='N' OR "A"."TEMPORARY"='Y')
Note
-----
- SQL plan baseline "SQL_PLAN_6r6szj1n2tsq7aea75811" used for this statement
The last line "Note" is the key - that confirms that the query is using a modified plan now.Let us explore some more ways to use this package in the next post.
No comments:
Post a Comment