oracle
パフォーマンスチューニング

Oracle SQLチューニング・アドバイザを利用する

これまでSQL Developerからの利用は経験あったのですが、今回はSQL*Plusからの利用を試してみます。共有メモリにアドバイスを受けたいSQLが残っている場合（対象のSQL_IDがわかっている）は、SQL*Plusからの実行がお手軽そうだったためです。シンプルなSQLであれば自力でなんとかなるのですが、結合テーブルも多く複雑なSQLに対してのチューニングが数多く出てくる時は、「いちいち見ていられない」と思い今回チューニング・アドバイザの本格活用に踏み切りました。

作業概要

  • 権限付与
  • チューニングタスクの作成
    • 必要に応じてチューニングタスク実行時パラメータの追加設定
  • チューニングアドバイザ・タスクを実行
  • SQLチューニング・タスクのレポートの表示
  • チューニングアドバイザ・タスクの削除

前提

以下のSQL（SQL_ID=’0tw5hbkvqrqbf’)のチューニングアドバイスを受けます。
select * from ex005 where c5 = :1;
※テーブルex005のカラムc5にはインデックスを作成していないので、インデックスを作成するようにアドバイスを受けるはずです。

チューニング・アドバイザを利用するユーザには権限付与が必要です

GRANT ADVISOR TO SOONI
;

チューニングタスクを作成します。

チューニングアドバイスを受ける事をタスクとして登録します。タスク名は重複がないなら何でもよいです。今回は「tun_0tw5hbkvqrqbf」とします 。以下は「time_limit => 1800」とあえて指定していますが、省略した場合のデフォルトがこの30分です。

declare
vCHECK_SQL_NAME varchar2(200);
vSQLTEXT clob;
begin 
vCHECK_SQL_NAME := DBMS_SQLTUNE.CREATE_TUNING_TASK(
task_name => 'tun_0tw5hbkvqrqbf'
,sql_id => '0tw5hbkvqrqbf'
,time_limit => 1800
);
DBMS_OUTPUT.PUT_LINE('vCHECK_SQL_NAME:'||vCHECK_SQL_NAME);
end;
/
SQL> set lin 100
SQL> SET SERVEROUTPUT ON
SQL> declare
  2  vCHECK_SQL_NAME varchar2(200);
  3  vSQLTEXT clob;
  4  begin
  5  vCHECK_SQL_NAME := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  6  task_name => 'tun_0tw5hbkvqrqbf'
  7  ,sql_id => '0tw5hbkvqrqbf'
  8  ,time_limit => 1800
  9  );
 10  DBMS_OUTPUT.PUT_LINE('vCHECK_SQL_NAME:'||vCHECK_SQL_NAME);
 11  end;
 12  /
vCHECK_SQL_NAME:tun_0tw5hbkvqrqbf

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

SQL>

必要に応じて

このあと、さらに以下のようなチューニングタスク実行時パラメータを設定する事も可能です。（省略も可能）
https://docs.oracle.com/cd/E16338_01/appdev.112/b56262/d_sqltun.htm#CHDGAJDB

--  
begin 
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
 task_name => 'tun_0tw5hbkvqrqbf_6'
,parameter  => 'USERNAME'
,value      => 'SOONI'  --文を解析するユーザー 
);
end;
/
begin 
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
 task_name => 'tun_0tw5hbkvqrqbf'
,parameter  => 'plan_filter'
,value      => 'LAST_GENERATED'   -- タイムスタンプが最も新しい計画。
);
end;
/

DBA_ADVISOR_LOG を参照する事で確認できます。

COLUMN OWNER FORMAT A10
COLUMN TASK_NAME FORMAT A20
COLUMN STATUS FORMAT A10
SELECT t.OWNER,t.TASK_ID,t.TASK_NAME,t.EXECUTION_START,t.EXECUTION_START,t.STATUS
FROM   DBA_ADVISOR_LOG t where t.TASK_NAME = 'tun_0tw5hbkvqrqbf'
;
SQL> COLUMN OWNER FORMAT A10
SQL> COLUMN TASK_NAME FORMAT A20
SQL> COLUMN STATUS FORMAT A10
SQL> SELECT t.OWNER,t.TASK_ID,t.TASK_NAME,t.EXECUTION_START,t.EXECUTION_START,t.STATUS
  2  FROM   DBA_ADVISOR_LOG t where t.TASK_NAME = 'tun_0tw5hbkvqrqbf'
  3  ;

OWNER         TASK_ID TASK_NAME            EXECUTION_START     EXECUTION_START     STATUS
---------- ---------- -------------------- ------------------- ------------------- ----------
SOONI            2883 tun_0tw5hbkvqrqbf                                            INITIAL

