読者です 読者をやめる 読者になる 読者になる

Oracle 統計情報取得の基礎メモ

oracle db

参考

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 on823 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=6692869959: 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行が選択されました。

結果が返ってくるまで結構時間かかります。

参考