Oracle SQLのハードパース、ソフトパースの調査をしてみた
こんにちは。次世代システム研究室のM.Mです。
現在、関わっているサービスにてSQLチューニングをする機会があったので、今回はその際に実施した内容を共有したいと思います。
尚、利用しているRDBMSはOracle11g、アプリケーションはPHP7を利用しております。
目次
1. ハードパース、ソフトパースとは2. どのようなSQLに問題があったか
3. SQL*Plusにて問題のあったSQLの状況を確認する
3-1. SQL*Plusでバインド変数を使ったSQLってどうやって実行するのか？
3-2. バインド変数が使われたのか調査
3-3. ソフトパース、ハードパースの確認
4. SQL*Plusで知っておいた方がよいと思えたコマンドについて
1. ハードパース、ソフトパースとは
SQLの処理の流れは大きく分けると「SQLの解析」「SQLの実行」「行の取得(SELECTの場合)」の3つに分かれます。その「SQLの解析」は以下のように行われます。
1．共有プールに解析済みSQL情報が存在しているか確認する
2．存在していない場合、SQLの検証を行う
3．実行計画の作成を行う
4．作成された解析済みSQL情報を共有プールへ格納する
その後、「SQLの実行」に移ります。
上記1にて共有プールに解析済みSQL情報が存在していれば、上記2、3、4がスキップされ「SQLの実行」に移ります。
共有プールに解析済みSQL情報が存在しており、その解析済みSQL情報を利用して、SQLの解析処理をスキップすることをソフトパース、スキップせずに上記1から4すべて実行されることをハードパースと呼びます。
基本的に、共有プールの解析済みSQL情報が利用されソフトパースが行われるのは、解析済みSQLと完全一致したSQLの場合のみです。
そのため、WHERE句の条件の値が動的に変わるような場合、バインド変数を利用せずに、値をそのままSQL文に記載してしまうと、値が変わるごとに異なるSQLと判断されハードパースが発生します。
ハードパースが多発すると、CPU負荷の上昇、共有プール内の他のSQLの解析結果を追い出すことになったり、パフォーマンス低下につながります。
2. どのようなSQLに問題があったか
以下のようなバインド変数が使われているようで使われていないSQLが見つかりました。※実際のサービスで利用しているSQLをブログ用に変更しております。
SELECT
UPD_ID,
USER_ID,
TERM_POINT_FLG,
CASE WHEN TERM_POINT_FLG = 1 THEN
EXPIRED_DATE
ELSE
TO_DATE('$general_point_expired_date', 'YYYY/MM/DD')
END EXPIRED_DATE,
USABLE_POINT
FROM
USER_POINT_DETAIL
WHERE
USER_ID = :user_id
AND USABLE_POINT > 0
AND EXPIRED_FLG = :expired_flg
AND DELETE_FLG = :delete_flg;
WHERE句の:user_id, :expired_flg, :delete_flgがバインド変数になります。SELECT句の$general_point_expired_dateがPHPの変数になります。
セキュリティの観点からもバインド変数を利用してSQLを実行するというのは、RDBMSを利用したWEBのアプリケーションでは当たり前になっています。
ただ、WHERE句やINSERT, UPDATEにて登録・更新する値については、バインド変数を利用するように十分に意識はされていますが、上記SQLのようなSELECT句で動的に値を変更するケースは多くないせいか、SQL文の生成時にPHPの変数の値が直接埋め込まれるようになっていました。
大文字小文字の違い、スペースの違いなども含めて、過去に実行されたSQLと完全一致していないSQLは、異なるSQLとみなされて毎回ハードパースされるため避けるべきだと認識はしていましたが、そもそもバインド変数って、WHERE句の条件やINSERTのVALUE句、UPDATEのSET句などで、実際にテーブルのカラムに割り当てるものしか使えないのか？と不安になってきたので、実際にハードパースされるのか確認することにしました。
3. SQL*Plusにて問題のあったSQLの状況を確認する
Oracleの場合、Oracle Enterprise Managerを利用してSQLの状況などをGUIにて確認するかと思いますが、主にDBAが利用しており開発者の利用まで検討が進んでいないケースもあるかと思います。今回は自力で調査できるところまで実施するということで、SQL*Plusにて調査することにしました。
（実行計画などの確認できる権限を持ったユーザーで接続はできる前提です。）
3-1. SQL*Plusでバインド変数を使ったSQLってどうやって実行するのか？
そもそもSQL*Plusで接続するケースが少なく、何か調査などでSQLの実行結果をcsvファイルに出力させる用途であったり、実行するSQLもその場限りのものが多く、バインド変数を意識したSQLをSQL*Plusであえて実行はしないと思います。（もちろん、本番環境で不要なキャッシュが乗らないように意識する必要がある場合は別ですが）
以下は、上記SQLの$general_point_expired_dateを手入力で適当な日付（今回は2019/07/01)を設定し、WHERE句の部分はバインド変数を利用してSQLを実行した結果になります。
SQL> -------------------------------------
SQL> -- user_idのバインド設定
SQL> -------------------------------------
SQL> variable user_id number
SQL> execute :user_id := 12345678
PL/SQLプロシージャが正常に完了しました。
SQL> -------------------------------------
SQL> -- expired_flgのバインド設定
SQL> -------------------------------------
SQL> variable expired_flg number
SQL> execute :expired_flg := 0
PL/SQLプロシージャが正常に完了しました。
SQL> -------------------------------------
SQL> -- delete_flgのバインド設定
SQL> -------------------------------------
SQL> variable delete_flg number
SQL> execute :delete_flg := 0
PL/SQLプロシージャが正常に完了しました。
SQL> -------------------------------------
SQL> -- 対象SQLの実行
SQL> -- SQL_IDを調査しやすいように /* BIND_TEST_0626 */を追加
SQL> -- $general_point_expired_dateの部分は2019/07/01とする
SQL> -- user_id, expired_flg, delete_flgはバインド変数を利用
SQL> -------------------------------------
SQL> SELECT /* BIND_TEST_0626 */
2 UPD_ID,
3 USER_ID,
4 TERM_POINT_FLG,
5 CASE WHEN TERM_POINT_FLG = 1 THEN
6 EXPIRED_DATE
7 ELSE
8 TO_DATE('2019/07/01', 'YYYY/MM/DD')
9 END EXPIRED_DATE,
10 USABLE_POINT
11 FROM
12 USER_POINT_DETAIL
13 WHERE
14 USER_ID = :user_id
15 AND USABLE_POINT > 0
16 AND EXPIRED_FLG = :expired_flg
17 AND DELETE_FLG = :delete_flg;
UDP_ID USER_ID TERM_POINT_FLG EXPIRED_DATE USABLE_POINT
---------- ---------- -------------- -------------------- ------------
1300617 12345678 0 2019-07-01 00:00:00 2
...
(省略)
...
3001行が選択されました。
3-2. バインド変数が使われたのか調査
V$SQLから対象のSQLを検索してSQL_IDを取得して、そのSQL_IDを利用してV$SQL_BIND_CAPTUREからバインドされた情報を取得します。※V$SQLから対象のSQLを検索してSQL_IDを取得しなくても、Oracle 18cから SET FEEDBACK ON SQL_IDを実行してからSQLを実行すると実行結果の後にSQL_IDが表示されるようです。（SQL*Plusだけバージョン上げてもDB自体がOracle11gだと表示されませんでした。）
以下、バインド変数の利用を確認した内容となります。
SQL> -------------------------------------
SQL> -- V$SQLから対象のSQLを検索してSQL_IDを取得します
SQL> -- 検索用に設定したBIND_TEST_0626で検索します
SQL> -------------------------------------
SQL> SELECT SQL_ID, SQL_TEXT FROM V$SQL
2 WHERE SQL_TEXT LIKE ('%BIND_TEST_0626%');
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------
7qacccm7pkypk
SELECT /* BIND_TEST_0626 */ UPD_ID, USER_ID, TERM_
POINT_FLG, CASE WHEN TERM_POINT_FLG = 1 THEN EXPIR
ED_DATE ELSE TO_DATE('2019/07/01', 'YYYY/MM/DD')
END EXPIRED_DATE, USABLE_POINT FROM USER_POINT_DETA
IL WHERE USER_ID = :user_id AND USABLE_POINT > 0 A
ND EXPIRED_FLG = :expired_flg AND DELETE_FLG = :delete_flg
SQL> -------------------------------------
SQL> -- 取得したSQL_IDを利用して
SQL> -- V$SQL_BIND_CAPTUREからバインドされた情報を確認する
SQL> -------------------------------------
SQL> SELECT NAME, VALUE_STRING FROM V$SQL_BIND_CAPTURE
2 WHERE SQL_ID = '7qacccm7pkypk';
NAME
---------------------------------------------------------------
VALUE_STRING
---------------------------------------------------------------
:USER_ID
12345678
:EXPIRED_FLG
0
:DELETE_FLG
0
SQL*Plusにてバインド変数を使った実行がされていることの確認はできました。今回のハードパースの調査とは関係ありませんが、SQL_IDから実行計画を参照することも可能です。
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7qacccm7pkypk'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID 7qacccm7pkypk, child number 0
-------------------------------------
SELECT /* BIND_TEST_0626 */ UPD_ID, USER_ID, TERM_POINT_FL
G, CASE WHEN TERM_POINT_FLG = 1 THEN EXPIRED_DATE ELSE
TO_DATE('2019/07/01', 'YYYY/MM/DD') END EXPIRED_DATE,
USABLE_POINT FROM USER_POINT_DETAIL WHERE USER_ID = :user_id
AND USABLE_POINT > 0 AND EXPIRED_FLG = :expired_flg AND DEL
ETE_FLG = :delete_flg
Plan hash value: 2489885457
----------------------------------------------------------------------
--------------------------------------
| Id | Operation | Name | R
ows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------
--------------------------------------
| 0 | SELECT STATEMENT | |
| | 4 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| USER_POINT_DETAIL |
1 | 31 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | USER_POINT_DETAIL_IDX01 |
1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------
--------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("USABLE_POINT">0 AND "EXPIRED_FLG"=:EXPIRED_FLG AND
"DELETE_FLG"=:DELETE_FLG))
2 - access("USER_ID"=:USER_ID)
25行が選択されました。
3-3. ソフトパース、ハードパースの確認
現在接続しているSQL*Plusにて処理されたパースの件数の確認をします。
パースされた件数を確認する方法は以下の通りです。SQL> SELECT NAME, VALUE FROM V$SESSTAT VSS, V$STATNAME VSN
2 WHERE VSS.STATISTIC# = VSN.STATISTIC#
3 AND NAME IN ('parse count (hard)', 'parse count (total)')
4 AND SID = userenv('sid')
5 ORDER BY NAME;
NAME VALUE
------------------------------------------------------ ----------
parse count (hard) 10
parse count (total) 60
現在のSQL*Plusでの接続にて、ハードパースされた件数とパースされた合計件数が表示されます。parse count (hard)はハードパースされた件数。
parse count (total)はハードパースも含む、パースされた合計件数となります。
ハードパースされるか確認するため、初めて実行されるSQLとなるように先ほど指定した日付の翌日(2019/07/02)を指定してSQLを実行します。
SQL> -------------------------------------
SQL> -- 前回実施したSQLの日付部分のみ2019/07/02に変更して実行する
SQL> -------------------------------------
SQL> SELECT /* BIND_TEST_0626 */
2 UPD_ID,
3 USER_ID,
4 TERM_POINT_FLG,
5 CASE WHEN TERM_POINT_FLG = 1 THEN
6 EXPIRED_DATE
7 ELSE
8 TO_DATE('2019/07/02', 'YYYY/MM/DD')
9 END EXPIRED_DATE,
10 USABLE_POINT
11 FROM
12 USER_POINT_DETAIL
13 WHERE
14 USER_ID = :user_id
15 AND USABLE_POINT > 0
16 AND EXPIRED_FLG = :expired_flg
17 AND DELETE_FLG = :delete_flg;
UDP_ID USER_ID TERM_POINT_FLG EXPIRED_DATE USABLE_POINT
---------- ---------- -------------- -------------------- ------------
1300617 12345678 0 2019-07-02 00:00:00 2
...
(省略)
...
3001行が選択されました。
SQL> ----------------------------------------------
SQL> -- ハードパースされているかパースの件数を確認します
SQL> ----------------------------------------------
SQL> SELECT NAME, VALUE FROM V$SESSTAT VSS, V$STATNAME VSN
2 WHERE VSS.STATISTIC# = VSN.STATISTIC#
3 AND NAME IN ('parse count (hard)', 'parse count (total)')
4 AND SID = userenv('sid')
5 ORDER BY NAME;
NAME VALUE
------------------------------------------------------ ----------
parse count (hard) 11
parse count (total) 62
parse count (hard)の件数が1件増えており、ハードパースがされていることが分かります。parse count (total)の件数が2件増えているのは、ハードパースされたSQLとパース件数を取得するSQLの2件実行されているからです。
再度同じSQL(2019/07/02を指定したSQL)を実行します。
SQL> -------------------------------------
SQL> -- 前回実施したSQLとすべて同じ
SQL> -------------------------------------
SQL> SELECT /* BIND_TEST_0626 */
2 UPD_ID,
3 USER_ID,
4 TERM_POINT_FLG,
5 CASE WHEN TERM_POINT_FLG = 1 THEN
6 EXPIRED_DATE
7 ELSE
8 TO_DATE('2019/07/02', 'YYYY/MM/DD')
9 END EXPIRED_DATE,
10 USABLE_POINT
11 FROM
12 USER_POINT_DETAIL
13 WHERE
14 USER_ID = :user_id
15 AND USABLE_POINT > 0
16 AND EXPIRED_FLG = :expired_flg
17 AND DELETE_FLG = :delete_flg;
UDP_ID USER_ID TERM_POINT_FLG EXPIRED_DATE USABLE_POINT
---------- ---------- -------------- -------------------- ------------
1300617 12345678 0 2019-07-02 00:00:00 2
...
(省略)
...
3001行が選択されました。
SQL> SELECT NAME, VALUE FROM V$SESSTAT VSS, V$STATNAME VSN
2 WHERE VSS.STATISTIC# = VSN.STATISTIC#
3 AND NAME IN ('parse count (hard)', 'parse count (total)')
4 AND SID = userenv('sid')
5 ORDER BY NAME;
NAME VALUE
------------------------------------------------------ ----------
parse count (hard) 11
parse count (total) 64
ハードパースの件数が増えていないことが確認できます。同じSQL文の2回目はハードパースではなく、ソフトパースが行われたことが分かります。
再度、翌日の指定をして実行します。(2019/07/03として)
SQL> -------------------------------------
SQL> -- 前回実施したSQLの日付部分のみ2019/07/03に変更して実行する
SQL> -------------------------------------
SQL> SELECT /* BIND_TEST_0626 */
2 UPD_ID,
3 USER_ID,
4 TERM_POINT_FLG,
5 CASE WHEN TERM_POINT_FLG = 1 THEN
6 EXPIRED_DATE
7 ELSE
8 TO_DATE('2019/07/03', 'YYYY/MM/DD')
9 END EXPIRED_DATE,
10 USABLE_POINT
11 FROM
12 USER_POINT_DETAIL
13 WHERE
14 USER_ID = :user_id
15 AND USABLE_POINT > 0
16 AND EXPIRED_FLG = :expired_flg
17 AND DELETE_FLG = :delete_flg;
UDP_ID USER_ID TERM_POINT_FLG EXPIRED_DATE USABLE_POINT
---------- ---------- -------------- -------------------- ------------
1300617 12345678 0 2019-07-03 00:00:00 2
...
(省略)
...
3001行が選択されました。
SQL> SELECT NAME, VALUE FROM V$SESSTAT VSS, V$STATNAME VSN
2 WHERE VSS.STATISTIC# = VSN.STATISTIC#
3 AND NAME IN ('parse count (hard)', 'parse count (total)')
4 AND SID = userenv('sid')
5 ORDER BY NAME;
NAME VALUE
------------------------------------------------------ ----------
parse count (hard) 12
parse count (total) 66
やはりハードパースの件数が増えていることが分かります。結果、SELECT句でも日付が異なる値を指定した場合、異なるSQLとみなされハードパースが行われることが分かりました。
また共有カーソルの機能で、内部的にバインド処理が行われるようにする設定もありますが、完全一致しないとハードパースが行われる設定になっていました。
SQL> show parameter cursor_sharing NAME TYPE VALUE ------------------------------------ ----------- --------------- cursor_sharing string EXACT
日付の部分をバインド変数化して、日付を変えてもハードパースされないか確認していきます。
SQL> -------------------------------------
SQL> -- general_point_expired_dateのバインド設定
SQL> -------------------------------------
SQL> variable general_point_expired_date VARCHAR2(32)
SQL> execute :general_point_expired_date := '2019/08/01'
PL/SQLプロシージャが正常に完了しました。
SQL> print general_point_expired_date
GENERAL_POINT_EXPIRED_DATE
--------------------------------
2019/08/01
SQL> -------------------------------------
SQL> -- SQL実行前のパース件数を確認します。
SQL> -------------------------------------
SQL> SELECT NAME, VALUE FROM V$SESSTAT VSS, V$STATNAME VSN
2 WHERE VSS.STATISTIC# = VSN.STATISTIC#
3 AND NAME IN ('parse count (hard)', 'parse count (total)')
4 AND SID = userenv('sid')
5 ORDER BY NAME;
NAME VALUE
------------------------------------------------------ ----------
parse count (hard) 13
parse count (total) 71
SQL> -------------------------------------
SQL> -- 日付部分もバインド変数化したSQLを実行します。
SQL> -------------------------------------
SQL> SELECT /* BIND_TEST_0626 */
2 UPD_ID,
3 USER_ID,
4 TERM_POINT_FLG,
5 CASE WHEN TERM_POINT_FLG = 1 THEN
6 EXPIRED_DATE
7 ELSE
8 TO_DATE(:general_point_expired_date, 'YYYY/MM/DD')
9 END EXPIRED_DATE,
10 USABLE_POINT
11 FROM
12 USER_POINT_DETAIL
13 WHERE
14 USER_ID = :user_id
15 AND USABLE_POINT > 0
16 AND EXPIRED_FLG = :expired_flg
17 AND DELETE_FLG = :delete_flg;
UDP_ID USER_ID TERM_POINT_FLG EXPIRED_DATE USABLE_POINT
---------- ---------- -------------- -------------------- ------------
1300617 12345678 0 2019-08-01 00:00:00 2
...
(省略)
...
3001行が選択されました。
SQL> -------------------------------------
SQL> -- SQL実行後のパース件数を確認します。
SQL> -------------------------------------
SQL> SELECT NAME, VALUE FROM V$SESSTAT VSS, V$STATNAME VSN
2 WHERE VSS.STATISTIC# = VSN.STATISTIC#
3 AND NAME IN ('parse count (hard)', 'parse count (total)')
4 AND SID = userenv('sid')
5 ORDER BY NAME;
NAME VALUE
------------------------------------------------------ ----------
parse count (hard) 14
parse count (total) 73
ハードパースの件数が増えています。これはバインド変数化したSQLの初回実行だからです。バインド変数の値を変更して同じSQLを実行してパース状況の確認を行います。
SQL> execute :general_point_expired_date := '2019/08/02'
PL/SQLプロシージャが正常に完了しました。
SQL> print general_point_expired_date
GENERAL_POINT_EXPIRED_DATE
--------------------------------
2019/08/02
SQL> --------------------------------
SQL> -- SQL実行前のパース件数を確認します。
SQL> --------------------------------
SQL> SELECT NAME, VALUE FROM V$SESSTAT VSS, V$STATNAME VSN
2 WHERE VSS.STATISTIC# = VSN.STATISTIC#
3 AND NAME IN ('parse count (hard)', 'parse count (total)')
4 AND SID = userenv('sid')
5 ORDER BY NAME;
NAME VALUE
------------------------------------------------------ ----------
parse count (hard) 15
parse count (total) 76
SQL> -------------------------------
SQL> -- 日付部分もバインド変数化した前回と同じSQLを実行します。
SQL> -------------------------------
SQL> SELECT /* BIND_TEST_0626 */
2 UPD_ID,
3 USER_ID,
4 TERM_POINT_FLG,
5 CASE WHEN TERM_POINT_FLG = 1 THEN
6 EXPIRED_DATE
7 ELSE
8 TO_DATE(:general_point_expired_date, 'YYYY/MM/DD')
9 END EXPIRED_DATE,
10 USABLE_POINT
11 FROM
12 USER_POINT_DETAIL
13 WHERE
14 USER_ID = :user_id
15 AND USABLE_POINT > 0
16 AND EXPIRED_FLG = :expired_flg
17 AND DELETE_FLG = :delete_flg;
UDP_ID USER_ID TERM_POINT_FLG EXPIRED_DATE USABLE_POINT
---------- ---------- -------------- -------------------- ------------
1300617 12345678 0 2019-08-02 00:00:00 2
...
(省略)
...
3001行が選択されました。
SQL> -------------------------------
SQL> -- SQL実行前のパース件数を確認します。
SQL> -------------------------------
SQL> SELECT NAME, VALUE FROM V$SESSTAT VSS, V$STATNAME VSN
2 WHERE VSS.STATISTIC# = VSN.STATISTIC#
3 AND NAME IN ('parse count (hard)', 'parse count (total)')
4 AND SID = userenv('sid')
5 ORDER BY NAME;
NAME VALUE
------------------------------------------------------ ----------
parse count (hard) 15
parse count (total) 78
ハードパースの件数が増えていないことが確認できます。またSQLの取得結果の日付も2019-08-02 00:00:00になっており、バインド変数にて変更された日付になっていることも確認できます。
WHERE句やINSERTのVALUE句、UPDATEのSET句のようにカラムと対応した箇所以外にもバインド変数が利用できることの確認はできました。
4. SQL*Plusで知っておいた方がよいと思えたコマンド
SQL*Plusでは、先ほどの調査方法のように、日付を変えてSQLを実行、パース件数調査SQLの実行といった一連の操作を１つのターミナルで交互に実行するのが非常に面倒です。なぜならSQL*Plusは↑↓キーなどでSQLの履歴が表示されず、SQLバッファに前回実行されたSQL文が残っているのみ、2つの異なるSQLを交互に実行するとなった場合、↑を2回押して前々回のSQLを再利用といったことができません。
（↑↓で履歴を呼び出したり、タブで補完したりという方法もあるようですが、rlwrapなどインストールが伴うようなのでSQL*Plusで頑張ってみる。）
そのため、2つのターミナルを開き、調査するSQLを実行する用のターミナルとパース件数を確認する用のターミナルに分けて調査しました。
パース件数を確認するSQLに指定するSIDは、調査するSQLを実行する用のターミナルで、以下のSQLを実行してSIDを調べ、上記で実行していたパース件数を取得するSQLのWHERE句のSIDに直接指定すればOKです。
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
2747
そうすることで、前々回のSQLを利用する必要はなくなりました。ただ、調査するSQLは複数行に分かれており、一部の行だけ変更することができなければ、長いSQL文を毎回入力して実行する必要があり、まだまだ非効率でした。
そこで、以下のコマンドを使って、一部だけ修正しながら実行しました。
SQL> -- まずSQLバッファの内容確認: LISTコマンド(省略してlだけでOK)
SQL> l
1 SELECT /* BIND_TEST_0626 */
2 UPD_ID,
3 USER_ID,
4 TERM_POINT_FLG,
5 CASE WHEN TERM_POINT_FLG = 1 THEN
6 EXPIRED_DATE
7 ELSE
8 TO_DATE('2019/07/03', 'YYYY/MM/DD')
9 END EXPIRED_DATE,
10 USABLE_POINT
11 FROM
12 USER_POINT_DETAIL
13 WHERE
14 USER_ID = :user_id
15 AND USABLE_POINT > 0
16 AND EXPIRED_FLG = :expired_flg
17* AND DELETE_FLG = :delete_flg
SQL> -- 8行目の日付を変えたい: CHANGEコマンド(省略してcだけでOK)
SQL> -- 変えたい行番号を入力
SQL> 8
8* TO_DATE('2019/07/03', 'YYYY/MM/DD')
SQL> -- 置換（書き換えたい値に/があるので!で区切っています)
SQL> c!2019/07/03!2019/07/04!
8* TO_DATE('2019/07/04', 'YYYY/MM/DD')
SQL> -- 変更後のバッファ内容確認
SQL> l
1 SELECT /* BIND_TEST_0626 */
2 UPD_ID,
3 USER_ID,
4 TERM_POINT_FLG,
5 CASE WHEN TERM_POINT_FLG = 1 THEN
6 EXPIRED_DATE
7 ELSE
8 TO_DATE('2019/07/04', 'YYYY/MM/DD')
9 END EXPIRED_DATE,
10 USABLE_POINT
11 FROM
12 USER_POINT_DETAIL
13 WHERE
14 USER_ID = :user_id
15 AND USABLE_POINT > 0
16 AND EXPIRED_FLG = :expired_flg
17* AND DELETE_FLG = :delete_flg
SQL> --2019/07/04に変更されています。
SQL> --さらに15行目の0より大きいという条件を100より大きいに変更します。
SQL> -- 変えたい行番号を入力
SQL> 15
15* AND USABLE_POINT > 0
SQL> -- 置換
SQL> c/0/100/
15* AND USABLE_POINT > 100
SQL> -- 変更後のバッファ内容確認
SQL> l
1 SELECT /* BIND_TEST_0626 */
2 UPD_ID,
3 USER_ID,
4 TERM_POINT_FLG,
5 CASE WHEN TERM_POINT_FLG = 1 THEN
6 EXPIRED_DATE
7 ELSE
8 TO_DATE('2019/07/04', 'YYYY/MM/DD')
9 END EXPIRED_DATE,
10 USABLE_POINT
11 FROM
12 USER_POINT_DETAIL
13 WHERE
14 USER_ID = :user_id
15 AND USABLE_POINT > 100
16 AND EXPIRED_FLG = :expired_flg
17* AND DELETE_FLG = :delete_flg
SQL> --100に変更されています。
SQL> -- スラッシュでバッファ内のSQLを実行
SQL> /
特定の行の特定の値だけ変更して実行することができました。他のコマンドはこちらを参照してください。
今回の調査では２つターミナルを使い、SQLバッファを操作するコマンドを利用して作業しましたが、やはり前回実行分のSQLしか再利用できないのは使い勝手が悪い。
ということで調べたところ、Oracle12cからSQL*Plusに履歴機能が追加されているとのことでした。
とは言え、利用しているDBはOracle11gだし、SQL*Plusだけバージョン上げても使えるのかな？と思いましたが試したところ使えました！
以下、履歴機能を使ってみた結果になります。
SQL> show history history is OFF SQL> set history on SQL> show history historyはONであり、設定先は "100"です。 SQL> -- 適当にSQLを実行 SQL> -- historyで履歴がみれます SQL> history 1 show history 2 select sysdate from dual; 3 select count(1) from hoge; 4 select * from hoge where hoge_id = 100; SQL> -- もう一度2番目のSQLを実行したい場合 SQL> history 2 run SYSDATE ------------------- 2019-06-26 12:03:54 SQL> --4行目の履歴を変更したい場合 SQL> history 4 edit ⇒ hoge_idの値を200に変更して保存します SQL> history 1 show history 2 select sysdate from dual; 3 select count(1) from hoge; 4 select * from hoge where hoge_id = 100; 5 select * from hoge where hoge_id = 200; ⇒ 変更したSQLが履歴に追加されます。その他のコマンドについてはこちらを参照してください。
所感
今回のようなバインド変数が利用されていないというケースは、事前のレビューで防げると思います。ただ実際には、レビューにかける工数を含めた形で開発スケジュールを見積もれていない、そもそもSQLの記載ルールを明確にしていないなど、事前に防げると分かっていても、防ぐ仕組みまで出来上がっていないことが多いと感じます。
（防ぐ仕組みを作るにも工数がかかるし、仕組みを維持する工数もかかりますしね。）
結果、リリース後にSQLのチューニングが必要になるケースが残ってしまうのだと感じています。
また、最新のOracle 19c(Exadataに限られるようですが)では、Automatic Indexingといった新機能があり、自動でインデックスを生成するようなことも行われるようです。
managing-indexes
introduction-to-sql-tuning
インデックスを作ればSELECTは早くなるのは分かるが、INSERTやUPDATEは遅くなると思うのだが、その辺りも考慮されるのか気になるところではあります。
このような機能により、SQLチューニングを行うという作業は今後どんどん減ってくる傾向にあると思いますが、だからといって、SQLの記載ルールやインデックス、バインド変数なんて意識しなくてよくなるのではなく、しっかり意識したうえでSQLを記載して、チューニングアドバイザやAutomatic Indexingなどはプラスアルファと考えておいたほうがよいと思います。
SQL*Plusについては、各種コマンドでいろんなことができるのは分かりましたが、はやりGUIなどで対応できないケースのみの利用で、がっつり使いこなせるレベルには到達しなさそうです。
履歴機能をSQLバッファに流し込むみたいな履歴機能とSQLバッファの機能が連携できればいいのにと思いました。
最後に、次世代システム研究室では、グループ全体のインテグレーションを支援してくれるアーキテクトを募集しています。アプリケーション開発の方、次世代システム研究室にご興味を持って頂ける方がいらっしゃいましたら、ぜひ 募集職種一覧 からご応募をお願いします。
皆さんのご応募をお待ちしています。
