long-RUNNING-REQUEST-ACTIVITY

 

http://www.br8dba.com/troubleshooting-long-running-queries/

#######################HISTORY FOR PROGRAM #####################################

REM +======================================================================+

REM

REM File Name: reqhistory.sql

REM 

REM Description:

REM   Query To Check Concurrent Program Run History

REM   

REM Notes:

REM   Usage: sqlplus <apps_user/apps_passwd> @reqhistory.sql 

REM 

REM Input Required :

REM    Number of days and User Concurrent Program Name  

REM   

REM +======================================================================+

ACCEPT NO_DAYS PROMPT "Enter Number of Days for History: ";

ACCEPT USER_CONC_PROG_NAME PROMPT "Enter User Concurrent Program Name: ";

        clear columns

set lines 180

set pages 100

col Parameters for a20 WORD_WRAPPED

set pages 100

col "Conc Program Name" for a30 WORD_WRAPPED

col "Started at" for a20

col "Completed at" for a20

col "Username" for a10 WORD_WRAPPED

SELECT distinct t.user_concurrent_program_name "Conc Program Name",

r.REQUEST_ID "Request ID",

to_char(r.ACTUAL_START_DATE,'dd-MON-yy hh24:mi:ss') "Started at",

to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') "Completed at",

decode(r.PHASE_CODE,'C','Completed','I','Inactive','P','Pending','R','Running','NA') "Phasecode",

decode(r.STATUS_CODE, 'A','Waiting', 'B','Resuming', 'C','Normal', 'D','Cancelled', 'E','Error', 'F','Scheduled', 'G','Warning', 'H','On Hold', 'I','Normal', 'M',

'No Manager', 'Q','Standby', 'R','Normal', 'S','Suspended', 'T','Terminating', 'U','Disabled', 'W','Paused', 'X','Terminated', 'Z','Waiting') "Status",r.argument_text "Parameters",

u.user_name "Username",

--ROUND ((v.actual_completion_date - v.actual_start_date) * 1440,

--              2

--            ) "Runtime (in Minutes)" 

round(((nvl(v.actual_completion_date,sysdate)-v.actual_start_date)*24*60),2) "ElapsedTime(Mins)"

FROM

apps.fnd_concurrent_requests r ,

apps.fnd_concurrent_programs p ,

apps.fnd_concurrent_programs_tl t,

apps.fnd_user u, apps.fnd_conc_req_summary_v v

WHERE 

r.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID

AND r.actual_start_date >= (sysdate - &NO_DAYS)

--AND r.requested_by=22378

AND   r.PROGRAM_APPLICATION_ID = p.APPLICATION_ID

AND t.concurrent_program_id=r.concurrent_program_id

AND r.REQUESTED_BY=u.user_id

AND v.request_id=r.request_id

--AND r.request_id ='2260046' in ('13829387','13850423')

and t.user_concurrent_program_name like '&USER_CONC_PROG_NAME'

order by to_char(r.ACTUAL_COMPLETION_DATE,'dd-MON-yy hh24:mi:ss') desc;

undef NO_DAYS

undef USER_CONC_PROG_NAME

SQL> @req-hist-lk.sql

Enter Number of Days for History: 5

Enter User Concurrent Program Name: Subledger Accounting Balances Update

Subledger Accounting Balances    36555200 19-MAY-21 03:34:23                        Running   Normal      555, , , 97499, A    MIBURNS                    565.88

Update

Subledger Accounting Balances    36554595 18-MAY-21 12:59:31   18-MAY-21 12:59:43   Completed Normal      200, , , 97497, A    KMUMA                  .2

Update

Subledger Accounting Balances    36553646 17-MAY-21 14:42:38   17-MAY-21 14:53:04   Completed Normal      555, , , 97493, A    MIBURNS                     10.43

Update

Subledger Accounting Balances    36550763 14-MAY-21 16:04:13   14-MAY-21 16:07:47   Completed Normal      222, , , 97487, A    SHENDERSON           3.57

Update

Subledger Accounting Balances    36550741 14-MAY-21 15:46:17   14-MAY-21 15:47:26   Completed Normal      222, , , 97485, A    SHENDERSON           1.15

Update

Subledger Accounting Balances    36553646 17-MAY-21 14:42:38   17-MAY-21 14:53:04   Completed Normal      555, , , 97493, A    MIBURNS                                   10.43

Update

Subledger Accounting Balances    36550336 14-MAY-21 09:35:10   15-MAY-21 13:54:10   Completed Terminated  555, , , 97471, A    MIBURNS                                    1699

Update

########REQID TO PID########from CM NODE##

select ORACLE_PROCESS_ID from apps.fnd_concurrent_requests where REQUEST_ID='&ENERREQID';

SQL> SELECT   vp.spid,

         vs.sid,

         fcr.request_id

  FROM   v$process vp,

         v$session vs,

         fnd_Concurrent_requests fcr

 WHERE   vs.paddr              = vp.addr

   AND   fcr.oracle_process_id = vp.spid

   AND   vs.sid                = &l_sid;  2    3    4    5    6    7    8    9

Enter value for l_sid: 36555200

SQL> select ORACLE_PROCESS_ID from apps.fnd_concurrent_requests where REQUEST_ID='&ENERREQID';

Enter value for enerreqid: 36555200

23305

SQL> !ps -ef|grep 23305

applmgr  12613 11365  0 12:42 pts/1    00:00:00 /bin/bash -c ps -ef|grep 23305

applmgr  12615 12613  0 12:42 pts/1    00:00:00 grep 23305

SQL>

#################FROM DB NODE SAME PID VALIDATED EXISTS PROCES IN DB NODE OR NOT##

SQL> !ps -ef|grep -i 23305

oracle    9909  8063  0 13:08 pts/0    00:00:00 /bin/bash -c ps -ef|grep -i 23305

oracle    9911  9909  0 13:08 pts/0    00:00:00 grep -i 23305

oracle   23305     1  0 03:34 ?        00:00:02 oracleEBSPRJ1 (LOCAL=NO)

SQL>

############FROM DB NODE IDENTIFIED SID FROM PID#############

  SELECT b.spid,

         a.sid,

         a.serial#,

         a.username,

         a.osuser

    FROM v$session a, v$process b

   WHERE a.paddr = b.addr AND b.spid = '&spid'

ORDER BY b.spid;

SQL>

  SELECT b.spid,

         a.sid,

         a.serial#,

         a.username,

         a.osuser

    FROM v$session a, v$process b

   WHERE a.paddr = b.addr AND b.spid = '&spid'

ORDER BY b.spid;SQL>   2    3    4    5    6    7    8

Enter value for spid: 23305

old   7:    WHERE a.paddr = b.addr AND b.spid = '&spid'

new   7:    WHERE a.paddr = b.addr AND b.spid = '23305'

SPID                         SID    SERIAL# USERNAME             OSUSER

------------------------ ------- ---------- -------------------- ---------------

23305                       2801      34884 APPS                 applmgr

SQL>

##################FROM DB NODE IDENTIFIED SQL_ID FROM SID####################

SQL> select sql_id from v$session where sid='&SID';

Enter value for sid: 2801

old   1: select sql_id from v$session where sid='&SID'

new   1: select sql_id from v$session where sid='2801'

SQL_ID

-------------

4w8bh2ad16g4u

#############FROM DBNODE To find sql text for the above sql_id:############

SQL> select sql_fulltext from V$sql where sql_id='4w8bh2ad16g4u';

SQL_FULLTEXT

--------------------------------------------------------------------------------

 SELECT  /*+ PARALLEL (AEL) leading(aeh) use_nl(ael)   */ 1

           FROM xla_

###########FROM DB NODE To find wait event of the query for which it is waiting for: #################

SQL> select sql_id, state, last_call_et, event, program, osuser from v$session where sql_id='&sql_id';

Enter value for sql_id: 4w8bh2ad16g4u

old   1: select sql_id, state, last_call_et, event, program, osuser from v$session where sql_id='&sql_id'

new   1: select sql_id, state, last_call_et, event, program, osuser from v$session where sql_id='4w8bh2ad16g4u'

SQL_ID        STATE               LAST_CALL_ET EVENT                PROGRAM                                          OSUSER

------------- ------------------- ------------ -------------------- ------------------------------------------------ ---------------

4w8bh2ad16g4u WAITING                    34714 PX Deq: Execute Repl STANDARD@SONPRJ1A1.amyskitchen.net (TNS V1-V3)   applmgr

                                               y

4w8bh2ad16g4u WAITING                    34713 direct path read     oracle@SONPRJ1DB (P005)                          applmgr

4w8bh2ad16g4u WAITING                    34713 direct path read     oracle@SONPRJ1DB (P003)                          applmgr

4w8bh2ad16g4u WAITING                    34713 direct path read     oracle@SONPRJ1DB (P002)                          applmgr

4w8bh2ad16g4u WAITING                    34713 direct path read     oracle@SONPRJ1DB (P007)                          applmgr

4w8bh2ad16g4u WAITING                    34713 direct path read     oracle@SONPRJ1DB (P001)                          applmgr

4w8bh2ad16g4u WAITING                    34713 direct path read     oracle@SONPRJ1DB (P000)                          applmgr

4w8bh2ad16g4u WAITING                    34713 direct path read     oracle@SONPRJ1DB (P004)                          applmgr

4w8bh2ad16g4u WAITING                    34713 direct path read     oracle@SONPRJ1DB (P006)                          applmgr

9 rows selected.

SQL>

What is a direct path read?

A direct read is a physical I/O from a data file that bypasses the buffer cache and reads the data block directly into process-private memory.

so we are checking when last GSS(Gather Schema Stats Ran)

###############FROM DB NODE FINDOUT SQLID HISTORY##############

set lines 1000 pages 9999

COL instance_number FOR 9999 HEA 'Inst';

COL end_time HEA 'End Time';

COL plan_hash_value HEA 'Plan|Hash Value';

COL executions_total FOR 999,999 HEA 'Execs|Total';

COL rows_per_exec HEA 'Rows Per Exec';

COL et_secs_per_exec HEA 'Elap Secs|Per Exec';

COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';

COL io_secs_per_exec HEA 'IO Secs|Per Exec';

COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';

COL ap_secs_per_exec HEA 'App Secs|Per Exec';

COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';

COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';

COL ja_secs_per_exec HEA 'Java Secs|Per Exec';

