SQL PLAN from AWR report for given SQLID

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('at1cy00n5p7u0'));

Which SQL PLAN ID ran between snapid given SQL ID

SELECT plan_hash_value,snap_id,SQL_PROFILE,OPTIMIZER_COST,ROWS_PROCESSED_TOTAL,ELAPSED_TIME_DELTA FROM dba_hist_sqlstat WHERE
sql_id = 'cdvxbyn5t3nsy'  and snap_id >=24035 and snap_id<=24040 ORDER BY 1




SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300

SELECT DISTINCT sql_id, plan_hash_value
FROM dba_hist_sqlstat q,
    (
    SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
    FROM dba_hist_snapshot ss
    WHERE ss.begin_interval_time BETWEEN (SYSDATE - 1/12) AND SYSDATE
    ) s
WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap
  AND q.sql_id IN ( 'cdvxbyn5t3nsy')

TO take procedure backup from Sqlplus

REM procedure, package or package body.
REM This SCRIPT is provided as a guide and is not supported by
REM ORACLE Corporation.
set verify off
set feedback off
set lines 132
set pages 0
set heading off
set space 0
column text format a79
column line noprint
select DECODE(line,1,'create or replace ','')||text, line
from dba_source
where
owner = upper('APPS') and
type = upper('PACKAGE BODY')
and name = upper('XXLR_INTF_BOS_JDE_WO_PKG')
and line >= 1;
set verify on
set feedback on

Concurrent Request Count Day wise

set pause off
set pagesize 2000
set linesize 120
set wrap off
column user_concurrent_program_name format a45 noprint
column argument_text format a45 print
column user_name format a15
column start_time format a15
column end_time format a15
column comp_time format 9999.99
select request_id,
user_concurrent_program_name,
to_char(actual_start_date,'DD-MON HH24:MI:SS') START_TIME,
to_char(ACTUAL_COMPLETION_DATE,'DD-MON HH24:MI:SS') END_TIME,
(actual_completion_date-actual_start_date)*24*60 comp_time, argument_text,user_name, status_code, phase_code
from apps.fnd_concurrent_requests, apps.fnd_concurrent_programs_tl,apps.fnd_user
where fnd_concurrent_requests.concurrent_program_id = fnd_concurrent_programs_tl.concurrent_program_id
--and user_concurrent_program_name like '%Gather Schema%'
and fnd_concurrent_requests.concurrent_program_id=57389
and fnd_concurrent_programs_tl.language='US'
and requested_by=user_id and status_code in ('C')
order by actual_start_date;


Example:


COUNT(*) TRUNC(A.ACTUAL_COM
---------- ------------------
       205 01-NOV-16
       202 02-NOV-16
       220 03-NOV-16
       244 04-NOV-16
        38 05-NOV-16

Concurrent Request ID Pending From

select request_id,to_char(round((SYSDATE - requested_start_date) * 1440, 2))
from fnd_Concurrent_requests
where concurrent_program_id=57389 and  phase_code='P'  and status_code='I' order by 1 desc;

To find plan hash value for SQL ID

select sql_id, child_number, plan_hash_value plan_hash, executions execs,
(elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
sql_text
from v$sql s
where sql_id='cdvxbyn5t3nsy';

Conc Request Complete Time Day Wise

select count(*),TRUNC(a.actual_completion_date) from
APPLSYS.fnd_Concurrent_requests a
,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_user f
--    ,v$session vs
--    ,v$process vp
where 1=1
--and a.REQUEST_ID = 183923555
AND a.controlling_manager    = b.concurrent_process_id
AND a.concurrent_program_id  = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND c2.concurrent_program_id = c.concurrent_program_id
AND c2.application_id        = c.application_id
AND a.requested_by = f.user_id
AND b.queue_application_id   = q.application_id
AND b.concurrent_queue_id    = q.concurrent_queue_id
AND c2.language = 'US'
and c2.concurrent_program_id=57389
and a.actual_completion_date   between to_date('01-NOV-2016 01:40:00','DD-MON-YYYY HH24:MI:SS') and to_Date('21-NOV-2016 23:00:00','DD-MON-YYYY HH24:MI:SS')
group by TRUNC(a.actual_completion_date) ORDER BY 2;


example :
REQUEST_ID START_TIME      END_TIME        COMP_TIME ARGUMENT_TEXT                                 USER_NAME       S P
---------- --------------- --------------- --------- --------------------------------------------- --------------- - -
  11048480 23-OCT 08:34:06 23-OCT 08:39:21      5.25 , 145704                                      428686          C C
  11048688 23-OCT 08:52:17 23-OCT 08:56:20      4.05 , 154046                                      186219          C C
  11048873 23-OCT 09:10:16 23-OCT 09:14:00      3.73 , 154046                                      186219          C C
  11049070 23-OCT 09:26:05 23-OCT 09:29:33      3.47 , 144482                                      428686          C C
  11049192 23-OCT 09:37:26 23-OCT 09:39:48      2.37 , 305806                                      428687          C C
  11049990 23-OCT 10:41:15 23-OCT 10:46:11      4.93 , 64050                                       428686          C C