SQL>

チューニングアドバイザ・タスクを実行

チューニング対象となるSQLによってはDBにかなり負荷をかけますし、実行時間もかかってしまいますのでこのあたりを考慮の上実行してください。

exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK('tun_0tw5hbkvqrqbf')
SQL> exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK('tun_0tw5hbkvqrqbf')
PL/SQLプロシージャが正常に完了しました。
SQL>
-- 再度DBA_ADVISOR_LOGを参照するとSTATUSが変化しています
-- 
SQL> SELECT t.OWNER,t.TASK_ID,t.TASK_NAME,t.EXECUTION_START,t.EXECUTION_START,t.STATUS
  2  FROM   DBA_ADVISOR_LOG t where t.TASK_NAME = 'tun_0tw5hbkvqrqbf'
  3  ;

OWNER         TASK_ID TASK_NAME            EXECUTION_START     EXECUTION_START     STATUS
---------- ---------- -------------------- ------------------- ------------------- ----------
SOONI            2883 tun_0tw5hbkvqrqbf    2022-02-07 00:15:45 2022-02-07 00:15:45 COMPLETED

SQL>

SQLチューニング・タスクのレポートの表示 

SET LINESIZE      1024
SET PAGES         1000
SET LONG          5000000
SET LONGCHUNKSIZE 5000000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'tun_0tw5hbkvqrqbf') FROM dual
;

SQL> SET LINESIZE      1024
SQL> SET LONG          5000000
SQL> SET LONGCHUNKSIZE 5000000
SQL> /

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUN_0TW5HBKVQRQBF')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : tun_0tw5hbkvqrqbf
Tuning Task Owner  : SOONI
Workload Type      : Single SQL Statement
Execution Count    : 2
Current Execution  : EXEC_3586
Execution Type     : TUNE SQL
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUN_0TW5HBKVQRQBF')
--------------------------------------------------------------------------------
Started at         : 02/07/2022 00:15:45
Completed at       : 02/07/2022 00:15:45

-------------------------------------------------------------------------------
Schema Name: SOONI
SQL ID     : 0tw5hbkvqrqbf
SQL Text   : select * from ex005 where c5 = :1
Bind Variables :
 1 -  (VARCHAR2(32)):焼肉定食

-------------------------------------------------------------------------------

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUN_0TW5HBKVQRQBF')
--------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Index Finding (see explain plans section below)
--------------------------------------------------
  索引を1つ以上作成すると、この文の実行計画を改善できます。

  Recommendation (estimated benefit: 98.74%)
  ------------------------------------------
  - 物理スキーマ設計を改善するAccess Advisorの実行か、推奨される索引の作成を検討してください。
    create index SOONI.IDX$$_0B430001 on SOONI.EX005("C5");

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUN_0TW5HBKVQRQBF')
--------------------------------------------------------------------------------
  Rationale
  ---------
    推奨される索引を作成すると、この文の実行計画が大きく改善されます。ただし、単一の文ではなく代理SQLワークロードを使用した"Access
    Advisor"の実行が適切な場合もあります。この処理により、索引メンテナンス・オーバーヘッドおよび追加領域消費が考慮された包括的な索引推奨事項を取得
    できます。

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------


DBMS_SQLTUNE.REPORT_TUNING_TASK('TUN_0TW5HBKVQRQBF')
---------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 2069187346

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |  1049 |   479   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EX005 |     1 |  1049 |   479   (1)| 00:00:01 |
---------------------------------------------------------------------------


DBMS_SQLTUNE.REPORT_TUNING_TASK('TUN_0TW5HBKVQRQBF')
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C5"=:1)

2- Using New Indices
--------------------
Plan hash value: 2731668361

------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

DBMS_SQLTUNE.REPORT_TUNING_TASK('TUN_0TW5HBKVQRQBF')
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |  1049 |     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EX005          |     1 |  1049 |     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX$$_0B430001 |     1 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C5"=:1)


DBMS_SQLTUNE.REPORT_TUNING_TASK('TUN_0TW5HBKVQRQBF')
-------------------------------------------------------------------------------------------------------


SQL>

SQLチューニングタスクの削除

EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('tun_0tw5hbkvqrqbf')
;
SQL> EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('tun_0tw5hbkvqrqbf')

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

SQL> SELECT t.OWNER,t.TASK_ID,t.TASK_NAME,t.EXECUTION_START,t.EXECUTION_START,t.STATUS
  2  FROM   DBA_ADVISOR_LOG t where t.TASK_NAME = 'tun_0tw5hbkvqrqbf'
  3  ;

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

SQL> /
スポンサーリンク
sooni
タイトルとURLをコピーしました