Oracle mechanics

18.10.2013

Индексный доступ к таблицам удалённого обзора-2: практикум

Filed under: CBO,Oracle,remote,SQL Tuning — Игорь Усольцев @ 00:39
Tags: ,

Наглядная иллюстрация к теоретической демонстрации из предыдущего поста

На рабочей версии 11.2.0.3 был замечен показательный запрос (далее показана самая медленная нетрансформируемая его часть), с грубым нарушением SLA стабильно выполнявшийся за 6 с лишним минут:

SQL> select                                                  -- состоит из 2-х подзапросов,
  2         su.client_id_001 as client_id, ac.login as login
  3    from t_account ac
  4    join VIEW_WITH_UNION_ALL@LINK su                      -- использующих те самые UNION ALL обзоры через DBLINK
  5      on su.client_id_002 = ac.client_id
  6   where ac.login = 'Луноход1'
  7  union all
  8  select su.client_id_001 as client_id, o.text as login
  9    from t_order o
 10    join VIEW_WITH_UNION_ALL@LINK su                      -- и здесь
 11      on su.client_id_002 = o.client_id
 12   where o.service_id = 7
 13     and o.service_order_id = 7447316
 14  /

1012 rows selected.

Elapsed: 00:06:16.18

При этом план одновременно показывает два разных подхода к выполнению подзапросов:

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |   953 | 64406 |   386K (52)| 01:10:52 |      |      |
|   1 |  UNION-ALL                    |                     |       |       |            |          |      |      |
|*  2 |   HASH JOIN                   |                     |   292 | 12848 |   203K  (9)| 00:37:24 |      |      |-- 1-й подзапрос
|*  3 |    TABLE ACCESS FULL          | T_ACCOUNT           |     1 |    18 | 15457   (1)| 00:02:51 |      |      |
|   4 |    REMOTE                     | VIEW_WITH_UNION_ALL |   792M|    19G|   182K  (6)| 00:33:29 | LINK | R->S |-- Cost 182K: 792M Rows
|   5 |   NESTED LOOPS                |                     |   661 | 51558 |   182K  (6)| 00:33:29 |      |      |-- 2-й подзапрос
|   6 |    TABLE ACCESS BY INDEX ROWID| T_ORDER             |     1 |    52 |     3   (0)| 00:00:01 |      |      |
|*  7 |     INDEX UNIQUE SCAN         | T_ORDER_PK2         |     1 |       |     2   (0)| 00:00:01 |      |      |
|   8 |    REMOTE                     | VIEW_WITH_UNION_ALL |   661 | 17186 |   182K  (6)| 00:33:29 | LINK | R->S |-- Cost 182K: 661 Rows
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CLIENT_ID_002"="AC"."CLIENT_ID")
   3 - filter("AC"."LOGIN"='Луноход1' AND "AC"."CLIENT_ID" IS NOT NULL)
   7 - access("O"."SERVICE_ID"=7 AND "O"."SERVICE_ORDER_ID"=7447316)

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT "CLIENT_ID_002","CLIENT_ID_001" FROM "VIEW_WITH_UNION_ALL" "SU" -- рекурсивный запрос для TABLE ACCESS FULL удал.таблиц
       (accessing 'LINK' )

   8 - SELECT "CLIENT_ID_002","CLIENT_ID_001" FROM "VIEW_WITH_UNION_ALL" "SU" -- рекурсивный запрос, подходящий для INDEX SCAN
       WHERE "CLIENT_ID_002"=:1 (accessing 'LINK' )                           -- (с соответствующим предикатом)

— интересно заметить:

  • стоимость доступа (и расчётное время) к удалённому источнику VIEW_WITH_UNION_ALL не зависят от кол-ва ожидаемых / расчётных строк и вида удалённых запросов: так стоимость удалённых частей для обоих подзапросов = 182K, расчётное время 30+ минут — строки 4 и 8 плана, соответственно. На этих же строках локального плана с операцией REMOTE отсутствуют фильтры, т.е. временем удалённой фильтрации в строке 8 и уменьшением кол-ва передаваемых через dblink строк пренебрегается, либо они рассматриваются как компенсирующие факторы ;)
  • похоже, что оптимизатор знает о таблицах, составляющих удалённый обзор VIEW_WITH_UNION_ALL только кол-во строк 792M (совпадает с точностью), ср.размер строки 12 и размер блока удалённой бд:
Table Stats::
  Table: VIEW_WITH_UNION_ALL  Alias: VIEW_WITH_UNION_ALL  (NOT ANALYZED)
    #Rows: 792114084  #Blks:  580162  AvgRowLen:  12.00  ChainCnt:  0.00 -- вычисляемое #Blks = (#Rows * AvgRowLen) / db_block_size не соотв.статистике
  Column (#1): CLIENT_ID_002(  NO STATISTICS (using defaults)            -- понятно
    AvgLen: 13 NDV: 24753565 Nulls: 0 Density: 0.000000                  -- вычисляемый NDV, не соответсвует факт.статистике

Команда AUTOTRACE показывает только локальную статистику, а потому не информативна:

Statistics
----------------------------------------------------------
         15  recursive calls                               -- первое выполнение, hard parse
          1  db block gets
      75118  consistent gets
          0  physical reads
...
       1012  rows processed

Полезнее смотреть ожидания сессии, зафиксированные за время выполнения запроса, коих много и подавляющая часть связана с передачей данных через dblink:

SQL> @my_waits

EVENT                          WAIT_CLASS         WAITS TIME_WAITED_CS AVG_WAIT_CS
------------------------------ ----------- ------------ -------------- -----------
SQL*Net more data from dblink  Network          885,234          1,550       0.000
SQL*Net message from dblink    Network           20,168            710       0.040
resmgr:internal state change   Concurrency            1             10      10.070
SQL*Net message to dblink      Network           20,167              6       0.000

Рецепт исправления плана описан ранее, остаётся применить:

SQL> select--+ USE_NL(AC SU)
  2         su.client_id_001 as client_id, ac.login as login
  3    from t_account ac
  4    join VIEW_WITH_UNION_ALL@LINK su
  5      on su.client_id_002 = ac.client_id
  6   where ac.login = 'Луноход1'
  7  union all
  8  select su.client_id_001 as client_id, o.text as login
  9    from t_order o
 10    join VIEW_WITH_UNION_ALL@LINK su
 11      on su.client_id_002 = o.client_id
 12   where o.service_id = 7
 13     and o.service_order_id = 7447316
 14  /

1012 rows selected.

Elapsed: 00:00:05.45 -- 6 секунд меньше 6 минут

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |   953 | 64406 |   380K (51)| 01:09:47 |        |      |
|   1 |  UNION-ALL                    |                     |       |       |            |          |        |      |
|   2 |   NESTED LOOPS                |                     |   292 | 12848 |   198K  (6)| 00:36:19 |        |      |--правильная операция соединения
|*  3 |    TABLE ACCESS FULL          | T_ACCOUNT           |     1 |    18 | 15457   (1)| 00:02:51 |        |      |
|   4 |    REMOTE                     | VIEW_WITH_UNION_ALL |   367 |  9542 |   182K  (6)| 00:33:29 |   LINK | R->S |--, не меняя стоимости и времени,
|   5 |   NESTED LOOPS                |                     |   661 | 51558 |   182K  (6)| 00:33:29 |        |      |--*
|   6 |    TABLE ACCESS BY INDEX ROWID| T_ORDER             |     1 |    52 |     3   (0)| 00:00:01 |        |      |
|*  7 |     INDEX UNIQUE SCAN         | T_ORDER_PK2         |     1 |       |     2   (0)| 00:00:01 |        |      |--**
|   8 |    REMOTE                     | VIEW_WITH_UNION_ALL |   661 | 17186 |   182K  (6)| 00:33:29 |   LINK | R->S |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("AC"."LOGIN"='Луноход1' AND "AC"."CLIENT_ID" IS NOT NULL)
   7 - access("O"."SERVICE_ID"=7 AND "O"."SERVICE_ORDER_ID"=7447316)

Remote SQL Information (identified by operation id):
----------------------------------------------------

   4 - SELECT /*+ USE_NL ("VIEW_WITH_UNION_ALL") */ "CLIENT_ID_002","CLIENT_ID_001" FROM -- генерирует правильный удалённый запрос
       "VIEW_WITH_UNION_ALL" "SU" WHERE "CLIENT_ID_002"=:1 (accessing 'LINK' )           -- с предикатом, и использует индекс на той стороне

   8 - SELECT "CLIENT_ID_002","CLIENT_ID_001" FROM "VIEW_WITH_UNION_ALL" "SU"
       WHERE "CLIENT_ID_002"=:1 (accessing 'LINK' )

Локальная статистика AUTOTRACE, как и ожидалось, не изменилась:

Statistics
----------------------------------------------------------
         15  recursive calls
          1  db block gets
      75119  consistent gets
          0  physical reads
...
       1012  rows processed

, зато значительно отличается картина ожиданий:

SQL> @my_waits

EVENT                          WAIT_CLASS         WAITS TIME_WAITED_CS AVG_WAIT_CS
------------------------------ ----------- ------------ -------------- -----------
SQL*Net message from dblink    Network               18              1       0.080

*) стоит отметить, что «правильная» операция NESTED LOOPS во 2-ом подзапросе, являющая следствием уникальности индекса T_ORDER_PK2 (**) после обновления до 12.1 превратится в тыкву в неприятный для этого запроса HASH JOIN, и для долгосрочного закрепления плана в запрос (SQL Patch, Baseline/Outline) необходимо добавить ещё одну подсказку:

select--+ USE_NL(AC SU)
       su.client_id_001 as client_id, ac.login as login
  from t_account ac
  join VIEW_WITH_UNION_ALL@LINK su
    on su.client_id_002 = ac.client_id
 where ac.login = 'Луноход1'
union all
select--+ USE_NL(O SU)
       su.client_id_001 as client_id, o.text as login
  from t_order o
  join VIEW_WITH_UNION_ALL@LINK su
    on su.client_id_002 = o.client_id
 where o.service_id = 7
   and o.service_order_id = 7447316
/

2 комментария »

  1. Полезно! Когда то давно (8i, 9i), приходилось писать и дергать удаленную процедуру, которая на удаленной базе посчитает все нужное с использованием индексного доступа по вх. параметру, и вернула на локальный хост.

    комментарий от Sergey Golikov — 29.10.2013 @ 19:50 | Ответить

    • спасибо, Сергей, за подтверждение!
      Есть у меня ощущение, что со времён древних версий (8i, 9i) в этом тёмном углу оракловского кода мало что изменилось

      комментарий от Igor Usoltsev — 31.10.2013 @ 10:35 | Ответить


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