select
&column0
decode(x.row_number,null,
&order_by/sum(decode(x.row_number,null,&order_by)) over ()*100
) percentage,
&column1
xxen_util.responsibility(x.module,x.action) responsibility,
xxen_util.module_type(x.module,x.action) module_type,
xxen_util.module_name(x.module) module_name,
x.module,
&column2
&bind_columns
decode(x.row_number,null,x.executions) executions,
xxen_util.time(x.elapsed_time) time,
decode(x.row_number,null,x.elapsed_time) elapsed_time,
decode(x.row_number,null,x.user_io_wait_time) user_io_wait_time,
decode(x.row_number,null,x.cpu_time) cpu_time,
decode(x.row_number,null,(x.user_io_wait_time+x.cpu_time)/xxen_util.zero_to_null(x.cpu_time)) io_factor,
decode(x.row_number,null,x.plsql_exec_time) plsql_exec_time,
decode(x.row_number,null,x.concurrency_wait_time) concurrency_wait_time,
decode(x.row_number,null,x.application_wait_time) application_wait_time,
x.time_exec,
decode(x.row_number,null,x.buffer_io) buffer_io,
decode(x.row_number,null,x.disk_io) disk_io,
x.io_exec,
x.blocks_exec,
x.rows_processed,
x.rows_exec,
x.io_row,
x.blocks_row,
x.io_sec,
case when x.executions>100 then x.buffer_io/(decode(x.last_load_time,x.first_load_time,to_date(null),x.last_load_time)-x.first_load_time)/24/3600 end io_sec_avg,
x.buffer_rate,
x.disk_rate,
x.disk_percentage,
case when x.executions>100 then x.executions/(decode(x.last_load_time,x.first_load_time,to_date(null),x.last_load_time)-x.first_load_time)/24/3600 end execs_per_sec,
case when x.executions>100 then 100*x.elapsed_time/(decode(x.last_load_time,x.first_load_time,to_date(null),x.last_load_time)-x.first_load_time)/24/3600 end time_percentage,
&column3
x.parsing_schema_name schema,
x.parse_calls,
x.sorts,
xxen_util.client_time(x.first_load_time) first_load_time,
xxen_util.client_time(x.last_load_time) last_load_time,
&column4
x.action
from
(
select
decode(row_number() over (partition by x0.inst_num, x0.date_, x0.sql_id, x0.plan_hash_value, x0.capture_date order by x0.position),1,null,2) row_number,
x0.elapsed_time/xxen_util.zero_to_null(x0.executions) time_exec,
x0.buffer_io/xxen_util.zero_to_null(x0.executions) io_exec,
x0.buffer_gets/xxen_util.zero_to_null(x0.executions) blocks_exec,
x0.rows_processed/xxen_util.zero_to_null(x0.executions) rows_exec,
x0.buffer_io/xxen_util.zero_to_null(x0.rows_processed) io_row,
x0.buffer_gets/xxen_util.zero_to_null(x0.rows_processed) blocks_row,
x0.buffer_io/xxen_util.zero_to_null(x0.elapsed_time) io_sec,
(x0.buffer_io-x0.disk_io)/xxen_util.zero_to_null(x0.cpu_time) buffer_rate,
x0.disk_io/xxen_util.zero_to_null(x0.user_io_wait_time) disk_rate,
100*x0.disk_io/xxen_util.zero_to_null(x0.buffer_io) disk_percentage,
x0.*,
dhst.sql_text
from
(
select distinct
case when :aggregate_level like '% instance' then dhss.instance_number end inst_num,
case when :aggregate_level like '% per day%' then dhs.date_ end date_,
case when :aggregate_level like 'SQL%' then max(dhss.module) over (&partition_by) else dhss.module end module,
case when :aggregate_level like 'SQL%' then max(dhss.action) over (&partition_by) else dhss.action end action,
sum(dhss.elapsed_time_delta) over (&partition_by)/1000000 elapsed_time,
sum(dhss.iowait_delta) over (&partition_by)/1000000 user_io_wait_time,
sum(dhss.cpu_time_delta) over (&partition_by)/1000000 cpu_time,
sum(dhss.plsexec_time_delta) over (&partition_by)/1000000 plsql_exec_time,
sum(dhss.ccwait_delta) over (&partition_by)/1000000 concurrency_wait_time,
sum(dhss.apwait_delta) over (&partition_by)/1000000 application_wait_time,
vp.value*sum(dhss.buffer_gets_delta) over (&partition_by)/1000000 buffer_io,
sum(dhss.buffer_gets_delta) over (&partition_by) buffer_gets,
sum(dhss.physical_read_bytes_delta) over (&partition_by)/1000000 disk_io,
sum(dhss.executions_delta) over (&partition_by) executions,
sum(dhss.rows_processed_delta) over (&partition_by) rows_processed,
min(dhss.parsing_schema_name) over (&partition_by) parsing_schema_name,
sum(dhss.parse_calls_delta) over (&partition_by) parse_calls,
sum(dhss.sorts_delta) over (&partition_by) sorts,
min(dhs.begin_interval_time_) over (&partition_by) first_load_time,
max(dhs.end_interval_time_) over (&partition_by) last_load_time,
case when :aggregate_level like 'SQL%' then dhss.sql_id end sql_id,
case when :aggregate_level like 'SQL%' then dhss.plan_hash_value end plan_hash_value,
case when :aggregate_level like 'SQL%' then decode(dhst.command_type,1,'create table',2,'insert',3,'select',6,'update',7,'delete',9,'create index',11,'alter index',26,'lock table',42,'alter session',44,'commit',45,'rollback',46,'savepoint',47,'pl/sql block',48,'set transaction',50,'explain',62,'analyze table',90,'set constraints',170,'call',189,'merge','other') end command_type,
gsa.is_bind_sensitive,
gsa.is_bind_aware,
(select so.name from sys.obj$ so where gsa.program_id=so.obj#) code,
case when gsa.program_line#>0 then gsa.program_line# end code_line#,
dhsb.name bind,
dhsb.value_string bind_value,
dhsb.last_captured capture_date,
dhsb.position,
dhs.dbid
from
(
select trunc(dhs.begin_interval_time) date_,
cast(dhs.begin_interval_time as date) begin_interval_time_,
cast(dhs.end_interval_time as date) end_interval_time_,
dhs.*
from
dba_hist_snapshot dhs
) dhs,
dba_hist_sqlstat dhss,
dba_hist_sqltext dhst,
(
select
wsbm.bind.last_captured last_captured,
case
when wsbm.bind.datatype_string like 'TIMESTAMP%' then to_char(anydata.accesstimestamp(wsbm.bind.value_anydata))
when wsbm.bind.datatype_string='DATE' then to_char(anydata.accessdate(wsbm.bind.value_anydata))
else wsbm.bind.value_string
end value_string,
wsbm.*
from
(
select
ws.dbid,
ws.snap_id,
ws.instance_number,
ws.sql_id,
ws.plan_hash_value,
wsbm.name,
wsbm.position,
dbms_sqltune.extract_bind(ws.bind_data, wsbm.position) bind
from
sys.wrh$_sqlstat ws,
(
select distinct
min(wsbm.position) over (partition by wsbm.dbid, wsbm.sql_id, wsbm.name) position,
wsbm.dbid,
wsbm.sql_id,
wsbm.name
from
sys.wrh$_sql_bind_metadata wsbm
where
'&show_binds'='Y'
) wsbm
where
ws.dbid=wsbm.dbid and
ws.sql_id=wsbm.sql_id
) wsbm
where
wsbm.bind is not null
) dhsb,
(
select distinct
gsa.sql_id,
min(gsa.inst_id) keep (dense_rank first order by gsa.inst_id, gsa.plan_hash_value) over (partition by gsa.sql_id) inst_id,
min(gsa.plan_hash_value) keep (dense_rank first order by gsa.inst_id, gsa.plan_hash_value) over (partition by gsa.sql_id) plan_hash_value
from
gv$sqlarea gsa
where
2=2 and
'&show_sql'='Y'
) gsa0,
gv$sqlarea gsa,
(select vp.value from v$parameter vp where vp.name like 'db_block_size') vp
where
1=1 and
dhs.dbid=dhss.dbid and
dhs.instance_number=dhss.instance_number and
dhs.snap_id=dhss.snap_id and
dhss.dbid=dhst.dbid and
dhss.sql_id=dhst.sql_id and
dhss.sql_id=gsa0.sql_id(+) and
gsa0.sql_id=gsa.sql_id(+) and
gsa0.inst_id=gsa.inst_id(+) and
gsa0.plan_hash_value=gsa.plan_hash_value(+) and
dhss.dbid=dhsb.dbid(+) and
dhss.instance_number=dhsb.instance_number(+) and
dhss.snap_id=dhsb.snap_id(+) and
dhss.sql_id=dhsb.sql_id(+)
) x0,
dba_hist_sqltext dhst
where
x0.dbid=dhst.dbid and
x0.sql_id=dhst.sql_id
) x
order by
case when :aggregate_level in ('Module per day','SQL per day') then x.date_ end desc,
&order_by desc nulls last
&bind_order |
