Oracle R12 DBA scripts for troubleshooting - part 1

SQL Sript to view database sessions:

  SELECT s.Inst_id,
    s.sid,
    s.module,
    s.action,
    s.program,
    s.process,
    sa.executions,
    s.event,
    sa.sql_text,
    sa.buffer_gets,
    sa.disk_reads,
    sa.optimizer_cost,
    sa.optimizer_mode,
    s.blocking_session,
    ROUND(s.last_call_et/60,2) "Elapsed Time"
  FROM gv$session s,
    gv$sqlarea sa,
    gv$process p
  WHERE s.sql_address=sa.address(+)
  AND p.addr         =s.paddr
  AND s.inst_id      =p.inst_id
  AND s.inst_id      =sa.inst_id(+) ;

If there is a performance issue, you can look at columns like sql_text,optimizer_cost, event etc to figure out cause for slowness.


SQL script to list scheduled concurrent requests:

SELECT fcrsv.REQUEST_ID,
    fcrsv.program,
    fcrsv.REQUESTED_START_DATE,
    ROUND(MIN_RUN_TIME,2) min_run_time,
    ROUND(MAX_RUN_TIME,2) max_run_time,
    ROUND(AVG_RUN_TIME,2) avg_run_time,
    fu.description user_description,
    fcrsv.argument_text,
    fcrsv.program_short_name,
    fcrsv.requestor,
    fcrsv.printer,
    fcr.resubmit_interval
    ||' '
    || fcr.resubmit_interval_unit_code
    ||', '
    ||DECODE(fcr.resubmit_interval_type_code,'START','From the start of prior run','END','from the end of prior run',fcr.resubmit_interval_type_code) Interval_type
  FROM FND_CONC_REQ_SUMMARY_V fcrsv,
    fnd_concurrent_requests fcr,
    FND_CONC_PROG_ONSITE_INFO fcpoi,
    fnd_user fu
  WHERE fcrsv.PROGRAM_APPLICATION_ID=fcpoi.PROGRAM_APPLICATION_ID
  AND fcrsv.CONCURRENT_PROGRAM_ID   =fcpoi.CONCURRENT_PROGRAM_ID
  AND fcr.request_id                =fcrsv.request_id
  AND fcrsv.PHASE_CODE              ='P'
  AND fcrsv.STATUS_CODE             ='I'
  AND fcrsv.requested_start_date    >sysdate -1
    --  AND requested_start_date         <= sysdate+1/24
  AND fcrsv.hold_flag ='N'
  AND fu.user_id      =fcrsv.requested_by
  ORDER BY 3 ;

SQL script view the details of database sessions associated with concurrent request:

  SELECT fcr.request_id,
    s.Inst_id,
    s.sid,
    s.module,
    s.program,
    sa.executions,
    s.event,
    sa.sql_text,
    sa.buffer_gets,
    sa.disk_reads,
    sa.optimizer_cost,
    sa.optimizer_mode,
    ROUND(s.last_call_et/60,2) "Elapsed Time"
  FROM gv$session s,
    gv$sqlarea sa,
    gv$process p,
    fnd_concurrent_requests fcr
  WHERE s.sql_address=sa.address(+)
  AND p.addr         =s.paddr
  AND s.inst_id      =p.inst_id
  AND s.inst_id      =sa.inst_id(+)
  AND p.spid         =Fcr.oracle_process_id
  AND s.process      =fcr.os_process_id ;

