Hi Experts,
Please someone move this question to Performance Tuning forum as I am not able to search that forum.
I'm new to Performance Tuning and customer complains that DB gets hanged every day and below is the spreport for that period of time. and issue gets resolved once DB gets bounce(unacceptable workaround)
Below are the wait events always coming in TOP 5.
-------------------------------------------
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
library cache lock 210,226 175,560 835 79.0
cursor: mutex S 1,743,130 42,427 24 19.1
library cache: mutex X 2,085 2,602 1248 1.2
log file sync 2,432 529 218 .2
CPU time 480 .2
-------------------------------------------------------------
<<<<<<<<<< STATSPACK Rreport starts from Below >>>>>>>>>>>>>>>>>>>>.
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1232462049 prod 1 10-Jun-16 15:18 11.2.0.1.0 NO
Host Name Platform CPUs
Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
host01 Microsoft Windows x86 8 8 2 15.9
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 14289 15-Jun-16 11:00:00 332 3.2
End Snap: 14290 15-Jun-16 11:59:36 623 2.5
Elapsed: 59.60 (mins) Av Act Sess: 66.8
DB time: 3,981.86 (mins) DB CPU: 195.80 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 1,296M Std Block Size: 8K
Shared Pool: 1,936M Log Buffer: 6,896K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 66.8 153.8 2.88 0.75
DB CPU(s): 3.3 7.6 0.14 0.04
Redo size: 2,497.5 5,750.8
Logical reads: 2,561.1 5,897.2
Block changes: 15.0 34.6
Physical reads: 0.1 0.3
Physical writes: 2.5 5.7
User calls: 89.3 205.6
Parses: 27.2 62.7
Hard parses: 0.9 2.2
W/A MB processed: 0.8 1.8
Logons: 0.3 0.7
Executes: 23.2 53.5
Rollbacks: 0.0 0.0
Transactions: 0.4
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 Optimal W/A Exec %: 100.00
Library Hit %: 43.57 Soft Parse %: 96.55
Execute to Parse %: -17.34 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 85.85 % Non-Parse CPU: 66.07
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 82.90 83.17
% SQL with executions>1: 88.38 89.85
% Memory for SQL w/exec>1: 90.72 92.42
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
library cache lock 210,226 175,560 835 79.0
cursor: mutex S 1,743,130 42,427 24 19.1
library cache: mutex X 2,085 2,602 1248 1.2
log file sync 2,432 529 218 .2
CPU time 480 .2
-------------------------------------------------------------
Host CPU (CPUs: 8 Cores: 8 Sockets: 2)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
46.76 7.32 45.92
Instance CPU
~~~~~~~~~~~~ % Time (seconds)
-------- --------------
Host: Total time (s): 28,983.6
Host: Busy CPU time (s): 15,674.9
% of time Host is Busy: 54.1
Instance: Total CPU time (s): 11,751.0
% of Busy CPU used for Instance: 75.0
Instance: Total Database time (s): 239,515.6
%DB time waiting for CPU (Resource Mgr): 0.0
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 16,287.4 16,287.4
SGA use (MB): 5,097.1 5,097.1
PGA use (MB): 623.5 886.5
% Host Mem used for SGA+PGA: 35.1 36.7
-------------------------------------------------------------
Time Model System Stats DB/Inst: prod/prod Snaps: 14289-14290
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
connection management call elapsed 237,618.4 99.5
parse time elapsed 237,615.7 99.5
DB CPU 11,747.8 4.9
sql execute elapsed time 556.6 .2
PL/SQL execution elapsed time 6.6 .0
hard parse elapsed time 4.5 .0
hard parse (sharing criteria) elaps 3.6 .0
hard parse (bind mismatch) elapsed 0.6 .0
PL/SQL compilation elapsed time 0.1 .0
repeated bind elapsed time 0.0 .0
failed parse elapsed time 0.0 .0
sequence load elapsed time 0.0 .0
DB time 238,911.8
background elapsed time 603.9
background cpu time 3.2
-------------------------------------------------------------
Foreground Wait Events DB/Inst: prod/prod Snaps: 14289-14290
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
library cache lock 210,226 0 175,560 835 135.4 79.0
cursor: mutex S 1,743,130 100 42,427 24 1,122.4 19.1
library cache: mutex X 2,085 0 2,602 1248 1.3 1.2
log file sync 2,430 0 529 218 1.6 .2
enq: DX - contention 93 11 76 814 0.1 .0
SQL*Net message from dblink 95,702 0 46 0 61.6 .0
kksfbc child completion 209 100 27 128 0.1 .0
Disk file operations I/O 1,455 0 24 17 0.9 .0
inactive transaction branch 10 100 10 1014 0.0 .0
db file sequential read 382 0 2 6 0.2 .0
control file sequential read 2,584 0 1 0 1.7 .0
SQL*Net message to dblink 95,539 0 0 0 61.5 .0
cursor: pin S wait on X 13 0 0 12 0.0 .0
SQL*Net more data to client 2,470 0 0 0 1.6 .0
latch free 4 0 0 8 0.0 .0
latch: shared pool 4 0 0 6 0.0 .0
SQL*Net break/reset to clien 12 0 0 1 0.0 .0
db file scattered read 5 0 0 2 0.0 .0
read by other session 1 0 0 8 0.0 .0
buffer busy waits 9 0 0 1 0.0 .0
asynch descriptor resize 1,560 100 0 0 1.0 .0
SQL*Net message from client 287,451 0 713,268 2481 185.1
jobq slave wait 6,986 100 4,318 618 4.5
single-task message 163 0 4 23 0.1
SQL*Net message to client 287,478 0 1 0 185.1
-------------------------------------------------------------
Background Wait Events DB/Inst: prod/prod Snaps: 14289-14290
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
log file parallel write 2,763 0 366 133 1.8 .2
os thread startup 132 0 84 640 0.1 .0
db file parallel write 818 0 52 64 0.5 .0
control file parallel write 1,144 0 41 36 0.7 .0
control file sequential read 4,801 0 27 6 3.1 .0
Disk file operations I/O 166 0 1 9 0.1 .0
ADR block file write 131 0 1 9 0.1 .0
ADR block file read 239 0 0 1 0.2 .0
log file sync 2 0 0 59 0.0 .0
log file sequential read 14 0 0 8 0.0 .0
db file sequential read 11 0 0 8 0.0 .0
log file single write 4 0 0 15 0.0 .0
direct path write 3 0 0 16 0.0 .0
direct path read 4 0 0 6 0.0 .0
ADR file lock 120 12 0 0 0.1 .0
asynch descriptor resize 1,011 100 0 0 0.7 .0
rdbms ipc message 15,075 78 56,482 3747 9.7
DIAG idle wait 6,396 100 7,137 1116 4.1
Streams AQ: waiting for time 12 67 3,733 ###### 0.0
shared server idle wait 120 100 3,603 30022 0.1
dispatcher timer 60 100 3,601 60013 0.0
pmon timer 1,706 67 3,582 2100 1.1
Space Manager: slave idle wa 674 99 3,570 5297 0.4
Streams AQ: qmn slave idle w 137 0 3,570 26059 0.1
Streams AQ: qmn coordinator 252 50 3,570 14167 0.2
smon timer 27 33 3,448 ###### 0.0
SQL*Net message from client 2,614 0 3 1 1.7
class slave wait 26 0 0 0 0.0
SQL*Net message to client 1,753 0 0 0 1.1
-------------------------------------------------------------
Wait Events (fg and bg) DB/Inst: prod/prod Snaps: 14289-14290
-> s - second, cs - centisecond, ms - millisecond, us - microsecond
-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
library cache lock 210,226 0 175,560 835 135.4 79.0
cursor: mutex S 1,743,130 100 42,427 24 1,122.4 19.1
library cache: mutex X 2,085 0 2,602 1248 1.3 1.2
log file sync 2,432 0 529 218 1.6 .2
log file parallel write 2,763 0 366 133 1.8 .2
os thread startup 132 0 84 640 0.1 .0
enq: DX - contention 93 11 76 814 0.1 .0
db file parallel write 818 0 52 64 0.5 .0
SQL*Net message from dblink 95,702 0 46 0 61.6 .0
control file parallel write 1,144 0 41 36 0.7 .0
control file sequential read 7,385 0 27 4 4.8 .0
kksfbc child completion 209 100 27 128 0.1 .0
Disk file operations I/O 1,621 0 25 16 1.0 .0
inactive transaction branch 10 100 10 1014 0.0 .0
db file sequential read 393 0 2 6 0.3 .0
ADR block file write 131 0 1 9 0.1 .0
ADR block file read 239 0 0 1 0.2 .0
SQL*Net message to dblink 95,539 0 0 0 61.5 .0
cursor: pin S wait on X 13 0 0 12 0.0 .0
log file sequential read 14 0 0 8 0.0 .0
SQL*Net more data to client 2,470 0 0 0 1.6 .0
log file single write 4 0 0 15 0.0 .0
direct path write 3 0 0 16 0.0 .0
latch free 4 0 0 8 0.0 .0
latch: shared pool 4 0 0 6 0.0 .0
direct path read 4 0 0 6 0.0 .0
ADR file lock 120 12 0 0 0.1 .0
SQL*Net break/reset to clien 12 0 0 1 0.0 .0
db file scattered read 5 0 0 2 0.0 .0
read by other session 1 0 0 8 0.0 .0
buffer busy waits 9 0 0 1 0.0 .0
asynch descriptor resize 2,571 100 0 0 1.7 .0
SQL*Net message from client 290,065 0 713,270 2459 186.8
rdbms ipc message 15,075 78 56,482 3747 9.7
DIAG idle wait 6,396 100 7,137 1116 4.1
jobq slave wait 6,986 100 4,318 618 4.5
Streams AQ: waiting for time 12 67 3,733 ###### 0.0
shared server idle wait 120 100 3,603 30022 0.1
dispatcher timer 60 100 3,601 60013 0.0
pmon timer 1,706 67 3,582 2100 1.1
Space Manager: slave idle wa 674 99 3,570 5297 0.4
Streams AQ: qmn slave idle w 137 0 3,570 26059 0.1
Streams AQ: qmn coordinator 252 50 3,570 14167 0.2
smon timer 27 33 3,448 ###### 0.0
single-task message 163 0 4 23 0.1
SQL*Net message to client 289,231 0 1 0 186.2
class slave wait 26 0 0 0 0.0
-------------------------------------------------------------
Wait Event Histogram DB/Inst: prod/prod Snaps: 14289-14290
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
ADR block file read 239 87.0 3.3 6.7 2.9
ADR block file write 131 20.6 39.7 32.8 6.1 .8
ADR file lock 120 100.0
ARCH wait for archivelog l 1 100.0
Disk file operations I/O 1620 95.1 .6 .2 .6 .5 .9 1.9 .2
LGWR wait for redo copy 4 100.0
Log archive I/O 1 100.0
SQL*Net break/reset to cli 12 83.3 8.3 8.3
SQL*Net message from dblin 95K 94.9 1.3 1.2 2.1 .2 .2 .0
SQL*Net message to dblink 95K 100.0
SQL*Net more data to clien 2470 100.0
asynch descriptor resize 2571 100.0
buffer busy waits 9 66.7 33.3
control file parallel writ 1144 1.1 65.9 21.8 11.1 .1
control file sequential re 7385 78.5 1.5 3.2 4.2 10.8 1.0 .9
cursor: mutex S 1742K 99.5 .0 .0 .0 .0 .0 .1 .3
cursor: pin S 2 100.0
cursor: pin S wait on X 13 7.7 76.9 15.4
db file parallel write 818 13.1 8.2 10.9 19.3 24.8 16.4 6.5 .9
db file scattered read 5 40.0 60.0
db file sequential read 383 28.7 1.8 14.4 29.5 22.2 3.1 .3
direct path read 4 25.0 25.0 25.0 25.0
direct path write 3 33.3 33.3 33.3
enq: DX - contention 93 24.7 5.4 11.8 6.5 16.1 16.1 4.3 15.1
inactive transaction branc 10 100.0
kksfbc child completion 209 99.5 .5
latch free 4 25.0 25.0 25.0 25.0
latch: In memory undo latc 1 100.0
latch: call allocation 3 100.0
latch: messages 1 100.0
latch: shared pool 4 25.0 25.0 50.0
library cache lock 210K .5 .1 .2 .3 .5 1.6 71.7 25.1
library cache: mutex X 2088 27.7 .3 .1 .7 1.1 .6 42.4 27.1
log file parallel write 2763 2.3 18.8 25.7 22.6 29.6 1.0
log file sequential read 14 71.4 7.1 7.1 14.3
log file single write 4 25.0 25.0 25.0 25.0
log file sync 2432 .1 3.2 17.3 24.1 19.6 30.1 5.6
os thread startup 132 .8 64.4 .8 22.0 12.1
read by other session 1 100.0
DIAG idle wait 6395 81.6 18.4
SQL*Net message from clien 290K 89.4 4.4 1.7 1.5 1.6 .4 .5 .6
SQL*Net message to client 289K 100.0
SQL*Net more data from cli 2 100.0
Space Manager: slave idle 674 .1 99.9
Streams AQ: qmn coordinato 252 36.1 .8 2.0 10.7 50.4
Streams AQ: qmn slave idle 137 8.0 92.0
Streams AQ: waiting for ti 12 33.3 33.3 33.3
class slave wait 26 100.0
dispatcher timer 60 100.0
jobq slave wait 6986 .2 .0 .0 94.8 5.0
pmon timer 1706 23.9 .1 .4 1.3 .5 3.5 70.5
Wait Event Histogram DB/Inst: prod/prod Snaps: 14289-14290
-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000
-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms
-> % of Waits - value: .0 indicates value was <.05%, null is truly 0
-> Ordered by Event (idle events last)
Total ----------------- % of Waits ------------------
Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
rdbms ipc message 15K 6.0 .3 1.3 .7 1.2 .9 11.9 77.7
shared server idle wait 120 100.0
single-task message 163 12.3 78.5 9.2
smon timer 27 37.0 7.4 3.7 51.9
-------------------------------------------------------------
SQL ordered by CPU DB/Inst: prod/prod Snaps: 14289-14290
-> Total DB CPU (s): 11,748
-> Captured SQL accounts for 3.6% of Total DB CPU
-> SQL reported below exceeded 1.0% of Total DB CPU
CPU CPU per Elapsd Old
Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
191.43 120 1.60 1.6 191.62 385,830 1877942161
Module: JDBC Thin Client
SELECT SUM(nvl(NO_OF_HRS,:"SYS_B_0")) NOS FROM TS_TIMESHEET_CHIL
D WHERE trim(TS_DATE)=TO_DATE(:"SYS_B_1")+:"SYS_B_2" AND TS_ID I
N(SELECT TS_ID FROM TS_TIMESHEET WHERE (SAVE_STATUS=:"SYS_B_3" o
r approval_status=:"SYS_B_4") AND trim(MONTH)=:"SYS_B_5" AND YEA
-------------------------------------------------------------
SQL ordered by Gets DB/Inst: prod/prod Snaps: 14289-14290
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 9,158,372
-> Captured SQL accounts for 101.0% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
4,197,600 180 23,320.0 45.8 12.67 12.83 182906955
Module: w3wp.exe
SELECT DISTINCT a.emplid , a.NAME,(TO_CHAR (a.hire_dt, 'dd/MM/yy
yy')) joindt,(b.location_desc) LOCATION,(c.descr) department,a.s
upervisor_id, (e.j_desin_desc) designation,(a.grade) grade,a.com
pany,f.emailid,a.descr,a.gp_paygroup FROM ps_j_emp_ms_all_vw a I
1,627,417 3,160 515.0 17.8 35.27 35.08 2152467967
Module: JDBC Thin Client
select DISTINCT upper(b.APPROVERID)as APPROVERID,a.approver_lev
el,a.updatedby from atrs_approval_status a inner join app_approv
al_hierarchy_master b on a.approver_level=b.approver_level inne
r join atrs_requisition c on b.comp_loc_mapping=c.authid where
1,094,757 88 12,440.4 12.0 30.25 34.60 2299378077
Module: JDBC Thin Client
SELECT DISTINCT COMP_LOC_MAPPING,APPROVERID,H.APPROVER_LEVEL,A.A
TRREQ_NO,A.AUTHID,A.SELRESPONSELINENO,B.FLIGHTNAME, B.FLIGHTNO,B
.ATR_RES_NO,B.ATR_RES_LINE_NO,B.FRPLACE,B.TOPLACE,TO_CHAR(B.FDAT
E,'DD-MON-YY DY') AS FDATE,B.DEPHOURS, B.DEPMINS, TO_CHAR(TDATE,
570,000 1,140 500.0 6.2 3.31 3.49 3077390234
Module: ORACLE.EXE
SELECT "EMPLID","SUPERVISOR_ID" FROM "PS_J_EMP_MS_ALL_VW" "SYS_A
LIAS_1" WHERE "SUPERVISOR_ID"=:1 AND TO_NUMBER(TRIM("EMPLID"))=:
2 AND TRIM("EMPLID")=TRIM(TO_CHAR(:3))
385,830 120 3,215.3 4.2 191.43 191.62 1877942161
Module: JDBC Thin Client
SELECT SUM(nvl(NO_OF_HRS,:"SYS_B_0")) NOS FROM TS_TIMESHEET_CHIL
D WHERE trim(TS_DATE)=TO_DATE(:"SYS_B_1")+:"SYS_B_2" AND TS_ID I
N(SELECT TS_ID FROM TS_TIMESHEET WHERE (SAVE_STATUS=:"SYS_B_3" o
r approval_status=:"SYS_B_4") AND trim(MONTH)=:"SYS_B_5" AND YEA
139,318 1,699 82.0 1.5 2.31 2.46 3123932673
Module: JDBC Thin Client
select name from jubilantregisternew where employerID=:"SYS_B_0
"
136,817 1,699 80.5 1.5 2.93 3.11 2419850322
Module: JDBC Thin Client
select name from jubilantregisternew where employerID in(SELECT
emp_code FROM PRQ_BUYER WHERE PRQ_NO = :"SYS_B_0" and (pono=:"S
YS_B_1" or pono=:"SYS_B_2") AND COMPANY = :"SYS_B_3")
105,336 1,463 72.0 1.2 1.08 1.49 2174945291
Module: JDBC Thin Client
SELECT qty,to_char(pddate,:"SYS_B_0") pddate,baan_reqno,BAAN_PON
O FROM PRQNEW_ROORKEE_CHILD WHERE trim(location)=:"SYS_B_1" and
pono=:"SYS_B_2" and srno=:"SYS_B_3" and baan_reqno=:"SYS_B_4"
96,720 390 248.0 1.1 0.33 0.32 2166924839
SQL ordered by Gets DB/Inst: prod/prod Snaps: 14289-14290
-> End Buffer Gets Threshold: 10000 Total Buffer Gets: 9,158,372
-> Captured SQL accounts for 101.0% of Total Buffer Gets
-> SQL reported below exceeded 1.0% of Total Buffer Gets
CPU Elapsd Old
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
Module: JDBC Thin Client
SELECT * FROM TS_EMP_LEAVE WHERE trim(leavtype)!=:"SYS_B_0" and
trim(leavtype)!=:"SYS_B_1" and EMPCODE=:"SYS_B_2" AND TO_DATE(:
"SYS_B_3")+:"SYS_B_4" BETWEEN LEAVFRDT AND LEAVTODT
-------------------------------------------------------------
SQL ordered by Reads DB/Inst: prod/prod Snaps: 14289-14290
-> End Disk Reads Threshold: 1000 Total Disk Reads: 430
-> Captured SQL accounts for 114.9% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk Reads
CPU Elapsd
Physical Rds Executions Rds per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
220 2 110.0 51.2 1.25 9.09 333476733
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;
broken BOOLEAN := FALSE; BEGIN statspack.snap; :mydate := next_d
ate; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
191 15 12.7 44.4 0.19 1.05 1969545765
INSERT INTO STATS$LATCH ( SNAP_ID , DBID , INSTANCE_NUMBER , NAM
E , LATCH# , LEVEL# , GETS , MISSES , SLEEPS , IMMEDIATE_GETS ,
IMMEDIATE_MISSES , SPIN_GETS , WAIT_TIME ) SELECT :B3 , :B2 , :B
1 , NAME , LATCH# , LEVEL# , GETS , MISSES , SLEEPS , IMMEDIATE_
104 2 52.0 24.2 0.58 3.87 1221782610
INSERT INTO STATS$SQL_SUMMARY ( SNAP_ID , DBID , INSTANCE_NUMBER
, TEXT_SUBSET , SQL_ID , SHARABLE_MEM , SORTS , MODULE , LOADED
_VERSIONS , FETCHES , EXECUTIONS , PX_SERVERS_EXECUTIONS , END_O
F_FETCH_COUNT , LOADS , INVALIDATIONS , PARSE_CALLS , DISK_READS
72 969 0.1 16.7 0.34 0.64 1057128723
Module: JDBC Thin Client
INSERT INTO EARLY_PAY_INVOICE_DETAIL_LOG ( CUNO, SUNO,
PANO, CNAM, NAMA, SUPP, MAIL, TELP,
AMTI, CCUR, DUED, IDAT, ADAT, TTYPE, DO
CN, ISUP, INVDATE, STAT, CCNO , PBTN , PAMT , PD
21 11 1.9 4.9 0.00 0.02 386388955
select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece fr
om idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by pi
ece#
20 594 0.0 4.7 113.90 164.97 2219928536
Module: w3wp.exe
insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, u
serid,userhost,terminal,action#,returncode, obj$creator,obj$name
,auth$privileges,auth$grantee, new$owner,new$name,ses$actions,se
s$tid,logoff$pread, logoff$lwrite,logoff$dead,comment$text,spare
18 2 9.0 4.2 0.00 0.22 1629027619
INSERT INTO STATS$SQLTEXT ( OLD_HASH_VALUE , TEXT_SUBSET , PIECE
, SQL_ID , SQL_TEXT , ADDRESS , COMMAND_TYPE , LAST_SNAP_ID ) S
ELECT /*+ ordered use_nl(vst) */ NEW_SQL.OLD_HASH_VALUE , NEW_SQ
L.TEXT_SUBSET , VST.PIECE , VST.SQL_ID , VST.SQL_TEXT , VST.ADDR
15 2 7.5 3.5 0.02 0.10 3271175026
INSERT INTO STATS$PARAMETER ( SNAP_ID , DBID , INSTANCE_NUMBER ,
NAME , VALUE , ISDEFAULT , ISMODIFIED ) SELECT :B3 , :B2 , :B1
, I.KSPPINM , SV.KSPPSTVL , SV.KSPPSTDF , DECODE(BITAND(SV.KSPPS
TVF,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') FROM STATS$X$KSPPI I
10 46 0.2 2.3 0.00 0.04 3021139143
select privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from obj
auth$ where obj#=:1 and grantee#=:2 group by privilege#,nvl(col#
,0)
SQL ordered by Reads DB/Inst: prod/prod Snaps: 14289-14290
-> End Disk Reads Threshold: 1000 Total Disk Reads: 430
-> Captured SQL accounts for 114.9% of Total Disk Reads
-> SQL reported below exceeded 1.0% of Total Disk Reads
CPU Elapsd
Physical Rds Executions Rds per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
6 35 0.2 1.4 0.00 0.06 199663413
select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$
where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
5 186 0.0 1.2 0.03 0.04 46939367
Module: JDBC Thin Client
update ts_timesheet_child set no_of_hrs=:1,COMMNETS=:2 where ts_
srno=:3
-------------------------------------------------------------
SQL ordered by Executions DB/Inst: prod/prod Snaps: 14289-14290
-> End Executions Threshold: 100 Total Executions: 83,007
-> Captured SQL accounts for 99.3% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions
CPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
5,836 38,485 6.6 0.00 0.00 2275360153
select /*+ connect_by_filtering */ privilege#,level from sysauth
$ connect by grantee#=prior privilege# and privilege#>0 start wi
th grantee#=:1 and privilege#>0
5,350 7,906 1.5 0.00 0.00 43887102
Module: ORACLE.EXE
SELECT "A1"."PLACE" FROM "PLACEMASTER" "A1" WHERE "A1"."PCODE"=:
"SYS_B_0"
5,276 7,770 1.5 0.00 0.00 736623603
Module: JDBC Thin Client
select place from placemaster@intranetapps where pcode=:"SYS_B_0
"
3,200 754 0.2 0.00 0.00 611924186
Module: JDBC Thin Client
select ATRREQ_NO from atrs_response_hst where ATRREQ_NO= :"SYS_B
_0" and ATREQ_LINENO= :"SYS_B_1"
3,200 5,634 1.8 0.00 0.00 3776940206
Module: JDBC Thin Client
select to_char(fdate,:"SYS_B_0") as fdate,REQSTATUS,APPSTATUS f
rom atrs_requisition where to_date(sysdate,:"SYS_B_1")>to_date(f
date,:"SYS_B_2") and ATRREQ_NO= :"SYS_B_3"
3,160 3,160 1.0 0.01 0.01 2152467967
Module: JDBC Thin Client
select DISTINCT upper(b.APPROVERID)as APPROVERID,a.approver_lev
el,a.updatedby from atrs_approval_status a inner join app_approv
al_hierarchy_master b on a.approver_level=b.approver_level inne
r join atrs_requisition c on b.comp_loc_mapping=c.authid where
2,628 2,604 1.0 0.00 0.00 399830986
Module: JDBC Thin Client
SELECT A.supervisor_id as id,B.NAME,a.grade FROM ps_j_emp_ms_
all_vw@intranetapps A INNER JOIN ps_j_emp_ms_all_vw@intranetapps
B ON A.supervisor_id=B.EMPlid WHERE A.EMPlid=:"SYS_B_0"
2,628 2,604 1.0 0.00 0.00 3669356294
Module: ORACLE.EXE
SELECT "A2"."SUPERVISOR_ID","A1"."NAME","A2"."GRADE" FROM "PS_J_
EMP_MS_ALL_VW" "A2","PS_J_EMP_MS_ALL_VW" "A1" WHERE "A2"."EMPLID
"=:"SYS_B_0" AND "A2"."SUPERVISOR_ID"="A1"."EMPLID"
1,963 0 0.0 0.00 0.00 962520513
Module: JDBC Thin Client
select reqdate from atrs_requisition where reqdate between to_
date(:"SYS_B_0",:"SYS_B_1") and to_date(:"SYS_B_2",:"SYS_B_3")
1,963 3,200 1.6 0.00 0.00 1926944413
Module: JDBC Thin Client
SQL ordered by Executions DB/Inst: prod/prod Snaps: 14289-14290
-> End Executions Threshold: 100 Total Executions: 83,007
-> Captured SQL accounts for 99.3% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions
CPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
select ebill,travel,ticket_unqid,bill_unqid,bill_unqid,cancel_b
ook_req,dcp_request,date_change_line,ref_line,eticketfname,DATE_
CHANGE_PANELTY_flag, to_char(APPROVALDATE,:"SYS_B_0") APPROVALDA
TE,ticketbookingdate,ATRS_STATUS,ATRREQ_NO,verify_user,ATREQ_LIN
1,750 910 0.5 0.00 0.01 1764585079
Module: JDBC Thin Client
SELECT TRIM(T$DSCA) T$DSCA FROM baandb.TTIJUB003255@prodTOBJLL
WHERE TRIM(T$ITEM) = :"SYS_B_0"
1,699 1,261 0.7 0.00 0.00 566835454
Module: JDBC Thin Client
SELECT count(*) amount FROM prqnew WHERE srno=:"SYS_B_0" and lo
cation = :"SYS_B_1" having sum(amount)>=(select limit_ha2 from p
rq_limit where trim(location) = :"SYS_B_2") group by srno
1,699 1,607 0.9 0.00 0.00 2419850322
Module: JDBC Thin Client
select name from jubilantregisternew where employerID in(SELECT
emp_code FROM PRQ_BUYER WHERE PRQ_NO = :"SYS_B_0" and (pono=:"S
YS_B_1" or pono=:"SYS_B_2") AND COMPANY = :"SYS_B_3")
1,699 1,699 1.0 0.00 0.00 3123932673
Module: JDBC Thin Client
select name from jubilantregisternew where employerID=:"SYS_B_0
"
1,697 1,693 1.0 0.00 0.00 2330958930
Module: JDBC Thin Client
select t$dsca as ds from baandb.ttdprq003255@prodTOBJLL where lt
rim(rtrim(t$cotp))=:"SYS_B_0"
1,697 1,697 1.0 0.00 0.00 4285642080
Module: JDBC Thin Client
SELECT DISTINCT TRIM(TABLE_NAME) TABLE_NAME FROM PRQ_MAPPING WH
ERE TRIM(FIELD_TYPE) = 'LONG DESCRIPTION' AND COMPANY=255
1,649 1,649 1.0 0.00 0.00 1931493072
Module: ORACLE.EXE
SELECT "A2"."DESCR","A1"."WFID" FROM "APP_BOOKING_FOR_MAPPING" "
A3","APP_BOOKING_FOR_MST" "A2","APP_WORKFLOW_MAPPING" "A1" WHERE
"A3"."MPID"=:"SYS_B_0" AND "A1"."MPID"="A3"."MPID" AND "A3"."ID
"="A2"."ID"
1,635 1,635 1.0 0.00 0.00 3999796196
Module: JDBC Thin Client
select b.descr,c.wfid frOm app_booking_for_mapping@intranetapps
a inner join app_booking_for_mst@intranetapps b on a.id=b.id inn
er join app_workflow_mapping@intranetapps c on c.mpid=a.mpid whe
re a.mpid=:"SYS_B_0"
1,595 1,595 1.0 0.00 0.00 2278475439
SQL ordered by Executions DB/Inst: prod/prod Snaps: 14289-14290
-> End Executions Threshold: 100 Total Executions: 83,007
-> Captured SQL accounts for 99.3% of Total Executions
-> SQL reported below exceeded 1.0% of Total Executions
CPU per Elap per Old
Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------
Module: JDBC Thin Client
SELECT TO_CHAR(CREATE_DATE,:"SYS_B_0") AssignDt FROM PRQ_BUYER W
HERE PRQ_NO = :"SYS_B_1" and Pono=:"SYS_B_2" AND COMPANY = :"SYS
_B_3"
1,463 1,463 1.0 0.00 0.00 2174945291
Module: JDBC Thin Client
SELECT qty,to_char(pddate,:"SYS_B_0") pddate,baan_reqno,BAAN_PON
O FROM PRQNEW_ROORKEE_CHILD WHERE trim(location)=:"SYS_B_1" and
pono=:"SYS_B_2" and srno=:"SYS_B_3" and baan_reqno=:"SYS_B_4"
1,445 1,467 1.0 0.00 0.00 2811804085
Module: JDBC Thin Client
select distinct descr from prodway_location_mst WHERE TRIM(LOW
ER(CODE))=:"SYS_B_0"
1,436 1,436 1.0 0.00 0.00 2653044671
Module: JDBC Thin Client
SELECT COMPANY_S FROM PRQ_COMP_MAPPING WHERE COMPANY = :"SYS_B_0
" AND UPPER(TRIM(TABLE_NAME)) like :"SYS_B_1"
1,181 101 0.1 0.00 0.00 3172267652
Module: JDBC Thin Client
SELECT AUTHID,ENTITY,LOCATION,businessunit,deptid FROM app_auth
_mst a WHERE AUTHID=:"SYS_B_0" and APPID=:"SYS_B_1"
1,146 1,139 1.0 0.00 0.00 3909017610
Module: JDBC Thin Client
select distinct srno from prqnew where location=:"SYS_B_0" and p
hstatus=:"SYS_B_1" and srno=:"SYS_B_2" having sum(amount)>=(sele
ct limit_ha2 from prq_limit where trim(location) = :"SYS_B_3" )
group by srno union select distinct srno from prqnew where locat
1,140 24 0.0 0.00 0.00 3077390234
Module: ORACLE.EXE
SELECT "EMPLID","SUPERVISOR_ID" FROM "PS_J_EMP_MS_ALL_VW" "SYS_A
LIAS_1" WHERE "SUPERVISOR_ID"=:1 AND TO_NUMBER(TRIM("EMPLID"))=:
2 AND TRIM("EMPLID")=TRIM(TO_CHAR(:3))
969 969 1.0 0.00 0.00 1057128723
Module: JDBC Thin Client
INSERT INTO EARLY_PAY_INVOICE_DETAIL_LOG ( CUNO, SUNO,
PANO, CNAM, NAMA, SUPP, MAIL, TELP,
AMTI, CCUR, DUED, IDAT, ADAT, TTYPE, DO
CN, ISUP, INVDATE, STAT, CCNO , PBTN , PAMT , PD
933 6,104 6.5 0.00 0.00 3840591838
select privilege# from sysauth$ where (grantee#=:1 or grantee#=1
) and privilege#>0
-------------------------------------------------------------
SQL ordered by Parse Calls DB/Inst: prod/prod Snaps: 14289-14290
-> End Parse Calls Threshold: 1000 Total Parse Calls: 97,398
-> Captured SQL accounts for 78.1% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
6,960 0 7.15 2706293347
Module: ORACLE.EXE
SELECT /*+ FULL(P) +*/ * FROM "PS_J_EMP_MS_ALL_VW" P
5,836 5,836 5.99 2275360153
select /*+ connect_by_filtering */ privilege#,level from sysauth
$ connect by grantee#=prior privilege# and privilege#>0 start wi
th grantee#=:1 and privilege#>0
5,350 5,350 5.49 43887102
Module: ORACLE.EXE
SELECT "A1"."PLACE" FROM "PLACEMASTER" "A1" WHERE "A1"."PCODE"=:
"SYS_B_0"
5,350 0 5.49 2728017229
Module: ORACLE.EXE
SELECT /*+ FULL(P) +*/ * FROM "PLACEMASTER" P
5,276 5,276 5.42 736623603
Module: JDBC Thin Client
select place from placemaster@intranetapps where pcode=:"SYS_B_0
"
3,200 3,200 3.29 611924186
Module: JDBC Thin Client
select ATRREQ_NO from atrs_response_hst where ATRREQ_NO= :"SYS_B
_0" and ATREQ_LINENO= :"SYS_B_1"
3,200 3,200 3.29 3776940206
Module: JDBC Thin Client
select to_char(fdate,:"SYS_B_0") as fdate,REQSTATUS,APPSTATUS f
rom atrs_requisition where to_date(sysdate,:"SYS_B_1")>to_date(f
date,:"SYS_B_2") and ATRREQ_NO= :"SYS_B_3"
3,160 3,160 3.24 2152467967
Module: JDBC Thin Client
select DISTINCT upper(b.APPROVERID)as APPROVERID,a.approver_lev
el,a.updatedby from atrs_approval_status a inner join app_approv
al_hierarchy_master b on a.approver_level=b.approver_level inne
r join atrs_requisition c on b.comp_loc_mapping=c.authid where
2,651 0 2.72 1564464260
Module: ORACLE.EXE
SELECT /*+ FULL(P) +*/ * FROM "APP_BOOKING_FOR_MAPPING" P
2,651 0 2.72 2959608355
Module: ORACLE.EXE
SELECT /*+ FULL(P) +*/ * FROM "APP_BOOKING_FOR_MST" P
2,628 2,628 2.70 399830986
Module: JDBC Thin Client
SELECT A.supervisor_id as id,B.NAME,a.grade FROM ps_j_emp_ms_
SQL ordered by Parse Calls DB/Inst: prod/prod Snaps: 14289-14290
-> End Parse Calls Threshold: 1000 Total Parse Calls: 97,398
-> Captured SQL accounts for 78.1% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
all_vw@intranetapps A INNER JOIN ps_j_emp_ms_all_vw@intranetapps
B ON A.supervisor_id=B.EMPlid WHERE A.EMPlid=:"SYS_B_0"
2,628 2,628 2.70 3669356294
Module: ORACLE.EXE
SELECT "A2"."SUPERVISOR_ID","A1"."NAME","A2"."GRADE" FROM "PS_J_
EMP_MS_ALL_VW" "A2","PS_J_EMP_MS_ALL_VW" "A1" WHERE "A2"."EMPLID
"=:"SYS_B_0" AND "A2"."SUPERVISOR_ID"="A1"."EMPLID"
2,332 0 2.39 4011854102
Module: ORACLE.EXE
SELECT /*+ FULL(P) +*/ * FROM "APP_WORKFLOW_MAPPING" P
1,963 1,963 2.02 962520513
Module: JDBC Thin Client
select reqdate from atrs_requisition where reqdate between to_
date(:"SYS_B_0",:"SYS_B_1") and to_date(:"SYS_B_2",:"SYS_B_3")
1,963 1,963 2.02 1926944413
Module: JDBC Thin Client
select ebill,travel,ticket_unqid,bill_unqid,bill_unqid,cancel_b
ook_req,dcp_request,date_change_line,ref_line,eticketfname,DATE_
CHANGE_PANELTY_flag, to_char(APPROVALDATE,:"SYS_B_0") APPROVALDA
TE,ticketbookingdate,ATRS_STATUS,ATRREQ_NO,verify_user,ATREQ_LIN
1,750 1,750 1.80 1764585079
Module: JDBC Thin Client
SELECT TRIM(T$DSCA) T$DSCA FROM baandb.TTIJUB003255@prodTOBJLL
WHERE TRIM(T$ITEM) = :"SYS_B_0"
1,699 1,699 1.74 566835454
Module: JDBC Thin Client
SELECT count(*) amount FROM prqnew WHERE srno=:"SYS_B_0" and lo
cation = :"SYS_B_1" having sum(amount)>=(select limit_ha2 from p
rq_limit where trim(location) = :"SYS_B_2") group by srno
1,699 1,699 1.74 2419850322
Module: JDBC Thin Client
select name from jubilantregisternew where employerID in(SELECT
emp_code FROM PRQ_BUYER WHERE PRQ_NO = :"SYS_B_0" and (pono=:"S
YS_B_1" or pono=:"SYS_B_2") AND COMPANY = :"SYS_B_3")
1,699 1,699 1.74 3123932673
Module: JDBC Thin Client
select name from jubilantregisternew where employerID=:"SYS_B_0
"
1,697 1,697 1.74 2330958930
Module: JDBC Thin Client
select t$dsca as ds from baandb.ttdprq003255@prodTOBJLL where lt
rim(rtrim(t$cotp))=:"SYS_B_0"
SQL ordered by Parse Calls DB/Inst: prod/prod Snaps: 14289-14290
-> End Parse Calls Threshold: 1000 Total Parse Calls: 97,398
-> Captured SQL accounts for 78.1% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls
% Total Old
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
1,697 1,697 1.74 4285642080
Module: JDBC Thin Client
SELECT DISTINCT TRIM(TABLE_NAME) TABLE_NAME FROM PRQ_MAPPING WH
ERE TRIM(FIELD_TYPE) = 'LONG DESCRIPTION' AND COMPANY=255
1,649 1,649 1.69 1931493072
Module: ORACLE.EXE
SELECT "A2"."DESCR","A1"."WFID" FROM "APP_BOOKING_FOR_MAPPING" "
A3","APP_BOOKING_FOR_MST" "A2","APP_WORKFLOW_MAPPING" "A1" WHERE
"A3"."MPID"=:"SYS_B_0" AND "A1"."MPID"="A3"."MPID" AND "A3"."ID
"="A2"."ID"
1,635 1,635 1.68 3999796196
Module: JDBC Thin Client
select b.descr,c.wfid frOm app_booking_for_mapping@intranetapps
a inner join app_booking_for_mst@intranetapps b on a.id=b.id inn
er join app_workflow_mapping@intranetapps c on c.mpid=a.mpid whe
re a.mpid=:"SYS_B_0"
1,595 1,595 1.64 2278475439
Module: JDBC Thin Client
SELECT TO_CHAR(CREATE_DATE,:"SYS_B_0") AssignDt FROM PRQ_BUYER W
HERE PRQ_NO = :"SYS_B_1" and Pono=:"SYS_B_2" AND COMPANY = :"SYS
_B_3"
1,463 1,463 1.50 2174945291
Module: JDBC Thin Client
SELECT qty,to_char(pddate,:"SYS_B_0") pddate,baan_reqno,BAAN_PON
O FROM PRQNEW_ROORKEE_CHILD WHERE trim(location)=:"SYS_B_1" and
pono=:"SYS_B_2" and srno=:"SYS_B_3" and baan_reqno=:"SYS_B_4"
1,445 1,445 1.48 2811804085
Module: JDBC Thin Client
select distinct descr from prodway_location_mst WHERE TRIM(LOW
ER(CODE))=:"SYS_B_0"
1,436 1,436 1.47 2653044671
Module: JDBC Thin Client
SELECT COMPANY_S FROM PRQ_COMP_MAPPING WHERE COMPANY = :"SYS_B_0
" AND UPPER(TRIM(TABLE_NAME)) like :"SYS_B_1"
1,181 1,181 1.21 3172267652
Module: JDBC Thin Client
SELECT AUTHID,ENTITY,LOCATION,businessunit,deptid FROM app_auth
_mst a WHERE AUTHID=:"SYS_B_0" and APPID=:"SYS_B_1"
1,146 1,146 1.18 3909017610
Module: JDBC Thin Client
select distinct srno from prqnew where location=:"SYS_B_0" and p
hstatus=:"SYS_B_1" and srno=:"SYS_B_2" having sum(amount)>=(sele
ct limit_ha2 from prq_limit where trim(location) = :"SYS_B_3" )
group by srno union select distinct srno from prqnew where locat
SQL ordered by Parse Calls DB/Inst: prod/prod Snaps: 14289-14290
-> End Parse Calls Threshold: 1000 Total Parse Calls: 97,398
-> Captured SQL accounts for 78.1% of Total Parse Calls
-> SQL reported below exceeded 1.0% of Total Parse Calls