第1章 準備作業

この章では、本書で説明するプログラムを実行するために、以下の準備作業を行います。
・表領域を作成する
・ユーザーを作成する
・権限を付与する
・「プロファイリング」用のパッケージと、テーブルを作成する
・「プロファイリング」を行ってみる
・「プロファイリング」実行用スクリプトを作成する
・「実行計画」採取用テーブルを作成する

1.1. 表領域を作成する

まず最初に、本書で使用するテーブルを格納するための、表領域を作成します。
本書では、以下のような表領域を作成、使用します。
 表領域名ファイル名
データ用表領域sample_datac:\oracle\oradata\orcl\sample_data.dbf
索引用表領域sample_indexc:\oracle\oradata\orcl\sample_index.dbf
一時作業用表領域sample_tempc:\oracle\oradata\orcl\sample_temp.dbf
表領域を作成する前に、"system" ユーザー で接続しておきます。
"system" のパスワードは、初期設定で "manager" になっています。
CONN system/manager
"sample_data" というデータ用表領域を、
"c:\oracle\oradata\orcl\sample_data.dbf" というファイル名で作成します。
初期サイズは 10MB、
領域がいっぱいになったら 10MB ずつ自動的に拡張します。
ファイルサイズの上限はありません。
CREATE TABLESPACE sample_data
DATAFILE          'c:\oracle\oradata\orcl\sample_data.dbf'
SIZE              10M
AUTOEXTEND        ON
NEXT              10M
MAXSIZE           UNLIMITED
;
同じように、"sample_index" という索引用表領域を、
"c:\oracle\oradata\orcl\sample_index.dbf"  というファイル名で作成します。
CREATE TABLESPACE sample_index
DATAFILE          'c:\oracle\oradata\orcl\sample_index.dbf'
SIZE              10M
AUTOEXTEND        ON 
NEXT              10M 
MAXSIZE           UNLIMITED
;
"sample_temp" という一時作業用表領域を、
"c:\oracle\oradata\orcl\sample_temp.dbf"  というファイル名で作成します。
一時作業用表領域の作成は、"CREATE TEMPORARY TABLESPACE" 文を使用します。
また、ファイル名は、"DATAFILE" ではなく、"TEMPFILE" で指定します。
CREATE TEMPORARY
       TABLESPACE sample_temp
TEMPFILE          'c:\oracle\oradata\orcl\sample_temp.dbf'
SIZE              10M
AUTOEXTEND        ON
NEXT              10M 
MAXSIZE           UNLIMITED
;
ファイル名を間違えて作成してしまった時などは、以下のようにして削除します。
DROP TABLESPACE sample_data
INCLUDING CONTENTS
      AND DATAFILES
CASCADE CONSTRAINTS
;
DROP TABLESPACE sample_index
INCLUDING CONTENTS
      AND DATAFILES
CASCADE CONSTRAINTS
;
DROP TABLESPACE sample_temp
INCLUDING CONTENTS
      AND DATAFILES
CASCADE CONSTRAINTS
;
表領域の作成結果は次のようにして、確認できます。
SELECT
    tablespace_name
,   block_size
,   status
,   contents
,   logging
,   extent_management
,   allocation_type
,   segment_space_management
FROM
    dba_tablespaces
WHERE
    tablespace_name LIKE 'SAMPLE%'
;

SELECT
    file_name
,   tablespace_name
,   bytes
,   status
,   autoextensible
FROM
    dba_data_files
WHERE
    tablespace_name LIKE 'SAMPLE%'
UNION
SELECT
    tablespace_name
,   file_name
,   bytes
,   status
,   autoextensible
FROM
    dba_temp_files
WHERE
    tablespace_name LIKE 'SAMPLE%'
;
実行結果 
TABLESPACE_NAME  BLOCK_SIZE STATUS    CONTENTS  LOGGING   EXTENT_MAN ALLOCATIO SEGMEN
---------------- ---------- --------- --------- --------- ---------- --------- ------
SAMPLE_DATA            4096 ONLINE    PERMANENT LOGGING   LOCAL      SYSTEM    MANUAL
SAMPLE_INDEX           4096 ONLINE    PERMANENT LOGGING   LOCAL      SYSTEM    MANUAL
SAMPLE_TEMP            4096 ONLINE    TEMPORARY NOLOGGING LOCAL      UNIFORM   MANUAL


TABLESPACE_NAME  FILE_NAME                                     BYTES STATUS    AUT
---------------- ---------------------------------------- ---------- --------- ---
SAMPLE_DATA      C:\ORACLE\ORADATA\ORCL\SAMPLE_DATA.DBF     10485760 AVAILABLE YES
SAMPLE_INDEX     C:\ORACLE\ORADATA\ORCL\SAMPLE_INDEX.DBF    10485760 AVAILABLE YES
SAMPLE_TEMP      C:\ORACLE\ORADATA\ORCL\SAMPLE_TEMP.DBF     10485760 AVAILABLE YES
補 足 
表領域、テーブル、ユーザーなど、オブジェクト名は大文字・小文字を区別しませんが、
データディクショナリには、全て大文字で格納されていますので、
文字列として検索する場合は、大文字で指定する必要があります。
SQL> SELECT
  2      *
  3  FROM
  4      dba_tablespaces
  5  WHERE
  6      tablespace_name LIKE 'sample%'
  7  ;

