Oracle mechanics

16.12.2009

Простой запрос: FIRST_ROWS и CURSOR_SHARING_EXACT

Filed under: CBO,heuristics,hints,Oracle — Игорь Усольцев @ 16:16
Tags: ,

Простой запрос по индексу выполняется 465 секунд!

SELECT
A.ID, A.LOGIN, A.NAME, A.LASTNAME, A.EMAIL
FROM ACCOUNT A
WHERE (A.LOGIN LIKE '%%')
AND (A.NAME LIKE '%Кудряшов%' OR A.LASTNAME LIKE '%Кудряшов%')
AND A.COMPANY_ID = 1
ORDER BY A.LOGIN

Это странно, поскольку одно из условий A.COMPANY_ID = 1 обладает высокой избирательностью

SQL> SELECT 'ALL' as "Condition", count(*) FROM ACCOUNT A
  2  union
  3  SELECT 'COMPANY_ID = 1' as "Condition", count(*) FROM ACCOUNT A WHERE A.COMPANY_ID = 1;

Condition                          COUNT(*)
-------------------------------- ----------
ALL                                 2293282
COMPANY_ID = 1                          361

, столбец ACCOUNT.COMPANY_ID проиндексирован и команда EXPLAIN PLAN показывает, что именно этот индекс используется!

SQL> explain plan for
SELECT
A.ID, A.LOGIN, A.NAME, A.LASTNAME, A.EMAIL
FROM ACCOUNT A
WHERE (A.LOGIN LIKE '%%')
AND (A.NAME LIKE '%Кудряшов%' OR A.LASTNAME LIKE '%Кудряшов%')
AND A.COMPANY_ID = 1
ORDER BY A.LOGIN
/
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name                     | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                          |     1 |    66 |     5 |
|   1 |  SORT ORDER BY               |                          |     1 |    66 |     5 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACCOUNT                  |     1 |    66 |     4 |
|   3 |    INDEX RANGE SCAN          | IX_ACCOUNT_COMPANY_ID    |     1 |       |     3 |
-----------------------------------------------------------------------------------------

Известно (см., например, заметку Керри Осборна с характерным названием Explain Plan Lies), что EXPLAIN PLAN — это инструмент только для предварительной оценки возможного плана выполнения и в данном случае, скорее всего, мы увидели не актуальный, а один из возможных планов выполнения. AUTOTRACE, используя тот же инструмент (EXPLAIN PLAN), показывает тот же (скорее всего недействующий) план, но статистику выполнения запроса (раздел Statistics) AUTOTRACE показывает действительную:

SQL> set autotrace traceonly exp stat timi on
SQL> SELECT /*+ GATHER_PLAN_STATISTICS A7*/
A.ID, A.LOGIN, A.NAME, A.LASTNAME, A.EMAIL
FROM ACCOUNT A
WHERE (A.LOGIN LIKE '%%')
AND (A.NAME LIKE '%Кудряшов%' OR A.LASTNAME LIKE '%Кудряшов%')
AND A.COMPANY_ID = 1
ORDER BY A.LOGIN
/
Elapsed: 00:13:10.80
Execution Plan
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name                     | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                          |     1 |    66 |     5 |
|   1 |  SORT ORDER BY               |                          |     1 |    66 |     5 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACCOUNT                  |     1 |    66 |     4 |
|   3 |    INDEX RANGE SCAN          | IX_ACCOUNT_COMPANY_ID    |     1 |       |     3 |
-----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
...
1675452  consistent gets
185156  physical reads
...
0  sorts (memory)
0  sorts (disk)
1  rows processed

Судя по времени выполнения (более 13 минут), статистике (много чтений — 1675452  consistent gets, 185156  physical reads) и отсутствию в статистике сортировок (в плане точно указана операция SORT ORDER BY, однако в статистике выполнения: 0  sorts (memory), 0  sorts (disk)), план, который мы видим выше не имеет ничего общего с действительностью.

При последнем выполнении запроса я использовал подсказку /*+ GATHER_PLAN_STATISTICS A7*/ с «уникальным» комментарием (чтобы проще было найти запрос в shared pool) и после выполнения запроса с помощью процедуры dbms_xplan.display_cursor можно увидеть реально использованный план запроса и статистику выполнения плана (которая в данном случае не так интересна — важен реальный план, зафиксированный во время выполнения):

