Alter session set sql_trace=true;
Alter session set STATISTICS_LEVEL = ALL;

set echo off verify off termout off
set doc off
doc
— ———————————————————————————————-
—
— Script: xplan_extended_display_cursor.sql
—
— Version: 0.9
— December 2011
—
— Author: Randolf Geist
— oracle-randolf.blogspot.com
—
— Description: A free-standing SQL wrapper over DBMS_XPLAN. Provides access to the
— DBMS_XPLAN.DISPLAY_CURSOR pipelined function for a given SQL_ID and CHILD_NUMBER
—
— This is a prototype for an extended analysis of the data provided by the
— Runtime Profile (aka. Rowsource Statistics enabled via
— SQL_TRACE = TRUE, STATISTICS_LEVEL = ALL or GATHER_PLAN_STATISTICS hint)
— and reported via the ALLSTATS/MEMSTATS/IOSTATS formatting option of
— DBMS_XPLAN.DISPLAY_CURSOR
—
— Versions: This utility will work for all versions of 10g and upwards.
—
— Required: The same access as DBMS_XPLAN.DISPLAY_CURSOR requires. See the documentation
— of DISPLAY_CURSOR for your Oracle version for more information
—
— The script directly queries
— 1) V$SESSION
— 2) V$SQL_PLAN_STATISTICS_ALL
—
— Credits: Based on the original XPLAN implementation by Adrian Billington (http://www.oracle-developer.net/utilities.php
— resp. http://www.oracle-developer.net/content/utilities/xplan.zip)
— and inspired by Kyle Hailey’s TCF query (http://dboptimizer.com/2011/09/20/display_cursor/)
—
— Features: In addition to the PID (The PARENT_ID) and ORD (The order of execution, note that this doesn’t account for the special cases so it might be wrong)
— columns added by Adrian’s wrapper the following additional columns over ALLSTATS are provided:
—
— A_TIME_SELF : The time taken by the operation itself – this is the operation’s cumulative time minus the direct descendants operation’s cumulative time
— LIO_SELF : The LIOs done by the operation itself – this is the operation’s cumulative LIOs minus the direct descendants operation’s cumulative LIOs
— READS_SELF : The reads performed the operation itself – this is the operation’s cumulative reads minus the direct descendants operation’s cumulative reads
— WRITES_SELF : The writes performed the operation itself – this is the operation’s cumulative writes minus the direct descendants operation’s cumulative writes
— A_TIME_SELF_GRAPH : A graphical representation of A_TIME_SELF relative to the total A_TIME
— LIO_SELF_GRAPH : A graphical representation of LIO_SELF relative to the total LIO
— READS_SELF_GRAPH : A graphical representation of READS_SELF relative to the total READS
— WRITES_SELF_GRAPH : A graphical representation of WRITES_SELF relative to the total WRITES
— LIO_RATIO : Ratio of LIOs per row generated by the row source – the higher this ratio the more likely there could be a more efficient way to generate those rows (be aware of aggregation steps though)
— TCF_GRAPH : Each “+”/”-” sign represents one order of magnitude based on ratio between E_ROWS_TIMES_START and A-ROWS. Note that this will be misleading with Parallel Execution (see E_ROWS_TIMES_START)
— E_ROWS_TIMES_START : The E_ROWS multiplied by STARTS – this is useful for understanding the actual cardinality estimate for related combine child operations getting executed multiple times. Note that this will be misleading with Parallel Execution
—
— More information including demos can be found online at http://oracle-randolf.blogspot.com/2011/12/extended-displaycursor-with-rowsource.html
—
— Usage: @xplan_extended_display_cursor.sql [sql_id] [cursor_child_number] [format_option]
—
— If both the SQL_ID and CHILD_NUMBER are omitted the previously executed SQL_ID and CHILD_NUMBER of the session will be used
— If the SQL_ID is specified but the CHILD_NUMBER is omitted then CHILD_NUMBER 0 is assumed
—
— This prototype does not support processing multiple child cursors like DISPLAY_CURSOR is capable of
— when passing NULL as CHILD_NUMBER to DISPLAY_CURSOR. Hence a CHILD_NUMBER is mandatory, either
— implicitly generated (see above) or explicitly passed
—
— The default formatting option for the call to DBMS_XPLAN.DISPLAY_CURSOR is ALLSTATS LAST – extending this output is the primary purpose of this script
—
— Note: You need a veeery wide terminal setting for this prototype, something like linesize 400 should suffice
—
— This tool is free but comes with no warranty at all – use at your own risk
—
#

col plan_table_output format a400
set linesize 400 pagesize 0 tab off

/* ALLSTATS LAST is assumed as the default formatting option for DBMS_XPLAN.DISPLAY_CURSOR */
define default_fo = “ALLSTATS LAST”

column prev_sql_id new_value prev_sql_id
column prev_child_number new_value prev_cn

/* Get the previous command as default
if no SQL_ID / CHILD_NUMBER is passed */
select
prev_sql_id
, prev_child_number
from
v$session
where
sid = userenv(‘sid’)
;

— The following is a hack to use default
— values for defines
column 1 new_value 1
column 2 new_value 2
column 3 new_value 3

select
” as “1”
, ” as “2”
, ” as “3”
from
dual
where
rownum = 0;

column si new_value si
column cn new_value cn
column fo new_value fo

/* Use passed parameters else refer to previous SQL_ID / CHILD_NUMBER
ALLSTATS LAST is default formatting option */
select
nvl(‘&1’, ‘&prev_sql_id’) as si
, coalesce(‘&2’, ‘&prev_cn’, ‘0’) as cn
, nvl(‘&3’, ‘&default_fo’) as fo
from
dual
;

column last new_value last

/* Last or all execution */
select
case
when instr(‘&fo’, ‘LAST’) > 0
then ‘last_’
end as last
from
dual
;

set termout on

with
— The next three queries are based on the original XPLAN wrapper by Adrian Billington
— to determine the PID and ORD information, only slightly modified to deal with
— the 10g special case that V$SQL_PLAN_STATISTICS_ALL doesn’t include the ID = 0 operation
— and starts with 1 instead for Rowsource Statistics
sql_plan_data as
(
select
id
, parent_id
from
v$sql_plan_statistics_all
where
sql_id = ‘&si’
and child_number = &cn
),
hierarchy_data as
(
select
id
, parent_id
from
sql_plan_data
start with
id in
(
select
id
from
sql_plan_data p1
where
not exists
(
select
null
from
sql_plan_data p2
where
p2.id = p1.parent_id
)
)
connect by
prior id = parent_id
order siblings by
id desc
),
ordered_hierarchy_data as
(
select
id
, parent_id as pid
, row_number() over (order by rownum desc) as oid
, max(id) over () as maxid
, min(id) over () as minid
from
hierarchy_data
),
— The following query uses the MAX values
— rather than taking the values of PLAN OPERATION_ID = 0 (or 1 for 10g V$SQL_PLAN_STATISTICS_ALL)
— for determining the grand totals
—
— This is because queries that get cancelled do not
— necessarily have yet sensible values in the root plan operation
—
— Furthermore with Parallel Execution the elapsed time accumulated
— with the ALLSTATS option for operations performed in parallel
— will be greater than the wallclock elapsed time shown for the Query Coordinator
—
— Note that if you use GATHER_PLAN_STATISTICS with the default
— row sampling frequency the (LAST_)ELAPSED_TIME will be very likely
— wrong and hence the time-based graphs and self-statistics will be misleading
—
— Similar things might happen when cancelling queries
—
— For queries running with STATISTICS_LEVEL = ALL (or sample frequency set to 1)
— the A-TIME is pretty reliable
totals as
(
select
max(&last.cu_buffer_gets + &last.cr_buffer_gets) as total_lio
, max(&last.elapsed_time) as total_elapsed
, max(&last.disk_reads) as total_reads
, max(&last.disk_writes) as total_writes
from
v$sql_plan_statistics_all
where
sql_id = ‘&si’
and child_number = &cn
),
— The totals for the direct descendants of an operation
— These are required for calculating the work performed
— by a (parent) operation itself
— Basically this is the SUM grouped by PARENT_ID
direct_desc_totals as
(
select
sum(&last.cu_buffer_gets + &last.cr_buffer_gets) as lio
, sum(&last.elapsed_time) as elapsed
, sum(&last.disk_reads) as reads
, sum(&last.disk_writes) as writes
, parent_id
from
v$sql_plan_statistics_all
where
sql_id = ‘&si’
and child_number = &cn
group by
parent_id
),
— Putting the three together
— The statistics, direct descendant totals plus totals
extended_stats as
(
select
stats.id
, stats.parent_id
, stats.&last.elapsed_time as elapsed
, (stats.&last.cu_buffer_gets + stats.&last.cr_buffer_gets) as lio
, stats.&last.starts as starts
, stats.&last.output_rows as a_rows
, stats.cardinality as e_rows
, stats.&last.disk_reads as reads
, stats.&last.disk_writes as writes
, ddt.elapsed as ddt_elapsed
, ddt.lio as ddt_lio
, ddt.reads as ddt_reads
, ddt.writes as ddt_writes
, t.total_elapsed
, t.total_lio
, t.total_reads
, t.total_writes
from
v$sql_plan_statistics_all stats
, direct_desc_totals ddt
, totals t
where
stats.sql_id=’&si’
and stats.child_number = &cn
and ddt.parent_id (+) = stats.id
),
— Further information derived from above
derived_stats as
(
select
id
, greatest(elapsed – nvl(ddt_elapsed , 0), 0) as elapsed_self
, greatest(lio – nvl(ddt_lio, 0), 0) as lio_self
, trunc((greatest(lio – nvl(ddt_lio, 0), 0)) / nullif(a_rows, 0)) as lio_ratio
, greatest(reads – nvl(ddt_reads, 0), 0) as reads_self
, greatest(writes – nvl(ddt_writes,0) ,0) as writes_self
, total_elapsed
, total_lio
, total_reads
, total_writes
, trunc(log(10, nullif(starts * e_rows / nullif(a_rows, 0), 0))) as tcf_ratio
, starts * e_rows as e_rows_times_start
from
extended_stats
),
/* Format the data as required */
formatted_data1 as
(
select
id
, lio_ratio
, total_elapsed
, total_lio
, total_reads
, total_writes
, to_char(numtodsinterval(round(elapsed_self / 10000) * 10000 / 1000000, ‘SECOND’)) as e_time_interval
/* Imitate the DBMS_XPLAN number formatting */
, case
when lio_self >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, ‘FM99999’) || ‘E’
when lio_self >= 10000000000000000000 then to_char(lio_self/1000000000000000000, ‘FM99999’) || ‘E’
when lio_self >= 10000000000000000 then to_char(lio_self/1000000000000000, ‘FM99999’) || ‘P’
when lio_self >= 10000000000000 then to_char(lio_self/1000000000000, ‘FM99999’) || ‘T’
when lio_self >= 10000000000 then to_char(lio_self/1000000000, ‘FM99999’) || ‘G’
when lio_self >= 10000000 then to_char(lio_self/1000000, ‘FM99999’) || ‘M’
when lio_self >= 100000 then to_char(lio_self/1000, ‘FM99999’) || ‘K’
else to_char(lio_self, ‘FM99999’) || ‘ ‘
end as lio_self_format
, case
when reads_self >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, ‘FM99999’) || ‘E’
when reads_self >= 10000000000000000000 then to_char(reads_self/1000000000000000000, ‘FM99999’) || ‘E’
when reads_self >= 10000000000000000 then to_char(reads_self/1000000000000000, ‘FM99999’) || ‘P’
when reads_self >= 10000000000000 then to_char(reads_self/1000000000000, ‘FM99999’) || ‘T’
when reads_self >= 10000000000 then to_char(reads_self/1000000000, ‘FM99999’) || ‘G’
when reads_self >= 10000000 then to_char(reads_self/1000000, ‘FM99999’) || ‘M’
when reads_self >= 100000 then to_char(reads_self/1000, ‘FM99999’) || ‘K’
else to_char(reads_self, ‘FM99999’) || ‘ ‘
end as reads_self_format
, case
when writes_self >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, ‘FM99999’) || ‘E’
when writes_self >= 10000000000000000000 then to_char(writes_self/1000000000000000000, ‘FM99999’) || ‘E’
when writes_self >= 10000000000000000 then to_char(writes_self/1000000000000000, ‘FM99999’) || ‘P’
when writes_self >= 10000000000000 then to_char(writes_self/1000000000000, ‘FM99999’) || ‘T’
when writes_self >= 10000000000 then to_char(writes_self/1000000000, ‘FM99999’) || ‘G’
when writes_self >= 10000000 then to_char(writes_self/1000000, ‘FM99999’) || ‘M’
when writes_self >= 100000 then to_char(writes_self/1000, ‘FM99999’) || ‘K’
else to_char(writes_self, ‘FM99999’) || ‘ ‘
end as writes_self_format
, case
when e_rows_times_start >= 18000000000000000000 then to_char(18000000000000000000/1000000000000000000, ‘FM99999’) || ‘E’
when e_rows_times_start >= 10000000000000000000 then to_char(e_rows_times_start/1000000000000000000, ‘FM99999’) || ‘E’
when e_rows_times_start >= 10000000000000000 then to_char(e_rows_times_start/1000000000000000, ‘FM99999’) || ‘P’
when e_rows_times_start >= 10000000000000 then to_char(e_rows_times_start/1000000000000, ‘FM99999’) || ‘T’
when e_rows_times_start >= 10000000000 then to_char(e_rows_times_start/1000000000, ‘FM99999’) || ‘G’
when e_rows_times_start >= 10000000 then to_char(e_rows_times_start/1000000, ‘FM99999’) || ‘M’
when e_rows_times_start >= 100000 then to_char(e_rows_times_start/1000, ‘FM99999’) || ‘K’
else to_char(e_rows_times_start, ‘FM99999’) || ‘ ‘
end as e_rows_times_start_format
, rpad(‘ ‘, nvl(round(elapsed_self / nullif(total_elapsed, 0) * 12), 0) + 1, ‘@’) as elapsed_self_graph
, rpad(‘ ‘, nvl(round(lio_self / nullif(total_lio, 0) * 12), 0) + 1, ‘@’) as lio_self_graph
, rpad(‘ ‘, nvl(round(reads_self / nullif(total_reads, 0) * 12), 0) + 1, ‘@’) as reads_self_graph
, rpad(‘ ‘, nvl(round(writes_self / nullif(total_writes, 0) * 12), 0) + 1, ‘@’) as writes_self_graph
, ‘ ‘ ||
case
when tcf_ratio > 0
then rpad(‘-‘, tcf_ratio, ‘-‘)
else rpad(‘+’, tcf_ratio * -1, ‘+’)
end as tcf_graph
from
derived_stats
),
/* The final formatted data */
formatted_data as
(
select
/*+ Convert the INTERVAL representation to the A-TIME representation used by DBMS_XPLAN
by turning the days into hours */
to_char(to_number(substr(e_time_interval, 2, 9)) * 24 + to_number(substr(e_time_interval, 12, 2)), ‘FM900’) ||
substr(e_time_interval, 14, 9)
as a_time_self
, a.*
from
formatted_data1 a
),
/* Combine the information with the original DBMS_XPLAN output */
xplan_data as (
select
x.plan_table_output
, o.id
, o.pid
, o.oid
, o.maxid
, o.minid
, a.a_time_self
, a.lio_self_format
, a.reads_self_format
, a.writes_self_format
, a.elapsed_self_graph
, a.lio_self_graph
, a.reads_self_graph
, a.writes_self_graph
, a.lio_ratio
, a.tcf_graph
, a.total_elapsed
, a.total_lio
, a.total_reads
, a.total_writes
, a.e_rows_times_start_format
, x.rn
from
(
select /* Take advantage of 11g table function dynamic sampling */
/*+ dynamic_sampling(dc, 2) */
/* This ROWNUM determines the order of the output/processing */
rownum as rn
, plan_table_output
from
table(dbms_xplan.display_cursor(‘&si’,&cn, ‘&fo’)) dc
) x
, ordered_hierarchy_data o
, formatted_data a
where
o.id (+) = case
when regexp_like(x.plan_table_output, ‘^\|[\* 0-9]+\|’)
then to_number(regexp_substr(x.plan_table_output, ‘[0-9]+’))
end
and a.id (+) = case
when regexp_like(x.plan_table_output, ‘^\|[\* 0-9]+\|’)
then to_number(regexp_substr(x.plan_table_output, ‘[0-9]+’))
end
)
/* Inject the additional data into the original DBMS_XPLAN output
by using the MODEL clause */
select
plan_table_output
from
xplan_data
model
dimension by (rn as r)
measures
(
cast(plan_table_output as varchar2(4000)) as plan_table_output
, id
, maxid
, minid
, pid
, oid
, a_time_self
, lio_self_format
, reads_self_format
, writes_self_format
, e_rows_times_start_format
, elapsed_self_graph
, lio_self_graph
, reads_self_graph
, writes_self_graph
, lio_ratio
, tcf_graph
, total_elapsed
, total_lio
, total_reads
, total_writes
, greatest(max(length(maxid)) over () + 3, 6) as csize
, cast(null as varchar2(128)) as inject
, cast(null as varchar2(4000)) as inject2
)
rules sequential order
(
/* Prepare the injection of the OID / PID info */
inject[r] = case
/* MINID/MAXID are the same for all rows
so it doesn’t really matter
which offset we refer to */
when id[cv(r)+1] = minid[cv(r)+1]
or id[cv(r)+3] = minid[cv(r)+3]
or id[cv(r)-1] = maxid[cv(r)-1]
then rpad(‘-‘, csize[cv()]*2, ‘-‘)
when id[cv(r)+2] = minid[cv(r)+2]
then ‘|’ || lpad(‘Pid |’, csize[cv()]) || lpad(‘Ord |’, csize[cv()])
when id[cv()] is not null
then ‘|’ || lpad(pid[cv()] || ‘ |’, csize[cv()]) || lpad(oid[cv()] || ‘ |’, csize[cv()])
end
/* Prepare the injection of the remaining info */
, inject2[r] = case
when id[cv(r)+1] = minid[cv(r)+1]
or id[cv(r)+3] = minid[cv(r)+3]
or id[cv(r)-1] = maxid[cv(r)-1]
then rpad(‘-‘,
case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then
14 else 0 end /* A_TIME_SELF */ +
case when coalesce(total_lio[cv(r)+1], total_lio[cv(r)+3], total_lio[cv(r)-1]) > 0 then
11 else 0 end /* LIO_SELF */ +
case when coalesce(total_reads[cv(r)+1], total_reads[cv(r)+3], total_reads[cv(r)-1]) > 0 then
11 else 0 end /* READS_SELF */ +
case when coalesce(total_writes[cv(r)+1], total_writes[cv(r)+3], total_writes[cv(r)-1]) > 0 then
11 else 0 end /* WRITES_SELF */ +
case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then
14 else 0 end /* A_TIME_SELF_GRAPH */ +
case when coalesce(total_lio[cv(r)+1], total_lio[cv(r)+3], total_lio[cv(r)-1]) > 0 then
14 else 0 end /* LIO_SELF_GRAPH */ +
case when coalesce(total_reads[cv(r)+1], total_reads[cv(r)+3], total_reads[cv(r)-1]) > 0 then
14 else 0 end /* READS_SELF_GRAPH */ +
case when coalesce(total_writes[cv(r)+1], total_writes[cv(r)+3], total_writes[cv(r)-1]) > 0 then
14 else 0 end /* WRITES_SELF_GRAPH */ +
case when coalesce(total_lio[cv(r)+1], total_lio[cv(r)+3], total_lio[cv(r)-1]) > 0 then
11 else 0 end /* LIO_RATIO */ +
case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then
11 else 0 end /* TCF_GRAPH */ +
case when coalesce(total_elapsed[cv(r)+1], total_elapsed[cv(r)+3], total_elapsed[cv(r)-1]) > 0 then
11 else 0 end /* E_ROWS_TIMES_START */
, ‘-‘)
when id[cv(r)+2] = minid[cv(r)+2]
then case when total_elapsed[cv(r)+2] > 0 then
lpad(‘A-Time Self |’ , 14) end ||
case when total_lio[cv(r)+2] > 0 then
lpad(‘Bufs Self |’ , 11) end ||
case when total_reads[cv(r)+2] > 0 then
lpad(‘Reads Self|’ , 11) end ||
case when total_writes[cv(r)+2] > 0 then
lpad(‘Write Self|’ , 11) end ||
case when total_elapsed[cv(r)+2] > 0 then
lpad(‘A-Ti S-Graph |’, 14) end ||
case when total_lio[cv(r)+2] > 0 then
lpad(‘Bufs S-Graph |’, 14) end ||
case when total_reads[cv(r)+2] > 0 then
lpad(‘Reads S-Graph|’, 14) end ||
case when total_writes[cv(r)+2] > 0 then
lpad(‘Write S-Graph|’, 14) end ||
case when total_lio[cv(r)+2] > 0 then
lpad(‘LIO Ratio |’ , 11) end ||
case when total_elapsed[cv(r)+2] > 0 then
lpad(‘TCF Graph |’ , 11) end ||
case when total_elapsed[cv(r)+2] > 0 then
lpad(‘E-Rows*Sta|’ , 11) end
when id[cv()] is not null
then case when total_elapsed[cv()] > 0 then
lpad(a_time_self[cv()] || ‘ |’, 14) end ||
case when total_lio[cv()] > 0 then
lpad(lio_self_format[cv()] || ‘|’, 11) end ||
case when total_reads[cv()] > 0 then
lpad(reads_self_format[cv()] || ‘|’, 11) end ||
case when total_writes[cv()] > 0 then
lpad(writes_self_format[cv()] || ‘|’, 11) end ||
case when total_elapsed[cv()] > 0 then
rpad(elapsed_self_graph[cv()], 13) || ‘|’ end ||
case when total_lio[cv()] > 0 then
rpad(lio_self_graph[cv()], 13) || ‘|’ end ||
case when total_reads[cv()] > 0 then
rpad(reads_self_graph[cv()], 13) || ‘|’ end ||
case when total_writes[cv()] > 0 then
rpad(writes_self_graph[cv()], 13) || ‘|’ end ||
case when total_lio[cv()] > 0 then
lpad(lio_ratio[cv()] || ‘ |’, 11) end ||
case when total_elapsed[cv()] > 0 then
rpad(tcf_graph[cv()], 9) || ‘ |’ end ||
case when total_elapsed[cv()] > 0 then
lpad(e_rows_times_start_format[cv()] || ‘|’, 11) end
end
/* Putting it all together */
, plan_table_output[r] = case
when inject[cv()] like ‘—%’
then inject[cv()] || plan_table_output[cv()] || inject2[cv()]
when inject[cv()] is present
then regexp_replace(plan_table_output[cv()], ‘\|’, inject[cv()], 1, 2) || inject2[cv()]
else plan_table_output[cv()]
end
)
order by
r
;

