Oracle mechanics

13.04.2015

PL/SQL, RESULT_CACHE и sysdate в запросах

Filed under: Oracle,SQL Tuning — Игорь Усольцев @ 01:20
Tags: ,

Жил-был запрос, потреблявший бОльшую часть DB Time:

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:    307361 02-Apr-15 18:30:14       371       2.0
  End Snap:    307362 02-Apr-15 19:00:16       343       2.1
   Elapsed:               30.04 (mins)
   DB Time:            1,395.37 (mins)

...

SQL ordered by Elapsed Time

        Elapsed                  Elapsed Time                                   
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id    
---------------- -------------- ------------- ------ ------ ------ -------------
        50,849.2        122,466          0.42   60.7   56.1     .5 d3gt02wdxt8k3 -- с большим опережением
SELECT /*+ RESULT_CACHE*/ NVL(BUDGET, 0) FROM U0.V_CLIENT_DISCOUNTS D RIGHT JOIN
 DUAL ON CLIENT_ID = :B2 AND DT = TRUNC(:B1 )

         7,787.2          4,241          1.84    9.3   28.4     .0 14a9q0pdpf0jv -- от ближайшего конкурента

, указанный в тексте /*+ RESULT_CACHE*/ не работал, поскольку в глубинах вложенных обзоров запроса содержалось упоминание sysdate в виде TRUNC(SYSDATE), что не способствует использованию SQL Result Cache по определению, но и логика в этом есть не всегда. В частности, при разного рода округлениях sysdate, да и без округлений — например, для запроов с достаточно высоким RPS, как в этом случае возможность использования Result Cache может быть отнюдь не лишней

План запроса адекватно отражает источник высокой стоимости и невысокой скорости выполнения (~ 400 мс на выполнение):

SQL_ID  d3gt02wdxt8k3, child number 0
-------------------------------------
SELECT /*+ RESULT_CACHE*/ NVL(BUDGET, 0) FROM U0.V_CLIENT_DISCOUNTS D RIGHT JOIN DUAL ON CLIENT_ID = :B2 AND DT = TRUNC(:B1 )
 
Plan hash value: 349250770

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name                    | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                         |        |       |       |  1895 (100)|          |
|   1 |  NESTED LOOPS OUTER                       |                         |      1 |    13 |       |  1895   (1)| 00:00:21 |
|   2 |   FAST DUAL                               |                         |      1 |       |       |     2   (0)| 00:00:01 |
...
|* 23 |                   HASH JOIN OUTER         |                         |     11 |   990 |       |  1861   (1)| 00:00:21 |
|  24 |                    VIEW                   |                         |      1 |    31 |       |     7   (0)| 00:00:01 |
...
|  36 |                    VIEW                   |                         |    103K|  5969K|       |  1852   (1)| 00:00:21 |
|  37 |                     SORT GROUP BY         |                         |    103K|  3439K|  5320K|  1852   (1)| 00:00:21 |
|  38 |                      MAT_VIEW ACCESS FULL | MV_CLIENT_DISTRACTIONS  |    103K|  3439K|       |   260   (1)| 00:00:03 |
...
|* 47 |         INDEX UNIQUE SCAN                 | SYS_C001075911          |      1 |       |       |     0   (0)|          |
------------------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

...
  36 - SEL$12       / CB@SEL$11
  37 - SEL$12      
  38 - SEL$12       / MV_CLIENT_DISTRACTIONS@SEL$12

— практически вся стоимость генерируется в блоке SEL$12, при выполнении GROUP BY + FTS некого матвью, что подтверждается статистикой выполнения по ASH:

SQL> @ash_sqlmon2_hist d3gt02wdxt8k3 "" "" 307362
 
LAST_PLSQL   SQL_ID        PLAN_HASH_VALUE   ID PLAN_OPERATION                                 OBJECT_NAME            QBLOCK_NAME  ASH_ROWS WAIT_PROFILE
------------ ------------- --------------- ---- ---------------------------------------------- ---------------------- ------------ -------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Main Query   d3gt02wdxt8k3       349250770    0   SELECT STATEMENT                                                                        1 ON CPU(1)
...
                                             23                    HASH JOIN OUTER                                                      291 ON CPU(291)