レコードが選択されませんでした。

1.2. ユーザーを作成する

次に、ユーザーを作成します。
本書で使用するユーザー名を "johnny"、パスワードを "cougar" とします。
また、このユーザーが使用する表領域として、先ほど作成した表領域を指定します。
表領域の指定は必須ではありませんが、指定を省略した場合、
データ用表領域には "system" 表領域が、一時作業用には "temp" 表領域が使用されます。
一時作業用の "temp" はともかく、
"system" 表領域にユーザーデータを格納するのはやめておいた方がよいでしょう。
ユーザーを作成する前に、"system" ユーザー で接続しておきます。
CONN system/manager
ユーザーを作成します。
このユーザーが使用する表領域として、先ほど作成した表領域を指定します。
また、上限なしで、この表領域を使用できるようにします。
CREATE     USER       johnny
IDENTIFIED BY         cougar
DEFAULT    TABLESPACE sample_data
TEMPORARY  TABLESPACE sample_temp
QUOTA UNLIMITED ON    sample_data
QUOTA UNLIMITED ON    sample_index
QUOTA UNLIMITED ON    sample_temp
;
ユーザー名を間違えて作成してしまった時などは、以下のようにして削除します。
DROP USER johnny CASCADE
;
ユーザーの作成結果は次のようにして、確認できます。
SELECT
    username
,   account_status
,   default_tablespace
,   temporary_tablespace
FROM
    dba_users
WHERE
    username = 'JOHNNY'
;
実行結果 
USERNAME                   ACCOUNT_STATUS   DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
-------------------------- ---------------- ------------------ --------------------
JOHNNY                     OPEN             SAMPLE_DATA        SAMPLE_TEMP         

1.3. 権限を付与する

次に、作成したユーザーに、データベースに接続する権限や、
テーブルやプログラムなどを作成する権限を付与します。
ユーザーに直接権限を付与することもできますが、
通常は、権限をグループ化した「ロール」を作成し、ロールに権限を付与して、
そのロールをユーザーに付与するようにします。
"system" ユーザー で接続しておきます。
CONN system/manager
ロールを作成します。
ここでは、ロール名を "study" とします。
CREATE ROLE study
;
ロール名を間違えて作成してしまった時などは、以下のようにして削除します。
DROP ROLE study
;
ロール に、以下の権限を付与します。
権限付与される権限
CREATE SESSIONデータベースへの接続
ALTER SESSIONセッションパラメータの変更
CREATE TABLE表の作成
CREATE VIEWビューの作成
CREATE SYNONYMシノニムの作成
CREATE SEQUENCE順序の作成
CREATE PROCEDUREプロシージャ、ファンクション、パッケージの作成
CREATE TRIGGERトリガーの作成
GRANT CREATE SESSION
,     ALTER  SESSION
,     CREATE TABLE
,     CREATE VIEW
,     CREATE SYNONYM
,     CREATE SEQUENCE
,     CREATE PROCEDURE
,     CREATE TRIGGER
   TO study
;
ロールから権限を取り消すときは、以下のようにします。
REVOKE CREATE SESSION
,      ALTER  SESSION
,      CREATE TABLE
,      CREATE VIEW
,      CREATE SYNONYM
,      CREATE SEQUENCE
,      CREATE PROCEDURE
,      CREATE TRIGGER
  FROM study
;
結果は次のようにして、確認できます。
SELECT
    *
FROM
    role_sys_privs
WHERE
    role = 'STUDY'
;
実行結果 
ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
STUDY                          ALTER SESSION                            NO
STUDY                          CREATE PROCEDURE                         NO
STUDY                          CREATE SEQUENCE                          NO
STUDY                          CREATE SESSION                           NO
STUDY                          CREATE SYNONYM                           NO
STUDY                          CREATE TABLE                             NO
STUDY                          CREATE TRIGGER                           NO
STUDY                          CREATE VIEW                              NO
ユーザー にロールを付与します。
GRANT study TO johnny
;
ユーザー からロールを取り消すときは、以下のようにします。
REVOKE study FROM johnny
;
結果は次のようにして、確認できます。
SELECT
    *
FROM
    dba_role_privs
WHERE
    grantee = 'JOHNNY'
;
実行結果 
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
JOHNNY                         STUDY                          NO  YES
補 足 
デフォルトで作成されているロールとして、
データベースに接続する権限を持ったロール "connect"、
テーブルやパッケージなどのオブジェクトを作成する権限を持ったロール "resource" が、
ありますので、新たにロールを作成せずに、それを使ってもかまいません。
■ "connect" に含まれる権限
・ALTER  SESSION
・CREATE CLUSTER
・CREATE DATABASE LINK
・CREATE SEQUENCE
・CREATE SESSION
・CREATE SYNONYM
・CREATE TABLE
・CREATE VIEW
■ "resource" に含まれる権限
・CREATE CLUSTER
・CREATE PROCEDURE
・CREATE SEQUENCE
・CREATE TABLE
・CREATE TRIGER
GRANT connect
,     resource
   TO johnny
