Worldwide Secure Search All Blogs CRM On Demand Discussion Forums Documentation Education Oracle.com PartnerNetwork Technology Network Video and Multimedia Home Products and Services Industries Support Store Partners Communities About

TECHNOLOGY PRODUCTS APPLICATION PRODUCTS FUSION APPLICATIONS ORACLE SERVICES CUSTOMERS | PARTNERS ORACLE CORPORATION Legal | Privacy Worldwide Sites Africa Operation Argentina Australia Austria Bahrain Bangladesh Belgium & Luxembourg Belize Bhutan Bolivia Bosnia & Herzegovina Brasil Brunei Bulgaria Cambodia Canada - English Canada - French Chile China Colombia Costa Rica Croatia Czech Republic Denmark Ecuador Egypt Estonia Finland France Germany Greece Guatemala Honduras Hong Kong Hungary India Indonesia Iraq Ireland Israel Italy Japan Jordan Kazakhstan Korea Kuwait Laos Latvia Lebanon Lithuania Malaysia Maldives Malta Mexico Moldova Nepal Netherlands New Zealand Nicaragua Norway Oman Pakistan Panama Paraguay Peru Philippines Poland Portugal Puerto Rico Qatar Romania Russia Saudi Arabia Serbia & Montenegro Singapore Slovakia Slovenia South Africa Spain Sri Lanka Sweden Switzerland -- French Switzerland -- German Taiwan Thailand Turkey Ukraine United Arab Emirates United Kingdom United States Uraguay Venezuela Vietnam Yemen