SELECT 'gv$dba_hist_sqlstat' source,h.instance_number,

       TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,

       TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,

       h.sql_id,

       h.plan_hash_value, 

       h.executions_total,

       TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,

       TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,

       TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,

       TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,

       TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,

       TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,

       TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,

       TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,

       TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec

  FROM dba_hist_sqlstat h, 

       dba_hist_snapshot s

 WHERE h.sql_id = '&sql_id'

   AND h.executions_total > 0 

   AND s.snap_id = h.snap_id

   AND s.dbid = h.dbid

   AND s.instance_number = h.instance_number

UNION ALL  

SELECT 'gv$sqlarea_plan_hash' source,h.inst_id, 

       TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,

       TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,

       h.sql_id,

       h.plan_hash_value, 

       h.executions,

       TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec,

       TO_CHAR(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') et_secs_per_exec,

       TO_CHAR(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') cpu_secs_per_exec,

       TO_CHAR(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') io_secs_per_exec,

       TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cl_secs_per_exec,

       TO_CHAR(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') ap_secs_per_exec,

       TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cc_secs_per_exec,

       TO_CHAR(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') pl_secs_per_exec,

       TO_CHAR(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') ja_secs_per_exec

  FROM gv$sqlarea_plan_hash h 

 WHERE h.sql_id = '&sql_id'

   AND h.executions > 0 

order by source ;

                                                                                 Plan    Execs                  Elap Secs    CPU Secs     IO Secs      Clus Secs    App Secs         Conc Secs    PLSQL Secs   Java Secs

SOURCE                Inst SNAP_TIME        End Time         SQL_ID        Hash Value    Total Rows Per Exec    Per Exec     Per Exec     Per Exec     Per Exec     Per Exec         Per Exec     Per Exec     Per Exec

-------------------- ----- ---------------- ---------------- ------------- ---------- -------- ---------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------

gv$dba_hist_sqlstat      1 14-05-2021 18:30 14-05-2021 19:00 4w8bh2ad16g4u 2026566432        6                0   45,255.795      964.531   44,384.771        0.000   0.018 0.072        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 13:00 14-05-2021 13:30 4w8bh2ad16g4u 2026566432        4                0   28,320.286      602.027   27,776.749        0.000   0.024 0.082        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 22:00 14-05-2021 22:30 4w8bh2ad16g4u 2026566432        6                0   62,109.271    1,279.751   60,952.480        0.000   0.018 0.081        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 23:00 14-05-2021 23:31 4w8bh2ad16g4u 2026566432        6                0   66,924.387    1,378.192   65,678.593        0.000   0.018 0.082        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 08:30 15-05-2021 09:00 4w8bh2ad16g4u 2026566432        6                0  112,513.962    2,229.634  110,494.056        0.000   0.018 0.097        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 09:00 19-05-2021 09:30 4w8bh2ad16g4u 2026566432       10                0   99,700.686    1,928.695   97,952.110        0.000   0.024 0.110        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 17:00 14-05-2021 17:30 4w8bh2ad16g4u 2026566432        6                0   38,111.568      811.952   37,379.061        0.000   0.018 0.068        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 09:30 19-05-2021 10:00 4w8bh2ad16g4u 2026566432       10                0  101,146.939    1,956.427   99,373.174        0.000   0.024 0.110        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 17:30 14-05-2021 18:00 4w8bh2ad16g4u 2026566432        6                0   40,519.436      865.089   39,737.750        0.000   0.018 0.070        0.000        0.000

gv$dba_hist_sqlstat      1 17-05-2021 14:30 17-05-2021 15:00 4w8bh2ad16g4u 2026566432        8                0  103,273.257    2,007.030  101,453.288        0.000   0.019 0.090        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 18:00 14-05-2021 18:30 4w8bh2ad16g4u 2026566432        6                0   42,846.802      914.949   42,020.615        0.000   0.018 0.071        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 11:00 14-05-2021 11:30 4w8bh2ad16g4u 2026566432        2                0   27,701.911      587.474   27,172.333        0.000   0.047 0.150        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 01:00 15-05-2021 01:30 4w8bh2ad16g4u 2026566432        6                0   76,474.697    1,558.528   75,064.596        0.000   0.018 0.085        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 04:31 15-05-2021 05:00 4w8bh2ad16g4u 2026566432        6                0   93,248.643    1,884.926   91,542.799        0.000   0.018 0.090        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 13:00 15-05-2021 13:30 4w8bh2ad16g4u 2026566432        6                0  134,102.542    2,612.183  131,733.415        0.000   0.018 0.104        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 19:00 14-05-2021 19:30 4w8bh2ad16g4u 2026566432        6                0   47,667.128    1,010.066   46,753.761        0.000   0.018 0.075        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 01:30 15-05-2021 02:00 4w8bh2ad16g4u 2026566432        6                0   78,883.487    1,604.812   77,431.195        0.000   0.018 0.086        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 05:00 15-05-2021 05:30 4w8bh2ad16g4u 2026566432        6                0   95,656.319    1,934.970   93,905.233        0.000   0.018 0.091        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 09:30 15-05-2021 10:00 4w8bh2ad16g4u 2026566432        6                0  117,247.596    2,316.651  115,148.400        0.000   0.018 0.099        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 20:00 14-05-2021 20:30 4w8bh2ad16g4u 2026566432        6                0   52,477.904    1,089.641   51,492.317        0.000   0.018 0.077        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 10:00 15-05-2021 10:30 4w8bh2ad16g4u 2026566432        6                0  119,656.673    2,358.568  117,519.604        0.000   0.018 0.100        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 10:30 15-05-2021 11:00 4w8bh2ad16g4u 2026566432        6                0  122,064.119    2,400.390  119,889.296        0.000   0.018 0.100        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 02:00 15-05-2021 02:30 4w8bh2ad16g4u 2026566432        6                0   81,291.142    1,652.694   79,795.533        0.000   0.018 0.086        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 05:30 15-05-2021 06:00 4w8bh2ad16g4u 2026566432        6                0   98,066.737    1,979.314   96,275.543        0.000   0.018 0.092        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 21:30 14-05-2021 22:00 4w8bh2ad16g4u 2026566432        6                0   59,700.297    1,233.316   58,585.799        0.000   0.018 0.080        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 02:30 15-05-2021 03:00 4w8bh2ad16g4u 2026566432        6                0   83,700.151    1,701.515   82,160.054        0.000   0.018 0.087        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 06:00 15-05-2021 06:30 4w8bh2ad16g4u 2026566432        6                0  100,473.157    2,021.395   98,643.311        0.000   0.018 0.093        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 11:00 15-05-2021 11:30 4w8bh2ad16g4u 2026566432        6                0  124,472.815    2,441.985  122,259.728        0.000   0.018 0.101        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 03:00 15-05-2021 03:30 4w8bh2ad16g4u 2026566432        6                0   86,106.505    1,749.274   84,523.217        0.000   0.018 0.088        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 07:00 15-05-2021 07:30 4w8bh2ad16g4u 2026566432        6                0  105,289.578    2,105.466  103,383.448        0.000   0.018 0.095        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 14:00 15-05-2021 14:30 4w8bh2ad16g4u 2026566432        7                0  117,319.407    2,279.844  115,251.908        0.000   0.015 0.090        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 23:31 15-05-2021 00:00 4w8bh2ad16g4u 2026566432        6                0   69,250.679    1,426.574   67,960.800        0.000   0.018 0.083        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 11:30 15-05-2021 12:00 4w8bh2ad16g4u 2026566432        6                0  126,879.212    2,484.025  124,627.496        0.000   0.018 0.102        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 00:00 15-05-2021 00:30 4w8bh2ad16g4u 2026566432        6                0   71,662.057    1,475.260   70,327.163        0.000   0.018 0.084        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 03:30 15-05-2021 04:00 4w8bh2ad16g4u 2026566432        6                0   88,515.594    1,797.915   86,888.340        0.000   0.018 0.089        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 07:30 15-05-2021 08:00 4w8bh2ad16g4u 2026566432        6                0  107,697.300    2,147.038  105,753.290        0.000   0.018 0.095        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 12:00 15-05-2021 12:30 4w8bh2ad16g4u 2026566432        6                0  129,285.795    2,526.365  126,995.496        0.000   0.018 0.103        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 15:00 14-05-2021 15:30 4w8bh2ad16g4u 2026566432        4                0   42,649.439      906.347   41,830.974        0.000   0.024 0.092        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 08:00 15-05-2021 08:30 4w8bh2ad16g4u 2026566432        6                0  110,103.642    2,188.239  108,121.781        0.000   0.018 0.096        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 00:30 15-05-2021 01:00 4w8bh2ad16g4u 2026566432        6                0   74,068.434    1,516.746   72,696.428        0.000   0.018 0.084        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 04:00 15-05-2021 04:31 4w8bh2ad16g4u 2026566432        6                0   90,921.385    1,845.857   89,250.471        0.000   0.018 0.090        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 09:00 15-05-2021 09:30 4w8bh2ad16g4u 2026566432        6                0  114,921.622    2,272.122  112,862.996        0.000   0.018 0.098        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 12:30 15-05-2021 13:00 4w8bh2ad16g4u 2026566432        6                0  131,694.822    2,569.525  129,364.523        0.000   0.018 0.103        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 16:30 14-05-2021 17:00 4w8bh2ad16g4u 2026566432        6                0   35,704.043      759.279   35,018.902        0.000   0.018 0.066        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 19:30 14-05-2021 20:00 4w8bh2ad16g4u 2026566432        6                0   50,068.860    1,043.280   49,125.185        0.000   0.018 0.076        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 07:00 19-05-2021 07:30 4w8bh2ad16g4u 2026566432       10                0   93,969.035    1,824.410   92,315.391        0.000   0.024 0.107        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 12:00 19-05-2021 12:30 4w8bh2ad16g4u 2026566432       11                0   98,520.533    1,901.630   96,795.967        0.000   0.022 0.104        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 10:30 19-05-2021 11:00 4w8bh2ad16g4u 2026566432       10                0  104,036.267    2,012.157  102,211.846        0.000   0.024 0.112        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 06:30 15-05-2021 07:00 4w8bh2ad16g4u 2026566432        6                0  102,880.546    2,063.604  101,012.561        0.000   0.018 0.094        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 13:00 19-05-2021 13:30 4w8bh2ad16g4u 2026566432       11                0  101,148.400    1,947.808   99,381.904        0.000   0.022 0.105        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 12:30 14-05-2021 13:00 4w8bh2ad16g4u 2026566432        3                0   32,935.904      698.571   32,304.308        0.000   0.032 0.107        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 14:00 14-05-2021 14:30 4w8bh2ad16g4u 2026566432        4                0   35,423.532      753.706   34,742.924        0.000   0.024 0.086        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 16:00 14-05-2021 16:30 4w8bh2ad16g4u 2026566432        6                0   33,294.842      706.804   32,656.958        0.000   0.018 0.065        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 12:30 19-05-2021 13:00 4w8bh2ad16g4u 2026566432       11                0   99,834.561    1,924.879   98,088.534        0.000   0.022 0.104        0.000        0.000

gv$dba_hist_sqlstat      1 18-05-2021 12:30 18-05-2021 13:00 4w8bh2ad16g4u 2026566432       10                0   82,627.847    1,606.044   81,171.365        0.000   0.019 0.091        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 08:00 19-05-2021 08:30 4w8bh2ad16g4u 2026566432       10                0   96,858.993    1,876.820   95,157.722        0.000   0.024 0.108        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 21:00 14-05-2021 21:30 4w8bh2ad16g4u 2026566432        6                0   57,293.920    1,185.328   56,222.478        0.000   0.018 0.079        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 12:00 14-05-2021 12:30 4w8bh2ad16g4u 2026566432        3                0   28,118.132      594.349   27,581.625        0.000   0.032 0.105        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 22:30 14-05-2021 23:00 4w8bh2ad16g4u 2026566432        6                0   64,516.705    1,327.678   63,317.223        0.000   0.018 0.081        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 13:30 14-05-2021 14:00 4w8bh2ad16g4u 2026566432        4                0   31,811.892      676.409   31,201.376        0.000   0.024 0.085        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 14:30 14-05-2021 15:00 4w8bh2ad16g4u 2026566432        4                0   39,036.730      831.749   38,285.370        0.000   0.024 0.089        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 15:30 14-05-2021 16:00 4w8bh2ad16g4u 2026566432        5                0   37,020.804      784.024   36,311.886        0.000   0.019 0.076        0.000        0.000

gv$dba_hist_sqlstat      1 13-05-2021 14:00 13-05-2021 14:30 4w8bh2ad16g4u 2026566432        2                0       16.369        0.402       16.252        0.000   0.001 0.088        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 20:30 14-05-2021 21:00 4w8bh2ad16g4u 2026566432        6                0   54,886.266    1,137.309   53,858.238        0.000   0.018 0.078        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 04:00 19-05-2021 04:30 4w8bh2ad16g4u 2026566432       10                0   85,299.788    1,663.014   83,792.212        0.000   0.024 0.102        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 10:00 14-05-2021 10:30 4w8bh2ad16g4u 2026566432        2                0   13,255.086      279.059   13,004.965        0.000   0.047 0.144        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 04:30 19-05-2021 05:00 4w8bh2ad16g4u 2026566432       10                0   86,745.241    1,688.584   85,214.417        0.000   0.024 0.103        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 10:00 19-05-2021 10:30 4w8bh2ad16g4u 2026566432       10                0  102,590.043    1,985.682  100,789.871        0.000   0.024 0.112        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 11:30 14-05-2021 12:00 4w8bh2ad16g4u 2026566432        3                0   23,302.685      494.209   22,855.428        0.000   0.032 0.103        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 07:30 19-05-2021 08:00 4w8bh2ad16g4u 2026566432       10                0   95,414.300    1,850.772   93,736.686        0.000   0.024 0.107        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 11:00 19-05-2021 11:30 4w8bh2ad16g4u 2026566432       10                0  105,480.867    2,038.623  103,632.163        0.000   0.024 0.113        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 03:30 19-05-2021 04:00 4w8bh2ad16g4u 2026566432       10                0   83,904.763    1,633.519   82,423.616        0.000   0.024 0.101        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 09:30 14-05-2021 10:00 4w8bh2ad16g4u 2026566432        2                0    6,028.515      124.935    5,918.125        0.000   0.047 0.140        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 11:30 19-05-2021 12:00 4w8bh2ad16g4u 2026566432       11                0   97,207.960    1,877.481   95,505.516        0.000   0.022 0.103        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 08:30 19-05-2021 09:00 4w8bh2ad16g4u 2026566432       10                0   98,303.478    1,902.951   96,578.649        0.000   0.024 0.108        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 05:00 19-05-2021 05:30 4w8bh2ad16g4u 2026566432       10                0   88,190.688    1,718.403   86,632.949        0.000   0.024 0.104        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 13:30 15-05-2021 14:00 4w8bh2ad16g4u 2026566432        6                0  136,510.277    2,654.198  134,103.213        0.000   0.018 0.104        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 10:30 14-05-2021 11:00 4w8bh2ad16g4u 2026566432        2                0   20,478.511      433.606   20,089.304        0.000   0.047 0.147        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 05:30 19-05-2021 06:00 4w8bh2ad16g4u 2026566432       10                0   89,636.086    1,746.957   88,052.600        0.000   0.024 0.104        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 06:00 19-05-2021 06:30 4w8bh2ad16g4u 2026566432       10                0   91,079.932    1,772.745   89,473.023        0.000   0.024 0.105        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 06:30 19-05-2021 07:00 4w8bh2ad16g4u 2026566432       10                0   92,524.603    1,798.666   90,894.147        0.000   0.024 0.106        0.000        0.000

gv$sqlarea_plan_hash     1 19-05-2021 13:33 19-05-2021 13:33 4w8bh2ad16g4u 2026566432       11                0  101,272.342    1,949.845   99,503.868        0.000   0.022 0.000        0.000        0.000

82 rows selected.

SQL>

#####################FROM DB NODE LAST ANALYZED (GSS) FOR PROBLEMATIC SQL ID####################################

set lines 300 set pages 300

col table_name for a40

col owner for a30 

select distinct owner, table_name, STALE_STATS, last_analyzed, stattype_locked

  from dba_tab_statistics

  where (owner, table_name) in

  (select distinct owner, table_name

          from dba_tables

          where ( table_name)

          in ( select object_name

                  from gv$sql_plan

                  where upper(sql_id) = upper('&sql_id') and object_name is not null))

  --and STALE_STATS='YES'

/

OWNER                          TABLE_NAME                               STA LAST_ANALYZED   STATT

------------------------------ ---------------------------------------- --- --------------- -----

XLA                            XLA_AE_HEADERS                           NO  13-MAY-21

XLA                            XLA_AE_HEADERS                           NO  21-MAR-21

XLA                            XLA_AE_HEADERS                           NO  21-MAR-21

XLA                            XLA_AE_LINES                             NO  21-MAR-21

XLA                            XLA_AE_HEADERS                           NO  21-MAR-21

XLA                            XLA_AE_LINES                             NO  13-MAY-21

XLA                            XLA_AE_HEADERS                           NO  21-MAR-21

XLA                            XLA_AE_HEADERS                           NO  13-MAY-21

XLA                            XLA_AE_LINES                             NO  21-MAR-21

XLA                            XLA_AE_HEADERS                           NO  21-MAR-21

XLA                            XLA_AE_LINES                             NO  21-MAR-21

XLA                            XLA_AE_LINES                             NO  13-MAY-21

XLA                            XLA_AE_HEADERS                           NO  21-MAR-21

XLA                            XLA_AE_LINES                             NO  21-MAR-21

XLA                            XLA_AE_HEADERS                           NO  21-MAR-21

XLA                            XLA_AE_LINES                             NO  21-MAR-21

16 rows selected.

NOTE: IN THIS CASE GSS LOOKS GOOD

#####################FROM DB NODE SQL TUNNING ADVISOR FOR PROBLEMATIC SQL ID####################################

I. Create a tuning task

SET serveroutput ON

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          sql_id      => '&&my_sql_id',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 60,

                          task_name   => 'sql_tuning_task_&&my_sql_id',

                          description => 'Tuning task for statement &&my_sql_id.');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

Enter value for my_sql_id: 4w8bh2ad16g4u

OUTPUT:

=======

old   5:                           sql_id      => '&&my_sql_id',

new   5:                           sql_id      => '4w8bh2ad16g4u',

old   8:                           task_name   => 'sql_tuning_task_&&my_sql_id',

new   8:                           task_name   => 'sql_tuning_task_4w8bh2ad16g4u',

old   9:                           description => 'Tuning task for statement &&my_sql_id.');

new   9:                           description => 'Tuning task for statement 4w8bh2ad16g4u');

l_sql_tune_task_id: sql_tuning_task_4w8bh2ad16g4u

PL/SQL procedure successfully completed.

II. Check the status

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

SQL> SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

old   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id'

new   1: SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_4w8bh2ad16g4u'

TASK_NAME                      STATUS

------------------------------ -----------

sql_tuning_task_4w8bh2ad16g4u  INITIAL

III. Execute your SQL Tuning task

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_task_4w8bh2ad16g4u');

PL/SQL procedure successfully completed.

IV. Check the status again

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

SQL> SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_4w8bh2ad16g4u';

TASK_NAME                      STATUS

------------------------------ -----------

sql_tuning_task_4w8bh2ad16g4u  COMPLETED

Step 4: Review the recommendations by SQL Tuning Advisor

SET LINES 150

SET pages 50000

SET long 5000000

SET longc 5000000

 

SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual;

old   1: SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual

new   1: SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_4w8bh2ad16g4u') AS recommendations FROM dual

RECOMMENDATIONS

------------------------------------------------------------------------------------------------------------------------------------------------------

GENERAL INFORMATION SECTION

-------------------------------------------------------------------------------

Tuning Task Name   : sql_tuning_task_4w8bh2ad16g4u

Tuning Task Owner  : SYS

Workload Type      : Single SQL Statement

Execution Count    : 3

Current Execution  : EXEC_308998

Execution Type     : TUNE SQL

Scope              : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status  : COMPLETED

Started at         : 05/19/2021 13:46:07

Completed at       : 05/19/2021 13:46:40

-------------------------------------------------------------------------------

Schema Name: APPS

SQL ID     : 4w8bh2ad16g4u

SQL Text   :  SELECT  /*+ PARALLEL (AEL) leading(aeh) use_nl(ael)   */ 1

                        FROM xla_ae_headers       aeh

                            ,xla_ae_lines         ael

                       WHERE ael.ae_header_id                 =

             aeh.ae_header_id

                         AND ael.application_id               =

             aeh.application_id

                         AND aeh.accounting_entry_status_code = 'F'

                       AND (ael.control_balance_flag  ='P'

                           OR ael.analytical_balance_flag ='P'  )

             AND aeh.accounting_batch_id          =  :1

                          AND ael.application_id                 =

                          :2 FOR UPDATE OF ael.ae_header_id,ael.ae_line_num,

             ael.control_balance_flag,ael.analytical_balance_flag NOWAIT

-------------------------------------------------------------------------------

There are no recommendations to improve the statement.

-------------------------------------------------------------------------------

##################FROM DB NODE SQL ID HISTORY##############

set lines 1000 pages 9999

COL instance_number FOR 9999 HEA 'Inst';

COL end_time HEA 'End Time';

COL plan_hash_value HEA 'Plan|Hash Value';

COL executions_total FOR 999,999 HEA 'Execs|Total';

COL rows_per_exec HEA 'Rows Per Exec';

COL et_secs_per_exec HEA 'Elap Secs|Per Exec';

COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';

COL io_secs_per_exec HEA 'IO Secs|Per Exec';

COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';

COL ap_secs_per_exec HEA 'App Secs|Per Exec';

COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';

COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';

COL ja_secs_per_exec HEA 'Java Secs|Per Exec';

SELECT 'gv$dba_hist_sqlstat' source,h.instance_number,

       TO_CHAR(CAST(s.begin_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') snap_time,

       TO_CHAR(CAST(s.end_interval_time AS DATE), 'DD-MM-YYYY HH24:MI') end_time,

       h.sql_id,

       h.plan_hash_value, 

       h.executions_total,

       TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec,

       TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec,

       TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec,

       TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec,

       TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec,

       TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec,

       TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec,

       TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec,

       TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec

  FROM dba_hist_sqlstat h, 

       dba_hist_snapshot s

 WHERE h.sql_id = '&sql_id'

   AND h.executions_total > 0 

   AND s.snap_id = h.snap_id

   AND s.dbid = h.dbid

   AND s.instance_number = h.instance_number

UNION ALL  

SELECT 'gv$sqlarea_plan_hash' source,h.inst_id, 

       TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') snap_time,

       TO_CHAR(sysdate, 'DD-MM-YYYY HH24:MI') end_time,

       h.sql_id,

       h.plan_hash_value, 

       h.executions,

       TO_CHAR(ROUND(h.rows_processed / h.executions), '999,999,999,999') rows_per_exec,

       TO_CHAR(ROUND(h.elapsed_time / h.executions / 1e6, 3), '999,990.000') et_secs_per_exec,

       TO_CHAR(ROUND(h.cpu_time / h.executions / 1e6, 3), '999,990.000') cpu_secs_per_exec,

       TO_CHAR(ROUND(h.USER_IO_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') io_secs_per_exec,

       TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cl_secs_per_exec,

       TO_CHAR(ROUND(h.APPLICATION_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') ap_secs_per_exec,

       TO_CHAR(ROUND(h.CLUSTER_WAIT_TIME / h.executions / 1e6, 3), '999,990.000') cc_secs_per_exec,

       TO_CHAR(ROUND(h.PLSQL_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') pl_secs_per_exec,

       TO_CHAR(ROUND(h.JAVA_EXEC_TIME / h.executions / 1e6, 3), '999,990.000') ja_secs_per_exec

  FROM gv$sqlarea_plan_hash h 

 WHERE h.sql_id = '&sql_id'

   AND h.executions > 0 

order by source ;

                                                                                 Plan    Execs                  Elap Secs    CPU Secs     IO Secs      Clus Secs    App Secs         Conc Secs    PLSQL Secs   Java Secs

SOURCE                Inst SNAP_TIME        End Time         SQL_ID        Hash Value    Total Rows Per Exec    Per Exec     Per Exec     Per Exec     Per Exec     Per Exec         Per Exec     Per Exec     Per Exec

-------------------- ----- ---------------- ---------------- ------------- ---------- -------- ---------------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------

gv$dba_hist_sqlstat      1 14-05-2021 18:30 14-05-2021 19:00 4w8bh2ad16g4u 2026566432        6                0   45,255.795      964.531   44,384.771        0.000   0.018 0.072        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 13:00 14-05-2021 13:30 4w8bh2ad16g4u 2026566432        4                0   28,320.286      602.027   27,776.749        0.000   0.024 0.082        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 22:00 14-05-2021 22:30 4w8bh2ad16g4u 2026566432        6                0   62,109.271    1,279.751   60,952.480        0.000   0.018 0.081        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 23:00 14-05-2021 23:31 4w8bh2ad16g4u 2026566432        6                0   66,924.387    1,378.192   65,678.593        0.000   0.018 0.082        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 08:30 15-05-2021 09:00 4w8bh2ad16g4u 2026566432        6                0  112,513.962    2,229.634  110,494.056        0.000   0.018 0.097        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 09:00 19-05-2021 09:30 4w8bh2ad16g4u 2026566432       10                0   99,700.686    1,928.695   97,952.110        0.000   0.024 0.110        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 17:00 14-05-2021 17:30 4w8bh2ad16g4u 2026566432        6                0   38,111.568      811.952   37,379.061        0.000   0.018 0.068        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 09:30 19-05-2021 10:00 4w8bh2ad16g4u 2026566432       10                0  101,146.939    1,956.427   99,373.174        0.000   0.024 0.110        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 17:30 14-05-2021 18:00 4w8bh2ad16g4u 2026566432        6                0   40,519.436      865.089   39,737.750        0.000   0.018 0.070        0.000        0.000

gv$dba_hist_sqlstat      1 17-05-2021 14:30 17-05-2021 15:00 4w8bh2ad16g4u 2026566432        8                0  103,273.257    2,007.030  101,453.288        0.000   0.019 0.090        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 18:00 14-05-2021 18:30 4w8bh2ad16g4u 2026566432        6                0   42,846.802      914.949   42,020.615        0.000   0.018 0.071        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 11:00 14-05-2021 11:30 4w8bh2ad16g4u 2026566432        2                0   27,701.911      587.474   27,172.333        0.000   0.047 0.150        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 01:00 15-05-2021 01:30 4w8bh2ad16g4u 2026566432        6                0   76,474.697    1,558.528   75,064.596        0.000   0.018 0.085        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 04:31 15-05-2021 05:00 4w8bh2ad16g4u 2026566432        6                0   93,248.643    1,884.926   91,542.799        0.000   0.018 0.090        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 13:00 15-05-2021 13:30 4w8bh2ad16g4u 2026566432        6                0  134,102.542    2,612.183  131,733.415        0.000   0.018 0.104        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 19:00 14-05-2021 19:30 4w8bh2ad16g4u 2026566432        6                0   47,667.128    1,010.066   46,753.761        0.000   0.018 0.075        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 01:30 15-05-2021 02:00 4w8bh2ad16g4u 2026566432        6                0   78,883.487    1,604.812   77,431.195        0.000   0.018 0.086        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 05:00 15-05-2021 05:30 4w8bh2ad16g4u 2026566432        6                0   95,656.319    1,934.970   93,905.233        0.000   0.018 0.091        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 09:30 15-05-2021 10:00 4w8bh2ad16g4u 2026566432        6                0  117,247.596    2,316.651  115,148.400        0.000   0.018 0.099        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 20:00 14-05-2021 20:30 4w8bh2ad16g4u 2026566432        6                0   52,477.904    1,089.641   51,492.317        0.000   0.018 0.077        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 10:00 15-05-2021 10:30 4w8bh2ad16g4u 2026566432        6                0  119,656.673    2,358.568  117,519.604        0.000   0.018 0.100        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 10:30 15-05-2021 11:00 4w8bh2ad16g4u 2026566432        6                0  122,064.119    2,400.390  119,889.296        0.000   0.018 0.100        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 02:00 15-05-2021 02:30 4w8bh2ad16g4u 2026566432        6                0   81,291.142    1,652.694   79,795.533        0.000   0.018 0.086        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 05:30 15-05-2021 06:00 4w8bh2ad16g4u 2026566432        6                0   98,066.737    1,979.314   96,275.543        0.000   0.018 0.092        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 21:30 14-05-2021 22:00 4w8bh2ad16g4u 2026566432        6                0   59,700.297    1,233.316   58,585.799        0.000   0.018 0.080        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 02:30 15-05-2021 03:00 4w8bh2ad16g4u 2026566432        6                0   83,700.151    1,701.515   82,160.054        0.000   0.018 0.087        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 06:00 15-05-2021 06:30 4w8bh2ad16g4u 2026566432        6                0  100,473.157    2,021.395   98,643.311        0.000   0.018 0.093        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 11:00 15-05-2021 11:30 4w8bh2ad16g4u 2026566432        6                0  124,472.815    2,441.985  122,259.728        0.000   0.018 0.101        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 03:00 15-05-2021 03:30 4w8bh2ad16g4u 2026566432        6                0   86,106.505    1,749.274   84,523.217        0.000   0.018 0.088        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 07:00 15-05-2021 07:30 4w8bh2ad16g4u 2026566432        6                0  105,289.578    2,105.466  103,383.448        0.000   0.018 0.095        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 14:00 15-05-2021 14:30 4w8bh2ad16g4u 2026566432        7                0  117,319.407    2,279.844  115,251.908        0.000   0.015 0.090        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 23:31 15-05-2021 00:00 4w8bh2ad16g4u 2026566432        6                0   69,250.679    1,426.574   67,960.800        0.000   0.018 0.083        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 11:30 15-05-2021 12:00 4w8bh2ad16g4u 2026566432        6                0  126,879.212    2,484.025  124,627.496        0.000   0.018 0.102        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 00:00 15-05-2021 00:30 4w8bh2ad16g4u 2026566432        6                0   71,662.057    1,475.260   70,327.163        0.000   0.018 0.084        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 03:30 15-05-2021 04:00 4w8bh2ad16g4u 2026566432        6                0   88,515.594    1,797.915   86,888.340        0.000   0.018 0.089        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 07:30 15-05-2021 08:00 4w8bh2ad16g4u 2026566432        6                0  107,697.300    2,147.038  105,753.290        0.000   0.018 0.095        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 12:00 15-05-2021 12:30 4w8bh2ad16g4u 2026566432        6                0  129,285.795    2,526.365  126,995.496        0.000   0.018 0.103        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 15:00 14-05-2021 15:30 4w8bh2ad16g4u 2026566432        4                0   42,649.439      906.347   41,830.974        0.000   0.024 0.092        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 08:00 15-05-2021 08:30 4w8bh2ad16g4u 2026566432        6                0  110,103.642    2,188.239  108,121.781        0.000   0.018 0.096        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 00:30 15-05-2021 01:00 4w8bh2ad16g4u 2026566432        6                0   74,068.434    1,516.746   72,696.428        0.000   0.018 0.084        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 04:00 15-05-2021 04:31 4w8bh2ad16g4u 2026566432        6                0   90,921.385    1,845.857   89,250.471        0.000   0.018 0.090        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 09:00 15-05-2021 09:30 4w8bh2ad16g4u 2026566432        6                0  114,921.622    2,272.122  112,862.996        0.000   0.018 0.098        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 12:30 15-05-2021 13:00 4w8bh2ad16g4u 2026566432        6                0  131,694.822    2,569.525  129,364.523        0.000   0.018 0.103        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 16:30 14-05-2021 17:00 4w8bh2ad16g4u 2026566432        6                0   35,704.043      759.279   35,018.902        0.000   0.018 0.066        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 19:30 14-05-2021 20:00 4w8bh2ad16g4u 2026566432        6                0   50,068.860    1,043.280   49,125.185        0.000   0.018 0.076        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 07:00 19-05-2021 07:30 4w8bh2ad16g4u 2026566432       10                0   93,969.035    1,824.410   92,315.391        0.000   0.024 0.107        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 12:00 19-05-2021 12:30 4w8bh2ad16g4u 2026566432       11                0   98,520.533    1,901.630   96,795.967        0.000   0.022 0.104        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 10:30 19-05-2021 11:00 4w8bh2ad16g4u 2026566432       10                0  104,036.267    2,012.157  102,211.846        0.000   0.024 0.112        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 06:30 15-05-2021 07:00 4w8bh2ad16g4u 2026566432        6                0  102,880.546    2,063.604  101,012.561        0.000   0.018 0.094        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 13:00 19-05-2021 13:30 4w8bh2ad16g4u 2026566432       11                0  101,148.400    1,947.808   99,381.904        0.000   0.022 0.105        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 12:30 14-05-2021 13:00 4w8bh2ad16g4u 2026566432        3                0   32,935.904      698.571   32,304.308        0.000   0.032 0.107        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 14:00 14-05-2021 14:30 4w8bh2ad16g4u 2026566432        4                0   35,423.532      753.706   34,742.924        0.000   0.024 0.086        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 13:30 19-05-2021 14:00 4w8bh2ad16g4u 2026566432       11                0  102,460.440    1,972.011  100,671.762        0.000   0.022 0.105        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 16:00 14-05-2021 16:30 4w8bh2ad16g4u 2026566432        6                0   33,294.842      706.804   32,656.958        0.000   0.018 0.065        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 12:30 19-05-2021 13:00 4w8bh2ad16g4u 2026566432       11                0   99,834.561    1,924.879   98,088.534        0.000   0.022 0.104        0.000        0.000

gv$dba_hist_sqlstat      1 18-05-2021 12:30 18-05-2021 13:00 4w8bh2ad16g4u 2026566432       10                0   82,627.847    1,606.044   81,171.365        0.000   0.019 0.091        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 08:00 19-05-2021 08:30 4w8bh2ad16g4u 2026566432       10                0   96,858.993    1,876.820   95,157.722        0.000   0.024 0.108        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 21:00 14-05-2021 21:30 4w8bh2ad16g4u 2026566432        6                0   57,293.920    1,185.328   56,222.478        0.000   0.018 0.079        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 12:00 14-05-2021 12:30 4w8bh2ad16g4u 2026566432        3                0   28,118.132      594.349   27,581.625        0.000   0.032 0.105        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 22:30 14-05-2021 23:00 4w8bh2ad16g4u 2026566432        6                0   64,516.705    1,327.678   63,317.223        0.000   0.018 0.081        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 13:30 14-05-2021 14:00 4w8bh2ad16g4u 2026566432        4                0   31,811.892      676.409   31,201.376        0.000   0.024 0.085        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 14:30 14-05-2021 15:00 4w8bh2ad16g4u 2026566432        4                0   39,036.730      831.749   38,285.370        0.000   0.024 0.089        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 15:30 14-05-2021 16:00 4w8bh2ad16g4u 2026566432        5                0   37,020.804      784.024   36,311.886        0.000   0.019 0.076        0.000        0.000

gv$dba_hist_sqlstat      1 13-05-2021 14:00 13-05-2021 14:30 4w8bh2ad16g4u 2026566432        2                0       16.369        0.402       16.252        0.000   0.001 0.088        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 20:30 14-05-2021 21:00 4w8bh2ad16g4u 2026566432        6                0   54,886.266    1,137.309   53,858.238        0.000   0.018 0.078        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 04:00 19-05-2021 04:30 4w8bh2ad16g4u 2026566432       10                0   85,299.788    1,663.014   83,792.212        0.000   0.024 0.102        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 10:00 14-05-2021 10:30 4w8bh2ad16g4u 2026566432        2                0   13,255.086      279.059   13,004.965        0.000   0.047 0.144        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 04:30 19-05-2021 05:00 4w8bh2ad16g4u 2026566432       10                0   86,745.241    1,688.584   85,214.417        0.000   0.024 0.103        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 10:00 19-05-2021 10:30 4w8bh2ad16g4u 2026566432       10                0  102,590.043    1,985.682  100,789.871        0.000   0.024 0.112        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 11:30 14-05-2021 12:00 4w8bh2ad16g4u 2026566432        3                0   23,302.685      494.209   22,855.428        0.000   0.032 0.103        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 07:30 19-05-2021 08:00 4w8bh2ad16g4u 2026566432       10                0   95,414.300    1,850.772   93,736.686        0.000   0.024 0.107        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 11:00 19-05-2021 11:30 4w8bh2ad16g4u 2026566432       10                0  105,480.867    2,038.623  103,632.163        0.000   0.024 0.113        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 03:30 19-05-2021 04:00 4w8bh2ad16g4u 2026566432       10                0   83,904.763    1,633.519   82,423.616        0.000   0.024 0.101        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 09:30 14-05-2021 10:00 4w8bh2ad16g4u 2026566432        2                0    6,028.515      124.935    5,918.125        0.000   0.047 0.140        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 11:30 19-05-2021 12:00 4w8bh2ad16g4u 2026566432       11                0   97,207.960    1,877.481   95,505.516        0.000   0.022 0.103        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 08:30 19-05-2021 09:00 4w8bh2ad16g4u 2026566432       10                0   98,303.478    1,902.951   96,578.649        0.000   0.024 0.108        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 05:00 19-05-2021 05:30 4w8bh2ad16g4u 2026566432       10                0   88,190.688    1,718.403   86,632.949        0.000   0.024 0.104        0.000        0.000

gv$dba_hist_sqlstat      1 15-05-2021 13:30 15-05-2021 14:00 4w8bh2ad16g4u 2026566432        6                0  136,510.277    2,654.198  134,103.213        0.000   0.018 0.104        0.000        0.000

gv$dba_hist_sqlstat      1 14-05-2021 10:30 14-05-2021 11:00 4w8bh2ad16g4u 2026566432        2                0   20,478.511      433.606   20,089.304        0.000   0.047 0.147        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 05:30 19-05-2021 06:00 4w8bh2ad16g4u 2026566432       10                0   89,636.086    1,746.957   88,052.600        0.000   0.024 0.104        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 06:00 19-05-2021 06:30 4w8bh2ad16g4u 2026566432       10                0   91,079.932    1,772.745   89,473.023        0.000   0.024 0.105        0.000        0.000

gv$dba_hist_sqlstat      1 19-05-2021 06:30 19-05-2021 07:00 4w8bh2ad16g4u 2026566432       10                0   92,524.603    1,798.666   90,894.147        0.000   0.024 0.106        0.000        0.000

gv$sqlarea_plan_hash     1 19-05-2021 14:10 19-05-2021 14:10 4w8bh2ad16g4u 2026566432       11                0  102,879.770    1,980.635  101,083.379        0.000   0.022 0.000        0.000        0.000

83 rows selected.

SQL>

LONG-RUNNING-REQUEST.SQL

 set echo off pages 100 lines 202 

column REQUEST heading 'Request' format a9 

column PHASE heading 'Phase' format A8 

column STATUS heading 'Status' format A8 

column PROGRAM heading 'Program Name' format A60 

column SHORT heading 'Short Name' format A15 

column REQUESTOR heading 'Requestor' format A15 

column START_TIME heading 'Start Time' format A15 

column RUN_TIME justify left heading 'Time(e)' format 999999.9 

column OSPID heading 'OSPID' format a5 

column OS_PIDa heading 'OSPIDA' format a6 

column SID heading 'SID' format 99999 

column serial# heading 'Serial#' format 99999 

select substr(fcrv.request_id,1,9)REQUEST, 

decode(fcrv.phase_code,'P','Pending','R','Running','I','Inactive','Completed')PHASE, 

decode(fcrv.status_code, 

'A','Waiting', 

'B','Resuming', 

'C','Normal', 

'F','Scheduled', 

'G','Warning', 

'H','On Hold', 

'I','Normal', 

'M','No Manager', 

'Q','Standby', 

'R','Normal', 

'S','Suspended', 

'T','Terminating', 

'U','Disabled', 

'W','Paused', 

'X','Terminated', 

'Z','Waiting',fcrv.status_code)STATUS, 

substr(fcrv.program,1,60)PROGRAM, 

substr(fcrv.PROGRAM_SHORT_NAME,1,15)SHORT, 

substr(fcrv.requestor,1,15)REQUESTOR, 

-- to_char(fcrv.actual_start_date,'MM/DD/RR HH24:MI')START_TIME, 

round(((sysdate - fcrv.actual_start_date)*1440),1)RUN_TIME, 

substr(fcr.oracle_process_id,1,7)OSPID, 

s.sid,s.serial# 

--substr(fcr.os_process_id,1,7)OS_PIDa 

from apps.fnd_conc_req_summary_v fcrv, 

apps.fnd_concurrent_requests fcr, 

v$session s, 

v$process p 

where fcrv.phase_code = 'R' 

and fcrv.request_id = fcr.request_id 

and s.paddr = p.addr 

and fcr.oracle_process_id = p.spid 

and fcrv.concurrent_program_id not in ('40112','40113','36887') 

--and trunc(fcrv.actual_start_date) like trunc(sysdate) 

order by PHASE, STATUS, REQUEST desc;

Archivelog-spacealert

 

31 rows selected.

SQL> SELECT NAME,

       (SPACE_LIMIT / 1024 / 1024 / 1024) SPACE_LIMIT_GB,

         ((SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE) / 1024 / 1024 / 1024) AS SPACE_AVAILABLE_GB,

       ROUND((SPACE_USED - SPACE_RECLAIMABLE) / SPACE_LIMIT * 100, 1) AS PERCENT_FULL

  FROM V$RECOVERY_FILE_DEST;

  2    3    4    5

NAME

--------------------------------------------------------------------------------

SPACE_LIMIT_GB SPACE_AVAILABLE_GB PERCENT_FULL

-------------- ------------------ ------------

+RECO

           200                200            0

SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest_size           big integer 200G

SQL>

SQL>

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 300G SCOPE=BOTH;

System altered.

SQL> commit;

Commit complete.

SQL> show parameter DB_RECOVERY_FILE_DEST_SIZE

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest_size           big integer 300G

SQL> SELECT NAME,

       (SPACE_LIMIT / 1024 / 1024 / 1024) SPACE_LIMIT_GB,

         ((SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE) / 1024 / 1024 / 1024) AS SPACE_AVAILABLE_GB,

       ROUND((SPACE_USED - SPACE_RECLAIMABLE) / SPACE_LIMIT * 100, 1) AS PERCENT_FULL

  FROM V$RECOVERY_FILE_DEST;  2    3    4    5

NAME

--------------------------------------------------------------------------------

SPACE_LIMIT_GB SPACE_AVAILABLE_GB PERCENT_FULL

-------------- ------------------ ------------

+RECO

           300                300            0

SQL> alter system switch logfile;

System altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            +RECO

Oldest online log sequence     129336

Next log sequence to archive   129339

Current log sequence           129339

SQL> column name format a15

 select * from v$recovery_file_dest;SQL>

NAME            SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES

--------------- ----------- ---------- ----------------- ---------------

+RECO            3.2212E+11          0                 0               0

[oracle@servername:INSTANCE] /u01/app/oracle/diag/rdbms/modoprod_iad1qp/INSTANCE/trace

$ tail -20f alert_INSTANCE.log

  Current log# 2 seq# 129338 mem# 1: +DATA/modoprod_iad1qp/onlinelog/redolog202.dbf

Thread 1 advanced to log sequence 129339 (LGWR switch)

  Current log# 3 seq# 129339 mem# 0: +DATA/modoprod_iad1qp/onlinelog/redolog301.dbf

  Current log# 3 seq# 129339 mem# 1: +DATA/modoprod_iad1qp/onlinelog/redolog302.dbf

Fri May 21 13:48:22 2021

Archived Log entry 444165 added for thread 1 sequence 129338 ID 0x752f5768 dest 1:

Fri May 21 13:51:27 2021

Thread 1 cannot allocate new log, sequence 129340

Checkpoint not complete

  Current log# 3 seq# 129339 mem# 0: +DATA/modoprod_iad1qp/onlinelog/redolog301.dbf

  Current log# 3 seq# 129339 mem# 1: +DATA/modoprod_iad1qp/onlinelog/redolog302.dbf

Thread 1 cannot allocate new log, sequence 129340

Private strand flush not complete

  Current log# 3 seq# 129339 mem# 0: +DATA/modoprod_iad1qp/onlinelog/redolog301.dbf

  Current log# 3 seq# 129339 mem# 1: +DATA/modoprod_iad1qp/onlinelog/redolog302.dbf

Thread 1 advanced to log sequence 129340 (LGWR switch)

  Current log# 4 seq# 129340 mem# 0: +DATA/modoprod_iad1qp/onlinelog/redolog401.dbf

  Current log# 4 seq# 129340 mem# 1: +DATA/modoprod_iad1qp/onlinelog/redolog402.dbf

Fri May 21 13:51:34 2021

Archived Log entry 444167 added for thread 1 sequence 129339 ID 0x752f5768 dest 1:

^C

[oracle@servername:INSTANCE] /u01/app/oracle/diag/rdbms/modoprod_iad1qp/INSTANCE/trace

$

TO KNOW SCN from ONLINE REDO Log

INSTANCE set lines 200

INSTANCE select group#, status, archived, thread#, sequence#, to_char(first_change#) from v$log;

    GROUP# STATUS           ARC    THREAD#  SEQUENCE# TO_CHAR(FIRST_CHANGE#)

---------- ---------------- --- ---------- ---------- ----------------------------------------

         1 CURRENT          NO           1        205 59316138231

         2 INACTIVE         NO           1        202 59315711627

         3 INACTIVE         NO           1        203 59315880135

         4 INACTIVE         NO           1        204 59315892717

INSTANCE

#######################CURRENT DB SCN and ONLINE REDO LOG SCN DIFFERENCE##############33

INSTANCE select to_char(current_scn),to_char(sysdate,'ddmmyyyy hh24:mi:ss') ddate from v$database;

TO_CHAR(CURRENT_SCN)                     DDATE

---------------------------------------- -----------------

59316265472                              21052021 15:08:00

INSTANCE select group#, status, archived, thread#, sequence#, to_char(first_change#) from v$log;

    GROUP# STATUS           ARC    THREAD#  SEQUENCE# TO_CHAR(FIRST_CHANGE#)

---------- ---------------- --- ---------- ---------- ----------------------------------------

         1 CURRENT          NO           1        205 59316138231

         2 INACTIVE         NO           1        202 59315711627

         3 INACTIVE         NO           1        203 59315880135

         4 INACTIVE         NO           1        204 59315892717

INSTANCE

 

JARSIGN -ORACLE APPS

 1.###KEY CREATION####adjkey -initialize -keysize 2048 -alias LUPIN-ora01

2.####CSR CREATION#####keytool -sigalg SHA256withRSA -certreq -keystore adkeystore.dat -file adkeystore.csr -alias LUPIN-ora01

3.######CSR VALIDATION#####openssl req -text -noout -verify -in adkeystore.csr

***BEFORE THAT REMOVE .dat file and adsign.txt file*****NE_BASE LOCATION**

3.###IMPORT###adjkey -keystore adkeystore.dat -storepass myxuan -import -alias LUPIN-ora01 -trustcacerts -file adkeystore64.cer

4.###KEY VIEW###keytool -list -v -keystore adkeystore.dat

5.###INCASE OF USING KEYTOOL###

keytool -import -file adkeystore64.cer -trustcacerts -alias LUPIN-ora01 -keystore adkeystore.dat -storepass puneet -keypass myxuan -v

   (or)

keytool -import -trustcacerts -alias LUPINcorp -file LUPIN*Corporation.p7b -keystore adkeystore.dat -storepass puneet -keypass myxuan -v

   

6.###KEY VIEW###keytool -list -v -keystore adkeystore.dat

7.REGEN PRODUCT JAR USING ADADMIN

##############################################################################

The most current version of this document can be obtained through My Oracle Support Knowledge Document 1591073.1.

############VALIDATION#####

[appltest@SERVER scripts]$ cd $COMMON_TOP/java/classes/oracle/apps/fnd/jar

[appltest@SERVER jar]$ jarsigner -verify -verbose -certs fndall.jar|head -10

DB-PT

 To change best explain plan ( if customer says it works one day before fine now they are facing slowness)

IF customer provided sqlid then fine,else need to generate AWR report and check sql id based on sqlquerry which is running slow

****plan_chg.sql    ( we have to provide sqlid which it is running slow)

#######################

set pages 8000 lines 200

col starttime for a22

select b.snap_id,b.sql_id, b.plan_hash_value,to_char(begin_interval_time,'YY-MM-DD HH24:MI:SS') starttime, sum(executions_delta) execs,

  sum(elapsed_time_delta)/sum(executions_delta)/1000000 elaps_per_exec,

  sum(cpu_time_delta)/sum(executions_delta)/1000000 cpu_per_exec,

  sum(rows_processed_delta)/sum(executions_delta) rows_per_exec,

  sum(disk_reads_delta)/sum(executions_delta) reads_per_exec

  from dba_hist_snapshot a, dba_hist_sqlstat b

where a.snap_id=b.snap_id and b.sql_id='&&sql_id'

group by b.snap_id,b.sql_id, b.plan_hash_value, to_char(begin_interval_time,'YY-MM-DD HH24:MI:SS')

having sum(executions_delta)>0 order by 4;

*****plan_apply.sql ( This will generate timebased sqlscripts we have to choose right/best sql based on our best explain plan choice)

###############################      (ex|| coe_xfr_sql_profile_aj68z5crt401h_1060130606.sql)

SPO coe_xfr_sql_profile.log;

SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;

SET SERVEROUT ON SIZE UNL;

REM

REM $Header: 215187.1 coe_xfr_sql_profile.sql 11.4.5.5 2013/03/01 carlos.sierra $

REM

REM Copyright (c) 2000-2013, Oracle Corporation. All rights reserved.

REM

REM AUTHOR

REM   carlos.sierra@oracle.com

REM

REM SCRIPT

REM   coe_xfr_sql_profile.sql

REM

REM DESCRIPTION

REM   This script generates another that contains the commands to

REM   create a manual custom SQL Profile out of a known plan from

REM   memory or AWR. The manual custom profile can be implemented

REM   into the same SOURCE system where the plan was retrieved,

REM   or into another similar TARGET system that has same schema

REM   objects referenced by the SQL that generated the known plan.

REM

REM PRE-REQUISITES

REM   1. Oracle Tuning Pack license.

REM

REM PARAMETERS

REM   1. SQL_ID (required)

REM   2. Plan Hash Value for which a manual custom SQL Profile is

REM      needed (required). A list of known plans is presented.

REM      You may choose from list provided or enter a valid phv

REM      from a version of the SQL modified with Hints.

REM

REM EXECUTION

REM   1. Connect into SQL*Plus as user with access to data dictionary.

REM      Do not use SYS.

REM   2. Execute script coe_xfr_sql_profile.sql passing SQL_ID and

REM      plan hash value (parameters can be passed inline or until

REM      requested).

REM

REM EXAMPLE

REM   # sqlplus system

REM   SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];

REM   SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm 2055843663;

REM   SQL> START coe_xfr_sql_profile.sql gnjy0mn4y9pbm;

REM   SQL> START coe_xfr_sql_profile.sql;

REM

REM NOTES

REM   1. For possible errors see coe_xfr_sql_profile.log

REM   2. If SQLT is installed in SOURCE, you can use instead:

REM      sqlt/utl/sqltprofile.sql

REM   3. Be aware that using DBMS_SQLTUNE requires a license for

REM      Oracle Tuning Pack.

REM   4. Use a DBA user but not SYS.

REM   5. If you get "ORA-06532: Subscript outside of limit, ORA-06512: at line 1"

REM      Then you may consider this change (only in a test and disposable system):

REM      create or replace TYPE sys.sqlprof_attr AS VARRAY(5000) of VARCHAR2(500);

REM

SET TERM ON ECHO OFF;

PRO

PRO Parameter 1:

PRO SQL_ID (required)

PRO

DEF sql_id = '&1';

PRO

WITH

p AS (

SELECT plan_hash_value

  FROM gv$sql_plan

 WHERE sql_id = TRIM('&&sql_id.')

   AND other_xml IS NOT NULL

 UNION

SELECT plan_hash_value

  FROM dba_hist_sql_plan

 WHERE sql_id = TRIM('&&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 = TRIM('&&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 = TRIM('&&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;

PRO

PRO Parameter 2:

PRO PLAN_HASH_VALUE (required)

PRO

DEF plan_hash_value = '&2';

PRO

PRO Values passed to coe_xfr_sql_profile:

PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

PRO SQL_ID         : "&&sql_id."

PRO PLAN_HASH_VALUE: "&&plan_hash_value."

PRO

SET TERM OFF ECHO ON;

WHENEVER SQLERROR EXIT SQL.SQLCODE;

-- trim parameters

COL sql_id NEW_V sql_id FOR A30;

COL plan_hash_value NEW_V plan_hash_value FOR A30;

SELECT TRIM('&&sql_id.') sql_id, TRIM('&&plan_hash_value.') plan_hash_value FROM DUAL;

VAR sql_text CLOB;

VAR sql_text2 CLOB;

VAR other_xml CLOB;

EXEC :sql_text := NULL;

EXEC :sql_text2 := NULL;

EXEC :other_xml := NULL;

-- get sql_text from memory

DECLARE

  l_sql_text VARCHAR2(32767);

BEGIN -- 10g see bug 5017909

  FOR i IN (SELECT DISTINCT piece, sql_text

              FROM gv$sqltext_with_newlines

             WHERE sql_id = TRIM('&&sql_id.')

             ORDER BY 1, 2)

  LOOP

    IF :sql_text IS NULL THEN

      DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);

      DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);

    END IF;

    -- removes NUL characters

    l_sql_text := REPLACE(i.sql_text, CHR(00), ' ');

    -- adds a NUL character at the end of each line

    DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00));

  END LOOP;

  -- if found in memory then sql_text is not null

  IF :sql_text IS NOT NULL THEN

    DBMS_LOB.CLOSE(:sql_text);

  END IF;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('getting sql_text from memory: '||SQLERRM);

    :sql_text := NULL;

END;

/

SELECT :sql_text FROM DUAL;

-- get sql_text from awr

DECLARE

  l_sql_text VARCHAR2(32767);

  l_clob_size NUMBER;

  l_offset NUMBER;

BEGIN

  IF :sql_text IS NULL OR NVL(DBMS_LOB.GETLENGTH(:sql_text), 0) = 0 THEN

    SELECT sql_text

      INTO :sql_text2

      FROM dba_hist_sqltext

     WHERE sql_id = TRIM('&&sql_id.')

       AND sql_text IS NOT NULL

       AND ROWNUM = 1;

  END IF;

  -- if found in awr then sql_text2 is not null

  IF :sql_text2 IS NOT NULL THEN

    l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text2), 0);

    l_offset := 1;

    DBMS_LOB.CREATETEMPORARY(:sql_text, TRUE);

    DBMS_LOB.OPEN(:sql_text, DBMS_LOB.LOB_READWRITE);

    -- store in clob as 64 character pieces plus a NUL character at the end of each piece

    WHILE l_offset < l_clob_size

    LOOP

      IF l_clob_size - l_offset > 64 THEN

        l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, 64, l_offset), CHR(00), ' ');

      ELSE -- last piece

        l_sql_text := REPLACE(DBMS_LOB.SUBSTR(:sql_text2, l_clob_size - l_offset + 1, l_offset), CHR(00), ' ');

      END IF;

      DBMS_LOB.WRITEAPPEND(:sql_text, LENGTH(l_sql_text) + 1, l_sql_text||CHR(00));

      l_offset := l_offset + 64;

    END LOOP;

    DBMS_LOB.CLOSE(:sql_text);

  END IF;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('getting sql_text from awr: '||SQLERRM);

    :sql_text := NULL;

END;

/

SELECT :sql_text2 FROM DUAL;

SELECT :sql_text FROM DUAL;

-- validate sql_text

SET TERM ON;

BEGIN

  IF :sql_text IS NULL THEN

    RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');

  END IF;

END;

/

SET TERM OFF;

-- get other_xml from memory

BEGIN

  FOR i IN (SELECT other_xml

              FROM gv$sql_plan

             WHERE sql_id = TRIM('&&sql_id.')

               AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))

               AND other_xml IS NOT NULL

             ORDER BY

                   child_number, id)

  LOOP

    :other_xml := i.other_xml;

    EXIT; -- 1st

  END LOOP;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);

    :other_xml := NULL;

END;

/

-- get other_xml from awr

BEGIN

  IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN

    FOR i IN (SELECT other_xml

                FROM dba_hist_sql_plan

               WHERE sql_id = TRIM('&&sql_id.')

                 AND plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))

                 AND other_xml IS NOT NULL

               ORDER BY

                     id)

    LOOP

      :other_xml := i.other_xml;

      EXIT; -- 1st

    END LOOP;

  END IF;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);

    :other_xml := NULL;

