ksaitoの日記

日々試したことの覚え書き

sqlplusで実行計画を確認する

移転しました。

自動的にリダイレクトします。

CentOSにインストールしたOracle 11g XEでsqlplusでSQLの実行計画を確認する手順です。

サンプルスキーマの作成

サンプルスキーマのscottを作成します。
スクリプトの保管場所が変わったみたいです。

-bash-4.1$ sqlplus system @/u01/app/oracle/product/11.2.0/xe/rdbms/admin/utlsampl.sql 

SQL*Plus: Release 11.2.0.2.0 Production on 土 11月 3 14:11:37 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

パスワードを入力してください: 


Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
に接続されました。
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Productionとの接続が切断されました。
-bash-4.1$ 

設定

DBA権限でplustrace.sqlで実行することでautotraceに必要なロールが作成されます。
実行計画と統計情報を確認するためのユーザに権限を付与します。

$ sudo su - oracle
-bash-4.1$ . /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh
-bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on 土 11月 3 00:00:47 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
に接続されました。
SQL> @/u01/app/oracle/product/11.2.0/xe/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> connect system
パスワードを入力してください: 
接続されました。
SQL> grant plustrace to scott;

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

SQL> 

次に権限を付与したユーザで実行計画および統計情報を保管するためのテーブルを作成します。

SQL> connect scott/tiger
接続されました。
SQL> @/u01/app/oracle/product/11.2.0/xe/rdbms/admin/utlxplan.sql

表が作成されました。

SQL> 

実行計画と統計情報の確認

set autotrace onを実行して該当するSQLを実行するとSQLの結果と実行計画、統計情報が表示されます。

SQL> set autotrace on
SQL> r
  1* select * from emp

     EMPNO ENAME      JOB	       MGR HIREDATE	   SAL	     COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 80-12-17	   800			  20
      7499 ALLEN      SALESMAN	      7698 81-02-20	  1600	      300	  30
      7521 WARD       SALESMAN	      7698 81-02-22	  1250	      500	  30
      7566 JONES      MANAGER	      7839 81-04-02	  2975			  20
      7654 MARTIN     SALESMAN	      7698 81-09-28	  1250	     1400	  30
      7698 BLAKE      MANAGER	      7839 81-05-01	  2850			  30
      7782 CLARK      MANAGER	      7839 81-06-09	  2450			  10
      7839 KING       PRESIDENT 	   81-11-17	  5000			  10
      7844 TURNER     SALESMAN	      7698 81-09-08	  1500		0	  30
      7900 JAMES      CLERK	      7698 81-12-03	   950			  30
      7902 FORD       ANALYST	      7566 81-12-03	  3000			  20
      7934 MILLER     CLERK	      7782 82-01-23	  1300			  10

12行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |    12 |  1044 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    12 |  1044 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


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

SQL>