undefine default_fo
undefine prev_sql_id
undefine prev_cn
undefine last
undefine si
undefine cn
undefine fo
undefine 1
undefine 2
undefine 3

col plan_table_output clear
col prev_sql_id clear
col prev_child_number clear
col si clear
col cn clear
col fo clear
col last clear

EXAMPLE:

[oracle@testdb2 ~]$ sqlplus ‘/as sysdba’

SQL*Plus: Release 10.2.0.5.0 – Production on Mon Jan 9 12:58:57 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> Alter session set sql_trace=true;
Alter session set STATISTICS_LEVEL = ALL;
Session altered.

SQL>

Session altered.

SQL>
SQL> Alter session set sql_trace=true;

Session altered.

SQL> select count (*) from dba_objects;

COUNT(*)
———-
31094

SQL> @ xplan_extended_display_cursor 2jf3kh52jy9fd 0
SQL_ID 2jf3kh52jy9fd, child number 0
————————————-
select count (*) from dba_objects

Plan hash value: 2476103605

————————————————————————————————————————————————————————————————————————————–
| Id | Pid | Ord | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | A-Time Self |Bufs Self |A-Ti S-Graph |Bufs S-Graph |LIO Ratio |TCF Graph |E-Rows*Sta|
————————————————————————————————————————————————————————————————————————————–
| 0 | | 13 | SELECT STATEMENT | | 1 | | 1 |00:00:00.77 | 12166 | | | | 00:00:00.00 | 0 | | | 0 | | |
| 1 | 0 | 12 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.77 | 12166 | | | | 00:00:00.02 | 0 | | | 0 | | 1 |
| 2 | 1 | 11 | VIEW | DBA_OBJECTS | 1 | 25931 | 31094 |00:00:00.75 | 12166 | | | | 00:00:00.09 | 0 | @ | | 0 | | 25931 |
| 3 | 2 | 10 | UNION-ALL | | 1 | | 31094 |00:00:00.66 | 12166 | | | | 00:00:00.12 | 0 | @@ | | 0 | | |
|* 4 | 3 | 6 | FILTER | | 1 | | 31078 |00:00:00.53 | 12159 | | | | 00:00:00.08 | 0 | @ | | 0 | | |
|* 5 | 4 | 3 | HASH JOIN | | 1 | 32255 | 32291 |00:00:00.36 | 443 | 1593K| 1593K| 1477K (0)| 00:00:00.25 | 0 | @@@@ | | 0 | | 32255 |
| 6 | 5 | 1 | TABLE ACCESS FULL | USER$ | 1 | 65 | 66 |00:00:00.01 | 6 | | | | 00:00:00.00 | 6 | | | 0 | | 65 |
|* 7 | 5 | 2 | TABLE ACCESS FULL | OBJ$ | 1 | 32255 | 32291 |00:00:00.11 | 437 | | | | 00:00:00.11 | 437 | @@ | | 0 | | 32255 |
|* 8 | 4 | 5 | TABLE ACCESS BY INDEX ROWID| IND$ | 5855 | 1 | 5184 |00:00:00.10 | 11716 | | | | 00:00:00.06 | 5859 | @ | @@@@@@ | 1 | | 5855 |
|* 9 | 8 | 4 | INDEX UNIQUE SCAN | I_IND1 | 5855 | 1 | 5855 |00:00:00.04 | 5857 | | | | 00:00:00.04 | 5857 | @ | @@@@@@ | 1 | | 5855 |
|* 10 | 3 | 9 | HASH JOIN | | 1 | 16 | 16 |00:00:00.01 | 7 | 1517K| 1517K| 606K (0)| 00:00:00.00 | 0 | | | 0 | | 16 |
| 11 | 10 | 7 | INDEX FULL SCAN | I_LINK1 | 1 | 16 | 16 |00:00:00.01 | 1 | | | | 00:00:00.00 | 1 | | | 0 | | 16 |
| 12 | 10 | 8 | TABLE ACCESS FULL | USER$ | 1 | 65 | 66 |00:00:00.01 | 6 | | | | 00:00:00.00 | 6 | | | 0 | | 65 |
————————————————————————————————————————————————————————————————————————————–

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

4 – filter(((“O”.”TYPE#”<>1 AND “O”.”TYPE#”<>10) OR (“O”.”TYPE#”=1 AND =1)))
5 – access(“O”.”OWNER#”=”U”.”USER#”)
7 – filter((“O”.”LINKNAME” IS NULL AND BITAND(“O”.”FLAGS”,128)=0 AND “O”.”NAME”<>‘_NEXT_OBJECT’ AND
“O”.”NAME”<>‘_default_auditing_options_’))
8 – filter((“I”.”TYPE#”=1 OR “I”.”TYPE#”=2 OR “I”.”TYPE#”=3 OR “I”.”TYPE#”=4 OR “I”.”TYPE#”=6 OR “I”.”TYPE#”=7 OR
“I”.”TYPE#”=9))
9 – access(“I”.”OBJ#”=:B1)
10 – access(“L”.”OWNER#”=”U”.”USER#”)

36 rows selected.