...
                                             36                      VIEW                                             SEL$12             64 ON CPU(64)
                                             37                        SORT GROUP BY                                  SEL$12           4145 ON CPU(4145)
                                             38                          MAT_VIEW ACCESS FULL  MV_CLIENT_DISTRACTIONS SEL$12            500 ON CPU(353); gc buffer busy acquire(86); gc cr multi block request(45); gc cr block congested(6); gc cr block 2-way(4); latch: cache buffers chains(3); gc cr block busy(2); buffer busy waits(1)
...
SQL Summary                              0    0 ASH fixed 5059 execs from 119 sessions                                                 5059  ash rows were fixed from 02.04.2015 18:30:06 to 02.04.2015 19:00:02

SQL> @dba_hist_sqlstat "sql_id = 'd3gt02wdxt8k3' and snap_id = 307362"
 
BEGIN_SNAP_TIME      EXECS ROWS_PROCESSED SQL_ID              PLAN       COST PARSE_PER_EXEC ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC ROWS_PER_EXEC FETCHES_PER_EXEC IOWAITS_PER_EXEC CLWAITS_PER_EXEC_US PLSQL_PER_EXEC PX_PER_EXEC
--------------- ---------- -------------- ------------- ---------- ---------- -------------- ------------ ------------ ------------- ------------- ---------------- ---------------- ------------------- -------------- -----------
02.04 18:30         122466         122487 d3gt02wdxt8k3  349250770       2024          0,001       415211       233011          1688             1                1             2080               12406             20           0

Для matview MV_CLIENT_DISTRACTIONS присутствуют необходимый индекс и для операции Join Predicate Push Down (JPPD) inline view с GROUP BY давно уже формально не являются препятствием в простых случаях, но в реальном запросе при упомянутой многократной вложенности вида:

SELECT ...
FROM (SELECT ...
      FROM ...
      INNER JOIN (SELECT ...
                  FROM
                   (SELECT ...
                    FROM
                      (SELECT ...
                       FROM
                        ...
                          LEFT JOIN (SELECT ...
                                     FROM MV_CLIENT_DISTRACTIONS
                                     GROUP BY ...
                                    ) V ON ... -- join condition for inline view SEL$12
                                    LEFT JOIN ...

у оптимизатора что-то не получается:

JPPD:     JPPD bypassed: View contains a group by.
kkqfppRelFilter: Not pushing filter predicates in query block SEL$12 (#0) because no predicate to push

— что похоже на функциональные ограничения CBO при выполнении JPPD (глубина вложенности?), которых в действительности множество, интересные примеры можно найти у Mikhail Velikikh. JPPD bypassed View has non-standard group by

Ограничения, повторюсь, именно функциональные, в принципе нерешаемые простыми подсказками типа PUSH_PRED

Разрешить проблему на первый взгляд можно двумя способами:

1) заменить проблемный inline view на pipelined функцию, как описано в JPPD в присутствии удалённой таблицы и View Merging,

2) и/или всё-таки заставить работать Result Cache в присутствии sysdate

Оба этих метода мы и использовали:

1) Замена inline view -> pipelined функцию кроме значительного снижения формальной стоимости:

Plan hash value: 2394784798
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                                              | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                       |        |       |    43 (100)|          |
...
|  24 |                    MERGE JOIN OUTER                    |      1 |    76 |    27   (0)| 00:00:01 |
...
|  30 |                     BUFFER SORT                        |      1 |    52 |    26   (0)| 00:00:01 |
|  31 |                      VIEW                              |      1 |    52 |    22   (0)| 00:00:01 |
|  32 |                       COLLECTION ITERATOR PICKLER FETCH|      1 |     2 |    22   (0)| 00:00:01 | -- *

показала и практический рез-т:

SQL> @v$sqlstats 6v4y9g5b9vg0s
 
   EXECS SQL_ID              PLAN ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC CONC_PER_EXEC CLU_PER_EXEC UIO_PER_EXEC ROWS_PER_EXEC
-------- ------------- ---------- ------------ ------------ ------------- ------------- ------------ ------------ -------------
40227897 6v4y9g5b9vg0s 2394784798         2628          857            26            88           53          176             1

— со ср.временем выполнения 2,6 мс при использовании pipelined (PL/SQL) против 415,2 мс для чистого SQL, запрос исчез из ТОПа AWR, etc.. (т.о. при разумном использовании PL/SQL в SQL запросах очень неплохо себя показывает, несмотря на context switch всякого рода:)

Интересно, что использование pipelined функции с default cardinality = 4072 показывало план стоимостью 8190, кот.был, конечно же, быстрее первоначального, но запрос всё же потреблял ~ 100 consistent gets на выполнение. После уточнения cardinality функции до 1 (*) с помощью /*+ OPT_ESTIMATE(TABLE CB ROWS=1)*/, GETS_PER_EXEC снизилось до 26, плюс разумное уточнение стоимости 1895 -> 43 гармоничнее отражает пропорции изменения среднего времени выполнения ;)

2) Для кэширования рез-та запроса, использующего sysdate, Руслан Бикбаев предложил отличный вариант использования PL/SQL RESULT_CACHE, на примере простого теста выглядящий следующим образом:

