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

Flashback features mindmap

Подборка материалов по управлению планами запросов

Подборка статей про управление планами запросов: SQL profile, SQL Plan Baseline, SQL Patch, Stored Outlines:

1. https://iusoltsev.wordpress.com/2014/12/14/hardcoded-hints-query-plan-spm/
2. http://www.fors.ru/upload/magazine/05/http_texts/russia_ruoug_deev_sql_plans.html
3. https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=108825519711641&id=1931944.1&_afrWindowMode=0&_adf.ctrl-state=y0xdrlgpx_4

dbms_xplan.display

Для тех у кого нет прав вызывать паклеты на prod базе, но есть права на чтение таблиц ниже приведен способ, как с использованием вспомогательной базы данных вывести план запроса в привычном виде.
Основные мысли навеяны статьей http://douggault.blogspot.ru/2009/04/trouble-with-dbmsxplan.html

Итак, план запроса мы будем строть при помощи функции dbms_xplan.display. Описание параметров этой функции такое

DBMS_XPLAN.DISPLAY(
   table_name    IN  VARCHAR2  DEFAULT 'PLAN_TABLE',
   statement_id  IN  VARCHAR2  DEFAULT  NULL, 
   format        IN  VARCHAR2  DEFAULT  'TYPICAL',
   filter_preds  IN  VARCHAR2 DEFAULT NULL);

Теперь вместо первого параметра мы передадим имя представления/материализованного представления/таблицы. Представление строим над копией таблицы v$sql_plan с продакшена. Это позволяет обойти следующие неприятности:

• в dba_hist_sql_plan не копируются колонки filter_predicates и access_predicates
• ошибку ORA-22992: cannot use LOB locators selected from remote tables (немного нечестную, т.к. я не тяну LOB колонок в запросах. Второй вариант ее обхода – с использованием материализованного представления см. ниже).

Текст представления:

CREATE OR REPLACE VIEW vw_plan_table_prod AS 
SELECT
sql_id AS statement_id, 
plan_hash_value AS plan_id, 
timestamp, 
remarks, 
operation, 
options, 
object_node, 
object_owner, 
object_name, 
object_alias, 
OBJECT# object_instance, 
object_type, 
optimizer, 
search_columns, 
id, 
parent_id, 
depth, 
position, 
cost, 
cardinality, 
bytes, 
other_tag, 
partition_start, 
partition_stop, 
partition_id, 
other, 
remarks other_xml, 
distribution, 
cpu_cost, 
io_cost, 
temp_space, 
access_predicates, 
filter_predicates, 
projection, 
time, 
qblock_name,
child_number
FROM v$sql_plan_prod

Для получения плана запроса использовать

SET LINESIZE 300 
SET PAGESIZE 0 
SET HEADING OFF
COLUMN PLAN_TABLE_OUTPUT FORMAT A300 TRUNCATE

SELECT *
FROM   TABLE(dbms_xplan.display(table_name   => 'vw_plan_table_prod',
                                statement_id => '0jhz0hkckw4q4',
                                format       => 'ALL',
                                filter_preds => 'plan_id=1275605462 and child_number=0'));

Дополнительный фильтр в параметре filter_preds опционален и используется, если для запроса построено несколько планов.
Объеснение как работает фильтр и как он трансформирует запрос к таблице с планами в статье, указанной выше.

Вариант 2.

Используем материализованное представление

CREATE MATERIALIZED VIEW vw_plan_table_prod 
REFRESH ON DEMAND 
AS 
SELECT
sql_id AS statement_id, 
plan_hash_value AS plan_id, 
timestamp, 
remarks, 
operation, 
options, 
object_node, 
object_owner, 
object_name, 
object_alias, 
OBJECT# object_instance, 
object_type, 
optimizer, 
search_columns, 
id, 
parent_id, 
depth, 
position, 
cost, 
cardinality, 
bytes, 
other_tag, 
partition_start, 
partition_stop, 
partition_id, 
other, 
remarks other_xml, 
distribution, 
cpu_cost, 
io_cost, 
temp_space, 
access_predicates, 
filter_predicates, 
projection, 
time, 
qblock_name,
child_number
FROM v$sql_plan@loopback
;

SET LINESIZE 300 
SET PAGESIZE 0 
SET HEADING OFF
COLUMN PLAN_TABLE_OUTPUT FORMAT A300 TRUNCATE

SELECT * FROM v$diag_info WHERE NAME = 'Default Trace File';
--ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
BEGIN dbms_mview.refresh(list => 'vw_plan_table_prod', method => 'C', atomic_refresh => FALSE); END;
/

SELECT *
FROM   TABLE(dbms_xplan.display(table_name   => 'vw_plan_table_prod',
                                statement_id => 'gd90ygn1j4026',
                                format       => 'ALL',
                                filter_preds => ''));

Аналогично можно построить запрос для dba_hist_sql_plan

Настройки плана счетов в OEBS

Запрос для получения настроек плана счетов в OEBS для набора книг (или всех наборов книг)

select b.set_of_books_id, b.name, b.chart_of_accounts_id, s.application_column_name, s.segment_name, t.flex_value_set_name
from gl_sets_of_books b, 
  FND_ID_FLEX_SEGMENTS s, 
  FND_FLEX_VALUE_SETS t
WHERE b.chart_of_accounts_id = s.id_flex_num
  AND b.set_of_books_id IN (...)
  AND t.flex_value_set_id = s.flex_value_set_id
ORDER BY b.set_of_books_id, segment_num;