セッション情報などを見るために show_proc_ses_lock.sql をダウンロードして、sqlplus でログインして、
$ wget http://github.com/yoheia/yoheia/raw/master/oracle/sql/show_proc_ses_lock.sql $ sqlplus / as sysdba
show_proc_ses_lock.sql を実行して該当 SQL の PLAN_HASH_VALUE を確認する。
SQL> @show_proc_ses_lock.sql OSPID BLOCK SID SERIAL# SQL_ID USERNAME STATUS TY LMODE REQUEST ID1 ID2 LOCK_TIME HASH_VALUE ADDRESS PLAN_HASH_VALUE SQL ... ----- ------ ------ ------- ------------- ---------- -------- -- ----- ------- ---------- ---------- ---------- ---------- ---------------- --------------- --- ... ... 849 233 2135 0uy584dfyctqb SCOTT ACTIVE TM 2 0 83608 0 3.4 1575380683 00000000DE67E418 3549116597 INSERT INTO ... 849 233 2135 0uy584dfyctqb SCOTT ACTIVE TM 2 0 83610 0 3.4 1575380683 00000000DE67E418 3549116597 INSERT INTO ... ... 30 rows selected.
該当 SQL の実行計画を確認する。
「INSERT INTO ...」が遅い SQL だとして、PLAN_HASH_VALUE は 3549116597 なので、
SQL> select * from table(dbms_xplan.display('v$sql_plan',null,null,'PLAN_HASH_VALUE=3549116597')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 3549116597 -------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | | | | 58234 (100)| | | | | 1 | SEQUENCE | HOGE_DL_DETAIL_SEQ | | | | | | | | | 2 | VIEW | | 282K| 90M| | 58234 (2)| 00:11:39 | | | | 3 | SORT GROUP BY | | 282K| 84M| | | | | | | 4 | CONCATENATION | | | | | | | | | |* 5 | FILTER | | | | | | | | | |* 6 | HASH JOIN RIGHT OUTER | | 2838 | 870K| | 19411 (2)| 00:03:53 | | | ... |*154 | INDEX RANGE SCAN | HOGE_HOGE_LG_HOGE_HOGE_IX | 1 | | | 1 (0)| 00:00:01 | | | -------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter(( IS NOT NULL OR IS NOT NULL OR IS NOT NULL OR IS NOT NULL OR IS NOT NULL OR IS NOT NULL OR IS NOT NULL OR IS NOT NULL)) ... 309 rows selected.
