Зачастую 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. К сожалению, ни названий таблиц, ни номеров объектов в файле и таблице нет, поэтому разобраться со сложным запросом будет очень тяжело. Наверное придется ориентироваться на количество партиций и на порядок операций
Комментариев нет:
Отправить комментарий