;

1.4. 「プロファイリング」用のパッケージと、テーブルを作成する

「プロファイリング」とは、プログラムの実行情報を収集・分析することです。
"DBMS_PROIFILER" パッケージを使用することで、プログラムの各行の、実行時間、実行回数などを
取得することができます。
パフォーマンスチューニングを行う際、ただやみくもにコードを変更するのではなく、
まずプロファイリングを行って、コードのどの部分がボトルネックになっているかを見極めた上で
作業することが大切です。
この節では、"DBMS_PROIFILER" パッケージと、パッケージが使用するテーブルとを作成します。

DBMS_PROIFILER パッケージの作成

"sys"ユーザーで接続します。
"sys" のパスワードは、初期設定で "change_on_install" になっています。
CONN sys/change_on_install
「OS認証」での接続しか許可されていない場合、これでは接続できません。
その場合 Administrator で、Windows にログインし、
次のようにして接続します。
CONN / AS sysdba
"$ORACLE_HOME/rdbms/admin/profload.sql" を実行し、
"DBMS_PROIFILER" パッケージを作成します
@?\rdbms\admin\profload.sql
補 足 
■ファイルに保存してある  PL/SQL または、SQL の実行
ファイルに保存してある、PL/SQL、SQL を実行するには、
"@ファイル名" もしくは、"START ファイル名" とします。

■$ORACLE_HOME ディレクトリ
"@?\rdbms\admin\profload.sql" の "?" は、$ORACLE_HOME ディレクトリを指定したのと同じ意味になります。
例えば、筆者の環境では、"@c:\oracle\ora90\rdbms\admin\profload.sql" と指定したのと同じです。
または、"@%ORACLE_HOME%/rdbms/admin/profload.sql" とも指定できます。
実行結果 
sql> @?\rdbms\admin\profload.sql

パッケージが作成されました。

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

シノニムが作成されました。

ライブラリが作成されました。

パッケージ本体が作成されました。

Testing for correct installation
SYS.DBMS_PROFILER successfully loaded.

PL/SQLプロシージャが正常に完了しました。

プロファイリング結果保存用テーブルの作成

プロファイリング対象プログラムを実行するユーザーで接続します。
CONN johnny/cougar
"$ORACLE_HOME/rdbms/admin/proftab.sql" を実行し、
"DBMS_PROIFILER" パッケージが使用するテーブル、シーケンスを作成します。
@?\rdbms\admin\proftab.sql
実行結果 
SQL> @?\rdbms\admin\proftab.sql
drop table plsql_profiler_data cascade constraints
           *
1行でエラーが発生しました。
ORA-00942: 表またはビューが存在しません。

drop table plsql_profiler_units cascade constraints
           *
1行でエラーが発生しました。
ORA-00942: 表またはビューが存在しません。

drop table plsql_profiler_runs cascade constraints
           *
1行でエラーが発生しました。
ORA-00942: 表またはビューが存在しません。

drop sequence plsql_profiler_runnumber
              *
1行でエラーが発生しました。
ORA-02289: 順序が存在しません。

表が作成されました。

コメントが作成されました。

表が作成されました。

コメントが作成されました。

表が作成されました。

コメントが作成されました。

順序が作成されました。
次のようなテーブルが作成されます。
■ plsql_profiler_runs
runid NUMBER plsql_profiler_runnumberで作成される一意の実行識別子。
run_owner VARCHAR2(32) 実行を開始したユーザー。
run_date DATE 実行の開始時間。
run_comment VARCHAR2(2047) この実行に関してユーザーが指定したコメント。
run_total_time NUMBER この実行の経過時間(ナノ秒)。
■ plsql_profiler_units
runid NUMBER plsql_profiler_runnumberで作成される一意の実行識別子。
unit_number NUMBER 内部的に生成されたライブラリ・ユニット番号。
■ plsql_profiler_data
runid NUMBER 一意の(生成された)実行識別子。
unit_number NUMBER 内部的に生成されたライブラリ・ユニット番号。
line# NUMBER ユニット内のNULL以外の行番号。
total_occur NUMBER 行が実行された回数。
total_time NUMBER 行の実行に要した合計時間(ナノ秒)。
min_time NUMBER この行の最小実行時間(ナノ秒)。
max_time NUMBER この行の最大実行時間(ナノ秒)。
補 足 
テーブル等を一旦削除してから作成していますので、初めて実行するときは、
ご覧のように「エラーが発生」しますが、問題ありません。

1.5. 「プロファイリング」を行ってみる

この節では、"DBMS_PROIFILER" パッケージを使用して、プロファイリングを行う簡単な例を紹介します。

プロファイリング対象プログラムの作成

