Oracle mechanics

31.03.2012

explain plan

Filed under: CBO,Oracle,Plan Management — Игорь Усольцев @ 12:53
Tags: ,

Для себя давно принял за правило не полагаться на планы выполнения, показываемые командой explain plan, в особенности в неэлементарных случаях. Кроме упоминаемого разработчиками оптимизатора влияния значений связанных переменных, достаточно часто можно столкнуться с различиями между реальным планом выполнения из V$SQL_PLAN ( DBMS_XPLAN.DISPLAY_CURSOR) и показываемым после выполнения explain plan планом в смысле выбора и порядка применения операций доступа к данным, методов соединения и т.д., т.е. именно в плане действий, которые будут выполнены

На днях столкнулся с тем, что при выполнении для explain plan даже расчёт стоимости (арифметическая операция)  на некоторых этапах выполняется иначе чем в процессе оптимизации запроса для полностью совпадающих планах (по PLAN_HASH_VALUE) и рассчитанных ожидаемых количествах строк (E-Rows), например:

11.2.0.3.SQL> explain plan for
2  SELECT CUSTOMER_TRX_ID,TRX_NUMBER,TRX_DATE,AMOUNT,CONTRACT_NAME,RECEIPT_DATE
...
5  FROM
6   some_comp_view WHERE trim(contract_name) = '12672/10'  order
7    by trx_number
8  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 1597358833

-----------------------------------------------------------------------------------------
| Id  | Operation                                          | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                   |       |   267 |     0   (0)|
|   1 |  SORT AGGREGATE                                    |     1 |     9 |            |
|*  2 |   FILTER                                           |       |       |            |
|   3 |    TABLE ACCESS BY INDEX ROWID                     |     2 |    18 |     5   (0)|
|*  4 |     INDEX RANGE SCAN                               |     1 |       |     4   (0)|
|*  5 |  FILTER                                            |       |       |            |
|   6 |   SORT GROUP BY                                    |       |       |            |
|*  7 |    FILTER                                          |       |       |            |
|*  8 |     FILTER                                         |       |       |            |
|*  9 |      HASH JOIN OUTER                               |     1 |   267 |   388   (3)|
|  10 |       NESTED LOOPS                                 |       |       |            |
|  11 |        NESTED LOOPS                                |     1 |   233 |   291   (2)|
|  12 |         NESTED LOOPS                               |     1 |   210 |   287   (2)|
|  13 |          NESTED LOOPS                              |     1 |   186 |   285   (2)|
|  14 |           NESTED LOOPS                             |     1 |   174 |   283   (2)|
|  15 |            NESTED LOOPS                            |     1 |   166 |   281   (2)|
|* 16 |             HASH JOIN                              |     1 |   130 |   276   (2)|
|  17 |              NESTED LOOPS                          |       |       |            |
|  18 |               NESTED LOOPS                         |    16 |  1520 |   258   (2)|
|  19 |                NESTED LOOPS                        |    15 |  1080 |   228   (2)|
|  20 |                 NESTED LOOPS                       |    12 |   636 |   216   (2)|
|  21 |                  NESTED LOOPS                      |    61 |  1586 |    34  (12)|
|* 22 |                   INDEX FAST FULL SCAN             |    61 |  1220 |    34  (12)|
|* 23 |                   INDEX UNIQUE SCAN                |     1 |     6 |     0   (0)|
...
-----------------------------------------------------------------------------------------

SQL> SELECT CUSTOMER_TRX_ID,TRX_NUMBER,TRX_DATE,AMOUNT,CONTRACT_NAME,RECEIPT_DATE,
...
4  FROM
5   some_comp_view WHERE trim(contract_name) = '12672/10'  order
6    by trx_number
7  /

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor( '','','typical last'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 1597358833

-----------------------------------------------------------------------------------------
| Id  | Operation                                          | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                   |       |       |     1 (100)|
|   1 |  SORT AGGREGATE                                    |     1 |     9 |            |
|*  2 |   FILTER                                           |       |       |            |
|   3 |    TABLE ACCESS BY INDEX ROWID                     |     2 |    18 |     5   (0)|
|*  4 |     INDEX RANGE SCAN                               |     1 |       |     4   (0)|
|*  5 |  FILTER                                            |       |       |            |
|   6 |   SORT GROUP BY                                    |       |       |            |
|*  7 |    FILTER                                          |       |       |            |
|*  8 |     FILTER                                         |       |       |            |
|*  9 |      HASH JOIN OUTER                               |     1 |   267 |   388   (3)|
|  10 |       NESTED LOOPS                                 |       |       |            |
|  11 |        NESTED LOOPS                                |     1 |   233 |   291   (2)|
|  12 |         NESTED LOOPS                               |     1 |   210 |   287   (2)|
|  13 |          NESTED LOOPS                              |     1 |   186 |   285   (2)|
|  14 |           NESTED LOOPS                             |     1 |   174 |   283   (2)|
|  15 |            NESTED LOOPS                            |     1 |   166 |   281   (2)|
|* 16 |             HASH JOIN                              |     1 |   130 |   276   (2)|
|  17 |              NESTED LOOPS                          |       |       |            |
|  18 |               NESTED LOOPS                         |    16 |  1520 |   258   (2)|
|  19 |                NESTED LOOPS                        |    15 |  1080 |   228   (2)|
|  20 |                 NESTED LOOPS                       |    12 |   636 |   216   (2)|
|  21 |                  NESTED LOOPS                      |    61 |  1586 |    34  (12)|
|* 22 |                   INDEX FAST FULL SCAN             |    61 |  1220 |    34  (12)|
|* 23 |                   INDEX UNIQUE SCAN                |     1 |     6 |     0   (0)|
...
-----------------------------------------------------------------------------------------

— планы полностью не привожу, они длинные — по 98 шагов, в примере хорошо видно несовпадение итоговых стоимостей полностью совпадающих планов:

1 != 0

В списке выбираемых полей нет функций, в запросе не используются связанные переменные, сессия работала без изменения контекста

Понятно, что запрос — не из самых простых, там и Scalar Subquery, и множественные вложенные Inline View — собственно из-за чего общая стоимость запроса не учитывает стоимость отдельных компонентов, но это — другая история и не повод ошибаться)

Разумеется, погрешность в расчётах никак не влияет на собственно план, но, похоже, арифметику команда explain 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 такие блоггеры, как: