When you have a specific SQL statement that is been running in your database (e.g., it might be part of nightly ETL flow that is used for loading your Data Warehouse), then how can you get a list of all available execution plans that the optimizer has chosen for this specific statement?
Each statement is uniquely identified by an SQL_ID and whenever the statement undergoes a hard parsing, then an execution plan is generated, which is identified uniquely by a hash value, called plan_hash_value.
Ideally, for a specific statement the same execution plan must be generated each time is being hard parsed. However in practice, there might be more than one execution plans, each one with a different cost and resulting in different execution times. The generation of a different execution plan might be due to a number of reasons, such as object statistics, optimizer environment (e.g., init parameters relevant to the optimizer), or the version of the Oracle software.
Lets see an example:
Assume the following SQL statement:
------------------------------------------------------------------------------------------------------------
nikos@NIKOSDB> get mysql
1 SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
2 SUM(s.amount_sold) sales_amount
3 FROM sales s, times t, customers c, channels ch
4 WHERE s.time_id = t.time_id
5 AND s.cust_id = c.cust_id
6 AND s.channel_id = ch.channel_id
7 AND c.cust_state_province = 'CA'
8 AND ch.channel_desc in ('Internet','Catalog')
9 AND t.calendar_quarter_desc IN ('1999-01','1999-02')
10* GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc
nikos@NIKOSDB> /
CHANNEL_CLASS CUST_CITY CALENDA SALES_AMOUNT
-------------------- ------------------------------ ------- ------------
Indirect Quartzhill 1999-01 987.3
Indirect Arbuckle 1999-02 241.2
Indirect Legrand 1999-02 18.66
Indirect Los Angeles 1999-01 1886.19
Indirect Cloverdale 1999-02 266.28
Indirect Pescadero 1999-02 298.44
Indirect Legrand 1999-01 26.32
Indirect Pescadero 1999-01 26.32
Indirect San Mateo 1999-01 8754.59
Indirect Arbuckle 1999-01 270.08
Indirect Montara 1999-01 289.07
Indirect Los Angeles 1999-02 2128.59
Indirect Pala 1999-02 936.62
Indirect El Sobrante 1999-02 3744.03
Indirect El Sobrante 1999-01 5392.34
Indirect Cloverdale 1999-01 52.64
Indirect San Francisco 1999-02 11257
Indirect San Francisco 1999-01 3058.27
Indirect Pala 1999-01 3263.93
Indirect San Mateo 1999-02 21399.42
Indirect Montara 1999-02 1618.01
Indirect Quartzhill 1999-02 412.83
22 rows selected.
Elapsed: 00:00:00.26
------------------------------------------------------------------------------------------------------------
nikos@NIKOSDB> @fs
Enter value for sql_text: %SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc%
Enter value for sql_id:
PARSING_SCHEMA_NAME SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------------------------ ------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
SH 9j820km30uynd 0 1062259086 1 .97 19,317 SELECT ch.channel_class, c.cust_city, t.calendar_quarter_des
c, SUM(s.amount_sold) sales_amount FROM sales s, times t,
customers c, 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 ('I
nternet','Catalog') AND t.calendar_quarter_desc IN ('1999-
01','1999-02') GROUP BY ch.channel_class, c.cust_city, t.cal
endar_quarter_desc
SH 9j820km30uynd 1 2000181989 1 .19 2,641 SELECT ch.channel_class, c.cust_city, t.calendar_quarter_des
c, SUM(s.amount_sold) sales_amount FROM sales s, times t,
customers c, 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 ('I
nternet','Catalog') AND t.calendar_quarter_desc IN ('1999-
01','1999-02') GROUP BY ch.channel_class, c.cust_city, t.cal
endar_quarter_desc
Elapsed: 00:00:00.09
------------------------------------------------------------------------------------------------------------
nikos@NIKOSDB> host cat fs.sql
col sql_text for a60 wrap
set verify off
set pagesize 999
set lines 999
col username format a13
col prog format a22
col sid format 999
col child_number format 99999 heading CHILD
col ocategory format a10
col avg_etime format 9,999,999.99
col avg_pio format 9,999,999.99
col avg_lio format 999,999,999
col etime format 9,999,999.99
select PARSING_SCHEMA_NAME, sql_id, child_number, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
sql_text
from gv$sql s
where upper(sql_text) like upper(nvl('&sql_text',sql_text))
and sql_text not like '%from gv$sql where upper(sql_text) like nvl(%'
and sql_id like nvl('&sql_id',sql_id)
order by 1, 2, 3
/
------------------------------------------------------------------------------------------------------------
nikos@NIKOSDB> @fs_plans
********************************************************
Find the available execution plans for a specific SQL_ID
********************************************************
Session altered.
Elapsed: 00:00:00.00
Enter value for sql_id: 9j820km30uynd
SOURCE INST_ID SQL_ID PLAN_HASH_VALUE AVG_ET_SECS AVG_PX COST TIMESTAMP PARSING_SCHEMA_NAME
-------------------- ---------- ------------- --------------- ----------- ---------- ----------- ------------------------- ----------------
dba_hist_sql_plan 9j820km30uynd 1858418313 .03 0 1113 22-08-2013 21:46:21
dba_hist_sql_plan 9j820km30uynd 2093608901 .05 0 558 22-08-2013 21:45:53
dba_hist_sql_plan 9j820km30uynd 2000181989 .05 0 929 22-08-2013 21:46:11
dba_hist_sql_plan 9j820km30uynd 3208336303 .09 0 558 22-08-2013 22:09:03
dba_hist_sql_plan 9j820km30uynd 127002096 .14 0 558 22-08-2013 22:28:46
gv$sqlarea_plan_hash 1 9j820km30uynd 2000181989 .19 0 929 27-08-2013 16:55:17 SH
dba_hist_sql_plan 9j820km30uynd 1062259086 .97 0 558 27-08-2013 10:17:37
gv$sqlarea_plan_hash 1 9j820km30uynd 1062259086 .97 0 558 27-08-2013 10:17:37 SH
dba_hist_sql_plan 9j820km30uynd 632852794 1.05 0 558 25-08-2013 20:47:17
dba_hist_sql_plan 9j820km30uynd 3018881530 1.14 0 558 26-08-2013 18:15:31
10 rows selected.
Elapsed: 00:00:00.07
The script that we have used is the following:
------------------------------------------------------------------------------------------------------------
nikos@NIKOSDB> host cat 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: 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
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,
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.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, --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
/
undef sql_id
------------------------------------------------------------------------------------------------------------
nikos@NIKOSDB> host cat xplan.sql
set linesize 999
set pagesize 999
select * from table( dbms_xplan.display_cursor('&sql_id', '&child_number', 'ALL ALLSTATS LAST'));
nikos@NIKOSDB> @xplan
Enter value for sql_id: 9j820km30uynd
Enter value for child_number:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9j820km30uynd, child number 0
-------------------------------------
SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount FROM sales s, times t, customers c,
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 hash value: 1062259086
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 558 (100)| | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | |
| 2 | LOAD AS SELECT | | | | | | | | 264K| 264K| 264K (0)|
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 406 (1)| 00:00:05 | | | | | |
| 4 | HASH GROUP BY | | 254 | 18542 | 153 (2)| 00:00:02 | | | 744K| 744K| 1154K (0)|
|* 5 | HASH JOIN | | 254 | 18542 | 129 (2)| 00:00:02 | | | 855K| 855K| 1248K (0)|
|* 6 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 18 (0)| 00:00:01 | | | | | |
|* 7 | HASH JOIN | | 254 | 14478 | 110 (1)| 00:00:02 | | | 744K| 744K| 1289K (0)|
|* 8 | HASH JOIN | | 254 | 10668 | 107 (0)| 00:00:02 | | | 825K| 825K| 376K (0)|
|* 9 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | 3 (0)| 00:00:01 | | | | | |
| 10 | PARTITION RANGE SUBQUERY | | 254 | 5334 | 104 (0)| 00:00:02 |KEY(SQ)|KEY(SQ)| | | |
| 11 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 254 | 5334 | 104 (0)| 00:00:02 |KEY(SQ)|KEY(SQ)| | | |
| 12 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | | |
| 13 | BITMAP AND | | | | | | | | | | |
| 14 | BITMAP MERGE | | | | | | | | 1024K| 512K| 4096 (0)|
| 15 | BITMAP KEY ITERATION | | | | | | | | | | |
| 16 | BUFFER SORT | | | | | | | | 73728 | 73728 | |
|* 17 | TABLE ACCESS FULL | CHANNELS | 2 | 26 | 3 (0)| 00:00:01 | | | | | |
|* 18 | BITMAP INDEX RANGE SCAN | SALES_CHANNEL_BIX | | | | |KEY(SQ)|KEY(SQ)| | | |
| 19 | BITMAP MERGE | | | | | | | | 1024K| 512K|37888 (0)|
| 20 | BITMAP KEY ITERATION | | | | | | | | | | |
| 21 | BUFFER SORT | | | | | | | | 73728 | 73728 | |
|* 22 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 18 (0)| 00:00:01 | | | | | |
|* 23 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX | | | | |KEY(SQ)|KEY(SQ)| | | |
| 24 | BITMAP MERGE | | | | | | | | 1024K| 512K|37888 (0)|
| 25 | BITMAP KEY ITERATION | | | | | | | | | | |
| 26 | BUFFER SORT | | | | | | | | 2992K| 769K|96256 (0)|
| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_C37A91 | 383 | 1915 | 2 (0)| 00:00:01 | | | | | |
|* 28 | BITMAP INDEX RANGE SCAN | SALES_CUST_BIX | | | | |KEY(SQ)|KEY(SQ)| | | |
| 29 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6605_C37A91 | 383 | 5745 | 2 (0)| 00:00:01 | | | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9j820km30uynd, child number 1
-------------------------------------
SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount FROM sales s, times t, customers c,
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 hash value: 2000181989
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 929 (100)| | | | | | |
| 1 | HASH GROUP BY | | 16 | 1344 | 929 (1)| 00:00:12 | | | 744K| 744K| 1148K (0)|
|* 2 | HASH JOIN | | 16 | 1344 | 907 (1)| 00:00:11 | | | 783K| 783K| 1244K (0)|
|* 3 | HASH JOIN | | 16 | 1088 | 888 (1)| 00:00:11 | | | 801K| 801K| 365K (0)|
|* 4 | HASH JOIN | | 16 | 752 | 885 (1)| 00:00:11 | | | 1169K| 1100K| 1576K (0)|
| 5 | PARTITION RANGE SUBQUERY | | 38 | 811 | 479 (1)| 00:00:06 |KEY(SQ)|KEY(SQ)| | | |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 38 | 811 | 479 (1)| 00:00:06 |KEY(SQ)|KEY(SQ)| | | |
| 7 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | | |
| 8 | BITMAP AND | | | | | | | | | | |
| 9 | BITMAP MERGE | | | | | | | | 1024K| 512K| 4096 (0)|
| 10 | BITMAP KEY ITERATION | | | | | | | | | | |
| 11 | BUFFER SORT | | | | | | | | 73728 | 73728 | |
|* 12 | TABLE ACCESS FULL | CHANNELS | 2 | 26 | 3 (0)| 00:00:01 | | | | | |
|* 13 | BITMAP INDEX RANGE SCAN | SALES_CHANNEL_BIX | | | | |KEY(SQ)|KEY(SQ)| | | |
| 14 | BITMAP MERGE | | | | | | | | 1024K| 512K|37888 (0)|
| 15 | BITMAP KEY ITERATION | | | | | | | | | | |
| 16 | BUFFER SORT | | | | | | | | 73728 | 73728 | |
|* 17 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 18 (0)| 00:00:01 | | | | | |
|* 18 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX | | | | |KEY(SQ)|KEY(SQ)| | | |
|* 19 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 406 (1)| 00:00:05 | | | | | |
|* 20 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | 3 (0)| 00:00:01 | | | | | |
|* 21 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 18 (0)| 00:00:01 | | | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------
In order to view a specific execution plan from the AWR repository, then we have to use procedure DBMS_XPLAN.DISPLAY_AWR. For example, in order to view plan 2093608901 (plan_hash_value), we can do the following:
------------------------------------------------------------------------------------------------------------
nikos@NIKOSDB> host cat xplan_awr.sql
set linesize 999
set pagesize 999
select * from table( dbms_xplan.display_awr('&sql_id', plan_hash_value => '&plan_hash_value', format =>'ALL ALLSTATS LAST'))
/
nikos@NIKOSDB> @xplan_awr
Enter value for sql_id: 9j820km30uynd
Enter value for plan_hash_value: 2093608901
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9j820km30uynd
--------------------
SELECT ch.channel_class, c.cust_city, t.calendar_quarter_desc,
SUM(s.amount_sold) sales_amount FROM sales s, times t, customers c,
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 hash value: 2093608901
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 558 (100)| | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | LOAD AS SELECT | | | | | | | |
| 3 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 406 (1)| 00:00:05 | | |
| 4 | HASH GROUP BY | | 254 | 18542 | 153 (2)| 00:00:02 | | |
| 5 | HASH JOIN | | 254 | 18542 | 129 (2)| 00:00:02 | | |
| 6 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 18 (0)| 00:00:01 | | |
| 7 | HASH JOIN | | 254 | 14478 | 110 (1)| 00:00:02 | | |
| 8 | HASH JOIN | | 254 | 10668 | 107 (0)| 00:00:02 | | |
| 9 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | 3 (0)| 00:00:01 | | |
| 10 | PARTITION RANGE SUBQUERY | | 254 | 5334 | 104 (0)| 00:00:02 |KEY(SQ)|KEY(SQ)|
| 11 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 254 | 5334 | 104 (0)| 00:00:02 |KEY(SQ)|KEY(SQ)|
| 12 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 13 | BITMAP AND | | | | | | | |
| 14 | BITMAP MERGE | | | | | | | |
| 15 | BITMAP KEY ITERATION | | | | | | | |
| 16 | BUFFER SORT | | | | | | | |
| 17 | TABLE ACCESS FULL | CHANNELS | 2 | 26 | 3 (0)| 00:00:01 | | |
| 18 | BITMAP INDEX RANGE SCAN | SALES_CHANNEL_BIX | | | | |KEY(SQ)|KEY(SQ)|
| 19 | BITMAP MERGE | | | | | | | |
| 20 | BITMAP KEY ITERATION | | | | | | | |
| 21 | BUFFER SORT | | | | | | | |
| 22 | TABLE ACCESS FULL | TIMES | 183 | 2928 | 18 (0)| 00:00:01 | | |
| 23 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX | | | | |KEY(SQ)|KEY(SQ)|
| 24 | BITMAP MERGE | | | | | | | |
| 25 | BITMAP KEY ITERATION | | | | | | | |
| 26 | BUFFER SORT | | | | | | | |
| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_C06B4A | 383 | 1915 | 2 (0)| 00:00:01 | | |
| 28 | BITMAP INDEX RANGE SCAN | SALES_CUST_BIX | | | | |KEY(SQ)|KEY(SQ)|
| 29 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_C06B4A | 383 | 5745 | 2 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
We have also truncated part from the output.
In general, it is very useful to be able to find the various execution plans used for a specific SQL statement, especially in the case when a query's performance suddenly deteriorates, due to a change in the execution plan. Then there might be a need to quickly revert to an older execution plan with better execution time. We will discuss how we can fix the execution plan for a specific SQL statement, with the use of SQL Profiles in a future post.
Enjoy!
Oracle Learner.
No comments:
Post a Comment