Reports 2017-11-18T12:27:27+00:00

DBA AWR Active Session History

Description
Categories: DBA, Enginatics
Active session history from the automatic workload repository

select
y.*
from
(
select
x.sample_time,
x.sid_serial#,
xxen_util.user_name(x.client_id) user_name,
xxen_util.responsibility(x.action) responsibility,
xxen_util.module_type(x.module) module_type,
xxen_util.module_name(x.module) module_name,
case when lower(x.module) like '%frm%' then 'Forms' when lower(x.module) like '%fwk%' then 'OAF' end ui_type,
x.module,
x.program,
x.program_line#,
x.sql_id,
x.plan_hash_value,
case when x.count_sql_id>1 then 10*x.count_sql_id end elapsed_time,
round(100*x.count_sql_id/x.count,2) percentage,
x.sql_text,
x.execution_count,
x.sql_exec_start,
x.entry_procedure,
x.procedure,
x.sess_elapsed_time,
x.sess_first_time,
x.sql_first_time,
x.session_state,
x.event,
x.wait_class,
x.blocking_session_status,
x.pga_allocated,
x.pga_total,
x.pga_percentage,
x.temp_space_allocated,
x.temp_space_total,
x.temp_space_percentage,
x.machine,
x.instance_number,
x.command_type,
x.action,
x.schema
from
(
select
dhash.sample_time_ sample_time,
dhash.session_id||' - '||dhash.session_serial# sid_serial#,
dhash.client_id,
nvl(dhash.module,dhash.program) module,
gsa.object_name program,
gsa.program_line#,
dhash.sql_id,
dhash.sql_plan_hash_value plan_hash_value,
count(*) over (partition by dhash.session_id, dhash.session_serial#, dhash.sql_id) count_sql_id,
count(*) over (partition by dhash.session_id, dhash.session_serial#) count,
dhst.sql_text,
dhash.sql_exec_id-16777216 execution_count,
dhash.sql_exec_start,
dp0.object_name||case when dp0.procedure_name is not null then '.'||dp0.procedure_name end entry_procedure,
dp.object_name||case when dp.procedure_name is not null then '.'||dp.procedure_name end procedure,
(max(dhash.sample_time_) over (partition by dhash.session_id, dhash.session_serial#)-min(dhash.sample_time_) over (partition by dhash.session_id, dhash.session_serial#))*3600*24 sess_elapsed_time,
min(dhash.sample_time_) over (partition by dhash.session_id, dhash.session_serial#) sess_first_time,
min(dhash.sample_time_) over (partition by dhash.session_id, dhash.session_serial#, dhash.sql_id) sql_first_time,
dhash.session_state,
dhash.event,
dhash.wait_class,
dhash.blocking_session_status,
dhash.pga_allocated/1000000 pga_allocated,
dhash.temp_space_allocated/1000000 temp_space_allocated,
sum(dhash.pga_allocated) over (partition by dhash.sample_time)/1000000 pga_total,
sum(dhash.temp_space_allocated) over (partition by dhash.sample_time)/1000000 temp_space_total,
dhash.pga_allocated/xxen_util.zero_to_null(sum(dhash.pga_allocated) over (partition by dhash.sample_time))*100 pga_percentage,
dhash.temp_space_allocated/xxen_util.zero_to_null(sum(dhash.temp_space_allocated) over (partition by dhash.sample_time))*100 temp_space_percentage,
dhash.machine,
dhash.instance_number,
lower(dhash.sql_opname) command_type,
dhash.action,
du.username schema
from
&request_id_table
dba_hist_snapshot dhs,
(
select
cast(dhash.sample_time as date) sample_time_,
dhash.*
from
dba_hist_active_sess_history dhash
) dhash,
dba_hist_sqltext dhst,
dba_procedures dp0,
dba_procedures dp,
(
select
gsa.sql_id,
gsa.plan_hash_value,
gsa.is_bind_sensitive,
gsa.is_bind_aware,
do.object_name,
case when gsa.program_line#>0 then gsa.program_line# end program_line#
from
gv$sqlarea gsa,
dba_objects do
where
2=2 and
gsa.program_id=do.object_id(+)
) gsa,
dba_users du
where
1=1 and
dhs.dbid=(select vd.dbid from v$database vd) and
dhs.dbid=dhash.dbid and
dhs.snap_id=dhash.snap_id and
dhs.instance_number=dhash.instance_number and
dhash.session_type='FOREGROUND' and
dhash.dbid=dhst.dbid(+) and
dhash.sql_id=dhst.sql_id(+) and
dhash.plsql_entry_object_id=dp0.object_id(+) and
dhash.plsql_entry_subprogram_id=dp0.subprogram_id(+) and
dhash.plsql_object_id=dp.object_id(+) and
dhash.plsql_subprogram_id=dp.subprogram_id(+) and
dhash.sql_id=gsa.sql_id(+) and
dhash.sql_plan_hash_value=gsa.plan_hash_value(+) and
dhash.user_id=du.user_id(+)
) x
where
3=3
) y
order by
y.sample_time,
y.sess_first_time desc,
y.percentage desc,
y.sql_first_time desc

Parameter Name SQL text Validation
User Name
dhash.client_id=:user_name
LOV
Module Type
xxen_util.module_type(dhash.module)=:module_type
LOV
Module contains
lower(dhash.module) like '%'||lower(:module)||'%'
LOV
Action contains
lower(dhash.action) like '%'||lower(:action)||'%'
Entry Procedure contains
lower(x.entry_procedure) like '%'||lower(:entry_procedure)||'%'
Request Id
fcr.user_name=dhash.client_id and
dhs.begin_interval_time<=fcr.actual_completion_date and
dhs.end_interval_time>=fcr.actual_start_date and
dhash.sample_time between fcr.actual_start_date and nvl(fcr.actual_completion_date,dhash.sample_time)
Number
From Time
dhs.end_interval_time>=:from_time and
dhash.sample_time>=:from_time
DateTime
To Time
dhs.begin_interval_time<=:to_time and
dhash.sample_time<=:to_time
DateTime
Wait Event
dhash.event=:event_name
LOV
SID - Serial#
dhash.session_id=substr(:sid_serial,1,instr(:sid_serial,' - ')-1) and
dhash.session_serial#=substr(:sid_serial,instr(:sid_serial,' - ')+3)
SQL Id
dhash.sql_id=:sql_id
Schema
du.username=:schema
LOV
UI Sessions only
(dhash.module like '%:frm:%' or dhash.module like 'frmweb@%' or dhash.module like '%:fwk:%')
LOV
Request Id
(select fcr.actual_start_date, fcr.actual_completion_date, fu.user_name from fnd_concurrent_requests fcr, fnd_user fu where fcr.request_id=:request_id and fcr.requested_by=fu.user_id) fcr,