UJP - 技術情報

SQL*Plus Autotraceを使う

０．更新履歴

2001.10.08　新規作成

１．はじめに

　このドキュメントでは，Oracle8iにて，SQL*PlusのAUTOTRACE機能を使えるようにする．
　AUTOTRACE機能を使うことで，SQL文の実行時に実行計画やOracleが使うリソースの統計を調べる事ができ，チューニングにつながる．

２．準備

sysユーザで接続する．

C:\>sqlplus sys/change_on_install

SQL*Plus: Release 8.1.7.0.0 - Production on 月 Oct 8 14:02:46 2001

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Personal Oracle8i Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
に接続されました。
SQL>

plustrce.sql文を実行する．
- $ORACLE_HOME/sqlplus/admin/plustrce.sql

SQL> @d:\oracle\ora81\sqlplus\admin\plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
          *
1行でエラーが発生しました。
ORA-01919: ロール 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_$session to plustrace;

権限付与が成功しました。

SQL> grant plustrace to dba with admin option;

権限付与が成功しました。

SQL>
SQL> set echo off
SQL>

３．統計を取るユーザに権限を与える

AUTOTRACEを使用するユーザにロールを付与する．
- 今回は，scottユーザに付与している．

SQL> grant plustrace to scott
  2  ;

権限付与が成功しました。

SQL>

AUTOTRACEを使用するユーザでログインする．
- 今回は，scottユーザとしている．
utlxplan.sqlを実行する．
- $ORACLE_HOME/rdbms/admin/utlxplan.sql

SQL> connect scott/tiger
接続されました。
SQL> @d:\oracle\ora81\rdbms\admin\utlxplan.sql

表が作成されました。

SQL> quit
Personal Oracle8i Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Productionとの接続が切断されました。
C:\>

４．AUTOTRACEを使ってみる

権限の与えられたscottユーザで接続する．

C:\>sqlplus scott/tiger

SQL*Plus: Release 8.1.7.0.0 - Production on 月 Oct 8 14:24:11 2001

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Personal Oracle8i Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
に接続されました。
SQL>

autotraceを利用できるようにする．
- setコマンドは，";"等のターミネータは必要ない．

SQL> set autotrace on
SQL>

SQL文を実行してみる．

SQL> select ename from emp;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

ENAME
----------
JAMES
FORD
MILLER

14行が選択されました。

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP'



統計
----------------------------------------------------------
          0  recursive calls
          4  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        924  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed
SQL>

実行計画のみを表示する．

SQL> set autotrace traceonly explain
SQL> select ename from emp;

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'EMP'


SQL>

統計情報のみを表示する．

SQL> set autotrace traceonly statistics
SQL> select ename from emp;

14行が選択されました。

統計
----------------------------------------------------------
          0  recursive calls
          4  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        924  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed
SQL>

AUTOTRACEを使わないように設定する．

SQL> set autotrace off
SQL> select ename from emp;

ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
ENAME
----------
JAMES
FORD
MILLER
14行が選択されました。
SQL>

５．統計について

　統計で出力される項目の主なもの項目の説明を行う．

統計の説明
項目	内容
db block gets	論理的に読み込まれたブロック数．
consistant gets	論理的に読み込まれたブロック数．
physical reads	物理的に読み込まれたブロック数．
sorts(memory)	メモリ上でソートされた数
sorts(disk)	ディスク上でソートされた数．
forws processed	処理したデータベースの行の数．