Oracle Databaseバージョンアップ後の性能劣化で試したい暫定対処

この記事は、JPOUG Advent Calendar 2020 の17日目の記事です。

16日目はmoritaxp_oironさんの記事『DatabaseのAT（オートマ）、Autonomous Database を見る 』でした。Autonomous Data Warehouse(ADW)でできること、できないこと（やらなくても良いこと）をわかりやすく解説してくださっていて非常に勉強になりました。

ちなみに私の普通自動車免許はMTですが、最後にMT車に乗ったのは教習所です。

長期サポートリリースの19cが昨年リリースされたことで、19cをご利用されているお客様からのお問い合わせも徐々に増えてきました。新しいバージョンがリリースされると必ずいただくのが「移行後に特定のクエリが遅延するようになった」というお問い合わせです。

バージョンが上がることで様々な機能や仕様が追加・改善されるため、多くのケースではクエリのパフォーマンスは改善、もしくは据え置きです。しかし、中にはまれにオプティマイザの動作や機能・仕様の変更によりクエリの性能が落ちてしまうこともあります。

また、バージョンアップに伴い、移行先バージョンを使用した開発環境（テストデータ）では問題なかったクエリが、本番環境で遅延するといったお問い合わせもいただきます。

多くのケースでは実行計画の差異や適切な実行計画が選択されていないことに起因するため、SQLトレース（EVENT 10046）やCBOトレース（EVENT 10053）をご提供いただき、実行計画に差がないか？などの調査をサポートセンターで行いますが、お客様から「サポートの回答を待っている間に何か簡単に試せることはないか？」というご要望をいただくことがあります。

今回は、バージョンアップ後に発生した or 開発環境では発生しなかったクエリの性能劣化が起きたときにユーザ側で試せることをまとめます。

バージョンアップ後のクエリ性能劣化の切り分けや暫定対処策として、効果がある可能性が高い方法がOPTIMIZER_FEATURES_ENABLEの変更です。

このパラメータはコストベース・オプティマイザ（CBO）の動作を指定したバージョンに制限します。移行前のバージョンで期待したパフォーマンス実績がある場合には有効な方法です。

指定方法としては、パラメータ、ヒント句があります。パラメータはインスタンス全体にも指定可能ですが動作確認としてはまずはセッション単位で指定します。

　
　-- ALTER SESSION で11.2.0.4 に設定
　SQL> ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='11.2.0.4';
　
　-- ヒント句で11.2.0.4 に設定
　SQL> SELECT /*+ OPTIMIZER_FEATURES_ENABLE('11.2.0.4') */ empno,ename, …;
　

移行前のバージョンを指定することで改善した場合は、バージョンアップの過程で追加された機能や仕様、もしくは実行計画生成のアルゴリズムに起因して異なる実行計画が生成された可能性が高いと言えます。

動的統計はSQLのパース時に動作し、クエリ実行時点のデータを元に動的に統計情報を取得します。動的統計が動作するかは、初期化パラメータOPTIMIZER_DYNAMIC_SAMPLINGに指定されているレベルと、クエリ実行対象の表の統計情報取得状況に依存します。

レベルごとの動作の違いは、SQLチューニング・ガイド「動的統計レベルについて 」をご参照ください。

移行する前と後、開発環境と本番環境ではクエリの対象となる表に対し、統計情報の取得状況に差がある場合があります。このようなケースではクエリ実行時の動的統計の動作に違いが出てしまい、実行計画が異なることでパフォーマンスに影響が出ることもあります。

レベル0（動的統計を使用しない）、レベル11（CBOが必要と判断した場合に動作）の両方で試すことでクエリの性能改善がみられるかご確認ください。

　
　-- 0に設定する例
　SQL> ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 0;
　
　-- 11に設定する例
　SQL> ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 11;
　

バインドピークでは、バインド変数の入力値を考慮した実行計画を生成します。バインドピークをはじめとしたオプティマイザの動作改善機能はバージョンが上がるごとに追加されていますので、基本的にはこれらの機能を使用をすることで最適な実行計画が選択されます。

あくまで切り分けの観点で、もしバインド変数を利用しているSQLでの遅延が起きている場合には、該当機能をOFFにすることで性能改善がみられるかご確認ください。

　
　-- バインドピークをOFFに変更
　SQL> ALTER SESSION SET “_optim_peek_user_binds” = FALSE;
　

