WORKLOAD REPOSITORY report for

DB Name DB Id Instance Inst num Release RAC Host
VIS 210556022 VIS 1 10.2.0.3.0 NO r12db.sanmateo.ibm.com

  Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 146 12-Sep-08 12:03:59 78 32.2
End Snap: 147 12-Sep-08 12:09:31 77 32.0
Elapsed:   5.54 (mins)    
DB Time:   19.69 (mins)    

 

Report Summary

Cache Sizes

  Begin End    
Buffer Cache: 2,640M 2,640M Std Block Size: 8K
Shared Pool Size: 608M 608M Log Buffer: 15,148K

Load Profile

  Per Second Per Transaction
Redo size: 2,139,421.71 48,270.28
Logical reads: 471,999.31 10,649.39
Block changes: 15,122.60 341.20
Physical reads: 2.69 0.06
Physical writes: 0.10 0.00
User calls: 2,852.97 64.37
Parses: 2,257.15 50.93
Hard parses: 0.52 0.01
Sorts: 2,128.29 48.02
Logons: 0.22 0.01
Executes: 27,298.37 615.91
Transactions: 44.32  

% Blocks changed per Read: 3.20 Recursive Call %: 95.20
Rollback per transaction %: 0.02 Rows per Sort: 1.94

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 99.96 Redo NoWait %: 100.00
Buffer Hit %: 100.00 In-memory Sort %: 100.00
Library Hit %: 99.98 Soft Parse %: 99.98
Execute to Parse %: 91.73 Latch Hit %: 97.50
Parse CPU to Parse Elapsd %: 205.72 % Non-Parse CPU: 99.28

Shared Pool Statistics

  Begin End
Memory Usage %: 34.48 35.55
% SQL with executions>1: 89.33 91.51
% Memory for SQL w/exec>1: 87.57 87.40

Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time   1,151   97.4  
log file parallel write 10,980 9 1 .7 System I/O
log file sync 6,695 6 1 .5 Commit
enq: UL - contention 620 4 7 .4 Application
db file sequential read 669 2 4 .2 User I/O

Main Report


Back to Top

Wait Events Statistics

Back to Top

Time Model Statistics

Statistic Name Time (s) % of DB Time
sql execute elapsed time 1,161.85 98.34
DB CPU 1,150.76 97.40
PL/SQL execution elapsed time 301.28 25.50
parse time elapsed 13.70 1.16
hard parse elapsed time 0.81 0.07
repeated bind elapsed time 0.62 0.05
inbound PL/SQL rpc elapsed time 0.33 0.03
connection management call elapsed time 0.16 0.01
sequence load elapsed time 0.12 0.01
hard parse (sharing criteria) elapsed time 0.08 0.01
PL/SQL compilation elapsed time 0.08 0.01
hard parse (bind mismatch) elapsed time 0.00 0.00
DB time 1,181.48  
background elapsed time 13.17  
background cpu time 1.85  

Back to Wait Events Statistics
Back to Top

Wait Class

Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
System I/O 12,827 0.00 11 1 0.87
Commit 6,695 0.00 6 1 0.45
Concurrency 67,695 0.19 5 0 4.59
Application 796 0.13 4 6 0.05
User I/O 999 0.00 3 3 0.07
Network 919,460 0.00 1 0 62.40
Other 5,330 83.15 0 0 0.36
Configuration 343 0.00 0 0 0.02

Back to Wait Events Statistics
Back to Top

Wait Events

Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
log file parallel write 10,980 0.00 9 1 0.75
log file sync 6,695 0.00 6 1 0.45
enq: UL - contention 620 0.16 4 7 0.04
db file sequential read 669 0.00 2 4 0.05
library cache load lock 69 0.00 2 24 0.00
control file parallel write 154 0.00 2 10 0.01
cursor: pin S wait on X 188 67.55 1 7 0.01
buffer busy waits 60,316 0.00 1 0 4.09
control file sequential read 1,693 0.00 1 0 0.11
SQL*Net message to client 889,271 0.00 1 0 60.36
read by other session 235 0.00 1 3 0.02
enq: TX - index contention 2,613 0.00 1 0 0.18
SQL*Net more data from client 29,284 0.00 1 0 1.99
kksfbc child completion 10 80.00 0 40 0.00
db file scattered read 62 0.00 0 4 0.00
enq: TX - row lock contention 150 0.00 0 1 0.01
latch: cache buffers chains 3,742 0.00 0 0 0.25
enq: HW - contention 65 0.00 0 1 0.00
enq: SQ - contention 276 0.00 0 0 0.02
latch: In memory undo latch 686 0.00 0 0 0.05
enq: TX - contention 25 0.00 0 1 0.00
buffer deadlock 4,543 97.38 0 0 0.31
SQL*Net more data to client 905 0.00 0 0 0.06
rdbms ipc reply 43 0.00 0 0 0.00
row cache lock 64 0.00 0 0 0.00
LGWR wait for redo copy 488 0.00 0 0 0.03
SQL*Net break/reset to client 26 0.00 0 0 0.00
latch: library cache 2 0.00 0 1 0.00
latch free 12 0.00 0 0 0.00
cursor: pin S 205 0.00 0 0 0.01
enq: FB - contention 4 0.00 0 0 0.00
enq: TX - allocate ITL entry 2 0.00 0 0 0.00
direct path write 33 0.00 0 0 0.00
cursor: mutex X 7 0.00 0 0 0.00
cursor: mutex S 8 0.00 0 0 0.00
SQL*Net message from client 889,270 0.00 14,547 16 60.35
Streams AQ: qmn slave idle wait 94 0.00 320 3408 0.01
Streams AQ: qmn coordinator idle wait 145 35.17 320 2209 0.01
PL/SQL lock timer 83 97.59 316 3813 0.01

Back to Wait Events Statistics
Back to Top

Background Wait Events

Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
log file parallel write 10,980 0.00 9 1 0.75
control file parallel write 154 0.00 2 10 0.01
control file sequential read 66 0.00 0 7 0.00
events in waitclass Other 488 0.00 0 0 0.03
rdbms ipc message 9,494 12.23 2,909 306 0.64
smon timer 142 0.00 334 2353 0.01
pmon timer 113 100.00 325 2878 0.01
Streams AQ: qmn slave idle wait 94 0.00 320 3408 0.01
Streams AQ: qmn coordinator idle wait 145 35.17 320 2209 0.01

Back to Wait Events Statistics
Back to Top

Operating System Statistics

Statistic Total
BUSY_TIME 122,233
IDLE_TIME 275,357
IOWAIT_TIME 388
NICE_TIME 0
SYS_TIME 10,064
USER_TIME 110,684
LOAD 1
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 52,930,556
NUM_CPUS 12
NUM_CPU_SOCKETS 4

Back to Wait Events Statistics
Back to Top

Service Statistics

Service Name DB Time (s) DB CPU (s) Physical Reads Logical Reads
SYS$USERS 1,182.40 1,151.60 898 156,909,336
SYS$BACKGROUND 0.00 0.00 0 1,240
VIS 0.00 0.00 0 0

Back to Wait Events Statistics
Back to Top

Service Wait Class Stats

Service Name User I/O Total Wts User I/O Wt Time Concurcy Total Wts Concurcy Wt Time Admin Total Wts Admin Wt Time Network Total Wts Network Wt Time
SYS$USERS 999 336 67695 481 0 0 919237 136
SYS$BACKGROUND 0 0 0 0 0 0 0 0

Back to Wait Events Statistics
Back to Top

 

SQL Statistics

Back to Top

