この記事はNigel BaylissによるUsing the Automatic SQL Tuning Set in Oracle Database 19cを日本語に翻訳したものです。
2024年5月17日
Introduction
自動SQLチューニング・セット(ASTS)に関する以前の投稿をご覧になったことがあるかもしれません。 自動SPMのライセンスが緩和され、この機能はASTSがONになっていることに依存するため、再度この機能についてお話したいと思います。
ASTSは個々のSQL文情報をキャプチャしてSYSAUX表領域に格納するため、ASTSを有効にした場合、領域が消費されることが予想されます。一部のお客様から急激な増加が報告されたため、なぜこのようなことが起こるのかを調査しました。その後、この問題に対処するために2つの重要なパッチが作成されました：
Patch/Bug 15878434 – EXTEND LITERAL REPLACEMENT TO SUPPORT MERGE STATEMENTS TOO
Patch/Bug 35488357 – REDUCE NUMBER OF SQL STATEMENTS WITH SAME FORCE MATCHING SIGNATURE IN AUTO SQL TUNING SET
これらのパッチが必要ですか？
Patch 35488357は、SQL文が初めて出現した際、最初のバッチでキャプチャする動的SQL文の数を減らします。 例えば、アプリケーションが以下のようなSQLを生成する場合、データベースは最大1000のバージョンをキャプチャできます:
select col from table where id = 10; select col from table where id = 20; select col from table where id = 30; ...etc
Patch 15878434では、これを100に減らします。 技術的に言えば、ASTSは同じフォース・マッチング・シグネチャを持つSQLステートメントを100個までしかキャプチャできません(以前の1000個ではなく)。 これは動的SQLを利用している場合でも、ASTSがキャプチャする文の数を調整するようになったためです。
アプリケーションがバインド値を使っていたり、リテラルの利用を限定しているのであれば、このパッチを適用する必要はありません。
Patch 15878434により、MERGE文のフォース・マッチング・シグネチャが正しく扱われるようになります。たとえば、バインド変数の代わりにリテラルを使用するMERGE文には異なるフォース・マッチング・シグネチャがあるため、ASTS はすべてのバージョンを制限なくキャプチャします。たとえば、
merge into tab1 t1 using ... set t1.col1 = 'some literal value 1', col1 = 'some other literal 1' ... merge into tab1 t1 using ... set t1.col1 = 'some literal value 2', col1 = 'some other literal 2' ... merge into tab1 t1 using ... set t1.col1 = 'some literal value 3', col1 = 'some other literal 3' ... ...
自動SQLチューニング・セットが無期限または大幅に増加したお客様がいました。このパッチで、上記のMERGE文が同じフォース・マッチング・シグネチャを持つようになり、100を超えるバージョンがキャプチャされなくなります。
アプリケーションがMERGE文でリテラルではなくバインド変数を使用している場合は、このパッチを適用する必要はありません。
問題のある使用例
とあるオープンソースのORMフレームワークが生成した問題のある使用例を見ました。それは次のようなSQL文を生成しました。
insert into t1 values (:bind1, :bind2, :bind3, :bind4 ... ); insert into t1 values (:bind1, :bind2, :bind2, :bind3 ... ); insert into t1 values (:bind1, :bind4, :bind5, :bind6 ... ); ...
これらのSQL文はバインド値を使用しますが、バインド名が異なるため、各INSERT文は異なるフォース・マッチング・シグネチャを持ちます。 バインド変数の数が多ければ、組合せの数は膨大になります。ASTSは、1つ1つキャプチャします。 オラクルは、この動作はOracle Databaseではなく、クライアント側のツールで対処する必要があると考えています。 なお、私たちは関連するオープン・ソース・コミュニティに働きかけています。
パッチを入手できますか？
パッチは、現在RUに含まれていませんが、早急に対応するように依頼しています。パッチは比較的新しいもので、まだお客様からのリクエストがないため、MOSにはまだ表示されません。まずはリクエストしてください！
データベースが動的SQLしか利用していない場合は？
SQL計画管理は、繰り返し可能なSQL文で動作するように設計されています。そのため、動的SQLのみを使用し、カーソル共有の強制が使えない場合、SPMは大きな付加価値にはなりません。
ただし、自動索引作成などの自動化機能は、ASTSに依存しており、自動索引作成は、SQLが繰り返し実行されない場合でも、価値を提供します。 これがASTSで動的SQLをキャプチャする理由です。非常に多くの異なるSQL文を使用するユースケースを見られたため、シグネチャの最大キャプチャ数を1000から100に削減しました。