Oracle Databaseの仕様ですが、当初は問題なく動くものの、Oracle Databaseのユーザー数が増えてくると性能問題が発生してしまう、というものです。
前提
- Oracle Databaseで複数のOracleユーザーを利用
- ユーザーごとに同じ名前のテーブルを作成
CREATE TABLE TAB1 ( ... ); - 同じSQL文をユーザーごとに実行
SELECT * FROM TAB1; - ユーザーがどんどん、どんどん増えていく... ← ここがポイント
複数のユーザが同じ名前のテーブルを所有していても、これらのテーブルは名前が同じであるだけで、オブジェクトとしては異なっている、という状況です。
発生する性能問題
SQL文の実行のタイミング次第だと思うのですが、ユーザーごとに似たようなタイミングで実施する業務(SQL文)は多いですよね...。そのSQL文の実行が複数のユーザーにおいて同じタイミングでおこなわれることにより性能問題として顕在化するものです。
- SQL文のハードパースで時間がかかる
- ハードパース中のカーソル操作での library cache lock や 共有カーソル関連（cursor: mutex .. / cursor: pin ..）の待機イベントが発生
- SQL文のハードパースにより同時実行性が低下
- ハードパースにおいてlibrary cacheラッチが取得されることで、排他制御がおこなわれる。排他制御がなされることで同時実行性が低下
SQL文のハードパースが発生している事象の結果としてVersion Countの高いSQLが確認できます。バージョンカウントとは、同一のSQL文(親カーソル)に対して生成された子カーソルの数のことです。
対処案
ユーザーごとに異なるSQL文に変更することでSQL文のハードパースを減らすことができます。
元のSQL
SELECT * FROM TAB1;
対処案1: テーブル名をスキーマ名で修飾する
SELECT * FROM USER1.TAB1;
対処案2: ユーザーを示すコメントを追加する
SELECT /* USER1 */ * FROM TAB1;
補足1
共有カーソル関連（cursor: mutex .. / cursor: pin ..）の待機イベントについては、津島博士のパフォーマンス講座 第32回 SQL統計と実行計画の出力について をご確認ください。
また、津島博士のパフォーマンス講座 第7回 共有プールについてには以下のような記述があります。
SQL文解析時のハード・パースにはlibrary cacheラッチ（Oracle Database 11gからは効率が良いmutexメカニズムを使用しています。そのため、より細かい単位で排他制御が可能になり待機が少なくなります）が獲得されます（ソフト・パースもSQLカーソルを探すときに獲得しますが、ハード・パースの方が獲得する時間が長くなります。これについてはここでは省略します）。このように、ラッチが獲得されると並列性が低下しますので、できるだけ少なくした方がパフォーマンスが良いことになります。そのため、ハード・パースはできるだけ避けたい訳です。
（3）共有されないSQL
共有できるような同一SQL文でも共有されない場合があります。そのようなSQL文は別バージョンとして新たな子カーソルの実行計画が作成されます（カーソルは子カーソル毎に実行計画を作成できるようになっています。このとき統計情報のversion_countがアップされます）。StatspackやAWRの「SQL」セクションの「SQL ordered by Version Count」を参照するとversion_countの多い順に出力されますので、SQL文を確認することができます。以下が別バージョンとして扱われる代表的な理由になります（V$SQL_SHARED_CURSORを参照すると子カーソルを共有されない理由が分かります）ので、できるだけ別バージョンにならないように注意して下さい。
補足2
本件はとあるお客様よりお問い合わせをいただいた事象をもとに記述いたしました。(とあるお客様からのお問い合わせをきっかけに私は事象を認識したのですが、まわりのエンジニアと会話したところ、別のお客様でも同様の事象が発生していたことを教えていただきました。)
特に、ISVのお客様において、Autonomous Database Serverlessを利用して複数のエンドユーザー様にサービスを提供する場合、(PDB構成がとれないため)エンドユーザー様ごとにOracle Databaseのユーザを分けて提供することがあります。
昔にくらべてハードウェア性能が格段にあがったことにより、多くのエンドユーザー様のデータを格納することができるようになったことで顕在化するケースがでてきたもの、と考えています。