SQL ordered by Elapsed Time

Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
815 815 9,983 0.08 69.00 4f5mjr4hnaz3u PYUSLV declare begin pay_archive...
807 807 9,977 0.08 68.28 5sfm6rppc4y8x PYUSLV BEGIN pay_us_action_arch.actio...
112 112 59,939 0.00 9.45 7rnh3r6aupd00 PYUSLV SELECT /*+ RULE*/ fnd_number.n...
80 80 9,964 0.01 6.75 9jv68n0ctas75 PYUSLV begin pay_core_utils . get_pro...
62 62 279,525 0.00 5.29 3n5uwzsh3y8gr PYUSLV SELECT NVL(SUM(prb.balance_val...
48 48 9,994 0.00 4.06 4ds53shhfcynn PYUSLV declare begin hr_pre_pay....
41 41 9,998 0.00 3.45 80wvw6gatjvb2 PYUSLV SELECT PTP_TD.START_DATE FROM ...
31 27 40 0.79 2.66 dab1prc9qyv3v PYUSLV begin hr_nonrun_asact...
29 29 149,809 0.00 2.45 g3835fnqtvy06 PYUSLV SELECT NVL(SUM(prb.balance_val...
28 28 169,858 0.00 2.34 6500rnmy4f88w PYUSLV SELECT PAP.ACCRUAL_PLAN_ID, P...
25 25 44,935 0.00 2.13 4725065t8x3wk PYUSLV SELECT /*+ RULE*/ fnd_number.n...
19 19 474,298 0.00 1.64 5w5up6vdp2qyp PYUSLV SELECT RP.SEQUENCE_NO, RPV.VA...
16 16 428,020 0.00 1.38 02k10jzw8c495 PYUSLV SELECT INTLK.LOCKING_ACTION_ID...
16 16 476,639 0.00 1.36 g6tx1p7d69dxg PYUSLV SELECT ENT.USER_ENTITY_ID, EN...
16 16 9,999 0.00 1.34 36z3h9rgkd8aa PYUSLV begin ...
16 16 475,989 0.00 1.32 fdv98jawj03hg PYUSLV SELECT FUE.USER_ENTITY_NAME , ...
16 16 690,895 0.00 1.31 4gz4b2bh1fs14 PYUSLV SELECT CONTEXT_ID, SEQUENCE_N...
15 15 109,985 0.00 1.30 6v07302134wyf PYUSLV INSERT INTO PAY_ACTION_INFORMA...
14 14 9,977 0.00 1.15 gq04p7vgjcmkg PYUSLV SELECT PPT.CATEGORY CATEGORY, ...
13 13 9,996 0.00 1.10 2z261p0p82p32 PYUSLV BEGIN FFP51704_01010001.FORMUL...
12 12 10,000 0.00 1.05 7d66dw0vz7yaa PYUSLV begin pay_cc_process_utils . r...

Back to SQL Statistics
Back to Top

SQL ordered by CPU Time

CPU Time (s) Elapsed Time (s) Executions CPU per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
815 815 9,983 0.08 69.00 4f5mjr4hnaz3u PYUSLV declare begin pay_archive...
807 807 9,977 0.08 68.28 5sfm6rppc4y8x PYUSLV BEGIN pay_us_action_arch.actio...
112 112 59,939 0.00 9.45 7rnh3r6aupd00 PYUSLV SELECT /*+ RULE*/ fnd_number.n...
80 80 9,964 0.01 6.75 9jv68n0ctas75 PYUSLV begin pay_core_utils . get_pro...
62 62 279,525 0.00 5.29 3n5uwzsh3y8gr PYUSLV SELECT NVL(SUM(prb.balance_val...
48 48 9,994 0.00 4.06 4ds53shhfcynn PYUSLV declare begin hr_pre_pay....
41 41 9,998 0.00 3.45 80wvw6gatjvb2 PYUSLV SELECT PTP_TD.START_DATE FROM ...
29 29 149,809 0.00 2.45 g3835fnqtvy06 PYUSLV SELECT NVL(SUM(prb.balance_val...
28 28 169,858 0.00 2.34 6500rnmy4f88w PYUSLV SELECT PAP.ACCRUAL_PLAN_ID, P...
27 31 40 0.67 2.66 dab1prc9qyv3v PYUSLV begin hr_nonrun_asact...
25 25 44,935 0.00 2.13 4725065t8x3wk PYUSLV SELECT /*+ RULE*/ fnd_number.n...
19 19 474,298 0.00 1.64 5w5up6vdp2qyp PYUSLV SELECT RP.SEQUENCE_NO, RPV.VA...
16 16 428,020 0.00 1.38 02k10jzw8c495 PYUSLV SELECT INTLK.LOCKING_ACTION_ID...
16 16 476,639 0.00 1.36 g6tx1p7d69dxg PYUSLV SELECT ENT.USER_ENTITY_ID, EN...
16 16 9,999 0.00 1.34 36z3h9rgkd8aa PYUSLV begin ...
16 16 475,989 0.00 1.32 fdv98jawj03hg PYUSLV SELECT FUE.USER_ENTITY_NAME , ...
16 16 690,895 0.00 1.31 4gz4b2bh1fs14 PYUSLV SELECT CONTEXT_ID, SEQUENCE_N...
15 15 109,985 0.00 1.30 6v07302134wyf PYUSLV INSERT INTO PAY_ACTION_INFORMA...
14 14 9,977 0.00 1.15 gq04p7vgjcmkg PYUSLV SELECT PPT.CATEGORY CATEGORY, ...
13 13 9,996 0.00 1.10 2z261p0p82p32 PYUSLV BEGIN FFP51704_01010001.FORMUL...
12 12 10,000 0.00 1.05 7d66dw0vz7yaa PYUSLV begin pay_cc_process_utils . r...

Back to SQL Statistics
Back to Top

SQL ordered by Gets

Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
110,668,273 9,977 11,092.34 70.53 806.63 806.75 5sfm6rppc4y8x PYUSLV BEGIN pay_us_action_arch.actio...
110,210,997 9,983 11,039.87 70.24 815.34 815.27 4f5mjr4hnaz3u PYUSLV declare begin pay_archive...
33,543,170 59,939 559.62 21.38 111.67 111.63 7rnh3r6aupd00 PYUSLV SELECT /*+ RULE*/ fnd_number.n...
15,983,608 279,525 57.18 10.19 62.45 62.45 3n5uwzsh3y8gr PYUSLV SELECT NVL(SUM(prb.balance_val...
12,956,346 9,964 1,300.32 8.26 79.74 79.77 9jv68n0ctas75 PYUSLV begin pay_core_utils . get_pro...
8,865,610 9,998 886.74 5.65 40.79 40.74 80wvw6gatjvb2 PYUSLV SELECT PTP_TD.START_DATE FROM ...
6,433,322 149,809 42.94 4.10 28.92 28.91 g3835fnqtvy06 PYUSLV SELECT NVL(SUM(prb.balance_val...
5,834,136 44,935 129.84 3.72 25.12 25.11 4725065t8x3wk PYUSLV SELECT /*+ RULE*/ fnd_number.n...
5,252,632 474,298 11.07 3.35 19.41 19.41 5w5up6vdp2qyp PYUSLV SELECT RP.SEQUENCE_NO, RPV.VA...
4,726,317 40 118,157.93 3.01 26.81 31.40 dab1prc9qyv3v PYUSLV begin hr_nonrun_asact...
4,717,214 428,020 11.02 3.01 16.29 16.28 02k10jzw8c495 PYUSLV SELECT INTLK.LOCKING_ACTION_ID...
4,228,114 9,994 423.07 2.69 47.90 47.92 4ds53shhfcynn PYUSLV declare begin hr_pre_pay....
4,089,980 475,989 8.59 2.61 15.59 15.58 fdv98jawj03hg PYUSLV SELECT FUE.USER_ENTITY_NAME , ...
4,034,884 476,639 8.47 2.57 16.04 16.03 g6tx1p7d69dxg PYUSLV SELECT ENT.USER_ENTITY_ID, EN...
3,495,924 577,173 6.06 2.23 11.26 11.26 cn5un9cxnah8w PYUSLV SELECT NVL(PBD.PERIOD_TYPE, '...
2,731,060 10,000 273.11 1.74 12.35 12.35 7d66dw0vz7yaa PYUSLV begin pay_cc_process_utils . r...
2,197,654 9,988 220.03 1.40 5.56 5.59 02dvcwb36048p PYUSLV UPDATE PAY_ACTION_INFORMATION ...
2,119,466 9,980 212.37 1.35 4.96 4.96 cj64zg9gg7780 PYUSLV SELECT ACTION_INFORMATION_ID ,...
2,088,510 690,895 3.02 1.33 15.54 15.53 4gz4b2bh1fs14 PYUSLV SELECT CONTEXT_ID, SEQUENCE_N...
2,000,686 109,985 18.19 1.28 15.39 15.39 6v07302134wyf PYUSLV INSERT INTO PAY_ACTION_INFORMA...
1,842,881 9,999 184.31 1.17 15.82 15.83 36z3h9rgkd8aa PYUSLV begin ...
1,795,879 9,997 179.64 1.14 10.55 10.55 7mmdd7sypzu9x PYUSLV BEGIN FFP1893_01010001.FORMULA...
1,746,333 581,273 3.00 1.11 8.61 8.61 77d28bpwm9svg PYUSLV SELECT PBV.RUN_BALANCE_STATUS,...
1,649,147 169,858 9.71 1.05 27.70 27.69 6500rnmy4f88w PYUSLV SELECT PAP.ACCRUAL_PLAN_ID, P...

Back to SQL Statistics
Back to Top

SQL ordered by Reads

Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
147 19,999 0.01 16.44 5.48 6.06 9665ycstch5r4 PYUSLV select /*+ ORDERED USE_NL(AA...
29 5 5.80 3.24 0.02 0.26 g1u8jx4wbddx2 PYUSLV SELECT G.SEGMENT_NAME, G.APPL...
15 1 15.00 1.68 0.02 0.05 31a13pnjps7j3   SELECT source, (case w...
5 9,983 0.00 0.56 815.34 815.27 4f5mjr4hnaz3u PYUSLV declare begin pay_archive...
5 9,977 0.00 0.56 806.63 806.75 5sfm6rppc4y8x PYUSLV BEGIN pay_us_action_arch.actio...
5 109,985 0.00 0.56 15.39 15.39 6v07302134wyf PYUSLV INSERT INTO PAY_ACTION_INFORMA...
5 5 1.00 0.56 0.01 0.09 76duuz8ty1574 PYUSLV SELECT S.SEGMENT_NUM SEGNUM, ...
5 1 5.00 0.56 0.02 0.02 cf621qmts91wf   SELECT /*+ ordered use_nl(stat...
4 1 4.00 0.45 4.19 4.19 1knpzqdv410rs PYUSLV select paa.rowid from...
2 5 0.40 0.22 0.04 0.17 6wpjhjnkaa3rj PYUSLV SELECT F.APPLICATION_ID, F.TA...

Back to SQL Statistics
Back to Top

SQL ordered by Executions

Executions Rows Processed Rows per Exec CPU per Exec (s) Elap per Exec (s) SQL Id SQL Module SQL Text
690,895 1,165,589 1.69 0.00 0.00 4gz4b2bh1fs14 PYUSLV SELECT CONTEXT_ID, SEQUENCE_N...
581,273 583,319 1.00 0.00 0.00 77d28bpwm9svg PYUSLV SELECT PBV.RUN_BALANCE_STATUS,...
577,173 583,551 1.01 0.00 0.00 cn5un9cxnah8w PYUSLV SELECT NVL(PBD.PERIOD_TYPE, '...
476,639 479,620 1.01 0.00 0.00 g6tx1p7d69dxg PYUSLV SELECT ENT.USER_ENTITY_ID, EN...
475,989 478,626 1.01 0.00 0.00 fdv98jawj03hg PYUSLV SELECT FUE.USER_ENTITY_NAME , ...
474,298 956,538 2.02 0.00 0.00 5w5up6vdp2qyp PYUSLV SELECT RP.SEQUENCE_NO, RPV.VA...
428,020 429,213 1.00 0.00 0.00 02k10jzw8c495 PYUSLV SELECT INTLK.LOCKING_ACTION_ID...
279,525 278,422 1.00 0.00 0.00 3n5uwzsh3y8gr PYUSLV SELECT NVL(SUM(prb.balance_val...
169,858 0 0.00 0.00 0.00 6500rnmy4f88w PYUSLV SELECT PAP.ACCRUAL_PLAN_ID, P...
149,809 149,589 1.00 0.00 0.00 g3835fnqtvy06 PYUSLV SELECT NVL(SUM(prb.balance_val...
109,985 109,983 1.00 0.00 0.00 6v07302134wyf PYUSLV INSERT INTO PAY_ACTION_INFORMA...

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
279,136 279,525 37.20 3n5uwzsh3y8gr PYUSLV SELECT NVL(SUM(prb.balance_val...
149,360 149,809 19.91 g3835fnqtvy06 PYUSLV SELECT NVL(SUM(prb.balance_val...
59,852 59,939 7.98 7rnh3r6aupd00 PYUSLV SELECT /*+ RULE*/ fnd_number.n...
44,934 44,935 5.99 4725065t8x3wk PYUSLV SELECT /*+ RULE*/ fnd_number.n...
29,957 29,987 3.99 264qzrka8fsps PYUSLV SELECT NVL(SUM(prb.balance_val...
20,000 19,999 2.67 83kbt99vg4svq PYUSLV begin ...
19,822 19,986 2.64 165d2bgc2b9hr PYUSLV SELECT NVL(SUM(prb.balance_val...
10,000 9,994 1.33 4ds53shhfcynn PYUSLV declare begin hr_pre_pay....
9,999 9,999 1.33 36z3h9rgkd8aa PYUSLV begin ...
9,999 9,983 1.33 4f5mjr4hnaz3u PYUSLV declare begin pay_archive...
9,996 9,977 1.33 5sfm6rppc4y8x PYUSLV BEGIN pay_us_action_arch.actio...
9,996 10,000 1.33 97mt0g20bcfc3 PYUSLV begin ...
9,988 10,000 1.33 3rg5fyfgd5uwc PYUSLV begin pay_US_rules.get_tim...
9,913 10,000 1.32 d7shgtk3p7s4u PYUSLV SELECT /*+ ordered index(BAL...
8,901 8,901 1.19 cr74qtm56vcq6 NACHA BEGIN PAY_US_NACHA_TAPE.RUN_FO...

Back to SQL Statistics
Back to Top

 

SQL ordered by Sharable Memory

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

 

SQL ordered by Version Count

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

 

Complete List of SQL Text

SQL Id SQL Text
02dvcwb36048p UPDATE PAY_ACTION_INFORMATION SET ACTION_INFORMATION_ID = :B1 , OBJECT_VERSION_NUMBER = :B32 , ACTION_INFORMATION1 = :B31 , ACTION_INFORMATION2 = :B30 , ACTION_INFORMATION3 = :B29 , ACTION_INFORMATION4 = :B28 , ACTION_INFORMATION5 = :B27 , ACTION_INFORMATION6 = :B26 , ACTION_INFORMATION7 = :B25 , ACTION_INFORMATION8 = :B24 , ACTION_INFORMATION9 = :B23 , ACTION_INFORMATION10 = :B22 , ACTION_INFORMATION11 = :B21 , ACTION_INFORMATION12 = :B20 , ACTION_INFORMATION13 = :B19 , ACTION_INFORMATION14 = :B18 , ACTION_INFORMATION15 = :B17 , ACTION_INFORMATION16 = :B16 , ACTION_INFORMATION17 = :B15 , ACTION_INFORMATION18 = :B14 , ACTION_INFORMATION19 = :B13 , ACTION_INFORMATION20 = :B12 , ACTION_INFORMATION21 = :B11 , ACTION_INFORMATION22 = :B10 , ACTION_INFORMATION23 = :B9 , ACTION_INFORMATION24 = :B8 , ACTION_INFORMATION25 = :B7 , ACTION_INFORMATION26 = :B6 , ACTION_INFORMATION27 = :B5 , ACTION_INFORMATION28 = :B4 , ACTION_INFORMATION29 = :B3 , ACTION_INFORMATION30 = :B2 WHERE ACTION_INFORMATION_ID = :B1
02k10jzw8c495 SELECT INTLK.LOCKING_ACTION_ID FROM PAY_ACTION_INTERLOCKS INTLK, PAY_ASSIGNMENT_ACTIONS PAA, PAY_PAYROLL_ACTIONS PPA WHERE INTLK.LOCKED_ACTION_ID = :B1 AND INTLK.LOCKING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PPA.ACTION_TYPE IN ('P', 'U') AND PAA.SOURCE_ACTION_ID IS NULL
165d2bgc2b9hr SELECT NVL(SUM(prb.balance_value), 0) FROM /* Assignment within a Government Reporting Entity for YTD based on run balances */ pay_run_balances prb, pay_defined_balances pdb, pay_payroll_actions ppa, pay_assignment_actions paa where paa.assignment_action_id = :ASSIGNMENT_ACTION_ID and prb.tax_unit_id = :TAX_UNIT_ID and ppa.payroll_action_id = paa.payroll_action_id and pdb.balance_type_id = :U1 and pdb.balance_dimension_id = :U2 and pdb.defined_balance_id = prb.defined_balance_id and paa.assignment_id = prb.assignment_id and prb.action_sequence <= paa.action_sequence and prb.effective_date >= trunc(ppa.effective_date, 'Y')
1knpzqdv410rs select paa.rowid from (select paa1.assignment_action_id, hou.name, ppf.last_name, ppf.first_name from hr_organization_units hou, per_people_f ppf, per_assignments_f paf, pay_assignment_actions paa1, pay_payroll_actions ppa1 where paa1.object_type is null and paa1.payroll_action_id = ppa1.payroll_action_id and paa1.payroll_action_id = :pactid and paa1.assignment_id = paf.assignment_id and ppa1.effective_date between paf.effective_start_date and paf.effective_end_date and paf.person_id = ppf.person_id and ppa1.effective_date between ppf.effective_start_date and ppf.effective_end_date and paf.organization_id = hou.organization_id union all select paa1.assignment_action_id, hou.name, null, null from hr_organization_units hou, pay_assignment_actions paa1 where paa1.object_type = 'HOU' and paa1.object_id = hou.organization_id and paa1.payroll_action_id = :pactid ) un, pay_assignment_actions paa where paa.payroll_action_id = :pactid and pa a.assignment_action_id = un.assignment_action_id order by un.name, un.last_name, un.first_name for update of paa.assignment_id
264qzrka8fsps SELECT NVL(SUM(prb.balance_value), 0) FROM /* US Assignment for Run based on run balances */ pay_run_balances prb, pay_defined_balances pdb where prb.assignment_action_id = :ASSIGNMENT_ACTION_ID and pdb.balance_type_id = :U1 and pdb.balance_dimension_id = :U2 and pdb.defined_balance_id = prb.defined_balance_id
2z261p0p82p32 BEGIN FFP51704_01010001.FORMULA( :N3, :I7, :N2, :T1, :I1, :T5, :I5, :T4, :I4, :N8, :I12, :T3, :I3, :T8, :I14, :N6, :I10, :T7, :I13, :N4, :I8, :D1, :N12, :I18, :N10, :I16, :N7, :I11, :N11, :I17, :T6, :I6, :N1, :T2, :I2, :N5, :I9, :N9, :I15, :FFERLN, :FFERCD, :FFERMT); END;
31a13pnjps7j3 SELECT source, (case when time_secs < 1 then 1 else time_secs end) as time_secs, operation FROM ( SELECT 1 as source, trunc((sysdate - cast(ll.log_date as date)) * 86400) as time_secs, decode(ll.operation, 'OPEN', 0 , 1 ) as operation, ll.log_id as log_id FROM DBA_SCHEDULER_WINDOW_LOG ll , ( SELECT max(l.log_id) as max_log_id FROM DBA_SCHEDULER_WINDOW_LOG l , DBA_SCHEDULER_WINGROUP_MEMBERS m WHERE l.window_name = m.window_name AND m.window_group_name = 'MAINTENANCE_WINDOW_GROUP' AND l.operation in ('OPEN', 'CLOSE') AND CAST(l.log_date AS DATE) < (SELECT cast(s1.end_interval_time as date) FROM WRM$_SNAPSHOT s1 WHERE s1.dbid = :dbid AND s1.instance_number = :inst_num AND s1.snap_id = :bid ) ) max_log WHERE ll.log_id = max_log.max_log_id UNION ALL SELECT 2 as source, trunc((sysdate - cast(l.log_date as date)) * 86400) as time_secs, decode(l.operation, 'OPEN', 0 , 1 ) as operation , l.log_id as log_id FROM DBA_SCHEDULER_WINDOW_LOG l , DBA_SCHEDULER_WINGROUP_MEMBERS m WHERE l.window_name = m.window_name AND m.window_group_name = 'MAINTENANCE_WINDOW_GROUP' AND l.operation in ('OPEN', 'CLOSE') AND CAST(l.log_date AS DATE) <= (SELECT cast(s2.end_interval_time as date) FROM WRM$_SNAPSHOT s2 WHERE s2.dbid = :dbid AND s2.instance_number = :inst_num AND s2.snap_id = :eid ) AND CAST(l.log_date AS DATE) >= (SELECT cast(s3.end_interval_time as date) FROM WRM$_SNAPSHOT s3 WHERE s3.dbid = :dbid AND s3.instance_number = :inst_num AND s3.snap_id = :bid ) UNION ALL SELECT 0 as source, trunc((sysdate - cast(s4.end_interval_time as date)) * 86400) as time_secs, decode(s4.snap_id, :bid, 2 , 3 ) as operation, 0 as log_id FROM WRM$_SNAPSHOT s4 WHERE s4.dbid = :dbid AND s4.instance_number = :inst_num AND s4.snap_id in (:bid, :eid) ) WHERE time_secs >= 0 ORDER BY source ASC, time_secs DESC, log_id ASC
36z3h9rgkd8aa begin hrassact.update_action_sequence (:aa_id, :itpflg); end;
3n5uwzsh3y8gr SELECT NVL(SUM(prb.balance_value), 0) FROM /* Assignment Payments on run balances */ pay_run_balances prb , pay_defined_balances pdb , pay_assignment_actions ASSACT , pay_assignment_actions BAL_ASSACT , pay_payroll_actions PACT , pay_payroll_actions BACT , pay_assignment_actions PPASSACT , pay_payroll_actions PPACT , pay_action_interlocks INTLCK2 , pay_run_types_f BAL_PRT , pay_run_types_f PRT where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and BACT.action_type in ('P', 'U', 'R', 'Q') and PPASSACT.assignment_action_id = pay_core_utils.get_pp_action_id(BACT.action_type, BAL_ASSACT.assignment_action_id) and PPASSACT.payroll_action_id = PPACT.payroll_action_id and PPACT.action_type in ('P', 'U') and PPASSACT.assignment_action_id = INTLCK2.locking_action_id and INTLCK2.locked_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action_id = PACT.payroll_action_id and nvl(BAL_ASSACT.run_type_id, -999) = BAL_PRT.run_type_id (+) and nvl(ASSACT.run_type_id, -999) = PRT.run_type_id (+) and ASSACT.assignment_action_id = decode(BACT.action_type, 'P', ASSACT.assignment_action_id, 'U', ASSACT.assignment_action_id, 'R', decode(nvl(BAL_PRT.run_method, 'N'), 'S ', BAL_ASSACT.ASSIGNMENT_ACTION_ID, decode (nvl(PRT.run_method, 'N'), 'S', -999, ASSACT.assignment_action_id)), 'Q', decode(nvl(BAL_PRT.run_method, 'N'), 'S', BAL_ASSACT.ASSIGNMENT_ACTION_ID, decode (nvl(PRT.run_method, 'N'), 'S', -999, ASSACT.assignment_action_id)) ) and ASSACT.tax_unit_id = decode(BACT.action_type, 'P', ASSACT.tax_unit_id, 'U', ASSACT.tax_unit_id, 'R', decode(BAL_ASSACT.SOURCE_ACTION_ID, NULL, ASSACT.TAX_UNIT_ID, BAL_ASSACT.tax_unit_id), 'Q', decode(BAL_ASSACT.SOURCE_ACTION_ID, NULL, ASSACT.TAX_UNIT_ID, BAL_ASSACT.tax_unit_id) ) and ASSACT.assignment_id = BAL_ASSACT.assignment_id and nvl(PACT.future_process_mode, 'Y') = 'Y' and PACT.action_type <> 'V' and prb.assignment_action_id = ASSACT.assignment_action_id and prb.defined_balance_id = pdb.defined_balance_id and pdb.balance_type_id = :U1 and pdb.balance_dimension_id = :U2
3rg5fyfgd5uwc begin pay_US_rules.get_time_def_for_entry( :element_entry_id, :assignment_id, :assignment_action_id, :business_group_id, :time_def_id); end;
4725065t8x3wk SELECT /*+ RULE*/ fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale), 0)) FROM /* Assignment within Government Reporting Entity */ pay_run_result_values TARGET , pay_balance_feeds_f FEED , pay_run_results RR , pay_assignment_actions ASSACT , pay_assignment_actions BAL_ASSACT , pay_payroll_actions PACT , pay_payroll_actions BACT where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and FEED.balance_type_id = :U1 + decode(TARGET.INPUT_VALUE_ID, null, 0, 0) and FEED.input_value_id = TARGET.input_value_id and nvl(TARGET.result_value, '0') != '0' and TARGET.run_result_id = RR.run_result_id and RR.assignment_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action_id = PACT.payroll_action_id and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date and RR.status in ('P', 'PA') and ASSACT.action_sequence <= BAL_ASSACT.action_sequence and ASSACT.assignment_id = BAL_ASSACT.assignment_id and ASSACT.tax_unit_id = :TAX_UNIT_ID /* Year To Date */ and PACT.effective_date >= trunc(BACT.effective_date, 'Y') /* Within Jurisdiction */ and :JURISDICTION_LEVEL != 0 and substr (RR.jurisdiction_code, 1, :JURISDICTION_LEVEL) = substr (:JURISDICTION_CODE, 1, :JURISDIC TION_LEVEL)
4ds53shhfcynn declare begin hr_pre_pay.do_prepayment(:p1, :p2, :p3, :p4, :5, :p6:p6ind, :p7); end;
4f5mjr4hnaz3u declare begin pay_archive.process_employee(:asg_action); end;
4gz4b2bh1fs14 SELECT CONTEXT_ID, SEQUENCE_NO FROM FF_ROUTE_CONTEXT_USAGES WHERE ROUTE_ID = :B1 ORDER BY SEQUENCE_NO
5sfm6rppc4y8x BEGIN pay_us_action_arch.action_archive_data(:aactid, :l_effective_date); END;
5w5up6vdp2qyp SELECT RP.SEQUENCE_NO, RPV.VALUE FROM FF_ROUTE_PARAMETER_VALUES RPV , FF_ROUTE_PARAMETERS RP WHERE RPV.USER_ENTITY_ID = :B2 AND RP.ROUTE_ID = :B1 AND RPV.ROUTE_PARAMETER_ID = RP.ROUTE_PARAMETER_ID ORDER BY RP.SEQUENCE_NO
6500rnmy4f88w SELECT PAP.ACCRUAL_PLAN_ID, PAP.ACCRUAL_PLAN_ELEMENT_TYPE_ID FROM PAY_ACCRUAL_PLANS PAP, PAY_ELEMENT_LINKS_F PEL, PAY_ELEMENT_ENTRIES_F PEE WHERE PAP.ACCRUAL_CATEGORY = :B3 AND PEL.ELEMENT_TYPE_ID= PAP.ACCRUAL_PLAN_ELEMENT_TYPE_ID AND :B1 BETWEEN PEL.EFFECTIVE_START_DATE AND PEL.EFFECTIVE_END_DATE AND PEE.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID AND PEE.ASSIGNMENT_ID = :B2 AND :B1 BETWEEN PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE
6v07302134wyf INSERT INTO PAY_ACTION_INFORMATION (ACTION_INFORMATION_ID , ACTION_CONTEXT_ID , ACTION_CONTEXT_TYPE , TAX_UNIT_ID , JURISDICTION_CODE , SOURCE_ID , SOURCE_TEXT , TAX_GROUP , OBJECT_VERSION_NUMBER , EFFECTIVE_DATE , ASSIGNMENT_ID , ACTION_INFORMATION_CATEGORY , ACTION_INFORMATION1 , ACTION_INFORMATION2 , ACTION_INFORMATION3 , ACTION_INFORMATION4 , ACTION_INFORMATION5 , ACTION_INFORMATION6 , ACTION_INFORMATION7 , ACTION_INFORMATION8 , ACTION_INFORMATION9 , ACTION_INFORMATION10 , ACTION_INFORMATION11 , ACTION_INFORMATION12 , ACTION_INFORMATION13 , ACTION_INFORMATION14 , ACTION_INFORMATION15 , ACTION_INFORMATION16 , ACTION_INFORMATION17 , ACTION_INFORMATION18 , ACTION_INFORMATION19 , ACTION_INFORMATION20 , ACTION_INFORMATION21 , ACTION_INFORMATION22 , ACTION_INFORMATION23 , ACTION_INFORMATION24 , ACTION_INFORMATION25 , ACTION_INFORMATION26 , ACTION_INFORMATION27 , ACTION_INFORMATION28 , ACTION_INFORMATION29 , ACTION_INFORMATION30 ) VALUES (:B42 , :B41 , :B40 , :B39 , :B38 , :B37 , :B36 , :B35 , :B34 , :B33 , :B32 , :B31 , :B30 , :B29 , :B28 , :B27 , :B26 , :B25 , :B24 , :B23 , :B22 , :B21 , :B20 , :B19 , :B18 , :B17 , :B16 , :B15 , :B14 , :B13 , :B12 , :B11 , :B10 , :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , :B1 )
6wpjhjnkaa3rj SELECT F.APPLICATION_ID, F.TABLE_APPLICATION_ID, T.TABLE_ID, F.APPLICATION_TABLE_NAME, NVL(:B3 , F.APPLICATION_TABLE_NAME), F.APPLICATION_TABLE_TYPE, F.UNIQUE_ID_COLUMN_NAME, F.SET_DEFINING_COLUMN_NAME, F.DYNAMIC_INSERTS_FEASIBLE_FLAG, F.MAXIMUM_CONCATENATION_LEN, F.CONCATENATION_LEN_WARNING FROM FND_ID_FLEXS F, FND_TABLES T, FND_APPLICATION A WHERE F.ID_FLEX_CODE = :B2 AND F.APPLICATION_ID = A.APPLICATION_ID AND A.APPLICATION_SHORT_NAME = :B1 AND T.APPLICATION_ID = F.TABLE_APPLICATION_ID AND T.TABLE_NAME = F.APPLICATION_TABLE_NAME
76duuz8ty1574 SELECT S.SEGMENT_NUM SEGNUM, S.DISPLAY_FLAG DISPLAYED, S.REQUIRED_FLAG REQUIRED, SAV.SEGMENT_ATTRIBUTE_TYPE FQNAME FROM FND_ID_FLEX_SEGMENTS S, FND_SEGMENT_ATTRIBUTE_VALUES SAV WHERE S.APPLICATION_ID = :B3 AND S.ID_FLEX_CODE = :B2 AND S.ID_FLEX_NUM = :B1 AND S.ENABLED_FLAG = 'Y' AND SAV.APPLICATION_COLUMN_NAME(+) = S.APPLICATION_COLUMN_NAME AND SAV.APPLICATION_ID(+) = :B3 AND SAV.ID_FLEX_CODE(+) = :B2 AND SAV.ID_FLEX_NUM(+) = :B1 AND SAV.ATTRIBUTE_VALUE(+) = 'Y' ORDER BY S.SEGMENT_NUM
77d28bpwm9svg SELECT PBV.RUN_BALANCE_STATUS, PBV.BALANCE_LOAD_DATE FROM PAY_BALANCE_VALIDATION PBV WHERE PBV.DEFINED_BALANCE_ID = :B2 AND PBV.BUSINESS_GROUP_ID = :B1
7d66dw0vz7yaa begin pay_cc_process_utils . reset_dates_for_run ( p_asg_id => :v_asg_id , p_sysdate => to_date ( :v_sysdate , :date_format ) , p_assact_id => :v_assact_id ) ; end ;
7mmdd7sypzu9x BEGIN FFP1893_01010001.FORMULA( :N1, :N2, :T1, :I1, :D1, :T2, :I2, :T7, :I7, :T8, :I8, :T5, :I5, :N5, :T4, :I4, :N4, :N3, :T3, :I3, :T6, :I6, :FFERLN, :FFERCD, :FFERMT); END;
7rnh3r6aupd00 SELECT /*+ RULE*/ fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale), 0)) FROM /* Assignment within Government Reporting Entity */ pay_run_result_values TARGET , pay_balance_feeds_f FEED , pay_run_results RR , pay_assignment_actions ASSACT , pay_assignment_actions BAL_ASSACT , pay_payroll_actions PACT , pay_payroll_actions BACT where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and FEED.balance_type_id = :U1 + decode(TARGET.INPUT_VALUE_ID, null, 0, 0) and FEED.input_value_id = TARGET.input_value_id and nvl(TARGET.result_value, '0') != '0' and TARGET.run_result_id = RR.run_result_id and RR.assignment_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action_id = PACT.payroll_action_id and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date and RR.status in ('P', 'PA') and ASSACT.action_sequence <= BAL_ASSACT.action_sequence and ASSACT.assignment_id = BAL_ASSACT.assignment_id and ASSACT.tax_unit_id = :TAX_UNIT_ID /* Year To Date */ and PACT.effective_date >= trunc(BACT.effective_date, 'Y')
80wvw6gatjvb2 SELECT PTP_TD.START_DATE FROM PER_TIME_PERIODS PTP_TD, PAY_ASSIGNMENT_ACTIONS PAA, PAY_PAYROLL_ACTIONS PPA, PER_TIME_PERIODS PTP_PPA WHERE PTP_TD.TIME_DEFINITION_ID = :B2 AND PAA.ASSIGNMENT_ACTION_ID = :B1 AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PPA.PAYROLL_ID = PTP_PPA.PAYROLL_ID AND PPA.DATE_EARNED BETWEEN PTP_PPA.START_DATE AND PTP_PPA.END_DATE AND PTP_TD.START_DATE > PTP_PPA.START_DATE AND PTP_TD.START_DATE <= PTP_PPA.END_DATE
83kbt99vg4svq begin hr_nonrun_asact.reinterlock (:assactid, :pactype); end;
9665ycstch5r4 select /*+ ORDERED USE_NL(AA PA RR RRV LIV IV PERA ET FC EC EL) INDEX(AA PAY_ASSIGNMENT_ACTIONS_PK, PA PAY_PAYROLL_ACTIONS_PK, RR PAY_RUN_RESULTS_N50, ET PAY_ELEMENT_TYPES_F_PK, EL PAY_ELEMENT_LINKS_F_PK, EE PAY_ELEMENT_ENTRIES_F_PK, IV PAY_INPUT_VALUES_F_N1, PERA +*/ EL2.element_link_id , EL2.costable_type , EL2.cost_allocation_keyflex_id , EL2.balancing_keyflex_id , EL2.element_set_id , decode(IV.uom, 'M', nvl(FC.precision, 3), 3) , nvl(EC.costing_debit_or_credit, 'D') , to_char(RR.run_result_id) , decode(:b0, 'A', decode(EE.entry_type, 'A', EE.target_entry_id, 'R', EE.target_entry_id, RR.source_id), RR.source_id) , decode(:b0, 'A', nvl(EE.entry_type, 'N'), 'N') , nvl(EE.creator_type, 'X') , EE.cost_allocation_keyflex_id , RR.element_type_id , RRV.input_value_id , RRV.result_value , IV.uom , decode(EL2.costable_type, 'D', decode(decode(RR.source_type, 'E', EE.creator_type, 'N'), 'RR', 2, 'EE', 2, 'NR', 2, 'PR', 2, 3), 1) , RR.jurisdiction_code , decode(IV.uom, 'M', decode(IV.name, 'Pay Value', 1, 0), 0) , to_char(decode(:b2, 0, PA.date_earned, decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned))), 'YYYY/MM/DD') , EL2.transfer_to_gl_flag , decode(:b3, null , null , hr_cost.get_context_value(:b3, RR.run_result_id, RR.element_type_id, PA.date_earned)) , decode(:b5, null , null , hr_cost.get_context_value(:b5, RR.run_result_id, RR.element_type_id, PA.date_earned)) , decode(:b7, null , null , hr_cost. get_context_value(:b7, RR.run_result_id, RR.element_type_id, PA.date_earned)) , decode(:b9, null , null , hr_cost.get_context_value(:b9, RR.run_result_id, RR.element_type_id, PA.date_earned)) from pay_assignment_actions AA , pay_payroll_actions PA , per_all_assignments_f PERA , pay_run_results RR , pay_element_entries_f EE , pay_element_types_f ET , pay_element_classifications EC , fnd_currencies FC , pay_input_values_f IV , pay_run_result_values RRV , pay_link_input_values_f LIV , pay_element_links_f EL , pay_element_links_f EL2 where ((((((((((((((((((((((((((((((AA.assignment_action_id=:b11 and AA.payroll_action_id=PA.payroll_action_id) and RR.assignment_action_id=:b11) and RR.status like 'P%') and RR.element_type_id=ET.element_type_id) and RRV.run_result_id=RR.run_result_id) and IV.input_value_id=RRV.input_value_id) and decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)) between IV.effective_start_date and IV.effective_end_date) and IV.element_type_id=ET.element_type_id) and RRV.result_value is not null ) and (decode(:b13, 'Y', 1, fnd_number.canonical_to_number(RRV.result_value))+decode(LIV.DEFAULT_VALUE, 'M', 0, 0))<>0) and RRV.input_value_id=LIV.input_value_id) and decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)) between LIV.effective_start_date and LIV.effective_end_date) and LIV.costed_flag='Y') and LIV.element_link_i d=EL.element_link_id) and decode(RR.entry_type, 'B', nvl(RR.element_entry_id, RR.source_id), RR.source_id)=EE.element_entry_id(+)) and decode(RR.end_date, null , to_date(:b14, 'YYYY/MM/DD'), hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, to_date(:b14, 'YYYY/MM/DD'))) between EE.effective_start_date(+) and EE.effective_end_date(+)) and RR.element_type_id=ET.element_type_id) and decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)) between ET.effective_start_date and ET.effective_end_date) and ET.classification_id=EC.classification_id) and ET.output_currency_code=FC.currency_code) and (ET.element_type_id+0)=(EL.element_type_id+0)) and decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)) between EL.effective_start_date and EL.effective_end_date) and EL.costable_type<>'N') and ((PA.action_type='B' and hr_cost.cost_bal_adj(nvl(RR.element_entry_id, RR.source_id), PA.date_earned)='Y') or PA.action_type<>'B')) and PERA.assignment_id=:b16) and (PERA.business_group_id+0)=(EL.business_group_id+0)) and decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)) between PERA.effective_start_date and PERA.effective_end_date) and (((EE.element_entry_id is not null and RR.source_type='E') and EL.element_link_id=EE.element_link_id) or ((((((((((EE.element_entry_id is null or (EE.element_entry_id is not null and RR.source_type<>'E')) and (((EL.payroll_id is not null and EL.payroll_id=PERA.payroll_id) or (EL.link_to_all_payrolls_flag='Y' and PERA.payroll_id is not null )) or EL.payroll_id is null )) and (EL.organization_id=PERA.organization_id or EL.organization_id is null )) and (EL.position_id=PERA.position_id or EL.position_id is null )) and (EL.job_id=PERA.job_id or EL.job_id is null )) and (EL.grade_id=PERA.grade_id or EL.grade_id is null )) and (EL.location_id=PERA.location_id or EL.location_id is null )) and (EL.pay_basis_id=PERA.pay_basis_id or EL.pay_basis_id is null )) and (EL.employment_category=PERA.employment_category or EL.employment_category is null )) and (EL.people_group_id is null or exists (select 1 from pay_assignment_link_usages_f PAL where ((PAL.assignment_id=:b16 and PAL.element_link_id=EL.element_link_id) and decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)) between PAL.effective_start_date and PAL.effective_end_date)))))) and EL2.element_link_id=EL.element_link_id) and decode(:b2, 0, PA.date_earned, decode(EL.costable_type, 'D', PA.date_earned, decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)))) between EL2.effective_start_date and EL2.effective_end_date) order by 17, 8, 14
97mt0g20bcfc3 begin pay_US_rules.get_default_jurisdiction (:asgact, :eeid, :defval:defvalind); end;
9jv68n0ctas75 begin pay_core_utils . get_prorated_dates ( :ee_id , :asg_act_id , :time_def_type , :time_def_id:time_def_ind , :pro_dates:prod_ind , :pro_types:prot_ind ) ; END ;
cf621qmts91wf SELECT /*+ ordered use_nl(stat text) */ text.sql_text, text.command_type, stat.* FROM (SELECT * FROM (SELECT s.sql_id, max(s.plan_hash_value) as planhash, sum(s.cpu_time_delta) as cpu_time, sum(s.elapsed_time_delta) as elapsed_time, sum(s.elapsed_time_delta - s.plsexec_time_delta - s.javexec_time_delta) as sqlx_time, sum(s.plsexec_time_delta) as plsqlx_time, sum(s.javexec_time_delta) as javax_time, sum(s.disk_reads_delta + s.direct_writes_delta) as io_blocks, sum(s.iowait_delta) as io_wait, sum(s.clwait_delta) as cl_wait, sum(s.rows_processed_delta) as rows_processed, sum(s.fetches_delta) as fetches, sum(s.end_of_fetch_count_delta) as eofs, sum(s.executions_delta) as execs, sum(s.px_servers_execs_delta) as px FROM (SELECT t1.* FROM WRH$_SQLSTAT t1, WRM$_SNAPSHOT s1 WHERE t1.dbid = s1.dbid AND t1.instance_number = s1.instance_number AND t1.snap_id = s1.snap_id AND s1.bl_moved = 0 UNION ALL SELECT t2.* FROM WRH$_SQLSTAT_BL t2, WRM$_SNAPSHOT s2 WHERE t2.dbid = s2.dbid AND t2.instance_number = s2.instance_number AND t2.snap_id = s2.snap_id AND s2.bl_moved <> 0) s WHERE s.snap_id > :startsn and s.snap_id <= :endsn and s.dbid = :dbid and s.instance_number = :inst_id and s.sql_id in ( SELECT ash.sql_id FROM ( SELECT a.sql_id as sql_id , coun t(*) as occur_count FROM (SELECT t1.* FROM WRH$_ACTIVE_SESSION_HISTORY t1, WRM$_SNAPSHOT s1 WHERE t1.dbid = s1.dbid AND t1.instance_number = s1.instance_number AND t1.snap_id = s1.snap_id AND s1.bl_moved = 0 UNION ALL SELECT t2.* FROM WRH$_ACTIVE_SESSION_HISTORY_BL t2, WRM$_SNAPSHOT s2 WHERE t2.dbid = s2.dbid AND t2.instance_number = s2.instance_number AND t2.snap_id = s2.snap_id AND s2.bl_moved <> 0) a WHERE a.snap_id > :startsn and a.snap_id <= :endsn and a.dbid = :db_id and a.instance_number = :instance_number and a.sql_id is not null and a.wait_time = 0 and a.session_type = 1 and a.event_id in ( select en.event_id from WRH$_EVENT_NAME en where a.dbid = en.dbid and en.wait_class = 'User I/O') GROUP BY a.sql_id HAVING count(*) >= :cutoff ORDER BY occur_count DESC ) ash WHERE rownum <= :topsql_from_ash ) GROUP BY s.sql_id HAVING sum(s.iowait_delta) >= :cutoff ORDER BY sum(s.iowait_delta) DESC ) WHERE ROWNUM <= :topsql) stat , WRH$_SQLTEXT text WHERE text.dbid = :db_id AND text.sql_id = stat.sql_id ORDER BY stat.io_wait DESC
cj64zg9gg7780 SELECT ACTION_INFORMATION_ID , ACTION_CONTEXT_ID , ACTION_CONTEXT_TYPE , TAX_UNIT_ID , JURISDICTION_CODE , SOURCE_ID , SOURCE_TEXT , TAX_GROUP , OBJECT_VERSION_NUMBER , EFFECTIVE_DATE , ASSIGNMENT_ID , ACTION_INFORMATION_CATEGORY , ACTION_INFORMATION1 , ACTION_INFORMATION2 , ACTION_INFORMATION3 , ACTION_INFORMATION4 , ACTION_INFORMATION5 , ACTION_INFORMATION6 , ACTION_INFORMATION7 , ACTION_INFORMATION8 , ACTION_INFORMATION9 , ACTION_INFORMATION10 , ACTION_INFORMATION11 , ACTION_INFORMATION12 , ACTION_INFORMATION13 , ACTION_INFORMATION14 , ACTION_INFORMATION15 , ACTION_INFORMATION16 , ACTION_INFORMATION17 , ACTION_INFORMATION18 , ACTION_INFORMATION19 , ACTION_INFORMATION20 , ACTION_INFORMATION21 , ACTION_INFORMATION22 , ACTION_INFORMATION23 , ACTION_INFORMATION24 , ACTION_INFORMATION25 , ACTION_INFORMATION26 , ACTION_INFORMATION27 , ACTION_INFORMATION28 , ACTION_INFORMATION29 , ACTION_INFORMATION30 FROM PAY_ACTION_INFORMATION WHERE ACTION_INFORMATION_ID = :B1 FOR UPDATE NOWAIT
cn5un9cxnah8w SELECT NVL(PBD.PERIOD_TYPE, 'NULL'), PBD.START_DATE_CODE FROM PAY_BALANCE_DIMENSIONS PBD, PAY_DEFINED_BALANCES PDB WHERE PDB.DEFINED_BALANCE_ID = :B1 AND PDB.BALANCE_DIMENSION_ID = PBD.BALANCE_DIMENSION_ID
cr74qtm56vcq6 BEGIN PAY_US_NACHA_TAPE.RUN_FORMULA; END;
d7shgtk3p7s4u SELECT /*+ ordered index(BAL_ASSACT PAY_ASSIGNMENT_ACTIONS_PK) index(BACT PAY_PAYROLL_ACTIONS_PK) index(ASSACT PAY_ASSIGNMENT_ACTIONS_PK) index(PACT PAY_PAYROLL_ACTIONS_PK) index(INTLCK2 PAY_ACTION_INTERLOCKS_FK1) index(PPASSACT PAY_ASSIGNMENT_ACTIONS_PK) index(PPACT PAY_PAYROLL_ACTIONS_PK) index(RR PAY_RUN_RESULTS_N50) index(TARGET PAY_RUN_RESULT_VALUES_N50) index(FEED PAY_BALANCE_FEEDS_F_N50) */ fnd_number.number_to_canonical(/*+ RULE*/ nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale), 0) ) FROM pay_assignment_actions BAL_ASSACT , pay_payroll_actions BACT , pay_assignment_actions PPASSACT , pay_payroll_actions PPACT , pay_action_interlocks INTLCK2 , pay_assignment_actions ASSACT , pay_payroll_actions PACT , pay_run_results RR , pay_run_result_values TARGET , pay_balance_feeds_f FEED where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and FEED.balance_type_id = :U1 + DECODE(TARGET.INPUT_VALUE_ID, NULL , 0, 0) and FEED.input_value_id = TARGET.input_value_id and nvl(TARGET.result_value, '0') != '0' and TARGET.run_result_id = RR.run_result_id and RR.assignment_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action _id = PACT.payroll_action_id and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date and RR.status in ('P', 'PA') and BACT.action_type in ('P', 'U', 'R', 'Q') and PPASSACT.assignment_action_id = pay_core_utils.get_pp_action_id(BACT.action_type, BAL_ASSACT.assignment_action_id) and PPASSACT.payroll_action_id = PPACT.payroll_action_id and PPACT.action_type in ('P', 'U') and nvl(PACT.future_process_mode, 'Y') = 'Y' and PPASSACT.assignment_action_id = INTLCK2.locking_action_id and INTLCK2.locked_action_id = ASSACT.assignment_action_id and pay_core_utils.include_action_in_payment(BACT.action_type, BAL_ASSACT.assignment_action_id, ASSACT.assignment_action_id) = 'Y' and NOT EXISTS (SELECT /*+ NO_PUSH_SUBQ */ NULL FROM pay_payroll_actions RPACT , pay_assignment_actions RASSACT , pay_action_interlocks RINTLK where ASSACT.assignment_action_id = RINTLK.locked_action_id and RINTLK.locking_action_id = RASSACT.assignment_action_id and RPACT.payroll_action_id = RASSACT.payroll_action_id and RPACT.action_type = 'V')
dab1prc9qyv3v begin hr_nonrun_asact.asact(:pactid, :atype, :itpflg, :ptype, :lub, :lul, :use_pop_person); end;
fdv98jawj03hg SELECT FUE.USER_ENTITY_NAME , NVL(FUE.BUSINESS_GROUP_ID, -1) , NVL(FUE.LEGISLATION_CODE, ' ') FROM FF_USER_ENTITIES FUE , FF_USER_ENTITIES FUE_B WHERE FUE.CREATOR_ID = :B2 AND FUE_B.CREATOR_TYPE = 'B' AND FUE.CREATOR_ID = FUE_B.CREATOR_ID AND FUE.CREATOR_TYPE = 'RB' AND FUE.USER_ENTITY_NAME = FUE_B.USER_ENTITY_NAME||'_'||TO_CHAR(:B1 )
g1u8jx4wbddx2 SELECT G.SEGMENT_NAME, G.APPLICATION_COLUMN_NAME, C.COLUMN_TYPE, C.WIDTH, G.REQUIRED_FLAG, G.SECURITY_ENABLED_FLAG, G.CONCATENATION_DESCRIPTION_LEN, G.DEFAULT_TYPE, G.DEFAULT_VALUE, G.FLEX_VALUE_SET_ID, G.RUNTIME_PROPERTY_FUNCTION, G.ADDITIONAL_WHERE_CLAUSE FROM FND_ID_FLEX_SEGMENTS G, FND_COLUMNS C WHERE G.APPLICATION_ID = :B5 AND G.ID_FLEX_CODE = :B4 AND G.ID_FLEX_NUM = :B3 AND G.ENABLED_FLAG = 'Y' AND C.APPLICATION_ID = :B2 AND C.TABLE_ID = :B1 AND C.COLUMN_NAME = G.APPLICATION_COLUMN_NAME ORDER BY G.SEGMENT_NUM
g3835fnqtvy06 SELECT NVL(SUM(prb.balance_value), 0) FROM /* Assignment Payments on run balances */ pay_balance_types pbt , pay_run_balances prb , pay_defined_balances pdb , pay_assignment_actions ASSACT , pay_assignment_actions BAL_ASSACT , pay_payroll_actions PACT , pay_payroll_actions BACT , pay_assignment_actions PPASSACT , pay_payroll_actions PPACT , pay_action_interlocks INTLCK2 , pay_run_types_f BAL_PRT , pay_run_types_f PRT where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and BACT.action_type in ('P', 'U', 'R', 'Q') and PPASSACT.assignment_action_id = pay_core_utils.get_pp_action_id(BACT.action_type, BAL_ASSACT.assignment_action_id) and PPASSACT.payroll_action_id = PPACT.payroll_action_id and PPACT.action_type in ('P', 'U') and PPASSACT.assignment_action_id = INTLCK2.locking_action_id and INTLCK2.locked_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action_id = PACT.payroll_action_id and nvl(BAL_ASSACT.run_type_id, -999) = BAL_PRT.run_type_id (+) and nvl(ASSACT.run_type_id, -999) = PRT.run_type_id (+) and ASSACT.assignment_action_id = decode(BACT.action_type, 'P', ASSACT.assignment_action_id, 'U', ASSACT.assignment_action_id, 'R', decode(nvl(BAL_PRT.run_method, 'N' ), 'S', BAL_ASSACT.ASSIGNMENT_ACTION_ID, decode (nvl(PRT.run_method, 'N'), 'S', -999, ASSACT.assignment_action_id)), 'Q', decode(nvl(BAL_PRT.run_method, 'N'), 'S', BAL_ASSACT.ASSIGNMENT_ACTION_ID, decode (nvl(PRT.run_method, 'N'), 'S', -999, ASSACT.assignment_action_id)) ) and ASSACT.tax_unit_id = decode(BACT.action_type, 'P', ASSACT.tax_unit_id, 'U', ASSACT.tax_unit_id, 'R', decode(BAL_ASSACT.SOURCE_ACTION_ID, NULL, ASSACT.TAX_UNIT_ID, BAL_ASSACT.tax_unit_id), 'Q', decode(BAL_ASSACT.SOURCE_ACTION_ID, NULL, ASSACT.TAX_UNIT_ID, BAL_ASSACT.tax_unit_id) ) and ASSACT.assignment_id = BAL_ASSACT.assignment_id and nvl(PACT.future_process_mode, 'Y') = 'Y' and PACT.action_type <> 'V' and prb.assignment_action_id = ASSACT.assignment_action_id and prb.defined_balance_id = pdb.defined_balance_id and pdb.balance_dimension_id = :U2 and pbt.balance_type_id = :U1 and pdb.balance_type_id = pbt.balance_type_id and substr(prb.jurisdiction_code, 1, pbt.jurisdiction_level) = substr(:JURISDICTION_CODE, 1, pbt.jurisdiction_level)
g6tx1p7d69dxg SELECT ENT.USER_ENTITY_ID, ENT.CREATOR_TYPE, ENT.NOTFOUND_ALLOWED_FLAG, RO.ROUTE_ID, RO.TEXT, RO.OPTIMIZER_HINT FROM FF_USER_ENTITIES ENT , FF_ROUTES RO WHERE ENT.USER_ENTITY_NAME = :B3 AND ( (ENT.LEGISLATION_CODE IS NULL AND ENT.BUSINESS_GROUP_ID IS NULL) OR (ENT.BUSINESS_GROUP_ID IS NULL AND :B2 = ENT.LEGISLATION_CODE ) OR ENT.BUSINESS_GROUP_ID + 0 = :B1 ) AND ENT.ROUTE_ID = RO.ROUTE_ID
gq04p7vgjcmkg SELECT PPT.CATEGORY CATEGORY, PPM.PERSONAL_PAYMENT_METHOD_ID PERSONAL_METHOD, PEA.PRENOTE_DATE PRENOTE_DATE, PPT.VALIDATION_DAYS VALID_DAYS, PPM.PERCENTAGE PERCENTAGE, PPM.AMOUNT AMOUNT, OPM.ORG_PAYMENT_METHOD_ID ORG_METHOD, HR_PRE_PAY.SET_CASH_RULE(PPT.CATEGORY, OPM.PMETH_INFORMATION1) CASH_RULE, OPM.CURRENCY_CODE PAYMENT_CURRENCY, PPT.PRE_VALIDATION_REQUIRED VALIDATION_REQUIRED, PPT.VALIDATION_VALUE VALIDATION_VALUE, OPM.EXTERNAL_ACCOUNT_ID EXTERNAL_ACCOUNT_ID FROM PAY_EXTERNAL_ACCOUNTS PEA, PAY_PAYMENT_TYPES PPT, PAY_ORG_PAYMENT_METHODS_F OPM, PAY_PERSONAL_PAYMENT_METHODS_F PPM, PAY_ASSIGNMENT_ACTIONS ACT WHERE ACT.ASSIGNMENT_ACTION_ID=:B4 AND PPM.ASSIGNMENT_ID = ACT.ASSIGNMENT_ID AND PPM.RUN_TYPE_ID = :B3 AND PPM.ORG_PAYMENT_METHOD_ID = OPM.ORG_PAYMENT_METHOD_ID AND OPM.PAYMENT_TYPE_ID = PPT.PAYMENT_TYPE_ID AND OPM.DEFINED_BALANCE_ID = :B2 AND PPM.EXTERNAL_ACCOUNT_ID = PEA.EXTERNAL_ACCOUNT_ID (+) AND FND_DATE.CANONICAL_TO_DATE(:B1 ) BETWEEN PPM.EFFECTIVE_START_DATE AND PPM.EFFECTIVE_END_DATE AND FND_DATE.CANONICAL_TO_DATE(:B1 ) BETWEEN OPM.EFFECTIVE_START_DATE AND OPM.EFFECTIVE_END_DATE ORDER BY PPM.PERSON_ID, PPM.PRIORITY

