-- Find concurrent request that had performance issue
select trunc((nvl(actual_completion_date, sysdate) - actual_start_date)*24*60, 2) runtime_minutes,frt.responsibility_name, fcpt.user_concurrent_program_name, fcpt.concurrent_program_name, fcr.actual_start_date, fcr.actual_completion_date,
fcr.phase_code, fcr.status_code, fe.execution_file_name, fcr.argument_text, fcpt.executable_id
from fnd_concurrent_requests fcr
,apps.fnd_concurrent_programs_vl fcpt
,apps.fnd_responsibility_tl frt
,apps.fnd_executables fe
where fcpt.concurrent_program_id = fcr.concurrent_program_id
and fe.executable_id = fcpt.executable_id
-- and fcpt.user_concurrent_program_name like 'PSC GL Reference Update for 190 Type Invoices%'
-- and responsibility_name like '%PSC%'
and fcr.responsibility_id = frt.responsibility_id
and fcr.actual_start_date is not null
and fcr.request_id='107472634'
-- and phase_code = 'R'
and request_date > sysdate - 90
order by fcr.actual_start_date desc
718.03 PSC Grants Interface Grants PMS Synchronization Report (Un-reconciled) HHSGRTUN 8/3/2022 12:34:46 PM 8/4/2022 12:32:48 AM C C HHSGRTUN 2937, 3, 31-AUG-2022, , N 10469
-- Find sid/serial# based on start and end times
select session_id, session_serial#,sql_id,count(*)*10 runtime_sec, min(sample_time), max(sample_time)
from dba_hist_active_sess_history
where 1=1
and sample_time > sysdate - 60
and sample_time BETWEEN TO_DATE('03-AUG-2022 08:27','DD-MON-YYYY HH24:MI')
AND TO_DATE('03-AUG-2022 11:32','DD-MON-YYYY HH24:MI')
--and session_id=1026 and session_serial#=22860
group by session_id, session_serial#,sql_id--,-- module,
order by min(sample_time) desc
8972 31528 e:PSCCUST:cp:psccust/PSCTC190GLUPD 23680 20/JUN/2022 7:30:37.907 PM 20/JUN/2022 10:48:14.575 PM
select session_id, session_serial#, module, sql_id, sql_plan_hash_value, count(*)*10 runtime_sec, min(sample_time), max(sample_time)
from dba_hist_active_sess_history
where 1=1
and (session_id, session_serial#) in ((5220,27142))
group by session_id, session_serial#, sql_id, sql_plan_hash_value, module
order by count(*)*10 desc
8972 31528 e:PSCCUST:cp:psccust/PSCTC190GLUPD 413kvqbrw070v 1309738701 23680 20/JUN/2022 7:30:37.907 PM 20/JUN/2022 10:48:14.575 PM
-- Look at history of sql plans for problem sql_id
select session_id, session_serial#, sql_id, sql_plan_hash_value, count(*)*10 runtime_sec, min(sample_time), max(sample_time)
from dba_hist_active_sess_history
where sql_id = 'a5tws7tsuc9zh'
group by session_id, session_serial#, sql_id, sql_plan_hash_value
order by min(sample_time) desc
select a.snap_id,begin_interval_time,end_interval_time,sql_id,plan_hash_value
from dba_hist_snapshot a, dba_hist_sqlstat b
where a.snap_id=b.snap_id
and a.dbid=b.dbid
and a.instance_number=b.instance_number
and b.sql_id='a5tws7tsuc9zh'
and a.begin_interval_time>sysdate-60
order by snap_id;
select
SQL_ID
, PLAN_HASH_VALUE
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60) ELA_MINS
from DBA_HIST_SQLSTAT
where SQL_ID in (
'f1k7kq8an7ktn')
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS;
f1k7kq8an7ktn 287300685
select *
from dba_hist_sqlbind
where sql_id = '413kvqbrw070v'
-- Check for wait events
select NVL(a.event, 'ON CPU') AS event, a.session_id, a.session_serial#,
COUNT(*)*10 AS total_wait_time, min(sample_time), max(sample_time)
from dba_hist_active_sess_history a
where 1=1
and (session_id, session_serial#) in ((10408,34869))
group by NVL(a.event, 'ON CPU'),a.session_id, a.session_serial#
-- Get sql plans with runtimes for each plan
WITH
p AS (
SELECT plan_hash_value
FROM gv$sql_plan
WHERE sql_id = :sql_id
AND other_xml IS NOT NULL
UNION
SELECT plan_hash_value
FROM dba_hist_sql_plan
WHERE sql_id = :sql_id
AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
SUM(elapsed_time)/SUM(executions) avg_et_secs
FROM gv$sql
WHERE sql_id = :sql_id
AND executions > 0
GROUP BY
plan_hash_value ),
a AS (
SELECT plan_hash_value,
SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
FROM dba_hist_sqlstat
WHERE sql_id = :sql_id
AND executions_total > 0
GROUP BY
plan_hash_value )
SELECT p.plan_hash_value,
ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
FROM p, m, a
WHERE p.plan_hash_value = m.plan_hash_value(+)
AND p.plan_hash_value = a.plan_hash_value(+)
ORDER BY
avg_et_secs NULLS LAST;
SID of Concurrent Request:
select s.inst_id, fcr.request_id, fv.requestor, fv.Program cmgr_job,
p.PID,
p.SERIAL#,
p.USERNAME p_user,
p.SPID,
to_char(s.logon_time,'DD-MON-YY HH24:MI:SS') Logon_Time,
s.program,
s.command,
s.sid,
s.serial#,
s.username,
s.process,
s.machine,
s.action,
s.module
from apps.fnd_concurrent_requests fcr,
apps.FND_CONC_REQ_SUMMARY_V fv,
gv$session s,
gv$process p
where fcr.request_id = &request_id
and p.SPID = fcr.oracle_process_id
and s.process = fcr.OS_PROCESS_ID
and s.inst_id = p.inst_id
and p.addr = s.paddr
and fv.request_id = fcr.request_id
;
pause try to lock the same row in a different session here
with lo as (select /*+ materialize */ sid, row_wait_obj# from v$session where event like 'enq%')
select lo.sid, do.object_name, do.object_type, do.object_id, do.data_object_id, lo.row_wait_obj#
from dba_objects do, lo
where do.object_id=lo.row_wait_obj#
or do.data_object_id=lo.row_wait_obj#;
Bind Variable:
select INST_ID,SQL_ID,POSITION,NAME,VALUE_STRING from GV$SQL_BIND_CAPTURE where sql_id='c73qbuzd38n1b' and inst_id=1 order by POSITION
select * from sys.dba_hist_sqlbind t where t.sql_id='c73qbuzd38n1b';
SELECT sql_id, b.name BIND_NAME, b.value_string BIND_STRING from gv$sql t
JOIN gv$sql_bind_capture b USING (sql_id) WHERE b.value_string IS NOT NULL
AND sql_id ='c73qbuzd38n1b';
select
sn.END_INTERVAL_TIME,
sb.NAME,
sb.VALUE_STRING
from
DBA_HIST_SQLBIND sb,
DBA_HIST_SNAPSHOT sn
where
sb.sql_id='c73qbuzd38n1b' and
sb.WAS_CAPTURED='YES' and
sn.snap_id=sb.snap_id
order by
sb.snap_id,
sb.NAME;
Buffer Gets:
SELECT
s.sid
,s.serial#
,s.sql_id
,sq.sorts
,sq.DISK_READS
,sq.BUFFER_GETS
,sq.ROWS_PROCESSED
,sq.SQLTYPE
,sq.SQL_TEXT
FROM gv$session s
, gv$sql sq
WHERE s.sql_id = sq.sql_id and s.sid=11351 and rownum<10 order by sq.sorts desc
