Oracle mechanics

20.04.2014

Partition Push-Up при выполнении MIN/MAX операций

Filed under: heuristics,Oracle,Partitioning — Игорь Усольцев @ 00:44
Tags:

При наблюдении за замедлением выполнения запросов типа:

select * from 
(select max(max_value) supermax_value           -- этот подзапрос был оформлен в виде VIEW
          from (select max(x) max_value from t1 -- одна из таблиц партицирована RANGE PARTITION по искомому ключу
                union all
                select max(y)           from t2
                union all
                select max(z)           from t3
                union all
                ...)
)

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

11.1.0.7.@ SQL> select max(last_date) max_last_date
  2            from (select last_date
  3                    from (select max(showtime) last_date
  4                            from shows))
  5  /

MAX_LAST
--------
16.04.13

1 row selected.

Elapsed: 00:00:00.47 -- быстро

, то при добавлении обвязки типа SELECT * FROM (), характерной при использовании обзора, скорость резко падает:

SQL> select *  from (select max(last_date) max_last_date
  2                    from (select last_date
  3                            from (select max(showtime) last_date
  4                                    from shows)))
  5  /

MAX_LAST
--------
16.04.13

1 row selected.

Elapsed: 00:06:10.67 -- небыстро

При детальном рассмотрении оказалось, такое поведение характерно до версии Oracle 12c включительно, не относится к оптимизатору и плану выполнения, и, похоже, определяется на стадии выполнения механизмом PARTITION PUSHUP

Тестовая схема:

drop table t_part_pushup
/
create table t_part_pushup (x number, y number) storage(initial 10k next 10k)
partition by range (y) (partition P_0 values less than (0))
/
begin
for i in 1..999 loop
  execute immediate 'alter table t_part_pushup add partition p_'||i||' values less than ('||i||')';
end loop;
end;
/
insert into t_part_pushup select level, level-2 from dual connect by level <= 1000
/
exec dbms_stats.gather_table_stats('','T_PART_PUSHUP')

Далее описаны тесты на 12.1.0.1, однако, результат повторяем на 11g, учитывая, что фикс Bug 6239039 — Enhancement to allow partition push-up for MIN / MAX operators (Doc ID 6239039.8):

… in case of a range-partitioned table, the max value of the partitioning key column will always occur in the highest non-empty partition.

This enhancement tries to optimize the MAX / MIN operators on partition keys when possible

успешно функционирует, начиная с версии 11.1.0.6:

SQL> select * from v$system_fix_control where bugno = 6239039;
 
  BUGNO VALUE SQL_FEATURE             DESCRIPTION                                  OPTIMIZER_FEATURE_ENABLE  EVENT IS_DEFAULT
------- ----- ----------------------- -------------------------------------------- ------------------------- ----- ----------
6239039     1 QKSFM_PARTITION_6239039 Allow partition pushup for max/min operators 11.1.0.6                      0          1

Итак, быстрый запрос без «обвязки»:

SQL> select max(last_y) max_last_y
  2            from (select max(Y) last_y from t_part_pushup)
  3  /

MAX_LAST_Y
----------
       998

Elapsed: 00:00:00.00

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(format => 'all allstats advanced last'));

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |      1 |        |       |  1357 (100)|          |       |       |      1 |00:00:00.01 |       7 |
|   1 |  SORT AGGREGATE               |               |      1 |      1 |    13 |            |          |       |       |      1 |00:00:00.01 |       7 |
|   2 |   VIEW                        |               |      1 |      1 |    13 |  1357   (1)| 00:00:01 |       |       |      1 |00:00:00.01 |       7 |
|   3 |    PARTITION RANGE ALL MIN/MAX|               |      1 |      1 |     4 |            |          |  1000 |     1 |      1 |00:00:00.01 |       7 | -- новая операция*
|   4 |     SORT AGGREGATE            |               |      1 |      1 |     4 |            |          |       |       |      1 |00:00:00.01 |       7 | -- **
|   5 |      TABLE ACCESS FULL        | T_PART_PUSHUP |      1 |   1000 |  4000 |  1357   (1)| 00:00:01 |  1000 |     1 |      1 |00:00:00.01 |       7 |
---------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$2 / from$_subquery$_001@SEL$1
   3 - SEL$2
   5 - SEL$2 / T_PART_PUSHUP@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      DB_VERSION('12.1.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      FULL(@"SEL$2" "T_PART_PUSHUP"@"SEL$2")
      END_OUTLINE_DATA
  */

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets

— выполняется за 7 буферных чтений, логично и эффективно читая партиции в обратном порядке
*) Используется добавленная, имхо, в 12c операция PARTITION RANGE ALL MIN/MAX, в отличие от PARTITION RANGE ALL в версии 11g, что, однако, никак не отменяет проблемы
**) SORT AGGREGATE выполняется после доступа к каждой партиции в отличие от «медленного» запроса, где сортировка выполняется
после чтения всех партиций:

