LoginSignup
7
8

More than 3 years have passed since last update.

@sugimount(sugi mount)

[Oracle Cloud] SQLチューニング・アドバイザで、簡単にパフォーマンスチューニングしてみた

Posted at

はじめに

RDBMS を運用していくうえで、パフォーマンスの問題に直面することがあります。パフォーマンス改善するために、使っている RDBMS に精通したデータベースエンジニアが、多くの時間を使って解析・チューニング・確認作業を繰り返していきます。深い知識を持っているエンジニアが多くの時間を費やすことが必要でした。

Oracle Database には、SQLチューニング・アドバイザと呼ばれる、チューニングをアドバイスしてくれる機能があります。これを使うことで、推奨のチューニング事項をお知らせしてくれて、それをそのまま適用が出来ます。データベースエンジニアが不要になるわけではありませんが、大幅な時間短縮が期待できます。

今回の記事では、Oracle Cloud の Database Cloud Service(DBCS) と Enterprise Manager をつかって、SQLチューニング・アドバイザでパフォーマンスチューニングをしてみます。

前提条件

  • DBCS Enterprise Edition 以上 が構成されていること (SQLチューニング・アドバイザには、EE 以上が必要です)
  • Enterprise Manager で DBCS をモニタリングしていること

こちらの手順でモニタリングの設定手順を紹介しています。これを参考にして構成を組めます。
https://qiita.com/sugimount/items/1ca6aedfaa08e43ef9b4

テストデータ作成

SQL チューニング・アドバイザを実行するために、DBCS にテストデータを 5000万行格納します。
Create Table

CREATE TABLE EXAMPLES (
  ID INTEGER
 ,LABEL     VARCHAR2(100)  NOT NULL
 ,SCORE     NUMBER         NOT NULL
 ,RATE      NUMBER         NOT NULL
 ,BIRTHDAY  DATE           NOT NULL
 ,PRIMARY KEY (ID)
);

5000万のテストデータ生成 (1時間50分ほど実行に時間かかった)

INSERT INTO EXAMPLES
    SELECT 
      ROWNUM
     ,DBMS_RANDOM.STRING('A', 100)
     ,FLOOR(DBMS_RANDOM.VALUE(1, 100000))
     ,DBMS_RANDOM.VALUE()
     ,TO_DATE('19000101','YYYYMMDD') + FLOOR(DBMS_RANDOM.VALUE(1, 365 * 120))
    FROM
      (select 0 from all_catalog where rownum <= 10000)
     ,(select 0 from all_catalog where rownum <= 5000)
;
COMMIT;

テストデータ生成後、行数を確認します。

SELECT COUNT(SCORE) FROM EXAMPLES;

実行結果。正常に5000行が生成されています。

SQL> SELECT COUNT(SCORE) FROM EXAMPLES;

COUNT(SCORE)
------------
    50000000

SQL>

10件だけ取得して、実際のデータを見てみます。

SELECT * FROM EXAMPLES WHERE ROWNUM <= 10;

memo : delete

DELETE FROM EXAMPLES;
COMMIT;

SQL Monitoring

SQLチューニング・アドバイザを確認する前に、SQL Monitoring 機能を紹介します。
Oracle Database で実行されている SQL クエリーの詳細を GUI で簡単に把握できる機能です。

Oracle Database のキャッシュクリアするために、CDB に sysdba で接続します。

