Oracle mechanics

13.10.2011

Медленный разбор SQL запроса (long parse time): вариант №2

Filed under: CBO,commonplace,hints,Oracle,RBO — Игорь Усольцев @ 10:08
Tags: , , ,

Ещё один интересный случай, связанный с длительной фазой разбора (hard parsing) запроса в Oracle 10.2.0.3

В отличие от предыдущего случая (Медленный разбор SQL запроса (long parse time)), когда много времени тратилось на перебор многочисленных комбинаций соединений таблиц (пермутаций) уже на этапе оптимизации и зависело от лимита кол-ва таких перестановок (параметр _optimizer_max_permutations) или выбора используемого оптимизатора (CBO или RBO), на этот раз при первом выполнении (и, соответственно, выполнении hard parsing) запроса из 118 таблиц:

SQL> select operation, count(*) from v$sql_plan
 2 where (plan_hash_value, child_number) =
 3       (select plan_hash_value from v$sql where sql_text like 'SELECT /* MY_LONG_QUERY */%' and child_number = 0)
 4 and operation like 'TABLE%'
 5 group by operation
 6 /

OPERATION    COUNT(*)
------------ --------
TABLE ACCESS      118

кроме табличной комбинаторики, значительное время тратилось на этапе трансформации запроса (query transformation), предшествующей оптимизации:

-----------------------------------------------------------------------------
|                   Query Text & Parameters Parsing                         |
|                                 V                                         |
|                        Query Transformation                               |  <-- описываемый случай "тормозил" и в этом месте
|   -------> (incl.Cost-Based Query Transformation - CBQT)                  |
|   |                             V                                         |
|   |                    Query Optimization                                 |
|   |                             V                                         |
|   -< Cost-Based Optimization <     > Rule-Based Optimization              |
|(with join order permutations)        (with simplified table order choice?)|  <-- тут "притормаживал" случай, описанный ранее
-----------------------------------------------------------------------------

Обнаружить замечательный запрос удалось по SQL трейсу — пара таких запросов потребляла львиную долю времени выполнения пользовательской операции (бизнес-процесса):

SELECT /* MY_LONG_QUERY */ ...

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1     17.80      17.44          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0        650          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3     17.80      17.45          0        650          0           1

Тот же результат можно наблюдать в статистике сессии (v$sess_time_model):

STAT_NAME                DELTA
------------------------ --------
DB time                  17567316
DB CPU                   17505213
parse time elapsed       17421981
hard parse elapsed time  17413513

Изменение оптимизатора на RULE на результат заметно не повлияло :

SQL> SELECT--+ RULE
 /* MY_LONG_QUERY */
 ...
 Elapsed: 00:00:17.07

А уменьшение количества пермутаций таблиц сократило время разбора незначительно, с 17 до 11 секунд:

SQL> SELECT--+ opt_param( '_optimizer_max_permutations' 20 )
 2  /* MY_LONG_QUERY */
 ...
 Elapsed: 00:00:11.37

при этом на количество протестированных оптимизатором комбинаций порядка таблиц для блоков запросов (Join Order) параметр _optimizer_max_permutations влияет не прямо — например, при значениях _optimizer_max_permutations <= 20 в трейсе 10053 можно видеть, что Oracle всё равно тестирует 101 вариант:)

Join order aborted: cost > best plan cost
***********************
***********************
Join order[101]:

— вероятно, количество тестируемых пермутаций зависит (кроме значения параметра _optimizer_max_permutations) от количества таблиц в конкретном блоке запроса

Интересно, что ограничение количества тестируемых пермутаций (при большом количестве таблиц) сокращает время разбора не только для CBO, но и при Rule-Based Optimization:

SQL> alter session set optimizer_mode=rule;

Session altered.

SQL> SELECT/*+ opt_param( '_optimizer_max_permutations' 1 )
 2             MY_LONG_QUERY */
...
Elapsed: 00:00:11.36

Интересно потому, что выбор лучшего порядка таблиц основан на стоимостном подходе, но вполне успешно используется также при optimizer_mode=rule:

Considering cardinality-based initial join order.
 ...
 Best NL cost: 849.38
 resc: 849.38 resc_io: 847.00 resc_cpu: 53647963
 resp: 849.38 resp_io: 847.00 resp_cpu: 53647963
 Join Card:  239.99 = outer (0.01) * inner (43292.00) * sel (1)
 Join Card - Rounded: 240 Computed: 239.99
 Join order aborted: cost > best plan cost

И, по прежнему, несмотря на использование Rule-Based Optimization большая часть времени на этапе трансформации запроса (и физического места в трейс-файле) уходит на типичные операции Cost-Based Query Transformation (CBQT):

**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#0).
PM:   Checking validity of predicate move-around in SEL$1 (#0).
CBQT: Validity checks passed for 666p8pj2vagjg.

Оптимального результата удалось добиться, дополнительно (к ограничению количества пермутаций) отключив «простую» трансформацию запроса (предшествующую оптимизации, соотв.подсказка NO_QUERY_TRANSFORMATION), и Cost-Based Query Transformation (набор операций, используемых Oracle НЕзависимо от выбора метода оптимизации, параметр _optimizer_cost_based_transformation):

SQL> SELECT/*+ NO_QUERY_TRANSFORMATION
2            opt_param( '_optimizer_cost_based_transformation' 'off' )
3            opt_param( '_optimizer_max_permutations' 10 )
4            MY_LONG_QUERY */
...

Elapsed: 00:00:00.61

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

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

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 такие блоггеры, как: