SQL OUTLINES or PROFILES
set lines 170
set pages 100
col created format a14
col type format a6
col status format a10
col fm format a3
col profile_name format a30
col sql_text format a50
col comp_data format a50
SELECT created,
type,
status,
force_matching fm,
profile_name,
sql_text,
comp_data
FROM DBA_SQL_PROFILES PROF,
DBMSHSXP_SQL_PROFILE_ATTR ATTR
WHERE prof.name = attr.profile_name
ORDER BY status, created desc;
set echo on
set lines 300 pages 0
set trimspool on
--spool
explain_plan.out
select t.*
from v$sql
s,
table(dbms_xplan.display_cursor(s.sql_id,
s.child_number, 'TYPICAL ALLSTATS
LAST')) t
where s.sql_id = '&sql_id';
EXAMPLE OUTPUT
==============
SQL_ID 1fd4dhwcxj9c4, child number 1
-------------------------------------
select shadow from eisaudittrail where id = :1
Plan hash value: 3922317436
----------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EISAUDITTRAIL | 1 | 93 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_AUDITTRAIL | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:1)
