Oracle mechanics

25.11.2009

latch: cache buffers chains и настройка запросов с использованием cardinality feedback

Filed under: CBO,Oracle,wait events — Igor Usoltsev @ 16:15
Tags: ,
Симптомы

Заметные проблемы общей производительности на работающей OLTP системе: высокая процессорная нагрузка при незначительном вводе-выводе, запрос из v$session показывает несколько активных сессий, выполняющих в течение сотен секунд один и тот же запрос, ожидающих latch: cache buffers chains или просто активно использующих процессор (On CPU / runqueue)

Отчёт ADDM в первых строках указывает на тот же запрос, но дополняет картину кластерными ожиданиями, также относящимися к буферному кэшу:

RATIONALE: Ожидание «latch: cache buffers chains» в классе «Concurrency» занимает 8% времени бд при выполнении запроса SQL_ID «6drhaqddkx6xd».
RATIONALE: Ожидание «gc current block 2-way» в классе «Cluster» занимает 6% времени бд при выполнении запроса SQL_ID «6drhaqddkx6xd».
RATIONALE: Ожидание «gc buffer busy» в классе «Cluster» занимает 2%времени бд при выполнении запроса SQL_ID «6drhaqddkx6xd».

Причину появления нагрузки (ошибка в частоте запуска модуля) удалось локализовать и устранить быстро, долгоиграющие сессии принудительно завершены, теперь интересно разобраться с событиями ожидания и проблемным запросом.

Анализ

Исходя из ожидания «latch: cache buffers chains» можно попробовать идентифицировать высококонкурентные блоки буферного кэша (hot blocks), но в нашем случае проблемный запрос известен и можно попытаться выяснить причину, анализируя запрос. Поскольку запрос уже не выполняется (а выполнять столь долгий запрос заново для сбора актульной статистики выполнения желания нет), воспользуемся историческими обзорами (active session history) для анализа потребления ресурсов нашим запросом:

select hsqls.snap_id as ID,
hsqls.sql_id as SQL,
hsqls.optimizer_cost as COST,
hsqls.sorts_total as SORTS,
hsqls.executions_total as EXE,
hsqls.parse_calls_total as PARSES,
hsqls.disk_reads_total as DISK_READS,
hsqls.buffer_gets_total as BUFFERS,
hsqls.rows_processed_total as ROWS_,
hsqls.cpu_time_total as CPU_TIME,
hsqls.iowait_total as IOWAITS,
hsqls.apwait_total as APWAITS,
hsqls.ccwait_total as CCWAITS
from dba_hist_sqlstat hsqls
where sql_id = '6drhaqddkx6xd'
order by 1 desc
ID  SQL   COST  SORTS   EXE  PARSES  DISK_READS  BUFFERS    ROWS_  CPU_TIME     IOWAITS    CCWAITS
3   6d... 4246  268971  10   10      16304       610702825  0      28937917189  126721699  3109468679
4   6d... 4246  280638  13   13      30236       877045800  0      30900909000  517409083  6201363978

При небольших стоимости (COST=4246) и количестве вызовов (10 и 13 в час) запрос сделал сотни тысяч сортировок, истратил много процессорного времени (~ 2800 секунд на выполнение), потратил много времени на ожидания класса «Concurrency» (столбец CCWAITS) и т.д. и при этом не выбрал ни одной строчки (столбец dba_hist_sqlstat.rows_processed_total) !

SQL> select sql_id,
max(buffer_gets_total/decode(executions_total,0,1,executions_total)) as BUFFERS
from dba_hist_sqlstat where sql_id = '6drhaqddkx6xd'
group by sql_id order by 2 desc;
SQL_ID           BUFFERS
6drhaqddkx6xd    372269886

На каждое выпонение запрос считывал до 372,269,886 буферных блоков кэша по 4 кБ каждый! При таком потреблении ресурсов понятно, откуда при одновременном выполнении нескольких таких запросов появляется конкуренция за списки и блоки буферного кэша (соответствующие события ожидания latch: cache buffers chains, gc current block 2-way, gc buffer busy).

Воспользуемся пакетом DBMS_SQLTUNE для автоматической оптимизации нашего запроса (подготовки SQL Profile):

DECLARE
my_sql_id VARCHAR2(30) := '6drhaqddkx6xd';
my_task_name VARCHAR2(30);
BEGIN
begin
DBMS_SQLTUNE.DROP_TUNING_TASK(my_sql_id);
exception when others then NULL;
end;
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
 sql_id => my_sql_id,
 scope => 'COMPREHENSIVE',
 time_limit  => 600,
 task_name   => my_sql_id,
 description => 'SQL analysis for SQL_ID='||my_sql_id);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => my_task_name);
