元の記事は 2017年7月7日に公開されました。
翻訳-brutaltagの作者に感謝します。
通常、レポートシステムは、さまざまなイベントによってトリガーされる何百もの長いリクエストを実行します。 クエリパラメータは、クライアントのリストと時間間隔（毎日、毎週、毎月）です。 テーブル内のデータが不均一であるため、1つのクエリはレポートパラメーターに応じて1行または100万行のいずれかを生成できます（ファクトテーブル内のクライアントの行数は異なります）。 各レポートは、入力パラメーターを受け取り、追加の変換を実行し、 関連する変数で静的カーソルを開き、最後にこの開いたカーソルを返すメイン関数を持つパッケージの形式で作成されます。 DBパラメータCURSOR_SHARINGがFORCEに設定されています。
このような状況では、オプティマイザーによるクエリプランの再利用の場合でも、クエリがリテラルの形式のパラメータで完全に解析される場合でも、パフォーマンスの低下に対処する必要があります。 バインドされた変数は、最適でないクエリプランを引き起こす可能性があります。
アレックス・ゴルバチョフは、彼の著書「Oracle Expert Practices」で、トム・カイトが語った興味深いストーリーを紹介しています。 毎週月曜日に、ユーザーは変更されたクエリプランを処理する必要がありました。 信じがたいですが、それは：
「エンドユーザーの観察によれば、月曜日に大雨が降ったとき、データベースのパフォーマンスはひどいものでした。 それ以外の曜日または月曜日には、雨が降っても問題はありませんでした。 DBAとの会話から、トム・カイトは、データベースが再起動されるまで問題が続き、その後パフォーマンスが正常になることを学びました。 それが回避策でした：雨の月曜日-再起動。
これは実際のケースであり、Oracleがどのように機能するかに関する優れた知識のおかげで、問題は魔法なしで完全に解決されました。 記事の最後に解決策を示します。
関連する変数がどのように機能するかを示す小さな例を次に示します。
不均一なデータを持つテーブルを作成します。
SQL> CREATE TABLE VVP_HARD_PARSE_TEST(C1 NUMBER, C2 NUMBER, C3 VARCHAR2(300)); TABLE created. SQL> INSERT INTO VVP_HARD_PARSE_TEST SELECT ROWNUM C1, CASE WHEN LEVEL < 9 THEN 1 WHEN MOD(ROWNUM, 100)=99 THEN 99 ELSE 1000000 END C2, RPAD('A', 300, 'A') C3 FROM DUAL CONNECT BY LEVEL CREATE INDEX IND_VVP_HARD_PARSE_TEST_C2 ON VVP_HARD_PARSE_TEST(C2); INDEX created. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => USER, TABNAME => 'VVP_HARD_PARSE_TEST', CASCADE => TRUE, METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE 254'); PL/SQL PROCEDURE successfully completed. SQL> SELECT histogram FROM user_tab_columns WHERE table_name = 'VVP_HARD_PARSE_TEST' AND column_name = 'C2'; HISTOGRAM --------- FREQUENCY SQL> SELECT c2, COUNT(*) FROM VVP_HARD_PARSE_TEST GROUP BY c2 ORDER BY 1; C2 COUNT(*) ----------------------- 1 8 99 10000 1000000 989992
つまり、100万行のテーブルVVP_HARD_PARSE_TESTがあり、10,000の場合、フィールドはC2 = 99、C2 = 1の8レコード、残りはC2 = 1,000,000です。フィールドC2のヒストグラムは、このデータ分布に関するOracleオプティマイザーを示しています。 この状況は、データの不均等な分布として知られています。ヒストグラムは、要求されたデータに基づいて適切なクエリプランを選択するのに役立ちます。
このテーブルへの簡単なクエリを観察します。 明らかに要求のために
SELECT * FROM VVP_HARD_PARSE_TEST WHERE c2 = :p
p = 1の場合、INDEX RANGE SCANが最良の選択です。p= 1000000の場合は、FULL TABLE SCANを使用することをお勧めします。 Query1とQuery1000000のクエリは、コメント内のテキストを除いて同一です。これは、異なるクエリプラン識別子を取得するために行われます。
DECLARE p NUMBER; v NUMBER; BEGIN V := 0; p := 1000000; FOR rec IN (SELECT /*+query1000000*/ * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); v : =0; p := 1; FOR rec IN (SELECT /*+query1000000*/ * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); ----------------- V := 0; p := 1; FOR rec IN (SELECT /*+query1*/ * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); v := 0; p := 1000000; FOR rec IN (SELECT /*+query1*/ * FROM VVP_HARD_PARSE_TEST WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); END;
次に、クエリプランを見てみましょう。
SQL> SELECT sql_id, child_number, executions, plan_hash_value, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT % * FROM VVP_HARD_PARSE_TEST WHERE C2%'; SQL_ID CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE SQL_TEXT ------------------------------------------------- 7rqnhhp6pahw2 0 2 2782757451 SELECT /*+query1000000*/ * FROM VVP_HARD_PARSE_TEST WHERE C2 = :B1 7xwt28hvw3u9s 0 2 2463783749 SELECT /*+query1*/ * FROM VVP_HARD_PARSE_TEST WHERE C2 = :B1 SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '7rqnhhp6pahw2', format => 'basic +peeked_binds')); SELECT /*+query1000000*/ * FROM VVP_HARD_PARSE_TEST WHERE C2 = :B1 PLAN hash VALUE: 2782757451 ------------------------------------------------- | Id | Operation | Name | ------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| VVP_HARD_PARSE_TEST | ------------------------------------------------- Peeked Binds (IDENTIFIED BY position): -------------------------------------- 1 - :B1 (NUMBER): 1000000 SQl> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '7xwt28hvw3u9s', format => 'basic +peeked_binds')); SELECT /*+query1*/ * FROM VVP_HARD_PARSE_TEST WHERE C2 = :B1 PLAN hash VALUE: 2463783749 ------------------------------------------------------------------ | Id | Operation | Name | ------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| VVP_HARD_PARSE_TEST | | 2 | INDEX RANGE SCAN | IND_VVP_HARD_PARSE_TEST_C2 | ------------------------------------------------------------------ Peeked Binds (IDENTIFIED BY position): -------------------------------------- 1 - :B1 (NUMBER): 1
ご覧のとおり、異なる要求の計画は、最初の実行時に一度だけ作成されます（各要求に対してCHILD_NUMBER = 0の子カーソルが1つだけ存在します）。 各要求は2回実行されます（EXECUTION = 2）。 ハード解析中に、Oracleは関連する変数の値を取得し、これらの値に従って計画を選択します。 ただし、関連する変数が2回目の実行で変更された場合でも、彼は次の実行に同じ計画を使用します。 最適でない計画が使用されています-変数C2 = 1のQuery1000000は、INDEX RANGE SCANの代わりにFULL TABLE SCANを使用します。逆も同様です。
アプリケーションを修正し、クエリのリテラルとしてパラメーターを使用することが問題を解決するのに最適な方法であることは明らかですが、既知の欠点がある動的SQLにつながります。 別の方法は、関連する変数のクエリを無効にする（
ALTER SESSION SET "_OPTIM_PEEK_USER_BINDS" = FALSE
）か、ヒストグラムを削除する（ リンク ）ことです。
考えられる解決策の1つは、 仮想プライベートデータベースとも呼ばれるデータアクセスポリシーの代替使用です（きめ細かなアクセス制御、きめの細かいアクセス制御、行レベルの制御）。 これにより、要求をその場で変更できるため、要求が詳細なアクセス制御を使用するたびに要求計画を完全に分析できます。 この手法は、Randalph Geistの記事で詳しく説明されています 。 この方法の欠点は、完全な解析の数が増え、クエリプランを操作できないことです。
これから何をするか見てみましょう。 データを分析した後、年間のトランザクション数またはトランザクション数に応じて、大、中、小（LMSまたは9-5-1）の3つのカテゴリに顧客を分類することにしました。 また、レポートの行数は期間に厳密に依存します：月次-大、週-中、日-小または9-5-1。 その後、ソリューションは簡単です。セキュリティポリシーの述語を各カテゴリと各期間に依存させます。 そのため、リクエストごとに9個の子カーソルを取得できます。 さらに、異なるポリシーを持つクエリは同じクエリ識別子につながります。これにより、SQL PLAN MANAGEMENT（SQLプランベースライン）を実装できます。
SQL> CREATE TABLE HARD_PARSE_TABLE AS SELECT * FROM dual; TABLE created. SQL> CREATE TABLE CLIENTS_HP_STATISTICS (client_seqno NUMBER, client_id VARCHAR2(255), cnt_year NUMBER); TABLE created. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (1, 'SMALL CLIENT', 8); 1 ROW inserted. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (99, 'MIDDLE CLIENT', 50001); 1 ROW inserted. SQL> INSERT INTO CLIENTS_HP_STATISTICS (client_seqno, client_id, cnt_year) VALUES (1000000,'LARGE CLIENT', 989992); 1 ROW inserted. SQL> CREATE OR REPLACE PACKAGE FORCE_HARD_PARSE_PKG IS gc_small CONSTANT NUMBER := 1; gc_middle CONSTANT NUMBER := 5; gc_large CONSTANT NUMBER := 9; gc_client_middle CONSTANT NUMBER := 50000; gc_client_large CONSTANT NUMBER := 500000; gc_daterange_middle CONSTANT NUMBER := 10; gc_daterange_large CONSTANT NUMBER := 50; FUNCTION FORCE_HARD_PARSE(in_schema VARCHAR2, in_object VARCHAR2) RETURN VARCHAR2; PROCEDURE SET_PREDICATE (n NUMBER); PROCEDURE SET_PREDICATES (p_daterange NUMBER DEFAULT NULL, p_clientrange NUMBER DEFAULT NULL); PROCEDURE CALC_PREDICATE; PROCEDURE CALC_PREDICATES(p_date_interval NUMBER DEFAULT 1, p_client_seqno NUMBER DEFAULT NULL, p_client_id VARCHAR2 DEFAULT NULL, p_client_seqno_list VARCHAR2 DEFAULT NULL ); END FORCE_HARD_PARSE_PKG; PACKAGE created. SQL> CREATE OR REPLACE PACKAGE BODY FORCE_HARD_PARSE_PKG IS g_predicate NUMBER; -- g_daterange || 0 || g_clientrange g_daterange NUMBER; -- 1 - small, 5 - middle, 9 - large g_clientrange NUMBER; -- 1 - small, 5 - middle, 9 - large -- FUNCTION FORCE_HARD_PARSE(in_schema VARCHAR2, in_object VARCHAR2) RETURN VARCHAR2 IS BEGIN IF NVL(g_predicate, 0) = 0 THEN RETURN NULL; ELSE RETURN TO_CHAR(g_predicate, 'TM') || ' = ' || TO_CHAR(g_predicate, 'TM'); END IF; END FORCE_HARD_PARSE; -- PROCEDURE SET_PREDICATE (n NUMBER) IS BEGIN g_predicate := n; END; PROCEDURE SET_PREDICATES (p_daterange NUMBER DEFAULT NULL, p_clientrange NUMBER DEFAULT NULL) IS BEGIN IF p_daterange IS NOT NULL THEN g_daterange := p_daterange; CALC_PREDICATE; END IF; IF p_clientrange IS NOT NULL THEN g_clientrange := p_clientrange; CALC_PREDICATE; END IF; END SET_PREDICATES; PROCEDURE CALC_PREDICATE IS BEGIN g_predicate := NVL(g_daterange, 0) * 100 + NVL(g_clientrange, 0); END CALC_PREDICATE; PROCEDURE CALC_PREDICATES (p_date_interval NUMBER DEFAULT 1, p_client_seqno NUMBER DEFAULT NULL, p_client_id VARCHAR2 DEFAULT NULL, p_client_seqno_list VARCHAR2 DEFAULT NULL) IS v_cnt NUMBER; BEGIN IF p_date_interval IS NOT NULL THEN g_daterange := CASE WHEN p_date_interval < gc_daterange_middle THEN gc_small WHEN p_date_interval < gc_daterange_large THEN gc_middle ELSE gc_large END; CALC_PREDICATE; END IF; IF COALESCE(p_client_seqno, p_client_id, p_client_seqno_list) IS NOT NULL THEN SELECT NVL(SUM(cnt_year), 0) AS cnt INTO v_cnt FROM CLIENTS_HP_STATISTICS t WHERE 1=1 AND (p_client_seqno IS NULL OR p_client_seqno = t.client_seqno) AND (p_client_id IS NULL OR p_client_id = t.client_id) AND (p_client_seqno_list IS NULL OR t.client_seqno IN (SELECT SUBSTR(s, CASE WHEN LEVEL > 1 THEN INSTR(s, ',', 1, LEVEL - 1 ) + 1 ELSE 1 END, INSTR(s, ',', 1, LEVEL) – CASE WHEN LEVEL > 1 THEN INSTR(s, ',', 1, LEVEL – 1) + 1 ELSE 1 END) FROM (SELECT p_client_seqno_list||',' AS s FROM DUAL) CONNECT BY INSTR(s, ',', 1, LEVEL) > 0)); g_clientrange := CASE WHEN v_cnt > gc_client_large THEN gc_large WHEN v_cnt > gc_client_middle THEN gc_middle ELSE gc_small END; CALC_PREDICATE; END IF; END CALC_PREDICATES; END FORCE_HARD_PARSE_PKG; PACKAGE BODY created. SQL> EXEC DBMS_RLS.ADD_POLICY (USER, 'HARD_PARSE_TABLE', 'HARD_PARSE_POLICY', USER, 'FORCE_HARD_PARSE_PKG.FORCE_HARD_PARSE', 'select'); PL/SQL PROCEDURE successfully completed.
ここで、このようなテクノロジーをレポートに埋め込みたい場合は、クエリにHARD_PARSE_TABLEを追加し（これで少しは損なわれません）、メインクエリが実行される前にCALC_PREDICATESを呼び出す必要があります。
この手法が前の例をどのように変換できるか見てみましょう。
DECLARE p NUMBER; v NUMBER; BEGIN V := 0; p := 1000000; FORCE_HARD_PARSE_PKG.SET_PREDICATE(1000000); FOR rec IN (SELECT /*+query_hp1000000*/ * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); v := 0; p := 1; FORCE_HARD_PARSE_PKG.SET_PREDICATE(1); FOR rec IN (SELECT /*+query_hp1000000*/ * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); ----------------- V := 0; p := 1; FORCE_HARD_PARSE_PKG.SET_PREDICATE(1); FOR rec IN (SELECT /*+query_hp1*/ * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); v := 0; p := 1000000; FORCE_HARD_PARSE_PKG.SET_PREDICATE(1000000); FOR rec IN (SELECT /*+query_hp1*/ * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2 = p) LOOP V := v + 1; END LOOP; dbms_output.put_line(v); END;
実行計画を見てみましょう。
SQL> SELECT sql_id, child_number, executions, plan_hash_value, sql_text, s.* FROM v$sql s WHERE sql_text LIKE 'SELECT % * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE c2%' ORDER BY 1,2; SQL_ID CHILD_NUMBER EXECUTIONS PLAN_HASH_VALUE SQL_TEXT -------------------------------------------------------------------------------- 7wva3uqbgh4qf 0 1 1136240498 SELECT /*+query_hp1000000*/ * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE C2 = :B1 7wva3uqbgh4qf 1 1 3246475190 SELECT /*+query_hp1000000*/ * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE C2 = :B1 8cju3tfjvwm1p 0 1 3246475190 SELECT /*+query_hp1*/ * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE C2 = :B1 8cju3tfjvwm1p 1 1 1136240498 SELECT /*+query_hp1*/ * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE C2 = :B1 -- SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '7wva3uqbgh4qf', cursor_child_no => 0, format => 'basic +peeked_binds')); SELECT /*+query_hp1000000*/ * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE C2 = :B1 PLAN hash VALUE: 1136240498 ---------------------------------------------------- | Id | Operation | Name | ---------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | MERGE JOIN CARTESIAN| | | 2 | TABLE ACCESS FULL | HARD_PARSE_TABLE | | 3 | BUFFER SORT | | | 4 | TABLE ACCESS FULL | VVP_HARD_PARSE_TEST | ---------------------------------------------------- Peeked Binds (IDENTIFIED BY position): -------------------------------------- 1 - :B1 (NUMBER): 1000000 -- SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '7wva3uqbgh4qf', cursor_child_no => 1, format => 'basic +peeked_binds')); SELECT /*+query_hp1000000*/ * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE C2 = :B1 PLAN hash VALUE: 3246475190 -------------------------------------------------------------------- | Id | Operation | Name | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | MERGE JOIN CARTESIAN | | | 2 | TABLE ACCESS FULL | HARD_PARSE_TABLE | | 3 | BUFFER SORT | | | 4 | TABLE ACCESS BY INDEX ROWID| VVP_HARD_PARSE_TEST | | 5 | INDEX RANGE SCAN | IND_VVP_HARD_PARSE_TEST_C2 | -------------------------------------------------------------------- Peeked Binds (IDENTIFIED BY position): -------------------------------------- 1 - :B1 (NUMBER): 1 -- SQl> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '8cju3tfjvwm1p', cursor_child_no => 0, format => 'basic +peeked_binds')); SELECT /*+query_hp1*/ * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE C2 = :B1 PLAN hash VALUE: 3246475190 -------------------------------------------------------------------- | Id | Operation | Name | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | MERGE JOIN CARTESIAN | | | 2 | TABLE ACCESS FULL | HARD_PARSE_TABLE | | 3 | BUFFER SORT | | | 4 | TABLE ACCESS BY INDEX ROWID| VVP_HARD_PARSE_TEST | | 5 | INDEX RANGE SCAN | IND_VVP_HARD_PARSE_TEST_C2 | -------------------------------------------------------------------- Peeked Binds (IDENTIFIED BY position): -------------------------------------- 1 - :B1 (NUMBER): 1 -- SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '8cju3tfjvwm1p', cursor_child_no => 1, format => 'basic +peeked_binds')); SELECT /*+query_hp1*/ * FROM VVP_HARD_PARSE_TEST, HARD_PARSE_TABLE WHERE C2 = :B1 PLAN hash VALUE: 1136240498 ---------------------------------------------------- | Id | Operation | Name | ---------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | MERGE JOIN CARTESIAN| | | 2 | TABLE ACCESS FULL | HARD_PARSE_TABLE | | 3 | BUFFER SORT | | | 4 | TABLE ACCESS FULL | VVP_HARD_PARSE_TEST | ---------------------------------------------------- Peeked Binds (IDENTIFIED BY position): -------------------------------------- 1 - :B1 (NUMBER): 1000000
よさそう！ 各クエリは、異なる子カーソルと異なるプランで2回実行されます。 パラメーターC2 = 1,000,000の場合、両方のクエリでFULL TABLE SCANが表示され、パラメーターC1 = 1の場合、常にINDEX RANGE SCANが表示されます。
最後に、雨の月曜日の場合の解決策を示します。
「毎週日曜日の週末にコールドバックアップが行われたため、すべてのクエリプランは月曜日の朝に最初に実行されたときに再生成されました。 通常、従業員の1人は他の従業員よりも早く作業を開始し、彼の要求計画はその週に他のユーザーに対してうまく実行されました。 ただし、雨が降っていた場合、このユーザーは朝のルートに問題があるため、営業日の開始までに遅れました。 その後、レポートのバッチ計算が最初に開始されましたが、関連する変数の不適切な値のため、残りのケースではクエリプランが完全に悪かったのです。
そして、いくつかの便利なシステムビュー：
•
dba_tab_histograms, all_tab_histograms, user_tab_histograms
•
v$vpd_policy
•
v$sql_bind_capture
•
dba_hist_sqlbind