Oracle 統計情報取得の基礎メモ
参考
Oracle SQLチューニング講座（5）：SQLチューニングの基盤となる統計情報 (1/3) - ＠IT
Oralce9iの記事なので古い情報なはず。
まあでも基礎としてはちょうどいいのでは。。
V$表は「第4回」の記事のほうが詳しい。
最後のまとめ表を引用。。
メリット デメリット SQLトレース＋TKPROFユーティリティ ・詳細な情報が取得可能
・時間統計の取得が可能
・アプリケーションで実行されるすべてのSQLを取得可能・トレースファイルを格納するためのディスク領域が必要
・取得時に多少の負荷が発生SQL*PlusのAUTOTRACE機能 ・簡単に実行計画を確認することが可能 ・環境設定が必要 動的パフォーマンスビューの利用 ・過去に実行されたSQLの実行計画を確認することが可能 ・共有SQL領域が大きい環境やシステム自体の負荷が非常に高い環境では、オーバーヘッドとなる場合がある
SQLトレース＋TKPROFユーティリティ
最も詳細。でも面倒臭いしDBへの負荷も高い。
接続先設定（専用サーバー接続/共有サーバー接続）を調べる
SQL> SELECT SERVER FROM V$SESSION WHERE SID = USERENV('SID') ; SERVER ------------------ DEDICATED
トレースファイル作成ディレクトリを探す
専用サーバならUSER_DUMP_DEST、共有サーバならBACKGROUND_DUMP_DEST。
SQL> SHOW PARAMETER USER_DUMP_DEST NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ user_dump_dest string C:\oraclexe\app\oracle\diag\rd bms\xe\xe\trace
トレースを取得して解析する
時間に関連する統計の収集も行うようにしておく。
SQL> ALTER SESSION SET TIMED_STATISTICS=TRUE; セッションが変更されました。 SQL> ALTER SESSION SET SQL_TRACE=TRUE; セッションが変更されました。 SQL> select j.*, (select count(1) from employees e where e.job_id = j.job_id) as cnt from jobs j; JOB_ID JOB_TITLE MIN_SALA -------------------- ---------------------------------------------------------------------- -------- AD_PRES President 200 AD_VP Administration Vice President 150 AD_ASST Administration Assistant 30 (略) PR_REP Public Relations Representative 45 19行が選択されました。 SQL> ALTER SESSION SET SQL_TRACE=FALSE; セッションが変更されました。 SQL> exit Oracle Database 11g Express Edition Release 11.2.0.2.0 - Productionとの接続が切断されました。 PS$cd C:\oraclexe\app\oracle\diag\rdbms\xe\xe\trace PS$tkprof xe_ora_2876.trc xe_ora_2876.prf explain=hr/hr aggregate=no sys=no sort=fchela TKPROF: Release 11.2.0.2.0 - Development on 金 8月 23 22:53:52 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. could not open output file xe_ora_2876.prf
なんでや。。そういえばWin7はCドライブに書き込むの何か制限あったような。。
powershellを「管理者として実行」して再チャレンジ。
PS$tkprof xe_ora_2876.trc 2876.prf explain=hr/hr aggregate=no sys=no sort=fchela TKPROF: Release 11.2.0.2.0 - Development on 金 8月 23 23:01:51 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. PS$
(^ω^)にこっ
得られた情報
SQL ID: cwxkcwsmy90km Plan Hash: 3853955282
select j.*, (select count(1)
from
employees e where e.job_id = j.job_id) as cnt from jobs j
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 7 12 0 19
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.01 7 12 0 19
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 50 (HR)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
19 19 19 SORT AGGREGATE (cr=4 pr=1 pw=0 time=334 us)
108 108 108 INDEX RANGE SCAN EMP_JOB_IX (cr=4 pr=1 pw=0 time=309 us cost=1 size=54 card=6)(object id 23562)
19 19 19 TABLE ACCESS FULL JOBS (cr=8 pr=6 pw=0 time=1889 us cost=3 size=627 card=19)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
19 SORT (AGGREGATE)
108 INDEX MODE: ANALYZED (RANGE SCAN) OF 'EMP_JOB_IX' (INDEX)
19 TABLE ACCESS MODE: ANALYZED (FULL) OF 'JOBS' (TABLE)
そのほか
ALTER SESSION SET EVENTでバインド変数、待機イベントも出力できる。
SYSDBA権限なら別セッションのSQLトレースも取得できる。
参考
SQL*PlusのAUTOTRACE
手軽でDB負荷も低い。でも情報は少ないし自セッションのものしか取得できない。メモリソート・ディスクソート回数が確認できる。
初期設定：AUTOTRACEを使うユーザにPLUSTRACEをGRANTし、PLAN_TABLE表を作成。
SQL> conn sys/admin as sysdba 接続されました。 SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\sqlplus\admin\plustrce.sql SQL> SQL> drop role plustrace; ロールが削除されました。 SQL> create role plustrace; ロールが作成されました。 SQL> SQL> grant select on v_$sesstat to plustrace; 権限付与が成功しました。 SQL> grant select on v_$statname to plustrace; 権限付与が成功しました。 SQL> grant select on v_$mystat to plustrace; 権限付与が成功しました。 SQL> grant plustrace to dba with admin option; 権限付与が成功しました。 SQL> SQL> set echo off
SQL> GRANT plustrace TO hr; 権限付与が成功しました。 SQL> disconn Oracle Database 11g Express Edition Release 11.2.0.2.0 - Productionとの接続が切断されました。 SQL> conn hr/hr@xe 接続されました。 SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\sqlplus\admin\utlxplan.sql SP2-0310: ファイル"C:\oraclexe\app\oracle\product\11.2.0\server\sqlplus\admin\utlxplan.sql"をオープンできません。
なんでや。。と思ったら11gでは不要らしい。なのでdescribeしてみると
SQL> describe plan_table 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- STATEMENT_ID VARCHAR2(30) PLAN_ID NUMBER (略) QBLOCK_NAME VARCHAR2(30)
plan_tableはもうある。
得られた情報
SQL> SET AUTOTRACE TRACEONLY
SQL> select j.*, (select count(1) from employees e where e.job_id = j.job_id) as cnt from jobs j;
19行が選択されました。
実行計画
----------------------------------------------------------
Plan hash value: 3853955282
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 627 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX RANGE SCAN| EMP_JOB_IX | 6 | 54 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| JOBS | 19 | 627 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."JOB_ID"=:B1)
統計
----------------------------------------------------------
1 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
1608 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19 rows processed
SQL> SET AUTOTRACE OFF
参考
V$表
共有プールにキャッシュされている範囲内で、過去に実行されたSQLを調べる。
ハッシュ値とアドレス値を調べる
上で実行したSQLのPLAN HASH VALUEが3853955282だって書いてあるからこれで抽出できるんでしょう。
SQL> conn sys/admin as sysdba 接続されました。 SQL> select sql_text, address, hash_value from v$sql where plan_hash_value = 3853955282; SQL_TEXT ADDRESS HASH_VALUE ----------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------- EXPLAIN PLAN SET STATEMENT_ID='312173' INTO HR.prof$plan_table FOR select j.*, (select count(1) from employees e where e.job_id = j.job_id) as cnt from jobs j B9EBDBDC 274475288 select j.*, (select count(1) from employees e where e.job_id = j.job_id) as cnt from jobs j B15BE3E4 669286995 select j.*, (select count(1) from employees e where e.job_id = j.job_id) as cnt from jobs j B15BE3E4 669286995 EXPLAIN PLAN SET STATEMENT_ID='PLUS312186' FOR select j.*, (select count(1) from employees e where e.job_id = j.job_id) as cnt from jobs j AE3865D0 3262729416
得られた情報
SQL> DEFINE W_ADDRESS=B15BE3E4 SQL> DEFINE W_HASH_VALUE=669286995 SQL> col operation form a50 SQL> col options form a50 SQL> col object_name form a20 SQL> col optimizer form a20 SQL> SELECT id, 2 lpad (' ', depth) || operation operation, 3 options, 4 object_name, 5 optimizer, 6 cost 7 FROM v$sql_plan 8 WHERE hash_value=&W_HASH_VALUE 9 AND address='&W_ADDRESS' 10 START WITH id = 0 11 CONNECT BY 12 (PRIOR id=parent_id 13 AND PRIOR hash_value=hash_value 14 AND PRIOR child_number=child_number) 15 ORDER SIBLINGS BY id, position; 旧 8: WHERE hash_value=&W_HASH_VALUE 新 8: WHERE hash_value=669286995 旧 9: AND address='&W_ADDRESS' 新 9: AND address='B15BE3E4' ID OPERATION OPTIONS OBJECT_NAME OPTIMIZER COST ---------- -------------------------------------------------- -------------------------------------------------- -------------------- -------------------- ---------- 0 SELECT STATEMENT ALL_ROWS 3 1 SORT AGGREGATE 2 INDEX RANGE SCAN EMP_JOB_IX 1 3 TABLE ACCESS FULL JOBS 3 0 SELECT STATEMENT ALL_ROWS 3 1 SORT AGGREGATE 2 INDEX RANGE SCAN EMP_JOB_IX 1 3 TABLE ACCESS FULL JOBS 3 8行が選択されました。
結果が返ってくるまで結構時間かかります。
