Bug 9706532: проблемы материализации подзапросов в рамках глобальной транзакции

Разработчики показали проблему – Bug 9706532 – “WITH” subqueries cannot be materialized inside a global transaction (or PLSQL RPC over DBlink) [ID 9706532.8]: оптимизированный запрос, использующий subquery factoring с материализацией, локально отрабатывает за 3 секунды, а преобразованный в обзор через db link – за 40 с лишним:

11.2.0.3.@ SQL> select * from APPS.TEST_VX;

12886 rows selected.

Elapsed: 00:00:03.01

SQL> select * from APPS.TEST_VX@LOOPBACK;

12886 rows selected.

Elapsed: 00:00:41.63

Естественным решением будет заказ патча 9706532 в ТП Oracle для своей платформы – что займёт от недели до месяца – в зависимости от постановки вопроса)

Другим вариантом решения проблемы может быть оптимизация запроса без использования subquery factoring с помощью подсказок NO_MERGE / NO_UNNEST со следующим результатом, например:

SQL> select * from apps.test_vx_no_merge@LOOPBACK;

12886 rows selected.

Elapsed: 00:00:14.10

Однако, учитывая особенности описания бага:

The choice of materializing or not may appear intermittent depending on whether there is already a usable child cursor for the SQL in the shared pool.

можно проверить, действительно ли удалённый запрос может выполняться с использованием материализации и при каких условиях

При удалённом выполнении через db link запроса

select * from APPS.TEST_VX@LOOPBACK;

на дальнем инстансе выполняется рекурсивный запрос типа

SELECT "A1"."ID",... FROM "APPS"."TEST_VX" "A1";

с sql_id = 313ann4g3qf50, на момент начала теста отсутствующий/удалённый из library cache:

SQL> select count(*) from v$sql where sql_id = '313ann4g3qf50';

  COUNT(*)
----------
         0

Если первым выполняется удалённый запрос:

SQL> select * from APPS.TEST_VX@LOOPBACK;

12886 rows selected.

Elapsed: 00:00:41.63

Execution Plan
----------------------------------------------------------
Plan hash value: 2897349620

-------------------------------------------------------------
| Id  | Operation                                           |
-------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE                             |
|*  1 |  HASH JOIN                                          |
|   2 |   TABLE ACCESS FULL                                 |
|*  3 |   HASH JOIN                                         |
|*  4 |    TABLE ACCESS FULL                                |

- материализация не используется => запрос выполняется медленно

После чего точно по этому же плану и с той же низкой скоростью будет выполняться и локальный рекурсивный запрос:

SQL> SELECT "A1"."ID",.. FROM "APPS"."TEST_VX" "A1";

12886 rows selected.

Elapsed: 00:00:42.15

Execution Plan
----------------------------------------------------------
Plan hash value: 2897349620

что отражается в обзоре V$SQL:

SQL> @v$sql 313ann4g3qf50

LAST_ACTIVE_TIME    CHILD_NUMBER OPTIMIZER_COST PLAN_HASH_VALUE EXECUTIONS ELAPSED_PER_EXEC ROWS_PER_EXEC
------------------- ------------ -------------- --------------- ---------- ---------------- -------------
20.06.2012 15:54:59            0           4279      2897349620          2           41,808         12886

Если же, напротив, первым будет выполняться локальный рекурсивный запрос (при отсутствии/после удаления разобранного курсора из shared pool), то будет выбран быстрый план с материализацией не только для рекурсивного, но и для последующего удалённого выполнения через db link:

SQL> @purge_cursor 313ann4g3qf50

PL/SQL procedure successfully completed.

SQL> select count(*) from v$sql where sql_id = '313ann4g3qf50';

  COUNT(*)
----------
         0

SQL> SELECT "A1"."ID",.. FROM "APPS"."TEST_VX" "A1";

12886 rows selected.

Elapsed: 00:00:02.62

Execution Plan
----------------------------------------------------------
Plan hash value: 1942170807

--------------------------------------------------------------
| Id  | Operation                                  | Name    |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                           |         |
|   1 |  VIEW                                      | TEST_VX |
|   2 |   TEMP TABLE TRANSFORMATION                |         |
...

SQL> select * from APPS.TEST_VX@LOOPBACK;

12886 rows selected.

Elapsed: 00:00:02.59

SQL> @v$sql 313ann4g3qf50

LAST_ACTIVE_TIME    CHILD_NUMBER OPTIMIZER_COST PLAN_HASH_VALUE EXECUTIONS ELAPSED_PER_EXEC ROWS_PER_EXEC
------------------- ------------ -------------- --------------- ---------- ---------------- -------------
20.06.2012 16:05:36            0            467      1942170807          2            2,543         12886

Таким образом, если после каждой реинициализации shared pool удалённого инстанса (командами startup или alter system flush shared_pool) выполнять там локально рекурсивный запрос и закреплять разобранный курсор в кэше (sys.dbms_shared_pool.keep) то все последующие выполнения запроса через db link будут   быстрыми, успешно использующие материализацию, несмотря на баг! И это счастье будет продолжаться ровно до тех пор пока в library cache будет оставаться “правильный” курсор. Понятно, что способ, мягко говоря, достаточно громоздкий и ненадёжный для практического использования

Попытка закрепить правильный, с материализацией, план выполнения рекурсивного запроса на удалённом инстансе не меняет картины: если после создания baseline первым выполнялся удалённый запрос – baseline отвергается:

SPM: failed to reproduce the plan using the following info:
...
--
SPM: generated non-matching plan:

При обратном порядке выполнения – baseline успешно используется сначала для локального рекурсивного, затем и для удалённого выполнения:

SQL> @v$sql_bacs.sql 313ann4g3qf50

LAST_ACTIVE_TIME    CHILD_NUMBER OPTIMIZER_COST PLAN_HASH_VALUE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_PER_EXEC ROWS_PER_EXEC
------------------- ------------ -------------- --------------- ------------------------------ ---------- ---------------- -------------
20.06.2012 16:09:56            0            467      1942170807 SQL_PLAN_csqtmnz5s859mc2322adb          2            2,566         12886

P.S. Уже после тестов обнаружил превосходный кейс от уважаемого Доменика Брукса – Dominic Brooks: Materialize – там в т.ч. про материализацию при выполнении локального запроса в рамках глобальной транзакции и описанный баг – рекомендую

About these ads