SQL script to view list of active application users:

  SELECT s.INST_ID,
    S.SID,
    S.SERIAL#,
    L.PROCESS_SPID,
    S.machine,
    s.event,
    s.action,
    s.module,
    s.blocking_session,
    s.last_call_et,
    -- NVL(F.START_TIME, NVL(R.START_TIME, L.START_TIME)) TIME,
    USR.USER_NAME,
    RSP.RESPONSIBILITY_NAME,
    FRM.USER_FORM_NAME,
    FLOOR(SYSDATE -s.logon_time)
    || ':'
    || mod(FLOOR((SYSDATE - s.logon_time)*24), 24)
    || ':'
    ||LTRIM(TO_CHAR(TRUNC(((SYSDATE - s.logon_time)*24- FLOOR((SYSDATE - s.logon_time)*24))*60), '09')) TIME
  FROM FND_RESPONSIBILITY_TL RSP,
    FND_FORM_TL FRM,
    FND_USER USR,
    FND_LOGINS L,
    FND_LOGIN_RESPONSIBILITIES R,
    FND_LOGIN_RESP_FORMS F,
    GV$SESSION S
  WHERE R.LOGIN_ID        = F.LOGIN_ID
  AND R.LOGIN_RESP_ID     = F.LOGIN_RESP_ID
  AND L.LOGIN_ID          = R.LOGIN_ID
  AND L.END_TIME         IS NULL
  AND R.END_TIME         IS NULL
  AND F.END_TIME         IS NULL
  AND L.USER_ID           = USR.USER_ID
  AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID
  AND R.RESP_APPL_ID      = RSP.APPLICATION_ID
  AND RSP.LANGUAGE        = userenv('LANG')
  AND FRM.FORM_ID         = F.FORM_ID
  AND FRM.APPLICATION_ID  = F.FORM_APPL_ID
  AND FRM.LANGUAGE        = userenv('LANG')
  AND S.AUDSID            = F.AUDSID
  UNION
  SELECT s.INST_ID,
    S.SID,
    S.SERIAL#,
    L.PROCESS_SPID,
    s.machine,
    s.event,
    s.action,
    s.module,
    s.blocking_session,
    s.last_call_et,
    -- NVL(R.START_TIME, L.START_TIME) TIME,
    USR.USER_NAME,
    RSP.RESPONSIBILITY_NAME,
    'FNDSCSGN',
    FLOOR(SYSDATE -s.logon_time)
    || ':'
    || mod(FLOOR((SYSDATE - s.logon_time)*24), 24)
    || ':'
    ||LTRIM(TO_CHAR(TRUNC(((SYSDATE - s.logon_time)*24- FLOOR((SYSDATE - s.logon_time)*24))*60), '09')) TIME
  FROM FND_RESPONSIBILITY_TL RSP,
    FND_USER USR,
    FND_LOGINS L,
    FND_LOGIN_RESPONSIBILITIES R ,
    GV$SESSION S
  WHERE L.LOGIN_ID        = R.LOGIN_ID
  AND L.END_TIME         IS NULL
  AND R.END_TIME         IS NULL
  AND L.USER_ID           = USR.USER_ID
  AND R.RESPONSIBILITY_ID = RSP.RESPONSIBILITY_ID
  AND R.RESP_APPL_ID      = RSP.APPLICATION_ID
  AND RSP.LANGUAGE        = userenv('LANG')
  AND S.AUDSID            = R.AUDSID
  ORDER BY 6 ;


SQL script view list database sessions with top usage of CPU:

  SELECT "INST_ID",
    "SID",
    "SPID",
    "CPU",
    "EVENT",
    "PROGRAM",
    "MODULE",
    "ACTION",
    "LAST_CALL_ET",
    "SQL_TEXT"
  FROM
    (SELECT s.inst_id,
      s.sid,
      p.spid,
      (SELECT value
      FROM v$sesstat
      WHERE v$sesstat.sid = s.sid
      AND statistic#      =
        (SELECT statistic# FROM v$statname WHERE name = 'CPU used by this session'
        )
      ) cpu,
      s.event,
      s.program,
      s.module,
      s.action,
      s.last_call_et,
      sa.sql_text--,p.pga_user_mem,p.pga_alloc_mem
    FROM gv$session s,
      gv$process p,
      gv$sqlarea sa
    WHERE s.paddr    =p.addr
    AND s.sql_address=sa.address
    AND s.status     ='ACTIVE'
    )
  WHERE cpu  IS NOT NULL
  AND module IS NOT NULL
  AND sql_text NOT LIKE 'begin fndcp%'
  AND sql_text NOT LIKE 'BEGIN FND_CP_GSM%'
  AND sql_text NOT LIKE 'BEGIN FND_CP_OPP%'
  AND rownum < 21
  ORDER BY 4 DESC ;

Note: This blog has been moved to http://www.oamonitor.com/wordpress/blog