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

# はじめに

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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/09922984-23bc-ebdc-e76a-1f2ddfed1901.png)



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

![1596944420002.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/eb917887-6284-227f-3532-789c14fd1f8c.png)



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

![1596944544750.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/d854019b-d477-a8b1-46eb-9ab332e6e213.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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/9d4f28a9-0f40-2bcb-1193-d2762067a5ac.png)



SQL Text : 全文

![1596947307794.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/376b59c0-e0b8-ed66-689d-1a9e65cc3cc3.png)



Activity

![1596947362582.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/0379d09d-5166-6f43-25f2-1731387844aa.png)



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

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

![1596947403474.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/c6e8b83a-a86a-f54b-c3b9-c914cfd182f1.png)



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

![1596947530516.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/389bb599-8172-782f-f2a0-410fa14aebc0.png)



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

![1596947888960.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/88ce79ff-784f-6031-a076-bbc1a8dc69a4.png)



# SQL Tuning Advisor

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

![1596948418059.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/ef7ead2a-2cb2-e242-1635-edb2cdf37047.png)



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

![1596948762706.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/c0c93662-3a50-a094-bb01-3d4432d8de07.png)



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

![1596949160486.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/027fd5ed-fc14-7e12-b641-af712683c72e.png)



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

![1596949270988.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/227a189e-b93c-076e-cdc9-7d636c10cf19.png)



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

![1596949329841.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/5cc6a187-add4-3246-fe93-533f3d7e21cb.png)



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

`SQL_TUNING_1596949427934`

![1596949478338.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/fcb56775-4de5-3da9-3ccc-6be2e051be2e.png)



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

![1596949493103.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/1b8b623b-136c-1ff8-87c6-a14b35a3c7e3.png)



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

![1596949582521.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/50cfeadb-55f2-1c9b-6a5e-02b7f190a3b6.png)



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

![1596949639863.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/6dd238a5-44b8-13e2-dc20-70f7d787cc4b.png)



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

![1596950737242.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/0c70c594-9acd-33bf-5e18-77205545d11f.png)



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

![1596950671759.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/5106a1ec-f7d9-ead6-b62b-bb8dc3d81ef3.png)



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

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

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

![1596951231765.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/ab1ed76c-32b2-b446-490c-1c4215c2944a.png)



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

![1596951338288.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/d057a456-8a66-dccc-604a-d3a0e991ea51.png)



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

![1596953094094.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/99c21667-badf-857f-c214-6912caf943cf.png)



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

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

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

![1596953160116.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/0524500e-635f-d1f4-5484-7d76511dd10e.png)



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

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

![1596954012591.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/002a17ef-ee1b-5357-7f22-4a5de14ed21b.png)



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

![1596954306534.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/deee8140-9de9-75b7-8591-ef4b7d083956.png)



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

![1596955080250.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/7d0881f1-6b39-cf0f-9a0e-7dc5cd53b509.png)



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

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

![1596955300487.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/c7ca8958-559d-a481-06f1-d6031d668e58.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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/f04cf0cd-e92e-fffd-e83a-17dc1a45fb92.png)



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

![1596955494500.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/30328483-18a5-a1f0-4ce6-95ab1f0cf7e0.png)



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

![1596955526154.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/1bddd83d-c01a-6cc1-a8bc-42978e57d5ca.png)



Job を確認します。

![1596955711664.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/d7782c7c-abf0-7252-4b3e-11fa05b4e7ae.png)



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

![1596955920990.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/4229890c-b5c9-8662-3543-ca5ff6eb58ce.png)



詳細をみます

![1596955951015.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/773c1e55-62b1-af0e-0b7b-6feefd7ba990.png)



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

![1596956032599.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/fd5b73b5-cfa7-6f81-d9c7-fda87d6e11ee.png)



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

![1596957688140.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/861a88bf-eefb-c21a-d2af-1cefdbd9de5b.png)



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

![1596957791645.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/cd2a3cac-9120-be10-d375-4604639c6b69.png)



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

![1596957843353.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/b06c5275-1e00-165e-a034-2adb3a849686.png)



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

![1596957915670.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/ff30f38b-2640-1cff-1187-46c11abe6382.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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/124d05e7-3438-f772-eaf4-ff7f19a88263.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