SQL> select sql_id, child_number from gv$sql where sql_text like '%/*+ GATHER_PLAN_STATISTICS A7*/%';
SQL_ID        CHILD_NUMBER
------------- ------------
9xdkurpq18mq5            1
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor( '9xdkurpq18mq5',1,'ALL IOSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| ACCOUNT             |      1 |      1 |    66 | 83278   (1)| 00:16:40 |      1 |00:05:19.20 |    1675K|    185K|
|*  2 |   INDEX RANGE SCAN          | IX_ACCOUNT_LOGIN    |      1 |  87556 |       |   387   (1)| 00:00:05 |   1752K|00:00:14.02 |   16359 |   7747 |
------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("A"."COMPANY_ID"=:SYS_B_3 AND ("A"."NAME" LIKE :SYS_B_1 OR "A"."LASTNAME" LIKE :SYS_B_2)))
2 - access("A"."LOGIN" LIKE :SYS_B_0)
filter("A"."LOGIN" LIKE :SYS_B_0)

Понятно, почему запрос выполняется так медленно — Oracle использует сканирование (index range scan) по индексу  IX_ACCOUNT_LOGIN для поля LOGIN с фильтром (A.LOGIN LIKE ‘%%’) — избирательность операции = 0. Но благодаря использованию этого индекса из плана исключена операция сортировки (ORDER BY), что мы и видели выше в статистике выполнения.

Осталось разобраться, почему Oracle абсолютно нелогично отдаёт предпочтение сканированию по индексу с нулевой избирательностью (A.LOGIN LIKE ‘%%’ !!!) вместо того, чтобы быстро выбрать и обработать 361 строку (из 2-х миллионов) по индексу для поля COMPANY_ID с фильтром A.COMPANY_ID = 1 ?

Для начала посмотрим параметры оптимизатора, установленные не по умолчанию на уровне сессии

SQL> select * from v$ses_optimizer_env where ISDEFAULT <> 'YES' and SID = sys_context('USERENV', 'SID');
SID         ID NAME                                     ISD VALUE
---------- ---------- ---------------------------------------- --- -------------------------
999         25 _pga_max_size                            NO  656580 KB
999         38 optimizer_mode                           NO  first_rows
999         48 cursor_sharing                           NO  similar

Интересно — используется значение параметра optimizer_mode = FIRST_ROWS, для которого, кроме стоимостного подхода (cost based optimization) характерно применение правил (heuristics), даже несмотря на увеличение стоимости запроса (что мы и видим — стоимость предполагаемого (оптимального) плана отличается от стоимости реального плана более чем в 10 000 раз — 5 против 83278). В частности: при наличии индекса по столбцам сортировки (ORDER BY), план пересматривается с учётом внутреннего хинта для использования этого индекса — это, похоже именно наш случай: действующий план использует индекс по столбцу LOGIN при сортировке ORDER BY A.LOGIN

В трейсе 10053 оригинального запроса можно видеть:

Peeked values of the binds in SQL statement
*******************************************
...
 Bind#0
...
  value="%%"
...
Best:: AccessPath: IndexRange  Index: IX_ACCOUNT_COMPANY_ID
Cost: 4.00  Degree: 1  Resp: 4.00  Card: 0.00  Bytes: 0
...
Best so far: Table#: 0  cost: 5.0010  card: 0.0038  bytes: 66
...
****** Recost for ORDER BY (using index) ************
***************************************
...
Access Path: TableScan
Cost:  35491.98  Resp: 35491.98  Degree: 0
Cost_io: 35422.00  Cost_cpu: 2011482391
Resp_io: 35422.00  Resp_cpu: 2011482391
kkofmx: index filter:"A"."LOGIN" LIKE :B1 AND ("A"."NAME" LIKE :B2 OR "A"."LASTNAME" LIKE :B3) AND "A"."COMPANY_ID"=:B4
Access Path: index (RangeScan)
Index: IX_ACCOUNT_LOGIN
resc_io: 83256.00  resc_cpu: 645436211
ix_sel: 0.05  ix_sel_with_filters: 0.05
Cost: 83278.45  Resp: 83278.45  Degree: 1
Best:: AccessPath: IndexRange  Index: IX_ACCOUNT_LOGIN
Cost: 83278.45  Degree: 1  Resp: 83278.45  Card: 0.00  Bytes: 66
  1. механизм bind peeking работает — оптимизатор отлично «знает» значение связанной переменной в условии (A.LOGIN LIKE ‘%%’) и технически может определить нулевую избирательность
  2. на основании стоимостного подхода предпочтение отдаётся операции индексного доступа Access Path: index (AllEqRange) по индексу IX_ACCOUNT_COMPANY_ID (условие A.COMPANY_ID = 1) вплоть до секции Recost for ORDER BY (using index), где на основании параметра optimizer_mode = FIRST_ROWS (пока предположительно) и наличия сортировки результатов происходит пересмотр предпочтений (recost for ORDER BY), после чего оптимизатор рассматривает только две операции доступа Access Path: TableScan (полное сканирование таблицы со стоимостью 35422) и Access Path: index (RangeScan) (сканирование по индексу IX_ACCOUNT_LOGIN стоимостью 83278). И опять же на основании правил FIRST_ROWS и с учётом наличия в запросе индексного фильтра:»A».»LOGIN» LIKE :B1 AND… предпочнение отдаётся неэффективному дорогому индексному доступу в полном соответствии с правилами параметра optimizer_mode
  3. подготовленный таким образом план «закрепляется» с помощью подсказки INDEX_RS_ASC (INDEX Range Scan ASCending ) в подготовленном для запроса OUTLINE
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
FIRST_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("ACCOUNT"."LOGIN"))
END_OUTLINE_DATA
*/