[oracle@oradb19ee ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 9 03:19:39 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL>

PDB01 に切り替え

SQL> alter session set container=PDB01;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
PDB01
SQL>

Buffer Cache のクリア

ALTER SYSTEM FLUSH BUFFER_CACHE;

SQL*Plus をいったん抜けます

exit

sugi で login

sqlplus sugi/Sug1_Passw0rd_dayo@pdb01

TABLE ACCESS FULL (テーブルフルスキャン) が、発生するような SELECT SQL クエリーを実行します。

SELECT COUNT(*) FROM EXAMPLES WHERE 10000 <= SCORE AND SCORE <= 20000 ORDER BY SCORE;

前提条件で設定されている Enterprise Manager を使って、Performance Hub の ASH(Active Session History) 分析 ページに移動します

1596944351817.png

グラフが伸びている箇所をクリックします。なお、時刻はUTCです。

1596944420002.png

SQL Monitoring を選択して、Sort 順をDuration(実行時間) 順 にして、一番実行時間が長い SQL を把握します。
そして、対象の SQL ID をクリックして詳細画面に移動します。

1596944544750.png

SQL クエリーに関する詳細な情報を確認可能です。いくつかピックアップして、代表的なもの紹介していきます。

  • SQL Status : SQLクエリーの状態。実行完了、実行中、など
  • SQL Text : SQLクエリーの文字列。クリックすることで、全文を確認可能
  • Username : 実行したユーザー名
  • Duration : 実行時間
  • Buffer Gets : Buffer Cache から取得した容量
  • I/O Requests : I/Oリクエストの回数
  • I/O Bytes : I/Oした容量
  • Plan Statistics : 実行計画

1596945636707.png

SQL Text : 全文

1596947307794.png

Activity

1596947362582.png

Metrics
SQL クエリーで消費したリソースを確認可能

  • CPU 使用率
  • メモリ (PGA) の使用容量
  • Read, Write のスループット
  • I/O Request の回数

1596947403474.png

バッファーキャッシュの Scan した量

1596947530516.png

Save Report を押すことで、今見ている SQL Monitoring 画面をHTMLファイルとして保存可能

1596947888960.png

SQL Tuning Advisor

SQL Monitoring で クエリーの詳細情報を確認できました。では次に、SQLチューニング・アドバイザーを使っていきましょう。ASH(Active Session History) Analytics タブで、チューニングアドバイスが欲しい SQL ID を選択します。

1596948418059.png

SQL ID を選ぶときに、青い SQL ID のリンクにマウスを重ねると、ポップアップで SQL 文の全文が確認できます。この文字列や実行時間も見ながら、該当の SQL を選んでいくとよいでしょう。

1596948762706.png

詳細画面でも、SQL Monitoring で見ていたものと似たような情報が確認できます。SQLテキストの全文や、実行元のモジュール(プログラム)、該当スキーマ、実行時間などが確認可能です。

1596949160486.png

プランのタブでも、実行計画が見えます。

1596949270988.png

それでは、実際にアドバイスを貰ってみましょう。SQLチューニング・アドバイザを選択して実行します。

1596949329841.png

デフォルトのパラメータのまま、実行を押します。名前は控えておいたほうが楽です。

SQL_TUNING_1596949427934

1596949478338.png

SQLチューニング・アドバイザを実行しています。このまま待機しても良いですし、時間が無い場合は閉じても大丈夫です。

1596949493103.png

閉じた場合は、アドバイザ・ホームから確認可能です

1596949582521.png

先ほど確認した SQL チューニング・アドバイザの名前をクリックすると、確認できます

1596949639863.png

SQL チューニング・アドバイザによって、2つのチューニング方法がアドバイスされています。「SQL プロファイル」と「索引(Index)」に関するアドバイスです。

1596950737242.png

順番に見ていきましょう。まず、ベネフィットという列です。この列の意味は、チューニング実行前と実行後の性能の違いが載っています。「SQLクエリーに掛かる時間を、XXパーセント分の削減が期待できる」という意味です。次の画像でアドバイス通りにチューニングを実行すると、72.24% を削減が出来て、27.76% の速度でSQLクエリーが完了する、という意味です。このままの速度が本当に出来るのかは保証されていないため、性能検証は必要です。ただ、SQL チューニング・アドバイザが有ることで、簡単に自動的にアドバイスを受けられるため、チューニングの作業を大幅に短縮できます。

1596950671759.png

「推奨」列を見てみると、「この文のパラレル実行を使用するために推奨されるSQLプロファイルの承認を検討してください。」と記載されています。SQL プロファイルを使った、パラレルクエリーに関するアドバイスのようです。

SQLプロファイルとは、SQL クエリーをどのように処理するかカスタマイズするための機能です。具体的には、Oracle Database の実行計画を調整できます。
パラレルクエリーとは、1個のSQLクエリーを複数の CPU で行うための機能です。パラレルクエリーが無いと、基本的には1個のSQLクエリーは1個のCPUで処理されます。複数のCPUを使うことで、性能アップを図る、というアドバイスですね。

詳細を見ていきましょう。画面右側にある眼鏡マークを押します。

1596951231765.png

SQL チューニング・アドバイザで推奨されている内容が書かれています。パラレルクエリーの並列度は4とアドバイスされています。これは、自分が動かしている DBCS は、4vCPU で動作しているため、「4vCPU でパラレルクエリーするといいよ」というアドバイスです。4vCPU でパラレルクエリーすることで、72.24% の速度改善が出来るものの、その代わりにリソースオーバーヘッドの増加が、11.03% あるようです。

1596951338288.png

元の画面に戻って、実行計画の比較を見てみます

1596953094094.png

「元の実行計画」と、チューニング実行後の「SQLプロファイルのある新しい実行計画」が表示されています。

「元の実行計画」では、1個の vCPU を使って、テーブル全体にアクセスをしている実行計画です。
「SQLプロファイルのある新しい実行計画」では、「PX COORDINATOR」や「PX SEND QC (RANDOM)」が追加されており、パラレルクエリーで実行する内容となっています。

TABLE ACCESS FULL が両方ともあるものの、「SQLプロファイルのある新しい実行計画」はパラレルクエリーで速度向上が期待できる内容となっています。

1596953160116.png

2個目のSQLチューニング・アドバイザの内容を見ていきます。タイプが「索引」とあるので、Index に関するアドバイスです。SUGI.EXAMPLES("SCORE") となっており、SCORE 列に関する Index を作ると良さそう、ということが分かります。

また、ベネフィットが 95.83% となっており、相当の速さが向上することが分かります。パラレルクエリーよりも性能改善が期待できるようです。右側の眼鏡をクリックして、詳細を見ていきましょう。

1596954012591.png

「元の実行計画」と、チューニング実行後の「SQLプロファイルのある新しい実行計画」が表示されています。「SQLプロファイルのある新しい実行計画」を見ると、「INDEX RANGE SCAN」が記載されており、Index を新たに作ればいいよ、というアドバイスです。

1596954306534.png

それでは、SQLチューニング・アドバイザによって推奨されたチューニングを実施していきます。Index 作成のアドバイスを選択して実装を押します。

1596955080250.png

自動的に作成される Index の情報が確認できます。また、SQL表示を押して、Index 作成のコマンドを確認できます。

  • Index Name : SUGI.IDX$$_00170001
  • Index Type : BTREE
  • Table Name : SUGI.EXAMPLES
  • Column : SCORE

1596955300487.png

SQL 文をそのまま確認できます。戻るを押します。

DECLARE
  cmd varchar2(400);
  sname varchar2(400);
BEGIN
  cmd := 'create  index SUGI.IDX$$_00170001 on SUGI.EXAMPLES("SCORE")';
  EXECUTE IMMEDIATE cmd;
  sname := dbms_sqltune.accept_sql_profile(task_name => 'SQL_TUNING_1596949427934', object_id => 1);
END;

1596955436906.png

内容を確認したので、Index を作成していきましょう。OK を押します。

1596955494500.png

Index を作成する Job が発行されました

1596955526154.png

Job を確認します。

1596955711664.png

実行中や履歴を確認すると、実行した Job があるはずです。自分の場合は、履歴ページにありました。が、何故か Failed となっていました

1596955920990.png

詳細をみます

1596955951015.png

Oracle Job としては失敗しています。が、実際に Index を確認してみると、作成されていました

1596956032599.png

Index Create を実施している場合は、SQL クエリーが実行されている旨が確認できます。

1596957688140.png

Index を確認するために、Table の詳細を表示します

1596957791645.png

SUGI を入れて検索して、Index を作成した Table EXAMPLES を選択します

1596957843353.png

SCORE に対する Index が作成されています

1596957915670.png

チューニングされているか確認

同じ SQL クエリーを実行してみて、性能が上がっていることを確認しましょう。まず、バッファーキャッシュが残っていると微妙なので、キャッシュをクリアします。

CDB に sysdba で接続

[oracle@oradb19ee ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 9 03:19:39 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL>

PDB01 に切り替え

SQL> alter session set container=PDB01;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
PDB01
SQL>

Buffer Cache のクリア

ALTER SYSTEM FLUSH BUFFER_CACHE;

exit

sugi で login

sqlplus sugi/Sug1_Passw0rd_dayo@pdb01

同じクエリーを実行します

SELECT COUNT(*) FROM EXAMPLES WHERE 10000 <= SCORE AND SCORE <= 20000 ORDER BY SCORE;

クエリーの詳細を、SQL Monitoring で確認してみましょう。相当の速度改善が確認出来ました

  • 実行計画で、Index が使われている
  • 実行時間 7 秒

1596958145570.png

参考URL

英語 Document
https://docs.oracle.com/en/enterprise-manager/index.html
https://docs.oracle.com/en/enterprise-manager/cloud-control/enterprise-manager-cloud-control/13.4/index.html

7
8
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
7
8