Reports2017-11-18T12:27:27+00:00

DBA SGA Active Session History

Description
Categories: DBA, Enginatics
Active session history from the SGA

select
x.*
from
(
select
gash.sample_time_ sample_time,
gash.session_id||' - '||gash.session_serial# sid_serial#,
xxen_util.user_name(gash.module,gash.action,gash.client_id) user_name,
xxen_util.responsibility(gash.module,gash.action) responsibility,
xxen_util.module_type(gash.module,gash.action) module_type,
xxen_util.module_name(gash.module) module_name,
case when lower(gash.module) like '%frm%' then 'Forms' when lower(gash.module) like '%fwk%' then 'OAF' end ui_type,
nvl(gash.module,gash.program) module,
gsa.object_name program,
gsa.program_line#,
gash.sql_id,
gash.sql_plan_hash_value plan_hash_value,
count(*) over (partition by gash.session_id, gash.session_serial#, gash.sql_id) elapsed_time,
round(100*(count(*) over (partition by gash.session_id, gash.session_serial#, gash.sql_id)/count(*) over (partition by gash.session_id, gash.session_serial#)),2) percentage,
(select gsa.sql_fulltext from gv$sqlarea gsa where gash.inst_id=gsa.inst_id and gash.sql_id=gsa.sql_id and rownum=1) sql_text,
gash.sql_exec_id-16777216 execution_count,
gash.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(gash.sample_time_) over (partition by gash.session_id, gash.session_serial#)-min(gash.sample_time_) over (partition by gash.session_id, gash.session_serial#))*3600*24 sess_elapsed_time,
min(gash.sample_time_) over (partition by gash.session_id, gash.session_serial#) sess_first_time,
min(gash.sample_time_) over (partition by gash.session_id, gash.session_serial#, gash.sql_id) sql_first_time,
gash.session_state,
gash.event,
gash.wait_class,
gash.blocking_session_status,
gash.pga_allocated/1000000 pga_allocated,
sum(gash.pga_allocated) over (partition by gash.sample_time)/1000000 pga_total,
gash.pga_allocated/xxen_util.zero_to_null(sum(gash.pga_allocated) over (partition by gash.sample_time))*100 pga_percentage,
gash.temp_space_allocated/1000000 temp_space_allocated,
sum(gash.temp_space_allocated) over (partition by gash.sample_time)/1000000 temp_space_total,
gash.temp_space_allocated/xxen_util.zero_to_null(sum(gash.temp_space_allocated) over (partition by gash.sample_time))*100 temp_space_percentage,
gash.machine,
gash.inst_id,
lower(gash.sql_opname) command_type,
gash.action,
du.username schema
from
&request_id_table
(select cast(gash.sample_time as date) sample_time_, gash.* from gv$active_session_history gash) gash,
dba_procedures dp0,
dba_procedures dp,
(
select distinct
gsa.sql_id,
gsa.plan_hash_value,
max(gsa.is_bind_sensitive) over (partition by gsa.sql_id,gsa.plan_hash_value) is_bind_sensitive,
max(gsa.is_bind_aware) over (partition by gsa.sql_id,gsa.plan_hash_value) is_bind_aware,
max(do.object_name) over (partition by gsa.sql_id,gsa.plan_hash_value) object_name,
max(case when gsa.program_line#>0 then gsa.program_line# end) over (partition by gsa.sql_id,gsa.plan_hash_value) 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
gash.session_type='FOREGROUND' and
gash.plsql_entry_object_id=dp0.object_id(+) and
gash.plsql_entry_subprogram_id=dp0.subprogram_id(+) and
gash.plsql_object_id=dp.object_id(+) and
gash.plsql_subprogram_id=dp.subprogram_id(+) and
gash.sql_id=gsa.sql_id(+) and
gash.sql_plan_hash_value=gsa.plan_hash_value(+) and
gash.user_id=du.user_id(+)
) x
where
3=3
order by
x.sample_time,
x.sess_first_time desc,
x.percentage desc,
x.sql_first_time desc

Parameter Name SQL text Validation
UI Sessions only
(gash.module like '%:frm:%' or gash.module like 'frmweb@%' or gash.module like '%:fwk:%')
LOV
Module Type
xxen_util.module_type(gash.module,gash.action)=:module_type
LOV
Schema
du.username=:schema
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,
Program contains
lower(do.object_name) like '%'||lower(:program)||'%'
SQL Id
gash.sql_id=:sql_id
SID - Serial#
gash.session_id=substr(:sid_serial,1,instr(:sid_serial,' - ')-1) and
gash.session_serial#=substr(:sid_serial,instr(:sid_serial,' - ')+3)
LOV
Wait Event
gash.event=:event_name
LOV
To Time
gash.sample_time<=:to_time
DateTime
From Time
gash.sample_time>=:from_time
DateTime
Request Id
fcr.user_name=gash.client_id and
gash.sample_time between fcr.actual_start_date and nvl(fcr.actual_completion_date,gash.sample_time)
LOV
Entry Procedure contains
lower(x.entry_procedure) like '%'||lower(:entry_procedure)||'%'
Program contains
gash.sql_id=gsa.sql_id and
gash.sql_plan_hash_value=gsa.plan_hash_value
Action contains
lower(gash.action) like '%'||lower(:action)||'%'
Module contains
lower(gash.module) like '%'||lower(:module)||'%'
LOV
User Name
gash.client_id like upper(:user_name)
LOV