実行計画が変動したSQLの実行計画を一括出力する

DBA_HIST_SQL_PLAN に PLAN_HASH_VALUE が複数ある SQL の実行計画を DBMS_XPLAN.DISPLAY_AWR で一括出力する SQL再帰SQLが出力されないよう OBJECT_OWNER で絞っている。

set pagesize 50000
set linesize 200

select b.* from
	( select distinct sql_id from
		( select p.sql_id,
			count(distinct p.plan_hash_value)
				over(partition by p.sql_id) as cnt
			from dba_hist_sql_plan p
				where p.object_owner = 'SCOTT'
		) where cnt > 1
	) a,
	table(dbms_xplan.display_awr(a.sql_id, null, null, 'ALL')) b
/
  • 出力結果例
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID 2z5kjv7k63rdv
--------------------
/* SQL Analyze(35,1) */ SELECT A.*   FROM TEST_TABLE_A A      , TBL_B B
 WHERE A.P_NO2  = B.P_NO    AND A.P_CHAR = B.P_CHAR    AND
TO_CHAR(B.P_DATE, 'YYYYMMDD') = '20120801'

Plan hash value: 1824609195

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |       |       | 30938 (100)|          |
|   1 |  NESTED LOOPS                |                 | 30012 |   967K| 30938   (1)| 00:06:12 |
|   2 |   NESTED LOOPS               |                 | 30300 |   967K| 30938   (1)| 00:06:12 |
|   3 |    TABLE ACCESS FULL         | TBL_B           |   300 |  3900 |    24   (5)| 00:00:01 |
|   4 |    INDEX RANGE SCAN          | TEST_TABLE_A_I1 |   101 |       |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| TEST_TABLE_A    |   100 |  2000 |   103   (0)| 00:00:02 |
------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / B@SEL$1
   4 - SEL$1 / A@SEL$1
   5 - SEL$1 / A@SEL$1

SQL_ID 2z5kjv7k63rdv
--------------------
/* SQL Analyze(35,1) */ SELECT A.*   FROM TEST_TABLE_A A      , TBL_B B
 WHERE A.P_NO2  = B.P_NO    AND A.P_CHAR = B.P_CHAR    AND
TO_CHAR(B.P_DATE, 'YYYYMMDD') = '20120801'

Plan hash value: 2581240648

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |       |       | 30938 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_TABLE_A    |   100 |  2000 |   103   (0)| 00:00:02 |
|   2 |   NESTED LOOPS              |                 | 30012 |   967K| 30938   (1)| 00:06:12 |
|   3 |    TABLE ACCESS FULL        | TBL_B           |   300 |  3900 |    24   (5)| 00:00:01 |
|   4 |    INDEX RANGE SCAN         | TEST_TABLE_A_I1 |   101 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / A@SEL$1
   3 - SEL$1 / B@SEL$1
   4 - SEL$1 / A@SEL$1

SQL_ID 2z5kjv7k63rdv
--------------------
/* SQL Analyze(35,1) */ SELECT A.*   FROM TEST_TABLE_A A      , TBL_B B
 WHERE A.P_NO2  = B.P_NO    AND A.P_CHAR = B.P_CHAR    AND
TO_CHAR(B.P_DATE, 'YYYYMMDD') = '20120801'

Plan hash value: 3921842617

-----------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |       |       |  2534 (100)|          |
|   1 |  HASH JOIN         |              | 30012 |   967K|  2534   (3)| 00:00:31 |
|   2 |   TABLE ACCESS FULL| TBL_B        |   300 |  3900 |    24   (5)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| TEST_TABLE_A |  2600K|    49M|  2495   (2)| 00:00:30 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / B@SEL$1
   3 - SEL$1 / A@SEL$1


76 rows selected.