Back to SQL Statistics
Back to Top

 

Instance Activity Statistics

Back to Top

Instance Activity Stats

Statistic Total per Second per Trans
CPU used by this session 119,294 358.85 8.10
CPU used when call started 119,301 358.87 8.10
CR blocks created 651,557 1,959.96 44.22
Cached Commit SCN referenced 306,926 923.27 20.83
Commit SCN cached 131 0.39 0.01
DB time 153,806 462.67 10.44
IMU CR rollbacks 20,985,856 63,128.08 1,424.31
IMU Flushes 6,914 20.80 0.47
IMU Redo allocation size 90,463,920 272,126.77 6,139.81
IMU commits 7,833 23.56 0.53
IMU contention 3,835 11.54 0.26
IMU ktichg flush 70 0.21 0.00
IMU recursive-transaction flush 200 0.60 0.01
IMU undo allocation size 141,103,280 424,456.30 9,576.71
SQL*Net roundtrips to/from client 888,970 2,674.13 60.33
active txn count during cleanout 2,395,199 7,205.06 162.56
application wait time 446 1.34 0.03
background timeouts 1,172 3.53 0.08
branch node splits 9 0.03 0.00
buffer is not pinned count 80,361,677 241,737.97 5,454.17
buffer is pinned count 73,912,601 222,338.34 5,016.47
bytes received via SQL*Net from client 261,795,943 787,514.91 17,768.15
bytes sent via SQL*Net to client 164,016,194 493,381.20 11,131.82
calls to get snapshot scn: kcmgss 9,872,317 29,697.16 670.04
calls to kcmgas 709,212 2,133.40 48.13
calls to kcmgcs 19,434 58.46 1.32
change write time 1,249 3.76 0.08
cleanout - number of ktugct calls 620,817 1,867.50 42.13
cleanouts and rollbacks - consistent read gets 593,496 1,785.31 40.28
cleanouts only - consistent read gets 3,050 9.17 0.21
cluster key scan block gets 2,425 7.29 0.16
cluster key scans 998 3.00 0.07
commit batch performed 0 0.00 0.00
commit batch requested 0 0.00 0.00
commit batch/immediate performed 424 1.28 0.03
commit batch/immediate requested 424 1.28 0.03
commit cleanout failures: block lost 140 0.42 0.01
commit cleanout failures: callback failure 5,679 17.08 0.39
commit cleanout failures: cannot pin 2,498 7.51 0.17
commit cleanouts 205,376 617.80 13.94
commit cleanouts successfully completed 197,059 592.78 13.37
commit immediate performed 424 1.28 0.03
commit immediate requested 424 1.28 0.03
commit txn count during cleanout 25,320 76.17 1.72
concurrency wait time 481 1.45 0.03
consistent changes 44,450,109 133,711.48 3,016.84
consistent gets 151,113,862 454,569.38 10,256.13
consistent gets - examination 81,454,802 245,026.22 5,528.36
consistent gets direct 32 0.10 0.00
consistent gets from cache 151,113,830 454,569.28 10,256.13
cursor authentications 161 0.48 0.01
data blocks consistent reads - undo records applied 44,420,354 133,621.97 3,014.82
db block changes 5,027,251 15,122.60 341.20
db block gets 5,794,281 17,429.92 393.26
db block gets direct 33 0.10 0.00
db block gets from cache 5,794,248 17,429.82 393.26
deferred (CURRENT) block cleanout applications 29,500 88.74 2.00
doubling up with imu segment 0 0.00 0.00
enqueue conversions 3,105 9.34 0.21
enqueue releases 91,945 276.58 6.24
enqueue requests 92,063 276.94 6.25
enqueue timeouts 119 0.36 0.01
enqueue waits 3,185 9.58 0.22
exchange deadlocks 4,543 13.67 0.31
execute count 9,074,878 27,298.37 615.91
failed probes on index block reclamation 29 0.09 0.00
free buffer inspected 68,402 205.76 4.64
free buffer requested 700,346 2,106.73 47.53
heap block compress 11,152 33.55 0.76
hot buffers moved to head of LRU 947 2.85 0.06
immediate (CR) block cleanout applications 596,546 1,794.48 40.49
immediate (CURRENT) block cleanout applications 37,550 112.96 2.55
index crx upgrade (found) 7,473 22.48 0.51
index crx upgrade (positioned) 510,600 1,535.95 34.65
index fetch by key 16,324,242 49,105.36 1,107.93
index scans kdiixs1 13,474,099 40,531.77 914.49
leaf node 90-10 splits 1,382 4.16 0.09
leaf node splits 4,125 12.41 0.28
lob reads 0 0.00 0.00
lob writes 182 0.55 0.01
lob writes unaligned 182 0.55 0.01
logons cumulative 74 0.22 0.01
messages received 11,046 33.23 0.75
messages sent 11,046 33.23 0.75
no buffer to keep pinned count 0 0.00 0.00
no work - consistent read gets 67,242,124 202,272.71 4,563.74
opened cursors cumulative 691,435 2,079.92 46.93
parse count (failures) 0 0.00 0.00
parse count (hard) 172 0.52 0.01
parse count (total) 750,351 2,257.15 50.93
parse time cpu 827 2.49 0.06
parse time elapsed 402 1.21 0.03
physical read IO requests 476 1.43 0.03
physical read bytes 7,323,648 22,030.45 497.06
physical read total IO requests 2,427 7.30 0.16
physical read total bytes 37,175,296 111,827.94 2,523.10
physical read total multi block requests 61 0.18 0.00
physical reads 894 2.69 0.06
physical reads cache 862 2.59 0.06
physical reads cache prefetch 418 1.26 0.03
physical reads direct 32 0.10 0.00
physical reads direct temporary tablespace 0 0.00 0.00
physical reads prefetch warmup 349 1.05 0.02
physical write IO requests 33 0.10 0.00
physical write bytes 270,336 813.20 18.35
physical write total IO requests 16,770 50.45 1.14
physical write total bytes 747,253,760 2,247,832.68 50,716.29
physical write total multi block requests 15,718 47.28 1.07
physical writes 33 0.10 0.00
physical writes direct 33 0.10 0.00
physical writes direct (lob) 33 0.10 0.00
physical writes non checkpoint 33 0.10 0.00
pinned buffers inspected 12 0.04 0.00
process last non-idle time 331 1.00 0.02
recursive aborts on index block reclamation 2 0.01 0.00
recursive calls 18,815,237 56,598.58 1,276.99
recursive cpu usage 106,275 319.69 7.21
redo blocks written 1,444,168 4,344.24 98.02
redo entries 2,592,169 7,797.57 175.93
redo ordering marks 28,325 85.21 1.92
redo size 711,214,376 2,139,421.71 48,270.28
redo subscn max counts 34,580 104.02 2.35
redo synch time 655 1.97 0.04
redo synch writes 5,557 16.72 0.38
redo wastage 3,884,536 11,685.17 263.64
redo write time 1,013 3.05 0.07
redo writer latching time 0 0.00 0.00
redo writes 10,976 33.02 0.74
rollback changes - undo records applied 1,762 5.30 0.12
rollbacks only - consistent read gets 60,460 181.87 4.10
rows fetched via callback 10,981,162 33,032.71 745.29
session connect time 0 0.00 0.00
session cursor cache hits 738,997 2,223.00 50.16
session logical reads 156,908,146 471,999.31 10,649.39
session uga memory 1,095,528 3,295.49 74.35
session uga memory max 209,997,848 631,699.77 14,252.60
shared hash latch upgrades - no wait 628,651 1,891.06 42.67
shared hash latch upgrades - wait 9,843 29.61 0.67
sorts (memory) 707,513 2,128.29 48.02
sorts (rows) 1,370,463 4,122.52 93.01
sql area evicted 0 0.00 0.00
sql area purged 0 0.00 0.00
switch current to new buffer 11,996 36.09 0.81
table fetch by rowid 71,232,566 214,276.46 4,834.57
table fetch continued row 32 0.10 0.00
table scan blocks gotten 9,387,906 28,239.99 637.16
table scan rows gotten 409,638,344 1,232,243.32 27,802.25
table scans (long tables) 0 0.00 0.00
table scans (short tables) 551,999 1,660.48 37.46
total number of times SMON posted 142 0.43 0.01
transaction rollbacks 424 1.28 0.03
undo change vector size 244,463,032 735,375.34 16,591.76
user I/O wait time 340 1.02 0.02
user calls 948,422 2,852.97 64.37
user commits 14,731 44.31 1.00
user rollbacks 3 0.01 0.00
workarea executions - optimal 470,990 1,416.80 31.97

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