まず、プロファイリング対象プログラムを作成します。
ここでは、さまざまな演算に、どのくらい時間がかかるのか計測してみます。
CREATE OR REPLACE PROCEDURE chapter1_test
IS
    -- PLS_INTEGER の 演算用
    v_int1          PLS_INTEGER;
    v_int2          PLS_INTEGER;
    v_int3          PLS_INTEGER;

    -- NUMBER の 演算用
    v_num1          NUMBER;
    v_num2          NUMBER;
    v_num3          NUMBER;

    -- 数値⇔文字列の変換用
    s               VARCHAR2(10);

    -- ローカルファンクション
    FUNCTION        func_add
    (
        a_int1  IN  PLS_INTEGER
    ,   a_int2  IN  PLS_INTEGER
    )   RETURN      PLS_INTEGER
    IS
    BEGIN
        RETURN(a_int1 + a_int2);
    END;

    -- ローカルプロシ−ジャ
    PROCEDURE       proc_add
    (
        a_int1  IN  PLS_INTEGER
    ,   a_int2  IN  PLS_INTEGER
    ,   a_int3  OUT PLS_INTEGER
    )
    IS
    BEGIN
        a_int3 := a_int1 + a_int2;
    END;
BEGIN
    --100万回ループさせる
    FOR loop_cnt IN 1..1000000
    LOOP
        --ループカウンタの剰余を使って、演算用の数値が大きな値にならないようする
        v_int1      :=  MOD(loop_cnt, 10000);
        IF  v_int1  =   0       THEN
            v_int1  :=  10000;
        END IF;
        v_num1      :=  MOD(loop_cnt, 10000);
        IF  v_num1  =   0       THEN
            v_num1  :=  10000;
        END IF;
        v_int2      :=  MOD(loop_cnt, 1000);
        IF  v_int2  =   0       THEN
            v_int2  :=  1000;
        END IF;
        v_num2      :=  MOD(loop_cnt, 1000);
        IF  v_num2  =   0       THEN
            v_num2  :=  1000;
        END IF;

        -- PLS_INTEGER の 演算
        v_int3 := v_int1 + v_int2;
        v_int3 := v_int1 - v_int2;
        v_int3 := v_int1 * v_int2;
        v_int3 := v_int1 / v_int2;
        v_int3 := MOD(v_int1, v_int2);
        v_int3 := v_int1 ** 2;
        v_int3 := POWER(v_int1, 2);

        -- NUMBER の 演算
        v_num3 := v_num1 + v_num2;
        v_num3 := v_num1 - v_num2;
        v_num3 := v_num1 * v_num2;
        v_num3 := v_num1 / v_num2;
        v_num3 := MOD(v_num1, v_num2);
        v_num3 := v_num1 ** 2;
        v_num3 := POWER(v_num1, 2);

        -- 文字列への変換
        s := TO_CHAR(v_int1);
        s := TO_CHAR(v_num1);

        -- 数値への変換
        v_int3 := TO_NUMBER(s);
        v_num3 := TO_NUMBER(s);

        -- さまざまな IF文
        IF v_int1 =   1000                       THEN
            NULL;
        END IF;
        IF v_int1 !=  1000                       THEN
            NULL;
        END IF;
        IF (v_int1 <  1000) OR  (1000 < v_int1)  THEN
            NULL;
        END IF;
        IF (1000 <= v_int1) AND (v_int1 <= 2000) THEN
            NULL;
        END IF;
        IF v_int1 BETWEEN 1000 AND 2000          THEN
            NULL;
        END IF;
        IF (v_int1 = 1000) OR (v_int1 = 2000)    THEN
            NULL;
        END IF;
        IF v_int1 IN (1000, 2000)                THEN
            NULL;
        END IF;

        IF    v_int1 = 1000 THEN
            NULL;
        ELSIF v_int1 = 2000 THEN
            NULL;
        ELSE
            NULL;
        END IF;

        -- CASE文
        CASE v_int1
            WHEN 1000 THEN
                NULL;
            WHEN 2000 THEN
                NULL;
            ELSE
                NULL;
        END CASE;

        -- PROCEDURE の 呼び出し
        proc_add(v_int1, v_int2, v_int3);

        -- FUNCTION の 呼び出し
        v_int3 := func_add(v_int1, v_int2);
    END LOOP;

END;
/
補 足 
■ループカウンタについて
PL/SQL では、「FOR ループ」の「ループカウンタ」を宣言する必要はありません。
BINARY_INTEGER型で暗黙に宣言されます。

プロファイリングの開始

後で識別可能なコメントを指定して、プロファイリング開始を指示します。
EXECUTE DBMS_PROFILER.START_PROFILER('プロファイリングのテスト')
補 足 
■ EXECUTE
"EXECUTE 〜 " は、"BEGIN 〜; END;" と変換されて実行されるので、末尾に ";" は不要です。

プロファイリング対象プログラムを実行

プロファイリング対象プログラムを実行します。
EXECUTE chapter1_test

プロファイリングの終了

プロファイリング終了を指示します。
EXECUTE DBMS_PROFILER.STOP_PROFILER

プロファイリング結果を取得

