WORKLOAD REPOSITORY report for

DB NameDB IdInstanceInst numReleaseRACHost
VIS210556022VIS110.2.0.3.0NOdb1.oasb.cisco

Snap IdSnap TimeSessionsCursors/Session
Begin Snap:909-Feb-11 10:44:46102 26.4
End Snap:1509-Feb-11 10:49:12103 27.2
Elapsed:  4.42 (mins)  
DB Time:  24.58 (mins)  

Report Summary

Cache Sizes

BeginEnd
Buffer Cache: 22,400M 22,400MStd Block Size: 8K
Shared Pool Size: 2,048M 2,048MLog Buffer: 63,464K

Load Profile

Per SecondPer Transaction
Redo size: 2,841,391.36 47,270.97
Logical reads: 791,659.23 13,170.48
Block changes: 18,913.40 314.65
Physical reads: 9.04 0.15
Physical writes: 3.27 0.05
User calls: 3,732.31 62.09
Parses: 2,948.30 49.05
Hard parses: 4.05 0.07
Sorts: 3,050.74 50.75
Logons: 0.78 0.01
Executes: 44,723.90 744.05
Transactions: 60.11 

% Blocks changed per Read: 2.39Recursive Call %: 95.71
Rollback per transaction %: 0.53Rows per Sort: 6.42

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 99.95Redo NoWait %: 100.00
Buffer Hit %: 100.00In-memory Sort %: 100.00
Library Hit %: 100.20Soft Parse %: 99.86
Execute to Parse %: 93.41Latch Hit %: 94.43
Parse CPU to Parse Elapsd %: 67.19% Non-Parse CPU: 99.22

Shared Pool Statistics

BeginEnd
Memory Usage %: 28.50 30.62
% SQL with executions>1: 74.68 93.72
% Memory for SQL w/exec>1: 77.62 91.71

Top 5 Timed Events

EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait Class
CPU time  1,436  97.3 
db file sequential read 2,690 10 4 .7User I/O
log file parallel write 13,963 6 0 .4System I/O
log file sync 8,532 4 0 .3Commit
latch: cache buffers chains 93,870 3 0 .2Concurrency

Main Report


Back to Top

Wait Events Statistics

Back to Top

Time Model Statistics

Statistic NameTime (s)% of DB Time
sql execute elapsed time 1,456.08 98.73
DB CPU 1,435.54 97.34
PL/SQL execution elapsed time 381.18 25.85
parse time elapsed 19.35 1.31
hard parse elapsed time 2.55 0.17
repeated bind elapsed time 0.60 0.04
connection management call elapsed time 0.45 0.03
inbound PL/SQL rpc elapsed time 0.37 0.02
hard parse (sharing criteria) elapsed time 0.34 0.02
PL/SQL compilation elapsed time 0.31 0.02
sequence load elapsed time 0.13 0.01
hard parse (bind mismatch) elapsed time 0.01 0.00
DB time 1,474.82  
background elapsed time 14.68  
background cpu time 5.49  

Back to Wait Events Statistics
Back to Top

Wait Class

Wait ClassWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
User I/O 3,743 0.00 11 3 0.23
System I/O 26,340 0.00 9 0 1.65
Concurrency 206,449 0.00 9 0 12.95
Other 13,181 65.53 4 0 0.83
Commit 8,532 0.00 4 0 0.54
Application 1,992 0.00 3 2 0.12
Network 961,164 0.00 1 0 60.29
Configuration 1,276 0.00 0 0 0.08

Back to Wait Events Statistics
Back to Top

Wait Events

EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
db file sequential read 2,690 0.00 10 4 0.17
log file parallel write 13,963 0.00 6 0 0.88
log file sync 8,532 0.00 4 0 0.54
latch: cache buffers chains 93,870 0.00 3 0 5.89
library cache pin 580 0.00 3 5 0.04
control file sequential read 12,110 0.00 3 0 0.76
enq: UL - contention 687 0.00 2 3 0.04
kksfbc child completion 36 97.22 2 49 0.00
wait list latch free 145 0.00 2 11 0.01
buffer busy waits 99,236 0.00 1 0 6.22
enq: TX - row lock contention 511 0.00 1 1 0.03
enq: TX - index contention 4,236 0.00 1 0 0.27
SQL*Net message to client 926,607 0.00 1 0 58.12
latch: In memory undo latch 6,620 0.00 0 0 0.42
db file scattered read 105 0.00 0 3 0.01
direct path read 688 0.00 0 0 0.04
enq: HW - contention 739 0.00 0 0 0.05
enq: TX - contention 507 0.00 0 0 0.03
buffer deadlock 8,659 99.35 0 0 0.54
latch: cache buffer handles 322 0.00 0 0 0.02
os thread startup 8 0.00 0 17 0.00
SQL*Net more data from client 29,694 0.00 0 0 1.86
db file parallel write 110 0.00 0 1 0.01
latch: library cache pin 531 0.00 0 0 0.03
latch free 917 0.00 0 0 0.06
control file parallel write 157 0.00 0 1 0.01
latch: library cache 1,327 0.00 0 0 0.08
enq: FB - contention 97 0.00 0 1 0.01
latch: undo global data 682 0.00 0 0 0.04
enq: SQ - contention 533 0.00 0 0 0.03
rdbms ipc reply 393 0.00 0 0 0.02
SQL*Net more data to client 4,863 0.00 0 0 0.31
SQL*Net break/reset to client 794 0.00 0 0 0.05
latch: session allocation 845 0.00 0 0 0.05
direct path write 260 0.00 0 0 0.02
LGWR wait for redo copy 532 0.00 0 0 0.03
latch: redo allocation 36 0.00 0 0 0.00
latch: library cache lock 30 0.00 0 0 0.00
latch: enqueue hash chains 10 0.00 0 0 0.00
latch: shared pool 1 0.00 0 0 0.00
cursor: mutex X 10 0.00 0 0 0.00
enq: TX - allocate ITL entry 4 0.00 0 0 0.00
SQL*Net message from client 926,605 0.00 15,963 17 58.12
Streams AQ: waiting for time management or cleanup tasks 1 100.00 1,261 1260537 0.00
Streams AQ: waiting for messages in the queue 134 100.00 997 7442 0.01
pipe get 183 100.00 777 4243 0.01
Streams AQ: qmn slave idle wait 114 0.00 527 4627 0.01
Streams AQ: qmn coordinator idle wait 123 34.96 256 2081 0.01
PL/SQL lock timer 21 100.00 205 9775 0.00
jobq slave wait 55 98.18 160 2917 0.00
class slave wait 2 0.00 0 0 0.00

Back to Wait Events Statistics
Back to Top

Background Wait Events

EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
log file parallel write 13,963 0.00 6 0 0.88
direct path read 686 0.00 0 0 0.04
os thread startup 8 0.00 0 17 0.00
db file parallel write 110 0.00 0 1 0.01
control file parallel write 157 0.00 0 1 0.01
control file sequential read 109 0.00 0 1 0.01
events in waitclass Other 936 0.00 0 0 0.06
direct path write 179 0.00 0 0 0.01
buffer busy waits 1 0.00 0 0 0.00
rdbms ipc message 11,721 8.62 4,332 370 0.74
Streams AQ: waiting for time management or cleanup tasks 1 100.00 1,261 1260537 0.00
Streams AQ: qmn slave idle wait 114 0.00 527 4627 0.01
pmon timer 121 91.74 259 2142 0.01
Streams AQ: qmn coordinator idle wait 123 34.96 256 2081 0.01
smon timer 369 0.00 252 682 0.02

Back to Wait Events Statistics
Back to Top

Operating System Statistics

StatisticTotal
BUSY_TIME 164,503
IDLE_TIME 473,039
IOWAIT_TIME 2,074
NICE_TIME 32
SYS_TIME 5,868
USER_TIME 157,562
LOAD 3
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 22,176,752
NUM_CPUS 24
NUM_CPU_SOCKETS 2

Back to Wait Events Statistics
Back to Top

Service Statistics

Service NameDB Time (s)DB CPU (s)Physical ReadsLogical Reads
VIS 1,453.80 1,424.60 104 208,758,627
SYS$USERS 21.00 11.00 635 192,209
SYS$BACKGROUND 0.00 0.00 1,667 1,012,993

Back to Wait Events Statistics
Back to Top

Service Wait Class Stats