Проверим правильность заключений, изменив параметр optimizer_mode

SQL> alter session set optimizer_mode = all_rows;
SQL> set autotrace traceonly exp stat timi on
SQL> SELECT
A.ID, A.LOGIN, A.NAME, A.LASTNAME, A.EMAIL
FROM ACCOUNT A
WHERE (A.LOGIN LIKE '%%')
AND (A.NAME LIKE '%Кудряшов%' OR A.LASTNAME LIKE '%Кудряшов%')
AND A.COMPANY_ID = 1
ORDER BY A.LOGIN
/
...
Elapsed: 00:00:00.01
Execution Plan
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name                     | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                          |     1 |    66 |     5 |
|   1 |  SORT ORDER BY               |                          |     1 |    66 |     5 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACCOUNT                  |     1 |    66 |     4 |
|   3 |    INDEX RANGE SCAN          | IX_ACCOUNT_COMPANY_ID    |     1 |       |     3 |
-----------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
365  consistent gets
0  physical reads
1  sorts (memory)
0  sorts (disk)
1  rows processed

Судя по согласованной с планом статистике (в точном соответствии с планом присутствует сортировка) и отличному времени выполнения, можно не проверять — план выполнения, выданный командой EXPLAIN PLAN правильный. Для ALL_ROWS всё логично — «чистый» стоимостной подход (Cost Based Optimization), никаких правил (heuristics)

Также быстро (используя тот же оптимальный план) запрос выполняется при использовании современной и более ориентированной на стоимость (и менее — на правила) подсказку FIRST_ROWS(1):

SQL> SELECT /*+ FIRST_ROWS(1) GATHER_PLAN_STATISTICS A7*/
A.ID, A.LOGIN, A.NAME, A.LASTNAME, A.EMAIL
FROM ACCOUNT A
WHERE (A.LOGIN LIKE '%')
AND (A.NAME LIKE '%Кудряшов%' OR A.LASTNAME LIKE '%Кудряшов%')
AND A.COMPANY_ID = 1
ORDER BY A.LOGIN
/
Elapsed: 00:00:00.02
Execution Plan
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name                     | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                          |     1 |    66 |     5 |
|   1 |  SORT ORDER BY               |                          |     1 |    66 |     5 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACCOUNT                  |     1 |    66 |     4 |
|   3 |    INDEX RANGE SCAN          | IX_ACCOUNT_COMPANY_ID    |     1 |       |     3 |
----------------------------------------------------------------------------------------

При использовании подсказки /*+ FIRST_ROWS(1) */ в трейсе 10053 также, как и в оригинальном запросе с optimizer_mode = FIRST_ROWS (что эквивалентно подсказке /*+ FIRST_ROWS */) можно найти секцию Recost for ORDER BY (using index), но в этом случае раздел заканчивается фразой Join order aborted: cost > best plan cost:

****** Recost for ORDER BY (using index) ************
...
Best:: AccessPath: IndexRange  Index: IX_ACCOUNT_LOGIN
Cost: 83278.45  Degree: 1  Resp: 83278.45  Card: 0.00  Bytes: 66
...
Join order aborted: cost > best plan cost

, т.е. правила FIRST_ROWS используются, но предпочтение отдаётся стоимостному подходу!