SQL> CREATE OR REPLACE FUNCTION CACHED_FUNCTION_SIMPLE(aFRIQ IN date default trunc(sysdate, 'mi')) -- переменная aFRIQ определяет период кеширования
  2    RETURN date RESULT_CACHE AS
  3    min_dt date;
  4  BEGIN
  5  select max(nvl(SQL_EXEC_START, sysdate)) into min_dt from v$session, emp; -- кэшируемый запрос
  6    RETURN min_dt;
  7  END CACHED_FUNCTION_SIMPLE;
  8  /

Function created.

12:00:05 12.1.0.2.SCOTT@/ORCL SQL> select to_char(CACHED_FUNCTION_SIMPLE(trunc(sysdate, 'mi')), 'mi:ss') from dual;

TO_CH
-----
00:05                            -- создаётся кэш

12:00:05 SQL> /

TO_CH
-----
00:05                            -- результат из кэша

12:00:08 SQL> /

TO_CH
-----
00:05                            -- результат из кэша

12:00:09 SQL> /

TO_CH
-----
02:03                            -- сформированы новый результат и кэш

12:02:03 SQL>                    -- по прошествии 1+ минуты, как и было задумано

Использование PL/SQL result cache может быть проверено/подтверждено из значения SCAN_COUNT соответствующего обзора:

SQL> select * from v$result_cache_objects order by id;
 
ID TYPE       STATUS    NAME                                                                             NAMESPACE  CREATION_TIMESTAMP  CREATOR_UID DEPEND_COUNT BLOCK_COUNT        SCN COLUMN_COUNT  PIN_COUNT SCAN_COUNT LRU_NUMBER  OBJECT_NO CACHE_ID                     CACHE_KEY                    DB_LINK
-- ---------- --------- -------------------------------------------------------------------------------- ---------- ------------------- ----------- ------------ ----------- ---------- ------------ ---------- ---------- ---------- ---------- ---------------------------- ---------------------------- -------
 0 Dependency Published SCOTT.CACHED_FUNCTION_SIMPLE                                                                12.04.2015 12:00:05         110            2           1    5122168            0          0          0          0      99024 SCOTT.CACHED_FUNCTION_SIMPLE SCOTT.CACHED_FUNCTION_SIMPLE No
 1 Result     Published "SCOTT"."CACHED_FUNCTION_SIMPLE"::8."CACHED_FUNCTION_SIMPLE"#d95c738912566372 #1 PLSQL      12.04.2015 12:00:05         110            3           1    5122168            1          0          2         67          0 ffvrfkbkn9uc4ffw8rsjab6b1n   95pvc6xjysmc71334vzx5kdz5z   No
 2 Dependency Published SCOTT.EMP                                                                                   12.04.2015 12:00:05         110            2           1    5122168            0          0          0          0      93306 SCOTT.EMP                    SCOTT.EMP                    No
 3 Dependency Published PUBLIC.V$SESSION                                                                            12.04.2015 12:00:05         110            2           1    5122168            0          0          0          0       1676 PUBLIC.V$SESSION             PUBLIC.V$SESSION             No
 4 Result     Published "SCOTT"."CACHED_FUNCTION_SIMPLE"::8."CACHED_FUNCTION_SIMPLE"#d95c738912566372 #1 PLSQL      12.04.2015 12:02:03         110            3           1    5122217            1          0          0         68          0 ffvrfkbkn9uc4ffw8rsjab6b1n   5bkj97j62cy6k3vxy31ugvpbqd   No