Statistic Begin Value End Value
session cursor cache count 11,927 14,945
opened cursors current 2,512 2,464
logons current 78 77

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

Statistic Total per Hour
log switches (derived) 0 0.00

Back to Instance Activity Statistics
Back to Top

 

IO Stats

Back to Top

Tablespace IO Stats

Tablespace Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)
SYSTEM 227 1 3.66 1.11 0 0 36 0.56
SYSAUX 146 0 2.26 1.66 33 0 0 0.00
APPS_TS_TX_IDX 56 0 4.29 4.46 0 0 7,180 0.07
APPS_TS_SEED 31 0 4.84 2.35 0 0 113 3.72
APPS_TS_TX_DATA 17 0 4.12 4.47 0 0 6,192 0.07

Back to IO Stats
Back to Top

File IO Stats

Tablespace Filename Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)
APPS_TS_SEED /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/reference1.dbf 9 0 6.67 3.78 0 0 34 4.71
APPS_TS_SEED /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/reference3.dbf 3 0 3.33 1.00 0 0 16 0.00
APPS_TS_SEED /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/reference4.dbf 4 0 5.00 2.50 0 0 18 3.89
APPS_TS_SEED /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/reference5.dbf 15 0 4.00 1.73 0 0 45 4.22
APPS_TS_TX_DATA /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_data21.dbf 3 0 3.33 2.00 0 0 11 2.73
APPS_TS_TX_DATA /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_data27.dbf 2 0 0.00 1.00 0 0 31 0.00
APPS_TS_TX_DATA /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_data28.dbf 3 0 6.67 5.00 0 0 1,697 0.05
APPS_TS_TX_DATA /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_data32.dbf 3 0 0.00 7.33 0 0 2,352 0.06
APPS_TS_TX_DATA /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_data39.dbf 2 0 10.00 8.00 0 0 1,454 0.07
APPS_TS_TX_DATA /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_data43.dbf 2 0 5.00 1.00 0 0 570 0.12
APPS_TS_TX_DATA /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_data47.dbf 2 0 5.00 6.50 0 0 77 0.52
APPS_TS_TX_IDX /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_idx16.dbf 3 0 0.00 3.67 0 0 461 0.22
APPS_TS_TX_IDX /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_idx18.dbf 1 0 30.00 4.00 0 0 779 0.00
APPS_TS_TX_IDX /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_idx2.dbf 2 0 5.00 8.00 0 0 1,426 0.04
APPS_TS_TX_IDX /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_idx24.dbf 1 0 0.00 9.00 0 0 1,424 0.08
APPS_TS_TX_IDX /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_idx27.dbf 26 0 1.54 2.81 0 0 142 0.49
APPS_TS_TX_IDX /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_idx28.dbf 2 0 5.00 7.50 0 0 57 0.53
APPS_TS_TX_IDX /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_idx33.dbf 4 0 7.50 4.75 0 0 837 0.02
APPS_TS_TX_IDX /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_idx34.dbf 2 0 5.00 8.00 0 0 417 0.02
APPS_TS_TX_IDX /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_idx35.dbf 2 0 5.00 8.00 0 0 419 0.02
APPS_TS_TX_IDX /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_idx36.dbf 2 0 10.00 7.50 0 0 129 0.16
APPS_TS_TX_IDX /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_idx37.dbf 2 0 5.00 8.00 0 0 179 0.00
APPS_TS_TX_IDX /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_idx38.dbf 2 0 5.00 6.00 0 0 189 0.00
APPS_TS_TX_IDX /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_idx40.dbf 2 0 10.00 1.00 0 0 252 0.04
APPS_TS_TX_IDX /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_idx41.dbf 1 0 10.00 1.00 0 0 261 0.04
APPS_TS_TX_IDX /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_idx5.dbf 3 0 6.67 5.33 0 0 1 0.00
APPS_TS_TX_IDX /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/tx_idx6.dbf 1 0 10.00 9.00 0 0 207 0.05
SYSAUX /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/sysaux01.dbf 146 0 2.26 1.66 33 0 0 0.00
SYSTEM /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/sys1.dbf 11 0 4.55 1.00 0 0 1 0.00
SYSTEM /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/sys10.dbf 27 0 3.70 1.00 0 0 0 0.00
SYSTEM /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/sys2.dbf 25 0 4.00 1.00 0 0 0 0.00
SYSTEM /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/sys3.dbf 32 0 4.06 1.34 0 0 1 0.00
SYSTEM /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/sys4.dbf 15 0 2.67 1.93 0 0 33 0.61
SYSTEM /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/sys5.dbf 9 0 5.56 1.00 0 0 0 0.00
SYSTEM /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/sys6.dbf 9 0 4.44 1.00 0 0 0 0.00
SYSTEM /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/sys7.dbf 39 0 2.82 1.00 0 0 1 0.00
SYSTEM /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/sys8.dbf 22 0 5.00 1.00 0 0 0 0.00
SYSTEM /ds1/oracle/VIS/db/tech_st/10.2.0/dbf/sys9.dbf 38 0 2.63 1.00 0 0 0 0.00

