Well, this is written by Nikos Karagiannidis under his blog http://oradwstories.blogspot.in/2015/03/dealing-fast-with-change-of-execution.html
You can visit his blog and check the wonderful articles he has written about optimizers and how to tune them.
I have just re blogged the contents here for my easy references. If you want to export, import and evolve the sql baselines from one instance to another, then feel free to visit my earlier writing on this topic https://samappsdba.blogspot.in/2017/01/how-to-create-export-import-and-evolve.html
It's 10:00 AM and your nightly ETL flows are still running! Τhe daily reports must have been sent to the management from 8:00! From you investigation you find out that a specific statement (e.g., one that corresponds to an ETL mapping) is taking much too long than usual (it used to run in minutes and now it is running for 4 hours!). Your first thought is that some change in the execution plan must have taken place. How do you fix this? Or better, how do you fix this fast? (remember the management is waiting for the report)?
The Problem
SQL performance degradation due to a change of plan is one problem, fixing the problem fast so as to allow production ETL flows to unstack is another one, ensuring stability for the execution plan of a statement is yet another one! In a previous post, we discussed a method for detecting and verifying a change of plan. So we assume that we have verified the change of plan and we have also identified some older plan as the "good plan" that we would like to revert to.
In this post, we would like to deal with the case of how we can quickly revert to an older plan, (one that hopefully lies in the cursor cache or/and in the AWR repository) and thus fix the production problem efficiently and in a limited time-window. To this end, we will use the "SQL Plan Management" features of Oracle 11g, which apart from solving the above problem, will also ensure the required stability of the execution plan.
SQL Plan Management in the broader sense, comprises the methods Oracle offers for controlling the execution plan(s) of a SQL statement. In this case, we want a method for forcing a specific execution plan, (the one that we have identified as the "good plan") for the SQL statement with the performance problem and guarantee that the optimizer will not divert from the plan in future executions.
There are several ways with which you can force a specific plan in Oracle and guarantee plan stability. From 11g and onwards the recommended method for achieving this is called: SQL Plan Management with the use of SQL Plan Baselines. For more detailed info about SQL Plan Baselines read this previous post.
Please note that prior to SQL Plan Baselines (e.g. in versions before 11g) one could achieve the same result with one for the following:
A special type of SQL Profiles, as described in this previous post.
Stored Outlines (deprecated in Oracle 11g). See here or here.
In this post, we will show how we can achieve this (i.e., revert to an older better plan and stick with it) with SQL Plan Baselines. To this end, we will describe the necessary steps, give a full example and finally provide a script that includes all these steps and thus simplifies the whole process.
The Steps of How to Force a Plan with an SQL Plan Baseline
STEP 1: Identify the sql id of the statement in question
As trivial as it might sound, the very first step is to get the sql id of the problematic statement. We have described how you do this in this post.
STEP 2: Identify the target plan and where is located (Cursor Cache or AWR)
Once we have the sql id, then the next step is to identify which is the older "good plan" that we would like to revert to. To do this, we have to search the execution history for this specific sql id. The details of how you do this are in this post. Also, the next steps depend on whether the target plan is loaded in the cursor cache (i.e., the Library Cache in the Shared Pool). If it is, then we simply have to load the plan into an SQL Plan Baseline and we are over. Otherwise, the target plan must be loaded in AWR and the process is a bit more complex (see next). If it is in neither place, then there is no target plan to revert to and we can forget about it altogether! :-)
STEP 3: If the target plan is in the Cursor Cache then load it into an SQL Plan Baseline and finish
Now, if the target plan is loaded in the cursor cache then we only have to load it into an SQL Plan Baseline. This takes place with a simple call to function DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE, like this:
declare
i pls_integer;
begin
i:= DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id => '&sql_id',
plan_hash_value => &phv);
end;
That' s all there is to it. This call makes the target plan an "accepted plan" and thus the next time this sql id appears the optimizer will go with this plan.
STEP 4: If the target plan is in the AWR then identify the Begin and End AWR snapshot ids
If the plan is in the AWR, then first we have to identify the begin and end AWR snapshot ids of the AWR snapshots that have stored the target plan. This is easy by querying the view DBA_HIST_SQLSTAT (also you can use the script fs_awr.sql described in this post). At a bare minimum the query will be something like this:
SELECT snap_id
FROM dba_hist_sqlstat
WHERE
sql_id = '&sql_id' AND plan_hash_value = &phv
ORDER BY 1
STEP 5: (Target Plan in AWR) Load plan into an SQL Tuning Set (STS)
Once we have the begin and end snapshot ids, we have to load the plan into an SQL Tuning Set, because the SQL Plan Baseline will be loaded with our plan via this STS. Before we load the target plan into an STS, we first have to create one. For this we have to use a call like this:
-- create the STS
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'nikos_sql_tuning_set',
description => 'test loading a plan from AWR into an STS',
sqlset_owner => 'NIKOS');
END;
Now, that the STS is created we just have to loaded it with our target plan. This can be achieved with the following piece of code:
-- load the STS from AWR (you need to specify the begin and end snaphsots)
DECLARE
mycursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN mycursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY (
begin_snap => 850,
end_snap => 851,
basic_filter => 'sql_id = ''&sql_id'' AND plan_hash_value = &phv')) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => 'nikos_sql_tuning_set',
populate_cursor => mycursor);
END;
Note that we have to pass as input the begin and end snapshot ids as well as filtering information so as to get the specific plan for the specific sql id.
STEP 6: (Target Plan in AWR) Load plan into a SQL Plan Baseline from the STS
As the final step we have to create an SQL Plan Baseline and load it with the target plan from the STS. This takes place with a call like the following:
declare
i pls_integer;
begin
i := DBMS_SPM.LOAD_PLANS_FROM_SQLSET (
sqlset_name => 'nikos_sql_tuning_set',
sqlset_owner => 'NIKOS');
end;
And that is all! Now we have an SQL Plan Baseline loaded with our target plan as an "accepted" plan and whenever Oracle tries to execute the specific statement will choose this plan. We can verify that our plan is loaded in a SQL Plan Baseline by a simple query on DBA_SQL_PLAN_BASELINES view (or *_SQL_PLAN_BASELINES in general).
This way, we can very fast fix the problem with the problematic statement and allow the ETL flows to continue execution, so as to meet the business need. It goes without saying, that this is a nice and fast solution but does not gives us an excuse for omitting to find the root-cause for the change of plan. Now that we have got the management pressure off our back, we have all the required time to investigate the problem, as a true professional that we are.
Next we will give a full example of the above process.
An Example
Lets see an example of forcing a plan with an SQL Plan Baseline, So lets say we have a query that corresponds to a specific sql id and is executed with a specific execution plan that performance-wise makes us very happy. It is shown in Listing 1.
SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND c.cust_state_province = 'CA'
AND ch.channel_desc in ('Internet','Catalog')
AND t.calendar_quarter_desc IN ('1999-01','1999-02')
GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc
sql_id ==> frxcws72k2zt2
Plan hash value: 3426328104
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| Pstart| Pstop | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1315 (100)| | | | | |
| 1 | HASH GROUP BY | | 22 | 1315 (69)| | | 744K| 744K| 1131K (0)|
|* 2 | HASH JOIN | | 15M| 491 (17)| | | 1001K| 1001K| 1082K (0)|
|* 3 | HASH JOIN | | 5403 | 412 (1)| | | 862K| 862K| 1288K (0)|
|* 4 | TABLE ACCESS FULL | CUSTOMERS | 3249 | 406 (1)| | | | | |
|* 5 | HASH JOIN | | 11738 | 6 (34)| | | 855K| 855K| 1225K (0)|
| 6 | PART JOIN FILTER CREATE | :BF0000 | 181 | 2 (0)| | | | | |
|* 7 | TABLE ACCESS FULL | TIMES | 181 | 2 (0)| | | | | |
| 8 | PARTITION RANGE JOIN-FILTER| | 118K| 3 (34)|:BF0000|:BF0000| | | |
| 9 | TABLE ACCESS FULL | SALES | 118K| 3 (34)|:BF0000|:BF0000| | | |
|* 10 | TABLE ACCESS FULL | CHANNELS | 14296 | 2 (0)| | | | | |
----------------------------------------------------------------------------------------------------------------------
Listing 1: An example query and its execution plan.
One day though, something happened and the optimizer decided to go astray and choose a different plan. One that caused us a "management headache"!
In order, to invoke the change of plan for our running example, we will tamper with the statistics of the CUSTOMERS table and make it look as a cute small table:
sys@NIKOSDB> exec dbms_stats.set_table_stats('SH', 'CUSTOMERS', numrows=>1, numblks=>1)
PL/SQL procedure successfully completed.
Listing 2: Set the statistics of CUSTOMERS to invoke a change of plan.
As you see in Listing 2, we have manually set the statistics of the CUSTOMERS table so as to make the optimizer believe that it has only 1 row stored in 1 block. Now we execute once more the query and we see that a new plan has shown up. The new plan appears in Listing 3 below. Essentially the optimizer has changed the join order from SALES-->TIMES-->CUSTOMERS-->CHANNELS to SALES-->CUSTOMERS-->TIMES-->CHANNELS.
Plan hash value: 593420798
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| Pstart| Pstop | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 503 (100)| | | | | |
| 1 | HASH GROUP BY | | 22 | 503 (4)| | | 744K| 744K| 1147K (0)|
|* 2 | HASH JOIN | | 136K| 497 (3)| | | 888K| 888K| 382K (0)|
|* 3 | TABLE ACCESS FULL | CHANNELS | 964 | 2 (0)| | | | | |
|* 4 | HASH JOIN | | 707 | 494 (2)| | | 855K| 855K| 1266K (0)|
| 5 | PART JOIN FILTER CREATE | :BF0000 | 181 | 2 (0)| | | | | |
|* 6 | TABLE ACCESS FULL | TIMES | 181 | 2 (0)| | | | | |
|* 7 | HASH JOIN | | 7129 | 491 (2)| | | 862K| 862K| 1289K (0)|
|* 8 | TABLE ACCESS FULL | CUSTOMERS | 3341 | 2 (0)| | | | | |
| 9 | PARTITION RANGE JOIN-FILTER| | 118K| 488 (2)|:BF0000|:BF0000| | | |
| 10 | TABLE ACCESS FULL | SALES | 118K| 488 (2)|:BF0000|:BF0000| | | |
----------------------------------------------------------------------------------------------------------------------
Listing 3: The new plan for our query. This will play the role of the "bad" plan.
So lets see what we have so far:
We have a specific query. This query has an sql id and this is "frxcws72k2zt2". Our query used to run efficiently with plan 3426328104 and one day the plan changed and plan 593420798 appeared. So we have the current plan (593420798) which is causing the trouble (we will call it the "bad plan") and an older plan that we would like to revert to (3426328104, i.e., the "good plan").
Now, if we check the execution plan history for this sql id (as we have shown in this post ), then we can see the bad plan stored in the cursor cache, and the good plan stored in AWR. See Listing 4 below,
sys@NIKOSDB> @fs_plans
********************************************************
Find the available execution plans for a specific SQL_ID
********************************************************
Session altered.
Elapsed: 00:00:00.01
Enter value for sql_id: frxcws72k2zt2
SOURCE INST_ID SQL_ID PLAN_HASH_VALUE EXECUTIONS AVG_ET_SECS AVG_PX COST TIMESTAMP PARSING_SCHEMA_NAME
-------------------- ---------- --------------- --------------- --------------- ----------- ------- ----------- ------------------------- ----------------
gv$sqlarea_plan_hash 1 frxcws72k2zt2 593420798 23 .00 0 503 28-02-2015 16:27:50 SYS
dba_hist_sql_plan frxcws72k2zt2 3426328104 18 .00 0 1315 28-02-2015 14:35:08
dba_hist_sql_plan frxcws72k2zt2 593420798 8 .01 0 503 28-02-2015 15:09:56
dba_hist_sql_plan frxcws72k2zt2 1018976501 1 .35 0 40 28-02-2015 14:32:57
gv$sqlarea_plan_hash 1 frxcws72k2zt2 1720392155 2 1.14 0 12 28-02-2015 16:26:39 SYS
dba_hist_sql_plan frxcws72k2zt2 769839257 2 1.39 0 5 28-02-2015 14:33:59
dba_hist_sql_plan frxcws72k2zt2 1720392155 1 2.43 0 12 28-02-2015 15:06:21
gv$sqlarea_plan_hash 1 frxcws72k2zt2 769839257 2 2.79 0 5 28-02-2015 14:33:59 SYS
8 rows selected.
Listing 4:The execution plan history of our query. See the bad plan (in red) stored in the cursor cache (exposed via view gv$sqlarea_plan_hash) and the good plan (in green) stored in the AWR (exposed via view dba_hist_sql_plan).
Now we have a clear goal: we want to force the optimizer from now on to use the good plan instead of the bad plan. To this end we will use SQL Plan Baselines. In Listing 5, we call a script (sqltune_fplan.sql) that comprises all the aforementioned steps in order to achieve this goal and we depict the output of this script.
sys@NIKOSDB> @sqltune_fplan
Wrote file sqlplus_settings
Parameter 1:
SQL_ID (required)
Enter value for 1: frxcws72k2zt2
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 3426328104
Parameter 3:
Specify the number of days back from SYSDATE for which you want to search the AWR (default 10):
Parameter 4:
Loaded plan is used as a fixed plan Y/N (default N):
***INFO***: The requested plan was NOT found in cursor cache (GV$SQL).
***INFO***: The requested plan must be searched in AWR (DBA_HIST_SQLSTAT)
***INFO***: The requested plan was found in AWR.
***INFO***: The requested plan has been loaded into the SQL Plan Baseline. Lets check DBA_SQL_PLAN_BASELINES ...
SQL_ID SIGNATURE SQLH PN ENABLED ACCEPTED FIXED DESCRIPTION CREATOR ORIGIN PARSING_SCHEMA_NAME SQL_TEXT
--------------- ---------- ------------------------------ ------------------------------ ---------- ---------- ---------- ---------------------------------------------------------------------------------------------------- -------------------- -------------------- ------------------------------ --------------------------------------------------------------------------------------------------------------------
frxcws72k2zt2 1.2622E+19 SYS_SQL_af2aba07d70e92d8 SQL_PLAN_ayapu0zbhx4qsaf4f8cb0 YES YES NO SYS MANUAL-LOAD SYS SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
*** And the plan is the following ...
(note that the plan hash value might be different)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_af2aba07d70e92d8
SQL text: SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount FROM sh.sales s, sh.times t,
sh.customers c, sh.channels ch WHERE s.time_id = t.time_id AND
s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND
c.cust_state_province = 'CA' AND ch.channel_desc in
('Internet','Catalog') AND t.calendar_quarter_desc IN
('1999-01','1999-02') GROUP BY ch.channel_class, c.cust_city,
t.calendar_quarter_desc
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_ayapu0zbhx4qsaf4f8cb0 Plan id: 2941226160
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 3426328104
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 84 | 507 (4)| 00:00:07 | | |
| 1 | HASH GROUP BY | | 1 | 84 | 507 (4)| 00:00:07 | | |
|* 2 | HASH JOIN | | 1 | 84 | 506 (4)| 00:00:07 | | |
|* 3 | HASH JOIN | | 1 | 63 | 503 (4)| 00:00:07 | | |
|* 4 | TABLE ACCESS FULL | CUSTOMERS | 1 | 26 | 2 (0)| 00:00:01 | | |
|* 5 | HASH JOIN | | 62 | 2294 | 501 (4)| 00:00:07 | | |
| 6 | PART JOIN FILTER CREATE | :BF0000 | 1 | 16 | 2 (0)| 00:00:01 | | |
|* 7 | TABLE ACCESS FULL | TIMES | 1 | 16 | 2 (0)| 00:00:01 | | |
| 8 | PARTITION RANGE JOIN-FILTER| | 918K| 18M| 494 (3)| 00:00:06 |:BF0000|:BF0000|
| 9 | TABLE ACCESS FULL | SALES | 918K| 18M| 494 (3)| 00:00:06 |:BF0000|:BF0000|
|* 10 | TABLE ACCESS FULL | CHANNELS | 1 | 21 | 2 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
3 - access("S"."CUST_ID"="C"."CUST_ID")
4 - filter("C"."CUST_STATE_PROVINCE"='CA')
5 - access("S"."TIME_ID"="T"."TIME_ID")
7 - filter("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02')
10 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
Listing 5: Output of the script sqltune_fplan.sql which forces a plan for a sql id with the use of SQL Plan Baselines.
The input provided to this script is simply the sql id of our query as well as the plan hash value of the good plan. For the rest input parameters, we just pass the default values. See some important parts from the output of this scrirpt highlighted in yellow. The good plan was not found in the cursor cache but was found in AWR. After we load the good plan in the SQL Plan Baseline, the script queries view DBA_SQL_PLAN_BASELINES in order to verify that our plan was indeed loaded. We can see that our plan has been loaded in the SQL Plan Baseline as an "ACCEPTED" plan, which means that will be chosen from the optimizer the next time this sql id appears. Finally the script calls function DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE to display this accepted plan in detail.
We can recognize our "good plan" not only from the plan hash value (3426328104 in our case - note: that the plan hash value of a plan in an SQL Plan Baseline might be different but that does not mean that it is a different plan, it is just that the hash value is computed differently) but also from the join order also, which returned to SALES-->TIMES-->CUSTOMERS-->CHANNELS.
If we attempt to rerun the query and retrieve the execution plan with a call to DBMS_XPLAN.DISPLAY_CURSOR, then we can see also a note at the end saying that our plan from the SQL Plan Baseline was chosen. See this in Listing 6 below.
Plan hash value: 3426328104
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| Pstart| Pstop | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 507 (100)| | | | | |
| 1 | HASH GROUP BY | | 1 | 507 (4)| | | 744K| 744K| 1134K (0)|
|* 2 | HASH JOIN | | 1 | 506 (4)| | | 1001K| 1001K| 1075K (0)|
|* 3 | HASH JOIN | | 1 | 503 (4)| | | 862K| 862K| 1294K (0)|
|* 4 | TABLE ACCESS FULL | CUSTOMERS | 1 | 2 (0)| | | | | |
|* 5 | HASH JOIN | | 62 | 501 (4)| | | 855K| 855K| 1198K (0)|
| 6 | PART JOIN FILTER CREATE | :BF0000 | 1 | 2 (0)| | | | | |
|* 7 | TABLE ACCESS FULL | TIMES | 1 | 2 (0)| | | | | |
| 8 | PARTITION RANGE JOIN-FILTER| | 918K| 494 (3)|:BF0000|:BF0000| | | |
| 9 | TABLE ACCESS FULL | SALES | 918K| 494 (3)|:BF0000|:BF0000| | | |
|* 10 | TABLE ACCESS FULL | CHANNELS | 1 | 2 (0)| | | | | |
----------------------------------------------------------------------------------------------------------------------
Note
-----
- SQL plan baseline SQL_PLAN_ayapu0zbhx4qsaf4f8cb0 used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
Listing 6:Verify that the "good plan" from the SQL Plan Baseline is used.
So we have achieved our goal which was:
1. to force the good plan for our query fast and easy.
2. to guarantee plan stability for our query.
script:-sqltune_fplan.sql
**********************
-- ----------------------------------------------------------------------------------------------
-- Fix the plan for a specific sql_id. Loads the plan into an SQL Plan Baseline (thus works for 11g and above only)
-- Input the sql_id and the requested plan, as well as the number of days back to search AWR for this plan (if it is
-- not loaded in the cursor cache).
--
-- DESCRIPTION
-- This script uses the concept of the SQL plan baseline in order to fix the plan of a specific sql id.
-- "Fixing" here is not with the strict meaning of SQL plan baselines "fixed plans". The script loads
-- the input plan into an SQL Plan Baseline (if one does not exist then Oracle will create one) as an
-- "enabled" and "accepted" plan. The plan (by default) will not be "fixed", which means that we will allow to the
-- SQL Plan Baseline to evolve (see http://oradwstories.blogspot.gr/2014/07/sql-plan-management-sql-plan-baselines.html
-- for more info), unless you provide "Y" in the FIXED input parameter. The plan must be loaded either in the cursor cache, or in AWR for the script to work.
--
-- PRE-REQUISITES
-- 1. Have in cache or AWR the input plan.
-- 2. Have in cache the input plan
--
-- PARAMETERS
-- 1. SQL_ID (required)
-- 2. PLAN_HASH_VALUE (required)
-- 3. DAYS_BACK (optional, default 10)
-- 4. FIXED (optional, default N)
--
-- (C) 2015 Nikos Karagiannidis - http://oradwstories.blogspot.com
-- ----------------------------------------------------------------------------------------------
@sqlplus_settings_store
set serveroutput on
set timing off
set verify off
set feedback off
-- get the input
PRO
PRO Parameter 1:
PRO SQL_ID (required)
DEF sql_id = '&1';
PRO
PRO Parameter 2:
PRO PLAN_HASH_VALUE (required)
DEF plan_hash_value = '&2';
PRO
PRO Parameter 3:
ACCEPT days_back NUMBER DEFAULT 10 PROMPT 'Specify the number of days back from SYSDATE for which you want to search the AWR (default 10):'
PRO
PRO Parameter 4:
ACCEPT fixed CHAR PROMPT 'Loaded plan is used as a fixed plan Y/N (default N):'
VARIABLE g_app_error_flag NUMBER
EXEC :g_app_error_flag := 0;
-- Check if the plan is in the CURSOR CACHE
VARIABLE g_plan_found_in_cache NUMBER
EXEC :g_plan_found_in_cache := 0
BEGIN
select count(*) into :g_plan_found_in_cache
from gv$sql
where 1=1
and sql_id = trim('&&sql_id')
and plan_hash_value = &&plan_hash_value;
if(:g_plan_found_in_cache > 0) then
DBMS_OUTPUT.PUT_LINE(chr(10)||'***INFO***: The requested plan was found in cursor cache (GV$SQL).');
else
DBMS_OUTPUT.PUT_LINE(chr(10)||'***INFO***: The requested plan was NOT found in cursor cache (GV$SQL).');
end if;
END;
/
var g_sqlid VARCHAR2(30)
exec :g_sqlid := trim('&&sql_id')
var g_phv NUMBER
exec :g_phv := &&plan_hash_value
var g_fixed VARCHAR2(15)
exec select decode(upper(trim(nvl('&&fixed', 'N'))), 'Y', 'YES', 'N', 'NO', 'invalid value') into :g_fixed from dual
-- if the plan is in the cursor cache, then load it directly in a SQL plan baseline
declare
l_i pls_integer;
--g_sqlid varchar2(30) := trim('&&sql_id');
--g_phv number := &&plan_hash_value;
begin
-- DEBUG -------------------------------------
--DBMS_OUTPUT.PUT_LINE('***DEBUG***: fixed = '||:g_fixed);
----------------------------------------------
if(:g_plan_found_in_cache > 0) then
-- load plan from cursor cache
l_i:= DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id => :g_sqlid,
plan_hash_value => :g_phv,
fixed => :g_fixed);
if (l_i <> 1) then
--:g_app_error_flag := 1;
--RAISE_APPLICATION_ERROR(-20100, '***ERROR***: Problem in loading plan from cursor cache into SQL plan baseline.'||l_i||' number of plans have been loaded.');
DBMS_OUTPUT.PUT_LINE(chr(10)||'***ERROR***: Problem in loading plan from cursor cache into SQL plan baseline.'||l_i||' number of plans have been loaded.');
DBMS_OUTPUT.PUT_LINE(chr(10)||'***INFO***: We will continue search in AWR ...');
-- set flags to alllow search in AWR
:g_plan_found_in_cache := 0;
:g_app_error_flag := 0;
end if;
end if;
end;
/
-- if the plan is not in the cursor cache, then we need to find it in AWR
begin
if(:g_plan_found_in_cache = 0 AND :g_app_error_flag = 0) then
DBMS_OUTPUT.PUT_LINE(chr(10)||'***INFO***: The requested plan must be searched in AWR (DBA_HIST_SQLSTAT)');
end if;
end;
/
var g_plan_found_in_awr number
exec :g_plan_found_in_awr := 0
var g_ps_name VARCHAR2(60)
var g_bsnap number
var g_esnap number
begin
if(:g_plan_found_in_cache = 0 AND :g_app_error_flag = 0) then
-- need to get a Begin and an End snapshot id, need to get the parsing schema name also to be used as the STS owner
select min(snap_id) begin_snap, max(snap_id) end_snap, min(PARSING_SCHEMA_NAME) ps_name into :g_bsnap, :g_esnap, :g_ps_name
from DBA_HIST_SQLSTAT a left outer join
DBA_HIST_SNAPSHOT b using (SNAP_ID)
where 1=1
and sql_id = :g_sqlid
and plan_hash_value = :g_phv
and b.begin_interval_time > sysdate - &&days_back;
if(:g_bsnap is null or :g_esnap is null) then
:g_app_error_flag := 1;
RAISE_APPLICATION_ERROR(-20101, '***ERROR***: Problem in finding plan in DBA_HIST_SQLSTATS');
end if;
-- in order to avoid an: ORA-13767: End snapshot ID must be greater than begin snapshot ID.
-- if the snap_ids are the same, then lower the begin snap id by one
if(:g_bsnap = :g_esnap) then
:g_bsnap := :g_bsnap - 1;
end if;
SELECT count(*) into :g_plan_found_in_awr
FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY (
begin_snap => :g_bsnap,
end_snap => :g_esnap,
basic_filter => 'sql_id = '''||:g_sqlid||''' AND plan_hash_value = '||:g_phv)) p;
if (:g_plan_found_in_awr = 0) then
:g_app_error_flag := 1;
RAISE_APPLICATION_ERROR(-20102, '***ERROR***: Problem in finding plan in AWR (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY)');
else
DBMS_OUTPUT.PUT_LINE(chr(10)||'***INFO***: The requested plan was found in AWR.');
end if;
end if;
end;
/
-- if plan is found in AWR, then load it via an sql tuning set (STS)
declare
l_mycursor DBMS_SQLTUNE.SQLSET_CURSOR;
l_stsname VARCHAR2(100) := 'sts_for_sqlid_'||:g_sqlid;
l_i pls_integer;
begin
if (:g_plan_found_in_awr > 0 AND :g_app_error_flag = 0) then
-- create an STS (SQL Tuning Set) for this
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => l_stsname,
description => 'STS for loading a plan into a SQL plan baseline from AWR',
sqlset_owner => :g_ps_name);
-- load the STS from AWR (you need to specify the begin and end snaphsots)
OPEN l_mycursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY (
begin_snap => :g_bsnap,
end_snap => :g_esnap,
basic_filter => 'sql_id = '''||:g_sqlid||''' AND plan_hash_value = '||:g_phv)) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => l_stsname,
populate_cursor => l_mycursor,
sqlset_owner => :g_ps_name);
-- Create the Baseline from the STS
l_i := DBMS_SPM.LOAD_PLANS_FROM_SQLSET (
sqlset_name => l_stsname,
sqlset_owner => :g_ps_name,
fixed => :g_fixed);
if (l_i <> 1) then
:g_app_error_flag := 1;
RAISE_APPLICATION_ERROR(-20103, '***ERROR***: Problem in loading plan from sql tuning set (STS) into SQL plan baseline.'||l_i||' number of plans have been loaded.');
end if;
end if;
end;
/
begin
if(:g_app_error_flag = 0) then
DBMS_OUTPUT.PUT_LINE(chr(10)||'***INFO***: The requested plan has been loaded into the SQL Plan Baseline. Lets check DBA_SQL_PLAN_BASELINES ...');
end if;
end;
/
-- Check creation of the SQL Plan baseline.
-- I need the sql signature in order to query dba_sql_plan_baseline. I will also
-- get the sql handle and plan name, in order to be able to retrieve the execution plan from dbms_xplan.DISPLAY_SQL_PLAN_BASELINE
-- Alternatively call: @@fspbaseline '&&sql_id'
var g_signature NUMBER
declare
------------------------------------------------------------------
-- get the signature for input sql_id (with no force matching)
------------------------------------------------------------------
function sqlid_2_signature(sql_id_in in varchar2) return number
is
l_sql_text clob;
l_sign number;
begin
-- get sql_text from memory
if(:g_plan_found_in_cache > 0) then
BEGIN
SELECT REPLACE(sql_fulltext, CHR(00), ' ') INTO l_sql_text
FROM gv$sqlarea
WHERE sql_id = sql_id_in
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS THEN
:g_app_error_flag := 1;
l_sql_text := NULL;
RAISE_APPLICATION_ERROR(-20104,'***ERROR***: Getting target sql_text from memory: '||SQLERRM);
END;
-- get sql_text from awr
elsif (:g_plan_found_in_awr > 0) then
BEGIN
SELECT REPLACE(sql_text, CHR(00), ' ') INTO l_sql_text
FROM dba_hist_sqltext
WHERE sql_id = sql_id_in
AND sql_text IS NOT NULL
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS THEN
:g_app_error_flag := 1;
l_sql_text := NULL;
RAISE_APPLICATION_ERROR(-20105,'***ERROR***: Getting target sql_text from awr: '||SQLERRM);
END;
-- something has gone wrong!
else
:g_app_error_flag := 1;
l_sql_text := NULL;
RAISE_APPLICATION_ERROR(-20106, '***ERROR***: Error while trying to get the sql text, Cannot find sqltext neither in memory nor in AWR!');
end if;
if(l_sql_text IS NULL) then
:g_app_error_flag := 1;
RAISE_APPLICATION_ERROR(-20107, '***ERROR***: Error while trying to get the sql text, Cannot find sqltext neither in memory nor in AWR!');
end if;
-- get the signature for this sql_id (with no force matching)
l_sign := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE (sql_text =>l_sql_text, force_match => 0);
return l_sign;
end;
begin
if(:g_app_error_flag = 0) then
:g_signature := sqlid_2_signature(:g_sqlid);
end if;
end;
/
-- return the baseline
col ENABLED format a10
col ACCEPTED format a10
col FIXED format a10
col REPRODUCED format a10
col DESCRIPTION format a100
col sql_text format a130 trunc
col CREATOR format a20
col ORIGIN format a20
col PARSING_SCHEMA_NAME format a30
column sqlh new_value sql_handle
column pn new_value plan_name
select *
from (
select
:g_sqlid sql_id,
SIGNATURE,
SQL_HANDLE sqlh,
PLAN_NAME pn,
ENABLED,
ACCEPTED,
FIXED,
--REPRODUCED, -- check https://blogs.oracle.com/optimizer/entry/how_does_sql_plan_management
DESCRIPTION,
CREATOR,
ORIGIN,
PARSING_SCHEMA_NAME,
SQL_TEXT
from dba_sql_plan_baselines
where
SIGNATURE = :g_signature
AND creator = USER
AND origin = 'MANUAL-LOAD'
order by created desc, FIXED DESC, ACCEPTED DESC
) t
where
rownum = 1
/
PROMPT
PROMPT *** And the plan is the following ...
PROMPT (note that the plan hash value might be different)
PROMPT
-- Check plan in the SQL Plan baseline
set linesize 9999
set pagesize 999
select * from table( dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(sql_handle =>'&&sql_handle', plan_name => '&&plan_name', format=>'TYPICAL'))
/
UNDEF sql_id
UNDEF plan_hash_value
UNDEF ref_date
UNDEF days_back
UNDEF sql_handle
UNDEF plan_name
UNDEF fixed
@sqlplus_get_settings
****************************
script-fs_plans.sql
***************
Find the available execution plans for a specific SQL_ID
--
-- Note that the AVG_ET_SECS (average elpased time) will not be accurate for parallel queries.
-- The ELAPSED_TIME column contains the sum of all parallel slaves. So the
-- script divides the value by the number of PX slaves used which gives an
-- approximation.
--
-- Note also that if parallel slaves are spread across multiple nodes on
-- a RAC database the PX_SERVERS_EXECUTIONS column will not be set.
--
-- author: Nikos Karagiannidis (C) 2013 - http://oradwstories.blogspot.com
---------------------------------------------------------------------------------------------
prompt
prompt ********************************************************
prompt Find the available execution plans for a specific SQL_ID
prompt ********************************************************
prompt
set linesize 999
col avg_et_secs justify right format 9999999.99
col cost justify right format 9999999999
col timestamp justify center format a25
col parsing_schema_name justify center format a30
col inst_id format 999999999
col executions_total format 99999999999999999
col executions format 99999999999999
alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
select 'gv$sqlarea_plan_hash' source, INST_ID,
SQL_ID, PLAN_HASH_VALUE,
executions,
round(elapsed_time/decode(nvl(executions,0),0,1,executions)/1e6/
decode(px_servers_executions,0,1,px_servers_executions)/decode(nvl(executions,0),0,1,executions),2) avg_et_secs,
px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_px,
optimizer_cost cost,
LAST_LOAD_TIME timestamp,
parsing_schema_name --FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME, SQL_PROFILE
from gv$sqlarea_plan_hash
where sql_id = nvl(trim('&&sql_id'),sql_id)
UNION
SELECT 'dba_hist_sql_plan' source, null INST_ID,
t1.sql_id sql_id, t1.plan_hash_value plan_hash_value,
t2.executions_total,
t2.avg_et_secs avg_et_secs,
t2.avg_px,
t1.cost cost,
t1.timestamp timestamp,
NULL parsing_schema_name
FROM dba_hist_sql_plan t1,
(
SELECT sql_id, plan_hash_value,
max(executions_total) executions_total, --round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6,2) avg_et_secs
round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6/
decode(SUM(px_servers_execs_total),0,1,SUM(px_servers_execs_total))/decode(SUM(executions_total),0,1,SUM(executions_total)),2) avg_et_secs,
SUM(px_servers_execs_total)/decode(SUM(executions_total),0,1,SUM(executions_total)) avg_px
FROM dba_hist_sqlstat
WHERE
executions_total > 0
GROUP BY sql_id, plan_hash_value
) t2
WHERE
t1.sql_id = nvl(TRIM('&sql_id.'), t1.sql_id)
AND t1.depth = 0
AND t1.sql_id = t2.sql_id(+)
AND t1.plan_hash_value = t2.plan_hash_value(+)
order by avg_et_secs, cost --timestamp desc
/
undef sql_id
You can visit his blog and check the wonderful articles he has written about optimizers and how to tune them.
I have just re blogged the contents here for my easy references. If you want to export, import and evolve the sql baselines from one instance to another, then feel free to visit my earlier writing on this topic https://samappsdba.blogspot.in/2017/01/how-to-create-export-import-and-evolve.html
It's 10:00 AM and your nightly ETL flows are still running! Τhe daily reports must have been sent to the management from 8:00! From you investigation you find out that a specific statement (e.g., one that corresponds to an ETL mapping) is taking much too long than usual (it used to run in minutes and now it is running for 4 hours!). Your first thought is that some change in the execution plan must have taken place. How do you fix this? Or better, how do you fix this fast? (remember the management is waiting for the report)?
The Problem
SQL performance degradation due to a change of plan is one problem, fixing the problem fast so as to allow production ETL flows to unstack is another one, ensuring stability for the execution plan of a statement is yet another one! In a previous post, we discussed a method for detecting and verifying a change of plan. So we assume that we have verified the change of plan and we have also identified some older plan as the "good plan" that we would like to revert to.
In this post, we would like to deal with the case of how we can quickly revert to an older plan, (one that hopefully lies in the cursor cache or/and in the AWR repository) and thus fix the production problem efficiently and in a limited time-window. To this end, we will use the "SQL Plan Management" features of Oracle 11g, which apart from solving the above problem, will also ensure the required stability of the execution plan.
SQL Plan Management in the broader sense, comprises the methods Oracle offers for controlling the execution plan(s) of a SQL statement. In this case, we want a method for forcing a specific execution plan, (the one that we have identified as the "good plan") for the SQL statement with the performance problem and guarantee that the optimizer will not divert from the plan in future executions.
There are several ways with which you can force a specific plan in Oracle and guarantee plan stability. From 11g and onwards the recommended method for achieving this is called: SQL Plan Management with the use of SQL Plan Baselines. For more detailed info about SQL Plan Baselines read this previous post.
Please note that prior to SQL Plan Baselines (e.g. in versions before 11g) one could achieve the same result with one for the following:
A special type of SQL Profiles, as described in this previous post.
Stored Outlines (deprecated in Oracle 11g). See here or here.
In this post, we will show how we can achieve this (i.e., revert to an older better plan and stick with it) with SQL Plan Baselines. To this end, we will describe the necessary steps, give a full example and finally provide a script that includes all these steps and thus simplifies the whole process.
The Steps of How to Force a Plan with an SQL Plan Baseline
STEP 1: Identify the sql id of the statement in question
As trivial as it might sound, the very first step is to get the sql id of the problematic statement. We have described how you do this in this post.
STEP 2: Identify the target plan and where is located (Cursor Cache or AWR)
Once we have the sql id, then the next step is to identify which is the older "good plan" that we would like to revert to. To do this, we have to search the execution history for this specific sql id. The details of how you do this are in this post. Also, the next steps depend on whether the target plan is loaded in the cursor cache (i.e., the Library Cache in the Shared Pool). If it is, then we simply have to load the plan into an SQL Plan Baseline and we are over. Otherwise, the target plan must be loaded in AWR and the process is a bit more complex (see next). If it is in neither place, then there is no target plan to revert to and we can forget about it altogether! :-)
STEP 3: If the target plan is in the Cursor Cache then load it into an SQL Plan Baseline and finish
Now, if the target plan is loaded in the cursor cache then we only have to load it into an SQL Plan Baseline. This takes place with a simple call to function DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE, like this:
declare
i pls_integer;
begin
i:= DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id => '&sql_id',
plan_hash_value => &phv);
end;
That' s all there is to it. This call makes the target plan an "accepted plan" and thus the next time this sql id appears the optimizer will go with this plan.
STEP 4: If the target plan is in the AWR then identify the Begin and End AWR snapshot ids
If the plan is in the AWR, then first we have to identify the begin and end AWR snapshot ids of the AWR snapshots that have stored the target plan. This is easy by querying the view DBA_HIST_SQLSTAT (also you can use the script fs_awr.sql described in this post). At a bare minimum the query will be something like this:
SELECT snap_id
FROM dba_hist_sqlstat
WHERE
sql_id = '&sql_id' AND plan_hash_value = &phv
ORDER BY 1
STEP 5: (Target Plan in AWR) Load plan into an SQL Tuning Set (STS)
Once we have the begin and end snapshot ids, we have to load the plan into an SQL Tuning Set, because the SQL Plan Baseline will be loaded with our plan via this STS. Before we load the target plan into an STS, we first have to create one. For this we have to use a call like this:
-- create the STS
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'nikos_sql_tuning_set',
description => 'test loading a plan from AWR into an STS',
sqlset_owner => 'NIKOS');
END;
Now, that the STS is created we just have to loaded it with our target plan. This can be achieved with the following piece of code:
-- load the STS from AWR (you need to specify the begin and end snaphsots)
DECLARE
mycursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN mycursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY (
begin_snap => 850,
end_snap => 851,
basic_filter => 'sql_id = ''&sql_id'' AND plan_hash_value = &phv')) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => 'nikos_sql_tuning_set',
populate_cursor => mycursor);
END;
Note that we have to pass as input the begin and end snapshot ids as well as filtering information so as to get the specific plan for the specific sql id.
STEP 6: (Target Plan in AWR) Load plan into a SQL Plan Baseline from the STS
As the final step we have to create an SQL Plan Baseline and load it with the target plan from the STS. This takes place with a call like the following:
declare
i pls_integer;
begin
i := DBMS_SPM.LOAD_PLANS_FROM_SQLSET (
sqlset_name => 'nikos_sql_tuning_set',
sqlset_owner => 'NIKOS');
end;
And that is all! Now we have an SQL Plan Baseline loaded with our target plan as an "accepted" plan and whenever Oracle tries to execute the specific statement will choose this plan. We can verify that our plan is loaded in a SQL Plan Baseline by a simple query on DBA_SQL_PLAN_BASELINES view (or *_SQL_PLAN_BASELINES in general).
This way, we can very fast fix the problem with the problematic statement and allow the ETL flows to continue execution, so as to meet the business need. It goes without saying, that this is a nice and fast solution but does not gives us an excuse for omitting to find the root-cause for the change of plan. Now that we have got the management pressure off our back, we have all the required time to investigate the problem, as a true professional that we are.
Next we will give a full example of the above process.
An Example
Lets see an example of forcing a plan with an SQL Plan Baseline, So lets say we have a query that corresponds to a specific sql id and is executed with a specific execution plan that performance-wise makes us very happy. It is shown in Listing 1.
SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND c.cust_state_province = 'CA'
AND ch.channel_desc in ('Internet','Catalog')
AND t.calendar_quarter_desc IN ('1999-01','1999-02')
GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc
sql_id ==> frxcws72k2zt2
Plan hash value: 3426328104
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| Pstart| Pstop | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1315 (100)| | | | | |
| 1 | HASH GROUP BY | | 22 | 1315 (69)| | | 744K| 744K| 1131K (0)|
|* 2 | HASH JOIN | | 15M| 491 (17)| | | 1001K| 1001K| 1082K (0)|
|* 3 | HASH JOIN | | 5403 | 412 (1)| | | 862K| 862K| 1288K (0)|
|* 4 | TABLE ACCESS FULL | CUSTOMERS | 3249 | 406 (1)| | | | | |
|* 5 | HASH JOIN | | 11738 | 6 (34)| | | 855K| 855K| 1225K (0)|
| 6 | PART JOIN FILTER CREATE | :BF0000 | 181 | 2 (0)| | | | | |
|* 7 | TABLE ACCESS FULL | TIMES | 181 | 2 (0)| | | | | |
| 8 | PARTITION RANGE JOIN-FILTER| | 118K| 3 (34)|:BF0000|:BF0000| | | |
| 9 | TABLE ACCESS FULL | SALES | 118K| 3 (34)|:BF0000|:BF0000| | | |
|* 10 | TABLE ACCESS FULL | CHANNELS | 14296 | 2 (0)| | | | | |
----------------------------------------------------------------------------------------------------------------------
Listing 1: An example query and its execution plan.
One day though, something happened and the optimizer decided to go astray and choose a different plan. One that caused us a "management headache"!
In order, to invoke the change of plan for our running example, we will tamper with the statistics of the CUSTOMERS table and make it look as a cute small table:
sys@NIKOSDB> exec dbms_stats.set_table_stats('SH', 'CUSTOMERS', numrows=>1, numblks=>1)
PL/SQL procedure successfully completed.
Listing 2: Set the statistics of CUSTOMERS to invoke a change of plan.
As you see in Listing 2, we have manually set the statistics of the CUSTOMERS table so as to make the optimizer believe that it has only 1 row stored in 1 block. Now we execute once more the query and we see that a new plan has shown up. The new plan appears in Listing 3 below. Essentially the optimizer has changed the join order from SALES-->TIMES-->CUSTOMERS-->CHANNELS to SALES-->CUSTOMERS-->TIMES-->CHANNELS.
Plan hash value: 593420798
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| Pstart| Pstop | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 503 (100)| | | | | |
| 1 | HASH GROUP BY | | 22 | 503 (4)| | | 744K| 744K| 1147K (0)|
|* 2 | HASH JOIN | | 136K| 497 (3)| | | 888K| 888K| 382K (0)|
|* 3 | TABLE ACCESS FULL | CHANNELS | 964 | 2 (0)| | | | | |
|* 4 | HASH JOIN | | 707 | 494 (2)| | | 855K| 855K| 1266K (0)|
| 5 | PART JOIN FILTER CREATE | :BF0000 | 181 | 2 (0)| | | | | |
|* 6 | TABLE ACCESS FULL | TIMES | 181 | 2 (0)| | | | | |
|* 7 | HASH JOIN | | 7129 | 491 (2)| | | 862K| 862K| 1289K (0)|
|* 8 | TABLE ACCESS FULL | CUSTOMERS | 3341 | 2 (0)| | | | | |
| 9 | PARTITION RANGE JOIN-FILTER| | 118K| 488 (2)|:BF0000|:BF0000| | | |
| 10 | TABLE ACCESS FULL | SALES | 118K| 488 (2)|:BF0000|:BF0000| | | |
----------------------------------------------------------------------------------------------------------------------
Listing 3: The new plan for our query. This will play the role of the "bad" plan.
So lets see what we have so far:
We have a specific query. This query has an sql id and this is "frxcws72k2zt2". Our query used to run efficiently with plan 3426328104 and one day the plan changed and plan 593420798 appeared. So we have the current plan (593420798) which is causing the trouble (we will call it the "bad plan") and an older plan that we would like to revert to (3426328104, i.e., the "good plan").
Now, if we check the execution plan history for this sql id (as we have shown in this post ), then we can see the bad plan stored in the cursor cache, and the good plan stored in AWR. See Listing 4 below,
sys@NIKOSDB> @fs_plans
********************************************************
Find the available execution plans for a specific SQL_ID
********************************************************
Session altered.
Elapsed: 00:00:00.01
Enter value for sql_id: frxcws72k2zt2
SOURCE INST_ID SQL_ID PLAN_HASH_VALUE EXECUTIONS AVG_ET_SECS AVG_PX COST TIMESTAMP PARSING_SCHEMA_NAME
-------------------- ---------- --------------- --------------- --------------- ----------- ------- ----------- ------------------------- ----------------
gv$sqlarea_plan_hash 1 frxcws72k2zt2 593420798 23 .00 0 503 28-02-2015 16:27:50 SYS
dba_hist_sql_plan frxcws72k2zt2 3426328104 18 .00 0 1315 28-02-2015 14:35:08
dba_hist_sql_plan frxcws72k2zt2 593420798 8 .01 0 503 28-02-2015 15:09:56
dba_hist_sql_plan frxcws72k2zt2 1018976501 1 .35 0 40 28-02-2015 14:32:57
gv$sqlarea_plan_hash 1 frxcws72k2zt2 1720392155 2 1.14 0 12 28-02-2015 16:26:39 SYS
dba_hist_sql_plan frxcws72k2zt2 769839257 2 1.39 0 5 28-02-2015 14:33:59
dba_hist_sql_plan frxcws72k2zt2 1720392155 1 2.43 0 12 28-02-2015 15:06:21
gv$sqlarea_plan_hash 1 frxcws72k2zt2 769839257 2 2.79 0 5 28-02-2015 14:33:59 SYS
8 rows selected.
Listing 4:The execution plan history of our query. See the bad plan (in red) stored in the cursor cache (exposed via view gv$sqlarea_plan_hash) and the good plan (in green) stored in the AWR (exposed via view dba_hist_sql_plan).
Now we have a clear goal: we want to force the optimizer from now on to use the good plan instead of the bad plan. To this end we will use SQL Plan Baselines. In Listing 5, we call a script (sqltune_fplan.sql) that comprises all the aforementioned steps in order to achieve this goal and we depict the output of this script.
sys@NIKOSDB> @sqltune_fplan
Wrote file sqlplus_settings
Parameter 1:
SQL_ID (required)
Enter value for 1: frxcws72k2zt2
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 3426328104
Parameter 3:
Specify the number of days back from SYSDATE for which you want to search the AWR (default 10):
Parameter 4:
Loaded plan is used as a fixed plan Y/N (default N):
***INFO***: The requested plan was NOT found in cursor cache (GV$SQL).
***INFO***: The requested plan must be searched in AWR (DBA_HIST_SQLSTAT)
***INFO***: The requested plan was found in AWR.
***INFO***: The requested plan has been loaded into the SQL Plan Baseline. Lets check DBA_SQL_PLAN_BASELINES ...
SQL_ID SIGNATURE SQLH PN ENABLED ACCEPTED FIXED DESCRIPTION CREATOR ORIGIN PARSING_SCHEMA_NAME SQL_TEXT
--------------- ---------- ------------------------------ ------------------------------ ---------- ---------- ---------- ---------------------------------------------------------------------------------------------------- -------------------- -------------------- ------------------------------ --------------------------------------------------------------------------------------------------------------------
frxcws72k2zt2 1.2622E+19 SYS_SQL_af2aba07d70e92d8 SQL_PLAN_ayapu0zbhx4qsaf4f8cb0 YES YES NO SYS MANUAL-LOAD SYS SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount
*** And the plan is the following ...
(note that the plan hash value might be different)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_af2aba07d70e92d8
SQL text: SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount FROM sh.sales s, sh.times t,
sh.customers c, sh.channels ch WHERE s.time_id = t.time_id AND
s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND
c.cust_state_province = 'CA' AND ch.channel_desc in
('Internet','Catalog') AND t.calendar_quarter_desc IN
('1999-01','1999-02') GROUP BY ch.channel_class, c.cust_city,
t.calendar_quarter_desc
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_ayapu0zbhx4qsaf4f8cb0 Plan id: 2941226160
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 3426328104
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 84 | 507 (4)| 00:00:07 | | |
| 1 | HASH GROUP BY | | 1 | 84 | 507 (4)| 00:00:07 | | |
|* 2 | HASH JOIN | | 1 | 84 | 506 (4)| 00:00:07 | | |
|* 3 | HASH JOIN | | 1 | 63 | 503 (4)| 00:00:07 | | |
|* 4 | TABLE ACCESS FULL | CUSTOMERS | 1 | 26 | 2 (0)| 00:00:01 | | |
|* 5 | HASH JOIN | | 62 | 2294 | 501 (4)| 00:00:07 | | |
| 6 | PART JOIN FILTER CREATE | :BF0000 | 1 | 16 | 2 (0)| 00:00:01 | | |
|* 7 | TABLE ACCESS FULL | TIMES | 1 | 16 | 2 (0)| 00:00:01 | | |
| 8 | PARTITION RANGE JOIN-FILTER| | 918K| 18M| 494 (3)| 00:00:06 |:BF0000|:BF0000|
| 9 | TABLE ACCESS FULL | SALES | 918K| 18M| 494 (3)| 00:00:06 |:BF0000|:BF0000|
|* 10 | TABLE ACCESS FULL | CHANNELS | 1 | 21 | 2 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
3 - access("S"."CUST_ID"="C"."CUST_ID")
4 - filter("C"."CUST_STATE_PROVINCE"='CA')
5 - access("S"."TIME_ID"="T"."TIME_ID")
7 - filter("T"."CALENDAR_QUARTER_DESC"='1999-01' OR "T"."CALENDAR_QUARTER_DESC"='1999-02')
10 - filter("CH"."CHANNEL_DESC"='Catalog' OR "CH"."CHANNEL_DESC"='Internet')
Listing 5: Output of the script sqltune_fplan.sql which forces a plan for a sql id with the use of SQL Plan Baselines.
The input provided to this script is simply the sql id of our query as well as the plan hash value of the good plan. For the rest input parameters, we just pass the default values. See some important parts from the output of this scrirpt highlighted in yellow. The good plan was not found in the cursor cache but was found in AWR. After we load the good plan in the SQL Plan Baseline, the script queries view DBA_SQL_PLAN_BASELINES in order to verify that our plan was indeed loaded. We can see that our plan has been loaded in the SQL Plan Baseline as an "ACCEPTED" plan, which means that will be chosen from the optimizer the next time this sql id appears. Finally the script calls function DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE to display this accepted plan in detail.
We can recognize our "good plan" not only from the plan hash value (3426328104 in our case - note: that the plan hash value of a plan in an SQL Plan Baseline might be different but that does not mean that it is a different plan, it is just that the hash value is computed differently) but also from the join order also, which returned to SALES-->TIMES-->CUSTOMERS-->CHANNELS.
If we attempt to rerun the query and retrieve the execution plan with a call to DBMS_XPLAN.DISPLAY_CURSOR, then we can see also a note at the end saying that our plan from the SQL Plan Baseline was chosen. See this in Listing 6 below.
Plan hash value: 3426328104
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| Pstart| Pstop | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 507 (100)| | | | | |
| 1 | HASH GROUP BY | | 1 | 507 (4)| | | 744K| 744K| 1134K (0)|
|* 2 | HASH JOIN | | 1 | 506 (4)| | | 1001K| 1001K| 1075K (0)|
|* 3 | HASH JOIN | | 1 | 503 (4)| | | 862K| 862K| 1294K (0)|
|* 4 | TABLE ACCESS FULL | CUSTOMERS | 1 | 2 (0)| | | | | |
|* 5 | HASH JOIN | | 62 | 501 (4)| | | 855K| 855K| 1198K (0)|
| 6 | PART JOIN FILTER CREATE | :BF0000 | 1 | 2 (0)| | | | | |
|* 7 | TABLE ACCESS FULL | TIMES | 1 | 2 (0)| | | | | |
| 8 | PARTITION RANGE JOIN-FILTER| | 918K| 494 (3)|:BF0000|:BF0000| | | |
| 9 | TABLE ACCESS FULL | SALES | 918K| 494 (3)|:BF0000|:BF0000| | | |
|* 10 | TABLE ACCESS FULL | CHANNELS | 1 | 2 (0)| | | | | |
----------------------------------------------------------------------------------------------------------------------
Note
-----
- SQL plan baseline SQL_PLAN_ayapu0zbhx4qsaf4f8cb0 used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
Listing 6:Verify that the "good plan" from the SQL Plan Baseline is used.
So we have achieved our goal which was:
1. to force the good plan for our query fast and easy.
2. to guarantee plan stability for our query.
script:-sqltune_fplan.sql
**********************
-- ----------------------------------------------------------------------------------------------
-- Fix the plan for a specific sql_id. Loads the plan into an SQL Plan Baseline (thus works for 11g and above only)
-- Input the sql_id and the requested plan, as well as the number of days back to search AWR for this plan (if it is
-- not loaded in the cursor cache).
--
-- DESCRIPTION
-- This script uses the concept of the SQL plan baseline in order to fix the plan of a specific sql id.
-- "Fixing" here is not with the strict meaning of SQL plan baselines "fixed plans". The script loads
-- the input plan into an SQL Plan Baseline (if one does not exist then Oracle will create one) as an
-- "enabled" and "accepted" plan. The plan (by default) will not be "fixed", which means that we will allow to the
-- SQL Plan Baseline to evolve (see http://oradwstories.blogspot.gr/2014/07/sql-plan-management-sql-plan-baselines.html
-- for more info), unless you provide "Y" in the FIXED input parameter. The plan must be loaded either in the cursor cache, or in AWR for the script to work.
--
-- PRE-REQUISITES
-- 1. Have in cache or AWR the input plan.
-- 2. Have in cache the input plan
--
-- PARAMETERS
-- 1. SQL_ID (required)
-- 2. PLAN_HASH_VALUE (required)
-- 3. DAYS_BACK (optional, default 10)
-- 4. FIXED (optional, default N)
--
-- (C) 2015 Nikos Karagiannidis - http://oradwstories.blogspot.com
-- ----------------------------------------------------------------------------------------------
@sqlplus_settings_store
set serveroutput on
set timing off
set verify off
set feedback off
-- get the input
PRO
PRO Parameter 1:
PRO SQL_ID (required)
DEF sql_id = '&1';
PRO
PRO Parameter 2:
PRO PLAN_HASH_VALUE (required)
DEF plan_hash_value = '&2';
PRO
PRO Parameter 3:
ACCEPT days_back NUMBER DEFAULT 10 PROMPT 'Specify the number of days back from SYSDATE for which you want to search the AWR (default 10):'
PRO
PRO Parameter 4:
ACCEPT fixed CHAR PROMPT 'Loaded plan is used as a fixed plan Y/N (default N):'
VARIABLE g_app_error_flag NUMBER
EXEC :g_app_error_flag := 0;
-- Check if the plan is in the CURSOR CACHE
VARIABLE g_plan_found_in_cache NUMBER
EXEC :g_plan_found_in_cache := 0
BEGIN
select count(*) into :g_plan_found_in_cache
from gv$sql
where 1=1
and sql_id = trim('&&sql_id')
and plan_hash_value = &&plan_hash_value;
if(:g_plan_found_in_cache > 0) then
DBMS_OUTPUT.PUT_LINE(chr(10)||'***INFO***: The requested plan was found in cursor cache (GV$SQL).');
else
DBMS_OUTPUT.PUT_LINE(chr(10)||'***INFO***: The requested plan was NOT found in cursor cache (GV$SQL).');
end if;
END;
/
var g_sqlid VARCHAR2(30)
exec :g_sqlid := trim('&&sql_id')
var g_phv NUMBER
exec :g_phv := &&plan_hash_value
var g_fixed VARCHAR2(15)
exec select decode(upper(trim(nvl('&&fixed', 'N'))), 'Y', 'YES', 'N', 'NO', 'invalid value') into :g_fixed from dual
-- if the plan is in the cursor cache, then load it directly in a SQL plan baseline
declare
l_i pls_integer;
--g_sqlid varchar2(30) := trim('&&sql_id');
--g_phv number := &&plan_hash_value;
begin
-- DEBUG -------------------------------------
--DBMS_OUTPUT.PUT_LINE('***DEBUG***: fixed = '||:g_fixed);
----------------------------------------------
if(:g_plan_found_in_cache > 0) then
-- load plan from cursor cache
l_i:= DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id => :g_sqlid,
plan_hash_value => :g_phv,
fixed => :g_fixed);
if (l_i <> 1) then
--:g_app_error_flag := 1;
--RAISE_APPLICATION_ERROR(-20100, '***ERROR***: Problem in loading plan from cursor cache into SQL plan baseline.'||l_i||' number of plans have been loaded.');
DBMS_OUTPUT.PUT_LINE(chr(10)||'***ERROR***: Problem in loading plan from cursor cache into SQL plan baseline.'||l_i||' number of plans have been loaded.');
DBMS_OUTPUT.PUT_LINE(chr(10)||'***INFO***: We will continue search in AWR ...');
-- set flags to alllow search in AWR
:g_plan_found_in_cache := 0;
:g_app_error_flag := 0;
end if;
end if;
end;
/
-- if the plan is not in the cursor cache, then we need to find it in AWR
begin
if(:g_plan_found_in_cache = 0 AND :g_app_error_flag = 0) then
DBMS_OUTPUT.PUT_LINE(chr(10)||'***INFO***: The requested plan must be searched in AWR (DBA_HIST_SQLSTAT)');
end if;
end;
/
var g_plan_found_in_awr number
exec :g_plan_found_in_awr := 0
var g_ps_name VARCHAR2(60)
var g_bsnap number
var g_esnap number
begin
if(:g_plan_found_in_cache = 0 AND :g_app_error_flag = 0) then
-- need to get a Begin and an End snapshot id, need to get the parsing schema name also to be used as the STS owner
select min(snap_id) begin_snap, max(snap_id) end_snap, min(PARSING_SCHEMA_NAME) ps_name into :g_bsnap, :g_esnap, :g_ps_name
from DBA_HIST_SQLSTAT a left outer join
DBA_HIST_SNAPSHOT b using (SNAP_ID)
where 1=1
and sql_id = :g_sqlid
and plan_hash_value = :g_phv
and b.begin_interval_time > sysdate - &&days_back;
if(:g_bsnap is null or :g_esnap is null) then
:g_app_error_flag := 1;
RAISE_APPLICATION_ERROR(-20101, '***ERROR***: Problem in finding plan in DBA_HIST_SQLSTATS');
end if;
-- in order to avoid an: ORA-13767: End snapshot ID must be greater than begin snapshot ID.
-- if the snap_ids are the same, then lower the begin snap id by one
if(:g_bsnap = :g_esnap) then
:g_bsnap := :g_bsnap - 1;
end if;
SELECT count(*) into :g_plan_found_in_awr
FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY (
begin_snap => :g_bsnap,
end_snap => :g_esnap,
basic_filter => 'sql_id = '''||:g_sqlid||''' AND plan_hash_value = '||:g_phv)) p;
if (:g_plan_found_in_awr = 0) then
:g_app_error_flag := 1;
RAISE_APPLICATION_ERROR(-20102, '***ERROR***: Problem in finding plan in AWR (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY)');
else
DBMS_OUTPUT.PUT_LINE(chr(10)||'***INFO***: The requested plan was found in AWR.');
end if;
end if;
end;
/
-- if plan is found in AWR, then load it via an sql tuning set (STS)
declare
l_mycursor DBMS_SQLTUNE.SQLSET_CURSOR;
l_stsname VARCHAR2(100) := 'sts_for_sqlid_'||:g_sqlid;
l_i pls_integer;
begin
if (:g_plan_found_in_awr > 0 AND :g_app_error_flag = 0) then
-- create an STS (SQL Tuning Set) for this
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => l_stsname,
description => 'STS for loading a plan into a SQL plan baseline from AWR',
sqlset_owner => :g_ps_name);
-- load the STS from AWR (you need to specify the begin and end snaphsots)
OPEN l_mycursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY (
begin_snap => :g_bsnap,
end_snap => :g_esnap,
basic_filter => 'sql_id = '''||:g_sqlid||''' AND plan_hash_value = '||:g_phv)) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => l_stsname,
populate_cursor => l_mycursor,
sqlset_owner => :g_ps_name);
-- Create the Baseline from the STS
l_i := DBMS_SPM.LOAD_PLANS_FROM_SQLSET (
sqlset_name => l_stsname,
sqlset_owner => :g_ps_name,
fixed => :g_fixed);
if (l_i <> 1) then
:g_app_error_flag := 1;
RAISE_APPLICATION_ERROR(-20103, '***ERROR***: Problem in loading plan from sql tuning set (STS) into SQL plan baseline.'||l_i||' number of plans have been loaded.');
end if;
end if;
end;
/
begin
if(:g_app_error_flag = 0) then
DBMS_OUTPUT.PUT_LINE(chr(10)||'***INFO***: The requested plan has been loaded into the SQL Plan Baseline. Lets check DBA_SQL_PLAN_BASELINES ...');
end if;
end;
/
-- Check creation of the SQL Plan baseline.
-- I need the sql signature in order to query dba_sql_plan_baseline. I will also
-- get the sql handle and plan name, in order to be able to retrieve the execution plan from dbms_xplan.DISPLAY_SQL_PLAN_BASELINE
-- Alternatively call: @@fspbaseline '&&sql_id'
var g_signature NUMBER
declare
------------------------------------------------------------------
-- get the signature for input sql_id (with no force matching)
------------------------------------------------------------------
function sqlid_2_signature(sql_id_in in varchar2) return number
is
l_sql_text clob;
l_sign number;
begin
-- get sql_text from memory
if(:g_plan_found_in_cache > 0) then
BEGIN
SELECT REPLACE(sql_fulltext, CHR(00), ' ') INTO l_sql_text
FROM gv$sqlarea
WHERE sql_id = sql_id_in
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS THEN
:g_app_error_flag := 1;
l_sql_text := NULL;
RAISE_APPLICATION_ERROR(-20104,'***ERROR***: Getting target sql_text from memory: '||SQLERRM);
END;
-- get sql_text from awr
elsif (:g_plan_found_in_awr > 0) then
BEGIN
SELECT REPLACE(sql_text, CHR(00), ' ') INTO l_sql_text
FROM dba_hist_sqltext
WHERE sql_id = sql_id_in
AND sql_text IS NOT NULL
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS THEN
:g_app_error_flag := 1;
l_sql_text := NULL;
RAISE_APPLICATION_ERROR(-20105,'***ERROR***: Getting target sql_text from awr: '||SQLERRM);
END;
-- something has gone wrong!
else
:g_app_error_flag := 1;
l_sql_text := NULL;
RAISE_APPLICATION_ERROR(-20106, '***ERROR***: Error while trying to get the sql text, Cannot find sqltext neither in memory nor in AWR!');
end if;
if(l_sql_text IS NULL) then
:g_app_error_flag := 1;
RAISE_APPLICATION_ERROR(-20107, '***ERROR***: Error while trying to get the sql text, Cannot find sqltext neither in memory nor in AWR!');
end if;
-- get the signature for this sql_id (with no force matching)
l_sign := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE (sql_text =>l_sql_text, force_match => 0);
return l_sign;
end;
begin
if(:g_app_error_flag = 0) then
:g_signature := sqlid_2_signature(:g_sqlid);
end if;
end;
/
-- return the baseline
col ENABLED format a10
col ACCEPTED format a10
col FIXED format a10
col REPRODUCED format a10
col DESCRIPTION format a100
col sql_text format a130 trunc
col CREATOR format a20
col ORIGIN format a20
col PARSING_SCHEMA_NAME format a30
column sqlh new_value sql_handle
column pn new_value plan_name
select *
from (
select
:g_sqlid sql_id,
SIGNATURE,
SQL_HANDLE sqlh,
PLAN_NAME pn,
ENABLED,
ACCEPTED,
FIXED,
--REPRODUCED, -- check https://blogs.oracle.com/optimizer/entry/how_does_sql_plan_management
DESCRIPTION,
CREATOR,
ORIGIN,
PARSING_SCHEMA_NAME,
SQL_TEXT
from dba_sql_plan_baselines
where
SIGNATURE = :g_signature
AND creator = USER
AND origin = 'MANUAL-LOAD'
order by created desc, FIXED DESC, ACCEPTED DESC
) t
where
rownum = 1
/
PROMPT
PROMPT *** And the plan is the following ...
PROMPT (note that the plan hash value might be different)
PROMPT
-- Check plan in the SQL Plan baseline
set linesize 9999
set pagesize 999
select * from table( dbms_xplan.DISPLAY_SQL_PLAN_BASELINE(sql_handle =>'&&sql_handle', plan_name => '&&plan_name', format=>'TYPICAL'))
/
UNDEF sql_id
UNDEF plan_hash_value
UNDEF ref_date
UNDEF days_back
UNDEF sql_handle
UNDEF plan_name
UNDEF fixed
@sqlplus_get_settings
****************************
script-fs_plans.sql
***************
Find the available execution plans for a specific SQL_ID
--
-- Note that the AVG_ET_SECS (average elpased time) will not be accurate for parallel queries.
-- The ELAPSED_TIME column contains the sum of all parallel slaves. So the
-- script divides the value by the number of PX slaves used which gives an
-- approximation.
--
-- Note also that if parallel slaves are spread across multiple nodes on
-- a RAC database the PX_SERVERS_EXECUTIONS column will not be set.
--
-- author: Nikos Karagiannidis (C) 2013 - http://oradwstories.blogspot.com
---------------------------------------------------------------------------------------------
prompt
prompt ********************************************************
prompt Find the available execution plans for a specific SQL_ID
prompt ********************************************************
prompt
set linesize 999
col avg_et_secs justify right format 9999999.99
col cost justify right format 9999999999
col timestamp justify center format a25
col parsing_schema_name justify center format a30
col inst_id format 999999999
col executions_total format 99999999999999999
col executions format 99999999999999
alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
select 'gv$sqlarea_plan_hash' source, INST_ID,
SQL_ID, PLAN_HASH_VALUE,
executions,
round(elapsed_time/decode(nvl(executions,0),0,1,executions)/1e6/
decode(px_servers_executions,0,1,px_servers_executions)/decode(nvl(executions,0),0,1,executions),2) avg_et_secs,
px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_px,
optimizer_cost cost,
LAST_LOAD_TIME timestamp,
parsing_schema_name --FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME, SQL_PROFILE
from gv$sqlarea_plan_hash
where sql_id = nvl(trim('&&sql_id'),sql_id)
UNION
SELECT 'dba_hist_sql_plan' source, null INST_ID,
t1.sql_id sql_id, t1.plan_hash_value plan_hash_value,
t2.executions_total,
t2.avg_et_secs avg_et_secs,
t2.avg_px,
t1.cost cost,
t1.timestamp timestamp,
NULL parsing_schema_name
FROM dba_hist_sql_plan t1,
(
SELECT sql_id, plan_hash_value,
max(executions_total) executions_total, --round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6,2) avg_et_secs
round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6/
decode(SUM(px_servers_execs_total),0,1,SUM(px_servers_execs_total))/decode(SUM(executions_total),0,1,SUM(executions_total)),2) avg_et_secs,
SUM(px_servers_execs_total)/decode(SUM(executions_total),0,1,SUM(executions_total)) avg_px
FROM dba_hist_sqlstat
WHERE
executions_total > 0
GROUP BY sql_id, plan_hash_value
) t2
WHERE
t1.sql_id = nvl(TRIM('&sql_id.'), t1.sql_id)
AND t1.depth = 0
AND t1.sql_id = t2.sql_id(+)
AND t1.plan_hash_value = t2.plan_hash_value(+)
order by avg_et_secs, cost --timestamp desc
/
undef sql_id
