Оригинал статьи опубликован 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
Комментарии (7)
Sshumeev
26.12.2018 15:22Виктор добрый день! На какой версии выполнялись эти упражнения?
brutaltag
26.12.2018 15:37+1Виктор сейчас в Польше, там праздничные дни, и он мне пока не ответил. Вроде даже у него нет профиля на Хабре, поэтому будем благодарны за инвайт.
Насчет версии у меня есть предположение, что это Oracle 12, впрочем, в 11 тоже скорее всего сработает, насчет 10 и ниже есть сомнения (хотя VPD еще в Oracle 8.1 появился). Программный код выше я не воспроизводил. А какая версия у Вас?xtender
28.12.2018 02:34+1У меня пара замечаний по сабжу:
1. Для решения этой проблемы есть Adaptive cursor sharing, и он делает именно то, что нужно: на основе гистограмм создает подходящие дочерние курсоры. Если у них это не работало, значит надо было разбираться с причиной почему ACS не работал. Игорь Усольцев делал отличную подробную презентацию в российской юзергруппе оракл (RuOUG). Вообще, советую посещать наши мероприятия.
2. Раз на базе стоит cursor_sharing=force, то можно было просто создать профиль с одним единственным хинтом BIND_AWARE на любом из этих запросов, с указанием параметра force_match=>true.
3. Насчет динамики:
Понятно, что исправление приложения и использование параметров как литералов в запросе – это самый подходящий способ решения проблемы, но он ведет к динамическому SQL с его известными недостатками.
Я еще на 10-ке использовал несколько разных вариантов в зависимости от условий:
3.1 Если был возможен PL/SQL то просто разбивал через IF на пару разных вариантов, грубо говоря: IF cardinality>N then query1 else query2
A cardinality получал одним из быстрых способов.
3.2. Разбиение на union all с доп.подзапросом, например:
select/*+ index(t1 (a,b)) */ * from t1 where ... and (select count(*) from t1 where ... and rownum<=X)<X union all select/*+ full(t1) или index_ffs(t1) */ * from t1 where ... and (select count(*) from t1 where ... and rownum<=X)=X
И если данные уже промаркированы как в статье(отдельная таблица где помечены «большие/средние/маленькие»), то этот вариант был бы намного проще
4. Здесь неверно:
Другой путь – отключение запроса связанных переменных (ALTER SESSION SET "_OPTIM_PEEK_USER_BINDS" = FALSE) или удаление гистограмм (ссылка).
Это решение абсолютно для противоположной цели: отключение _OPTIM_PEEK_USER_BINDS и удаление гистограм делают в случае, если оракл плодит разные планы, а хочется строго одного и того же для любых биндов.
5. Существует и еще один подход: секционирование с учетом data skew. Тут вариантов много, одним из простейших является интервальное секционирование по «перекошенному» столбцу основного ACCESS предиката. А для статьи из примера удобно было бы секционировать как раз по SMALL/MIDDLE/LARGE, то есть три разные секции, каждая со своей статистикой(причем заработает даже без гистограмм, т.к. достаточно (num_rows-num_nulls)/num_distinct статистик по секции), что дает оптимизатору легко понять селективность столбца в данной секции.
ЗЫ. Надо было Виктору ко мне обратиться, вместе бы посмотрели почему ACS у них не работал.
GlukKazan
brutaltag
Есть вещи, над которыми разработчик не властен. Как автор перевода я не знаю иных подробностей подопытной системы, могу только заметить, что если бы разработчики и администраторы БД всегда находили общий язык, то мир лишился бы очень многих технологий.
Менять параметры БД в угоду тривиального решения одной из задач системы — верный способ нарваться на неприятности в других бизнес-модулях. И хорошо, если все скелеты сразу выпадут из шкафов, а могут и спустя годы…
xtender
CURSOR_SHARING = force, да еще и установленный на уровне системы, а не сессии, уже давно вызывает больше боли, чем дает пользы. Его по-моему давно убрали из основных тестов и поэтому кол-во багов с ним очень высоко. Есть у меня один клиент, который до сих пор продолжает упорно жрать кактус, постоянно ловя всякие неожиданные баги из версии в версию…