プロファイリング開始時に指定したコメントを用いて、プロファイリング結果を取得します。
下の例では、出力が見やすいように、書式を設定しています。
見出しの表示を 1000行ごとに、
1行の横幅を 500文字に、
名前を 25文字に、
回数を 8桁に、
行を 3桁に、
設定しています。
また、結果を "c:\plsql\spool.txt" というファイルに出力するよう指定しています。
SET PAGESIZE   1000
SET LINESIZE   500
COLUMN "名前"  FORMAT A25
COLUMN "回数"  FORMAT 99999999
COLUMN "行"    FORMAT 999
SET TRIMSPOOL ON
SPOOL c:\plsql\spool.txt
--
SELECT
    p.unit_name                           "名前"
,   p.total_occur                         "回数"
,   TO_CHAR(p.total_time,'000000.000000') "実行時間"
,   p.line#                               "行"
,   SUBSTR(s.text, 1, 200)                "ソース"
FROM
(
    SELECT
        u.unit_name
    ,   d.total_occur
--  ,   d.total_time                       -- ナノ秒
--  ,   d.total_time/1000       total_time -- マイクロ秒
--  ,   d.total_time/1000000    total_time -- ミリ秒
    ,   d.total_time/1000000000 total_time --  秒
    ,   d.line#
    FROM
        plsql_profiler_units u
    ,   plsql_profiler_data  d
    ,   plsql_profiler_runs  r
    WHERE d.runid       = u.runid
      AND d.unit_number = u.unit_number
      AND d.total_occur > 0
      AND r.run_comment = 'プロファイリングのテスト' -- プロファイリング開始時に指定したコメント
      AND u.runid       = r.runid
    ) p
,   user_source s
WHERE p.unit_name = s.name(+)
  AND p.line#     = s.line(+)
ORDER BY
    p.unit_name