SQL> select *
  2    from (select max(last_y) max_last_y
  3            from (select max(Y) last_y from t_part_pushup))
  4  /

MAX_LAST_Y
----------
       998

Elapsed: 00:00:00.02

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(format => 'all allstats advanced last'));

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |               |      1 |        |       |  1357 (100)|          |    |          |      1 |00:00:00.02 |    7000 |
|   1 |  VIEW                   |               |      1 |      1 |    13 |  1357   (1)| 00:00:01 |    |          |      1 |00:00:00.02 |    7000 |
|   2 |   SORT AGGREGATE        |               |      1 |      1 |    13 |            |          |    |          |      1 |00:00:00.02 |    7000 |
|   3 |    VIEW                 |               |      1 |      1 |    13 |  1357   (1)| 00:00:01 |    |          |      1 |00:00:00.02 |    7000 |
|   4 |     SORT AGGREGATE      |               |      1 |      1 |     4 |            |          |    |          |      1 |00:00:00.02 |    7000 | -- **
|   5 |      PARTITION RANGE ALL|               |      1 |   1000 |  4000 |  1357   (1)| 00:00:01 |  1 |     1000 |   1000 |00:00:00.02 |    7000 |
|   6 |       TABLE ACCESS FULL | T_PART_PUSHUP |   1000 |   1000 |  4000 |  1357   (1)| 00:00:01 |  1 |     1000 |   1000 |00:00:00.02 |    7000 |
---------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$2 / from$_subquery$_001@SEL$1
   2 - SEL$2
   3 - SEL$3 / from$_subquery$_002@SEL$2
   4 - SEL$3
   6 - SEL$3 / T_PART_PUSHUP@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      DB_VERSION('12.1.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      NO_ACCESS(@"SEL$2" "from$_subquery$_002"@"SEL$2")
      FULL(@"SEL$3" "T_PART_PUSHUP"@"SEL$3")
      END_OUTLINE_DATA
  */

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       7000  consistent gets

, что, соответственно, выражается в 1000-кратном увеличении consistent gets (и продолжительности реальных запросов)

Секции Outline Data обоих планов отличаются опять же скорее синтаксически, непринципиально, запрос прост, VIEW MERGING не применяется и, тем не менее, в отличие от первого «быстрого» выполнения во втором оптимизация partition push-up не применяется

Для трассировки операций с партициями попробовал 10128 event:

SQL> alter session set events '10128 trace name context forever, level 14';

Session altered.

SQL> select *
  2    from (select max(last_y) max_last_y
  3            from (select max(Y) last_y from t_part_pushup))
  4  /
          from (select max(Y) last_y from t_part_pushup))
                                          *
ERROR at line 3:
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

— что без предварительного создания локально доступной для записи таблицы kkpap_pruning (How to see Partition Pruning Occurred? (Doc ID 166118.1)) выдаёт тем не менее некие не слишком информативные данные
в трейсе:

kkpapDAAll: heap=cmp, typ=0, lvl=1, met=2
kkpapDCopy: heap=run, typ=0, lvl=1, met=2
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING                      -- для медленного запроса
  Partition iterator for level 1:
   iterator = RANGE [0, 999]
   index = 0
  current partition: part# = 0, subp# = 1048576, abs# = 0

*** 2014-04-19 13:15:56.631
*** SESSION ID:(11.49391) 2014-04-19 13:15:56.631
*** CONTAINER ID:(3) 2014-04-19 13:15:56.631
 
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = DESCENDING                     -- для быстрого запроса
  Partition iterator for level 1:
   iterator = RANGE [0, 999]
   index = 999
  current partition: part# = 999, subp# = 1048576, abs# = 999

А после создания необходимой таблицы:

SQL> create table kkpap_pruning
  2  (
  3  operation_id number,
  4  it_type varchar(5)
  5  CONSTRAINT check_it_type
  6  CHECK (it_type in ('RANGE', 'ARRAY')),
  7  it_level varchar(15)
  8  CONSTRAINT check_it_level
  9  CHECK (it_level in ('PARTITION', 'SUBPARTITION', 'ABSOLUTE')),
 10  it_order varchar(10)
 11  CONSTRAINT check_it_order CHECK (it_order in ('ASCENDING', 'DESCENDING')),
 12  it_call_time varchar(10)
 13  CONSTRAINT check_it_call_time
 14  CHECK (it_call_time in ('COMPILE', 'START', 'RUN')),
 15  pnum number,
 16  spnum number,
 17  apnum number
 18  );

