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