,   p.line#
;
SPOOL OFF
補 足 
■ SQL*Plus のコマンド について
SET ECHO ON/OFF@〜 でスクリプトを実行した場合、コマンドを表示する / しない
SET FEEDBACK ON/OFF問合せから戻されるレコード数を表示する / しない
SET HEADING ON/OFF列ヘッダーを表示する / しない
SET LINESIZE n1行の幅を n 文字にする
SET PAGESIZE n列ヘッダを n行おきに表示する
SET TRIMOUT ON/OFF表示行の終わりに後続の空白を除く / 除かない
SET TRIMSPOOL ON/OFFスプール行の終わりに後続の空白を除く / 除かない
SPOOL ファイル名問合せの結果を、指定されたファイルに格納する
SPOOL OFFSPOOL を終了する
COLUMN カラム名 FORMAT 書式列の書式を設定する
■ FORMAT 書式 について
9,999指定した位置にカンマを表示する
99.99数値の整数部と小数部を区切る小数点を表示する
$9999先行ドル記号を表示する
00000先行0(ゼロ)を表示する
999999 の数で指定した桁数で表示する
An文字列を n桁で表示する
実行結果 
名前                 回数 実行時間         行 ソース
--------------- --------- -------------- ---- ---------------------------------------------
<anonymous>             2  000000.111832    1
<anonymous>             2  000000.320459    1
<anonymous>             1  000000.001555    2
<anonymous>             1  000000.118094    2
<anonymous>             2  000000.080660    2
CHAPTER1_TEST     2000000  002366.534108   24         RETURN(a_int1 + a_int2);
CHAPTER1_TEST     1000000  001404.048740   36         a_int3 := a_int1 + a_int2;
CHAPTER1_TEST     2000001  002256.238357   40     FOR loop_cnt IN 1..1000000
CHAPTER1_TEST     1000000  005549.684937   43         v_int1      :=  MOD(loop_cnt, 10000);
CHAPTER1_TEST     1000000  000752.464351   44         IF  v_int1  =   0       THEN
CHAPTER1_TEST         100  000000.053095   45             v_int1  :=  10000;
CHAPTER1_TEST     1000000  003818.909145   47         v_num1      :=  MOD(loop_cnt, 10000);
CHAPTER1_TEST     1000000  001091.868057   48         IF  v_num1  =   0       THEN
CHAPTER1_TEST         100  000000.063860   49             v_num1  :=  10000;
CHAPTER1_TEST     1000000  003796.225075   51         v_int2      :=  MOD(loop_cnt, 1000);
CHAPTER1_TEST     1000000  000724.961621   52         IF  v_int2  =   0       THEN
CHAPTER1_TEST        1000  000000.538552   53             v_int2  :=  1000;
CHAPTER1_TEST     1000000  003610.172942   55         v_num2      :=  MOD(loop_cnt, 1000);
CHAPTER1_TEST     1000000  001012.227244   56         IF  v_num2  =   0       THEN
CHAPTER1_TEST        1000  000000.665986   57             v_num2  :=  1000;
CHAPTER1_TEST     1000000  000660.240931   61         v_int3 := v_int1 + v_int2;
CHAPTER1_TEST     1000000  000718.914508   62         v_int3 := v_int1 - v_int2;
CHAPTER1_TEST     1000000  000693.636310   63         v_int3 := v_int1 * v_int2;
CHAPTER1_TEST     1000000  003881.073593   64         v_int3 := v_int1 / v_int2;
CHAPTER1_TEST     1000000  004738.126502   65         v_int3 := MOD(v_int1, v_int2);
CHAPTER1_TEST     2000000  003232.242372   66         v_int3 := v_int1 ** 2;
CHAPTER1_TEST     1000000  002964.405186   67         v_int3 := POWER(v_int1, 2);
CHAPTER1_TEST     1000000  001203.521244   70         v_num3 := v_num1 + v_num2;
CHAPTER1_TEST     1000000  001113.617035   71         v_num3 := v_num1 - v_num2;
CHAPTER1_TEST     1000000  000872.569996   72         v_num3 := v_num1 * v_num2;
CHAPTER1_TEST     1000000  002770.068184   73         v_num3 := v_num1 / v_num2;
CHAPTER1_TEST     1000000  004152.885862   74         v_num3 := MOD(v_num1, v_num2);
CHAPTER1_TEST     2000000  002433.135744   75         v_num3 := v_num1 ** 2;
CHAPTER1_TEST     1000000  002613.598614   76         v_num3 := POWER(v_num1, 2);
CHAPTER1_TEST     1000000  009701.390183   79         s := TO_CHAR(v_int1);
CHAPTER1_TEST     1000000  007327.724853   80         s := TO_CHAR(v_num1);
CHAPTER1_TEST     1000000  002057.615743   83         v_int3 := TO_NUMBER(s);
CHAPTER1_TEST     1000000  000872.718552   84         v_num3 := TO_NUMBER(s);
CHAPTER1_TEST     1000000  000869.099302   87         IF v_int1 =   1000                       THEN
CHAPTER1_TEST     1000000  000708.409011   90         IF v_int1 !=  1000                       THEN
CHAPTER1_TEST     1000000  001037.456277   93         IF (v_int1 <  1000) OR  (1000 < v_int1)  THEN
CHAPTER1_TEST     1000000  000991.233486   96         IF (1000 <= v_int1) AND (v_int1 <= 2000) THEN
CHAPTER1_TEST     1000000  001139.487826   99         IF v_int1 BETWEEN 1000 AND 2000          THEN
CHAPTER1_TEST     1000000  000609.577902  100             NULL;
CHAPTER1_TEST     1000000  000974.630113  102         IF (v_int1 = 1000) OR (v_int1 = 2000)    THEN
CHAPTER1_TEST     1000000  001128.297524  105         IF v_int1 IN (1000, 2000)                THEN
CHAPTER1_TEST     1000000  000514.819409  106             NULL;
CHAPTER1_TEST     1000000  000703.606984  109         IF    v_int1 = 1000 THEN
CHAPTER1_TEST         100  000000.046720  110             NULL;
CHAPTER1_TEST      999900  000671.032588  111         ELSIF v_int1 = 2000 THEN
CHAPTER1_TEST         100  000000.046686  112             NULL;
CHAPTER1_TEST     1000000  001073.677768  118         CASE v_int1
CHAPTER1_TEST         100  000000.046709  120                 NULL;
CHAPTER1_TEST         100  000000.046686  122                 NULL;
CHAPTER1_TEST     2000000  001556.655708  128         proc_add(v_int1, v_int2, v_int3);
CHAPTER1_TEST     2000000  001419.561512  131         v_int3 := func_add(v_int1, v_int2);
"total_time" は、ナノ秒 (10億分の1秒) 単位であると、マニュアルに書いてあるので、
1,000,000,000 (10億) で割って、 秒単位で表示したつもりですが、値が大きすぎます。
どうやら、ピコ秒単位のようです。

それはさておき、プロファイリングの結果を細かくみていきましょう。
■ 四則演算について
加・減・乗算については、"PLS_INTEGER"による演算の方が速いのですが、
除算・剰余については、"NUMBER"の方が速いことがわかります。
四捨五入処理の影響でしょうか?
CHAPTER1_TEST     1000000  000660.240931   61         v_int3 := v_int1 + v_int2;
CHAPTER1_TEST     1000000  000718.914508   62         v_int3 := v_int1 - v_int2;
CHAPTER1_TEST     1000000  000693.636310   63         v_int3 := v_int1 * v_int2;
CHAPTER1_TEST     1000000  003881.073593   64         v_int3 := v_int1 / v_int2;
CHAPTER1_TEST     1000000  004738.126502   65         v_int3 := MOD(v_int1, v_int2);

CHAPTER1_TEST     1000000  001203.521244   70         v_num3 := v_num1 + v_num2;
CHAPTER1_TEST     1000000  001113.617035   71         v_num3 := v_num1 - v_num2;
CHAPTER1_TEST     1000000  000872.569996   72         v_num3 := v_num1 * v_num2;
CHAPTER1_TEST     1000000  002770.068184   73         v_num3 := v_num1 / v_num2;
CHAPTER1_TEST     1000000  004152.885862   74         v_num3 := MOD(v_num1, v_num2);
■ べき乗について
"PLS_INTEGER"よりは、"NUMBER"による演算の方が速いようです。
また、"PLS_INTEGER" では、"**" より、"POWER" を使った方が速く、
"NUMBER"では "**" の方が速いようです。
CHAPTER1_TEST     2000000  003232.242372   66         v_int3 := v_int1 ** 2;
CHAPTER1_TEST     1000000  002964.405186   67         v_int3 := POWER(v_int1, 2);

