第1章 準備作業
この章では、本書で説明するプログラムを実行するために、以下の準備作業を行います。
・表領域を作成する
・ユーザーを作成する
・権限を付与する
・「プロファイリング」用のパッケージと、テーブルを作成する
・「プロファイリング」を行ってみる
・「プロファイリング」実行用スクリプトを作成する
・「実行計画」採取用テーブルを作成する
|
1.1. 表領域を作成する
まず最初に、本書で使用するテーブルを格納するための、表領域を作成します。
本書では、以下のような表領域を作成、使用します。
| 表領域名 | ファイル名 |
データ用表領域 | sample_data | c:\oracle\oradata\orcl\sample_data.dbf |
索引用表領域 | sample_index | c:\oracle\oradata\orcl\sample_index.dbf |
一時作業用表領域 | sample_temp | c:\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 n | 1行の幅を n 文字にする |
SET PAGESIZE n | 列ヘッダを n行おきに表示する |
SET TRIMOUT ON/OFF | 表示行の終わりに後続の空白を除く / 除かない |
SET TRIMSPOOL ON/OFF | スプール行の終わりに後続の空白を除く / 除かない |
SPOOL ファイル名 | 問合せの結果を、指定されたファイルに格納する |
SPOOL OFF | SPOOL を終了する |
COLUMN カラム名 FORMAT 書式 | 列の書式を設定する |
■ FORMAT 書式 について
9,999 | 指定した位置にカンマを表示する |
99.99 | 数値の整数部と小数部を区切る小数点を表示する |
$9999 | 先行ドル記号を表示する |
00000 | 先行0(ゼロ)を表示する |
99999 | 9 の数で指定した桁数で表示する |
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 を実行するだけです。
実例は、この後の章で紹介して行きます。