WORKLOAD REPOSITORY report for DB Name DB Id Instance Inst num Release RAC Host VIS 220594996 VIS 1 10.2.0.3.0 NO r12db.sanmateo.ibm.com Snap Id Snap Time Sessions Cursors/Session Begin Snap: 995 26-Mar-09 21:37:43 169 35.5 End Snap: 996 26-Mar-09 23:00:35 169 34.0 Elapsed:   82.86 (mins)     DB Time:   555.32 (mins)     Report Summary Cache Sizes Begin End Buffer Cache: 8,128M 8,128M Std Block Size: 8K Shared Pool Size: 2,000M 2,000M Log Buffer: 30,724K Load Profile Per Second Per Transaction Redo size: 3,572,179.06 49,710.54 Logical reads: 656,497.44 9,135.84 Block changes: 24,544.35 341.56 Physical reads: 142.01 1.98 Physical writes: 342.73 4.77 User calls: 4,675.01 65.06 Parses: 3,703.04 51.53 Hard parses: 0.10 0.00 Sorts: 3,530.97 49.14 Logons: 0.05 0.00 Executes: 42,713.87 594.41 Transactions: 71.86   % Blocks changed per Read: 3.74 Recursive Call %: 95.08 Rollback per transaction %: 1.43 Rows per Sort: 1.89 Instance Efficiency Percentages (Target 100%) Buffer Nowait %: 99.94 Redo NoWait %: 100.00 Buffer Hit %: 99.98 In-memory Sort %: 100.00 Library Hit %: 100.01 Soft Parse %: 100.00 Execute to Parse %: 91.33 Latch Hit %: 96.42 Parse CPU to Parse Elapsd %: 87.49 % Non-Parse CPU: 99.14 Shared Pool Statistics Begin End Memory Usage %: 21.88 23.01 % SQL with executions>1: 87.33 94.42 % Memory for SQL w/exec>1: 89.11 90.77 Top 5 Timed Events Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class CPU time   28,533   85.6   enq: UL - contention 56,040 2,896 52 8.7 Application log file sync 151,429 788 5 2.4 Commit latch: cache buffers chains 13,355,671 764 0 2.3 Concurrency wait list latch free 12,617 136 11 .4 Other Main Report Report Summary Wait Events Statistics SQL Statistics Instance Activity Statistics IO Stats Buffer Pool Statistics Advisory Statistics Wait Statistics Undo Statistics Latch Statistics Segment Statistics Dictionary Cache Statistics Library Cache Statistics Memory Statistics Streams Statistics Resource Limit Statistics init.ora Parameters Back to Top Wait Events Statistics Time Model Statistics Wait Class Wait Events Background Wait Events Operating System Statistics Service Statistics Service Wait Class Stats Back to Top Time Model Statistics Total time in database user-calls (DB Time): 33319.3s Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic Ordered by % or DB time desc, Statistic name Statistic Name Time (s) % of DB Time sql execute elapsed time 32,165.32 96.54 DB CPU 28,532.91 85.63 PL/SQL execution elapsed time 10,635.94 31.92 parse time elapsed 342.26 1.03 repeated bind elapsed time 17.03 0.05 inbound PL/SQL rpc elapsed time 10.93 0.03 sequence load elapsed time 3.99 0.01 hard parse elapsed time 2.17 0.01 connection management call elapsed time 0.47 0.00 PL/SQL compilation elapsed time 0.27 0.00 hard parse (sharing criteria) elapsed time 0.06 0.00 Java execution elapsed time 0.05 0.00 hard parse (bind mismatch) elapsed time 0.02 0.00 DB time 33,319.31   background elapsed time 1,405.05   background cpu time 190.48   Back to Wait Events Statistics Back to Top Wait Class s - second cs - centisecond - 100th of a second ms - millisecond - 1000th of a second us - microsecond - 1000000th of a second ordered by wait time desc, waits desc Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn Application 62,846 0.12 2,898 46 0.18 Concurrency 15,456,865 0.02 987 0 43.26 Commit 151,429 0.00 788 5 0.42 Other 267,957 63.17 156 1 0.75 User I/O 710,167 0.00 51 0 1.99 Network 22,536,879 0.00 21 0 63.08 System I/O 287,277 0.00 20 0 0.80 Configuration 8,985 0.00 2 0 0.03 Back to Wait Events Statistics Back to Top Wait Events s - second cs - centisecond - 100th of a second ms - millisecond - 1000th of a second us - microsecond - 1000000th of a second ordered by wait time desc, waits desc (idle events last) Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn enq: UL - contention 56,040 0.13 2,896 52 0.16 log file sync 151,429 0.00 788 5 0.42 latch: cache buffers chains 13,355,671 0.00 764 0 37.38 wait list latch free 12,617 0.00 136 11 0.04 buffer busy waits 1,838,962 0.00 120 0 5.15 enq: TX - index contention 118,143 0.00 47 0 0.33 db file sequential read 703,385 0.00 46 0 1.97 cursor: pin S wait on X 4,708 65.31 33 7 0.01 control file parallel write 2,233 0.00 18 8 0.01 SQL*Net message to client 21,811,957 0.00 18 0 61.05 latch: In memory undo latch 121,687 0.00 17 0 0.34 read by other session 5,320 0.00 5 1 0.01 Streams AQ: qmn coordinator waiting for slave to start 1 100.00 5 4884 0.00 latch free 21,358 0.00 5 0 0.06 SQL*Net more data from client 722,670 0.00 4 0 2.02 buffer deadlock 174,048 97.22 3 0 0.49 kksfbc child completion 70 75.71 3 38 0.00 enq: TX - row lock contention 5,160 0.00 2 0 0.01 latch: library cache 10,457 0.00 2 0 0.03 os thread startup 55 0.00 2 35 0.00 latch: cache buffer handles 7,507 0.00 2 0 0.02 log file parallel write 216,671 0.00 1 0 0.61 enq: SQ - contention 8,852 0.00 1 0 0.02 latch: undo global data 5,952 0.00 1 0 0.02 latch: library cache pin 5,048 0.00 1 0 0.01 db file parallel write 67,679 0.00 1 0 0.19 log file switch completion 29 0.00 0 16 0.00 SQL*Net break/reset to client 1,646 0.00 0 0 0.00 latch: session allocation 1,501 0.00 0 0 0.00 LGWR wait for redo copy 6,012 0.00 0 0 0.02 rdbms ipc reply 521 0.00 0 1 0.00 latch: library cache lock 1,638 0.00 0 0 0.00 latch: redo allocation 677 0.00 0 0 0.00 enq: CU - contention 6 0.00 0 24 0.00 cursor: pin S 37,257 0.00 0 0 0.10 latch: row cache objects 470 0.00 0 0 0.00 latch: cache buffers lru chain 262 0.00 0 0 0.00 SQL*Net more data to client 2,252 0.00 0 0 0.01 latch: object queue header operation 46 0.00 0 1 0.00 enq: HW - contention 76 0.00 0 0 0.00 latch: enqueue hash chains 86 0.00 0 0 0.00 control file sequential read 690 0.00 0 0 0.00 db file scattered read 35 0.00 0 0 0.00 cursor: pin X 2 50.00 0 5 0.00 latch: checkpoint queue latch 26 0.00 0 0 0.00 latch: shared pool 12 0.00 0 0 0.00 enq: TX - allocate ITL entry 25 0.00 0 0 0.00 direct path read 996 0.00 0 0 0.00 latch: messages 8 0.00 0 0 0.00 log file single write 2 0.00 0 0 0.00 direct path write temp 109 0.00 0 0 0.00 cursor: mutex X 14 0.00 0 0 0.00 direct path read temp 109 0.00 0 0 0.00 direct path write 213 0.00 0 0 0.00 latch: redo writing 2 0.00 0 0 0.00 log file sequential read 2 0.00 0 0 0.00 undo segment extension 1 0.00 0 0 0.00 SQL*Net message from client 21,811,947 0.00 282,357 13 61.05 Streams AQ: waiting for messages in the queue 11,511 98.19 98,068 8519 0.03 pipe get 5,951 99.33 24,264 4077 0.02 PL/SQL lock timer 2,925 99.76 23,069 7887 0.01 Streams AQ: qmn slave idle wait 1,369 0.00 5,347 3906 0.00 Streams AQ: qmn coordinator idle wait 2,268 34.08 4,848 2138 0.01 Streams AQ: waiting for time management or cleanup tasks 3 100.00 2,189 729684 0.00 jobq slave wait 595 97.14 1,732 2911 0.00 class slave wait 2 0.00 0 0 0.00 Back to Wait Events Statistics Back to Top Background Wait Events ordered by wait time desc, waits desc (idle events last) Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn control file parallel write 2,233 0.00 18 8 0.01 events in waitclass Other 6,700 0.01 6 1 0.02 os thread startup 55 0.00 2 35 0.00 log file parallel write 216,671 0.00 1 0 0.61 db file parallel write 67,679 0.00 1 0 0.19 db file sequential read 71 0.00 0 2 0.00 latch: cache buffers chains 29 0.00 0 1 0.00 control file sequential read 656 0.00 0 0 0.00 buffer busy waits 4 0.00 0 1 0.00 direct path read 996 0.00 0 0 0.00 log file single write 2 0.00 0 0 0.00 direct path write 213 0.00 0 0 0.00 latch: redo writing 2 0.00 0 0 0.00 log file sequential read 2 0.00 0 0 0.00 rdbms ipc message 445,045 3.71 46,325 104 1.25 Streams AQ: qmn slave idle wait 1,369 0.00 5,347 3906 0.00 pmon timer 2,024 83.55 4,851 2397 0.01 Streams AQ: qmn coordinator idle wait 2,268 34.08 4,848 2138 0.01 smon timer 243 2.88 4,591 18892 0.00 Streams AQ: waiting for time management or cleanup tasks 3 100.00 2,189 729684 0.00 class slave wait 2 0.00 0 0 0.00 Back to Wait Events Statistics Back to Top Operating System Statistics Statistic Total BUSY_TIME 3,162,388 IDLE_TIME 2,803,087 IOWAIT_TIME 61,010 NICE_TIME 49 SYS_TIME 189,145 USER_TIME 2,944,247 LOAD 0 RSRC_MGR_CPU_WAIT_TIME 0 PHYSICAL_MEMORY_BYTES 339,052 NUM_CPUS 12 NUM_CPU_SOCKETS 4 Back to Wait Events Statistics Back to Top Service Statistics ordered by DB Time Service Name DB Time (s) DB CPU (s) Physical Reads Logical Reads VIS 33,317.50 28,531.60 704,829 3,263,847,163 SYS$USERS 1.80 1.30 119 11,483 SYS$BACKGROUND 0.00 0.00 1,051 83,319 Back to Wait Events Statistics Back to Top Service Wait Class Stats Wait Class info for services in the Service Statistics section. Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network Time Waited (Wt Time) in centisecond (100th of a second) Service Name User I/O Total Wts User I/O Wt Time Concurcy Total Wts Concurcy Wt Time Admin Total Wts Admin Wt Time Network Total Wts Network Wt Time VIS 708569 5090 15456777 98549 0 0 22536387 2115 SYS$USERS 327 16 0 0 0 0 1 0 SYS$BACKGROUND 1269 12 88 194 0 0 0 0 Back to Wait Events Statistics Back to Top SQL Statistics SQL ordered by Elapsed Time SQL ordered by CPU Time SQL ordered by Gets SQL ordered by Reads SQL ordered by Executions SQL ordered by Parse Calls SQL ordered by Sharable Memory SQL ordered by Version Count Complete List of SQL Text Back to Top SQL ordered by Elapsed Time Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text 19,968 19,870 249,233 0.08 59.93 4f5mjr4hnaz3u PYUSLV declare begin pay_archive... 19,872 19,774 249,671 0.08 59.64 5sfm6rppc4y8x PYUSLV BEGIN pay_us_action_arch.actio... 3,724 1,191 60 62.06 11.18 dab1prc9qyv3v PYUSLV begin hr_nonrun_asact... 1,398 1,307 249,987 0.01 4.19 4ds53shhfcynn PYUSLV declare begin hr_pre_pay.... 1,241 1,238 3,748,793 0.00 3.72 g2daxnc5b7rav PYUSLV SELECT /*+ ORDERED */ fnd_numb... 1,026 1,024 3,747,687 0.00 3.08 7rks7t2gukcwn PYUSLV SELECT /*+ ORDERED */ fnd_numb... 1,012 887 249,792 0.00 3.04 9jv68n0ctas75 PYUSLV begin pay_core_utils . get_pro... 932 931 4,247,316 0.00 2.80 6500rnmy4f88w PYUSLV SELECT PAP.ACCRUAL_PLAN_ID, P... 905 903 1,499,267 0.00 2.72 01pkua627ktjk PYUSLV SELECT fnd_number.number_to_ca... 893 891 2,748,986 0.00 2.68 dz7tyjxnar7gt PYUSLV SELECT /*+ ORDERED */ fnd_numb... 816 412 12,499 0.07 2.45 7y9t68kb4mc6q PYUSLV BEGIN pay_us_action_arch.actio... 650 643 28,392,138 0.00 1.95 4gz4b2bh1fs14 PYUSLV SELECT CONTEXT_ID, SEQUENCE_N... 633 579 249,995 0.00 1.90 699jt64x8hadf PYUSLV UPDATE PAY_ASSIGNMENT_ACTIONS ... 585 60 12,512 0.05 1.76 73rvua18a9ytx PYUSLV declare ret number ; BEGIN hr_... 503 502 1,124,666 0.00 1.51 8v2cvg5zhw9ua PYUSLV SELECT fnd_number.number_to_ca... 468 467 2,749,822 0.00 1.41 6v07302134wyf PYUSLV INSERT INTO PAY_ACTION_INFORMA... 440 438 10,720,406 0.00 1.32 02k10jzw8c495 PYUSLV SELECT INTLK.LOCKING_ACTION_ID... 419 419 13,593,609 0.00 1.26 grg3zu94xp62h PYUSLV SELECT PDB.BALANCE_TYPE_ID, P... 403 397 249,840 0.00 1.21 7mmdd7sypzu9x PYUSLV BEGIN FFP1893_01010001.FORMULA... 401 397 249,991 0.00 1.20 7d66dw0vz7yaa PYUSLV begin pay_cc_process_utils . r... 393 343 249,984 0.00 1.18 36z3h9rgkd8aa PYUSLV begin ... 366 365 249,901 0.00 1.10 2z261p0p82p32 PYUSLV BEGIN FFP51704_01010001.FORMUL... Back to SQL Statistics Back to Top SQL ordered by CPU Time Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 CPU Time (s) Elapsed Time (s) Executions CPU per Exec (s) % Total DB Time SQL Id SQL Module SQL Text 19,870 19,968 249,233 0.08 59.93 4f5mjr4hnaz3u PYUSLV declare begin pay_archive... 19,774 19,872 249,671 0.08 59.64 5sfm6rppc4y8x PYUSLV BEGIN pay_us_action_arch.actio... 1,307 1,398 249,987 0.01 4.19 4ds53shhfcynn PYUSLV declare begin hr_pre_pay.... 1,238 1,241 3,748,793 0.00 3.72 g2daxnc5b7rav PYUSLV SELECT /*+ ORDERED */ fnd_numb... 1,191 3,724 60 19.86 11.18 dab1prc9qyv3v PYUSLV begin hr_nonrun_asact... 1,024 1,026 3,747,687 0.00 3.08 7rks7t2gukcwn PYUSLV SELECT /*+ ORDERED */ fnd_numb... 931 932 4,247,316 0.00 2.80 6500rnmy4f88w PYUSLV SELECT PAP.ACCRUAL_PLAN_ID, P... 903 905 1,499,267 0.00 2.72 01pkua627ktjk PYUSLV SELECT fnd_number.number_to_ca... 891 893 2,748,986 0.00 2.68 dz7tyjxnar7gt PYUSLV SELECT /*+ ORDERED */ fnd_numb... 887 1,012 249,792 0.00 3.04 9jv68n0ctas75 PYUSLV begin pay_core_utils . get_pro... 643 650 28,392,138 0.00 1.95 4gz4b2bh1fs14 PYUSLV SELECT CONTEXT_ID, SEQUENCE_N... 579 633 249,995 0.00 1.90 699jt64x8hadf PYUSLV UPDATE PAY_ASSIGNMENT_ACTIONS ... 502 503 1,124,666 0.00 1.51 8v2cvg5zhw9ua PYUSLV SELECT fnd_number.number_to_ca... 467 468 2,749,822 0.00 1.41 6v07302134wyf PYUSLV INSERT INTO PAY_ACTION_INFORMA... 438 440 10,720,406 0.00 1.32 02k10jzw8c495 PYUSLV SELECT INTLK.LOCKING_ACTION_ID... 419 419 13,593,609 0.00 1.26 grg3zu94xp62h PYUSLV SELECT PDB.BALANCE_TYPE_ID, P... 412 816 12,499 0.03 2.45 7y9t68kb4mc6q PYUSLV BEGIN pay_us_action_arch.actio... 397 403 249,840 0.00 1.21 7mmdd7sypzu9x PYUSLV BEGIN FFP1893_01010001.FORMULA... 397 401 249,991 0.00 1.20 7d66dw0vz7yaa PYUSLV begin pay_cc_process_utils . r... 365 366 249,901 0.00 1.10 2z261p0p82p32 PYUSLV BEGIN FFP51704_01010001.FORMUL... 343 393 249,984 0.00 1.18 36z3h9rgkd8aa PYUSLV begin ... 60 585 12,512 0.00 1.76 73rvua18a9ytx PYUSLV declare ret number ; BEGIN hr_... Back to SQL Statistics Back to Top SQL ordered by Gets Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. Total Buffer Gets: 3,263,941,824 Captured SQL account for 61.4% of Total Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text 2,235,379,554 249,671 8,953.30 68.49 19774.11 19871.80 5sfm6rppc4y8x PYUSLV BEGIN pay_us_action_arch.actio... 2,213,043,065 249,233 8,879.41 67.80 19870.03 19967.86 4f5mjr4hnaz3u PYUSLV declare begin pay_archive... 251,949,867 3,748,793 67.21 7.72 1238.33 1240.86 g2daxnc5b7rav PYUSLV SELECT /*+ ORDERED */ fnd_numb... 248,589,156 1,499,267 165.81 7.62 903.49 904.70 01pkua627ktjk PYUSLV SELECT fnd_number.number_to_ca... 210,990,547 60 3,516,509.12 6.46 1191.43 3723.69 dab1prc9qyv3v PYUSLV begin hr_nonrun_asact... 198,425,050 3,747,687 52.95 6.08 1024.38 1026.14 7rks7t2gukcwn PYUSLV SELECT /*+ ORDERED */ fnd_numb... 184,034,366 2,748,986 66.95 5.64 891.05 892.82 dz7tyjxnar7gt PYUSLV SELECT /*+ ORDERED */ fnd_numb... 139,288,914 1,124,666 123.85 4.27 501.71 502.62 8v2cvg5zhw9ua PYUSLV SELECT fnd_number.number_to_ca... 122,248,530 13,593,609 8.99 3.75 418.53 419.37 grg3zu94xp62h PYUSLV SELECT PDB.BALANCE_TYPE_ID, P... 118,565,998 249,987 474.29 3.63 1307.06 1397.52 4ds53shhfcynn PYUSLV declare begin hr_pre_pay.... 118,009,125 10,720,406 11.01 3.62 437.88 439.99 02k10jzw8c495 PYUSLV SELECT INTLK.LOCKING_ACTION_ID... 85,142,247 28,392,138 3.00 2.61 643.18 650.27 4gz4b2bh1fs14 PYUSLV SELECT CONTEXT_ID, SEQUENCE_N... 77,293,256 249,991 309.18 2.37 397.21 401.47 7d66dw0vz7yaa PYUSLV begin pay_cc_process_utils . r... 67,470,207 249,792 270.11 2.07 887.04 1011.71 9jv68n0ctas75 PYUSLV begin pay_core_utils . get_pro... 59,342,224 249,840 237.52 1.82 397.37 403.09 7mmdd7sypzu9x PYUSLV BEGIN FFP1893_01010001.FORMULA... 55,818,693 2,749,822 20.30 1.71 467.02 468.42 6v07302134wyf PYUSLV INSERT INTO PAY_ACTION_INFORMA... 55,148,110 248,875 221.59 1.69 244.81 245.66 gxa19cf01sb0c PYUSLV SELECT PTS.TIME_IN_STATE, PTS... 54,360,244 13,594,588 4.00 1.67 267.06 267.94 9wupzu1crbdbb PYUSLV SELECT RO.TEXT, RO.OPTIMIZER_... 50,810,066 75,038 677.12 1.56 281.65 282.08 6gb9bfzbwqugc PYUSLV SELECT RGE.STARTING_PERSON_ID,... 47,533,326 4,247,316 11.19 1.46 930.82 932.12 6500rnmy4f88w PYUSLV SELECT PAP.ACCRUAL_PLAN_ID, P... 43,536,041 249,901 174.21 1.33 364.59 366.28 2z261p0p82p32 PYUSLV BEGIN FFP51704_01010001.FORMUL... 41,039,933 499,802 82.11 1.26 116.14 116.38 165d2bgc2b9hr PYUSLV SELECT NVL(SUM(prb.balance_val... 37,487,798 3,119,615 12.02 1.15 212.63 212.82 fnnymj5r1f0n8 RUNGEN SELECT PBG.LEGISLATION_CODE FR... 34,841,297 249,612 139.58 1.07 294.13 294.30 gq04p7vgjcmkg PYUSLV SELECT PPT.CATEGORY CATEGORY, ... 34,072,791 12,499 2,726.04 1.04 412.34 815.64 7y9t68kb4mc6q PYUSLV BEGIN pay_us_action_arch.actio... 32,910,599 499,729 65.86 1.01 146.89 147.88 b0j6srzdfp5jf PYUSLV select /*+ ORDERED USE_NL(AA... Back to SQL Statistics Back to Top SQL ordered by Reads Total Disk Reads: 706,028 Captured SQL account for 37.8% of Total Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text 429,965 249,671 1.72 60.90 19774.11 19871.80 5sfm6rppc4y8x PYUSLV BEGIN pay_us_action_arch.actio... 426,276 249,233 1.71 60.38 19870.03 19967.86 4f5mjr4hnaz3u PYUSLV declare begin pay_archive... 63,789 2,749,822 0.02 9.03 467.02 468.42 6v07302134wyf PYUSLV INSERT INTO PAY_ACTION_INFORMA... 59,813 249,986 0.24 8.47 284.77 294.27 g4jp561h1bs98 PYUSLV select /*+ ORDERED USE_NL(AC... 50,164 499,802 0.10 7.11 116.14 116.38 165d2bgc2b9hr PYUSLV SELECT NVL(SUM(prb.balance_val... 40,675 249,991 0.16 5.76 397.21 401.47 7d66dw0vz7yaa PYUSLV begin pay_cc_process_utils . r... 25,109 248,875 0.10 3.56 244.81 245.66 gxa19cf01sb0c PYUSLV SELECT PTS.TIME_IN_STATE, PTS... 20,753 499,729 0.04 2.94 146.89 147.88 b0j6srzdfp5jf PYUSLV select /*+ ORDERED USE_NL(AA... 17,592 4,247,316 0.00 2.49 930.82 932.12 6500rnmy4f88w PYUSLV SELECT PAP.ACCRUAL_PLAN_ID, P... 15,699 1,499,267 0.01 2.22 903.49 904.70 01pkua627ktjk PYUSLV SELECT fnd_number.number_to_ca... 13,792 60 229.87 1.95 1191.43 3723.69 dab1prc9qyv3v PYUSLV begin hr_nonrun_asact... 10,451 249,901 0.04 1.48 364.59 366.28 2z261p0p82p32 PYUSLV BEGIN FFP51704_01010001.FORMUL... Back to SQL Statistics Back to Top SQL ordered by Executions Total Executions: 212,362,745 Captured SQL account for 48.1% of Total Executions Rows Processed Rows per Exec CPU per Exec (s) Elap per Exec (s) SQL Id SQL Module SQL Text 28,392,138 43,826,491 1.54 0.00 0.00 4gz4b2bh1fs14 PYUSLV SELECT CONTEXT_ID, SEQUENCE_N... 13,594,588 13,606,464 1.00 0.00 0.00 9wupzu1crbdbb PYUSLV SELECT RO.TEXT, RO.OPTIMIZER_... 13,593,609 13,597,089 1.00 0.00 0.00 grg3zu94xp62h PYUSLV SELECT PDB.BALANCE_TYPE_ID, P... 10,720,406 10,729,869 1.00 0.00 0.00 02k10jzw8c495 PYUSLV SELECT INTLK.LOCKING_ACTION_ID... 4,247,316 0 0.00 0.00 0.00 6500rnmy4f88w PYUSLV SELECT PAP.ACCRUAL_PLAN_ID, P... 3,748,793 3,747,651 1.00 0.00 0.00 g2daxnc5b7rav PYUSLV SELECT /*+ ORDERED */ fnd_numb... 3,747,687 3,741,576 1.00 0.00 0.00 7rks7t2gukcwn PYUSLV SELECT /*+ ORDERED */ fnd_numb... 3,119,615 3,124,317 1.00 0.00 0.00 fnnymj5r1f0n8 RUNGEN SELECT PBG.LEGISLATION_CODE FR... 2,749,822 2,749,810 1.00 0.00 0.00 6v07302134wyf PYUSLV INSERT INTO PAY_ACTION_INFORMA... 2,749,451 2,749,839 1.00 0.00 0.00 cw5jzzppcw0kj PYUSLV SELECT PAY_ACTION_INFORMATION_... 2,748,986 2,745,211 1.00 0.00 0.00 dz7tyjxnar7gt PYUSLV SELECT /*+ ORDERED */ fnd_numb... Back to SQL Statistics Back to Top SQL ordered by Parse Calls Total Parse Calls: 18,410,591 Captured SQL account for 92.5% of Total Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text 3,725,953 3,747,687 20.24 7rks7t2gukcwn PYUSLV SELECT /*+ ORDERED */ fnd_numb... 3,725,385 3,748,793 20.24 g2daxnc5b7rav PYUSLV SELECT /*+ ORDERED */ fnd_numb... 2,735,188 2,748,986 14.86 dz7tyjxnar7gt PYUSLV SELECT /*+ ORDERED */ fnd_numb... 1,484,406 1,499,267 8.06 01pkua627ktjk PYUSLV SELECT fnd_number.number_to_ca... 1,110,985 1,124,666 6.03 8v2cvg5zhw9ua PYUSLV SELECT fnd_number.number_to_ca... 745,089 749,395 4.05 fqgs7vbu8htby PYUSLV SELECT fnd_number.number_to_ca... 499,641 499,690 2.71 83kbt99vg4svq PYUSLV begin ... 497,662 499,780 2.70 3n5uwzsh3y8gr PYUSLV SELECT NVL(SUM(prb.balance_val... 495,900 499,802 2.69 165d2bgc2b9hr PYUSLV SELECT NVL(SUM(prb.balance_val... 249,953 249,999 1.36 97mt0g20bcfc3 PYUSLV begin ... 249,933 249,984 1.36 36z3h9rgkd8aa PYUSLV begin ... 249,813 249,987 1.36 4ds53shhfcynn PYUSLV declare begin hr_pre_pay.... 249,440 249,233 1.35 4f5mjr4hnaz3u PYUSLV declare begin pay_archive... 249,119 249,671 1.35 5sfm6rppc4y8x PYUSLV BEGIN pay_us_action_arch.actio... 248,535 249,935 1.35 3rg5fyfgd5uwc PYUSLV begin pay_US_rules.get_tim... 245,393 249,884 1.33 d7shgtk3p7s4u PYUSLV SELECT /*+ ordered index(BAL... 222,270 222,270 1.21 cr74qtm56vcq6 NACHA BEGIN PAY_US_NACHA_TAPE.RUN_FO... Back to SQL Statistics Back to Top SQL ordered by Sharable Memory No data exists for this section of the report. Back to SQL Statistics Back to Top

SQL ordered by Version Count

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

Complete List of SQL Text

SQL Id	SQL Text
01pkua627ktjk	SELECT fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale), 0)) FROM /* Assignment within Government Reporting Entity */ pay_run_result_values TARGET , pay_balance_feeds_f FEED , pay_run_results RR , pay_assignment_actions ASSACT , pay_assignment_actions BAL_ASSACT , pay_payroll_actions PACT , pay_payroll_actions BACT where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and FEED.balance_type_id = :U1 + decode(TARGET.INPUT_VALUE_ID, null, 0, 0) and FEED.input_value_id = TARGET.input_value_id and nvl(TARGET.result_value, '0') != '0' and TARGET.run_result_id = RR.run_result_id and RR.assignment_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action_id = PACT.payroll_action_id and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date and RR.status in ('P', 'PA') and ASSACT.action_sequence <= BAL_ASSACT.action_sequence and ASSACT.assignment_id = BAL_ASSACT.assignment_id and ASSACT.tax_unit_id = :TAX_UNIT_ID /* Year To Date */ and PACT.effective_date >= trunc(BACT.effective_date, 'Y')
02k10jzw8c495	SELECT INTLK.LOCKING_ACTION_ID FROM PAY_ACTION_INTERLOCKS INTLK, PAY_ASSIGNMENT_ACTIONS PAA, PAY_PAYROLL_ACTIONS PPA WHERE INTLK.LOCKED_ACTION_ID = :B1 AND INTLK.LOCKING_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID AND PPA.ACTION_TYPE IN ('P', 'U') AND PAA.SOURCE_ACTION_ID IS NULL
165d2bgc2b9hr	SELECT NVL(SUM(prb.balance_value), 0) FROM /* Assignment within a Government Reporting Entity for YTD based on run balances */ pay_run_balances prb, pay_defined_balances pdb, pay_payroll_actions ppa, pay_assignment_actions paa where paa.assignment_action_id = :ASSIGNMENT_ACTION_ID and prb.tax_unit_id = :TAX_UNIT_ID and ppa.payroll_action_id = paa.payroll_action_id and pdb.balance_type_id = :U1 and pdb.balance_dimension_id = :U2 and pdb.defined_balance_id = prb.defined_balance_id and paa.assignment_id = prb.assignment_id and prb.action_sequence <= paa.action_sequence and prb.effective_date >= trunc(ppa.effective_date, 'Y')
2z261p0p82p32	BEGIN FFP51704_01010001.FORMULA( :N3, :I7, :N2, :T1, :I1, :T5, :I5, :T4, :I4, :N8, :I12, :T3, :I3, :T8, :I14, :N6, :I10, :T7, :I13, :N4, :I8, :D1, :N12, :I18, :N10, :I16, :N7, :I11, :N11, :I17, :T6, :I6, :N1, :T2, :I2, :N5, :I9, :N9, :I15, :FFERLN, :FFERCD, :FFERMT); END;
36z3h9rgkd8aa	begin hrassact.update_action_sequence (:aa_id, :itpflg); end;
3n5uwzsh3y8gr	SELECT NVL(SUM(prb.balance_value), 0) FROM /* Assignment Payments on run balances */ pay_run_balances prb , pay_defined_balances pdb , pay_assignment_actions ASSACT , pay_assignment_actions BAL_ASSACT , pay_payroll_actions PACT , pay_payroll_actions BACT , pay_assignment_actions PPASSACT , pay_payroll_actions PPACT , pay_action_interlocks INTLCK2 , pay_run_types_f BAL_PRT , pay_run_types_f PRT where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and BACT.action_type in ('P', 'U', 'R', 'Q') and PPASSACT.assignment_action_id = pay_core_utils.get_pp_action_id(BACT.action_type, BAL_ASSACT.assignment_action_id) and PPASSACT.payroll_action_id = PPACT.payroll_action_id and PPACT.action_type in ('P', 'U') and PPASSACT.assignment_action_id = INTLCK2.locking_action_id and INTLCK2.locked_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action_id = PACT.payroll_action_id and nvl(BAL_ASSACT.run_type_id, -999) = BAL_PRT.run_type_id (+) and nvl(ASSACT.run_type_id, -999) = PRT.run_type_id (+) and ASSACT.assignment_action_id = decode(BACT.action_type, 'P', ASSACT.assignment_action_id, 'U', ASSACT.assignment_action_id, 'R', decode(nvl(BAL_PRT.run_method, 'N'), 'S ', BAL_ASSACT.ASSIGNMENT_ACTION_ID, decode (nvl(PRT.run_method, 'N'), 'S', -999, ASSACT.assignment_action_id)), 'Q', decode(nvl(BAL_PRT.run_method, 'N'), 'S', BAL_ASSACT.ASSIGNMENT_ACTION_ID, decode (nvl(PRT.run_method, 'N'), 'S', -999, ASSACT.assignment_action_id)) ) and ASSACT.tax_unit_id = decode(BACT.action_type, 'P', ASSACT.tax_unit_id, 'U', ASSACT.tax_unit_id, 'R', decode(BAL_ASSACT.SOURCE_ACTION_ID, NULL, ASSACT.TAX_UNIT_ID, BAL_ASSACT.tax_unit_id), 'Q', decode(BAL_ASSACT.SOURCE_ACTION_ID, NULL, ASSACT.TAX_UNIT_ID, BAL_ASSACT.tax_unit_id) ) and ASSACT.assignment_id = BAL_ASSACT.assignment_id and nvl(PACT.future_process_mode, 'Y') = 'Y' and PACT.action_type <> 'V' and prb.assignment_action_id = ASSACT.assignment_action_id and prb.defined_balance_id = pdb.defined_balance_id and pdb.balance_type_id = :U1 and pdb.balance_dimension_id = :U2
3rg5fyfgd5uwc	begin pay_US_rules.get_time_def_for_entry( :element_entry_id, :assignment_id, :assignment_action_id, :business_group_id, :time_def_id); end;
4ds53shhfcynn	declare begin hr_pre_pay.do_prepayment(:p1, :p2, :p3, :p4, :5, :p6:p6ind, :p7); end;
4f5mjr4hnaz3u	declare begin pay_archive.process_employee(:asg_action); end;
4gz4b2bh1fs14	SELECT CONTEXT_ID, SEQUENCE_NO FROM FF_ROUTE_CONTEXT_USAGES WHERE ROUTE_ID = :B1 ORDER BY SEQUENCE_NO
5sfm6rppc4y8x	BEGIN pay_us_action_arch.action_archive_data(:aactid, :l_effective_date); END;
6500rnmy4f88w	SELECT PAP.ACCRUAL_PLAN_ID, PAP.ACCRUAL_PLAN_ELEMENT_TYPE_ID FROM PAY_ACCRUAL_PLANS PAP, PAY_ELEMENT_LINKS_F PEL, PAY_ELEMENT_ENTRIES_F PEE WHERE PAP.ACCRUAL_CATEGORY = :B3 AND PEL.ELEMENT_TYPE_ID= PAP.ACCRUAL_PLAN_ELEMENT_TYPE_ID AND :B1 BETWEEN PEL.EFFECTIVE_START_DATE AND PEL.EFFECTIVE_END_DATE AND PEE.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID AND PEE.ASSIGNMENT_ID = :B2 AND :B1 BETWEEN PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE
699jt64x8hadf	UPDATE PAY_ASSIGNMENT_ACTIONS SET SERIAL_NUMBER = :B2 WHERE ASSIGNMENT_ACTION_ID = :B1
6gb9bfzbwqugc	SELECT RGE.STARTING_PERSON_ID, RGE.ENDING_PERSON_ID, RGE.CHUNK_NUMBER, NVL(RGE.RAND_CHUNK_NUMBER, RGE.CHUNK_NUMBER) FROM PAY_POPULATION_RANGES RGE WHERE RGE.PAYROLL_ACTION_ID = :B1 AND RGE.RANGE_STATUS = 'U' AND ROWNUM < 2
6v07302134wyf	INSERT INTO PAY_ACTION_INFORMATION (ACTION_INFORMATION_ID , ACTION_CONTEXT_ID , ACTION_CONTEXT_TYPE , TAX_UNIT_ID , JURISDICTION_CODE , SOURCE_ID , SOURCE_TEXT , TAX_GROUP , OBJECT_VERSION_NUMBER , EFFECTIVE_DATE , ASSIGNMENT_ID , ACTION_INFORMATION_CATEGORY , ACTION_INFORMATION1 , ACTION_INFORMATION2 , ACTION_INFORMATION3 , ACTION_INFORMATION4 , ACTION_INFORMATION5 , ACTION_INFORMATION6 , ACTION_INFORMATION7 , ACTION_INFORMATION8 , ACTION_INFORMATION9 , ACTION_INFORMATION10 , ACTION_INFORMATION11 , ACTION_INFORMATION12 , ACTION_INFORMATION13 , ACTION_INFORMATION14 , ACTION_INFORMATION15 , ACTION_INFORMATION16 , ACTION_INFORMATION17 , ACTION_INFORMATION18 , ACTION_INFORMATION19 , ACTION_INFORMATION20 , ACTION_INFORMATION21 , ACTION_INFORMATION22 , ACTION_INFORMATION23 , ACTION_INFORMATION24 , ACTION_INFORMATION25 , ACTION_INFORMATION26 , ACTION_INFORMATION27 , ACTION_INFORMATION28 , ACTION_INFORMATION29 , ACTION_INFORMATION30 ) VALUES (:B42 , :B41 , :B40 , :B39 , :B38 , :B37 , :B36 , :B35 , :B34 , :B33 , :B32 , :B31 , :B30 , :B29 , :B28 , :B27 , :B26 , :B25 , :B24 , :B23 , :B22 , :B21 , :B20 , :B19 , :B18 , :B17 , :B16 , :B15 , :B14 , :B13 , :B12 , :B11 , :B10 , :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , :B1 )
73rvua18a9ytx	declare ret number ; BEGIN hr_nonrun_asact . get_next_pop_chunk ( :pactid , :atype , :l_lckhandle , :lub , :lul , :chunk_method , :threads , :slave_no , :curr_chunk , :max_chunks , :stperson:stperson_ind , :endperson:endperson_ind , :chunk:chunk_ind , :rand_chunk:rand_chunk_ind ) ; END ;
7d66dw0vz7yaa	begin pay_cc_process_utils . reset_dates_for_run ( p_asg_id => :v_asg_id , p_sysdate => to_date ( :v_sysdate , :date_format ) , p_assact_id => :v_assact_id ) ; end ;
7mmdd7sypzu9x	BEGIN FFP1893_01010001.FORMULA( :N1, :N2, :T1, :I1, :D1, :T2, :I2, :T7, :I7, :T8, :I8, :T5, :I5, :N5, :T4, :I4, :N4, :N3, :T3, :I3, :T6, :I6, :FFERLN, :FFERCD, :FFERMT); END;
7rks7t2gukcwn	SELECT /*+ ORDERED */ fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale), 0)) FROM pay_assignment_actions BAL_ASSACT , pay_payroll_actions BACT , pay_run_types_f BAL_PRT , pay_assignment_actions PPASSACT , pay_payroll_actions PPACT , pay_run_types_f PRT , 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 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 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 = decod e(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 ASSACT.payroll_action_id = PACT.payroll_action_id and nvl(PACT.future_process_mode, 'Y') = 'Y' and PACT.action_type not in ('V') and RR.assignment_action_id = ASSACT.assignment_action_id and TARGET.run_result_id = RR.run_result_id and nvl(TARGET.result_value, '0') != '0' and FEED.balance_type_id = :U1 + DECODE(TARGET.INPUT_VALUE_ID, NULL , 0, 0) and FEED.input_value_id = TARGET.input_value_id and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date and RR.status in ('P', 'PA') /* Within Jurisdiction */ and substr (RR.jurisdiction_code, 1, :JURISDICTION_LEVEL) = substr (:JURISDICTION_CODE, 1, :JURISDICTION_LEVEL)
7y9t68kb4mc6q	BEGIN pay_us_action_arch.action_action_creation(:pactid, :stperson, :endperson, :chunk); END;
83kbt99vg4svq	begin hr_nonrun_asact.reinterlock (:assactid, :pactype); end;
8v2cvg5zhw9ua	SELECT fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale), 0)) FROM /* Assignment within Government Reporting Entity */ pay_run_result_values TARGET , pay_balance_feeds_f FEED , pay_run_results RR , pay_assignment_actions ASSACT , pay_assignment_actions BAL_ASSACT , pay_payroll_actions PACT , pay_payroll_actions BACT where BAL_ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id and FEED.balance_type_id = :U1 + decode(TARGET.INPUT_VALUE_ID, null, 0, 0) and FEED.input_value_id = TARGET.input_value_id and nvl(TARGET.result_value, '0') != '0' and TARGET.run_result_id = RR.run_result_id and RR.assignment_action_id = ASSACT.assignment_action_id and ASSACT.payroll_action_id = PACT.payroll_action_id and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date and RR.status in ('P', 'PA') and ASSACT.action_sequence <= BAL_ASSACT.action_sequence and ASSACT.assignment_id = BAL_ASSACT.assignment_id and ASSACT.tax_unit_id = :TAX_UNIT_ID /* Year To Date */ and PACT.effective_date >= trunc(BACT.effective_date, 'Y') /* Within Jurisdiction */ and :JURISDICTION_LEVEL != 0 and substr (RR.jurisdiction_code, 1, :JURISDICTION_LEVEL) = substr (:JURISDICTION_CODE, 1, :JURISDICTION_LEVEL)
97mt0g20bcfc3	begin pay_US_rules.get_default_jurisdiction (:asgact, :eeid, :defval:defvalind); end;
9jv68n0ctas75	begin pay_core_utils . get_prorated_dates ( :ee_id , :asg_act_id , :time_def_type , :time_def_id:time_def_ind , :pro_dates:prod_ind , :pro_types:prot_ind ) ; END ;
9wupzu1crbdbb	SELECT RO.TEXT, RO.OPTIMIZER_HINT FROM FF_ROUTES RO WHERE RO.ROUTE_ID = :B1
b0j6srzdfp5jf	select /*+ ORDERED USE_NL(AA PA RR RRV LIV IV PERA ET FC EC EL) INDEX(AA PAY_ASSIGNMENT_ACTIONS_PK, PA PAY_PAYROLL_ACTIONS_PK, RR PAY_RUN_RESULTS_N50, ET PAY_ELEMENT_TYPES_F_PK, EL PAY_ELEMENT_LINKS_F_PK, EE PAY_ELEMENT_ENTRIES_F_PK, IV PAY_INPUT_VALUES_F_N1, PERA +*/ EL2.element_link_id , EL2.costable_type , EL2.cost_allocation_keyflex_id , EL2.balancing_keyflex_id , EL2.element_set_id , decode(IV.uom, 'M', nvl(FC.precision, 3), 3) , nvl(EC.costing_debit_or_credit, 'D') , to_char(RR.run_result_id) , decode(:b0, 'A', decode(EE.entry_type, 'A', EE.target_entry_id, 'R', EE.target_entry_id, RR.source_id), RR.source_id) , decode(:b0, 'A', nvl(EE.entry_type, 'N'), 'N') , decode(RR.source_type, 'E', nvl(EE.creator_type, 'X'), 'I', decode(EE.creator_type, null , 'X', 'N')) , EE.cost_allocation_keyflex_id , RR.element_type_id , RRV.input_value_id , RRV.result_value , IV.uom , decode(EL2.costable_type, 'D', decode(decode(RR.source_type, 'E', EE.creator_type, 'N'), 'RR', 2, 'EE', 2, 'NR', 2, 'PR', 2, 3), 1) , RR.jurisdiction_code , decode(IV.uom, 'M', decode(IV.name, 'Pay Value', 1, 0), 0) , to_char(decode(:b2, 0, PA.date_earned, decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned))), 'YYYY/MM/DD') , EL2.transfer_to_gl_flag , decode(:b3, null , null , hr_cost.get_context_value(:b3, RR.run_result_id, RR.element_type_id, PA.date_earned)) , decode(:b5, null , null , hr_cost.get_context_value(:b5, RR.run_result_i d, RR.element_type_id, PA.date_earned)) , decode(:b7, null , null , hr_cost.get_context_value(:b7, RR.run_result_id, RR.element_type_id, PA.date_earned)) , decode(:b9, null , null , hr_cost.get_context_value(:b9, RR.run_result_id, RR.element_type_id, PA.date_earned)) from pay_assignment_actions AA , pay_payroll_actions PA , per_all_assignments_f PERA , pay_run_results RR , pay_element_entries_f EE , pay_element_types_f ET , pay_element_classifications EC , fnd_currencies FC , pay_input_values_f IV , pay_run_result_values RRV , pay_link_input_values_f LIV , pay_element_links_f EL , pay_element_links_f EL2 where ((((((((((((((((((((((((((((((AA.assignment_action_id=:b11 and AA.payroll_action_id=PA.payroll_action_id) and RR.assignment_action_id=:b11) and RR.status like 'P%') and RR.element_type_id=ET.element_type_id) and RRV.run_result_id=RR.run_result_id) and IV.input_value_id=RRV.input_value_id) and decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)) between IV.effective_start_date and IV.effective_end_date) and IV.element_type_id=ET.element_type_id) and RRV.result_value is not null ) and (decode(:b13, 'Y', 1, fnd_number.canonical_to_number(RRV.result_value))+decode(LIV.DEFAULT_VALUE, 'M', 0, 0))<>0) and RRV.input_value_id=LIV.input_value_id) and decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)) between LIV.effective_start_date and LIV.effective_end_date) and LIV.costed_flag='Y') and LIV.element_link_id=EL.element_link_id) and decode(RR.entry_type, 'B', nvl(RR.element_entry_id, RR.source_id), RR.source_id)=EE.element_entry_id(+)) and decode(RR.end_date, null , to_date(:b14, 'YYYY/MM/DD'), hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, to_date(:b14, 'YYYY/MM/DD'))) between EE.effective_start_date(+) and EE.effective_end_date(+)) and RR.element_type_id=ET.element_type_id) and decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)) between ET.effective_start_date and ET.effective_end_date) and ET.classification_id=EC.classification_id) and ET.output_currency_code=FC.currency_code) and (ET.element_type_id+0)=(EL.element_type_id+0)) and decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)) between EL.effective_start_date and EL.effective_end_date) and EL.costable_type<>'N') and ((PA.action_type='B' and hr_cost.cost_bal_adj(nvl(RR.element_entry_id, RR.source_id), PA.date_earned)='Y') or PA.action_type<>'B')) and PERA.assignment_id=:b16) and (PERA.business_group_id+0)=(EL.business_group_id+0)) and decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)) between PERA.effective_start_date and PERA.effective_end_date) and (((EE.element_entry_id is not null and RR.source_type='E') and EL.element_link_id=EE.element_link_id) or ((((((((((EE.element_entry_id is null or (EE.element_entry_id is not null and RR.source_type<>'E')) and (((EL.payroll_id is not null and EL.payroll_id=PERA.payroll_id) or (EL.link_to_all_payrolls_flag='Y' and PERA.payroll_id is not null )) or EL.payroll_id is null )) and (EL.organization_id=PERA.organization_id or EL.organization_id is null )) and (EL.position_id=PERA.position_id or EL.position_id is null )) and (EL.job_id=PERA.job_id or EL.job_id is null )) and (EL.grade_id=PERA.grade_id or EL.grade_id is null )) and (EL.location_id=PERA.location_id or EL.location_id is null )) and (EL.pay_basis_id=PERA.pay_basis_id or EL.pay_basis_id is null )) and (EL.employment_category=PERA.employment_category or EL.employment_category is null )) and (EL.people_group_id is null or exists (select 1 from pay_assignment_link_usages_f PAL where ((PAL.assignment_id=:b16 and PAL.element_link_id=EL.element_link_id) and decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)) between PAL.effective_start_date and PAL.effective_end_date)))))) and EL2.element_link_id=EL.element_link_id) and decode(:b2, 0, PA.date_earned, decode(EL.costable_type, 'D', PA.date_earned, decode(RR.end_date, null , PA.date_earned, hr_cost.get_rr_date(RR.source_id, RR.source_type, RR.end_date, PA.date_earned)))) between EL2.effective_start_date and EL2.effective_end_date) order by 17, 8, 14
cr74qtm56vcq6	BEGIN PAY_US_NACHA_TAPE.RUN_FORMULA; END;
cw5jzzppcw0kj	SELECT PAY_ACTION_INFORMATION_S.NEXTVAL FROM SYS.DUAL
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;
dz7tyjxnar7gt	SELECT /*+ ORDERED */ fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale), 0)) FROM pay_assignment_actions BAL_ASSACT , pay_payroll_actions BACT , pay_run_types_f BAL_PRT , pay_assignment_actions PPASSACT , pay_payroll_actions PPACT , pay_run_types_f PRT , 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 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 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 ASSACT.payroll_action_id = PACT.payroll_action_id and nvl(PACT.future_process_mode, 'Y') = 'Y' and PACT.action_type not in ('V') and R R.assignment_action_id = ASSACT.assignment_action_id and RR.status in ('P', 'PA') and TARGET.run_result_id = RR.run_result_id and nvl(TARGET.result_value, '0') != '0' and FEED.balance_type_id = :U1 + DECODE(TARGET.INPUT_VALUE_ID, NULL , 0, 0) and FEED.input_value_id = TARGET.input_value_id and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date and exists (select null from pay_taxability_rules TR , pay_element_types_f ET , pay_balance_types BT where ET.element_type_id = RR.element_type_id and PACT.date_earned between ET.effective_start_date and ET.effective_end_date and BT.balance_type_id = :U1 and TR.classification_id = ET.classification_id + 0 and TR.tax_category = ET.element_information1 and TR.tax_type = BT.tax_type and TR.jurisdiction_code = '00-000-0000' and nvl(TR.status, 'VALID') <> 'D' )
fnnymj5r1f0n8	SELECT PBG.LEGISLATION_CODE FROM PER_BUSINESS_GROUPS PBG WHERE PBG.BUSINESS_GROUP_ID = :B1
fqgs7vbu8htby	SELECT fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale), 0)) FROM /* Assignment For Run */ pay_run_result_values TARGET , pay_balance_feeds_f FEED , pay_run_results RR , pay_assignment_actions ASSACT , pay_payroll_actions PACT where ASSACT.assignment_action_id = :ASSIGNMENT_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')
g2daxnc5b7rav	SELECT /*+ ORDERED */ fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale), 0)) FROM pay_assignment_actions BAL_ASSACT , pay_payroll_actions BACT , pay_run_types_f BAL_PRT , pay_assignment_actions PPASSACT , pay_payroll_actions PPACT , pay_run_types_f PRT , 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 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 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.ac tion_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 ASSACT.payroll_action_id = PACT.payroll_action_id and nvl(PACT.future_process_mode, 'Y') = 'Y' and PACT.action_type not in ('V') and RR.assig nment_action_id = ASSACT.assignment_action_id and RR.status in ('P', 'PA') and TARGET.run_result_id = RR.run_result_id and nvl(TARGET.result_value, '0') != '0' and FEED.balance_type_id = :U1 + DECODE(TARGET.INPUT_VALUE_ID, NULL , 0, 0) and FEED.input_value_id = TARGET.input_value_id and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date
g4jp561h1bs98	select /*+ ORDERED USE_NL(ACT EE ET EEV) INDEX(ACT PAY_ASSIGNMENT_ACTIONS_PK, EE PAY_ELEMENT_ENTRIES_F_N50, ET PAY_ELEMENT_TYPES_F_PK, EEV PAY_ELEMENT_ENTRY_VALUES_F_N50) +*/ EE.entry_type , to_char(EE.element_entry_id) , decode(:b0, 'T', decode(EE.entry_type, 'R', to_char(EE.target_entry_id), 'A', to_char(EE.target_entry_id), to_char(EE.original_entry_id)), to_char(EE.original_entry_id)) , EE.element_link_id , to_char(EE.target_entry_id) , EE.subpriority , ET.element_type_id , ET.processing_priority , ET.processing_type , EEV.input_value_id , EEV.screen_entry_value , to_char(null ) , to_char(null ) , 0 , to_char(null ) , to_char(null ) , decode(EE.entry_type, 'S', 1, 'E', 2, 'R', 2, 'A', 2, 'D', 3) , nvl(EE.target_entry_id, EE.element_entry_id) , decode(EE.entry_type, 'E', 2, 1) , to_char(EE.effective_start_date, :b1) , to_char(EE.effective_end_date, :b1) , ET.proration_group_id , pay_retro_pkg.is_retro_entry(EE.creator_type) , to_char(null ) , 'RECALC_NON_RETRO' , 'E' , PEPD.tax_unit_id , to_char(null ) , PEPD.time_definition_id , nvl(ET.time_definition_type, 'N') , to_char(decode(ET.processing_type, 'R', to_date(null , :b1), EE.date_earned), :b1) , decode(:b5, 'N', to_char(EE.source_start_date, :b1), null ) , decode(:b5, 'N', to_char(EE.source_end_date, :b1), null ) , EE.label_identifier , decode(ACT.action_status, 'B', decode(EE.creator_type, 'EE', pay_retro_pkg.process_retro_entry(EE.element_entry_id, EE.element_type_id, :b9:b10, :b11:b12, EE.creator_id, ACT.act ion_sequence), 'RR', pay_retro_pkg.process_retro_entry(EE.element_entry_id, EE.element_type_id, :b9:b14, :b11:b16, EE.creator_id, ACT.action_sequence), 1), 1) , decode(EE.creator_type, 'EE', decode(:b5, 'N', to_char(EE.source_start_date, :b1), null ), 'RR', decode(:b5, 'N', to_char(EE.source_start_date, :b1), null ), 'AE', to_char(EE.source_start_date, :b1), 'AD', to_char(EE.source_start_date, :b1), null ) , decode(EE.creator_type, 'EE', decode(:b5, 'N', to_char(EE.source_end_date, :b1), null ), 'RR', decode(:b5, 'N', to_char(EE.source_end_date, :b1), null ), 'AE', to_char(EE.source_end_date, :b1), 'AD', to_char(EE.source_end_date, :b1), null ) from pay_assignment_actions ACT , pay_element_entries_f EE , pay_element_types_f ET , pay_element_entry_values_f EEV , pay_entry_process_details PEPD where (((((((((((((((ACT.assignment_action_id=:b29 and EE.assignment_id=:b30) and ((:b31='N' and to_date(:b32, :b1) between EE.effective_start_date and EE.effective_end_date) or ((:b31='Y' and EE.effective_start_date<=to_date(:b32, :b1)) and EE.effective_end_date>=decode(ET.proration_group_id, null , decode(nvl(ET.time_definition_type, 'N'), 'N', to_date(:b32, :b1), pay_core_utils.get_time_period_start(ACT.payroll_action_id)), pay_interpreter_pkg.prorate_start_date(:b29, ET.proration_group_id))))) and EE.element_type_id=ET.element_type_id) and EE.element_entry_id=PEPD.element_entry_id(+)) and to_date(:b32, :b1) between ET.effective_start_date and ET.effective_end_date) and EEV.elem ent_entry_id(+)=EE.element_entry_id) and EEV.screen_entry_value(+) is not null ) and EE.effective_start_date=EEV.effective_start_date(+)) and EE.effective_end_date=EEV.effective_end_date(+)) and EE.entry_type<>'B') and ET.process_in_run_flag<>'N') and ((:b31='N' or ET.proration_group_id is null ) or (:b31='Y' and exists (select '' from pay_payroll_actions ppa , per_time_periods ptp , pay_element_entries_f pee where (((((pee.element_entry_id=EE.element_entry_id and ppa.payroll_action_id=ACT.payroll_action_id) and ppa.payroll_id=ptp.payroll_id) and to_date(:b32, :b1) between ptp.start_date and ptp.end_date) and pee.effective_start_date<=ptp.end_date) and pee.effective_end_date>=ptp.start_date))))) and not (ACT.action_status='B' and EE.creator_type='P')) and not (ACT.action_status='B' and EE.creator_type='R')) and ((((ET.processing_type='N' or EE.entry_type='D') and nvl(EE.date_earned, to_date('01/01/0001', 'DD/MM/YYYY'))<=to_date(:b32, :b1)) and not exists (select '' from pay_run_results prr where (((prr.source_id=EE.element_entry_id and prr.source_type='E') and nvl(prr.status, 'U') not in ('B', 'U')) and not exists (select '' from pay_run_results prr2 where (source_id=prr.run_result_id and source_type='R'))))) or ((ET.processing_type<>'N' and EE.entry_type<>'D') and ((nvl(ET.once_each_period_flag, 'N')='Y' and not exists (select '' from pay_run_results prr , pay_assignment_actions paa , pay_payroll_actions ppa , per_time_periods ptp , per_time_periods ptp1 , pay_payroll_actions ppa1 where (((((((((((prr.source_id=EE.element_entry_id and prr.source_type='E') and paa.assignment_action_id=prr.assignment_action_id) and ppa.payroll_action_id=paa.payroll_action_id) and ptp.payroll_id=ppa.payroll_id) and ppa.date_earned between ptp.start_date and ptp.end_date) and ppa1.payroll_action_id=ACT.payroll_action_id) and ptp1.payroll_id=ppa1.payroll_id) and ppa1.date_earned between ptp1.start_date and ptp1.end_date) and ptp.time_period_id=ptp1.time_period_id) and nvl(prr.status, 'U') not in ('B', 'U')) and not exists (select '' from pay_run_results prr2 where (source_id=prr.run_result_id and source_type='R'))))) or nvl(ET.once_each_period_flag, 'N')='N')))) order by 8 asc , 6 asc , 7 asc , 17 asc , 18 asc , 19 asc , 20 asc
gq04p7vgjcmkg	SELECT PPT.CATEGORY CATEGORY, PPM.PERSONAL_PAYMENT_METHOD_ID PERSONAL_METHOD, PEA.PRENOTE_DATE PRENOTE_DATE, PPT.VALIDATION_DAYS VALID_DAYS, PPM.PERCENTAGE PERCENTAGE, PPM.AMOUNT AMOUNT, OPM.ORG_PAYMENT_METHOD_ID ORG_METHOD, HR_PRE_PAY.SET_CASH_RULE(PPT.CATEGORY, OPM.PMETH_INFORMATION1) CASH_RULE, OPM.CURRENCY_CODE PAYMENT_CURRENCY, PPT.PRE_VALIDATION_REQUIRED VALIDATION_REQUIRED, PPT.VALIDATION_VALUE VALIDATION_VALUE, OPM.EXTERNAL_ACCOUNT_ID EXTERNAL_ACCOUNT_ID FROM PAY_EXTERNAL_ACCOUNTS PEA, PAY_PAYMENT_TYPES PPT, PAY_ORG_PAYMENT_METHODS_F OPM, PAY_PERSONAL_PAYMENT_METHODS_F PPM, PAY_ASSIGNMENT_ACTIONS ACT WHERE ACT.ASSIGNMENT_ACTION_ID=:B4 AND PPM.ASSIGNMENT_ID = ACT.ASSIGNMENT_ID AND PPM.RUN_TYPE_ID = :B3 AND PPM.ORG_PAYMENT_METHOD_ID = OPM.ORG_PAYMENT_METHOD_ID AND OPM.PAYMENT_TYPE_ID = PPT.PAYMENT_TYPE_ID AND OPM.DEFINED_BALANCE_ID = :B2 AND PPM.EXTERNAL_ACCOUNT_ID = PEA.EXTERNAL_ACCOUNT_ID (+) AND FND_DATE.CANONICAL_TO_DATE(:B1 ) BETWEEN PPM.EFFECTIVE_START_DATE AND PPM.EFFECTIVE_END_DATE AND FND_DATE.CANONICAL_TO_DATE(:B1 ) BETWEEN OPM.EFFECTIVE_START_DATE AND OPM.EFFECTIVE_END_DATE ORDER BY PPM.PERSON_ID, PPM.PRIORITY
grg3zu94xp62h	SELECT PDB.BALANCE_TYPE_ID, PDR.ROUTE_ID, PDR.BALANCE_TYPE_COLUMN, PDR.RETRIEVAL_COLUMN, NVL(PDR.DECODE_REQUIRED, 'N'), NVL(JURISDICTION_LEVEL, 0) FROM PAY_DEFINED_BALANCES PDB, PAY_DIMENSION_ROUTES PDR, PAY_BALANCE_TYPES PBT WHERE PDB.DEFINED_BALANCE_ID = :B2 AND PDB.BALANCE_DIMENSION_ID = PDR.BALANCE_DIMENSION_ID AND PBT.BALANCE_TYPE_ID = PDB.BALANCE_TYPE_ID AND PDR.PRIORITY = :B1
gxa19cf01sb0c	SELECT PTS.TIME_IN_STATE, PTS.STATE_NAME, PTS.JURISDICTION_CODE, PTS.FILING_STATUS_CODE, PTS.MEANING, PTS.WITHHOLDING_ALLOWANCES, PTS.SIT_ADDITIONAL_TAX, PTS.SIT_OVERRIDE_AMOUNT, PTS.SIT_OVERRIDE_RATE FROM PAY_US_EMP_TIME_IN_STATE_V PTS WHERE PTS.ASSIGNMENT_ID = :B2 AND PTS.JURISDICTION_CODE LIKE :B1