END;
/
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( '6drhaqddkx6xd') FROM DUAL;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : 6drhaqddkx6xd
Tuning Task Owner                 : TEST
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 600
Completion Status                 : COMPLETED
Started at                        : 11/19/2009 14:36:57
Completed at                      : 11/19/2009 14:37:08
-------------------------------------------------------------------------------
 Schema Name: TEST
 SQL ID     : 6drhaqddkx6xd
 SQL Text   : SELECT O.ID as OPTION_ID, I.OPTION_COUNT, 	DECODE(O.BASKET_NAME,
...
-------------------------------------------------------------------------------
 ADDITIONAL INFORMATION SECTION
 -------------------------------------------------------------------------------
 - The optimizer could not merge the view at line ID 30 of the execution plan.
 The optimizer cannot merge a view that contains an "ORDER BY" clause unless
 the statement is a "DELETE" or an "UPDATE" and the parent query is the top
 most query in the statement.
 - The optimizer could not merge the view at line ID 28 of the execution plan.
 The optimizer cannot merge a view that contains a "ROWNUM" pseudo column.
-------------------------------------------------------------------------------
 EXPLAIN PLANS SECTION
 -------------------------------------------------------------------------------
1- Original
 -----------
 Plan hash value: 981459234
----------------------------------------------------------------------------------------------------------------
 | Id  | Operation                                | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
 ----------------------------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT                         |                     |     1 |   373 |  4245   (1)| 00:00:51 |
 |   1 |  SORT ORDER BY                           |                     |     1 |   373 |  4245   (1)| 00:00:51 |
 |*  2 |   FILTER                                 |                     |       |       |            |          |
 |   3 |    NESTED LOOPS OUTER                    |                     |     1 |   373 |  4244   (1)| 00:00:51 |
 |   4 |     NESTED LOOPS OUTER                   |                     |     1 |   352 |  4242   (1)| 00:00:51 |
 |   5 |      NESTED LOOPS                        |                     |     1 |   334 |  3451   (1)| 00:00:42 |
 |   6 |       NESTED LOOPS                       |                     |     1 |   191 |  3447   (1)| 00:00:42 |
 |   7 |        NESTED LOOPS                      |                     |    20 |  3040 |  3407   (1)| 00:00:41 |
 |   8 |         NESTED LOOPS                     |                     |    20 |  2820 |  3367   (1)| 00:00:41 |
 |   9 |          NESTED LOOPS                    |                     |    64 |  7040 |  3239   (1)| 00:00:39 |
 |  10 |           NESTED LOOPS                   |                     |  1069 | 98348 |  1100   (1)| 00:00:14 |
 |  11 |            VIEW                          | VW_SQ_1             |  1069 |  5345 |    30   (0)| 00:00:01 |
 |  12 |             HASH UNIQUE                  |                     |  1069 | 28863 |            |          |
 |  13 |              NESTED LOOPS                |                     |  1069 | 28863 |    30   (0)| 00:00:01 |
 |* 14 |               TABLE ACCESS BY INDEX ROWID| CATALOG             |    11 |   209 |     8   (0)| 00:00:01 |
 |* 15 |                INDEX RANGE SCAN          | IDX$$_10DD10001     |    22 |       |     1   (0)| 00:00:01 |
 |* 16 |               INDEX RANGE SCAN           | IX_CATALOG_GOODS    |    99 |   792 |     2   (0)| 00:00:01 |
 |  17 |            TABLE ACCESS BY INDEX ROWID   | PRODUCT             |     1 |    87 |     1   (0)| 00:00:01 |
 |* 18 |             INDEX UNIQUE SCAN            | PK_PRODUCT          |     1 |       |     0   (0)| 00:00:01 |
 |* 19 |           TABLE ACCESS BY INDEX ROWID    | PRODUCT_LANG        |     1 |    18 |     2   (0)| 00:00:01 |
 |* 20 |            INDEX UNIQUE SCAN             | UX_PRODUCT_LANG     |     1 |       |     1   (0)| 00:00:01 |
 |* 21 |          TABLE ACCESS BY INDEX ROWID     | ITEM                |     1 |    31 |     2   (0)| 00:00:01 |
 |* 22 |           INDEX RANGE SCAN               | IX_ITEM_PRODUCT     |     1 |       |     1   (0)| 00:00:01 |
 |* 23 |         INDEX RANGE SCAN                 | COMPANY_IX1         |     1 |    11 |     2   (0)| 00:00:01 |
 |* 24 |        TABLE ACCESS BY INDEX ROWID       | COMPANY_LANG        |     1 |    39 |     2   (0)| 00:00:01 |
 |* 25 |         INDEX UNIQUE SCAN                | IX_COMPANY_LANG     |     1 |       |     1   (0)| 00:00:01 |
 |* 26 |       TABLE ACCESS BY INDEX ROWID        | OPTION              |     1 |   143 |     4   (0)| 00:00:01 |
 |* 27 |        INDEX RANGE SCAN                  | IX_OPTION           |     3 |       |     2   (0)| 00:00:01 |
 |* 28 |      VIEW                                |                     |     1 |    18 |   791   (1)| 00:00:10 |
 |  29 |       COUNT                              |                     |       |       |            |          |
 |  30 |        VIEW                              |                     |     1 |    13 |   791   (1)| 00:00:10 |
 |  31 |         SORT ORDER BY                    |                     |     1 |   107 |   791   (1)| 00:00:10 |
 |  32 |          SORT GROUP BY                   |                     |     1 |   107 |   791   (1)| 00:00:10 |
 |  33 |           NESTED LOOPS                   |                     |     1 |   107 |   789   (1)| 00:00:10 |
 |  34 |            NESTED LOOPS                  |                     |    15 |  1020 |   759   (1)| 00:00:10 |
 |  35 |             NESTED LOOPS                 |                     |    15 |   855 |   729   (1)| 00:00:09 |
 |  36 |              NESTED LOOPS                |                     |   124 |  4836 |   481   (1)| 00:00:06 |
 |* 37 |               INDEX FAST FULL SCAN       | SYS_IOT_TOP_77211   |   124 |  3224 |   357   (1)| 00:00:05 |
 |* 38 |               TABLE ACCESS BY INDEX ROWID| PRODUCT             |     1 |    13 |     1   (0)| 00:00:01 |
 |* 39 |                INDEX UNIQUE SCAN         | PK_PRODUCT          |     1 |       |     0   (0)| 00:00:01 |
 |* 40 |              TABLE ACCESS BY INDEX ROWID | PRODUCT_LANG        |     1 |    18 |     2   (0)| 00:00:01 |
 |* 41 |               INDEX UNIQUE SCAN          | UX_PRODUCT_LANG     |     1 |       |     1   (0)| 00:00:01 |
 |* 42 |             INDEX RANGE SCAN             | COMPANY_IX1         |     1 |    11 |     2   (0)| 00:00:01 |
 |* 43 |            TABLE ACCESS BY INDEX ROWID   | COMPANY_LANG        |     1 |    39 |     2   (0)| 00:00:01 |
 |* 44 |             INDEX UNIQUE SCAN            | IX_COMPANY_LANG     |     1 |       |     1   (0)| 00:00:01 |
 |  45 |     TABLE ACCESS BY INDEX ROWID          | PRICE               |     1 |    21 |     2   (0)| 00:00:01 |
 |* 46 |      INDEX UNIQUE SCAN                   | PK_PRICE            |     1 |       |     1   (0)| 00:00:01 |
 ----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
 ---------------------------------------------------
 2 - filter("PR"."ID" IS NOT NULL OR "O"."BUNDLE_TYPE"=:SYS_B_28 AND "P"."BUNDLE"=:SYS_B_29)
 14 - filter("CT"."GLOBAL_VISIBLE"=:SYS_B_26)
 15 - access("CT"."LID"=:SYS_B_27 AND "CT"."COMPANY_ID"=:SYS_B_25)
 16 - access("CP"."CATALOG_ID"="CT"."ID")
 18 - access("PRODUCT_ID"="P"."ID")
...

Автоматизировать процесс оптимизации запроса (с помощью SQL Profile), увы, не получилось — запрос действительно сложный и не проходит ряд ограничений пакета DBMS_SQLTUNE версии 10.2.0.4 — см. ADDITIONAL INFORMATION SECTION выше в выводе пакета DBMS_SQLTUNE.

Для дальнейшего анализа восстановим значения связанных переменных из обзора V$SQL_BIND_CAPTURE ( или исторического DBA_HIST_SQLBIND):

select c.LAST_CAPTURED, c.child_address, c.address, c.name, c.DATATYPE_STRING,
DECODE(c.WAS_CAPTURED, 'YES', c.value_string, '['||c.value_string||']:NOT_CAPTURED') bind_var
from v$sql a,
v$sql_bind_capture c
where c.child_address=a.child_address
and c.hash_value = a.HASH_VALUE
and a.SQL_ID = '6drhaqddkx6xd'
order by 1 desc nulls last, 4 desc;

и получим текст запроса со значениями связанных переменных:

SELECT O.ID as OPTION_ID,
I.OPTION_COUNT,
DECODE(O.BASKET_NAME,'ru',P.NAME||DECODE(I.NAME,'ru','ru','ru')||I.NAME||DECODE(O.NAME,'ru','ru','ru')||O.NAME,
O.BASKET_NAME) AS PRODUCT_NAME,
I.ID AS ITEM_ID,
P.ITEM_COUNT,
P.ID AS PRODUCT_ID,
P.BUNDLE,
O.BUNDLE_TYPE,
PR.CURRENCY,
PR.INTERVAL_BEGIN as QUANTITY,
O.MATERIAL_DELIVERY,
O.DELIVERY_PERIOD,
CL.NAME as COMPANY_NAME,
P.COMPANY_ID,
O.DOWNLOAD_URL,
RATING_PRG.I AS PRG_ROWNUM
FROM COMPANY CM,
PRODUCT P,
OPTION O,
ITEM I,
PRICE PR,
COMPANY_LANG CL,
PRODUCT_LANG PL,
(SELECT PRODUCT_ID, ROWNUM AS I
FROM (SELECT P.ID AS PRODUCT_ID, SUM(R.ORDER_CNT) AS CNT
FROM COMPANY      C,
PRODUCT      P,
RATING       R,
COMPANY_LANG CL,
PRODUCT_LANG PL
WHERE C.ID = P.COMPANY_ID
AND PL.LID = 'ru'
AND R.LID = 'ru'
AND PL.PRODUCT_ID = P.ID
AND PL.ACTIVE = 'Y'
AND PL.VISIBLE = 'Y'
AND CL.AUTHOR = 'Y'
AND C.ACTIVE = 'Y'
AND P.IN_RATING = 'Y'
AND P.ID = R.PRODUCT_ID
AND C.ID = CL.COMPANY_ID
AND CL.LID = PL.LID
AND R.DATE_INSERT > TRUNC(SYSDATE - 30)
GROUP BY P.ID, CL.COMPANY_ID, CL.NAME
ORDER BY CNT DESC)) RATING_PRG
WHERE CL.AUTHOR = 'Y'
AND CL.LID = 'ru'
AND CM.ID = P.COMPANY_ID
AND CM.ACTIVE = 'Y'
AND PL.PRODUCT_ID = P.ID
AND CL.COMPANY_ID = CM.ID
AND CL.LID = PL.LID
AND O.ITEM_ID = I.ID
AND I.PRODUCT_ID = P.ID
AND O.ACTIVE = 'Y'
AND O.VISIBLE = 'Y'
AND I.ACTIVE = 'Y'
AND I.VISIBLE = 'Y'
AND P.ID = RATING_PRG.PRODUCT_ID(+)
AND PL.ACTIVE = 'Y'
AND PL.VISIBLE = 'Y'
AND EXISTS (SELECT 'Y'
FROM CATALOG_PRODUCT CP, CATALOG CT
WHERE CP.PRODUCT_ID = P.ID
AND CP.CATALOG_ID = CT.ID
AND CT.COMPANY_ID = 1
AND CT.GLOBAL_VISIBLE = 'Y'
AND CT.LID = 'ru')
AND PR.ID(+) = GET_MIN_PRICE_ID(O.ID)
AND (PR.ID IS NOT NULL OR (O.BUNDLE_TYPE = 'D' AND
P.BUNDLE = 'Y'))
ORDER BY PRODUCT_NAME

Из вышеприведённого плана выполнения видно, что запрос можно поделить на две части: основной запрос (строки 1-27 и 45-46) и «встроенный обзор» во фразе FROM (inline view, строки 28-44 плана выполнения), при этом «встроенный обзор» выполняется один раз, не зависит и не меняет план своего выполнения (механизм join predicate push-down не работает для обзоров с GROUP BY в версии 10.2) в зависимости от условий основного запроса (query predicates) и, как легко проверить, выполняется относительно быстро с небольшими затратами ресурсов:

SQL> SELECT PRODUCT_ID, ROWNUM AS I
  2  FROM (SELECT P.ID AS PRODUCT_ID, SUM(R.ORDER_CNT) AS CNT
  3  FROM COMPANY      C,
  4  PRODUCT      P,
  5  RATING       R,
  6  COMPANY_LANG CL,
  7  PRODUCT_LANG PL
  8  WHERE C.ID = P.COMPANY_ID
  9  AND PL.LID = 'ru'
 10  AND R.LID = 'ru'
 11  AND PL.PRODUCT_ID = P.ID
 12  AND PL.ACTIVE = 'Y'
 13  AND PL.VISIBLE = 'Y'
 14  AND CL.AUTHOR = 'Y'
 15  AND C.ACTIVE = 'Y'
 16  AND P.IN_RATING = 'Y'
 17  AND P.ID = R.PRODUCT_ID
 18  AND C.ID = CL.COMPANY_ID
 19  AND CL.LID = PL.LID
 20  AND R.DATE_INSERT > TRUNC(SYSDATE - 30)
 21  GROUP BY P.ID, CL.COMPANY_ID, CL.NAME
 22  ORDER BY CNT DESC)
/
2333 rows selected.
Elapsed: 00:00:00.33
Execution Plan
------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |     1 |    13 |   810 |
|   1 |  COUNT                              |                    |       |       |       |
|   2 |   VIEW                              |                    |     1 |    13 |   810 |
|   3 |    SORT ORDER BY                    |                    |     1 |   107 |   810 |
|   4 |     HASH GROUP BY                   |                    |     1 |   107 |   810 |
|   5 |      NESTED LOOPS                   |                    |     1 |   107 |   808 |
|   6 |       NESTED LOOPS                  |                    |    15 |  1020 |   778 |
|   7 |        NESTED LOOPS                 |                    |    15 |   855 |   748 |
|   8 |         NESTED LOOPS                |                    |   124 |  4836 |   500 |
|   9 |          INDEX FAST FULL SCAN       | SYS_IOT_TOP_77211  |   124 |  3224 |   376 |
|  10 |          TABLE ACCESS BY INDEX ROWID|  PRODUCT           |     1 |    13 |     1 |
|  11 |           INDEX UNIQUE SCAN         | PK_PRODUCT         |     1 |       |     0 |
|  12 |         TABLE ACCESS BY INDEX ROWID | PRODUCT_LANG       |     1 |    18 |     2 |
|  13 |          INDEX UNIQUE SCAN          | UX_PRODUCT_LANG    |     1 |       |     1 |
|  14 |        INDEX RANGE SCAN             | COMPANY_IX1        |     1 |    11 |     2 |
|  15 |       TABLE ACCESS BY INDEX ROWID   | COMPANY_LANG       |     1 |    39 |     2 |
|  16 |        INDEX UNIQUE SCAN            | IX_COMPANY_LANG    |     1 |       |     1 |
------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
...
      36484  consistent gets
...
          1  sorts (memory)
          0  sorts (disk)
       2333  rows processed

Следовательно, проблема в плане выполнения основного запроса (который можно видеть выше в выводе пакета DBMS_SQLTUNE), в соответствии с которым в первую очередь выполняется подзапрос из фразы EXISTS для соединения с таблицей PRODUCT по уникальному ключу P.ID, пребразованный к следующему виду:

AND P.ID IN
(SELECT DISTINCT CP.PRODUCT_ID
FROM CATALOG_PRODUCT CP, CATALOG CT
WHERE CP.CATALOG_ID = CT.ID
AND CT.COMPANY_ID = 1
AND CT.GLOBAL_VISIBLE = 'Y'
AND CT.LID = 'ru')

(на использование DISTINCT в преобразованном запросе указывает операция HASH UNIQUE на 12 шаге плана).

По оценке оптимизатора этот подзапрос вернёт 1069 строк (выделеные синим цветом шаги 12-16, столбец Rows плана выполнения). Правильность этой оценки легко проверить:

SQL> SELECT count(distinct CP.PRODUCT_ID)
2  FROM CATALOG_PRODUCT CP, CATALOG CT
3  WHERE CP.CATALOG_ID = CT.ID
4  AND CT.COMPANY_ID = 1
5  AND CT.GLOBAL_VISIBLE = 'Y'
6  AND CT.LID = 'ru'
7  /
COUNT(DISTINCTCP.PRODUCT_ID)
----------------------------
52044

Оптимизатор заметно ошибся в оценке количества строк подзапроса (cardinality) и, как следствие, был выбран неверный порядок соединения таблиц в плане выполнения запроса. Чтобы понять почему, посмотрим какие данные имел оптимизатор для расчётов:

SQL> select * from user_tab_col_statistics
2  where table_name = 'CATALOG'
3  and column_name in ('COMPANY_ID','GLOBAL_VISIBLE','LID')
4  /
TABLE_NAME COLUMN_NAME    NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY  NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE GLOBAL_STATS USER_STATS AVG_COL_LEN HISTOGRAM
CATALOG    LID            18           6267      7561       0,055..  0         1           Вчера ночью   5854        YES          NO         9           NONE
CATALOG    GLOBAL_VISIBLE 2            4E        59         0,5      0         1           Вчера ночью   5854        YES          NO         2           NONE
CATALOG    COMPANY_ID     15           C102      C3574B60   0,06..   0         1           Вчера ночью   5854        YES          NO         4           NONE

SQL> select count(*) from CATALOG;
  COUNT(*)
----------
      5854

SQL> select count(*) from CATALOG_PRODUCT;
  COUNT(*)
----------
    455940

SQL> select count(distinct CATALOG_ID) from CATALOG_PRODUCT;
COUNT(DISTINCTCATALOG_ID)
-------------------------
                     4550

Статистика свежая, но отсутствуют гистограммы распределения значений для столбцов (и к сожалению в версии 10.2 ещё недоступна процедура dbms_stats.create_extended_stats для сбора корелированной статистики для значений группы столбцов). Т.е. оптимизатор имеет для расчётов избирательности условий подзапроса (predicate selectivity) самую простую входную информацию: минимальные / максимальные значения, количество неповторяющихся и NULL значений столбцов, количество строк в таблицах — из чего можно построить несложную формулу для оценки количества строк (cardinality), возвращаемых подзапросом:

subquery_cardinality = ( CATALOG_row_count / CATALOG.LID_num_distinct / CATALOG.COMPANY_ID_num_distinct / CATALOG.GLOBAL_VISIBLE_num_distinct ) * ( CATALOG_PRODUCT_row_count / CATALOG_PRODUCT_distinct_row_count ) = ( 5854 / (18 * 2 * 15 ) ) * ( 455940 / 4550 ) = 1086.3

Расчёт (~1086 строки) незначительно отличается от расчётов оптимизатора (1069 строк), полагаю из-за того, что выполнение проблемного запроса и статистику таблиц разделяет некоторое время (система боевая и запускать заново проблемный запрос необходимости пока нет). Принципиально понятно, что оптимизатору для более точных расчётов и построения оптимального (быстрого) плана не хватает данных о распределении значений в столбцах таблиц (включая данные о кореляции значений между столбцами), а значения в столбцах, как видно из сравнения оценки оптимизатора и результатов реального запроса, распределены очень неравномерно (skewed columns).

Варианты

Для оптимизации запроса можно построить гистограммы распределения значений столбцов, но для данной конфигурации и версии (OLTP, Oracle 10.2.0.4), это может быть спорным решением, вызывающим, например проблему High Version Count with CURSOR_SHARING = SIMILAR or FORCE [ID 261020.1].

Можно попытаться подобрать оптимальный план выполнения и сделать его стабильным с использованием stored outlines и/или подсказок (hints). Мне такой подход в принципе не нравится, поскольку объекты в бд постоянно меняются и может легко получится так, что тщательно подготовленный сегодня «рукотворный» план, может оказаться неоптимальным уже через месяц. Кроме того, в рассматриваемой системе отчётные запросы формируются «динамически» PHP-скриптами в зависимости от входных условий, при этом «на лету» формируется всё — от списка таблиц в списке FROM до списка запрашиваемых полей и условий запроса (включая при необходимости конструкции типа IN (SELECT…, AND EXISTS… и т.д.) — т.е. вариантов запросов сотни и тысячи.

Красивым вариантом решения представляется указать оптимизатору во время выполнения оценивать избирательность условий с помощью механизма dynamic sampling с помощью соответствующей подсказки. Этот механизм не будет фиксировать план выполнения, который будет по-прежнему строится CBO, но на основе более точных данных о распределении значений столбцов в таблицах.

Опыты

Прежде чем тестировать запрос, проверим с помощью команды Explain Plan, как подсказка повлияет на план выпонения запроса. Начнём со значения degree_of_sampling = 3 (Oracle будет предварительно считывать 96 блоков каждой таблицы из блока Q1 для анализа):

SQL> explain plan for
2  SELECT /*+ DYNAMIC_SAMPLING(@Q1 3) gather_plan_statistics*/ O.ID as OPTION_ID,
...
63  AND EXISTS (SELECT /*+ qb_name(Q1)*/ 'Y'
64  FROM CATALOG_PRODUCT CP, CATALOG CT
...
/
Explained.

SQL> select * from table(dbms_xplan.display);
------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                     |     1 |   373 |  4321 |
|   1 |  SORT ORDER BY                           |                     |     1 |   373 |  4321 |
|   2 |   FILTER                                 |                     |       |       |       |
|   3 |    NESTED LOOPS OUTER                    |                     |     1 |   373 |  4320 |
|   4 |     NESTED LOOPS OUTER                   |                     |     1 |   352 |  4318 |
|   5 |      NESTED LOOPS                        |                     |     1 |   334 |  3508 |
|   6 |       NESTED LOOPS                       |                     |     1 |   191 |  3504 |
|   7 |        NESTED LOOPS                      |                     |    21 |  3192 |  3462 |
|   8 |         NESTED LOOPS                     |                     |    21 |  2961 |  3420 |
|   9 |          NESTED LOOPS                    |                     |    65 |  7150 |  3290 |
|  10 |           NESTED LOOPS                   |                     |  1086 | 99912 |  1117 |
|  11 |            VIEW                          | VW_SQ_1             |  1086 |  5430 |    30 |
|  12 |             HASH UNIQUE                  |                     |  1086 | 29322 |       |
|  13 |              NESTED LOOPS                |                     |  1086 | 29322 |    30 |
|  14 |               TABLE ACCESS BY INDEX ROWID| CATALOG             |    11 |   209 |     8 |
|  15 |                INDEX RANGE SCAN          | IDX$$_10DD10001     |    22 |       |     1 |
|  16 |               INDEX RANGE SCAN           | IX_CATALOG_GOODS    |   100 |   800 |     2 |
|  17 |            TABLE ACCESS BY INDEX ROWID   | PRODUCT             |     1 |    87 |     1 |
|  18 |             INDEX UNIQUE SCAN            | PK_PRODUCT          |     1 |       |     0 |
|  19 |           TABLE ACCESS BY INDEX ROWID    | PRODUCT_LANG        |     1 |    18 |     2 |
|  20 |            INDEX UNIQUE SCAN             | UX_PRODUCT_LANG     |     1 |       |     1 |
|  21 |          TABLE ACCESS BY INDEX ROWID     | ITEM                |     1 |    31 |     2 |
|  22 |           INDEX RANGE SCAN               | IX_ITEM_PRODUCT     |     1 |       |     1 |
|  23 |         INDEX RANGE SCAN                 | COMPANY_IX1         |     1 |    11 |     2 |
|  24 |        TABLE ACCESS BY INDEX ROWID       | COMPANY_LANG        |     1 |    39 |     2 |
|  25 |         INDEX UNIQUE SCAN                | IX_COMPANY_LANG     |     1 |       |     1 |
|  26 |       TABLE ACCESS BY INDEX ROWID        | OPTION              |     1 |   143 |     4 |
|  27 |        INDEX RANGE SCAN                  | IX_OPTION           |     3 |       |     2 |
|  28 |      VIEW                                |                     |     1 |    18 |   810 |
|  29 |       COUNT                              |                     |       |       |       |
|  30 |        VIEW                              |                     |     1 |    13 |   810 |
|  31 |         SORT ORDER BY                    |                     |     1 |   107 |   810 |
|  32 |          SORT GROUP BY                   |                     |     1 |   107 |   810 |
|  33 |           NESTED LOOPS                   |                     |     1 |   107 |   808 |
|  34 |            NESTED LOOPS                  |                     |    15 |  1020 |   778 |
|  35 |             NESTED LOOPS                 |                     |    15 |   855 |   748 |
|  36 |              NESTED LOOPS                |                     |   124 |  4836 |   500 |
|  37 |               INDEX FAST FULL SCAN       | SYS_IOT_TOP_77211   |   124 |  3224 |   376 |
|  38 |               TABLE ACCESS BY INDEX ROWID| PRODUCT             |     1 |    13 |     1 |
|  39 |                INDEX UNIQUE SCAN         | PK_PRODUCT          |     1 |       |     0 |
|  40 |              TABLE ACCESS BY INDEX ROWID | PRODUCT_LANG        |     1 |    18 |     2 |
|  41 |               INDEX UNIQUE SCAN          | UX_PRODUCT_LANG     |     1 |       |     1 |
|  42 |             INDEX RANGE SCAN             | COMPANY_IX1         |     1 |    11 |     2 |
|  43 |            TABLE ACCESS BY INDEX ROWID   | COMPANY_LANG        |     1 |    39 |     2 |
|  44 |             INDEX UNIQUE SCAN            | IX_COMPANY_LANG     |     1 |       |     1 |
|  45 |     TABLE ACCESS BY INDEX ROWID          | PRICE               |     1 |    21 |     2 |
|  46 |      INDEX UNIQUE SCAN                   | PK_PRICE            |     1 |       |     1 |
------------------------------------------------------------------------------------------------

План не изменился в части порядка выполнения (заметим кстати, что теперь оценка оптимизатором [избирательности подзапроса EXISTS] совпадает с нашими расчётами — 1086). Пробуем degree_of_sampling = 4 (128 блоков):

SQL> explain plan for
2  SELECT /*+ DYNAMIC_SAMPLING(@Q1 4) gather_plan_statistics*/ O.ID as OPTION_ID,
...
63  AND EXISTS (SELECT /*+ qb_name(Q1)*/ 'Y'
64  FROM CATALOG_PRODUCT CP, CATALOG CT
...
73  /
Explained.

SQL> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                 | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                      |    38 | 13984 | 13077 |
|   1 |  SORT ORDER BY                            |                      |    38 | 13984 | 13077 |
|   2 |   FILTER                                  |                      |       |       |       |
|   3 |    FILTER                                 |                      |       |       |       |
|   4 |     NESTED LOOPS OUTER                    |                      |    56 | 20608 | 13068 |
|   5 |      NESTED LOOPS                         |                      |    56 | 19432 | 12956 |
|   6 |       HASH JOIN OUTER                     |                      |    23 |  4692 | 12864 |
|   7 |        TABLE ACCESS BY INDEX ROWID        | ITEM                 |     1 |    31 |     2 |
|   8 |         NESTED LOOPS                      |                      |    23 |  4278 |  6471 |
|   9 |          NESTED LOOPS                     |                      |    19 |  2945 |  6433 |
|  10 |           NESTED LOOPS                    |                      |    49 |  6713 |  6335 |
|  11 |            NESTED LOOPS                   |                      |   578 | 28900 |  5735 |
|  12 |             TABLE ACCESS FULL             | COMPANY_LANG         |   578 | 22542 |  4579 |
|  13 |             INDEX RANGE SCAN              | COMPANY_IX1          |     1 |    11 |     2 |
|  14 |            TABLE ACCESS BY INDEX ROWID    | PRODUCT              |     1 |    87 |    17 |
|  15 |             INDEX RANGE SCAN              | IX_PRODUCT           |    19 |       |     1 |
|  16 |           TABLE ACCESS BY INDEX ROWID     | PRODUCT_LANG         |     1 |    18 |     2 |
|  17 |            INDEX UNIQUE SCAN              | UX_PRODUCT_LANG      |     1 |       |     1 |
|  18 |          INDEX RANGE SCAN                 | IX_ITEM_PRODUCT      |     1 |       |     1 |
|  19 |        VIEW                               |                      |     2 |    36 |  6393 |
|  20 |         COUNT                             |                      |       |       |       |
|  21 |          VIEW                             |                      |     2 |    10 |  6393 |
|  22 |           SORT ORDER BY                   |                      |     2 |   214 |  6393 |
|  23 |            HASH GROUP BY                  |                      |     2 |   214 |  6393 |
|  24 |             NESTED LOOPS                  |                      |     2 |   214 |  6391 |
|  25 |              NESTED LOOPS                 |                      |     3 |   267 |  6385 |
|  26 |               NESTED LOOPS                |                      |    25 |  1575 |  6335 |
|  27 |                NESTED LOOPS               |                      |   578 | 28900 |  5735 |
|  28 |                 TABLE ACCESS FULL         | COMPANY_LANG         |   578 | 22542 |  4579 |
|  29 |                 INDEX RANGE SCAN          | COMPANY_IX1          |     1 |    11 |     2 |
|  30 |                TABLE ACCESS BY INDEX ROWID| PRODUCT              |     1 |    13 |    17 |
|  31 |                 INDEX RANGE SCAN          | IX_PRODUCT           |    19 |       |     1 |
|  32 |               INDEX RANGE SCAN            | SYS_IOT_TOP_77211    |     1 |    26 |     2 |
|  33 |              TABLE ACCESS BY INDEX ROWID  | PRODUCT_LANG         |     1 |    18 |     2 |
|  34 |               INDEX UNIQUE SCAN           | UX_PRODUCT_LANG      |     1 |       |     1 |
|  35 |       TABLE ACCESS BY INDEX ROWID         | OPTION               |     2 |   286 |     4 |
|  36 |        INDEX RANGE SCAN                   | IX_OPTION            |     3 |       |     2 |
|  37 |      TABLE ACCESS BY INDEX ROWID          | PRICE                |     1 |    21 |     2 |
|  38 |       INDEX UNIQUE SCAN                   | PK_PRICE             |     1 |       |     1 |
|  39 |    NESTED LOOPS                           |                      |     1 |    27 |     4 |
|  40 |     INDEX RANGE SCAN                      | IX_CATALOG_PRODUCT_2 |     7 |    56 |     3 |
|  41 |     TABLE ACCESS BY INDEX ROWID           | CATALOG              |     1 |    19 |     1 |
|  42 |      INDEX UNIQUE SCAN                    | PK_CATALOG           |     1 |       |     0 |
--------------------------------------------------------------------------------------------------

План изменился, и теперь блок запроса Q1 (AND EXISTS…) перемещен на последние шаги плана 39-42. Проверим выполнение запроса:

SQL> set autotrace traceonly exp stat timi on
SQL> SELECT /*+ DYNAMIC_SAMPLING(@Q1 4) gather_plan_statistics*/ O.ID as OPTION_ID,
...
61   62  AND EXISTS (SELECT /*+ qb_name(Q1)*/ 'Y'
63  FROM CATALOG_PRODUCT CP, CATALOG CT
...
72  /

68776 rows selected.
Elapsed: 00:00:18.87

Statistics
----------------------------------------------------------
206663  recursive calls
0  db block gets
1134320  consistent gets
1335  physical reads
0  redo size
6573162  bytes sent via SQL*Net to client
50978  bytes received via SQL*Net from client
4587  SQL*Net roundtrips to/from client
69013  sorts (memory)
0  sorts (disk)
68776  rows processed

Очень неплохой результат, который (наверное) можно улучшить, используя механизм dynamic sampling для других таблиц запроса и подбирая оптимальное значение degree_of_sampling.

Интересно проверить статистику выполнения плана, собранную с помощью подсказки gather_plan_statistics:

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor( 'my_sql_id',null,'ALL IOSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT /*+ DYNAMIC_SAMPLING(@Q1 4) gather_plan_statistics*/ O.ID as OPTION_ID, ...
Plan hash value: 1389249843
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY                          |                     |      1 |     52 | 19396 | 16378   (1)| 00:03:17 |  68776 |00:00:17.81 |    1134K|   1335 |
|*  2 |   FILTER                                |                     |      1 |        |       |            |          |  68776 |00:00:22.15 |    1134K|   1335 |
|   3 |    NESTED LOOPS OUTER                   |                     |      1 |     52 | 19396 | 16377   (1)| 00:03:17 |  69011 |00:00:16.69 |    1134K|   1335 |
|*  4 |     HASH JOIN RIGHT OUTER               |                     |      1 |     52 | 18304 | 16273   (1)| 00:03:16 |  69011 |00:00:04.54 |     646K|      3 |
|   5 |      VIEW                               |                     |      1 |      2 |    36 |  6393   (1)| 00:01:17 |   2333 |00:00:01.33 |     219K|      3 |
|   6 |       COUNT                             |                     |      1 |        |       |            |          |   2333 |00:00:01.33 |     219K|      3 |
|   7 |        VIEW                             |                     |      1 |      2 |    26 |  6393   (1)| 00:01:17 |   2333 |00:00:01.33 |     219K|      3 |
|   8 |         SORT ORDER BY                   |                     |      1 |      2 |   214 |  6393   (1)| 00:01:17 |   2333 |00:00:01.32 |     219K|      3 |
|   9 |          HASH GROUP BY                  |                     |      1 |      2 |   214 |  6393   (1)| 00:01:17 |   2333 |00:00:01.32 |     219K|      3 |
|  10 |           NESTED LOOPS                  |                     |      1 |      2 |   214 |  6391   (1)| 00:01:17 |   3129 |00:00:00.91 |     219K|      3 |
|  11 |            NESTED LOOPS                 |                     |      1 |      3 |   267 |  6385   (1)| 00:01:17 |   3215 |00:00:01.56 |     209K|      3 |
|  12 |             NESTED LOOPS                |                     |      1 |     25 |  1575 |  6335   (1)| 00:01:17 |  59529 |00:00:00.71 |   89864 |      3 |
|  13 |              NESTED LOOPS               |                     |      1 |    578 | 28900 |  5735   (1)| 00:01:09 |   2011 |00:00:00.13 |   24735 |      0 |
|* 14 |               TABLE ACCESS FULL         | COMPANY_LANG        |      1 |    578 | 22542 |  4579   (2)| 00:00:55 |   2018 |00:00:00.27 |   20691 |      0 |
|* 15 |               INDEX RANGE SCAN          | COMPANY_IX1         |   2018 |      1 |    11 |     2   (0)| 00:00:01 |   2011 |00:00:00.02 |    4044 |      0 |
|* 16 |              TABLE ACCESS BY INDEX ROWID| PRODUCT             |   2011 |      1 |    13 |    17   (0)| 00:00:01 |  59529 |00:00:00.47 |   65129 |      3 |
|* 17 |               INDEX RANGE SCAN          | IX_PRODUCT          |   2011 |     19 |       |     1   (0)| 00:00:01 |  60382 |00:00:00.09 |    2222 |      3 |
|* 18 |             INDEX RANGE SCAN            | SYS_IOT_TOP_77211   |  59529 |      1 |    26 |     2   (0)| 00:00:01 |   3215 |00:00:00.53 |     120K|      0 |
|* 19 |            TABLE ACCESS BY INDEX ROWID  | PRODUCT_LANG        |   3215 |      1 |    18 |     2   (0)| 00:00:01 |   3129 |00:00:00.05 |    9674 |      0 |
|* 20 |             INDEX UNIQUE SCAN           | UX_PRODUCT_LANG     |   3215 |      1 |       |     1   (0)| 00:00:01 |   3215 |00:00:00.02 |    6432 |      0 |
|* 21 |      TABLE ACCESS BY INDEX ROWID        | OPTION              |      1 |      2 |   286 |     4   (0)| 00:00:01 |  69011 |00:00:02.80 |     427K|      0 |
|  22 |       NESTED LOOPS                      |                     |      1 |     52 | 17368 |  9880   (1)| 00:01:59 |    106K|00:04:04.60 |     379K|      0 |
|* 23 |        HASH JOIN SEMI                   |                     |      1 |     21 |  4011 |  9796   (1)| 00:01:58 |  34831 |00:00:02.05 |     309K|      0 |
|* 24 |         TABLE ACCESS BY INDEX ROWID     | ITEM                |      1 |      1 |    31 |     2   (0)| 00:00:01 |  34977 |00:00:01.78 |     308K|      0 |
|  25 |          NESTED LOOPS                   |                     |      1 |     23 |  4278 |  6471   (1)| 00:01:18 |  66180 |00:00:01.06 |     289K|      0 |
|  26 |           NESTED LOOPS                  |                     |      1 |     19 |  2945 |  6433   (1)| 00:01:18 |  30982 |00:00:01.18 |     258K|      0 |
|  27 |            NESTED LOOPS                 |                     |      1 |     49 |  6713 |  6335   (1)| 00:01:17 |  60382 |00:00:01.09 |   89562 |      0 |
|  28 |             NESTED LOOPS                |                     |      1 |    578 | 28900 |  5735   (1)| 00:01:09 |   2011 |00:00:00.14 |   24735 |      0 |
|* 29 |              TABLE ACCESS FULL          | COMPANY_LANG        |      1 |    578 | 22542 |  4579   (2)| 00:00:55 |   2018 |00:00:00.28 |   20691 |      0 |
|* 30 |              INDEX RANGE SCAN           | COMPANY_IX1         |   2018 |      1 |    11 |     2   (0)| 00:00:01 |   2011 |00:00:00.02 |    4044 |      0 |
|  31 |             TABLE ACCESS BY INDEX ROWID | PRODUCT             |   2011 |      1 |    87 |    17   (0)| 00:00:01 |  60382 |00:00:00.57 |   64827 |      0 |
|* 32 |              INDEX RANGE SCAN           | IX_PRODUCT          |   2011 |     19 |       |     1   (0)| 00:00:01 |  60382 |00:00:00.07 |    2222 |      0 |
|* 33 |            TABLE ACCESS BY INDEX ROWID  | PRODUCT_LANG        |  60382 |      1 |    18 |     2   (0)| 00:00:01 |  30982 |00:00:00.74 |     168K|      0 |
|* 34 |             INDEX UNIQUE SCAN           | UX_PRODUCT_LANG     |  60382 |      1 |       |     1   (0)| 00:00:01 |  46969 |00:00:00.37 |     120K|      0 |
|* 35 |           INDEX RANGE SCAN              | IX_ITEM_PRODUCT     |  30982 |      1 |       |     1   (0)| 00:00:01 |  35197 |00:00:00.23 |   31084 |      0 |
|  36 |         VIEW                            | VW_SQ_1             |      1 |    165K|   805K|  3324   (1)| 00:00:40 |  94077 |00:00:00.38 |    1593 |      0 |
|  37 |          NESTED LOOPS                   |                     |      1 |    165K|  4350K|  3324   (1)| 00:00:40 |  94077 |00:00:00.28 |    1593 |      0 |
|* 38 |           TABLE ACCESS BY INDEX ROWID   | CATALOG             |      1 |   1648 | 31312 |    26   (0)| 00:00:01 |    579 |00:00:00.01 |     126 |      0 |
|* 39 |            INDEX RANGE SCAN             | IXX_CATALOG_LID     |      1 |    325 |       |     1   (0)| 00:00:01 |    894 |00:00:00.01 |       5 |      0 |
|* 40 |           INDEX RANGE SCAN              | IX_CATALOG_GOODS    |    579 |    100 |   800 |     2   (0)| 00:00:01 |  94077 |00:00:00.10 |    1467 |      0 |
|* 41 |        INDEX RANGE SCAN                 | IX_OPTION           |  34831 |      3 |       |     2   (0)| 00:00:01 |  71610 |00:00:00.37 |   69909 |      0 |
|  42 |     TABLE ACCESS BY INDEX ROWID         | PRICE               |  69011 |      1 |    21 |     2   (0)| 00:00:01 |  68641 |00:00:13.23 |     487K|   1332 |
|* 43 |      INDEX UNIQUE SCAN                  | PK_PRICE            |  69011 |      1 |       |     1   (0)| 00:00:01 |  68641 |00:00:12.73 |     418K|   1332 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
...
Predicate Information (identified by operation id):
---------------------------------------------------
...
29 - filter(("CL"."LID"=:SYS_B_16 AND "CL"."AUTHOR"=:SYS_B_15))
...
Note
-----
- dynamic sampling used for this statement

Важно отметить, что реальный план выполнения не совпадает с планом, сформированным EXPLAIN PLAN, т.е. команда EXPLAIN PLAN даёт только предварительный, черновой план выполнения.

Несмотря на значительное улучшение времени выполнения, расхождения по-прежнему очень заметные: по плану запрос должен вернуть 52 строки, реально возвращает 68776 строк! По статистике выполнения можно точно определить расхождения рассчитанного оптимизатором количества строк (Starts*E-Rows) с реально полученным в ходе выполнения количеством (A-Rows) по шагам выполнения: например, на первом шаге выполнения при полном сканировании таблицы COMPANY_LANG (синяя строка 29 плана) с условиями CL.AUTHOR = ‘Y’ AND CL.LID = ‘ru’ (синий фильтр для строки 29) оптимизатор ожидает получить 578 строк (E-Rows), а при выполнении получаем 2018 (A-Rows). Аналогично предыдущему шагу оптимизации, можно предположить, что значения в столбцах AUTHOR и LID таблиц COMPANY_LANG распределены неравномерно и проверить это, применив dynamic sampling к таблице COMPANY_LANG:

SQL> SELECT /*+ DYNAMIC_SAMPLING(@Q1 4) DYNAMIC_SAMPLING(CL 4) gather_plan_statistics*/ O.ID as OPTION_ID,
...
62  AND EXISTS (SELECT /*+ qb_name(Q1)*/ 'Y'
...
72  /
68795 rows selected.
Elapsed: 00:00:12.27
...
Statistics
----------------------------------------------------------
     207119  recursive calls
    1136313  consistent gets
...
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor( 'acbz69wfsg248',null,'ALL IOSTATS LAST'));
...
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY                          |                     |      1 |    169 | 63037 | 21601   (1)| 00:04:20 |  68795 |00:00:11.12 |    1134K|    423 |
|*  2 |   FILTER                                |                     |      1 |        |       |            |          |  68795 |00:00:11.47 |    1134K|    423 |
|   3 |    NESTED LOOPS OUTER                   |                     |      1 |    169 | 63037 | 21600   (1)| 00:04:20 |  69030 |00:00:10.53 |    1134K|    423 |
|*  4 |     HASH JOIN RIGHT OUTER               |                     |      1 |    169 | 59488 | 21262   (1)| 00:04:16 |  69030 |00:00:05.70 |     647K|    206 |
|   5 |      VIEW                               |                     |      1 |      1 |    18 |  6394   (1)| 00:01:17 |   2350 |00:00:01.55 |     219K|      1 |
|   6 |       COUNT                             |                     |      1 |        |       |            |          |   2350 |00:00:01.55 |     219K|      1 |
|   7 |        VIEW                             |                     |      1 |      1 |    13 |  6394   (1)| 00:01:17 |   2350 |00:00:01.54 |     219K|      1 |
|   8 |         SORT ORDER BY                   |                     |      1 |      1 |   107 |  6394   (1)| 00:01:17 |   2350 |00:00:01.54 |     219K|      1 |
|   9 |          HASH GROUP BY                  |                     |      1 |      1 |   107 |  6394   (1)| 00:01:17 |   2350 |00:00:01.54 |     219K|      1 |
|  10 |           NESTED LOOPS                  |                     |      1 |      1 |   107 |  6392   (1)| 00:01:17 |   3156 |00:00:01.57 |     219K|      1 |
|  11 |            NESTED LOOPS                 |                     |      1 |      2 |   178 |  6388   (1)| 00:01:17 |   3241 |00:00:00.87 |     209K|      1 |
|  12 |             NESTED LOOPS                |                     |      1 |     25 |  1575 |  6338   (1)| 00:01:17 |  59578 |00:00:01.01 |   89916 |      1 |
|  13 |              NESTED LOOPS               |                     |      1 |    579 | 28950 |  5738   (1)| 00:01:09 |   2014 |00:00:00.21 |   24742 |      0 |
|* 14 |               TABLE ACCESS FULL         | COMPANY_LANG        |      1 |    579 | 22581 |  4579   (2)| 00:00:55 |   2021 |00:00:00.36 |   20691 |      0 |
|* 15 |               INDEX RANGE SCAN          | COMPANY_IX1         |   2021 |      1 |    11 |     2   (0)| 00:00:01 |   2014 |00:00:00.03 |    4051 |      0 |
|* 16 |              TABLE ACCESS BY INDEX ROWID| PRODUCT             |   2014 |      1 |    13 |    17   (0)| 00:00:01 |  59578 |00:00:00.69 |   65174 |      1 |
|* 17 |               INDEX RANGE SCAN          | IX_PRODUCT          |   2014 |     19 |       |     1   (0)| 00:00:01 |  60431 |00:00:00.08 |    2225 |      1 |
|* 18 |             INDEX RANGE SCAN            | SYS_IOT_TOP_77211   |  59578 |      1 |    26 |     2   (0)| 00:00:01 |   3241 |00:00:00.52 |     119K|      0 |
|* 19 |            TABLE ACCESS BY INDEX ROWID  | PRODUCT_LANG        |   3241 |      1 |    18 |     2   (0)| 00:00:01 |   3156 |00:00:00.05 |    9754 |      0 |
|* 20 |             INDEX UNIQUE SCAN           | UX_PRODUCT_LANG     |   3241 |      1 |       |     1   (0)| 00:00:01 |   3241 |00:00:00.03 |    6484 |      0 |
|* 21 |      TABLE ACCESS BY INDEX ROWID        | OPTION              |      1 |      2 |   286 |     4   (0)| 00:00:01 |  69030 |00:00:03.74 |     427K|    205 |
|  22 |       NESTED LOOPS                      |                     |      1 |    169 | 56446 | 14868   (1)| 00:02:59 |    106K|00:06:04.15 |     380K|    205 |
|* 23 |        HASH JOIN SEMI                   |                     |      1 |     69 | 13179 | 14592   (1)| 00:02:56 |  34847 |00:00:03.02 |     310K|    197 |
|* 24 |         TABLE ACCESS BY INDEX ROWID     | ITEM                |      1 |      1 |    31 |     2   (0)| 00:00:01 |  34993 |00:00:03.36 |     308K|    197 |
|  25 |          NESTED LOOPS                   |                     |      1 |     74 | 13764 | 11267   (1)| 00:02:16 |  66218 |00:00:01.59 |     289K|      0 |
|  26 |           NESTED LOOPS                  |                     |      1 |     63 |  9765 | 11141   (1)| 00:02:14 |  30999 |00:00:01.40 |     258K|      0 |
|  27 |            NESTED LOOPS                 |                     |      1 |    174 | 23838 | 10793   (1)| 00:02:10 |  60431 |00:00:00.85 |   89609 |      0 |
|  28 |             NESTED LOOPS                |                     |      1 |   2046 |    99K|  8672   (1)| 00:01:45 |   2014 |00:00:00.12 |   24742 |      0 |
|* 29 |              TABLE ACCESS FULL          | COMPANY_LANG        |      1 |   2046 | 79794 |  4579   (2)| 00:00:55 |   2021 |00:00:00.32 |   20691 |      0 |
|* 30 |              INDEX RANGE SCAN           | COMPANY_IX1         |   2021 |      1 |    11 |     2   (0)| 00:00:01 |   2014 |00:00:00.02 |    4051 |      0 |
|  31 |             TABLE ACCESS BY INDEX ROWID | PRODUCT             |   2014 |      1 |    87 |    17   (0)| 00:00:01 |  60431 |00:00:00.57 |   64867 |      0 |
|* 32 |              INDEX RANGE SCAN           | IX_PRODUCT          |   2014 |     19 |       |     1   (0)| 00:00:01 |  60431 |00:00:00.08 |    2225 |      0 |
|* 33 |            TABLE ACCESS BY INDEX ROWID  | PRODUCT_LANG        |  60431 |      1 |    18 |     2   (0)| 00:00:01 |  30999 |00:00:00.74 |     168K|      0 |
|* 34 |             INDEX UNIQUE SCAN           | UX_PRODUCT_LANG     |  60431 |      1 |       |     1   (0)| 00:00:01 |  47017 |00:00:00.38 |     120K|      0 |
|* 35 |           INDEX RANGE SCAN              | IX_ITEM_PRODUCT     |  30999 |      1 |       |     1   (0)| 00:00:01 |  35218 |00:00:00.22 |   31102 |      0 |
|  36 |         VIEW                            | VW_SQ_1             |      1 |    165K|   806K|  3324   (1)| 00:00:40 |  94131 |00:00:00.38 |    1594 |      0 |
|  37 |          NESTED LOOPS                   |                     |      1 |    165K|  4356K|  3324   (1)| 00:00:40 |  94131 |00:00:00.28 |    1594 |      0 |
|* 38 |           TABLE ACCESS BY INDEX ROWID   | CATALOG             |      1 |   1648 | 31312 |    26   (0)| 00:00:01 |    579 |00:00:00.01 |     126 |      0 |
|* 39 |            INDEX RANGE SCAN             | IXX_CATALOG_LID     |      1 |    325 |       |     1   (0)| 00:00:01 |    894 |00:00:00.01 |       5 |      0 |
|* 40 |           INDEX RANGE SCAN              | IX_CATALOG_GOODS    |    579 |    100 |   800 |     2   (0)| 00:00:01 |  94131 |00:00:00.10 |    1468 |      0 |
|* 41 |        INDEX RANGE SCAN                 | IX_OPTION           |  34847 |      3 |       |     2   (0)| 00:00:01 |  71629 |00:00:00.38 |   69941 |      8 |
|  42 |     TABLE ACCESS BY INDEX ROWID         | PRICE               |  69030 |      1 |    21 |     2   (0)| 00:00:01 |  68665 |00:00:05.40 |     487K|    217 |
|* 43 |      INDEX UNIQUE SCAN                  | PK_PRICE            |  69030 |      1 |       |     1   (0)| 00:00:01 |  68665 |00:00:04.95 |     419K|    217 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------

Теперь оптимизатор намного точнее оценивает избирательность условий при сканировании таблицы COMPANY_LANG (строка 29 плана) — ожидает получить 2046 строк и запрос реально возвращает 2021 строку.

При более точной оценке избирательности условий растёт общая стоимость выполнения запроса и ожидаемое общее количество строк. Точнее, эти цифры приближаются к фактическим значениям, однако сам план, время и статистика выполнения запроса практически не изменилась по сравнению с предыдущим выриантом. Можно продолжать экперименты :)

Cardinality feedback

Dynamic sampling

About these ads

Комментарии (2) »

  1. жаль, нету в этом DBMS_SQLTUNE.CREATE_TUNING_TASK волшебной силы :)

    комментарий от Yuri — 11.08.2011 @ 10:48 | Ответить

    • оптимизировать оптимизированное всегда непросто ;)
      в этом случае, действительно, непростой запрос — и очень хорошо, что точно изложены причины невозможности построить SQL Profile
      гораздо хуже, например, когда Oracle позволяет успешно создать outline для успешно работавшего (до создания outline) запроса, после чего запрос (который старается изо всех сил использовать подготовленный outline) выдаёт ошибку при выполнении

      комментарий от Igor Usoltsev — 14.08.2011 @ 15:46 | Ответить


RSS-лента комментариев к этой записи. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Тема: Rubric. Блог на WordPress.com.

Отслеживать

Get every new post delivered to your Inbox.

Join 108 other followers

%d bloggers like this: