Oracle の ASH(Active Session History) から リテラルSQL を追跡してみる。
Oracle の ASH(Active Session History) から リテラルSQL を追跡してみます。
ASH にはアクティブなセッションの履歴情報、もう少し詳しく言うと
DB CPU または 待機状態(Disk IO/ロック待ち/Network待機/etc...) の
セッション情報 が 1回/1秒 の間隔で自動サンプリングされて、
V$ACTIVE_SESSION_HISTORYビューに格納されます。下記も参照
Oracle® Databaseリファレンス 11gリリース2 (11.2) B56311-10 V$ACTIVE_SESSION_HISTORY http://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_1007.htm#I1030299 > V$ACTIVE_SESSION_HISTORYは、データベース内のサンプリングされた > セッション・アクティビティを表示します。これには、1秒に1回取得される、 > アクティブなデータベース・セッションのスナップショットが含まれます。 > データベース・セッションは、CPU上にあった場合、またはIdle待機クラスに > 属していないイベントを待機していた場合に、アクティブであるとみなされます。 > 待機クラスの詳細は、V$EVENT_NAMEビューを参照してください。
リテラルSQLの場合、SQL_ID が都度替わるのでそこからは追跡できないですが、
SQLが意味的に同じ(例：特定表を抽出するSQLでWHERE句絞込条件の値だけリテラル、等)
場合は PLAN_HASH_VALUE が同じになる公算が高く、そこから追跡ができなくもないです。
検証してみるZe!(｀･ω･)ゞ リテラルSQLを流してみます。
SET TIME ON; SET TIMING ON; DECLARE v_sql VARCHAR2(1000); d_date DATE; BEGIN FOR i IN 1..100000 LOOP EXECUTE IMMEDIATE 'SELECT /* ' || TO_CHAR(i) || '*/ SYSDATE FROM MY_DUAL' INTO d_date; END LOOP; END; / PL/SQL procedure successfully completed. Elapsed: 00:00:54.06 11:55:29 SQL>
下記の SQL で ASHの一部 を csv形式 で抜きます。
11:57:13 SQL> !cat ash.sql SET LINESIZE 32767; SET PAGESIZE 0; SET FEED OFF; SET TRIMSPOOL ON; SET TERMOUT OFF; ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS'; ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY/MM/DD HH24:MI:SS'; spool ash.csv PROMPT "Y","M","D","H","MI","S","MODULE","PROGRAM","SQL_ID","EVENT","PLAN_HASH_VALUE" SELECT '"' || TO_CHAR(SAMPLE_TIME, 'YYYY') || '",' || '"' || TO_CHAR(SAMPLE_TIME, 'MM') || '",' || '"' || TO_CHAR(SAMPLE_TIME, 'DD') || '",' || '"' || TO_CHAR(SAMPLE_TIME, 'HH24') || '",' || '"' || TO_CHAR(SAMPLE_TIME, 'MI') || '",' || '"' || TO_CHAR(SAMPLE_TIME, 'SS') || '",' || '"' || MODULE || '",' || '"' || PROGRAM || '",' || '"' || SQL_ID || '",' || '"' || EVENT || '",' || '"' || SQL_PLAN_HASH_VALUE || '"' FROM V$ACTIVE_SESSION_HISTORY WHERE SAMPLE_TIME BETWEEN TO_TIMESTAMP('2015/07/13 11:54:35', 'YYYY/MM/DD HH24:MI:SS') AND TO_TIMESTAMP('2015/07/13 11:55:30', 'YYYY/MM/DD HH24:MI:SS'); spool off;
抽出結果は下記となります。
11:57:18 SQL> @./ash.sql 11:57:24 SQL> !cat ash.csv "Y","M","D","H","MI","S","MODULE","PROGRAM","SQL_ID","EVENT","PLAN_HASH_VALUE" "2015","07","13","11","55","28","SQL*Plus","sqlplus@dbserv1 (TNS V1-V3)","3w6rsvwhpz4kv","","2036514324" "2015","07","13","11","55","27","SQL*Plus","sqlplus@dbserv1 (TNS V1-V3)","gmbj758mq2q0w","","2036514324" "2015","07","13","11","55","26","SQL*Plus","sqlplus@dbserv1 (TNS V1-V3)","axsar92qx6b5m","","2036514324" "2015","07","13","11","55","25","","oracle@dbserv1 (CKPT)","","","0" "2015","07","13","11","55","25","SQL*Plus","sqlplus@dbserv1 (TNS V1-V3)","367v7k8d9m8xn","","2036514324" ： "2015","07","13","11","54","37","SQL*Plus","sqlplus@dbserv1 (TNS V1-V3)","40prdnduwzaj3","","2036514324" "2015","07","13","11","54","36","SQL*Plus","sqlplus@dbserv1 (TNS V1-V3)","cah0q6gtc2j22","","2036514324" "2015","07","13","11","54","35","SQL*Plus","sqlplus@dbserv1 (TNS V1-V3)","5u4u63gu2ymfh","","2036514324" 11:57:29 SQL>
既に結果が見えて無くもないですが、せっかく csv形式 で抜いたので、
Excel でグラフにしてみます。ピボットグラフでがちゃがちゃすると……
PLAN_HASH_VALUE = 2036514324 の SQL が大量に居てるのが解ったやで。
こいつを V$SQLビュー から抜いてみると、、、
SELECT SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE FROM V$SQL WHERE PLAN_HASH_VALUE = 2036514324 ORDER BY SQL_ID, CHILD_NUMBER; SQL_ID CHILD_NUMBER PLAN_HASH_VALUE ------------- ------------ --------------- 001kubp3kaswg 0 2036514324 00782rn7j921b 0 2036514324 008wkpdf4pxbc 0 2036514324 00an211rtx0v3 0 2036514324 ： gzws7gppv5x29 0 2036514324 gzyc248pqjnbz 0 2036514324 gzytchrgrdmt2 0 2036514324
適当にサンプリングして XPLAN で 実行計画共々SQLを抜いてみると、、、
SET LINESIZE 300; SET PAGESIZE 1000; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id => '00an211rtx0v3', format => 'ALL ALLSTATS LAST')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------- SQL_ID 00an211rtx0v3, child number 0 ------------------------------------- SELECT /* 95297*/ SYSDATE FROM MY_DUAL Plan hash value: 2036514324 ---------------------------------------------------------------------- | Id | Operation | Name | E-Rows | Cost (%CPU)| E-Time | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 3 (100)| | | 1 | TABLE ACCESS FULL| MY_DUAL | 1 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------- ：
やった、リテラルSQL を ASH から特定したやで！(｀･ω･)Ъ ※マッチポンプとも言う。
なお ASH参照には有償オプションが必要なんで、そこは注意しませふ(´･ω･)ゞ
Oracle® Databaseライセンス情報 11gリリース2 (11.2) B56284-10 Oracle Diagnostics Pack http://docs.oracle.com/cd/E16338_01/license.112/b56284/options.htm#CIHIHDDJ > V$ACTIVE_SESSION_HISTORY動的パフォーマンス・ビューおよびその基礎となる表X$ASHは、Diagnostics Packのライセンスが必要です。