END;

/

-- get other_xml from memory from modified SQL

BEGIN

  IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN

    FOR i IN (SELECT other_xml

                FROM gv$sql_plan

               WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))

                 AND other_xml IS NOT NULL

               ORDER BY

                     child_number, id)

    LOOP

      :other_xml := i.other_xml;

      EXIT; -- 1st

    END LOOP;

  END IF;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('getting other_xml from memory: '||SQLERRM);

    :other_xml := NULL;

END;

/

-- get other_xml from awr from modified SQL

BEGIN

  IF :other_xml IS NULL OR NVL(DBMS_LOB.GETLENGTH(:other_xml), 0) = 0 THEN

    FOR i IN (SELECT other_xml

                FROM dba_hist_sql_plan

               WHERE plan_hash_value = TO_NUMBER(TRIM('&&plan_hash_value.'))

                 AND other_xml IS NOT NULL

               ORDER BY

                     id)

    LOOP

      :other_xml := i.other_xml;

      EXIT; -- 1st

    END LOOP;

  END IF;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('getting other_xml from awr: '||SQLERRM);

    :other_xml := NULL;

END;

/

SELECT :other_xml FROM DUAL;

-- validate other_xml

SET TERM ON;

BEGIN

  IF :other_xml IS NULL THEN

    RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');

  END IF;

