PL/SQLのカバレッジツールを作ろう２
簡単にできるのですが、エクセルやサクラエディタからはイマイチ使えない。理由は単純でカバレッジのデータ収集開始をしたセッションと同じセッションで実行したストアドプログラムしかデータを収集してくれないから。
エクセルですべてのストアドプログラムを流してね。ってのはちと無理があるので作るのは止めにします。
同じセッションでテストする、あるいはセッションの開始時にデータ収集の開始、セッションの終わりにデータ収集の終了のコマンドを実行すれば利用可能です。残念ながら企画したとおりには出来なかったのですが、手作業でカバレッジを採る方法を書き留めておきます。
※ インデントは全角ブランクで行っていますので、コピペするときに全角ブランクを半角に変換してください。
作ろうとしていた内容をご説明
１．カバレッジのデータを保存するテーブルを作成する。
RDBMS/ADMINディレクトリにあるPROFTAB.SQLというスクリプトファイルを実行する。
※ 最後に付けました。
２．データの収集を開始します。
-- DBMS_PROFILER.START_PROFILER を実行する。
SELECT
CASE
WHEN DBMS_PROFILER.START_PROFILER('コメント1', 'コメント2') = 0 THEN
'profiling start'
ELSE
'error occurred'
END AS Return_State
FROM dual;
３．ストアドプログラムを実行（テスト）します。
４．メモリー上のデータをテーブルに保存します。
データはメモリー上に溜まるので貯めすぎないように、適時、テーブルに保存します。
-- DBMS_PROFILER.FLUSH_DATA を実行する。
SELECT
CASE
WHEN DBMS_PROFILER.FLUSH_DATA = 0 THEN
'flush data'
ELSE
'error occurred'
END AS Return_State
FROM dual;
５．データの収集を終了します。
メモリー上のデータをテーブルに保存して終了します。
-- DBMS_PROFILER.STOP_PROFILER を実行する。
SELECT
CASE
WHEN DBMS_PROFILER.STOP_PROFILER = 0 THEN
'profiling end'
ELSE
'error occurred'
END AS Return_State
FROM dual;
データを確認する
-- 以下のSQLを実行する。
-- 合計実行時間が０なっている行は通ってない。
-- NULLになっている行はコメントや宣言などの行になります。
-- 合計実行時間、最長実行時間の値が大きい行がチューニングポイントです。
※ 間違いがあったので修正。テストはしっかりしないと行けないね〜。
SELECT
a.TYPE AS プログラムタイプ
, a.NAME AS 名前
, a.LINE AS 行
, a.TEXT AS プログラムソース
, SUM(e.TOTAL_OCCUR) AS 実行回数
, SUM(e.TOTAL_TIME) AS 合計実行時間
, MIN(e.MIN_TIME) AS 最短実行時間
, MAX(e.MAX_TIME) AS 最長実行時間
FROM
SYS.USER_SOURCE a
INNER JOIN SYS.USER_OBJECTS b
ON a.TYPE = b.OBJECT_TYPE
AND a.NAME = b.OBJECT_NAME
LEFT JOIN
(SELECT
uni.UNIT_TYPE
, uni.UNIT_NAME
, dat.LINE# AS LINE
, SUM(dat.TOTAL_OCCUR) AS TOTAL_OCCUR
, SUM(dat.TOTAL_TIME) AS TOTAL_TIME
, MIN(dat.MIN_TIME) AS MIN_TIME
, MIN(dat.MAX_TIME) AS MAX_TIME
, run.RUN_DATE
FROM
PLSQL_PROFILER_RUNS run
INNER JOIN PLSQL_PROFILER_UNITS uni
ON run.RUNID = uni.RUNID
INNER JOIN PLSQL_PROFILER_DATA dat
ON uni.RUNID = dat.RUNID
AND uni.UNIT_NUMBER = dat.UNIT_NUMBER
GROUP BY
uni.UNIT_TYPE, uni.UNIT_NAME, dat.LINE#, run.RUN_DATE) e
ON a.TYPE = e.UNIT_TYPE
AND a.NAME = e.UNIT_NAME
AND a.LINE = e.LINE
AND b.LAST_DDL_TIME < e.RUN_DATE -- 修正されたらやり直し
GROUP BY
a.TYPE, a.NAME, a.LINE, a.TEXT
ORDER BY
a.TYPE, a.NAME, a.LINE
PROFTAB.SQLの内容
drop table plsql_profiler_data cascade constraints;
drop table plsql_profiler_units cascade constraints;
drop table plsql_profiler_runs cascade constraints;
drop sequence plsql_profiler_runnumber;
create table plsql_profiler_runs
(
runid number primary key, -- unique run identifier,
-- from plsql_profiler_runnumber
related_run number, -- runid of related run (for client/
-- server correlation)
run_owner varchar2(32), -- user who started run
run_date date, -- start time of run
run_comment varchar2(2047), -- user provided comment for this run
run_total_time number, -- elapsed time for this run
run_system_info varchar2(2047), -- currently unused
run_comment1 varchar2(2047), -- additional comment
spare1 varchar2(256) -- unused
);
comment on table plsql_profiler_runs is
'Run-specific information for the PL/SQL profiler';
create table plsql_profiler_units
(
runid number references plsql_profiler_runs,
unit_number number, -- internally generated library unit #
unit_type varchar2(32), -- library unit type
unit_owner varchar2(32), -- library unit owner name
unit_name varchar2(32), -- library unit name
-- timestamp on library unit, can be used to detect changes to
-- unit between runs
unit_timestamp date,
total_time number DEFAULT 0 NOT NULL,
spare1 number, -- unused
spare2 number, -- unused
--
primary key (runid, unit_number)
);
comment on table plsql_profiler_units is
'Information about each library unit in a run';
create table plsql_profiler_data
(
runid number, -- unique (generated) run identifier
unit_number number, -- internally generated library unit #
line# number not null, -- line number in unit
total_occur number, -- number of times line was executed
total_time number, -- total time spent executing line
min_time number, -- minimum execution time for this line
max_time number, -- maximum execution time for this line
spare1 number, -- unused
spare2 number, -- unused
spare3 number, -- unused
spare4 number, -- unused
--
primary key (runid, unit_number, line#),
foreign key (runid, unit_number) references plsql_profiler_units
);
comment on table plsql_profiler_data is
'Accumulated data from all profiler runs';
create sequence plsql_profiler_runnumber start with 1 nocache;