Интересно отметить, что при исключении [бессмысленного] условия (A.LOGIN LIKE ‘%%’) оригинальный запрос (optimizer_mode = FIRST_ROWS) выполняется также быстро:

SQL> SELECT
A.ID, A.LOGIN, A.NAME, A.LASTNAME, A.EMAIL
FROM ACCOUNT A
WHERE
--(A.LOGIN LIKE '%%') AND
(A.NAME LIKE '%Кудряшов%' OR A.LASTNAME LIKE '%Кудряшов%')
AND A.COMPANY_ID = 1
ORDER BY A.LOGIN
/
...
Elapsed: 00:00:00.01

При этом трейсе 10053 показывает, что в разделе Recost for ORDER BY (using index) рассматривается только один метод доступа Access Path: TableScan (полное сканирование таблицы) — видимо, из-за отсутствия индексного фильтра:»A».»LOGIN» LIKE :B1… и позднее предпочтение отдаётся индексному доступу по «правильному» индексу Best:: AccessPath: IndexRange  Index: IX_ACCOUNT_COMPANY_ID,  по стоимостным расчётам и в соответствии с правилами FIRST_ROWS: при возможности выбирать сканирование по индексу (index scan) вместо полного сканирования таблицы (full scan)

По этому же «быстрому» плану выполняется запрос с подсказкой CURSOR_SHARING_EXACT

SQL> SELECT /*+ CURSOR_SHARING_EXACT */
A.ID, A.LOGIN, A.NAME, A.LASTNAME, A.EMAIL
FROM ACCOUNT A
WHERE (A.LOGIN LIKE '%')
AND (A.NAME LIKE '%Кудряшов%' OR A.LASTNAME LIKE '%Кудряшов%')
AND A.COMPANY_ID = 1
ORDER BY A.LOGIN
/
Elapsed: 00:00:00.01
Execution Plan
-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name                     | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                          |     1 |    66 |     5 |
|   1 |  SORT ORDER BY               |                          |     1 |    66 |     5 |
|   2 |   TABLE ACCESS BY INDEX ROWID|    ACCOUNT               |     1 |    66 |     4 |
|   3 |    INDEX RANGE SCAN          | IX_ACCOUNT_COMPANY_ID    |     1 |       |     3 |
----------------------------------------------------------------------------------------

Опять же из трейса 10053 можно видеть:

1. Учитывая, что подсказка CURSOR_SHARING_EXACT не предполагает попыток использовать связанные переменные, оптимизатор видит запрос в текстовом виде, включая условие (A.LOGIN LIKE ‘%%’) и при начальном выборе методов доступа это условие даже не рассматривает как фильтр для индексного доступа, используются только следующие фильтры:

kkofmx: index filter:("A"."NAME" LIKE '%Кудряшов%' OR "A"."LASTNAME" LIKE '%Кудряшов%') AND "A"."COMPANY_ID"=1
kkofmx: index filter:"A"."COMPANY_ID"=1

2. Секция Recost for ORDER BY (using index), характерная для FIRST_ROWS, по-прежнему присутствует, но в ней для индекса IX_ACCOUNT_LOGIN рассматривается полное сканирование индекса Access Path: index (FullScan), логично учитывая «слабый» индексный фильтр («A».»LOGIN» LIKE ‘%%’ AND…) с соответственно возросшей стоимостью (Cost: 90589)

3. В результате этот план отвергается с формулировкой «наихудший план для устаревшей подсказки first rows» (worse old first rows plan)

****** Recost for ORDER BY (using index) ************
...
-----------------------------------------
Access Path: TableScan
Cost:  35491.98  Resp: 35491.98  Degree: 0
...
kkofmx: index filter:"A"."LOGIN" LIKE '%%' AND ("A"."NAME" LIKE '%Кудряшов%' OR "A"."LASTNAME" LIKE '%Кудряшов%') AND "A"."COMPANY_ID"=1
Access Path: index (FullScan)
Index: IX_ACCOUNT_LOGIN
...
Cost: 90589.93  Resp: 90589.93  Degree: 1
Best:: AccessPath: IndexRange  Index: IX_ACCOUNT_LOGIN
Cost: 90589.93  Degree: 1  Resp: 90589.93  Card: 0.00  Bytes: 66
***********************
...
Join order aborted: worse old first rows plan

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

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

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

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

Логотип WordPress.com

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

Фотография Twitter

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

Фотография Facebook

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

Google+ photo

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

Connecting to %s

Создайте бесплатный сайт или блог на WordPress.com.

%d такие блоггеры, как: