Метод распараллеливания DML, который можно встретить при обновлении OEBS, и как с этим бороться

Намедни, накатывая очередное обновление OEBS в части бд, Иван Постников обратил внимание на странно захинтованные запросы вида:

SELECT /*+ rowid(cust_accts) */
...
  FROM HZ_CUST_ACCOUNTS CUST_ACCTS
 WHERE ORIG_SYSTEM_REFERENCE IS NOT NULL
   AND STATUS IN ('A', 'I')
   AND CUST_ACCTS.CUST_ACCOUNT_ID NOT IN
       (SELECT /*+ HASH_AJ */
         OSR.OWNER_TABLE_ID
          FROM HZ_ORIG_SYS_REFERENCES OSR
         WHERE OSR.OWNER_TABLE_NAME = 'HZ_CUST_ACCOUNTS'
           AND CUST_ACCTS.ORIG_SYSTEM_REFERENCE = OSR.ORIG_SYSTEM_REFERENCE
           AND OSR.STATUS = 'A')
   AND ROWID BETWEEN :b2 AND :b1

, выполняющиеся неразумное время пачками по несколько десятков штук

По словам Леонида Борчука мы столкнулись с хардкорной реализацией разработчиками OEBS идеи параллельного выполнения запроса — rowid-ы таблицы предварительно делятся на пачки с последующим «параллельным» запуском batch-запросов — для скорости, полагаю)

Правда упомянутые разработчики в этих, по идее мелких и быстрых запросах, оставили (забыли?) хинт от «взрослого» большого запроса — /*+ HASH_AJ */, и получалась следующая статистика/картина выполнения:

SQL> @ash_sqlmon2 a6b03616hfvh4
 
LAST_PLSQL  PLAN_HASH_VALUE   ID PLAN_OPERATION                       OBJECT_NAME            CARDINALITY   ASH_ROWS WAIT_PROFILE
----------- --------------- ---- ------------------------------------ ---------------------- ----------- ---------- --------------------------------------------------------------------------------------------------
Main Query       3954253208    0   SELECT STATEMENT                                                                 
                               1     FILTER                                                                         
>                              2       HASH JOIN ANTI                                                 61         58 ON CPU(58)
                               3         TABLE ACCESS BY ROWID RANGE  HZ_CUST_ACCOUNTS              6068            
>>>                            4         TABLE ACCESS FULL            HZ_ORIG_SYS_REFERENCES     2315600     227454 direct path read(225850); ON CPU(1552); reliable message(33); enq: KO - fast object checkpoint(19)
SQL Summary               0    0 ASH fixed 233 execs from 32 sessions                                        227512  ash rows were fixed from 09.06.2016 10:10:53 to 09.06.2016 12:09:33

— написано /*+ HASH_AJ */ — получите HASH JOIN ANTI + TABLE ACCESS FULL!

В рез-тате эти по замыслу небольшие запросы по диапазону ROWID, запускаемые параллельно и конкурируя за I/O, выполнялись по 1000+ секунд, заметно тормозя весь процесс обновления

Доступным решением стало внешнее отключение хинтов в теле запроса:

SQL> @sqlpatch+ a6b03616hfvh4 IGNORE_OPTIM_EMBEDDED_HINTS NO_HINTS

— после чего ср.время выполнения запроса ELA_PER_EXEC:

SQL> @v$sqlstats2 a6b03616hfvh4

--------------------------------------------------------------
SQL_ID=a6b03616hfvh4 Shared Pool statistics by PLAN_HASH_VALUE
--------------------------------------------------------------
 
INST EXECS LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC PLAN_HASH_VALUE AVG_CBO_COST     CHILDS SQL_PATCH
---- ----- -------------------- ------------------- ------------ --------------- ------------ ---------- ---------
   1  1679 2016-06-08/20:12:16  09.06.2016 12:34:29   1081948942      3954253208       327090          1
   1   812 2016-06-09/12:29:54  09.06.2016 12:34:30     12215574      1853353876        45930          2 NO_HINTS

— удалось снизить на пару порядков, до 12+ секунд, не прерывая процесса обновления

И таких запросов было несколько, т.е. описанный подход применяется достаточно активно

Реклама

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

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

Логотип WordPress.com

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

Фотография Twitter

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

Фотография Facebook

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

Google+ photo

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

Connecting to %s