MySWL×SCSK

MySQL ClusterをはじめとするHAソリューションなど、
SCSKが最先端の技術力でソリューションを提供します

見積依頼 資料ダウンロードはこちら

TOPICS

[OracleDB] SQLのボトルネックを特定する方法

 Oracleのパフォーマンスチューニングで役立つ(実務で実際に役立った)ネタを紹介していきます。

 SQLのボトルネックを特定する方法は色々ありますが、PL/SQLのボトルネックを特定する方法はご存知でしょうか?

 DBMS_PROFILERを使用することで、PL/SQL実行時のステップ毎の実行時間を測定できるようになります。
 これにより、PL/SQLのソースのどのステップで実行に時間が掛かっているか特定することができます。

[参考] DBMS_PROFILER -Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス
https://docs.oracle.com/cd/E82638_01/ARPLS/DBMS_PROFILER.htm


 SCSK株式会社は、本ドキュメントの使用の結果生じるいかなる損害についても、責任を負いかねますのでご了承ください。本番環境で実施する際には、十分検証してからの実施を推奨します。

1. DBMS_PROFILERパッケージのインストール

    bash-3.2$ sqlplus / as sysdba
    SQL> @$ORACLE_HOME/rdbms/admin/profload.sql

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

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

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

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

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

    Testing for correct installation
    SYS.DBMS_PROFILER successfully loaded.

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

    SQL>


2. プロファイラが使用するテーブルを作成

    bash-3.2$ sqlplus test/test
    SQL> @$ORACLE_HOME/rdbms/admin/proftab.sql


3. テスト用テーブルを作成

    SQL> @cr_sample_tbl.sql
    SQL> create table sample_tbl
    2 (
    3 id number,
    4 cola varchar2(100)
    5 )
    6 ;


4. テスト用プロシージャのコンパイル

    SQL> @sample_proc.sql
    SQL> CREATE OR REPLACE PROCEDURE sample_proc IS
    2 cnt number:=0;
    3 BEGIN
    4 FOR i IN 1..500000 LOOP
    5 insert into sample_tbl values (i,'aaaaaaaaaa');
    6 END LOOP;
    7 commit;
    8 select count(*) into cnt from sample_tbl;
    9 dbms_output.put_line(cnt);
    10 END;
    11 /

    プロシージャが作成されました。

    経過: 00:00:00.09
    SQL> show error
    エラーはありません。
    SQL>


5. テスト用プロシージャの実行

    SQL> @exec_sample_proc.sql
    SQL> declare
    2 err number;
    3 begin
    4 err:=DBMS_PROFILER.START_PROFILER (to_char(sysdate,'dd-Mon-YYYY hh:mi:ss'));
    5
    6 sample_proc;
    7
    8 err:=DBMS_PROFILER.STOP_PROFILER ;
    9 end;
    10 /
    500000

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

    経過: 00:01:23.89


6. プロファイリング結果の取得

    SQL> @show_profiling_result.sql
    SQL> column RUN_COMMENT format a40
    SQL> select runid, run_date, RUN_COMMENT from plsql_profiler_runs order by runid;

    RUNID RUN_DATE RUN_COMMENT
    ---------- -------- ----------------------------------------
    1 18-05-10 10-5月 -2018 12:48:14
    2 18-05-10 10-5月 -2018 12:56:14
    3 18-05-10 10-5月 -2018 01:01:41
    4 18-05-10 10-5月 -2018 01:13:18

    経過: 00:00:00.01
    SQL>
    SQL> column unit_name format a15
    SQL> column occured format 999999
    SQL> column line# format 99999
    SQL> column tot_time format 999.999999
    SQL> column text format a60
    SQL> select
    2 p.unit_name
    3 , p.occured
    4 , p.tot_time
    5 , p.line# line,
    6 substr(s.text, 1,75) text
    7 from
    8 (select
    9 u.unit_name
    10 , d.TOTAL_OCCUR occured
    11 , (d.TOTAL_TIME/1000000000) tot_time
    12 , d.line#
    13 from
    14 plsql_profiler_units u
    15 , plsql_profiler_data d
    16 where
    17 d.RUNID=u.runid
    18 and d.UNIT_NUMBER = u.unit_number
    19 and d.TOTAL_OCCUR >0
    20 and u.unit_name = '&proc_name'
    21 and u.runid = &id
    22 ) p
    23 , user_source s
    24 where
    25 p.unit_name = s.name(+)
    26 and p.line# = s.line (+)
    27 order by
    28 p.unit_name, p.line#;
    proc_nameに値を入力してください: SAMPLE_PROC ←対象のプロシージャ名を指定
    旧 20: and u.unit_name = '&proc_name'
    新 20: and u.unit_name = 'SAMPLE_PROC'
    idに値を入力してください: 4 ←対象のrunidを指定
    旧 21: and u.runid = &id
    新 21: and u.runid = 4

    UNIT_NAME OCCURED TOT_TIME LINE TEXT
    --------------- ------- ----------- ---------- ------------------------------------------------------------
    SAMPLE_PROC 1 .000001 2 cnt number:=0;
    SAMPLE_PROC 500001 .305684 4 FOR i IN 1..500000 LOOP
    SAMPLE_PROC 500000 82.493505 5 insert into sample_tbl values (i,'aaaaaaaaaa'); ←全実行時間(01:23.89)中ほとんどが
    SAMPLE_PROC 1 .018485 7 commit; このステップで消費していることが分かる。
    SAMPLE_PROC 1 .070546 8 select count(*) into cnt from sample_tbl;
    SAMPLE_PROC 1 .000718 9 dbms_output.put_line(cnt);
    SAMPLE_PROC 1 .000015 10 END;

    7行が選択されました。

    経過: 00:00:00.18
    SQL>


このようにステップごとの実行回数や実行時間が測定できるので、ソースのどこに
原因がどこにあるか特定できるようになります。(上記の例の場合はinsert処理)

SCSKだから選ばれる

10年以上にわたるMySQLの取り組み

10年以上にわたるMySQLの取り組み

MySQLオフィシャルトレーニングを多数担当。日本オラクル社のパートナー認定制度「MySQL Specialization」国内第1号取得。オリジナル全文検索ソリューション開発

SCSKの強み

SCSKの強み

オフィシャルトレーニング資格を有する技術者が提供する高い技術力。大規模通信系システム、大規模基幹系システムなども経験。国内企業数百社に対する導入実績。MySQLに関連する全てのサービスをワンストップで提供

お問い合わせ

MySQLやMySQL関連ソリューションに関するお問い合わせ、資料請求、お見積、ご相談などございましたら、こちらよりご連絡下さいませ。

お問い合わせ資料ダウンロード見積依頼

OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。文中の社名、商品名等は各社の商標または登録商標である場合があります。