Back to IO Stats
Back to Top

 

Buffer Pool Statistics

P Number of Buffers Pool Hit% Buffer Gets Physical Reads Physical Writes Free Buff Wait Writ Comp Wait Buffer Busy Waits
D 329,835 100 156,162,988 865 0 0 0 60,550


Back to Top

 

Advisory Statistics

Back to Top

Instance Recovery Stats

  Targt MTTR (s) Estd MTTR (s) Recovery Estd IOs Actual Redo Blks Target Redo Blks Log File Size Redo Blks Log Ckpt Timeout Redo Blks Log Ckpt Interval Redo Blks
B 0 178 95627 2580418 37748700 37748700   99999999
E 0 255 131573 4024586 37748700 37748700   99999999

Back to Advisory Statistics
Back to Top

Buffer Pool Advisory

P Size for Est (M) Size Factor Buffers for Estimate Est Phys Read Factor Estimated Physical Reads
D 256 0.10 31,984 1.43 147,356
D 512 0.19 63,968 1.21 124,322
D 768 0.29 95,952 1.12 115,147
D 1,024 0.39 127,936 1.00 103,037
D 1,280 0.48 159,920 1.00 102,836
D 1,536 0.58 191,904 1.00 102,755
D 1,792 0.68 223,888 1.00 102,755
D 2,048 0.78 255,872 1.00 102,755
D 2,304 0.87 287,856 1.00 102,755
D 2,560 0.97 319,840 1.00 102,755
D 2,640 1.00 329,835 1.00 102,755
D 2,816 1.07 351,824 1.00 102,755
D 3,072 1.16 383,808 1.00 102,755
D 3,328 1.26 415,792 1.00 102,755
D 3,584 1.36 447,776 1.00 102,755
D 3,840 1.45 479,760 1.00 102,755
D 4,096 1.55 511,744 1.00 102,755
D 4,352 1.65 543,728 1.00 102,755
D 4,608 1.75 575,712 1.00 102,755
D 4,864 1.84 607,696 1.00 102,755
D 5,120 1.94 639,680 1.00 102,755

