Y - Queries

 

-- 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