Service NameUser I/O Total WtsUser I/O Wt TimeConcurcy Total WtsConcurcy Wt TimeAdmin Total WtsAdmin Wt TimeNetwork Total WtsNetwork Wt Time
VIS 30 23 206440 870 0 0 960560 67
SYS$USERS 2014 832 0 0 0 0 38 0
SYS$BACKGROUND 1699 206 9 14 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 IdSQL ModuleSQL Text
1,065 1,063 9,990 0.11 72.18 4f5mjr4hnaz3u PYUSLV declare begin pay_archive...
1,062 1,059 9,996 0.11 72.01 5sfm6rppc4y8x PYUSLV BEGIN pay_us_action_arch.actio...
322 322 149,836 0.00 21.83 g3835fnqtvy06 PYUSLV SELECT NVL(SUM(prb.balance_val...
110 111 3,111,082 0.00 7.49 02k10jzw8c495 PYUSLV SELECT INTLK.LOCKING_ACTION_ID...
89 88 9,359 0.01 6.02 9jv68n0ctas75 PYUSLV begin pay_core_utils . get_pro...
63 63 279,321 0.00 4.27 3n5uwzsh3y8gr PYUSLV SELECT NVL(SUM(prb.balance_val...
49 48 9,968 0.00 3.29 80wvw6gatjvb2 PYUSLV SELECT PTP_TD.START_DATE FROM ...
31 30 9,996 0.00 2.13 4ds53shhfcynn PYUSLV declare begin hr_pre_pay....
23 23 582,565 0.00 1.58 5w5up6vdp2qyp PYUSLV SELECT RP.SEQUENCE_NO, RPV.VA...
19 18 120 0.16 1.26 dab1prc9qyv3v PYUSLV begin hr_nonrun_asact...
19 19 583,736 0.00 1.26 g6tx1p7d69dxg PYUSLV SELECT ENT.USER_ENTITY_ID, EN...
18 18 10,000 0.00 1.25 36z3h9rgkd8aa PYUSLV begin ...
18 18 582,272 0.00 1.24 fdv98jawj03hg PYUSLV SELECT FUE.USER_ENTITY_NAME , ...
18 18 109,984 0.00 1.22 6v07302134wyf PYUSLV INSERT INTO PAY_ACTION_INFORMA...
18 17 9,990 0.00 1.20 7mmdd7sypzu9x PYUSLV BEGIN FFP1893_01010001.FORMULA...

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 IdSQL ModuleSQL Text
1,063 1,065 9,990 0.11 72.18 4f5mjr4hnaz3u PYUSLV declare begin pay_archive...
1,059 1,062 9,996 0.11 72.01 5sfm6rppc4y8x PYUSLV BEGIN pay_us_action_arch.actio...
322 322 149,836 0.00 21.83 g3835fnqtvy06 PYUSLV SELECT NVL(SUM(prb.balance_val...
111 110 3,111,082 0.00 7.49 02k10jzw8c495 PYUSLV SELECT INTLK.LOCKING_ACTION_ID...
88 89 9,359 0.01 6.02 9jv68n0ctas75 PYUSLV begin pay_core_utils . get_pro...
63 63 279,321 0.00 4.27 3n5uwzsh3y8gr PYUSLV SELECT NVL(SUM(prb.balance_val...
48 49 9,968 0.00 3.29 80wvw6gatjvb2 PYUSLV SELECT PTP_TD.START_DATE FROM ...
30 31 9,996 0.00 2.13 4ds53shhfcynn PYUSLV declare begin hr_pre_pay....
23 23 582,565 0.00 1.58 5w5up6vdp2qyp PYUSLV SELECT RP.SEQUENCE_NO, RPV.VA...
19 19 583,736 0.00 1.26 g6tx1p7d69dxg PYUSLV SELECT ENT.USER_ENTITY_ID, EN...
18 18 582,272 0.00 1.24 fdv98jawj03hg PYUSLV SELECT FUE.USER_ENTITY_NAME , ...
18 18 10,000 0.00 1.25 36z3h9rgkd8aa PYUSLV begin ...
18 19 120 0.15 1.26 dab1prc9qyv3v PYUSLV begin hr_nonrun_asact...
18 18 109,984 0.00 1.22 6v07302134wyf PYUSLV INSERT INTO PAY_ACTION_INFORMA...
17 18 9,990 0.00 1.20 7mmdd7sypzu9x PYUSLV BEGIN FFP1893_01010001.FORMULA...

Back to SQL Statistics
Back to Top

SQL ordered by Gets

Buffer Gets Executions Gets per Exec %TotalCPU Time (s)Elapsed Time (s) SQL IdSQL ModuleSQL Text
144,815,418 9,996 14,487.34 68.97 1058.86 1061.96 5sfm6rppc4y8x PYUSLV BEGIN pay_us_action_arch.actio...
144,590,895 9,990 14,473.56 68.86 1063.36 1064.52 4f5mjr4hnaz3u PYUSLV declare begin pay_archive...
69,302,841 149,836 462.52 33.01 321.54 322.00 g3835fnqtvy06 PYUSLV SELECT NVL(SUM(prb.balance_val...
32,835,183 3,111,082 10.55 15.64 111.06 110.49 02k10jzw8c495 PYUSLV SELECT INTLK.LOCKING_ACTION_ID...
15,771,098 279,321 56.46 7.51 63.06 63.00 3n5uwzsh3y8gr PYUSLV SELECT NVL(SUM(prb.balance_val...
14,955,276 9,359 1,597.96 7.12 88.12 88.84 9jv68n0ctas75 PYUSLV begin pay_core_utils . get_pro...
9,092,183 9,968 912.14 4.33 48.46 48.52 80wvw6gatjvb2 PYUSLV SELECT PTP_TD.START_DATE FROM ...
6,328,069 582,565 10.86 3.01 23.32 23.25 5w5up6vdp2qyp PYUSLV SELECT RP.SEQUENCE_NO, RPV.VA...
5,474,941 120 45,624.51 2.61 18.09 18.64 dab1prc9qyv3v PYUSLV begin hr_nonrun_asact...
4,886,830 582,272 8.39 2.33 18.39 18.34 fdv98jawj03hg PYUSLV SELECT FUE.USER_ENTITY_NAME , ...
4,855,678 583,736 8.32 2.31 18.54 18.62 g6tx1p7d69dxg PYUSLV SELECT ENT.USER_ENTITY_ID, EN...
4,344,724 9,996 434.65 2.07 30.48 31.41 4ds53shhfcynn PYUSLV declare begin hr_pre_pay....
4,067,671 169,833 23.95 1.94 12.34 12.33 6500rnmy4f88w PYUSLV SELECT PAP.ACCRUAL_PLAN_ID, P...
3,792,986 79,657 47.62 1.81 6.55 6.61 5ts0t88sz97xs PYUSLV SELECT PDB.DEFINED_BALANCE_ID ...
3,599,142 10,000 359.91 1.71 18.27 18.38 36z3h9rgkd8aa PYUSLV begin ...
3,482,742 583,793 5.97 1.66 11.59 11.56 cn5un9cxnah8w PYUSLV SELECT NVL(PBD.PERIOD_TYPE, '...
3,360,316 9,990 336.37 1.60 17.21 17.71 7mmdd7sypzu9x PYUSLV BEGIN FFP1893_01010001.FORMULA...
3,314,650 9,995 331.63 1.58 14.29 14.30 7d66dw0vz7yaa PYUSLV begin pay_cc_process_utils . r...
2,821,223 9,987 282.49 1.34 6.99 6.99 02dvcwb36048p PYUSLV UPDATE PAY_ACTION_INFORMATION ...
2,734,333 9,994 273.60 1.30 6.14 6.14 cj64zg9gg7780 PYUSLV SELECT ACTION_INFORMATION_ID ,...
2,433,989 39,979 60.88 1.16 12.50 12.63 f78ff26qcfjan PYUSLV update PAY_ASSIGNMENT_ACTIONS ...
2,238,285 9,902 226.04 1.07 8.27 8.68 5m04808f1ytsw PYUSLV SELECT NVL(PRT.SHORTNAME, PRA...
2,235,478 9,981 223.97 1.06 9.04 9.59 2yjb88xutjfga PYUSLV update pay_assignment_actions ...
2,232,156 109,984 20.30 1.06 17.82 17.97 6v07302134wyf PYUSLV INSERT INTO PAY_ACTION_INFORMA...
2,215,532 9,989 221.80 1.06 11.13 11.22 a9pf25b5p69c6 PYUSLV declare cursor dp ( p_act in n...
2,191,877 9,922 220.91 1.04 8.31 8.65 4pszqmyjx91xc PYUSLV select tax_unit_id into :b0:b1...
2,188,514 9,922 220.57 1.04 8.38 8.73 gmn54j007428y PYUSLV select tax_unit_id into :b0:b1...
2,136,457 9,994 213.77 1.02 11.77 11.81 gj45bp5w5anrf PYUSLV SELECT DISTINCT PEE.ASSIGNMENT...

Back to SQL Statistics
Back to Top

SQL ordered by Reads

Physical ReadsExecutionsReads per Exec %TotalCPU Time (s)Elapsed Time (s) SQL IdSQL ModuleSQL Text
975 1 975.00 40.68 3.22 5.65 05s9358mm6vrr   begin dbms_feature_usage_inter...
543 1 543.00 22.65 0.75 1.35 43w0r9122v7jm MMON_SLAVE select max(bytes) from dba_seg...
451 1 451.00 18.82 5.08 9.44 1krdnbh743rc6 SQL*Plus DECLARE l_APPLICATION V...
438 81 5.41 18.27 2.56 3.22 b92u4gf9av6ky SQL*Plus begin dbms_stats.gather_table_...
169 1 169.00 7.05 0.06 0.38 00s9ssyu9pwhw MMON_SLAVE select count(*), count(*), N...
45 1 45.00 1.88 0.23 0.27 fmysjzxwxjuwj MMON_SLAVE BEGIN DBMS_FEATURE_PARTITION_S...
39 1,552 0.03 1.63 0.24 0.38 95mpkn5xz9001   insert into hist_head$(obj#, ...
39 1 39.00 1.63 0.04 0.19 9wa06dzuu5g37 MMON_SLAVE select count(*), count(*), N...
38 1,552 0.02 1.59 0.13 0.14 5rxbazwmcdfaz   insert into sys.wri$_optstat_h...
35 1 35.00 1.46 0.05 0.18 1m7zxctxm9bhq MMON_SLAVE select decode(cap + app + prop...

Back to SQL Statistics
Back to Top

SQL ordered by Executions

Executions Rows ProcessedRows per ExecCPU per Exec (s)Elap per Exec (s) SQL IdSQL ModuleSQL Text
3,111,082 3,098,399 1.00 0.00 0.00 02k10jzw8c495 PYUSLV SELECT INTLK.LOCKING_ACTION_ID...
583,793 584,228 1.00 0.00 0.00 cn5un9cxnah8w PYUSLV SELECT NVL(PBD.PERIOD_TYPE, '...
583,736 583,862 1.00 0.00 0.00 g6tx1p7d69dxg PYUSLV SELECT ENT.USER_ENTITY_ID, EN...
583,727 903,595 1.55 0.00 0.00 4gz4b2bh1fs14 PYUSLV SELECT CONTEXT_ID, SEQUENCE_N...
582,882 580,527 1.00 0.00 0.00 77d28bpwm9svg PYUSLV SELECT PBV.RUN_BALANCE_STATUS,...
582,565 1,153,264 1.98 0.00 0.00 5w5up6vdp2qyp PYUSLV SELECT RP.SEQUENCE_NO, RPV.VA...
582,272 582,707 1.00 0.00 0.00 fdv98jawj03hg PYUSLV SELECT FUE.USER_ENTITY_NAME , ...
279,321 278,503 1.00 0.00 0.00 3n5uwzsh3y8gr PYUSLV SELECT NVL(SUM(prb.balance_val...
259,681 259,824 1.00 0.00 0.00 5p88n2zhuqfd9 PYUSLV SELECT PPA.EFFECTIVE_DATE, PP...
169,833 0 0.00 0.00 0.00 6500rnmy4f88w PYUSLV SELECT PAP.ACCRUAL_PLAN_ID, P...
149,836 148,918 0.99 0.00 0.00 g3835fnqtvy06 PYUSLV SELECT NVL(SUM(prb.balance_val...
124,862 0 0.00 0.00 0.00 bm8gwn43tn8hd PYUSLV SELECT PLB.LATEST_BALANCE_ID, ...
124,851 124,912 1.00 0.00 0.00 fh5m86a7xvv2k RUNGEN SELECT 1 FROM PAY_UPGRADE_LEGI...
124,438 124,616 1.00 0.00 0.00 fnnymj5r1f0n8 RUNGEN SELECT PBG.LEGISLATION_CODE FR...
119,836 119,917 1.00 0.00 0.00 6gpzh0p0vbvq9 PYUSLV SELECT 'x' FROM HR_LEGISLATION...

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

Parse CallsExecutions % Total Parses SQL IdSQL ModuleSQL Text
275,950 279,321 35.29 3n5uwzsh3y8gr PYUSLV SELECT NVL(SUM(prb.balance_val...
149,677 149,836 19.14 g3835fnqtvy06 PYUSLV SELECT NVL(SUM(prb.balance_val...
79,752 79,942 10.20 165d2bgc2b9hr PYUSLV SELECT NVL(SUM(prb.balance_val...
44,855 44,968 5.74 8a01qnnc9as1r PYUSLV SELECT NVL(SUM(prb.balance_val...
29,932 29,945 3.83 264qzrka8fsps PYUSLV SELECT NVL(SUM(prb.balance_val...
19,958 19,997 2.55 83kbt99vg4svq PYUSLV begin ...
9,978 9,965 1.28 97mt0g20bcfc3 PYUSLV begin ...
9,976 9,992 1.28 3rg5fyfgd5uwc PYUSLV begin pay_US_rules.get_tim...
9,963 9,996 1.27 d7shgtk3p7s4u PYUSLV SELECT /*+ ordered index(BAL...
9,958 9,996 1.27 4ds53shhfcynn PYUSLV declare begin hr_pre_pay....
9,933 9,996 1.27 5sfm6rppc4y8x PYUSLV BEGIN pay_us_action_arch.actio...
9,930 10,000 1.27 36z3h9rgkd8aa PYUSLV begin ...
9,930 9,990 1.27 4f5mjr4hnaz3u PYUSLV declare begin pay_archive...
8,901 8,901 1.14 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 IdSQL Text
00s9ssyu9pwhwselect count(*), count(*), NULL from dba_aws where AW_NUMBER >= 1000
02dvcwb36048pUPDATE 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
02k10jzw8c495SELECT 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
05s9358mm6vrrbegin dbms_feature_usage_internal.exec_db_usage_sampling(:bind1); end;
165d2bgc2b9hrSELECT 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')
1krdnbh743rc6 DECLARE l_APPLICATION VARCHAR2(240); l_PROGRAM VARCHAR2(240); l_DESCRIPTION VARCHAR2(240); l_START_TIME VARCHAR2(240); l_SUB_REQUEST BOOLEAN := FALSE; l_ARGUMENT1 VARCHAR2(240); l_ARGUMENT2 VARCHAR2(240); l_ARGUMENT3 VARCHAR2(240); l_ARGUMENT4 VARCHAR2(240); l_ARGUMENT5 VARCHAR2(240); l_ARGUMENT6 VARCHAR2(240); l_ARGUMENT7 VARCHAR2(240); l_ARGUMENT8 VARCHAR2(240); l_ARGUMENT9 VARCHAR2(240); l_ARGUMENT10 VARCHAR2(240); l_ARGUMENT11 VARCHAR2(240); l_ARGUMENT12 VARCHAR2(240); l_payroll_name VARCHAR2(240):= '58Semi-Month'; l_user_name VARCHAR2(240):= '58BM_USER'; l_responsibility_name VARCHAR2(240):= '58BM_US SHRMS Manager'; l_start_date VARCHAR2(240):= '2007/09/01'; l_end_date VARCHAR2(240):= '2007/09/15'; l_threads VARCHAR2(240):= '24'; l_success BOOLEAN; l_boolean BOOLEAN; l_req_id number; l_snap_id number; l_errnum number; l_err VARCHAR2(2000); concurrent_program_failure EXCEPTION; terminate_program EXCEPTION; --Cursor to get Payroll Id CURSOR c_payroll_id(p_payroll_name IN VARCHAR2) IS SELECT payroll_id, business_group_id FROM pay_all_payrolls_f WHERE payroll_name = p_payroll_name; l_payroll_id pay_all_payrolls.payroll_id%TYPE; l_business_group_id pay_all_payrolls.business_group_id%TYPE; invalid_payroll_id EXCEPTION; --Cursor to get consolidation set id CURSOR c_consolidation_set_id(p_payroll_id IN NUMBER) IS SELECT c.consolidation_set_id FROM pay_consolidation_sets c , pay_all_payrolls_f p WHERE p.consolidation_set_id = c.consolidation_set_id AND p.payroll_id = p_payroll_id; l_consolidation_set_id pay_consolidation_sets.consolidation_set_id%TYPE; invalid_consolidation_set_id EXCEPTION; CURSOR c_run_type_id IS SELECT run_type_id FROM pay_run_types_f WHERE legislation_code='US' AND run_method = 'C' AND shortname='REGULAR'; l_run_type_id pay_run_types_f.run_type_id%TYPE; invalid_run_type_id EXCEPTION; --Cursor to get User ID CURSOR c_user_id(p_user_name IN VARCHAR2) IS SELECT user_id FROM fnd_user WHERE user_name = p_user_name; l_user_id fnd_user.user_id%TYPE; invalid_user EXCEPTION; --Cursor to get responsibility id CURSOR c_resp_id(p_responsibility_name IN VARCHAR2) IS SELECT responsibility_id, application_id FROM fnd_responsibility_tl WHERE responsibility_name = p_responsibility_name; l_application_id fnd_responsibility_tl.application_id%TYPE; l_responsibility_id fnd_responsibility_tl.responsibility_id%TYPE; invalid_responsibility EXCEPTION; --Cursor to get Org Payment Type id for NACHA(MT)/Check(CH) CURSOR c_payment_type_id(p_payment_type_name IN VARCHAR2, p_category IN VARCHAR2) IS SELECT payment_type_id FROM pay_payment_types WHERE territory_code = 'US' AND category = p_category AND payment_type_name = p_payment_type_name; l_payment_type_id pay_payme nt_types.payment_type_id%TYPE; invalid_payment_type_id EXCEPTION; --Cursor to get Org Payment Method for the NACHA, Business Group and Payroll CURSOR c_org_pay_method_id(p_payment_type_id IN NUMBER , p_business_group_id IN NUMBER , p_payroll_id IN NUMBER) IS SELECT popmf.org_payment_method_id FROM pay_org_payment_methods_f popmf , pay_org_pay_method_usages_f popmuf WHERE payment_type_id = p_payment_type_id AND business_group_id = p_business_group_id AND popmf.org_payment_method_id = popmuf.org_payment_method_id AND popmuf.payroll_id = p_payroll_id; l_org_payment_method_id pay_org_payment_methods_f.org_payment_method_id%TYPE; invalid_org_payment_method_id EXCEPTION; --Cursor to get Org Payment Method for the NACHA, Business Group, Payroll and Check CURSOR c_org_pay_method_id1(p_payment_type_id IN NUMBER , p_business_group_id IN NUMBER , p_payroll_id IN NUMBER , p_org_payment_method_name IN VARCHAR2) IS SELECT popmf.org_payment_method_id FROM pay_org_payment_methods_f popmf , pay_org_pay_method_usages_f popmuf WHERE payment_type_id = p_payment_type_id AND business_group_id = p_business_group_id AND popmf.org_payment_method_id = popmuf.org_payment_method_id AND popmuf.payroll_id = p_payroll_id AND popmf.org_payment_method_name = p_org_payment_method_name; l_org_payment_method_id1 pay_org_payment_methods_f.org_payment_method_id%TYPE; invalid_org_payment_method_id1 EXCEPTION; CURSOR c_payroll(p_reques t_id IN NUMBER) IS SELECT action_status FROM pay_payroll_actions WHERE request_id = p_request_id; l_action_status pay_payroll_actions.action_status%TYPE; PROCEDURE get_status(p_request_id IN NUMBER) IS CURSOR c_payroll(p_request_id IN NUMBER) IS SELECT action_status FROM pay_payroll_actions WHERE request_id = p_request_id; l_action_status pay_payroll_actions.action_status%TYPE; data_found exception; BEGIN -- dbms_output.put_line('begin get_status ...'); OPEN c_payroll(p_request_id); FETCH c_payroll INTO l_action_status; IF c_payroll%NOTFOUND THEN l_action_status := NULL; END IF; CLOSE c_payroll; IF l_action_status NOT IN ('C', 'E') OR l_action_status IS NULL THEN -- dbms_output.put_line('sleeping in get_status ...'); dbms_lock.sleep(10); get_status(p_request_id); ELSE dbms_output.put_line('l_action_status='||l_action_status); raise data_found; -- dbms_output.put_line('raise data_found ...'); END IF; EXCEPTION WHEN data_found THEN -- dbms_output.put_line('step complete ...'); -- dbms_output.put_line('data_found exception ...'); NULL; -- dbms_output.put_line('end get_status ...'); END get_status; BEGIN dbms_output.put_line('BEGIN submitpayroll.sql script ...'); -- l_snap_id := dbms_workload_repository.create_snapshot; -- dbms_output.put_line('BEGIN Snap ID for submitpayroll.sql script -> '||to_char(l_snap_id)); OPEN c_user_id(l_user_name); FETCH c_user_id INTO l_user_id; IF c_user_id%NOTFOUND THEN RAISE invalid_user; END IF; CLOSE c_user_id; OPEN c_resp_id(l_responsibility_name); FETCH c_resp_id INTO l_responsibility_id, l_application_id; IF c_resp_id%NOTFOUND THEN RAISE invalid_responsibility; END IF; CLOSE c_resp_id; OPEN c_payroll_id(l_payroll_name); FETCH c_payroll_id INTO l_payroll_id, l_business_group_id; IF c_payroll_id%NOTFOUND THEN RAISE invalid_payroll_id; END IF; CLOSE c_payroll_id; OPEN c_consolidation_set_id(l_payroll_id); FETCH c_consolidation_set_id INTO l_consolidation_set_id; IF c_consolidation_set_id%NOTFOUND THEN RAISE invalid_consolidation_set_id; END IF; OPEN c_run_type_id; FETCH c_run_type_id INTO l_run_type_id; IF c_run_type_id%NOTFOUND THEN RAISE invalid_run_type_id; END IF; CLOSE c_run_type_id; --Initialize for concurrent program fnd_global.apps_initialize( user_id => l_user_id , resp_id => l_responsibility_id , resp_appl_id => l_application_id); --Start of Submit Payroll Process l_req_id := FND_REQUEST.SUBMIT_REQUEST( APPLICATION => 'PAY' , PROGRAM => 'RUNGEN' , DESCRIPTION => 'Test' , START_TIME => l_START_TIME , SUB_REQUEST => l_SUB_REQUEST , ARGUMENT1 => 'RUN' , ARGUMENT2 => l_payroll_id , ARGUMENT3 => l_consolidation_set_id , ARGUMENT4 => l_start_date --Date Earned , ARGUMENT5 => l_start_date --Date Paid , ARGUMENT6 => l_ARGUMENT6 --Assignment Set , ARGUMENT7 => l_ARGUMENT7 --Element Set , ARGUMENT8 => l_run_type_id , ARGUMENT9 => l_ARGUMENT9 , ARGUMENT10 => l_ARGUMENT10 ); dbms_output.put_line('Request ID for Payroll Process -> '||to_char(l_req_id)); -- l_snap_id := dbms_workload_repository.create_snapshot; -- dbms_output.put_line('BEGIN Snap ID for Payroll Step -> '||to_char(l_snap_id)); IF l_req_id = 0 THEN l_err := fnd_message.get; dbms_output.put_line(l_err); ELSE -- dbms_output.put_line('commiting 1 ...'); COMMIT; --Commit, otherwise program is not submitted END IF; -- dbms_output.put_line('sleeping 2 .....in main call'); -- dbms_lock.sleep(60); get_status(l_req_id); --Recursive call to wait for the completion of process --Test whether the program exited in Error. OPEN c_payroll(l_req_id); FETCH c_payroll INTO l_action_status; CLOSE c_payroll; IF l_action_status = 'E' THEN RAISE concurrent_program_failure; END IF; -- RAISE terminate_program; l_snap_id := dbms_workload_repository.create_snapshot; dbms_output.put_line('END Snap ID for Payroll Step -> '||to_char(l_snap_id)); --End of Submit Payroll Process --Start of PrePayments l_req_id := FND_REQUEST.SUBMIT_REQUEST( APPLICATION => 'PAY' --l_APPLICATION , PROGRAM => 'PREPAY' --l_PROGRAM , DESCRIPTION => 'Test' --l_DESCRIPTION , START_TIME => l_START_TIME , SUB_REQUEST => l_SUB_REQUEST , ARGUMENT1 => 'PREPAY' --Process Name , ARGUMENT2 => l_payroll_id --Payroll , ARGUMENT3 => l_consolidation_set_id --Consolidation Set , ARGUMENT4 => l_start_date --Start Date , ARGUMENT5 => l_end_date --End Date , ARGUMENT6 => NULL --Payment Method Override , ARGUMENT7 => NULL --Action Parameter Group ); dbms_output.put_line('Request ID for PrePayments-> '||to_char(l_req_id)); IF l_req_id = 0 THEN l_err := fnd_message.get; dbms_output.put_line(l_err); ELSE -- dbms_output.put_line('commiting 2 ...'); COMMIT; --Commit, otherwise program is not submitted END IF; -- dbms_output.put_line('sleeping 3 .....in main call'); -- dbms_lock.sleep(60); get_status(l_req_id); --Recursive call to wait for the completion of process --Test whether the program exited in Error. OPEN c_payroll(l_req_id); FETCH c_payroll INTO l_action_status; CLOSE c_payroll; IF l_action_status = 'E' THEN RAISE concurrent_program_failure; END IF; -- RAISE terminate_program; l_snap_id := dbms_workload_repository.create_snapshot; dbms_output.put_line('END Snap ID for PrePayments Step -> '||to_char(l_snap_id)); --End of PrePayments --Start of External Process Archive l_req_id := FND_REQUEST.SUBMIT_REQUEST( APPLICATION => 'PAY' , PROGRAM => 'PAYUSXFR' , DESCRIPTION => 'Test' , START_TIME => l_START_TIME , SUB_REQUEST => l_SUB_ REQUEST , ARGUMENT1 => 'ARCHIVE' , ARGUMENT2 => 'XFR_INTERFACE' , ARGUMENT3 => 'FED' , ARGUMENT4 => l_start_date , ARGUMENT5 => l_end_date , ARGUMENT6 => 'RT' , ARGUMENT7 => l_business_group_id , ARGUMENT8 => 'XFR_INTERFACE' , ARGUMENT9 => 'XFR_INTERFACE' , ARGUMENT10 => l_payroll_id , ARGUMENT11 => l_consolidation_set_id , ARGUMENT12 => '1' , ARGUMENT13 => 'TRANSFER_PAYROLL_ID='||l_payroll_id||'' , ARGUMENT14 => 'TRANSFER_CONSOLIDATION_SET_ID='||l_consolidation_set_id||'' ); dbms_output.put_line('Request ID for external process archive -> '||to_char(l_req_id)); -- l_snap_id := dbms_workload_repository.create_snapshot; -- dbms_output.put_line('BEGIN Snap ID for Archive Step -> '||to_char(l_snap_id)); IF l_req_id = 0 THEN l_err := fnd_message.get; dbms_output.put_line(l_err); ELSE -- dbms_output.put_line('commiting 3 ...'); COMMIT; --Commit, otherwise program is not submitted END IF; get_status(l_req_id); --Test whether the program exited in Error. OPEN c_payroll(l_req_id); FETCH c_payroll INTO l_action_status; CLOSE c_payroll; IF l_action_status = 'E' THEN RAISE concurrent_program_failure; END IF; -- RAISE terminate_program; l_snap_id := dbms_workload_repository.create_snapshot; dbms_output.put_line('END Snap ID for Archive Step -> '||to_char(l_snap_id)); --End of External Process Archive --Start of NACHA OPEN c_payment_type_id('NACHA', 'MT '); FETCH c_payment_type_id INTO l_payment_type_id; IF c_payment_type_id%NOTFOUND THEN RAISE invalid_payment_type_id; END IF; CLOSE c_payment_type_id; OPEN c_org_pay_method_id(l_payment_type_id, l_business_group_id, l_payroll_id); FETCH c_org_pay_method_id INTO l_org_payment_method_id; CLOSE c_org_pay_method_id; l_req_id := FND_REQUEST.SUBMIT_REQUEST( APPLICATION => 'PAY' , PROGRAM => 'NACHA' , DESCRIPTION => 'Test' , START_TIME => l_START_TIME , SUB_REQUEST => l_SUB_REQUEST , ARGUMENT1 => 'MAGTAPE' , ARGUMENT2 => l_payroll_id , ARGUMENT3 => l_consolidation_set_id , ARGUMENT4 => l_start_date , ARGUMENT5 => l_end_date , ARGUMENT6 => l_payment_type_id --Payment Type Name , ARGUMENT7 => l_org_payment_method_id --Org Pay Method Id , ARGUMENT8 => NULL , ARGUMENT9 => '0' , ARGUMENT10 => '1' , ARGUMENT11 => '1' , ARGUMENT12 => 'FILE_ID_MODIFIER=0' , ARGUMENT13 => NULL , ARGUMENT14 => NULL , ARGUMENT15 => NULL ); dbms_output.put_line('Request ID for nacha-> '||to_char(l_req_id)); IF l_req_id = 0 THEN l_err := fnd_message.get; dbms_output.put_line(l_err); ELSE -- dbms_output.put_line('commiting 4 ...'); COMMIT; --Commit, otherwise program is not submitted END IF; -- dbms_output.put_line('sleeping 4 .....in main call'); -- dbms_lock.sleep(60); get_status(l_req_id); --Recursive call to wait for the completion of process --Test whether the program exited in Error. OPEN c_payroll(l_req_id); FETCH c_payroll INTO l_action_status; CLOSE c_payroll; IF l_action_status = 'E' THEN RAISE concurrent_program_failure; END IF; -- RAISE terminate_program; l_snap_id := dbms_workload_repository.create_snapshot; dbms_output.put_line('END Snap ID for NACHA Step -> '||to_char(l_snap_id)); --End of NACHA --Start of Check Writer OPEN c_payment_type_id('Check', 'CH'); FETCH c_payment_type_id INTO l_payment_type_id; IF c_payment_type_id%NOTFOUND THEN RAISE invalid_payment_type_id; END IF; CLOSE c_payment_type_id; OPEN c_org_pay_method_id1(l_payment_type_id, l_business_group_id, l_payroll_id, 'Check'); FETCH c_org_pay_method_id1 INTO l_org_payment_method_id1; CLOSE c_org_pay_method_id1; l_req_id := FND_REQUEST.SUBMIT_REQUEST( APPLICATION => 'PAY' , PROGRAM => 'CHECK_WRITER' , DESCRIPTION => 'Test' , START_TIME => l_START_TIME , SUB_REQUEST => l_SUB_REQUEST , ARGUMENT1 => 'CHEQUE' , ARGUMENT2 => l_payroll_id , ARGUMENT3 => l_consolidation_set_id , ARGUMENT4 => l_start_date , ARGUMENT5 => l_end_date , ARGUMENT6 => l_payment_type_id , ARGUMENT7 => l_org_payment_method_id1 , ARGUMENT8 => 'DEFAULT' , ARGUMENT9 => 'PAYUSCDA' , ARGUMENT10 => '1' , ARGUMENT11 => NULL , ARGUMENT12 => NULL , ARGUMENT13 => NULL , ARGUMENT14 => NULL ); dbms_output.put_line('Re quest ID for check writer-> '||to_char(l_req_id)); IF l_req_id = 0 THEN l_err := fnd_message.get; dbms_output.put_line(l_err); ELSE -- dbms_output.put_line('commiting 5 ...'); COMMIT; --Commit, otherwise program is not submitted END IF; -- dbms_output.put_line('sleeping 5 .....in main call'); -- dbms_lock.sleep(60); get_status(l_req_id); --Recursive call to wait for the completion of process --Test whether the program exited in Error. OPEN c_payroll(l_req_id); FETCH c_payroll INTO l_action_status; CLOSE c_payroll; IF l_action_status = 'E' THEN RAISE concurrent_program_failure; END IF; -- RAISE terminate_program; l_snap_id := dbms_workload_repository.create_snapshot; dbms_output.put_line('END Snap ID for Check Writer Step -> '||to_char(l_snap_id)); --End of Check Writer --Start of Costing l_req_id := FND_REQUEST.SUBMIT_REQUEST( APPLICATION => 'PAY' , PROGRAM => 'COSTING' , DESCRIPTION => 'Test' , START_TIME => l_START_TIME , SUB_REQUEST => l_SUB_REQUEST , ARGUMENT1 => 'COSTING' , ARGUMENT2 => l_payroll_id , ARGUMENT3 => l_consolidation_set_id , ARGUMENT4 => l_start_date , ARGUMENT5 => l_end_date , ARGUMENT6 => NULL ); dbms_output.put_line('Request ID for costing-> '||to_char(l_req_id)); IF l_req_id = 0 THEN l_err := fnd_message.get; dbms_output.put_line(l_err); ELSE -- dbms_output.put_line('commiting 6 ...'); COMMIT; --Commit, otherwise program is not submitted END IF; -- dbms_output.put_line('sleeping 6 .....in main call'); -- dbms_lock.sleep(60); get_status(l_req_id); --Recursive call to wait for the completion of process --Test whether the program exited in Error. OPEN c_payroll(l_req_id); FETCH c_payroll INTO l_action_status; CLOSE c_payroll; IF l_action_status = 'E' THEN RAISE concurrent_program_failure; END IF; -- RAISE terminate_program; -- l_snap_id := dbms_workload_repository.create_snapshot; -- dbms_output.put_line('END Snap ID for submitpayroll.sql script -> '||to_char(l_snap_id)); dbms_output.put_line('END submitpayroll.sql script ...'); --End of Costing --Check for exceptions EXCEPTION WHEN terminate_program THEN dbms_output.put_line ('terminate_program exception encountered') ; WHEN concurrent_program_failure THEN dbms_output.put_line ('concurrent_program_failure exception encountered') ; WHEN invalid_user THEN dbms_output.put_line ('Invalid User ID supplied') ; WHEN invalid_responsibility THEN dbms_output.put_line ('Invalid Responsibility supplied') ; WHEN invalid_payroll_id THEN dbms_output.put_line ('Invalid Payroll ID supplied') ; WHEN invalid_consolidation_set_id THEN dbms_output.put_line ('Invalid consolidation SET ID supplied') ; WHEN invalid_run_type_id THEN dbms_output.put_line ('Invalid Run Type supplied') ; WHEN OTHERS THEN BEGIN dbms_output.put_line('Unknown EXCEPTION encountered ...'); l_errnum := SQLCODE; dbms_output.put_line(l_e rrnum); END ; IF c_payroll%ISOPEN THEN CLOSE c_payroll; END IF; END;
1m7zxctxm9bhqselect decode(cap + app + prop + msg + aq , 0, 0, 1), 0, NULL from (select decode (count(*), 0, 0, 1) cap from dba_capture), (select decode (count(*), 0, 0, 1) app from dba_apply), (select decode (count(*), 0, 0, 1) prop from dba_propagation), (select decode (count(*), 0, 0, 1) msg from dba_streams_message_consumers where streams_name != 'SCHEDULER_COORDINATOR' and streams_name != 'SCHEDULER_PICKUP'), (select decode (count(*), 0, 0, 1) aq from system.aq$_queue_tables)
264qzrka8fspsSELECT 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
2yjb88xutjfgaupdate pay_assignment_actions set action_status='C' where assignment_action_id=:b0
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
3rg5fyfgd5uwcbegin pay_US_rules.get_time_def_for_entry( :element_entry_id, :assignment_id, :assignment_action_id, :business_group_id, :time_def_id); end;
43w0r9122v7jmselect max(bytes) from dba_segments
4ds53shhfcynndeclare begin hr_pre_pay.do_prepayment(:p1, :p2, :p3, :p4, :5, :p6:p6ind, :p7); end;
4f5mjr4hnaz3udeclare begin pay_archive.process_employee(:asg_action); end;
4gz4b2bh1fs14SELECT CONTEXT_ID, SEQUENCE_NO FROM FF_ROUTE_CONTEXT_USAGES WHERE ROUTE_ID = :B1 ORDER BY SEQUENCE_NO
4pszqmyjx91xcselect tax_unit_id into :b0:b1 from pay_assignment_actions where assignment_action_id=:b2
5m04808f1ytswSELECT NVL(PRT.SHORTNAME, PRA.LEGISLATIVE_PARAMETERS), 'FF ROUTE:RUNTYPE' FROM PAY_PAYROLL_ACTIONS PRA, PAY_ASSIGNMENT_ACTIONS PAA, PAY_RUN_TYPES_F PRT WHERE PRA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID AND PAA.ASSIGNMENT_ACTION_ID = :B1 AND NVL(PAA.RUN_TYPE_ID, -1) = PRT.RUN_TYPE_ID (+)
5p88n2zhuqfd9SELECT PPA.EFFECTIVE_DATE, PPA.BUSINESS_GROUP_ID, PPA.PAYROLL_ID, PPA.ACTION_TYPE FROM PAY_PAYROLL_ACTIONS PPA, PAY_ASSIGNMENT_ACTIONS PAA WHERE PAA.ASSIGNMENT_ACTION_ID = :B1 AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
5rxbazwmcdfazinsert into sys.wri$_optstat_histhead_history (obj#, intcol#, savtime, flags) values (:1, :2, :3, 0)
5sfm6rppc4y8xBEGIN pay_us_action_arch.action_archive_data(:aactid, :l_effective_date); END;
5ts0t88sz97xsSELECT PDB.DEFINED_BALANCE_ID FROM PAY_DEFINED_BALANCES PDB, PAY_BALANCE_DIMENSIONS PBD WHERE PDB.BALANCE_TYPE_ID = :B3 AND PBD.DATABASE_ITEM_SUFFIX = :B2 AND PBD.BALANCE_DIMENSION_ID = PDB.BALANCE_DIMENSION_ID AND ((PBD.LEGISLATION_CODE = :B1 AND PBD.BUSINESS_GROUP_ID IS NULL) OR (PBD.LEGISLATION_CODE IS NULL AND PBD.BUSINESS_GROUP_ID IS NOT NULL))
5w5up6vdp2qypSELECT 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
6500rnmy4f88wSELECT 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
6gpzh0p0vbvq9SELECT 'x' FROM HR_LEGISLATION_INSTALLATIONS WHERE :B2 =APPLICATION_SHORT_NAME AND NVL(:B1 , 'x')=NVL(LEGISLATION_CODE, 'x') AND (STATUS='I' OR ACTION IS NOT NULL)
6v07302134wyfINSERT 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 )
77d28bpwm9svgSELECT 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
7d66dw0vz7yaabegin 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 ;
7mmdd7sypzu9xBEGIN 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;
80wvw6gatjvb2SELECT 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;
8a01qnnc9as1rSELECT NVL(SUM(prb.balance_value), 0) FROM /* Assignment in JD Government Reporting Entity for YTD based on run balances */ pay_balance_types pbt, 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 pbt.balance_type_id = :U1 and pdb.balance_type_id = pbt.balance_type_id 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') and substr(prb.jurisdiction_code, 1, pbt.jurisdiction_level) = substr(:JURISDICTION_CODE, 1, pbt.jurisdiction_level)
95mpkn5xz9001insert into hist_head$(obj#, intcol#, bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, spare1, spare2, avgcln, col#) values(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18)
97mt0g20bcfc3 begin pay_US_rules.get_default_jurisdiction (:asgact, :eeid, :defval:defvalind); end;
9jv68n0ctas75begin 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 ;
9wa06dzuu5g37select count(*), count(*), NULL from DBA_OLAP2_CUBES where invalid != 'Y' and OWNER = 'SYS' and CUBE_NAME = 'STKPRICE_TBL'
a9pf25b5p69c6declare cursor dp ( p_act in number ) is select distinct paf2 . assignment_id , paa . action_sequence from pay_assignment_actions paa , pay_payroll_actions ppa , per_all_assignments_f paf , per_periods_of_service pos , per_all_assignments_f paf2 where paa . assignment_action_id = p_act and paa . payroll_action_id = ppa . payroll_action_id and paf . assignment_id = paa . assignment_id and ppa . effective_date between paf . effective_start_date and paf . effective_end_date and paf . person_id = pos . person_id and pos . period_of_service_id = paf2 . period_of_service_id ; BEGIN for dprec in dp ( :assactid ) loop begin select 1 into :act_exists from sys . dual where exists ( select null from pay_assignment_actions paa , pay_payroll_actions ppa , pay_action_classifications pcl where paa . assignment_id = dprec . assignment_id and paa . payroll_action_id = ppa . payroll_action_id and ppa . action_type = pcl . action_type and pcl . classification_name = 'SEQUENCED' and paa . action_status not in ( 'C' , 'S' , 'B' ) and paa . action_sequence < dprec . action_sequence ) ; exception when no_data_found then null ; end ; end loop ; END ;
b92u4gf9av6kybegin dbms_stats.gather_table_stats('SYS', :bind1, cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE); end;
bm8gwn43tn8hd SELECT PLB.LATEST_BALANCE_ID, PLB.ASSIGNMENT_ACTION_ID, PLB.VALUE, PLB.EXPIRY_DATE, ACT.ACTION_SEQUENCE CURRENT_ACTION_SEQ, OWN_ACT.ACTION_SEQUENCE OWNER_ACTION_SEQ, PACT.EFFECTIVE_DATE CURRENT_EFFECTIVE_DATE, PLB.EXPIRED_ASSIGNMENT_ACTION_ID, PLB.EXPIRED_VALUE, PLB.EXPIRED_DATE, PLB.PREV_ASSIGNMENT_ACTION_ID, PLB.PREV_BALANCE_VALUE, PLB.PREV_EXPIRY_DATE, PLB.TAX_UNIT_ID, PLB.JURISDICTION_CODE, PLB.ORIGINAL_ENTRY_ID, PLB.SOURCE_ID, PLB.SOURCE_TEXT, PLB.SOURCE_TEXT2, PLB.SOURCE_NUMBER, PLB.TAX_GROUP, PLB.PAYROLL_ID, PLB.LOCAL_UNIT_ID, PLB.ORGANIZATION_ID, PLB.SOURCE_NUMBER2 FROM PAY_LATEST_BALANCES PLB , PER_ALL_ASSIGNMENTS_F ASSIGN , PAY_ASSIGNMENT_ACTIONS ACT , PAY_PAYROLL_ACTIONS PACT , PAY_ASSIGNMENT_ACTIONS OWN_ACT WHERE PLB.DEFINED_BALANCE_ID = :B15 AND ACT.ASSIGNMENT_ACTION_ID = :B14 AND ASSIGN.ASSIGNMENT_ID = ACT.ASSIGNMENT_ID AND EXISTS (SELECT '' FROM PAY_OBJECT_GROUPS POG WHERE POG.SOURCE_ID (+) = ACT.ASSIGNMENT_ID AND POG.SOURCE_TYPE (+) = 'PAF' AND NVL(PLB.PROCESS_GROUP_ID, NVL(POG.PARENT_OBJECT_GROUP_ID, -1)) = NVL(POG.PARENT_OBJECT_GROUP_ID, -1) ) AND PLB.PERSON_ID = ASSIGN.PERSON_ID AND NVL(PLB.ASSIGNMENT_ID, ACT.ASSIGNMENT_ID) = ACT.ASSIGNMENT_ID AND PACT.PAYROLL_ACTION_ID = ACT.PAYROLL_ACTION_ID AND PACT.EFFECTIVE_DATE BETWEEN ASSIGN.EFFECTIVE_START_DATE AND ASSIGN.EFFECTIVE_END_DATE AND OWN_ACT.ASSIGNMENT_ACTION_ID = PLB.ASSIGNMENT_ACTION_ID AND NVL(PLB.TAX_UNIT_ID, NVL(:B13 , -1)) = NVL(:B13 , -1) AND NVL(SUBSTR(NVL(PLB.JUR ISDICTION_CODE, NVL(:B12 , -1)), 1, :B11 ), -1) = NVL( SUBSTR(NVL(:B12 , -1), 1, :B11 ), -1) AND NVL(PLB.ORIGINAL_ENTRY_ID, NVL(:B10 , -1)) = NVL(:B10 , -1) AND NVL(PLB.SOURCE_ID, NVL(:B9 , -1)) = NVL(:B9 , -1) AND NVL(PLB.SOURCE_TEXT, NVL(:B8 , -1)) = NVL(:B8 , -1) AND NVL(PLB.SOURCE_TEXT2, NVL(:B7 , -1)) = NVL(:B7 , -1) AND NVL(PLB.SOURCE_NUMBER, NVL(:B6 , -1)) = NVL(:B6 , -1) AND NVL(PLB.TAX_GROUP, NVL(:B5 , -1)) = NVL(:B5 , -1) AND NVL(PLB.PAYROLL_ID, NVL(:B4 , -1)) = NVL(:B4 , -1) AND NVL(PLB.LOCAL_UNIT_ID, NVL(:B3 , -1)) = NVL(:B3 , -1) AND NVL(PLB.ORGANIZATION_ID, NVL(:B2 , -1)) = NVL(:B2 , -1) AND NVL(PLB.SOURCE_NUMBER2, NVL(:B1 , -1)) = NVL(:B1 , -1) ORDER BY PLB.LATEST_BALANCE_ID
cj64zg9gg7780SELECT 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
cn5un9cxnah8wSELECT 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
cr74qtm56vcq6BEGIN 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;
f78ff26qcfjanupdate PAY_ASSIGNMENT_ACTIONS set ACTION_STATUS='C' where (rowid=:b0 and ACTION_STATUS<>'S')
fdv98jawj03hgSELECT 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 )
fh5m86a7xvv2kSELECT 1 FROM PAY_UPGRADE_LEGISLATIONS PUL WHERE PUL.UPGRADE_DEFINITION_ID = :B2 AND PUL.LEGISLATION_CODE = :B1
fmysjzxwxjuwjBEGIN DBMS_FEATURE_PARTITION_SYSTEM(:feature_boolean, :aux_cnt, :feature_info); END;
fnnymj5r1f0n8SELECT PBG.LEGISLATION_CODE FROM PER_BUSINESS_GROUPS PBG WHERE PBG.BUSINESS_GROUP_ID = :B1
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)
g6tx1p7d69dxgSELECT 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
gj45bp5w5anrfSELECT DISTINCT PEE.ASSIGNMENT_ID , PET.PRORATION_GROUP_ID FROM PAY_ELEMENT_ENTRIES_F PEE, PAY_ELEMENT_TYPES_F PET WHERE PEE.ELEMENT_ENTRY_ID = :B2 AND PEE.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND PEE.EFFECTIVE_START_DATE <= :B1 AND PEE.EFFECTIVE_END_DATE >= PAY_INTERPRETER_PKG.TIME_FN(:B3 , PET.PRORATION_GROUP_ID , :B2 ) AND :B1 BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
gmn54j007428yselect tax_unit_id into :b0:b1 from pay_assignment_actions paa where paa.assignment_action_id=:b2

Back to SQL Statistics
Back to Top

Instance Activity Statistics

Back to Top

Instance Activity Stats

StatisticTotalper Secondper Trans
CPU used by this session 147,741 557.05 9.27
CPU used when call started 147,753 557.10 9.27
CR blocks created 778,805 2,936.45 48.85
Cached Commit SCN referenced 1,655,114 6,240.53 103.82
Commit SCN cached 307 1.16 0.02
DB time 358,466 1,351.58 22.49
DBWR checkpoint buffers written 590 2.22 0.04
DBWR checkpoints 0 0.00 0.00
DBWR parallel query checkpoint buffers written 0 0.00 0.00
DBWR thread checkpoint buffers written 0 0.00 0.00
DBWR transaction table writes 34 0.13 0.00
DBWR undo block writes 476 1.79 0.03
DFO trees parallelized 0 0.00 0.00
IMU CR rollbacks 37,568,862 141,651.69 2,356.60
IMU Flushes 8,915 33.61 0.56
IMU Redo allocation size 154,236,888 581,543.20 9,674.88
IMU commits 7,454 28.10 0.47
IMU contention 3,945 14.87 0.25
IMU ktichg flush 184 0.69 0.01
IMU pool not allocated 0 0.00 0.00
IMU recursive-transaction flush 234 0.88 0.01
IMU undo allocation size 220,527,576 831,489.24 13,833.12
IMU- failed to get a private strand 0 0.00 0.00
PX local messages recv'd 0 0.00 0.00
PX local messages sent 0 0.00 0.00
Parallel operations downgraded 25 to 50 pct 0 0.00 0.00
Parallel operations downgraded 50 to 75 pct 0 0.00 0.00
Parallel operations not downgraded 0 0.00 0.00
SMON posted for undo segment shrink 1 0.00 0.00
SQL*Net roundtrips to/from client 926,042 3,491.60 58.09
active txn count during cleanout 4,380,265 16,515.59 274.76
application wait time 308 1.16 0.02
background checkpoints completed 0 0.00 0.00
background checkpoints started 0 0.00 0.00
background timeouts 1,024 3.86 0.06
branch node splits 12 0.05 0.00
buffer is not pinned count 91,742,417 345,910.63 5,754.76
buffer is pinned count 50,797,551 191,529.87 3,186.40
bytes received via SQL*Net from client 241,675,455 911,226.36 15,159.67
bytes sent via SQL*Net to client 149,902,361 565,200.06 9,402.98
calls to get snapshot scn: kcmgss 12,042,780 45,406.76 755.41
calls to kcmgas 827,467 3,119.93 51.90
calls to kcmgcs 19,188 72.35 1.20
change write time 1,070 4.03 0.07
cleanout - number of ktugct calls 685,688 2,585.36 43.01
cleanouts and rollbacks - consistent read gets 639,514 2,411.26 40.12
cleanouts only - consistent read gets 5,616 21.17 0.35
cluster key scan block gets 520,811 1,963.69 32.67
cluster key scans 357,935 1,349.58 22.45
commit batch/immediate performed 646 2.44 0.04
commit batch/immediate requested 646 2.44 0.04
commit cleanout failures: block lost 368 1.39 0.02
commit cleanout failures: callback failure 17,495 65.96 1.10
commit cleanout failures: cannot pin 3,200 12.07 0.20
commit cleanouts 219,172 826.38 13.75
commit cleanouts successfully completed 198,109 746.96 12.43
commit immediate performed 646 2.44 0.04
commit immediate requested 646 2.44 0.04
commit txn count during cleanout 64,509 243.23 4.05
concurrency wait time 883 3.33 0.06
consistent changes 81,460,563 307,143.36 5,109.81
consistent gets 204,193,309 769,901.63 12,808.51
consistent gets - examination 142,484,131 537,229.96 8,937.66
consistent gets direct 10 0.04 0.00
consistent gets from cache 204,193,299 769,901.59 12,808.51
cursor authentications 883 3.33 0.06
data blocks consistent reads - undo records applied 81,397,034 306,903.83 5,105.82
db block changes 5,016,212 18,913.40 314.65
db block gets 5,770,551 21,757.60 361.97
db block gets direct 98 0.37 0.01
db block gets from cache 5,770,453 21,757.23 361.97
deferred (CURRENT) block cleanout applications 26,070 98.30 1.64
enqueue conversions 3,139 11.84 0.20
enqueue releases 108,579 409.39 6.81
enqueue requests 109,054 411.18 6.84
enqueue timeouts 475 1.79 0.03
enqueue waits 5,953 22.45 0.37
exchange deadlocks 8,664 32.67 0.54
execute count 11,861,672 44,723.90 744.05
failed probes on index block reclamation 2 0.01 0.00
free buffer inspected 112,450 423.99 7.05
free buffer requested 845,826 3,189.15 53.06
heap block compress 13,119 49.46 0.82
immediate (CR) block cleanout applications 645,132 2,432.44 40.47
immediate (CURRENT) block cleanout applications 27,898 105.19 1.75
index crx upgrade (found) 41,321 155.80 2.59
index crx upgrade (positioned) 990,425 3,734.35 62.13
index fast full scans (direct read) 0 0.00 0.00
index fast full scans (full) 0 0.00 0.00
index fast full scans (rowid ranges) 0 0.00 0.00
index fetch by key 28,676,473 108,123.34 1,798.80
index scans kdiixs1 23,165,719 87,345.29 1,453.13
leaf node 90-10 splits 1,376 5.19 0.09
leaf node splits 6,549 24.69 0.41
lob reads 2,008 7.57 0.13
lob writes 4,259 16.06 0.27
lob writes unaligned 4,259 16.06 0.27
logons cumulative 206 0.78 0.01
messages received 14,472 54.57 0.91
messages sent 14,472 54.57 0.91
no buffer to keep pinned count 0 0.00 0.00
no work - consistent read gets 59,298,354 223,581.76 3,719.63
opened cursors cumulative 731,101 2,756.58 45.86
parse count (failures) 0 0.00 0.00
parse count (hard) 1,074 4.05 0.07
parse count (total) 781,948 2,948.30 49.05
parse time cpu 1,118 4.22 0.07
parse time elapsed 1,664 6.27 0.10
physical read IO requests 1,920 7.24 0.12
physical read bytes 19,636,224 74,037.49 1,231.73
physical read total IO requests 15,584 58.76 0.98
physical read total bytes 230,776,832 870,133.59 14,476.03
physical read total multi block requests 105 0.40 0.01
physical reads 2,397 9.04 0.15
physical reads cache 1,701 6.41 0.11
physical reads cache prefetch 477 1.80 0.03
physical reads direct 696 2.62 0.04
physical reads direct (lob) 2 0.01 0.00
physical reads direct temporary tablespace 0 0.00 0.00
physical reads prefetch warmup 276 1.04 0.02
physical write IO requests 395 1.49 0.02
physical write bytes 7,102,464 26,779.52 445.52
physical write total IO requests 20,605 77.69 1.29
physical write total bytes 799,430,144 3,014,215.16 50,146.16
physical write total multi block requests 19,140 72.17 1.20
physical writes 867 3.27 0.05
physical writes direct 277 1.04 0.02
physical writes direct (lob) 98 0.37 0.01
physical writes direct temporary tablespace 0 0.00 0.00
physical writes from cache 590 2.22 0.04
physical writes non checkpoint 720 2.71 0.05
process last non-idle time 240 0.90 0.02
queries parallelized 0 0.00 0.00
recursive aborts on index block reclamation 0 0.00 0.00
recursive calls 22,105,849 83,349.10 1,386.64
recursive cpu usage 133,245 502.39 8.36
redo blocks written 1,532,443 5,778.01 96.13
redo buffer allocation retries 0 0.00 0.00
redo entries 2,567,304 9,679.90 161.04
redo log space requests 0 0.00 0.00
redo log space wait time 0 0.00 0.00
redo ordering marks 29,123 109.81 1.83
redo size 753,593,816 2,841,391.36 47,270.97
redo subscn max counts 51,184 192.99 3.21
redo synch time 453 1.71 0.03
redo synch writes 8,384 31.61 0.53
redo wastage 5,144,004 19,395.23 322.67
redo write time 675 2.55 0.04
redo writer latching time 1 0.00 0.00
redo writes 13,963 52.65 0.88
rollback changes - undo records applied 2,793 10.53 0.18
rollbacks only - consistent read gets 144,544 545.00 9.07
rows fetched via callback 18,538,469 69,898.46 1,162.87
session connect time 0 0.00 0.00
session cursor cache hits 757,701 2,856.88 47.53
session logical reads 209,963,860 791,659.23 13,170.48
session pga memory 13,952,616 52,607.71 875.21
session pga memory max 7,219,464 27,220.66 452.86
session uga memory 34,368,594,840 129,585,230.53 2,155,852.14
session uga memory max 692,235,904 2,610,044.13 43,422.15
shared hash latch upgrades - no wait 1,099,733 4,146.49 68.98
shared hash latch upgrades - wait 49,998 188.52 3.14
sorts (memory) 809,118 3,050.74 50.75
sorts (rows) 5,194,094 19,584.10 325.81
sql area purged 1 0.00 0.00
switch current to new buffer 12,133 45.75 0.76
table fetch by rowid 58,706,346 221,349.62 3,682.50
table fetch continued row 3,418 12.89 0.21
table scan blocks gotten 8,820,906 33,258.83 553.31
table scan rows gotten 381,257,684 1,437,514.83 23,915.30
table scans (direct read) 0 0.00 0.00
table scans (long tables) 0 0.00 0.00
table scans (rowid ranges) 0 0.00 0.00
table scans (short tables) 276,782 1,043.59 17.36
total number of times SMON posted 369 1.39 0.02
transaction rollbacks 646 2.44 0.04
undo change vector size 263,270,152 992,648.19 16,514.25
user I/O wait time 1,055 3.98 0.07
user calls 989,882 3,732.31 62.09
user commits 15,857 59.79 0.99
user rollbacks 85 0.32 0.01
workarea executions - optimal 503,823 1,899.64 31.60
write clones created in foreground 0 0.00 0.00

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

StatisticBegin ValueEnd Value
session cursor cache count 18,696 26,801
opened cursors current 2,694 2,797
logons current 102 103

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

StatisticTotalper Hour
log switches (derived) 0 0.00

Back to Instance Activity Statistics
Back to Top

IO Stats

Back to Top

Tablespace IO Stats

TablespaceReadsAv Reads/sAv Rd(ms)Av Blks/RdWritesAv Writes/sBuffer WaitsAv Buf Wt(ms)
SYSTEM 889 3 2.40 1.14 13 0 88 0.00
SYSAUX 335 1 2.54 1.81 99 0 0 0.00
APPS_TS_TX_IDX 267 1 7.27 1.00 67 0 40,305 0.02
APPS_TS_TX_DATA 175 1 6.29 1.50 88 0 21,216 0.02
APPS_UNDOTS1 7 0 8.57 1.00 77 0 36,197 0.01
CTXSYS 14 0 9.29 1.00 2 0 0 0.00
CWMLITE 13 0 16.92 1.00 2 0 0 0.00
DCM 12 0 9.17 1.00 2 0 0 0.00
GEOR_TBS 12 0 19.17 1.00 2 0 0 0.00
APPS_CALCLIP 4 0 7.50 1.00 4 0 0 0.00
B2B_LOB 7 0 14.29 1.00 1 0 0 0.00
B2B_RT 7 0 8.57 1.00 1 0 0 0.00
BAM 7 0 11.43 1.00 1 0 0 0.00
ODM_DATA 7 0 10.00 1.00 1 0 0 0.00
OLTS_ATTRSTORE 7 0 11.43 1.00 1 0 0 0.00
OLTS_BATTRSTORE 7 0 12.86 1.00 1 0 0 0.00
OLTS_CT_STORE 7 0 20.00 1.00 1 0 0 0.00
OLTS_DEFAULT 7 0 15.71 1.00 1 0 0 0.00
OLTS_SVRMGSTORE 7 0 12.86 1.00 1 0 0 0.00
B2B_DT 6 0 3.33 1.00 1 0 0 0.00
B2B_IDX 6 0 3.33 1.00 1 0 0 0.00
DISCO_PTM5_CACHE 6 0 16.67 1.00 1 0 0 0.00
DISCO_PTM5_META 6 0 16.67 1.00 1 0 0 0.00
DSGATEWAY_TAB 6 0 5.00 1.00 1 0 0 0.00
HTMLDB 6 0 16.67 1.00 1 0 0 0.00
IAS_META 6 0 30.00 1.00 1 0 0 0.00
MTR 6 0 10.00 1.00 1 0 0 0.00
NOETIX 6 0 6.67 1.00 1 0 0 0.00
NON_APPS_TS_DATA 6 0 13.33 1.00 1 0 0 0.00
OCATS 6 0 5.00 1.00 1 0 0 0.00
OPMOR 6 0 10.00 1.00 1 0 0 0.00
ORABPEL 6 0 23.33 1.00 1 0 0 0.00
OWAPUB 6 0 11.67 1.00 1 0 0 0.00
PORTAL 6 0 5.00 1.00 1 0 0 0.00
PORTAL_DOC 6 0 16.67 1.00 1 0 0 0.00
PORTAL_IDX 6 0 10.00 1.00 1 0 0 0.00
PORTAL_LOG 6 0 18.33 1.00 1 0 0 0.00
RE 6 0 10.00 1.00 1 0 0 0.00
SYNCSERVER 6 0 10.00 1.00 1 0 0 0.00
UDDISYS_TS 6 0 13.33 1.00 1 0 0 0.00
WCRSYS_TS 6 0 10.00 1.00 1 0 0 0.00
XDB 6 0 20.00 1.00 1 0 0 0.00
APPS_TS_ARCHIVE 1 0 10.00 1.00 4 0 67 1.64
APPS_OMO 1 0 0.00 1.00 1 0 0 0.00
APPS_TS_DISCO 1 0 0.00 1.00 1 0 0 0.00
APPS_TS_DISCO_OLAP 1 0 0.00 1.00 1 0 0 0.00

Back to IO Stats
Back to Top

File IO Stats

TablespaceFilenameReadsAv Reads/sAv Rd(ms)Av Blks/RdWritesAv Writes/sBuffer WaitsAv Buf Wt(ms)
APPS_CALCLIP /oracle/visdata/apps_calclip.dbf 1 0 10.00 1.00 1 0 0 0.00
APPS_CALCLIP /oracle/visdata/apps_calclip1.dbf 1 0 10.00 1.00 1 0 0 0.00
APPS_CALCLIP /oracle/visdata/apps_calclip2.dbf 1 0 0.00 1.00 1 0 0 0.00
APPS_CALCLIP /oracle/visdata/apps_calclip3.dbf 1 0 10.00 1.00 1 0 0 0.00
APPS_OMO /oracle/visdata/omo1.dbf 1 0 0.00 1.00 1 0 0 0.00
APPS_TS_ARCHIVE /oracle/visdata/archive1.dbf 1 0 10.00 1.00 4 0 67 1.64
APPS_TS_DISCO /oracle/visdata/disco1.dbf 1 0 0.00 1.00 1 0 0 0.00
APPS_TS_DISCO_OLAP /oracle/visdata/disco_drake1.dbf 1 0 0.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data18.dbf 1 0 0.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data19.dbf 1 0 0.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data2.dbf 1 0 0.00 1.00 1 0 273 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data20.dbf 1 0 0.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data21.dbf 1 0 0.00 1.00 1 0 502 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data22.dbf 1 0 0.00 1.00 1 0 601 0.02
APPS_TS_TX_DATA /oracle/visdata/tx_data23.dbf 1 0 0.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data24.dbf 1 0 10.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data25.dbf 1 0 0.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data26.dbf 1 0 0.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data27.dbf 3 0 6.67 5.00 5 0 293 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data28.dbf 1 0 0.00 1.00 2 0 1,860 0.04
APPS_TS_TX_DATA /oracle/visdata/tx_data29.dbf 1 0 0.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data3.dbf 1 0 0.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data30.dbf 1 0 0.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data31.dbf 1 0 0.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data32.dbf 1 0 0.00 1.00 1 0 778 0.04
APPS_TS_TX_DATA /oracle/visdata/tx_data33.dbf 1 0 0.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data34.dbf 1 0 0.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data35.dbf 1 0 0.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data36.dbf 2 0 0.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data37.dbf 2 0 0.00 1.00 1 0 1,798 0.03
APPS_TS_TX_DATA /oracle/visdata/tx_data38.dbf 2 0 5.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data39.dbf 4 0 2.50 4.50 2 0 1,048 0.04
APPS_TS_TX_DATA /oracle/visdata/tx_data4.dbf 2 0 10.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data40.dbf 2 0 0.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data41.dbf 2 0 0.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data42.dbf 2 0 5.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data43.dbf 2 0 0.00 1.00 3 0 2,393 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data44.dbf 2 0 5.00 1.00 2 0 660 0.03
APPS_TS_TX_DATA /oracle/visdata/tx_data45.dbf 2 0 0.00 1.00 1 0 455 0.07
APPS_TS_TX_DATA /oracle/visdata/tx_data46.dbf 2 0 10.00 1.00 1 0 564 0.02
APPS_TS_TX_DATA /oracle/visdata/tx_data47.dbf 2 0 0.00 1.00 1 0 395 0.03
APPS_TS_TX_DATA /oracle/visdata/tx_data48.dbf 2 0 15.00 1.00 1 0 514 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data49.dbf 2 0 0.00 1.00 1 0 322 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data5.dbf 2 0 5.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data50.dbf 2 0 0.00 1.00 1 0 261 0.04
APPS_TS_TX_DATA /oracle/visdata/tx_data51.dbf 3 0 6.67 1.00 1 0 805 0.01
APPS_TS_TX_DATA /oracle/visdata/tx_data52.dbf 2 0 5.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data53.dbf 2 0 15.00 1.00 1 0 137 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data54.dbf 4 0 7.50 1.00 2 0 195 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data55.dbf 3 0 3.33 1.00 1 0 107 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data56.dbf 3 0 3.33 1.00 1 0 146 0.07
APPS_TS_TX_DATA /oracle/visdata/tx_data57.dbf 3 0 6.67 1.00 1 0 337 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data58.dbf 3 0 6.67 1.00 2 0 627 0.02
APPS_TS_TX_DATA /oracle/visdata/tx_data59.dbf 3 0 6.67 1.00 2 0 291 0.03
APPS_TS_TX_DATA /oracle/visdata/tx_data6.dbf 3 0 10.00 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data60.dbf 3 0 6.67 1.00 2 0 215 0.05
APPS_TS_TX_DATA /oracle/visdata/tx_data61.dbf 3 0 13.33 1.00 1 0 149 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data62.dbf 4 0 12.50 1.00 1 0 459 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data63.dbf 3 0 3.33 1.00 6 0 376 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data64.dbf 3 0 6.67 1.00 2 0 372 0.03
APPS_TS_TX_DATA /oracle/visdata/tx_data65.dbf 4 0 5.00 1.25 1 0 234 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data66.dbf 3 0 0.00 1.00 1 0 144 0.07
APPS_TS_TX_DATA /oracle/visdata/tx_data67.dbf 3 0 13.33 1.00 1 0 115 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data68.dbf 3 0 3.33 1.00 1 0 201 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data69.dbf 4 0 5.00 4.00 1 0 121 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data7.dbf 3 0 0.00 1.00 1 0 29 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data70.dbf 5 0 12.00 3.60 1 0 313 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data71.dbf 5 0 6.00 3.60 1 0 265 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data72.dbf 5 0 10.00 1.00 1 0 387 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data73.dbf 5 0 8.00 1.00 1 0 250 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data74.dbf 6 0 13.33 2.83 1 0 404 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data75.dbf 6 0 10.00 2.83 1 0 337 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data76.dbf 4 0 20.00 1.00 1 0 306 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data77.dbf 4 0 5.00 1.00 1 0 291 0.03
APPS_TS_TX_DATA /oracle/visdata/tx_data78.dbf 4 0 5.00 1.00 1 0 886 0.03
APPS_TS_TX_DATA /oracle/visdata/tx_data8.dbf 4 0 12.50 1.00 1 0 0 0.00
APPS_TS_TX_DATA /oracle/visdata/tx_data9.dbf 4 0 7.50 1.00 1 0 0 0.00
APPS_TS_TX_IDX /oracle/visdata/tx_idx1.dbf 4 0 2.50 1.00 1 0 374 0.03
APPS_TS_TX_IDX /oracle/visdata/tx_idx10.dbf 4 0 2.50 1.00 1 0 321 0.00
APPS_TS_TX_IDX /oracle/visdata/tx_idx11.dbf 4 0 12.50 1.00 1 0 361 0.03
APPS_TS_TX_IDX /oracle/visdata/tx_idx12.dbf 4 0 7.50 1.00 1 0 758 0.00
APPS_TS_TX_IDX /oracle/visdata/tx_idx13.dbf 4 0 7.50 1.00 1 0 852 0.07
APPS_TS_TX_IDX /oracle/visdata/tx_idx14.dbf 4 0 5.00 1.00 1 0 636 0.05
APPS_TS_TX_IDX /oracle/visdata/tx_idx15.dbf 4 0 12.50 1.00 2 0 800 0.04
APPS_TS_TX_IDX /oracle/visdata/tx_idx16.dbf 4 0 5.00 1.00 1 0 860 0.01
APPS_TS_TX_IDX /oracle/visdata/tx_idx17.dbf 4 0 7.50 1.00 1 0 635 0.00
APPS_TS_TX_IDX /oracle/visdata/tx_idx18.dbf 4 0 10.00 1.00 1 0 1,040 0.02
APPS_TS_TX_IDX /oracle/visdata/tx_idx19.dbf 4 0 12.50 1.00 1 0 628 0.00
APPS_TS_TX_IDX /oracle/visdata/tx_idx2.dbf 5 0 6.00 1.00 1 0 586 0.03
APPS_TS_TX_IDX /oracle/visdata/tx_idx20.dbf 5 0 6.00 1.00 1 0 527 0.00
APPS_TS_TX_IDX /oracle/visdata/tx_idx21.dbf 5 0 6.00 1.00 1 0 699 0.04
APPS_TS_TX_IDX /oracle/visdata/tx_idx22.dbf 5 0 4.00 1.00 1 0 771 0.04
APPS_TS_TX_IDX /oracle/visdata/tx_idx23.dbf 5 0 16.00 1.00 1 0 780 0.03
APPS_TS_TX_IDX /oracle/visdata/tx_idx24.dbf 5 0 12.00 1.00 1 0 842 0.02
APPS_TS_TX_IDX /oracle/visdata/tx_idx25.dbf 5 0 18.00 1.00 1 0 1,253 0.03
APPS_TS_TX_IDX /oracle/visdata/tx_idx26.dbf 5 0 8.00 1.00 1 0 1,037 0.00
APPS_TS_TX_IDX /oracle/visdata/tx_idx27.dbf 7 0 4.29 1.00 1 0 963 0.01
APPS_TS_TX_IDX /oracle/visdata/tx_idx28.dbf 5 0 2.00 1.00 1 0 1,465 0.01
APPS_TS_TX_IDX /oracle/visdata/tx_idx29.dbf 17 0 8.24 1.00 16 0 1,129 0.02
APPS_TS_TX_IDX /oracle/visdata/tx_idx3.dbf 5 0 12.00 1.00 3 0 1,746 0.01
APPS_TS_TX_IDX /oracle/visdata/tx_idx30.dbf 5 0 2.00 1.00 1 0 1,077 0.07
APPS_TS_TX_IDX /oracle/visdata/tx_idx31.dbf 5 0 4.00 1.00 1 0 1,306 0.02
APPS_TS_TX_IDX /oracle/visdata/tx_idx32.dbf 5 0 6.00 1.00 1 0 1,379 0.01
APPS_TS_TX_IDX /oracle/visdata/tx_idx33.dbf 5 0 6.00 1.00 1 0 1,366 0.04
APPS_TS_TX_IDX /oracle/visdata/tx_idx34.dbf 5 0 6.00 1.00 1 0 1,769 0.05
APPS_TS_TX_IDX /oracle/visdata/tx_idx35.dbf 5 0 8.00 1.00 1 0 449 0.00
APPS_TS_TX_IDX /oracle/visdata/tx_idx36.dbf 5 0 6.00 1.00 1 0 487 0.06
APPS_TS_TX_IDX /oracle/visdata/tx_idx37.dbf 6 0 8.33 1.00 1 0 290 0.00
APPS_TS_TX_IDX /oracle/visdata/tx_idx38.dbf 6 0 6.67 1.00 1 0 235 0.04
APPS_TS_TX_IDX /oracle/visdata/tx_idx39.dbf 6 0 11.67 1.00 1 0 140 0.00
APPS_TS_TX_IDX /oracle/visdata/tx_idx4.dbf 6 0 6.67 1.00 1 0 299 0.03
APPS_TS_TX_IDX /oracle/visdata/tx_idx40.dbf 6 0 10.00 1.00 1 0 411 0.00
APPS_TS_TX_IDX /oracle/visdata/tx_idx41.dbf 6 0 1.67 1.00 1 0 359 0.03
APPS_TS_TX_IDX /oracle/visdata/tx_idx42.dbf 6 0 5.00 1.00 1 0 873 0.00
APPS_TS_TX_IDX /oracle/visdata/tx_idx43.dbf 6 0 5.00 1.00 1 0 1,270 0.00
APPS_TS_TX_IDX /oracle/visdata/tx_idx44.dbf 6 0 1.67 1.00 1 0 1,168 0.03
APPS_TS_TX_IDX /oracle/visdata/tx_idx45.dbf 6 0 3.33 1.00 1 0 1,395 0.01
APPS_TS_TX_IDX /oracle/visdata/tx_idx46.dbf 6 0 6.67 1.00 1 0 1,223 0.03
APPS_TS_TX_IDX /oracle/visdata/tx_idx47.dbf 6 0 3.33 1.00 1 0 439 0.00
APPS_TS_TX_IDX /oracle/visdata/tx_idx48.dbf 6 0 8.33 1.00 1 0 576 0.05
APPS_TS_TX_IDX /oracle/visdata/tx_idx49.dbf 6 0 5.00 1.00 1 0 700 0.01
APPS_TS_TX_IDX /oracle/visdata/tx_idx5.dbf 6 0 8.33 1.00 1 0 765 0.00
APPS_TS_TX_IDX /oracle/visdata/tx_idx6.dbf 6 0 8.33 1.00 1 0 602 0.02
APPS_TS_TX_IDX /oracle/visdata/tx_idx7.dbf 6 0 5.00 1.00 1 0 967 0.01
APPS_TS_TX_IDX /oracle/visdata/tx_idx8.dbf 6 0 16.67 1.00 1 0 1,116 0.07
APPS_TS_TX_IDX /oracle/visdata/tx_idx9.dbf 6 0 10.00 1.00 1 0 581 0.00
APPS_UNDOTS1 /oracle/visdata/undo.dbf 1 0 0.00 1.00 49 0 16,700 0.01
APPS_UNDOTS1 /oracle/visdata/undo1.dbf 6 0 10.00 1.00 28 0 19,497 0.01
B2B_DT /oracle/visdata/b2b_dt.dbf 6 0 3.33 1.00 1 0 0 0.00
B2B_IDX /oracle/visdata/b2b_idx.dbf 6 0 3.33 1.00 1 0 0 0.00
B2B_LOB /oracle/visdata/b2b_lob.dbf 7 0 14.29 1.00 1 0 0 0.00
B2B_RT /oracle/visdata/b2b_rt.dbf 7 0 8.57 1.00 1 0 0 0.00
BAM /oracle/visdata/bam.dbf 7 0 11.43 1.00 1 0 0 0.00
CTXSYS /oracle/visdata/ctx1.dbf 7 0 10.00 1.00 1 0 0 0.00
CTXSYS /oracle/visdata/ctx2.dbf 7 0 8.57 1.00 1 0 0 0.00
CWMLITE /oracle/visdata/cwmlite01.dbf 7 0 17.14 1.00 1 0 0 0.00
CWMLITE /oracle/visdata/cwmlite02.dbf 6 0 16.67 1.00 1 0 0 0.00
DCM /oracle/visdata/dcm.dbf 6 0 8.33 1.00 1 0 0 0.00
DCM /oracle/visdata/dcm1.dbf 6 0 10.00 1.00 1 0 0 0.00
DISCO_PTM5_CACHE /oracle/visdata/discopltc1.dbf 6 0 16.67 1.00 1 0 0 0.00
DISCO_PTM5_META /oracle/visdata/discopltm1.dbf 6 0 16.67 1.00 1 0 0 0.00
DSGATEWAY_TAB /oracle/visdata/oss_sys01.dbf 6 0 5.00 1.00 1 0 0 0.00
GEOR_TBS /oracle/visdata/apps_ts_spatial.dbf 6 0 11.67 1.00 1 0 0 0.00
GEOR_TBS /oracle/visdata/geor_tbs02.dbf 6 0 26.67 1.00 1 0 0 0.00
HTMLDB /oracle/visdata/htmldb.dbf 6 0 16.67 1.00 1 0 0 0.00
IAS_META /oracle/visdata/ias_meta01.dbf 6 0 30.00 1.00 1 0 0 0.00
MTR /oracle/visdata/opmtr01.dbf 6 0 10.00 1.00 1 0 0 0.00
NOETIX /oracle/visdata/noetix.dbf 6 0 6.67 1.00 1 0 0 0.00
NON_APPS_TS_DATA /oracle/visdata/non_apps_data1.dbf 6 0 13.33 1.00 1 0 0 0.00
OCATS /oracle/visdata/oca.dbf 6 0 5.00 1.00 1 0 0 0.00
ODM_DATA /oracle/visdata/odm.dbf 7 0 10.00 1.00 1 0 0 0.00
OLTS_ATTRSTORE /oracle/visdata/attrs1_oid.dbf 7 0 11.43 1.00 1 0 0 0.00
OLTS_BATTRSTORE /oracle/visdata/battrs1_oid.dbf 7 0 12.86 1.00 1 0 0 0.00
OLTS_CT_STORE /oracle/visdata/gcats1_oid.dbf 7 0 20.00 1.00 1 0 0 0.00
OLTS_DEFAULT /oracle/visdata/gdefault1_oid.dbf 7 0 15.71 1.00 1 0 0 0.00
OLTS_SVRMGSTORE /oracle/visdata/svrmg1_oid.dbf 7 0 12.86 1.00 1 0 0 0.00
OPMOR /oracle/visdata/opmor01.dbf 6 0 10.00 1.00 1 0 0 0.00
ORABPEL /oracle/visdata/orabpel.dbf 6 0 23.33 1.00 1 0 0 0.00
OWAPUB /oracle/visdata/owa1.dbf 6 0 11.67 1.00 1 0 0 0.00
PORTAL /oracle/visdata/portal.dbf 6 0 5.00 1.00 1 0 0 0.00
PORTAL_DOC /oracle/visdata/ptldoc.dbf 6 0 16.67 1.00 1 0 0 0.00
PORTAL_IDX /oracle/visdata/ptlidx.dbf 6 0 10.00 1.00 1 0 0 0.00
PORTAL_LOG /oracle/visdata/ptllog.dbf 6 0 18.33 1.00 1 0 0 0.00
RE /oracle/visdata/opre01.dbf 6 0 10.00 1.00 1 0 0 0.00
SYNCSERVER /oracle/visdata/mobile01.dbf 6 0 10.00 1.00 1 0 0 0.00
SYSAUX /oracle/visdata/sysaux01.dbf 335 1 2.54 1.81 99 0 0 0.00
SYSTEM /oracle/visdata/sys1.dbf 46 0 2.83 1.00 1 0 0 0.00
SYSTEM /oracle/visdata/sys10.dbf 25 0 3.60 1.00 1 0 0 0.00
SYSTEM /oracle/visdata/sys2.dbf 99 0 1.52 1.00 1 0 1 0.00
SYSTEM /oracle/visdata/sys3.dbf 164 1 2.50 1.11 4 0 87 0.00
SYSTEM /oracle/visdata/sys4.dbf 193 1 1.87 1.03 1 0 0 0.00
SYSTEM /oracle/visdata/sys5.dbf 145 1 1.86 1.08 1 0 0 0.00
SYSTEM /oracle/visdata/sys6.dbf 82 0 2.68 1.39 1 0 0 0.00
SYSTEM /oracle/visdata/sys7.dbf 66 0 3.18 1.38 1 0 0 0.00
SYSTEM /oracle/visdata/sys8.dbf 44 0 4.77 1.77 1 0 0 0.00
SYSTEM /oracle/visdata/sys9.dbf 25 0 3.20 1.00 1 0 0 0.00
UDDISYS_TS /oracle/visdata/uddisys01.dbf 6 0 13.33 1.00 1 0 0 0.00
WCRSYS_TS /oracle/visdata/wcrsys01.dbf 6 0 10.00 1.00 1 0 0 0.00
XDB /oracle/visdata/xdb01.dbf 6 0 20.00 1.00 1 0 0 0.00

Back to IO Stats
Back to Top

Buffer Pool Statistics

PNumber of BuffersPool Hit%Buffer GetsPhysical ReadsPhysical WritesFree Buff WaitWrit Comp WaitBuffer Busy Waits
D 2,770,600 100 207,978,478 1,704 590 0 0 99,233


Back to Top

Advisory Statistics

Back to Top

Instance Recovery Stats

Targt MTTR (s) Estd MTTR (s)Recovery Estd IOsActual Redo BlksTarget Redo BlksLog File Size Redo BlksLog Ckpt Timeout Redo BlksLog Ckpt Interval Redo Blks
B 0 14 516 3912 45298440 45298440    
E 0 109 60729 1535267 45298440 45298440    

Back to Advisory Statistics
Back to Top

Buffer Pool Advisory

PSize for Est (M)Size FactorBuffers for EstimateEst Phys Read FactorEstimated Physical Reads
D 2,240 0.10 277,060 1.35 876,338
D 4,480 0.20 554,120 1.00 647,566
D 6,720 0.30 831,180 1.00 647,559
D 8,960 0.40 1,108,240 1.00 647,559
D 11,200 0.50 1,385,300 1.00 647,559
D 13,440 0.60 1,662,360 1.00 647,559
D 15,680 0.70 1,939,420 1.00 647,559
D 17,920 0.80 2,216,480 1.00 647,559
D 20,160 0.90 2,493,540 1.00 647,559
D 22,400 1.00 2,770,600 1.00 647,559
D 24,640 1.10 3,047,660 1.00 647,559
D 26,880 1.20 3,324,720 1.00 647,559
D 29,120 1.30 3,601,780 1.00 647,559
D 31,360 1.40 3,878,840 1.00 647,559
D 33,600 1.50 4,155,900 1.00 647,559
D 35,840 1.60 4,432,960 1.00 647,559
D 38,080 1.70 4,710,020 1.00 647,559
D 40,320 1.80 4,987,080 1.00 647,559
D 42,560 1.90 5,264,140 1.00 647,559
D 44,800 2.00 5,541,200 0.99 643,206

Back to Advisory Statistics
Back to Top

PGA Aggr Summary

PGA Cache Hit %W/A MB ProcessedExtra W/A MB Read/Written
100.00 1,378 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 MemGlobal Mem Bound(K)
B 6,144 5,396 270.83 0.00 0.00 0.00 0.00 629,140
E 6,144 5,394 273.88 0.00 0.00 0.00 0.00 629,140

Back to Advisory Statistics
Back to Top

PGA Aggr Target Histogram

Low Optimal High OptimalTotal ExecsOptimal Execs1-Pass ExecsM-Pass Execs
2K 4K 499,396 499,396 0 0
64K 128K 32 32 0 0
128K 256K 7 7 0 0
256K 512K 25 25 0 0
512K 1024K 273 273 0 0
1M 2M 79 79 0 0
2M 4M 2 2 0 0
4M 8M 2 2 0 0
8M 16M 2 2 0 0

Back to Advisory Statistics
Back to Top

PGA Memory Advisory

PGA Target Est (MB)Size FactrW/A MB ProcessedEstd Extra W/A MB Read/ Written to Disk Estd PGA Cache Hit %Estd PGA Overalloc Count
768 0.13 43,376.50 0.00 100.00 0
1,536 0.25 43,376.50 0.00 100.00 0
3,072 0.50 43,376.50 0.00 100.00 0
4,608 0.75 43,376.50 0.00 100.00 0
6,144 1.00 43,376.50 0.00 100.00 0
7,373 1.20 43,376.50 0.00 100.00 0
8,602 1.40 43,376.50 0.00 100.00 0
9,830 1.60 43,376.50 0.00 100.00 0
11,059 1.80 43,376.50 0.00 100.00 0
12,288 2.00 43,376.50 0.00 100.00 0
18,432 3.00 43,376.50 0.00 100.00 0
24,576 4.00 43,376.50 0.00 100.00 0
36,864 6.00 43,376.50 0.00 100.00 0
49,152 8.00 43,376.50 0.00 100.00 0

Back to Advisory Statistics
Back to Top

Shared Pool Advisory

Shared Pool Size(M)SP Size FactrEst LC Size (M)Est LC Mem ObjEst LC Time Saved (s)Est LC Time Saved FactrEst LC Load Time (s)Est LC Load Time FactrEst LC Mem Obj Hits
608 0.29 236 10,754 103,950 1.00 167 1.21 10,290,370
816 0.40 426 18,540 103,978 1.00 139 1.01 10,294,637
1,024 0.50 499 24,119 103,979 1.00 138 1.00 10,294,732
1,232 0.60 499 24,119 103,979 1.00 138 1.00 10,294,732
1,440 0.70 499 24,119 103,979 1.00 138 1.00 10,294,732
1,648 0.80 499 24,119 103,979 1.00 138 1.00 10,294,732
1,856 0.90 499 24,119 103,979 1.00 138 1.00 10,294,732
2,064 1.00 499 24,119 103,979 1.00 138 1.00 10,294,732
2,272 1.10 499 24,119 103,979 1.00 138 1.00 10,294,732
2,480 1.20 499 24,119 103,979 1.00 138 1.00 10,294,732
2,688 1.30 499 24,119 103,979 1.00 138 1.00 10,294,732
2,896 1.40 499 24,119 103,979 1.00 138 1.00 10,294,732
3,104 1.50 499 24,119 103,979 1.00 138 1.00 10,294,732
3,312 1.60 499 24,119 103,979 1.00 138 1.00 10,294,732
3,520 1.71 499 24,119 103,979 1.00 138 1.00 10,294,732
3,728 1.81 499 24,119 103,979 1.00 138 1.00 10,294,732
3,936 1.91 499 24,119 103,979 1.00 138 1.00 10,294,732
4,144 2.01 499 24,119 103,979 1.00 138 1.00 10,294,732

Back to Advisory Statistics
Back to Top

SGA Target Advisory

SGA Target Size (M)SGA Size FactorEst DB Time (s)Est Physical Reads
6,144 0.25 5,973 647,559
12,288 0.50 5,973 647,559
18,432 0.75 5,973 647,559
24,576 1.00 5,973 647,559
30,720 1.25 5,973 647,559
36,864 1.50 5,973 647,559
43,008 1.75 5,973 647,559
49,152 2.00 5,963 643,220

Back to Advisory Statistics
Back to Top

Streams Pool Advisory

Size for Est (MB)Size FactorEst Spill CountEst Spill Time (s)Est Unspill CountEst Unspill Time (s)
16 0.50 0 0 0 0
32 1.00 0 0 0 0
48 1.50 0 0 0 0
64 2.00 0 0 0 0
80 2.50 0 0 0 0
96 3.00 0 0 0 0
112 3.50 0 0 0 0
128 4.00 0 0 0 0
144 4.50 0 0 0 0
160 5.00 0 0 0 0
176 5.50 0 0 0 0
192 6.00 0 0 0 0
208 6.50 0 0 0 0
224 7.00 0 0 0 0
240 7.50 0 0 0 0
256 8.00 0 0 0 0
272 8.50 0 0 0 0
288 9.00 0 0 0 0
304 9.50 0 0 0 0
320 10.00 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

ClassWaitsTotal Wait Time (s)Avg Time (ms)
data block 62,040 1 0
undo block 33,357 0 0
1st level bmb 605 0 0
undo header 2,843 0 0
segment header 368 0 0
2nd level bmb 8 0 0
extent map 7 0 0

Back to Wait Statistics
Back to Top

Enqueue Activity

Enqueue Type (Request Reason)RequestsSucc GetsFailed GetsWaitsWt Time (s)Av Wt Time(ms)
UL-User-defined 3,536 3,442 94 594 2 4.19
TX-Transaction (index contention) 3,612 3,612 0 3,604 1 0.21
TX-Transaction (row lock contention) 823 442 381 440 1 1.34
TX-Transaction 25,506 25,506 0 299 0 0.80
HW-Segment High Water Mark 3,145 3,145 0 639 0 0.33
FB-Format Block 766 766 0 53 0 1.51
SQ-Sequence Cache 721 721 0 320 0 0.06
TX-Transaction (allocate ITL entry) 4 4 0 4 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 TransactionsMax Qry Len (s)Max Tx ConcurcyMin/Max TR (mins)STO/ OOS uS/uR/uU/ eS/eR/eU
1 34.42 19,556 1,170 28 789.2/789.2 0/0 0/0/0/0/0/0

Back to Undo Statistics
Back to Top

Undo Segment Stats

End TimeNum Undo BlocksNumber of TransactionsMax Qry Len (s)Max Tx ConcyTun Ret (mins)STO/ OOS uS/uR/uU/ eS/eR/eU
09-Feb 10:47 34,422 19,556 1,170 28 789 0/0 0/0/0/0/0/0

Back to Undo Statistics
Back to Top

Latch Statistics

Back to Top

Latch Activity

Latch NameGet RequestsPct Get MissAvg Slps /MissWait Time (s)NoWait RequestsPct NoWait Miss
ASM db client latch 569 0.00   0 0  
AWR Alerted Metric Element list 3,582 0.00   0 0  
Consistent RBA 13,983 0.11 0.00 0 0  
FOB s.o list latch 3,332 0.06 0.00 0 0  
In memory undo latch 81,603,096 4.26 0.00 0 17,185 0.03
JS queue state obj latch 1,590 0.00   0 0  
JS slv state obj latch 16 0.00   0 0  
KMG MMAN ready and startup request latch 88 0.00   0 0  
KTF sga latch 1 0.00   0 78 0.00
KWQMN job cache list latch 1 0.00   0 0  
KWQP Prop Status 44 0.00   0 0  
MQL Tracking Latch 0     0 6 0.00
Memory Management Latch 0     0 88 0.00
OS process 639 0.00   0 0  
OS process allocation 515 0.00   0 0  
OS process: request allocation 395 0.25 0.00 0 0  
PL/SQL warning settings 259,075 0.00   0 0  
SGA IO buffer pool latch 63 0.00   0 63 0.00
SQL memory manager latch 6 0.00   0 88 0.00
SQL memory manager workarea list latch 74,136 0.04 0.00 0 0  
Shared B-Tree 218 0.00   0 0  
active checkpoint queue latch 374 0.00   0 0  
active service list 1,322 0.15 0.00 0 118 0.00
archive control 46 0.00   0 0  
begin backup scn array 81 0.00   0 0  
bq:time manger info latch 10 0.00   0 0  
bufq statistics 18 0.00   0 0  
cache buffer handles 12,229,061 5.93 0.00 0 0  
cache buffers chains 453,332,185 7.05 0.00 3 701,719 16.01
cache buffers lru chain 30,807 0.27 0.00 0 1,616,936 0.65
cache table scan latch 42 0.00   0 42 0.00
channel handle pool latch 765 0.39 0.00 0 0  
channel operations parent latch 6,735 0.56 0.00 0 0  
checkpoint queue latch 35,555 0.00   0 60,795 0.00
client/application info 2,878 0.00   0 0  
compile environment latch 21,594 0.00   0 0  
dml lock allocation 125,712 0.32 0.00 0 0  
dummy allocation 411 0.24 0.00 0 0  
enqueue hash chains 228,791 0.68 0.01 0 3 0.00
enqueues 61,452 0.91 0.00 0 0  
event group latch 198 0.00   0 0  
file cache latch 2,869 0.00   0 0  
first spare latch 217 0.00   0 0  
global KZLD latch for mem in SGA 189 0.00   0 0  
global ctx hash table latch 384 0.00   0 0  
hash table column usage latch 81 0.00   0 71,279 0.00
hash table modification latch 68 0.00   0 0  
job workq parent latch 0     0 14 0.00
job_queue_processes parameter latch 10 0.00   0 0  
kks stats 1,941 0.00   0 0  
ksuosstats global area 30 0.00   0 0  
ktm global data 371 0.00   0 0  
kwqbsn:qsga 184 0.00   0 0  
kwqbsn:qxl 1 0.00   0 0  
lgwr LWN SCN 14,018 0.14 0.00 0 0  
library cache 71,248,742 5.71 0.00 0 528 0.00
library cache load lock 1,946 0.00   0 0  
library cache lock 10,138,634 3.03 0.00 0 0  
library cache lock allocation 18,252 0.01 0.00 0 0  
library cache pin 42,999,168 3.04 0.00 0 4 0.00
library cache pin allocation 9,131 0.04 0.00 0 0  
list of block allocation 12,080 0.03 0.00 0 0  
loader state object freelist 740 0.95 0.00 0 0  
logminer context allocation 6 0.00   0 0  
longop free list parent 124 0.00   0 125 0.00
message pool operations parent latch 738 1.08 0.00 0 0  
messages 45,581 0.04 0.00 0 0  
mostly latch-free SCN 14,996 5.57 0.00 0 0  
multiblock read objects 228 0.00   0 0  
ncodef allocation latch 14 0.00   0 0  
object queue header heap 3,483 0.00   0 1,484 0.00
object queue header operation 194,232 0.01 0.00 0 0  
object stats modification 733 0.55 0.00 0 0  
parallel query alloc buffer 32 0.00   0 0  
parameter list 593 0.17 0.00 0 0  
parameter table allocation management 229 0.00   0 0  
post/wait queue 17,848 0.04 0.00 0 8,529 0.04
process allocation 395 2.28 0.56 0 195 1.54
process group creation 395 0.00   0 0  
qmn task queue latch 322 23.60 0.01 0 0  
redo allocation 84,277 1.57 0.03 0 2,565,103 1.38
redo copy 0     0 2,567,406 0.07
redo writing 48,184 0.00   0 0  
resmgr group change latch 1,157 0.00   0 0  
resmgr:actses active list 601 0.83 0.00 0 0  
resmgr:actses change group 574 0.00   0 0  
resmgr:free threads list 407 0.49 0.00 0 0  
resmgr:schema config 6 0.00   0 0  
row cache objects 787,715 1.17 0.00 0 0  
rules engine rule set statistics 600 0.00   0 0  
sequence cache 1,173,669 2.78 0.00 0 0  
session allocation 6,559,068 15.91 0.00 0 0  
session idle bit 2,000,915 0.03 0.00 0 0  
session state list latch 1,178 0.85 0.20 0 0  
session switching 14 0.00   0 0  
session timer 118 0.00   0 0  
shared pool 243,090 0.07 0.01 0 0  
shared pool sim alloc 56 0.00   0 0  
shared pool simulator 47,790 0.00 0.00 0 0  
simulator hash latch 14,927,737 0.01 0.00 0 0  
simulator lru latch 14,916,330 5.65 0.00 0 3,559 0.00
slave class 2 0.00   0 0  
slave class create 8 0.00   0 0  
sort extent pool 2,600 0.96 0.00 0 0  
state object free list 14 0.00   0 0  
statistics aggregation 504 0.00   0 0  
temp lob duration state obj allocation 2 0.00   0 0  
temporary table state object allocation 368 0.54 0.00 0 0  
threshold alerts latch 239 0.00   0 0  
transaction allocation 1,838 0.00   0 0  
transaction branch allocation 14 0.00   0 0  
undo global data 90,088,996 1.08 0.00 0 204 0.00
user lock 772 0.39 0.00 0 0  

Back to Latch Statistics
Back to Top

Latch Sleep Breakdown

Latch NameGet RequestsMissesSleepsSpin GetsSleep1Sleep2Sleep3
cache buffers chains 453,332,185 31,946,422 93,867 31,848,061 0 0 0
library cache 71,248,742 4,069,806 1,327 4,068,805 0 0 0
In memory undo latch 81,603,096 3,473,263 6,620 3,468,076 0 0 0
library cache pin 42,999,168 1,305,098 531 1,304,691 0 0 0
session allocation 6,559,068 1,043,268 845 1,042,635 0 0 0
undo global data 90,088,996 970,385 682 969,901 0 0 0
simulator lru latch 14,916,330 842,977 882 842,333 0 0 0
cache buffer handles 12,229,061 724,989 322 724,752 0 0 0
library cache lock 10,138,634 306,851 30 306,829 0 0 0
sequence cache 1,173,669 32,605 23 32,590 0 0 0
simulator hash latch 14,927,737 2,088 4 2,085 0 0 0
enqueue hash chains 228,791 1,562 10 1,554 0 0 0
redo allocation 84,277 1,320 36 1,292 0 0 0
shared pool 243,090 162 1 161 0 0 0
qmn task queue latch 322 76 1 75 0 0 0
session state list latch 1,178 10 2 8 0 0 0
process allocation 395 9 5 4 0 0 0

Back to Latch Statistics
Back to Top

Latch Miss Sources

Latch NameWhereNoWait Misses SleepsWaiter Sleeps
In memory undo latch kturbk 0 3,894 5,045
In memory undo latch ktiFlush: child 0 1,678 556
In memory undo latch kticmt: child 0 611 6
In memory undo latch ktichg: child 0 231 958
In memory undo latch ktiTxnPoolFree 0 200 2
In memory undo latch ktbgfc 0 6 53
active service list ksws_event: ksws event 0 2 0
cache buffer handles kcbzgs 0 221 205
cache buffer handles kcbzfs 0 101 117
cache buffers chains kcbchg: kslbegin: bufs not pinned 0 339,264 338,421
cache buffers chains kcbgtcr: fast path 0 104,820 5,129
cache buffers chains kcbgtcr: kslbegin excl 0 47,637 19,210
cache buffers chains kcbchg: kslbegin: call CR func 0 36,641 163,220
cache buffers chains kcbrls: kslbegin 0 4,309 13,911
cache buffers chains kcbgcur: kslbegin 0 4,063 1,244
cache buffers chains kcbzgb: scan from tail. nowait 0 3,681 0
cache buffers chains kcbget: pin buffer 0 2,717 556
cache buffers chains kcbzwb 0 2,030 1,003
cache buffers chains kcbcge 0 344 3,271
cache buffers chains kcb_is_private 0 173 592
cache buffers chains kcbnlc 0 68 265
cache buffers chains kcbget: exchange rls 0 31 17
cache buffers chains kcb_post_apply: kcbhq62 0 22 109
cache buffers chains kcb_pre_apply: kcbhq61 0 18 527
cache buffers chains kcbget: exchange 0 18 22
cache buffers chains kcbesc: escalate 0 9 3
cache buffers chains kcbso1: set no access 0 2 0
enqueue hash chains ksqgtl3 0 4 5
enqueue hash chains ksqcmi: kslgpl 0 2 0
enqueue hash chains ksqrcl 0 2 0
enqueue hash chains ksqcmi: get hash chain latch after wait 0 1 3
enqueue hash chains ksqcnl 0 1 2
library cache kglpndl: child: after processing 0 370 41
library cache kglpnc: child 0 258 394
library cache kglpndl: child: before processing 0 235 490
library cache kglpin: child: heap processing 0 187 95
library cache kglpnp: child 0 168 198
library cache kglhdgc: child: 0 57 65
library cache kglobpn: child: 0 29 35
library cache kglpin 0 7 0
library cache kglhdgn: child: 0 3 2
library cache kglLockCursor 0 1 1
library cache kgldti: 2child 0 1 0
library cache lock kgllkdl: child: cleanup 0 18 6
library cache lock kgllkal: child: multiinstance 0 12 24
library cache lock kgllkdl: child: no lock handle 0 11 3
library cache pin kglpndl 0 237 227
library cache pin kglpnc: child 0 219 220
library cache pin kglpnp: child 0 53 46
library cache pin kglpnal: child: alloc space 0 22 38
process allocation ksucrp 0 5 0
redo allocation kcrfw_redo_gen: redo allocation 1 0 28 0
redo allocation kcrfw_redo_gen: redo allocation 3 0 6 26
redo allocation kcrfw_redo_gen: redo allocation 2 0 2 0
sequence cache kdnnxt: cached seq 0 12 2
sequence cache kdnssd 0 11 0
session allocation ksuprc 0 473 404
session allocation ksudlc 0 366 436
session allocation ksuxds: not user session 0 3 2
session allocation ksucri 0 2 2
session state list latch kpseqd 0 2 0
shared pool kghalo 0 1 1
simulator hash latch kcbsacc: lookup dba 0 4 4
simulator lru latch kcbs_simulate: simulate set 0 882 882
undo global data kturimugur: child 0 576 259
undo global data ktudba: KSLBEGIN 0 105 423
undo global data ktufrbs: child 0 1 0

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

OwnerTablespace NameObject NameSubobject NameObj. TypeLogical Reads%Total
HR APPS_TS_TX_IDX PAY_ASSIGNMENT_ACTIONS_PK   INDEX 24,232,848 11.54
HR APPS_TS_TX_IDX PAY_ACTION_INTERLOCKS_FK2   INDEX 16,018,512 7.63
HR APPS_TS_TX_IDX PAY_PAYROLL_ACTIONS_PK   INDEX 15,412,976 7.34
HR APPS_TS_TX_DATA PAY_ASSIGNMENT_ACTIONS   TABLE 11,358,304 5.41
HR APPS_TS_TX_DATA PAY_PAYROLL_ACTIONS   TABLE 9,970,896 4.75

Back to Segment Statistics
Back to Top

Segments by Physical Reads

OwnerTablespace NameObject NameSubobject NameObj. TypePhysical Reads%Total
APPLSYS APPS_TS_TX_DATA FND_CONCURRENT_REQUESTS   TABLE 89 3.71
SYS SYSAUX WRH$_SQLSTAT 10556022_0 TABLE PARTITION 20 0.83
SYS SYSAUX WRI$_OPTSTAT_HISTHEAD_HISTORY   TABLE 17 0.71
SYS SYSAUX WRH$_LATCH 10556022_0 TABLE PARTITION 13 0.54
SYS SYSAUX WRH$_FILESTATXS 10556022_0 TABLE PARTITION 12 0.50

Back to Segment Statistics
Back to Top

Segments by Row Lock Waits

OwnerTablespace NameObject NameSubobject NameObj. TypeRow Lock Waits% of Capture
APPLSYS APPS_TS_TX_DATA FND_CONCURRENT_REQUESTS   TABLE 1,152 21.54
HR APPS_TS_TX_IDX PAY_ACTION_INTERLOCKS_PK   INDEX 611 11.42
HR APPS_TS_TX_DATA PAY_PAYROLL_ACTIONS   TABLE 326 6.09
HR APPS_TS_TX_IDX PAY_RUN_BALANCES_N2   INDEX 265 4.95
HR APPS_TS_TX_IDX PAY_ASSIGNMENT_ACTIONS_N50   INDEX 236 4.41

Back to Segment Statistics
Back to Top

Segments by ITL Waits

OwnerTablespace NameObject NameSubobject NameObj. TypeITL Waits% of Capture
HR APPS_TS_TX_IDX PAY_RUN_BALANCES_N1   INDEX 3 75.00
HR APPS_TS_TX_IDX PAY_ACTION_INFORMATION_N2   INDEX 1 25.00

Back to Segment Statistics
Back to Top

Segments by Buffer Busy Waits

OwnerTablespace NameObject NameSubobject NameObj. TypeBuffer Busy Waits% of Capture
HR APPS_TS_TX_DATA PAY_ASSIGNMENT_ACTIONS   TABLE 7,757 12.31
HR APPS_TS_TX_DATA PAY_POPULATION_RANGES   TABLE 5,119 8.13
HR APPS_TS_TX_IDX PAY_ASSIGNMENT_ACTIONS_PK   INDEX 4,495 7.14
HR APPS_TS_TX_IDX PAY_ACTION_INTERLOCKS_PK   INDEX 4,494 7.13
HR APPS_TS_TX_IDX PAY_ACTION_INFORMATION_PK   INDEX 4,448 7.06

Back to Segment Statistics
Back to Top

Dictionary Cache Stats

CacheGet RequestsPct MissScan ReqsPct MissMod ReqsFinal Usage
dc_awr_control 30 0.00 0   13 1
dc_files 518 0.00 0   0 259
dc_global_oids 366 2.19 0   0 75
dc_histogram_data 2,405 1.83 0   0 2,055
dc_histogram_defs 21,124 17.50 0   1,618 40,862
dc_object_grants 626 2.56 0   0 167
dc_object_ids 94,994 0.14 0   0 6,873
dc_objects 5,139 9.07 0   1 9,549
dc_profiles 197 0.00 0   0 1
dc_rollback_segments 237 0.00 0   0 50
dc_segments 18,107 0.55 0   711 6,341
dc_sequences 401 0.50 0   401 29
dc_tablespace_quotas 672 0.00 0   0 10
dc_tablespaces 53,551 0.00 0   0 57
dc_usernames 1,780 0.17 0   0 48
dc_users 62,358 0.00 0   0 81
outstanding_alerts 124 0.00 0   0 109


Back to Top

Library Cache Activity

NamespaceGet RequestsPct MissPin RequestsPct MissReloadsInvali- dations
BODY 3,823 0.13 1,675,785 -0.06 0 0
CLUSTER 33 0.00 52 0.00 0 0
INDEX 125 68.80 847 17.83 0 0
PIPE 36 0.00 36 0.00 0 0
SQL AREA 474 94.51 17,391,314 -0.24 483 0
TABLE/PROCEDURE 6,234 2.12 2,099,423 0.03 223 0
TRIGGER 323 0.00 217,349 -0.06 0 0


Back to Top

Memory Statistics

Back to Top

Process Memory Summary

CategoryAlloc (MB)Used (MB)Avg Alloc (MB)Std Dev Alloc (MB)Max Alloc (MB)Hist Max Alloc (MB)Num ProcNum Alloc
B Other 192.50   1.89 2.16 22 31 102 102
  Freeable 60.75 0.00 0.86 1.90 10   71 71
  SQL 10.88 5.37 0.11 0.16 1 3 95 93
  PL/SQL 6.74 2.65 0.07 0.04 0 4 102 102
E Other 198.03   1.92 2.14 22 31 103 103
  Freeable 58.38 0.00 1.10 2.14 10   53 53
  SQL 10.66 5.03 0.11 0.14 1 3 96 93
  PL/SQL 6.85 2.72 0.07 0.04 0 0 103 103

Back to Memory Statistics
Back to Top

SGA Memory Summary

SGA regionsBegin Size (Bytes)End Size (Bytes) (if different)
Database Buffers 23,488,102,400  
Fixed Size 2,119,248  
Redo Buffers 64,987,136  
Variable Size 2,214,594,992  

Back to Memory Statistics
Back to Top

SGA breakdown difference

PoolNameBegin MBEnd MB% Diff
java free memory 16.00 16.00 0.00
large PX msg pool 1.03 1.03 0.00
large free memory 14.97 14.97 0.00
shared ASH buffers 40.50 40.50 0.00
shared CCursor   21.34  
shared KGLS heap 40.20 40.72 1.31
shared KQR M PO 26.40 26.75 1.31
shared db_block_hash_buckets 180.00 180.00 0.00
shared free memory 1,464.32 1,420.87 -2.97
shared library cache 36.15 38.00 5.11
shared private strands 22.17 22.17 0.00
shared sql area 39.23 72.13 83.87
streams free memory 31.99 31.99 -0.00
  buffer_cache 22,400.00 22,400.00 0.00
  fixed_sga 2.02 2.02 0.00
  log_buffer 61.98 61.98 0.00

Back to Memory Statistics
Back to Top

Streams Statistics

Back to Top

Streams CPU/IO Usage

Session TypeCPU TimeUser I/O TimeSys I/O Time
QMON Slaves 7,607 0 0
QMON Coordinator 4,835 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 NameIncoming per secondOutgoing per secondSpilled per secondDiff Pct Spilled
APPLSYS.WF_CONTROL 0 0 0 0

Back to Streams Statistics
Back to Top

Buffered Subscribers

Subscriber NameIncoming per secondOutgoing per secondSpilled per second
WFBESCL_20643 0 0 0

Back to Streams Statistics
Back to Top

Rule Set

Ruleset NameEvalsFast EvalsSQL ExecsCPU TimeElapsed 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 NameBegin valueEnd value (if different)
_b_tree_bitmap_plans FALSE   
_fast_full_scan_enabled FALSE   
_gby_hash_aggregation_enabled FALSE   
_kks_use_mutex_pin FALSE   
_like_with_bind_as_equality TRUE   
_sort_elimination_cost_ratio 5   
_sqlexec_progression_cost 2147483647   
_system_trig_enabled TRUE   
_trace_files_public TRUE   
background_dump_dest /oracle/db/tech_st/10.2.0/admin/VIS_db1/bdump   
compatible 10.2.0.3   
control_files /raw_links/cntrl01.dbf, /raw_links/cntrl02.dbf, /raw_links/cntrl03.dbf   
core_dump_dest /oracle/db/tech_st/10.2.0/admin/VIS_db1/cdump   
cursor_sharing EXACT   
db_block_checking FALSE   
db_block_checksum TRUE   
db_block_size 8192   
db_files 512   
db_name VIS   
dml_locks 10000   
ifile /oracle/db/tech_st/10.2.0/dbs/VIS_db1_ifile.ora   
job_queue_processes 2   
log_buffer 63016960   
log_checkpoint_interval 0   
log_checkpoint_timeout 0   
log_checkpoints_to_alert TRUE   
max_dump_file_size 20480   
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_index_caching 0   
optimizer_index_cost_adj 50   
optimizer_secure_view_merging FALSE   
parallel_max_servers 24   
parallel_min_servers 0   
pga_aggregate_target 6442450944   
plsql_code_type INTERPRETED   
plsql_native_library_dir /oracle/db/tech_st/10.2.0/plsql/nativelib   
plsql_native_library_subdir_count 149   
plsql_optimize_level 2   
processes 800   
session_cached_cursors 500   
sessions 1600   
sga_target 25769803776   
shared_pool_reserved_size 104857600   
shared_pool_size 2147483648   
timed_statistics TRUE   
undo_management AUTO   
undo_retention 36000   
undo_tablespace APPS_UNDOTS1   
user_dump_dest /oracle/db/tech_st/10.2.0/admin/VIS_db1/udump   
utl_file_dir /usr/tmp, /usr/tmp, /oracle/db/tech_st/10.2.0/appsutil/outbound/VIS_db1, /usr/tmp   
workarea_size_policy AUTO   


Back to Top

End of Report