Back to SQL Statistics
Back to Top

Instance Activity Statistics

• Instance Activity Stats
• Instance Activity Stats - Absolute Values
• Instance Activity Stats - Thread Activity

Instance Activity Stats

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

• Statistics with absolute values (should not be diffed)

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

• Statistics identified by '(derived)' come from sources other than SYSSTAT

Back to Instance Activity Statistics
Back to Top

IO Stats

• Tablespace IO Stats
• File IO Stats

Tablespace IO Stats

• ordered by IOs (Reads + Writes) desc

Back to IO Stats
Back to Top

File IO Stats

• ordered by Tablespace, File

Back to IO Stats
Back to Top

Buffer Pool Statistics

• Standard block size Pools D: default, K: keep, R: recycle
• Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

Back to Top

Advisory Statistics

• Instance Recovery Stats
• Buffer Pool Advisory
• PGA Aggr Summary
• PGA Aggr Target Stats
• PGA Aggr Target Histogram
• PGA Memory Advisory
• Shared Pool Advisory
• SGA Target Advisory
• Streams Pool Advisory
• Java Pool Advisory

Instance Recovery Stats

• B: Begin snapshot, E: End snapshot

Back to Advisory Statistics
Back to Top

Buffer Pool Advisory

• Only rows with estimated physical reads >0 are displayed
• ordered by Block Size, Buffers For Estimate

Back to Advisory Statistics
Back to Top

PGA Aggr Summary

• PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory

Back to Advisory Statistics
Back to Top

PGA Aggr Target Stats

• B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval)
• Auto PGA Target - actual workarea memory target
• W/A PGA Used - amount of memory used for all Workareas (manual + auto)
• %PGA W/A Mem - percentage of PGA memory allocated to workareas
• %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
• %Man W/A Mem - percentage of workarea memory under manual control

Back to Advisory Statistics
Back to Top

PGA Aggr Target Histogram

• Optimal Executions are purely in-memory operations

Back to Advisory Statistics
Back to Top

PGA Memory Advisory

• When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0

Back to Advisory Statistics
Back to Top

Shared Pool Advisory

• SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
• Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid.

Back to Advisory Statistics
Back to Top

SGA Target Advisory

Back to Advisory Statistics
Back to Top

Streams Pool Advisory