Автор статьи – Виктор Варламов(varlamovVp18), OCP.
Оригинал статьи опубликован 07.07.2017.
Отдельное спасибо автору перевода — brutaltag.

В нашей системе подготовки отчетности обычно выполняются сотни длительных запросов, которые вызываются различными событиями. Параметрами запросов служат список клиентов и временной интервал (дневной, недельный, месячный). Из-за неравномерных данных в таблицах один запрос может выдать как одну строку, так и миллион строк, в зависимости от параметров отчета (у разных клиентов — различное количество строк в таблицах фактов). Каждый отчет выполнен в виде пакета с основной функцией, которая принимает входные параметры, проводит дополнительные преобразования, затем открывает статический курсор со связанными переменными и в конце возвращает этот открытый курсор. Параметр БД CURSOR_SHARING выставлен в FORCE.
В такой ситуации приходится сталкиваться с плохой производительностью, как в случае повторного использования плана запроса оптимизатором, так и при полном разборе запроса с параметрами в виде литералов. Связанные переменные могут вызвать неоптимальный план запроса.

В своей книге “Oracle Expert Practices” Алекс Горбачев приводит интересную историю, рассказанную ему Томом Кайтом. Каждый дождливый понедельник пользователям приходилось сталкиваться с измененным планом запроса. В это трудно поверить, но так и было:
«Согласно наблюдениям конечных пользователей, в случаях, когда в понедельник шел сильный дождь, производительность базы данных была ужасной. В любой другой день недели или же в понедельник без дождя проблем не было. Из разговора с администратором БД Том Кайт узнал, что трудности продолжалис�� до принудительного рестарта базы данных, после чего производительность становилась нормальной. Вот такой был обходной маневр: дождливый понедельник – рестарт».

Это реальный случай, и проблема была решена совершенно без всякой магии, только благодаря отличным знаниям того, как работает 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


Другими словами, у нас есть таблица VVP_HARD_PARSE_TEST с миллионом строк, где в 10.000 случаев поле C2 = 99, 8 записей с C2 = 1, а остальные с C2 = 1000000. Гистограмма по полю С2 указывает оптимизатору 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 существует для каждого запроса). Каждый запрос выполняется дважды (EXECUTION = 2). Во время жесткого разбора Oracle получает значения связанных переменных и выбирает план соответственно этим значениям. Но он использует тот же самый план и для следующего запуска, несмотря на то что связанные переменные изменились во втором запуске. Используются неоптимальные планы – Query1000000 с переменной C2 = 1 использует FULL TABLE SCAN вместо INDEX RANGE SCAN, и наоборот.

Понятно, что исправление приложения и использование параметров как литералов в запросе – это самый подходящий способ решения проблемы, но он ведет к динамическому SQL с его известными недостатками. Другой путь – отключение запроса связанных переменных (ALTER SESSION SET "_OPTIM_PEEK_USER_BINDS" = FALSE) или удаление гистограмм (ссылка).

Одно из возможных решений — это альтернативное использование политик на доступ к данным, также известных как Virtual Private Database (детальный контроль доступа, Fine Grained Access Control, контроль на уровне строк). Это позволяет менять запросы на лету и поэтому может вызвать полный разбор плана запроса каждый раз, когда запрос использует детальный контроль доступа. Эта техника подробно описана в статье Рэндальфа Гейста. Недостатком этого метода является возрастающее число полных разборов и невозможность манипулировать планами запросов.

Посмотрите, что мы сейчас сделаем. После анализа наших данных мы решаем разбить клиентов на три категории – Большие, Средние и Маленькие (L-M-S или 9-5-1) – согласно количествам сделок или транзакций в течение года. Также количество строк в отчете строго зависит от периода: Месячный – Large, Недельный – Middle, Дневной – Small или 9-5-1. Далее решение простое – сделаем предикат политики безопасности зависящим от каждой категории и от каждого периода. Так, для каждого запроса мы получим 9 возможных дочерних курсоров. Более того, запросы с разными политиками приведут нас к одним и тем же идентификаторам запросов, это дает возможность реализовать SQL PLAN MANAGEMENT (sql plan baseline).

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

Выглядит здорово! Каждый запрос выполняется дважды, с различными дочерними курсорами и разными планами. Для параметра C2 = 1000000 мы видим FULL TABLE SCAN в обоих запросах, а для параметра C1 = 1 мы видим всегда INDEX RANGE SCAN.

В конце привожу разгадку случая с дождливыми понедельниками:

«Оказывается, каждые выходные в воскресенье происходил холодный бэкап, так что все планы запросов перегенерировались по первому выполнению утром в понедельник. Один из сотрудников обычно начинал свою работу раньше остальных, и его план запроса выполнялся хорошо и для остальных пользователей в течение недели. Однако если шел дождь, этот пользователь опаздывал к началу рабочего дня из-за проблем с его утренним маршрутом. Тогда первым запускался пакетный расчет отчетов, но план запроса из-за неподходящих значений связанных переменных был совершенно плох для остальных случаев».

И несколько полезных системных представлений:
• dba_tab_histograms, all_tab_histograms, user_tab_histograms
• v$vpd_policy
• v$sql_bind_capture
• dba_hist_sqlbind