CHAPTER1_TEST     2000000  002433.135744   75         v_num3 := v_num1 ** 2;
CHAPTER1_TEST     1000000  002613.598614   76         v_num3 := POWER(v_num1, 2);
■ 等号、不等号について
"=" より、"!=" の方が速いようです。
「等しくない」を判定するには、普通に "!=" を使った方が、
"<" と ">" を組み合わせるより速いようです。
CHAPTER1_TEST     1000000  000869.099302   87         IF v_int1 =   1000                       THEN
CHAPTER1_TEST     1000000  000708.409011   90         IF v_int1 !=  1000                       THEN
CHAPTER1_TEST     1000000  001037.456277   93         IF (v_int1 <  1000) OR  (1000 < v_int1)  THEN
■ "BETWEEN" について
範囲指定には、"BETWEEN" を使うより、"<=" と ">=" を組み合わせた方が速いようです。
これは、"1000 <= v_int1" が成立しなかった場合、"v_int1 <= 2000" を判定しない
「短絡評価」による結果でしょう。
CHAPTER1_TEST     1000000  000991.233486   96         IF (1000 <= v_int1) AND (v_int1 <= 2000) THEN
CHAPTER1_TEST     1000000  001139.487826   99         IF v_int1 BETWEEN 1000 AND 2000          THEN
■ "IN" について
OR 条件には、"IN" を使うより、複数の条件を "OR" で繋いだ方が速いようです。
これも、"1000 = v_int1" が成立した場合、"v_int1 = 2000" を判定しない
「短絡評価」による結果でしょう。
CHAPTER1_TEST     1000000  000974.630113  102         IF (v_int1 = 1000) OR (v_int1 = 2000)    THEN
CHAPTER1_TEST     1000000  001128.297524  105         IF v_int1 IN (1000, 2000)                THEN
■ "IF"文と "CASE"文 について
IF文より、CASE文の方が、実行回数が少なくて済み、速いようです。
CHAPTER1_TEST     1000000  000703.606984  109         IF    v_int1 = 1000 THEN
CHAPTER1_TEST      999900  000671.032588  111         ELSIF v_int1 = 2000 THEN
CHAPTER1_TEST     1000000  001073.677768  118         CASE v_int1
■ "PROCEDURE" と "FUNCTION" について
呼び出しには、"PROCEDURE"の方が時間がかかるようですが、
値を返すのは、"FUNCTION"の方が時間がかかるようです。
CHAPTER1_TEST     2000000  002366.534108   24         RETURN(a_int1 + a_int2);
CHAPTER1_TEST     2000000  001419.561512  131         v_int3 := func_add(v_int1, v_int2);

CHAPTER1_TEST     1000000  001404.048740   36         a_int3 := a_int1 + a_int2;
CHAPTER1_TEST     2000000  001556.655708  128         proc_add(v_int1, v_int2, v_int3);
■ 文字列への変換
"PLS_INTEGER" より、"NUMBER"の方が速いようです。
CHAPTER1_TEST     1000000  009701.390183   79         s := TO_CHAR(v_int1);
CHAPTER1_TEST     1000000  007327.724853   80         s := TO_CHAR(v_num1);
これは、"NUMBER"が、二進化十進数形式で格納されていることを考えれば当たり前かもしれません。
たとえば、12 という数値は、以下のような形式で格納されています。
("NUMBER" には、指数や、符号も格納しているので、この通りというわけではありません。)
PLS_INTEGER など 通常の2進数 0xC
NUMBER など 2進化10進数 0x12
CHAR など 文字列 0x3132
■ 文字列から数値への変換
"PLS_INTEGER" より、"NUMBER"の方が速いようです。
これも、"NUMBER"の内部形式を考えれば当たり前でしょう。
CHAPTER1_TEST     1000000  002057.615743   83         v_int3 := TO_NUMBER(s);
CHAPTER1_TEST     1000000  000872.718552   84         v_num3 := TO_NUMBER(s);

1.6. 「プロファイリング」実行用スクリプトを作成する

毎回、これら一連のSQL
・プロファイリング開始
・プログラム実行
・プロファイリング終了
・プロファイリング結果取得
を指定するのも手間だし、間違いの元なので、
それらを自動で行えるようなスクリプトを作成して保存しておきます。
プロファイリング対象プログラムを、「置換文字列 (下記補足 参照)」にして、
実行時に指定できるようにします (下の例では、"&program_name")。
また、識別用のコメントを、「バインド変数 (下記補足 参照)」にして、システム日付から自動で作成し、
プロファイリングの開始時、結果取得時に渡すようにしています(下の例では、":v_run_comment")。
スクリプト内のコマンドを表示しない ("SET ECHO OFF") という設定も追加しています。
-- 識別用のコメントを用意 (バインド変数)
VARIABLE v_run_comment VARCHAR2(14);
-- プロファイリングを実行
BEGIN
    -- 識別可能なコメント を作成
    SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')
    INTO   :v_run_comment
    FROM DUAL
    ;

    -- プロファイリングの開始
    DBMS_PROFILER.START_PROFILER(:v_run_comment);

    -- プロファイリング対象プログラムを実行
    &program_name;

    -- プロファイリングの終了
    DBMS_PROFILER.STOP_PROFILER;