Back to Advisory Statistics
Back to Top

PGA Aggr Summary

PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
100.00 9,683 0

Back to Advisory Statistics
Back to Top

PGA Aggr Target Stats

  PGA Aggr Target(M) Auto PGA Target(M) PGA Mem Alloc(M) W/A PGA Used(M) %PGA W/A Mem %Auto W/A Mem %Man W/A Mem Global Mem Bound(K)
B 2,048 1,755 213.58 0.00 0.00 0.00 0.00 102,400
E 2,048 1,754 205.68 0.00 0.00 0.00 0.00 102,400

Back to Advisory Statistics
Back to Top

PGA Aggr Target Histogram

Low Optimal High Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
2K 4K 459,148 459,148 0 0
64K 128K 5 5 0 0
128K 256K 16 16 0 0
256K 512K 6 6 0 0
512K 1024K 10,242 10,242 0 0

Back to Advisory Statistics
Back to Top

PGA Memory Advisory

PGA Target Est (MB) Size Factr W/A MB Processed Estd Extra W/A MB Read/ Written to Disk Estd PGA Cache Hit % Estd PGA Overalloc Count
256 0.13 22,928.75 0.00 100.00 0
512 0.25 22,928.75 0.00 100.00 0
1,024 0.50 22,928.75 0.00 100.00 0
1,536 0.75 22,928.75 0.00 100.00 0
2,048 1.00 22,928.75 0.00 100.00 0
2,458 1.20 22,928.75 0.00 100.00 0
2,867 1.40 22,928.75 0.00 100.00 0
3,277 1.60 22,928.75 0.00 100.00 0
3,686 1.80 22,928.75 0.00 100.00 0
4,096 2.00 22,928.75 0.00 100.00 0
6,144 3.00 22,928.75 0.00 100.00 0
8,192 4.00 22,928.75 0.00 100.00 0
12,288 6.00 22,928.75 0.00 100.00 0
16,384 8.00 22,928.75 0.00 100.00 0

