Оставить заявку
Заказать звонок
г. Москва, ул. Нагатинская, д.1 стр.40. На карте

26 Декабря 2018 Хабр, декабрь 2018

Проблема со связанными переменными: как превратить оптимизатор из врага в друга

РДТЕХ и icon рассказали о передовых технологиях централизованного производства документов, Array

Автор статьи – Виктор Варламов, OCP.
Оригинал статьи опубликован 07.07.2017.
Отдельное спасибо автору перевода, Тагиру Билалову.

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

Источник: habr.com


Возврат к списку

Пресс-центр

PR-служба РДТЕХ