ヒストグラムは列に含まれるデータ分布の情報で、統計情報取得時に、デフォルトでは全列に対して取得されます。データに偏りがある場合にはこちらの情報を取得することでより適切な実行計画が生成されます。

ヒストグラムは文字データの場合、前半32byteで偏りを判断します。そのため、住所データなどの「○○○県△△△市××××町一丁目一番一号」と「○○○県△△△市××××町一丁目一番二号」は同じデータとして認識されます。

もし、このようなデータが条件に入るクエリを実行していて、本番環境と開発環境でテストデータに差がある場合には、ヒストグラムの違いで異なる実行計画となり、パフォーマンスに影響を与える可能性があります。ヒストグラム統計の取得や削除により性能改善が見られるかご確認ください。

　
　-- ヒストグラムを取得しない統計情報の取得
　SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS -
　> ('スキーマ名','表名', NO_INVALIDATE=>FALSE, -
　> METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
　
　-- 取得済みの統計情報からヒストグラムを削除
　SQL> EXEC DBMS_STATS.DELETE_COLUMN_STATS -
　> ('スキーマ名','表名','列名',NO_INVALIDATE=>FALSE, -
　> COL_STAT_TYPE => 'HISTOGRAM');
　

※ヒストグラムの取得は通常通りDBMS_STATS.GATHER_TABLE_STATSの実行です

拡張統計は列グループや関数に基づいた統計情報です。クエリ内に同じ表に対する and / or の条件が含まれる場合は、拡張統計を取得することでカーディナリティ（戻される行数）の予測を改善できます。

SQL実行計画を改善する拡張統計(列グループ統計)の取得方法
https://www.ashisuto.co.jp/db_blog/article/20160714_extended_stats.html

同一テーブルに対する and / or の条件を使用しており、本番環境と開発環境で利用しているデータが異なる状況でパフォーマンスに差が出ているケースでは、拡張統計の取得有無が実行計画の生成に影響を与えている可能性があります。拡張統計の取得で性能改善が見られるかご確認ください。

上述のブログでは、DBMS_STATS.CREATE_EXTENDED_STATSを使用して明示的に列グループを作成していますが、ワークロードに基づき、作成する方法もあります。

　
　-- ワークロード監視を有効化（10分）
　SQL> CONN / AS SYSDBA
　SQL> EXEC DBMS_STATS.SEED_COL_USAGE (null,null,600);
　　
　-- 指定した期間内に対象のSQLを実行（EXPLAIN PLAN FORで実際に実行しなくても可）
　SQL> CONN USER/PASSWORD
　SQL> EXPLAIN PLAN FOR SELECT * FROM tab WHERE col1=’A’ AND col2=’B’;
　
　-- 記録された列使用情報を確認
　SQL> set long 10000
　SQL> SELECT DBMS_STATS.REPORT_COL_USAGE('USER','TAB') FROM DUAL;
　
　-- 表名を指定して列グループを作成
　SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS('USER','TAB') FROM DUAL;
　
　-- 出力例
　--------------------------------------------------------------------------------
　###############################################################################
　
　EXTENSIONS FOR USER.TAB
　........................
　
　1. (COL1, COL2) : SYS_STUNA$6DVXJXTP05EH56DTIR0X created
　###############################################################################
　
　-- 統計情報を取得
　SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS ('USER','TAB', NO_INVALIDATE=>FALSE);
　

拡張統計は実際にクエリが利用するWHERE句の条件に合わせて取得する必要があります。たとえば、col1、col2、col3で取得した列グループ統計は、col1、col2の条件を指定したクエリでは使用されないため、注意が必要です。

移行前や開発環境など、クエリの性能劣化が起きていない（期待した時間内に結果が返る）環境がある場合に有効な方法です。特定の実行計画にするためのヒント句のリストを取得します。

CBOトレースを取得するため、対象のSQLをハードパースさせる必要があります。

　
　-- 対象のADDRESS、HASH_VALUEを確認する
　SQL> conn /as sysdba
　SQL> SELECT SQL_ID,ADDRESS,HASH_VALUE,SQL_FULLTEXT FROM V$SQL
　2 WHERE SQL_FULLTEXT LIKE '%select col1 from test%';
　
　SQL_ID        ADDRESS          HASH_VALUE SQL_FULLTEXT
　------------- ---------------- ---------- ---------------------
　5qmzjwsrrjjw3 00007FFC2A1BA1D0 796444547  select col1 from test
　
　-- ハードパースをさせるため、対象のクエリを共有プールからパージ
　SQL> EXEC DBMS_SHARED_POOL.PURGE ('00007FFC2A1BA1D0,796444547','C');
　
　--CBOトレースのイベントを有効にする
　SQL> CONN USER/PASSWORD
　SQL> ALTER SESSION SET EVENTS '10053 LEVEL 1';
　
　-- 対象のクエリを実行（ハードパース）し、トレースを解除
　SQL> select col1 from test;
　SQL> ALTER SYSTEM SET EVENTS '10053 OFF’;
　
　-- トレースの出力先を確認
　SQL> SELECT VALUE FROM V$DIAG_INFO WHERE NAME = ‘Default Trace File’;
　

出力されたトレースファイル内を「Outline Data」で検索し「/*+ ～ */」内の内容を移行先（遅い）環境のクエリにヒント句として埋め込むことで性能が改善されるかご確認ください。

　
　SELECT
　/*+
　BEGIN_OUTLINE_DATA
　　IGNORE_OPTIM_EMBEDDED_HINTS
　　OPTIMIZER_FEATURES_ENABLE('19.1.0')
　　DB_VERSION('19.1.0')
　　ALL_ROWS
　　OUTLINE_LEAF(@"SEL$1")
　　FULL(@"SEL$1" "TEST"@"SEL$1")
　END_OUTLINE_DATA
　*/ col1 from test;
　

バージョンアップ前や開発環境からの移行後に発生するクエリ性能劣化については、実際のサポートでは「新機能／仕様変更によって実行計画が変わった」もしくは「本番環境と開発環境で同一の実行計画だが、使用しているデータが異なるため最適な実行計画は異なる」ことが原因のケースが多く確認できます。

今回ご紹介した方法は、サポートからの回答を待つ間に、実データや他ユーザに極力影響を与えずに簡単に試せる方法を記載しました。そのため、パラメータの変更などは「ALTER SESSION」で行い、SQL*Plusでクエリを実行することを前提としています。

実際にはアプリケーションからの実行で、ALTER SESSIONの埋め込みが難しいケースもあると思います。そのようなケースでは次の例のようにLOGONトリガーを使用することで、アプリケーションからの接続のタイミングで処理実行ユーザに対してALTER SESSIONを有効にすることができます。

　
　-- LOGONトリガーでALTER SESSIONを有効にする
　SQL> CONN USER/PASSWORD
　SQL> CREATE OR REPLACE TRIGGER tr1 AFTER LOGON ON SCHEMA
　2 BEGIN
　3 EXECUTE IMMEDIATE
　4 'ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE=''11.2.0.4'' ';
　5 END;
　6 /
　

確認が終わったら必ずトリガーの削除を忘れないようにしましょう！

　
　SQL> DROP TRIGGER tr1;
　

明日のJPOUG Advent Calendar 2020 の担当はryota_hnkさんです。ryota_hnkさん、よろしくお願いします！

2021年 18日目
Oracle Databaseのサポート対応で依頼することの多いファイルとコマンド
https://www.ashisuto.co.jp/db_blog/article/jpoug-oracledb-logfiles-command.html

2020年 17日目
Oracle Databaseバージョンアップ後の性能劣化で試したい暫定対処
https://www.ashisuto.co.jp/db_blog/article/jpoug-vup-temp-solution.html

2019年 24日目
【Oracle Database】DMLリダイレクションで一歩進んだ Active Data Guard の使い方（19c新機能）
https://www.ashisuto.co.jp/db_blog/article/20191224_ADG_REDIRECT_DML.html

2019年 20日目
【Oracle Database Cloud】"超"現実的に考える！Oracle CloudへのDB移行ガイド
https://www.ashisuto.co.jp/db_blog/article/20191220_cloud.html

2017年 14日目
Oracle Database 12cR2へのアップグレード後に発生するORA-01017
https://www.ashisuto.co.jp/db_blog/article/201712-ora-01017.html

2016年 9日目
【Oracle Database】2016年にサポートにお問い合わせをいただいたORAエラー TOP5
https://www.ashisuto.co.jp/db_blog/article/2016_oerr_rank.html

2015年 15日目
パフォーマンスダウンを「再現待ち」にしないための準備
https://www.ashisuto.co.jp/db_blog/article/20151215_oracle_pfmdwn.html