Back to Advisory Statistics
Back to Top

Shared Pool Advisory

Shared Pool Size(M) SP Size Factr Est LC Size (M) Est LC Mem Obj Est LC Time Saved (s) Est LC Time Saved Factr Est LC Load Time (s) Est LC Load Time Factr Est LC Mem Obj Hits
112 0.18 67 6,264 237,107 1.00 52 1.00 14,558,609
176 0.28 77 7,426 237,107 1.00 52 1.00 14,558,644
240 0.38 77 7,426 237,107 1.00 52 1.00 14,558,644
304 0.49 77 7,426 237,107 1.00 52 1.00 14,558,644
368 0.59 77 7,426 237,107 1.00 52 1.00 14,558,644
432 0.69 77 7,426 237,107 1.00 52 1.00 14,558,644
496 0.79 77 7,426 237,107 1.00 52 1.00 14,558,644
560 0.90 77 7,426 237,107 1.00 52 1.00 14,558,644
624 1.00 77 7,426 237,107 1.00 52 1.00 14,558,644
688 1.10 77 7,426 237,107 1.00 52 1.00 14,558,644
752 1.21 77 7,426 237,107 1.00 52 1.00 14,558,644
816 1.31 77 7,426 237,107 1.00 52 1.00 14,558,644
880 1.41 77 7,426 237,107 1.00 52 1.00 14,558,644
944 1.51 77 7,426 237,107 1.00 52 1.00 14,558,644
1,008 1.62 77 7,426 237,107 1.00 52 1.00 14,558,644
1,072 1.72 77 7,426 237,107 1.00 52 1.00 14,558,644
1,136 1.82 77 7,426 237,107 1.00 52 1.00 14,558,644
1,200 1.92 77 7,426 237,107 1.00 52 1.00 14,558,644
1,264 2.03 77 7,426 237,107 1.00 52 1.00 14,558,644

Back to Advisory Statistics
Back to Top

SGA Target Advisory

SGA Target Size (M) SGA Size Factor Est DB Time (s) Est Physical Reads
876 0.25 2,775 102,731
1,752 0.50 2,775 102,731
2,628 0.75 2,775 102,731
3,504 1.00 2,775 102,731
4,380 1.25 2,775 102,731
5,256 1.50 2,775 102,731
6,132 1.75 2,775 102,731
7,008 2.00 2,775 102,731

Back to Advisory Statistics
Back to Top

Streams Pool Advisory

Size for Est (MB) Size Factor Est Spill Count Est Spill Time (s) Est Unspill Count Est Unspill Time (s)
16 0.33 0 0 0 0
32 0.67 0 0 0 0
48 1.00 0 0 0 0
64 1.33 0 0 0 0
80 1.67 0 0 0 0
96 2.00 0 0 0 0
112 2.33 0 0 0 0
128 2.67 0 0 0 0
144 3.00 0 0 0 0
160 3.33 0 0 0 0
176 3.67 0 0 0 0
192 4.00 0 0 0 0
208 4.33 0 0 0 0
224 4.67 0 0 0 0
240 5.00 0 0 0 0
256 5.33 0 0 0 0
272 5.67 0 0 0 0
288 6.00 0 0 0 0
304 6.33 0 0 0 0
320 6.67 0 0 0 0

Back to Advisory Statistics
Back to Top

 

Java Pool Advisory

No data exists for this section of the report.

Back to Advisory Statistics
Back to Top

 

Wait Statistics

Back to Top

Buffer Wait Statistics

Class Waits Total Wait Time (s) Avg Time (ms)
data block 40,949 2 0
undo block 17,830 0 0
undo header 1,718 0 0
1st level bmb 14 0 2
segment header 29 0 0
2nd level bmb 5 0 0

Back to Wait Statistics
Back to Top

Enqueue Activity

Enqueue Type (Request Reason) Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms)
UL-User-defined 3,344 3,236 108 507 4 8.52
TX-Transaction (index contention) 2,283 2,283 0 2,283 1 0.25
TX-Transaction (row lock contention) 136 125 11 125 0 1.52
SQ-Sequence Cache 590 590 0 193 0 0.36
TX-Transaction 23,230 23,229 0 20 0 1.00
HW-Segment High Water Mark 665 665 0 52 0 0.19
FB-Format Block 77 77 0 3 0 0.00
TX-Transaction (allocate ITL entry) 2 2 0 2 0 0.00

Back to Wait Statistics
Back to Top

 

Undo Statistics

Back to Top

Undo Segment Summary

Undo TS# Num Undo Blocks (K) Number of Transactions Max Qry Len (s) Max Tx Concurcy Min/Max TR (mins) STO/ OOS uS/uR/uU/ eS/eR/eU
1 34.62 23,986 1,059 12   0/0 0/0/0/4/8896/0

Back to Undo Statistics
Back to Top

Undo Segment Stats

End Time Num Undo Blocks Number of Transactions Max Qry Len (s) Max Tx Concy Tun Ret (mins) STO/ OOS uS/uR/uU/ eS/eR/eU
12-Sep 12:19 94 362 687 3 267 0/0 0/0/0/0/0/0
12-Sep 12:09 34,527 23,624 1,059 12 214 0/0 0/0/0/4/8896/0

Back to Undo Statistics
Back to Top

 

Latch Statistics

Back to Top

Latch Activity

Latch Name Get Requests Pct Get Miss Avg Slps /Miss Wait Time (s) NoWait Requests Pct NoWait Miss
ASM db client latch 260 0.00   0 0  
AWR Alerted Metric Element list 3,157 0.00   0 0  
Consistent RBA 10,988 0.00   0 0  
FOB s.o list latch 1,090 0.00   0 0  
In memory undo latch 44,374,609 0.79 0.00 0 15,179 0.00
JS queue state obj latch 1,980 0.00   0 0  
KMG MMAN ready and startup request latch 111 0.00   0 0  
KTF sga latch 2 0.00   0 101 0.00
KWQMN job cache list latch 6 0.00   0 0  
KWQP Prop Status 42 0.00   0 0  
MQL Tracking Latch 0     0 7 0.00
Memory Management Latch 0     0 111 0.00
OS process 225 0.00   0 0  
OS process allocation 261 0.00   0 0  
OS process: request allocation 150 0.00   0 0  
PL/SQL warning settings 256,091 0.01 0.00 0 0  
SQL memory manager latch 1 0.00   0 110 0.00
SQL memory manager workarea list latch 668,164 0.02 0.00 0 0  
Shared B-Tree 218 0.00   0 0  
active checkpoint queue latch 222 0.00   0 0  
active service list 899 0.00   0 113 0.00
begin backup scn array 33 0.00   0 0  
bq:time manger info latch 12 0.00   0 0  
bufq statistics 3 0.00   0 0  
cache buffer handles 6,259,092 2.42 0.00 0 0  
cache buffers chains 333,923,865 3.31 0.00 0 465,976 12.99
cache buffers lru chain 25,335 0.42 0.00 0 1,351,280 0.53
cache table scan latch 8 0.00   0 8 0.00
channel handle pool latch 292 0.00   0 0  
channel operations parent latch 3,209 0.16 0.00 0 0  
checkpoint queue latch 22,557 0.00   0 35,947 0.00
client/application info 1,064 0.00   0 0  
commit callback allocation 12 0.00   0 0  
compile environment latch 16,620 0.00   0 0  
dml lock allocation 115,309 0.41 0.00 0 0  
dummy allocation 149 0.00   0 0  
enqueue hash chains 190,941 0.24 0.00 0 6 16.67
enqueues 37,625 0.22 0.00 0 0  
event group latch 75 0.00   0 0  
file cache latch 620 0.00   0 0  
global KZLD latch for mem in SGA 73 0.00   0 0  
global ctx hash table latch 141 0.00   0 0  
hash table column usage latch 0     0 8,178 0.02
hash table modification latch 48 0.00   0 0  
job_queue_processes parameter latch 6 0.00   0 0  
kks stats 226 0.00   0 0  
ksuosstats global area 24 0.00   0 0  
ktm global data 142 0.00   0 0  
kwqbsn:qsga 202 0.00   0 0  
lgwr LWN SCN 11,096 0.41 0.00 0 0  
library cache 18,312,942 0.57 0.00 0 0  
library cache load lock 500 1.00 0.00 0 4 0.00
library cache lock 9,742,736 1.54 0.00 0 0  
library cache lock allocation 6,954 0.00   0 0  
library cache pin 7,635,163 0.55 0.00 0 0  
library cache pin allocation 1,935 0.00   0 0  
list of block allocation 11,194 0.01 0.00 0 0  
loader state object freelist 310 0.00   0 0  
logminer context allocation 1 0.00   0 0  
message pool operations parent latch 284 0.35 0.00 0 0  
messages 34,992 0.02 0.00 0 0  
mostly latch-free SCN 11,480 3.70 0.00 0 0  
multiblock read objects 132 0.00   0 0  
ncodef allocation latch 5 0.00   0 0  
object queue header heap 1,145 0.00   0 282 0.00
object queue header operation 116,546 0.01 0.00 0 0  
object stats modification 295 0.00   0 0  
parallel query alloc buffer 44 0.00   0 0  
parameter table allocation management 83 0.00   0 0  
post/wait queue 14,011 0.00   0 6,693 0.04
process allocation 150 0.00   0 75 0.00
process group creation 150 0.00   0 0  
qmn task queue latch 351 0.00   0 0  
redo allocation 70,814 2.56 0.00 0 2,589,913 1.14
redo copy 0     0 2,592,195 0.02
redo writing 38,471 0.00   0 0  
resmgr group change latch 450 0.00   0 0  
resmgr:actses active list 224 0.00   0 0  
resmgr:actses change group 222 0.00   0 0  
resmgr:free threads list 149 0.00   0 0  
resmgr:schema config 1 0.00   0 0  
row cache objects 2,074,308 1.17 0.00 0 0  
rules engine rule set statistics 100 0.00   0 0  
sequence cache 1,172,046 1.14 0.00 0 0  
session allocation 941,272 2.95 0.00 0 0  
session idle bit 1,913,499 0.02 0.00 0 0  
session state list latch 445 0.00   0 0  
session switching 5 0.00   0 0  
session timer 113 0.00   0 0  
shared pool 67,530 0.22 0.00 0 0  
shared pool sim alloc 16 0.00   0 0  
shared pool simulator 36,481 0.00   0 0  
simulator hash latch 10,100,262 0.01 0.00 0 0  
simulator lru latch 10,093,406 3.29 0.00 0 2,324 0.00
sort extent pool 1,002 0.20 0.00 0 0  
state object free list 2 0.00   0 0  
statistics aggregation 84 0.00   0 0  
temporary table state object allocation 142 0.00   0 0  
threshold alerts latch 131 0.00   0 0  
transaction allocation 3,598 0.00   0 0  
transaction branch allocation 5 0.00   0 0  
undo global data 48,936,238 0.42 0.00 0 0  
user lock 294 0.00   0 0  