, как и любой другой правильный result cache зависит от модификаций изпользуемой таблицы SCOTT.EMP и мгновенно инвалидируется в результате DML — что ожидаемо, но не зависит от состояния системных обзоров (V$SESSION), что можно использовать в качестве неожиданного бонуса!

В планах выполнения прямого и рекурсивного запросов использование этого типа result cache не отображается:

SQL> @xplan

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
SQL_ID  gvqbu99pgrav6, child number 0
-------------------------------------
select to_char(CACHED_FUNCTION_SIMPLE(trunc(sysdate, 'mi')), 'mi:ss')
from dual

Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

Кроме этого, использование PL/SQL result cache имеет другие неприятные, но нефатальные имхо ограничения, влючая невозможность использования в качестве возвращаемых значений LOB и REF CURSOR и неприменимость к pipelined функциям

В рассматриваемом выше практическом случае PL/SQL result cache, к сожалению, не оказал заметного влияния, что объясняется как использованием широкого диапазона значений связанной переменной CLIENT_ID, так и частотой изменения подлежащих объектов при выполнении этого конкретного запроса:

SQL> select name, type, namespace, status, sum(block_count), count(*), sum(scan_count), sum(build_time)
  2   from gv$result_cache_objects
  3   where cache_id = '6xzhbtn70k04z2rjktd857v49m'
  4   group by name, type, namespace, status
  5  /
 
NAME                                                                  TYPE       NAMESPACE STATUS   SUM(BLOCK_COUNT)   COUNT(*) SUM(SCAN_COUNT) SUM(BUILD_TIME)
--------------------------------------------------------------------- ---------- --------- -------- ---------------- ---------- --------------- ---------------
"U0"."PK_DISTORTION"::11."SF_GET_DISTO_BUDGET"#35e4950d345e4dc3 #192  Result     PLSQL     Invalid             41770      41770             561           11517

— было создано 41803 кэшей при 438 сканированиях /случаях использования с затратами SUM(BUILD_TIME) ~ 115 секунд на создание кэша

Тем не менее, использование Result Cache для запросов, использующих sysdate с заданной точностью, мне представляется весьма интересным и перспективным методом улучшения производительности OLTP систем

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

  1. Добрый день Игорь!

    Такое совпадение, мне как-раз хотелось бы попробовать заставить работать result cache с обрезанными (до разных границ) SYSDATE.
    Вот только не могу понять, для чего именно добавлена таблица EMP в селекте внутри функции:

    . . . into min_dt from v$session, emp; — ???

    С уважением,
    Сергей

    комментарий от Sergey — 19.04.2015 @ 22:40 | Ответить

    • Приветствую, Сергей

      таблица EMP была просто добавлена, чтобы одновременно тестировать зависимость PL/SQL result cache-а как от содержимого fixed view v$session (кот.не инвалидирует кэш даже при использовании ежесекундно изменяющегося max(V$SESSION.SQL_EXEC_START)), так и от модификаций / DML данных пользовательской таблицы EMP, которые успешно сбрасывает/инвалидирует PL/SQL result cache, что и требовалось

      комментарий от Игорь Усольцев — 19.04.2015 @ 23:14 | Ответить

      • А, то есть это просто целевая таблица, но из нее ничего не выбирается,
        используется только ее флаг.

        Большое спасибо, завтра попробую у себя.

        комментарий от Sergey — 20.04.2015 @ 00:45 | Ответить


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