END;

/

SET TERM OFF;

-- generates script that creates sql profile in target system:

SET ECHO OFF;

PRO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql.

SET FEED OFF LIN 666 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 FOR WOR;

SET SERVEROUT ON SIZE UNL FOR WOR;

SPO OFF;

SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;

DECLARE

  l_pos NUMBER;

  l_clob_size NUMBER;

  l_offset NUMBER;

  l_sql_text VARCHAR2(32767);

  l_len NUMBER;

  l_hint VARCHAR2(32767);

BEGIN

  DBMS_OUTPUT.PUT_LINE('SPO coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..log;');

  DBMS_OUTPUT.PUT_LINE('SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;');

  DBMS_OUTPUT.PUT_LINE('REM');

  DBMS_OUTPUT.PUT_LINE('REM $Header: 215187.1 coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql 11.4.4.4 '||TO_CHAR(SYSDATE, 'YYYY/MM/DD')||' carlos.sierra $');

  DBMS_OUTPUT.PUT_LINE('REM');

  DBMS_OUTPUT.PUT_LINE('REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.');

  DBMS_OUTPUT.PUT_LINE('REM');

  DBMS_OUTPUT.PUT_LINE('REM AUTHOR');

  DBMS_OUTPUT.PUT_LINE('REM   carlos.sierra@oracle.com');

  DBMS_OUTPUT.PUT_LINE('REM');

  DBMS_OUTPUT.PUT_LINE('REM SCRIPT');

  DBMS_OUTPUT.PUT_LINE('REM   coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql');

  DBMS_OUTPUT.PUT_LINE('REM');

  DBMS_OUTPUT.PUT_LINE('REM DESCRIPTION');

  DBMS_OUTPUT.PUT_LINE('REM   This script is generated by coe_xfr_sql_profile.sql');

  DBMS_OUTPUT.PUT_LINE('REM   It contains the SQL*Plus commands to create a custom');

  DBMS_OUTPUT.PUT_LINE('REM   SQL Profile for SQL_ID &&sql_id. based on plan hash');

  DBMS_OUTPUT.PUT_LINE('REM   value &&plan_hash_value..');

  DBMS_OUTPUT.PUT_LINE('REM   The custom SQL Profile to be created by this script');

  DBMS_OUTPUT.PUT_LINE('REM   will affect plans for SQL commands with signature');

  DBMS_OUTPUT.PUT_LINE('REM   matching the one for SQL Text below.');

  DBMS_OUTPUT.PUT_LINE('REM   Review SQL Text and adjust accordingly.');

  DBMS_OUTPUT.PUT_LINE('REM');

  DBMS_OUTPUT.PUT_LINE('REM PARAMETERS');

  DBMS_OUTPUT.PUT_LINE('REM   None.');

  DBMS_OUTPUT.PUT_LINE('REM');

  DBMS_OUTPUT.PUT_LINE('REM EXAMPLE');

  DBMS_OUTPUT.PUT_LINE('REM   SQL> START coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql;');

  DBMS_OUTPUT.PUT_LINE('REM');

  DBMS_OUTPUT.PUT_LINE('REM NOTES');

  DBMS_OUTPUT.PUT_LINE('REM   1. Should be run as SYSTEM or SYSDBA.');

  DBMS_OUTPUT.PUT_LINE('REM   2. User must have CREATE ANY SQL PROFILE privilege.');

  DBMS_OUTPUT.PUT_LINE('REM   3. SOURCE and TARGET systems can be the same or similar.');

  DBMS_OUTPUT.PUT_LINE('REM   4. To drop this custom SQL Profile after it has been created:');

  DBMS_OUTPUT.PUT_LINE('REM      EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(''coe_&&sql_id._&&plan_hash_value.'');');

  DBMS_OUTPUT.PUT_LINE('REM   5. Be aware that using DBMS_SQLTUNE requires a license');

  DBMS_OUTPUT.PUT_LINE('REM      for the Oracle Tuning Pack.');

  DBMS_OUTPUT.PUT_LINE('REM   6. If you modified a SQL putting Hints in order to produce a desired');

  DBMS_OUTPUT.PUT_LINE('REM      Plan, you can remove the artifical Hints from SQL Text pieces below.');

  DBMS_OUTPUT.PUT_LINE('REM      By doing so you can create a custom SQL Profile for the original');

  DBMS_OUTPUT.PUT_LINE('REM      SQL but with the Plan captured from the modified SQL (with Hints).');

  DBMS_OUTPUT.PUT_LINE('REM');

  DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR EXIT SQL.SQLCODE;');

  DBMS_OUTPUT.PUT_LINE('REM');

  DBMS_OUTPUT.PUT_LINE('VAR signature NUMBER;');

  DBMS_OUTPUT.PUT_LINE('VAR signaturef NUMBER;');

  DBMS_OUTPUT.PUT_LINE('REM');

  DBMS_OUTPUT.PUT_LINE('DECLARE');

  DBMS_OUTPUT.PUT_LINE('sql_txt CLOB;');

  DBMS_OUTPUT.PUT_LINE('h       SYS.SQLPROF_ATTR;');

  DBMS_OUTPUT.PUT_LINE('PROCEDURE wa (p_line IN VARCHAR2) IS');

  DBMS_OUTPUT.PUT_LINE('BEGIN');

  DBMS_OUTPUT.PUT_LINE('DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);');

  DBMS_OUTPUT.PUT_LINE('END wa;');

  DBMS_OUTPUT.PUT_LINE('BEGIN');

  DBMS_OUTPUT.PUT_LINE('DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);');

  DBMS_OUTPUT.PUT_LINE('DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);');

  DBMS_OUTPUT.PUT_LINE('-- SQL Text pieces below do not have to be of same length.');

  DBMS_OUTPUT.PUT_LINE('-- So if you edit SQL Text (i.e. removing temporary Hints),');

  DBMS_OUTPUT.PUT_LINE('-- there is no need to edit or re-align unmodified pieces.');

  l_clob_size := NVL(DBMS_LOB.GETLENGTH(:sql_text), 0);

  l_offset := 1;

  WHILE l_offset < l_clob_size

  LOOP

    l_pos := DBMS_LOB.INSTR(:sql_text, CHR(00), l_offset);

    IF l_pos > 0 THEN

      l_len := l_pos - l_offset;

    ELSE -- last piece

      l_len := l_clob_size - l_pos + 1;

    END IF;

    l_sql_text := DBMS_LOB.SUBSTR(:sql_text, l_len, l_offset);

    /* cannot do such 3 replacement since a line could end with a comment using "--"

    l_sql_text := REPLACE(l_sql_text, CHR(10), ' '); -- replace LF with SP

    l_sql_text := REPLACE(l_sql_text, CHR(13), ' '); -- replace CR with SP

    l_sql_text := REPLACE(l_sql_text, CHR(09), ' '); -- replace TAB with SP

    */

    l_offset := l_offset + l_len + 1;

    IF l_len > 0 THEN

      IF INSTR(l_sql_text, '''[') + INSTR(l_sql_text, ']''') = 0 THEN

        l_sql_text := '['||l_sql_text||']';

      ELSIF INSTR(l_sql_text, '''{') + INSTR(l_sql_text, '}''') = 0 THEN

        l_sql_text := '{'||l_sql_text||'}';

      ELSIF INSTR(l_sql_text, '''<') + INSTR(l_sql_text, '>''') = 0 THEN

        l_sql_text := '<'||l_sql_text||'>';

      ELSIF INSTR(l_sql_text, '''(') + INSTR(l_sql_text, ')''') = 0 THEN

        l_sql_text := '('||l_sql_text||')';

      ELSIF INSTR(l_sql_text, '''"') + INSTR(l_sql_text, '"''') = 0 THEN

        l_sql_text := '"'||l_sql_text||'"';

      ELSIF INSTR(l_sql_text, '''|') + INSTR(l_sql_text, '|''') = 0 THEN

        l_sql_text := '|'||l_sql_text||'|';

      ELSIF INSTR(l_sql_text, '''~') + INSTR(l_sql_text, '~''') = 0 THEN

        l_sql_text := '~'||l_sql_text||'~';

      ELSIF INSTR(l_sql_text, '''^') + INSTR(l_sql_text, '^''') = 0 THEN

        l_sql_text := '^'||l_sql_text||'^';

      ELSIF INSTR(l_sql_text, '''@') + INSTR(l_sql_text, '@''') = 0 THEN

        l_sql_text := '@'||l_sql_text||'@';

      ELSIF INSTR(l_sql_text, '''#') + INSTR(l_sql_text, '#''') = 0 THEN

        l_sql_text := '#'||l_sql_text||'#';

      ELSIF INSTR(l_sql_text, '''%') + INSTR(l_sql_text, '%''') = 0 THEN

        l_sql_text := '%'||l_sql_text||'%';

      ELSIF INSTR(l_sql_text, '''$') + INSTR(l_sql_text, '$''') = 0 THEN

        l_sql_text := '$'||l_sql_text||'$';

      ELSE

        l_sql_text := CHR(96)||l_sql_text||CHR(96);

      END IF;

      DBMS_OUTPUT.PUT_LINE('wa(q'''||l_sql_text||''');');

    END IF;

  END LOOP;

  DBMS_OUTPUT.PUT_LINE('DBMS_LOB.CLOSE(sql_txt);');

  DBMS_OUTPUT.PUT_LINE('h := SYS.SQLPROF_ATTR(');

  DBMS_OUTPUT.PUT_LINE('q''[BEGIN_OUTLINE_DATA]'',');

  FOR i IN (SELECT /*+ opt_param('parallel_execution_enabled', 'false') */

                   SUBSTR(EXTRACTVALUE(VALUE(d), '/hint'), 1, 4000) hint

              FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(:other_xml), '/*/outline_data/hint'))) d)

  LOOP

    l_hint := i.hint;

    WHILE NVL(LENGTH(l_hint), 0) > 0

    LOOP

      IF LENGTH(l_hint) <= 500 THEN

        DBMS_OUTPUT.PUT_LINE('q''['||l_hint||']'',');

        l_hint := NULL;

      ELSE

        l_pos := INSTR(SUBSTR(l_hint, 1, 500), ' ', -1);

        DBMS_OUTPUT.PUT_LINE('q''['||SUBSTR(l_hint, 1, l_pos)||']'',');

        l_hint := '   '||SUBSTR(l_hint, l_pos);

      END IF;

    END LOOP;

  END LOOP;

  DBMS_OUTPUT.PUT_LINE('q''[END_OUTLINE_DATA]'');');

  DBMS_OUTPUT.PUT_LINE(':signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);');

  DBMS_OUTPUT.PUT_LINE(':signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);');

  DBMS_OUTPUT.PUT_LINE('DBMS_SQLTUNE.IMPORT_SQL_PROFILE (');

  DBMS_OUTPUT.PUT_LINE('sql_text    => sql_txt,');

  DBMS_OUTPUT.PUT_LINE('profile     => h,');

  DBMS_OUTPUT.PUT_LINE('name        => ''coe_&&sql_id._&&plan_hash_value.'',');

  DBMS_OUTPUT.PUT_LINE('description => ''coe &&sql_id. &&plan_hash_value. ''||:signature||'' ''||:signaturef||'''',');

  DBMS_OUTPUT.PUT_LINE('category    => ''DEFAULT'',');

  DBMS_OUTPUT.PUT_LINE('validate    => TRUE,');

  DBMS_OUTPUT.PUT_LINE('replace     => TRUE,');

  DBMS_OUTPUT.PUT_LINE('force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );');

  DBMS_OUTPUT.PUT_LINE('DBMS_LOB.FREETEMPORARY(sql_txt);');

  DBMS_OUTPUT.PUT_LINE('END;');

  DBMS_OUTPUT.PUT_LINE('/');

  DBMS_OUTPUT.PUT_LINE('WHENEVER SQLERROR CONTINUE');

  DBMS_OUTPUT.PUT_LINE('SET ECHO OFF;');

  DBMS_OUTPUT.PUT_LINE('PRINT signature');

  DBMS_OUTPUT.PUT_LINE('PRINT signaturef');

  DBMS_OUTPUT.PUT_LINE('PRO');

  DBMS_OUTPUT.PUT_LINE('PRO ... manual custom SQL Profile has been created');

  DBMS_OUTPUT.PUT_LINE('PRO');

  DBMS_OUTPUT.PUT_LINE('SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";');

  DBMS_OUTPUT.PUT_LINE('SPO OFF;');

  DBMS_OUTPUT.PUT_LINE('PRO');

  DBMS_OUTPUT.PUT_LINE('PRO COE_XFR_SQL_PROFILE_&&sql_id._&&plan_hash_value. completed');

END;

/

SPO OFF;

SET DEF ON TERM ON ECHO OFF FEED 6 VER ON HEA ON LIN 80 PAGES 14 LONG 80 LONGC 80 TRIMS OFF TI OFF TIMI OFF SERVEROUT OFF NUMF "" SQLP SQL>;

SET SERVEROUT OFF;

PRO

PRO Execute coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql

PRO on TARGET system in order to create a custom SQL Profile

PRO with plan &&plan_hash_value linked to adjusted sql_text.

PRO

UNDEFINE 1 2 sql_id plan_hash_value

CL COL

PRO

PRO COE_XFR_SQL_PROFILE completed.

###############################OTHERWAY NOT YET TRIED####################

DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => 'lkup888',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => 'lkup888_tuning_task1',

description => 'Tuning task1 for statement lkup888');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'lkup888_tuning_task1');

set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('lkup888_tuning_task1') from dual;

execute dbms_sqltune.accept_sql_profile(task_name =>'lkup888_tuning_task1', task_owner => 'SYS', replace =>TRUE);