среда, 25 марта 2015 г.

Partition pruning monitoring

Зачастую partition pruning не так просто увидеть в плане запроса: если используются bind переменные или подзапрос, то в плане будет стоять pstart и pstop KEY. Для этих случев Oracle сделал отдельный эвент 10128. Для его использования необходимо создать таблицу kkpap_pruning. Результаты можно просматривать в файле, а можно и в таблице. К сожалению внятной информации, как интерпретировать трейсы найти не удалось, так что ниже небольшое исследование:

Ниже приведены примеры для 4 запросов:
* без PP
* PP для одной партиции по равенству
* PP для 2 партиций для случая >=
* отсутствие PP для !=
* пример с bind-переменными, для которого по event мы увидим, какой PP имел место.
* пример с join таблиц
Инициализируем окружение и создаем объекты при помощи скрипта

SET SERVEROUTPUT OFF
SET PAGESIZE 0 FEEDBACK OFF
SET LINESIZE 32000
COL plan_table_output FORMAT a300


drop table kkpap_pruning;
DROP TABLE part_test;

create table kkpap_pruning 
(partition_count  NUMBER
,iterator         VARCHAR2(32)
,partition_level  VARCHAR2(32)
,order_pt         VARCHAR2(12)
,call_time        VARCHAR2(12)
,part#            NUMBER
,subp#            NUMBER
,abs#             NUMBER
);


CREATE TABLE part_test (
  id1 NUMBER NOT NULL,
  pad VARCHAR2(1000),
  val NUMBER)
PARTITION BY RANGE (id1) (
  PARTITION p1 VALUES LESS THAN (1),
  PARTITION p2 VALUES LESS THAN (2),
  PARTITION p3 VALUES LESS THAN (3),
  PARTITION p4 VALUES LESS THAN (4)
);

INSERT INTO part_test
SELECT MOD(ROWNUM, 4) , LPAD('x', 1000, 'x'), ROWNUM
FROM dual
CONNECT BY LEVEL <= 1000;  

BEGIN dbms_stats.gather_table_stats(USER, 'PART_TEST'); END;
/

NB: SET SERVEROUTPUT OFF необходим для того, что бы dbms_xplan.display_cursor работал правильно.

Пример 1. Нет PP, т.к. нет фильтрации

-- Без предикатов
alter session set events '10128 trace name context forever, level 2';
SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
);

SELECT * FROM TABLE(dbms_xplan.display_cursor());
------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |       |    32 (100)|          |       |       |
|   1 |  SORT AGGREGATE      |           |     1 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|           |  1000 |    32   (0)| 00:00:01 |     1 |     4 |
|   3 |    TABLE ACCESS FULL | PART_TEST |  1000 |    32   (0)| 00:00:01 |     1 |     4 |
------------------------------------------------------------------------------------------
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [0, 3]
   index = 0
  current partition: part# = 0, subp# = 1048576, abs# = 0
  current partition: part# = 1, subp# = 1048576, abs# = 1
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3

здесь и далее приведены части трейса, которые относятся к стадии выполнения call time = RUN. Как видно, в первом случае мы посещаем все 4 партиции нашей таблицы, что видно и из плана выполнения и из трейса

Пример 2. Равенство

SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1=1
);
SELECT * FROM TABLE(dbms_xplan.display_cursor());
-----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |       |       |     9 (100)|          |       |       |
|   1 |  SORT AGGREGATE         |           |     1 |     3 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|           |   250 |   750 |     9   (0)| 00:00:01 |     2 |     2 |
|*  3 |    TABLE ACCESS FULL    | PART_TEST |   250 |   750 |     9   (0)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------------

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

   3 - filter("ID1"=1)
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [1, 1]
   index = 1
  current partition: part# = 1, subp# = 1048576, abs# = 1   

Пример 3. Неравенство с 2 партициями

SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1>=2
);
SELECT * FROM TABLE(dbms_xplan.display_cursor());

-------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |       |       |    17 (100)|          |       |       |
|   1 |  SORT AGGREGATE           |           |     1 |     3 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|           |   583 |  1749 |    17   (0)| 00:00:01 |     3 |     4 |
|   3 |    TABLE ACCESS FULL      | PART_TEST |   583 |  1749 |    17   (0)| 00:00:01 |     3 |     4 |
-------------------------------------------------------------------------------------------------------
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [2, 3]
   index = 2
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3

Пример 4. Отсутвие PP для !

SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1!=2
);
SELECT * FROM TABLE(dbms_xplan.display_cursor());
--------------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |       |       |    32 (100)|          |       |       |
|   1 |  SORT AGGREGATE      |           |     1 |     3 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|           |   750 |  2250 |    32   (0)| 00:00:01 |     1 |     4 |
|*  3 |    TABLE ACCESS FULL | PART_TEST |   750 |  2250 |    32   (0)| 00:00:01 |     1 |     4 |
--------------------------------------------------------------------------------------------------
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [0, 3]
   index = 0
  current partition: part# = 0, subp# = 1048576, abs# = 0
  current partition: part# = 1, subp# = 1048576, abs# = 1
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3

Пример 6. PP с bind-переменными для 2 партиций

VAR pstart NUMBER
VAR pstop NUMBER
EXEC :pstart := 2;
EXEC :pstop := 3;
SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1 BETWEEN :pstart AND :pstop
);
SELECT * FROM TABLE(dbms_xplan.display_cursor());
--------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |       |       |    32 (100)|          |       |       |
|   1 |  SORT AGGREGATE            |           |     1 |     3 |            |          |       |       |
|*  2 |   FILTER                   |           |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|           |   583 |  1749 |    32   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL      | PART_TEST |   583 |  1749 |    32   (0)| 00:00:01 |   KEY |   KEY |
--------------------------------------------------------------------------------------------------------

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

   2 - filter(:PSTOP>=:PSTART)
   4 - filter(("ID1">=:PSTART AND "ID1"<=:PSTOP))

Partition Iterator Information:
  partition level = PARTITION
  call time = COMPILE
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [0, 3]
   index = 0
  current partition: part# = 0, subp# = 1048576, abs# = 0
  current partition: part# = 1, subp# = 1048576, abs# = 1
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [2, 3]
   index = 2
  current partition: part# = 2, subp# = 1048576, abs# = 2
  current partition: part# = 3, subp# = 1048576, abs# = 3   

Для этого случая я оставил в трейсе часть от call time = COMPILE. Как видно, из нее вообще ничего не понятно.

Результаты запроса

select * from KKPAP_PRUNING t;

PARTITION_COUNT ITERATOR                         PARTITION_LEVEL                  ORDER_PT     CALL_TIME         PART#      SUBP#       ABS#
--------------- -------------------------------- -------------------------------- ------------ ------------ ---------- ---------- ----------
              2 RANGE                            PARTITION                        ASCENDING    RUN               0        1048576          0
              2 RANGE                            PARTITION                        ASCENDING    RUN               1        1048576          1
              2 RANGE                            PARTITION                        ASCENDING    RUN               2        1048576          2
              2 RANGE                            PARTITION                        ASCENDING    RUN               3        1048576          3
              2 RANGE                            PARTITION                        ASCENDING    RUN               1        1048576          1
              2 RANGE                            PARTITION                        ASCENDING    RUN               2        1048576          2
              2 RANGE                            PARTITION                        ASCENDING    RUN               3        1048576          3
              2 RANGE                            PARTITION                        ASCENDING    RUN               0        1048576          0
              2 RANGE                            PARTITION                        ASCENDING    RUN               1        1048576          1
              2 RANGE                            PARTITION                        ASCENDING    RUN               2        1048576          2
              2 RANGE                            PARTITION                        ASCENDING    RUN               3        1048576          3
              3 RANGE                            PARTITION                        ASCENDING    RUN               2        1048576          2
              3 RANGE                            PARTITION                        ASCENDING    RUN               3        1048576          3

Пример 5. join

SELECT COUNT(*) FROM (
  SELECT * 
  FROM part_test p
  WHERE id1 IN (
      SELECT ROWNUM FROM dual CONNECT BY LEVEL <=2)
);

SELECT * FROM TABLE(dbms_xplan.display_cursor());

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |           |       |       |    11 (100)|          |       |       |
|   1 |  SORT AGGREGATE                   |           |     1 |    16 |            |          |       |       |
|   2 |   NESTED LOOPS                    |           |   250 |  4000 |    11  (10)| 00:00:01 |       |       |
|   3 |    VIEW                           | VW_NSO_1  |     1 |    13 |     3  (34)| 00:00:01 |       |       |
|   4 |     HASH UNIQUE                   |           |     1 |       |     3  (34)| 00:00:01 |       |       |
|   5 |      COUNT                        |           |       |       |            |          |       |       |
|   6 |       CONNECT BY WITHOUT FILTERING|           |       |       |            |          |       |       |
|   7 |        FAST DUAL                  |           |     1 |       |     2   (0)| 00:00:01 |       |       |
|   8 |    PARTITION RANGE ITERATOR       |           |   250 |   750 |     8   (0)| 00:00:01 |   KEY |   KEY |
|*  9 |     TABLE ACCESS FULL             | PART_TEST |   250 |   750 |     8   (0)| 00:00:01 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------------------

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

   9 - filter("ID1"="ROWNUM")

Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [1, 1]
   index = 1
  current partition: part# = 1, subp# = 1048576, abs# = 1
Partition Iterator Information:
  partition level = PARTITION
  call time = RUN
  order = ASCENDING
  Partition iterator for level 1:
   iterator = RANGE [2, 2]
   index = 2
  current partition: part# = 2, subp# = 1048576, abs# = 2

Выводы

Кратко можно заключить следующее:
1. В трейсе смотреть на блоки, относящиеся к call time = RUN
2. Какие партиции посещались видно с строке iterator = RANGE [2, 3] и в строках current partition: part# = 2, subp# = 1048576, abs# = 2.
3. Если нет доступа к файловой системе сервера, то результаты можно посмотреть и в таблице KKPAP_PRUNING.
4. К сожалению, ни названий таблиц, ни номеров объектов в файле и таблице нет, поэтому разобраться со сложным запросом будет очень тяжело. Наверное придется ориентироваться на количество партиций и на порядок операций

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