Table created.

SQL> select * from kkpap_pruning;

OPERATION_ID IT_TY IT_LEVEL        IT_ORDER   IT_CALL_TI       PNUM      SPNUM      APNUM
------------ ----- --------------- ---------- ---------- ---------- ---------- ----------
           3 RANGE PARTITION       DESCENDING RUN               999    1048576        999 -- для быстрого запроса
           5 RANGE PARTITION       ASCENDING  RUN                 0    1048576          0 -- для медленного запроса
           5 RANGE PARTITION       ASCENDING  RUN                 1    1048576          1 -- --//--
...
           5 RANGE PARTITION       ASCENDING  RUN               999    1048576        999 -- --//--

1001 rows selected.

— можно найти чуть более подробную но по-прежнему достаточно бесполезную информацию о порядке и использовании партиций, но не о причинах [не]применения partition push-up

Добавление локального индекса по ключу партицирования (partition key) меняет названия операций, никак не влияя на проблему:

SQL> create index t_pp_y_idx on t_part_pushup(y) storage(initial 10k next 10k) local
  2  /

Index created.

SQL> select max(last_y) max_last_y
  2            from (select max(Y) last_y from t_part_pushup)
  3  /

Elapsed: 00:00:00.00

------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |     1 |    13 |     1   (0)| 00:00:01 |       |    |
|   1 |  SORT AGGREGATE               |            |     1 |    13 |            |          |       |    |
|   2 |   VIEW                        |            |     1 |    13 |     1   (0)| 00:00:01 |       |    |
|   3 |    PARTITION RANGE ALL MIN/MAX|            |     1 |     4 |            |          |  1000 |  1 |
|   4 |     SORT AGGREGATE            |            |     1 |     4 |            |          |       |    |
|   5 |      INDEX FULL SCAN (MIN/MAX)| T_PP_Y_IDX |     1 |     4 |     1   (0)| 00:00:01 |  1000 |  1 |
------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets -- быстрый запрос по-прежнему выполняется быстро

SQL> select *
  2    from (select max(last_y) max_last_y
  3            from (select max(Y) last_y from t_part_pushup))
  4  /

Elapsed: 00:00:00.01

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |     1 |    13 |     1   (0)| 00:00:01 |       |       |
|   1 |  VIEW                          |            |     1 |    13 |     1   (0)| 00:00:01 |       |       |
|   2 |   SORT AGGREGATE               |            |     1 |    13 |            |          |       |       |
|   3 |    VIEW                        |            |     1 |    13 |     1   (0)| 00:00:01 |       |       |
|   4 |     SORT AGGREGATE             |            |     1 |     4 |            |          |       |       |
|   5 |      PARTITION RANGE ALL       |            |     1 |     4 |     1   (0)| 00:00:01 |     1 |  1000 |
|   6 |       INDEX FULL SCAN (MIN/MAX)| T_PP_Y_IDX |     1 |     4 |     1   (0)| 00:00:01 |     1 |  1000 |
-------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1000  consistent gets -- а медленный ...

Единственный пока найденный workaround — выделение через материализацию подзапроса к партицированной таблице, требующий изменения текста запроса, но работающий также и в обзоре:

SQL> with q as (select /*+ MATERIALIZE*/ max(Y) last_y from t_part_pushup)
  2  select *
  3    from (select max(last_y) max_last_y
  4            from q)
  5  /

Elapsed: 00:00:00.01

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |     1 |    13 |  1359   (1)| 00:00:01 |       |       |
|   1 |  TEMP TABLE TRANSFORMATION    |                           |       |       |            |          |       |       |
|   2 |   LOAD AS SELECT              | SYS_TEMP_0FD9D6C6D_B0E802 |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ALL MIN/MAX|                           |     1 |     4 |            |          |  1000 |     1 |
|   4 |     SORT AGGREGATE            |                           |     1 |     4 |            |          |       |       |
|   5 |      TABLE ACCESS FULL        | T_PART_PUSHUP             |  1000 |  4000 |  1357   (1)| 00:00:01 |  1000 |     1 |
|   6 |   VIEW                        |                           |     1 |    13 |     2   (0)| 00:00:01 |       |       |
|   7 |    SORT AGGREGATE             |                           |     1 |    13 |            |          |       |       |
|   8 |     VIEW                      |                           |     1 |    13 |     2   (0)| 00:00:01 |       |       |
|   9 |      TABLE ACCESS FULL        | SYS_TEMP_0FD9D6C6D_B0E802 |     1 |     4 |     2   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         11  recursive calls
          9  db block gets
         23  consistent gets

— вариант, конечно, не самый красивый, возможно, есть другие?

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

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

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