Back to Latch Statistics
Back to Top

Latch Sleep Breakdown

Latch Name Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3
cache buffers chains 333,923,865 11,047,946 3,740 11,044,101 0 0 0
In memory undo latch 44,374,609 349,861 686 349,194 0 0 0
simulator lru latch 10,093,406 332,477 10 332,471 0 0 0
library cache 18,312,942 104,055 2 104,053 0 0 0
session idle bit 1,913,499 421 2 419 0 0 0

Back to Latch Statistics
Back to Top

Latch Miss Sources

Latch Name Where NoWait Misses Sleeps Waiter Sleeps
In memory undo latch ktiFlush: child 0 571 155
In memory undo latch ktichg: child 0 65 393
In memory undo latch kturbk 0 36 122
In memory undo latch kticmt: child 0 14 5
cache buffers chains kcbchg: kslbegin: bufs not pinned 0 13,493 15,426
cache buffers chains kcbgtcr: kslbegin excl 0 6,275 918
cache buffers chains kcbgtcr: fast path 0 5,100 4,456
cache buffers chains kcbchg: kslbegin: call CR func 0 3,771 7,879
cache buffers chains kcbgcur: kslbegin 0 931 220
cache buffers chains kcbget: pin buffer 0 596 46
cache buffers chains kcbrls: kslbegin 0 410 959
cache buffers chains kcbzwb 0 211 121
cache buffers chains kcbzgb: scan from tail. nowait 0 75 0
cache buffers chains kcbcge 0 35 95
cache buffers chains kcb_is_private 0 12 790
cache buffers chains kcbnlc 0 8 21
cache buffers chains kcbget: exchange 0 7 13
cache buffers chains kcbesc: escalate 0 6 0
cache buffers chains kcbgtcr: kslbegin shared 0 6 4
cache buffers chains kcbget: exchange rls 0 3 9
cache buffers chains kcb_pre_apply: kcbhq61 0 2 169
cache buffers chains kcbnew: new latch again 0 1 0
library cache kglpndl: child: after processing 0 2 0
session idle bit ksupuc: set busy 0 2 2
session idle bit ksupuc: clear busy 0 1 1
simulator lru latch kcbs_simulate: simulate set 0 10 10

Back to Latch Statistics
Back to Top

 

Parent Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics
Back to Top

 

Child Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics
Back to Top

 

Segment Statistics

Back to Top

Segments by Logical Reads

Owner Tablespace Name Object Name Subobject Name Obj. Type Logical Reads %Total
HR APPS_TS_TX_DATA PAY_BALANCE_FEEDS_F   TABLE 23,980,992 15.28
HR APPS_TS_TX_IDX PAY_ASSIGNMENT_ACTIONS_PK   INDEX 11,964,384 7.63
HR APPS_TS_TX_DATA PER_TIME_PERIODS   TABLE 8,494,032 5.41
HR APPS_TS_TX_IDX PAY_PAYROLL_ACTIONS_PK   INDEX 7,344,320 4.68
HR APPS_TS_TX_DATA PAY_ASSIGNMENT_ACTIONS   TABLE 6,786,464 4.33

Back to Segment Statistics
Back to Top

Segments by Physical Reads

Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total
SYS SYSAUX WRH$_SQL_PLAN   TABLE 57 6.38
HR APPS_TS_TX_IDX PAY_COSTS_FK3   INDEX 36 4.03
HR APPS_TS_TX_IDX PAY_RUN_RESULT_VALUES_PK   INDEX 16 1.79
HR APPS_TS_TX_IDX PAY_COSTS_PK   INDEX 15 1.68
SYS SYSAUX WRH$_SQL_PLAN_PK   INDEX 13 1.45

Back to Segment Statistics
Back to Top

Segments by Row Lock Waits

Owner Tablespace Name Object Name Subobject Name Obj. Type Row Lock Waits % of Capture
HR APPS_TS_TX_IDX PAY_ACTION_INTERLOCKS_PK   INDEX 573 22.88
HR APPS_TS_TX_IDX PAY_ASSIGNMENT_ACTIONS_N50   INDEX 411 16.41
HR APPS_TS_TX_IDX PAY_ACTION_INTERLOCKS_FK2   INDEX 317 12.66
HR APPS_TS_TX_IDX PAY_ASSIGNMENT_ACTIONS_PK   INDEX 283 11.30
HR APPS_TS_TX_IDX PAY_ASSIGNMENT_ACTIONS_FK2   INDEX 177 7.07

Back to Segment Statistics
Back to Top

Segments by ITL Waits

Owner Tablespace Name Object Name Subobject Name Obj. Type ITL Waits % of Capture
HR APPS_TS_TX_IDX PAY_ACTION_INTERLOCKS_FK2   INDEX 2 100.00

Back to Segment Statistics
Back to Top

Segments by Buffer Busy Waits

Owner Tablespace Name Object Name Subobject Name Obj. Type Buffer Busy Waits % of Capture
HR APPS_TS_TX_IDX PAY_ASSIGNMENT_ACTIONS_PK   INDEX 6,574 16.19
HR APPS_TS_TX_DATA PAY_POPULATION_RANGES   TABLE 5,978 14.72
HR APPS_TS_TX_IDX PAY_ACTION_INTERLOCKS_PK   INDEX 5,424 13.36
HR APPS_TS_TX_DATA PAY_ASSIGNMENT_ACTIONS   TABLE 3,226 7.94
HR APPS_TS_TX_DATA PER_PERIODS_OF_SERVICE   TABLE 2,850 7.02

Back to Segment Statistics
Back to Top

 

Dictionary Cache Stats

Cache Get Requests Pct Miss Scan Reqs Pct Miss Mod Reqs Final Usage
dc_awr_control 10 0.00 0   2 1
dc_global_oids 68 11.76 0   0 37
dc_histogram_data 1,704 3.11 0   0 1,312
dc_histogram_defs 3,138 11.06 0   0 5,656
dc_object_grants 2 0.00 0   0 130
dc_object_ids 12,444 0.85 0   0 2,491
dc_objects 1,015 10.64 0   0 2,160
dc_profiles 73 0.00 0   0 1
dc_rollback_segments 529 0.00 0   0 59
dc_segments 2,078 4.76 0   67 2,736
dc_sequences 397 0.76 0   397 22
dc_table_scns 2 0.00 0   0 1
dc_tablespace_quotas 63 0.00 0   0 5
dc_tablespaces 334,990 0.00 0   0 56
dc_usernames 351 0.00 0   0 43
dc_users 336,423 0.00 0   0 76
outstanding_alerts 55 0.00 0   0 54


Back to Top

 

Library Cache Activity

Namespace Get Requests Pct Miss Pin Requests Pct Miss Reloads Invali- dations
BODY 1,922 0.52 1,649,528 0.00 0 0
CLUSTER 5 0.00 8 0.00 0 0
INDEX 17 0.00 63 0.00 0 0
SQL AREA 135 96.30 14,635,411 0.02 0 0
TABLE/PROCEDURE 1,280 5.63 2,047,299 0.00 0 0
TRIGGER 174 0.00 216,828 0.00 0 0


Back to Top

 

Memory Statistics

Back to Top

Process Memory Summary

  Category Alloc (MB) Used (MB) Avg Alloc (MB) Std Dev Alloc (MB) Max Alloc (MB) Hist Max Alloc (MB) Num Proc Num Alloc
B Other 137.44   1.78 2.99 22 33 77 77
  Freeable 57.63 0.00 1.03 1.95 11   56 56
  SQL 10.74 3.73 0.15 0.27 2 5 71 68
  PL/SQL 7.82 1.55 0.10 0.37 3 3 77 77
E Other 130.69   1.72 2.94 22 33 76 76
  Freeable 56.75 0.00 1.03 1.97 11   55 55
  SQL 10.58 3.68 0.15 0.28 2 5 70 67
  PL/SQL 7.71 1.44 0.10 0.37 3 3 76 76

Back to Memory Statistics
Back to Top

SGA Memory Summary

SGA regions Begin Size (Bytes) End Size (Bytes) (if different)
Database Buffers 2,768,240,640  
Fixed Size 1,265,028  
Redo Buffers 15,511,552  
Variable Size 889,193,084  

Back to Memory Statistics
Back to Top

SGA breakdown difference

Pool Name Begin MB End MB % Diff
java free memory 160.00 160.00 0.00
large PX msg pool 1.02 1.02 0.00
large free memory 14.98 14.98 0.00
shared ASH buffers 22.50 22.50 0.00
shared CCursor 6.31 6.77 7.39
shared FileOpenBlock 7.94 7.94 0.00
shared KGLS heap 15.80 16.39 3.72
shared KTI-UNDO 8.64 8.64 0.00
shared PL/SQL MPCODE   6.51  
shared db_block_hash_buckets 12.00 12.00 0.00
shared event statistics per sess 9.23 9.23 0.00
shared free memory 398.34 391.87 -1.62
shared library cache 9.46 9.75 3.04
shared private strands 8.38 8.38 0.00
shared sql area 30.33 33.87 11.68
streams free memory 47.99 47.99 0.00
  buffer_cache 2,640.00 2,640.00 0.00
  fixed_sga 1.21 1.21 0.00
  log_buffer 14.79 14.79 0.00

Back to Memory Statistics
Back to Top

 

Streams Statistics

Back to Top

Streams CPU/IO Usage

Session Type CPU Time User I/O Time Sys I/O Time
QMON Coordinator 6,005 0 0
QMON Slaves 4,009 0 0

Back to Streams Statistics
Back to Top

 

Streams Capture

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

 

Streams Apply

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Buffered Queues

Queue Schema and Name Incoming per second Outgoing per second Spilled per second Diff Pct Spilled
APPLSYS.WF_CONTROL 0 0 0 0

Back to Streams Statistics
Back to Top

Buffered Subscribers

Subscriber Name Incoming per second Outgoing per second Spilled per second
WFBESCL_20626 0 0 0

Back to Streams Statistics
Back to Top

Rule Set

Ruleset Name Evals Fast Evals SQL Execs CPU Time Elapsed Time
SYS.ALERT_QUE_R 0 0 0 0 0

Back to Streams Statistics
Back to Top

 

 

Resource Limit Stats

No data exists for this section of the report.


Back to Top

 

init.ora Parameters

Parameter Name Begin value End value (if different)
O7_DICTIONARY_ACCESSIBILITY FALSE   
_b_tree_bitmap_plans FALSE   
_fast_full_scan_enabled FALSE   
_kks_use_mutex_pin TRUE   
_like_with_bind_as_equality TRUE   
_sort_elimination_cost_ratio 5   
_sqlexec_progression_cost 2147483647   
_system_trig_enabled TRUE   
_trace_files_public TRUE   
aq_tm_processes 1   
audit_trail NONE   
background_dump_dest /ds1/oracle/VIS/db/tech_st/10.2.0/admin/VIS_r12db/bdump   
cluster_database FALSE   
compatible 10.2.0   
control_files /ds1/oracle/VIS/db/apps_st/data/cntrl01.dbf, /ds1/oracle/VIS/db/apps_st/data/cntrl02.dbf, /ds1/oracle/VIS/db/apps_st/data/cntrl03.dbf   
core_dump_dest /ds1/oracle/VIS/db/tech_st/10.2.0/admin/VIS_r12db/cdump   
cursor_sharing EXACT   
cursor_space_for_time FALSE   
db_block_checking FALSE   
db_block_checksum TRUE   
db_block_size 8192   
db_files 512   
db_name VIS   
disk_asynch_io TRUE   
dml_locks 10000   
filesystemio_options setall   
ifile /ds1/oracle/VIS/db/tech_st/10.2.0/dbs/VIS_r12db_ifile.ora   
java_pool_size 167772160   
job_queue_processes 2   
log_buffer 15271936   
log_checkpoint_interval 99999999   
log_checkpoint_timeout 99999999   
log_checkpoints_to_alert FALSE   
max_dump_file_size unlimited   
nls_comp binary   
nls_date_format DD-MON-RR   
nls_language american   
nls_length_semantics BYTE   
nls_numeric_characters .,   
nls_sort binary   
nls_territory america   
olap_page_pool_size 4194304   
open_cursors 600   
optimizer_secure_view_merging FALSE   
parallel_max_servers 24   
parallel_min_servers 0   
pga_aggregate_target 2147483648   
plsql_code_type INTERPRETED   
plsql_native_library_dir /ds1/oracle/VIS/db/tech_st/10.2.0/plsql/nativelib   
plsql_native_library_subdir_count 149   
plsql_optimize_level 2   
processes 600   
remote_login_passwordfile EXCLUSIVE   
session_cached_cursors 500   
session_max_open_files 20   
sessions 1200   
sga_max_size 3674210304   
sga_target 3674210304   
shared_pool_reserved_size 62914560   
shared_pool_size 637534208   
streams_pool_size 50331648   
timed_statistics TRUE   
undo_management AUTO   
undo_tablespace APPS_UNDOTS1   
user_dump_dest /ds1/oracle/VIS/db/tech_st/10.2.0/admin/VIS_r12db/udump   
utl_file_dir /usr/tmp, /ds1/oracle/VIS/db/tech_st/10.2.0/appsutil/outbound/VIS_r12db   
workarea_size_policy AUTO   


Back to Top

End of Report