1 Reply Latest reply: Jan 7, 2013 4:04 PM by Elaine W. RSS

    How to get session information from fnd_concurrent_requests table

    Sododba
      Hi, All

      I have a special need to get session information (such as SQL code) for concurrent requests. I understand it is easy when the concurrent request is running. Because you can link fnd_concurrent_requests' oracle_process_id and v$process and then you can get v$session information.

      But my question is little harder: How to get the session information when the concurrent request is completed. We only have oracle_process_id from concurrent request tables. The concurrent program tables do not contain any direct session sid and serial# so that we can not get the historical sql infomation from AWR once the concurrent request was done.

      Please advise.

      Thanks.
        • 1. Re: How to get session information from fnd_concurrent_requests table
          Elaine W.
          How to find concurrent program info and SQL statement from AWR data:

          Note: all SQL must be run on the database that had the error, because the object numbers for tablespaces and other things vary from database to database.

          1.     Find the exact time of the error from the email or alert log.
          In this case:
          Thu Jan 03 22:28:15 2013
          ORA-1652: unable to extend temp segment by 16 in tablespace APPS_TS_MEDIA
          ORA-1652: unable to extend temp segment by 16 in tablespace APPS_TS_MEDIA
          Thu Jan 03 22:28:16 2013
          2. Create the subset of the AWR data into a table:
          create table fprd_awr_sql as
          select
          sn.snap_id,
          sn.begin_interval_time,
          sn.end_interval_time,
          st.sql_id sql_id,
          s.sql_text sql_text,
          st.executions_delta executions,
          st.executions_total executions_total,
          (st.elapsed_time_delta/1000000)/st.executions_delta elapsed_time,
          st.elapsed_time_delta/1000000 elapsed_time_for_interval,
          st.elapsed_time_total/1000000 elapsed_time_total
          from
          dba_hist_sqltext s,
          dba_hist_snapshot sn,
          dba_hist_sqlstat st
          where
          st.sql_id = s.sql_id
          and st.snap_id = sn.snap_id
          and st.executions_delta <> 0
          and begin_interval_time between '03-JAN-13' and '04-JAN-13'
          /

          2. Set your NLS date parameter:
               alter session set nls_date_format = 'mm/dd/yyyy hh24:mi:ss'

          3. Find the concurrent programs running at that time:
          select
          reqs.oracle_session_id
          ,
          reqs.oracle_process_id oracle_process
          , reqs.os_process_id
          , actual_start_date
          , actual_completion_date
          , prg.user_concurrent_program_name concurrent_name
          , argument_text args
          from apps.fnd_user usr
          , apps.fnd_concurrent_requests reqs
          , apps.fnd_concurrent_programs_tl prg
          where reqs.concurrent_program_id = prg.concurrent_program_id
          and reqs.requested_by = usr.user_id
          and trunc(reqs.actual_start_date) = trunc(sysdate-4)
          and actual_completion_date > to_date('03/01/2013 22:28:14','DD/MM/YYYY HH24:MI:SS')
          and actual_start_date < to_date('03/01/2013 22:28:00','DD/MM/YYYY HH24:MI:SS')
          /

          4.     Find the AWR snapshot id for the time in question. The default is one snapshot taken every hour.
          select snap_id, begin_interval_time from dba_hist_snapshot
          where trunc(begin_interval_time) = trunc(sysdate-4)
          order by 1
          /

          5.     Then put the snapshot id in this SQL, to find the sessions using the file numbers in the tablespace having the error. We also look at the ones specifically allocating temp space.

          SELECT SESSION_ID, SESSION_SERIAL#, SQL_ID, SAMPLE_TIME, TEMP_SPACE_ALLOCATED, CURRENT_OBJ#,
          CURRENT_FILE#
          FROM DBA_HIST_ACTIVE_SESS_HISTORY
          WHERE SNAP_ID=36649
          AND CURRENT_FILE# IN
          (SELECT FILE_ID FROM DBA_DATA_FILES
          WHERE TABLESPACE_NAME = 'APPS_TS_MEDIA'
          ORDER BY 1)
          ORDER BY 3
          /
               
          6.     Now review the above output to find SQL_IDs by looking at the SAMPLE_TIME and TEMP_SPACE_ALLOCATED. It may be one or 2 , or maybe a lot of them.

          7.     The final step is to query the AWR data - DBA_HIST_ACTIVE_SESS_HISTORY – to input the SQL_ID(s) to get the exact SQL text:

          set echo off
          set trimspool on
          set pagesize 500
          set wrap on
          SET LINESIZE 5000 LONG 5000 LONGCHUNKSIZE 5000
          col sql_text format a75
          col sql_id format a15
          col stime format a25 heading "Start Time"
          col etime format a25 heading "End Time"
          col elapsed_time format 9999 heading "Elapsed|Time|(secs)"
          col executions format 999,999 heading "Execs"
          col timestamp format a21 heading "Timestamp|(rounded to hour)"
          set heading on
          REM break on dr on sql_id on timestamp on sql_text on elapsed_time skip 1
          select
          to_char(begin_interval_time,'DD-MON-YY HH24:MI:SS') start_time
          to_char(end_interval_time,'DD-MON-YY HH24:MI:SS') end_time,
          sql_id,
          elapsed_time,
          sql_text
          from fprd_awr_sql
          where sql_id = 'c1kwv50286gq1'
          /