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.