END;
/
-- 出力書式の設定
SET PAGESIZE   1000
SET LINESIZE   500
COLUMN "名前"  FORMAT A25
COLUMN "回数"  FORMAT 99999999
COLUMN "行"    FORMAT 999
SET ECHO OFF
SET TRIMSPOOL ON
SPOOL c:\plsql\spool.txt
-- プロファイリング結果を取得
SELECT
    p.unit_name                           "名前"
,   p.total_occur                         "回数"
,   TO_CHAR(p.total_time,'000000.000000') "実行時間"
,   p.line#                               "行"
,   SUBSTR(s.text, 1, 200)                "ソース"
FROM
(
    SELECT
        u.unit_name
    ,   d.total_occur
--  ,   d.total_time                       -- ナノ秒
--  ,   d.total_time/1000       total_time -- マイクロ秒
--  ,   d.total_time/1000000    total_time -- ミリ秒
    ,   d.total_time/1000000000 total_time --  秒
    ,   d.line#
    FROM
        plsql_profiler_units u
    ,   plsql_profiler_data  d
    ,   plsql_profiler_runs  r
    WHERE d.runid       = u.runid
      AND d.unit_number = u.unit_number
      AND d.total_occur > 0
      AND r.run_comment = :v_run_comment
      AND u.runid       = r.runid
    ) p
,   user_source s
WHERE p.unit_name = s.name(+)
  AND p.line#     = s.line(+)
ORDER BY
    p.unit_name
,   p.line#
;
SPOOL OFF
このスクリプトを、"c:\plsql\profile.sql" として保存します。
保存したスクリプトを実行すると「プロファイリング対象プログラム」の入力を求められます。
@c:\plsql\profile.sql
実行結果 
SQL> @c:\plsql\profile.sql
program_nameに値を入力してください: chapter1_test
旧  12:     &program_name;
新  12:     chapter1_test;

PL/SQLプロシージャが正常に完了しました。

名前                 回数 実行時間         行 ソース
--------------- --------- -------------- ---- ---------
<anonymous>             2  000000.111832    1
<anonymous>             2  000000.320459    1

・・・省略・・・
補 足 
■ バインド変数 について
SQL*Plus では、PL/SQL、SQL に対して変数を渡すことができます
(上記の例では ":v_run_comment")。
バインド変数は、ブロックの外部で割り当てられるので、
一連の PL/SQL、SQL を通して利用できますし、
一連の処理が終わった後でも、"PRINT 変数名" コマンドで、
値を出力することもできます。

■ 置換文字列 について
SQL*Plus では、PL/SQL、SQL を実行する前に、
文の一部を入力内容で置き換えることができます
(上記の例では "&program_name")。
バインド変数と違って、変数の値をセットするのではなく、
PL/SQL、SQL 自体を書き換えますので、テーブル名、列名、プロシージャ名など、
何にでも使用できます。

1.7. 「実行計画」採取用テーブルを作成する

通常、処理対象のレコードにアクセスする方法(アクセスパス)は複数存在します。
たとえば、テーブルを全件読むか、インデックスを使用するか、
インデックスを使用するとしたら、どのインデックスを使用するか、
また、表を結合させる場合は、どの順序でどう結合させればよいか、などです。
このように、どのアクセスパスを使用するか、といった組み合わせを「実行計画」と呼びます。
Oracle は SQL 文を解析した後、複数の実行計画の中から、最も効率的と思われる実行計画を選択します。
この節では、Oracle がどのような実行計画に基づいて処理を行うかを分析するための準備をします。

「実行計画」採取用テーブルの作成

実行計画を表示させたいユーザーで接続して、"$ORACLE_HOME/rdbms/admin/utlxplan.sql" を実行し、
テーブル "plan_table" を作成します。
CONN johnny/cougar
@?\rdbms\admin\utlxplan.sql
実行結果 
SQL> CONN johnny/cougar
接続されました。
SQL> @?\rdbms\admin\utlxplan.sql

表が作成されました。

ロールの作成

SQL*Plus で実行計画を取得するために、
"sysdba"ユーザーで "$ORACLE_HOME/sqlplus/admin/plustrce.sql" を実行し、
"plustrace" ロールを作成します
CONN / AS sysdba
@?\sqlplus\admin\plustrce.sql
実行結果 
SQL> CONN / AS sysdba
接続されました。
SQL> @?\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;

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

権限の付与

実行計画を表示するユーザーに、"plustrace" ロールを付与します。
CONN / AS sysdba
GRANT plustrace TO johnny
;

実行計画の表示

実行計画を表示するには、"AUTOTRACE ON" とした後、SQL を実行するだけです。
実例は、この後の章で紹介して行きます。
inserted by FC2 system