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>
No comments:
Post a Comment