Concurrent Processing Performance Analysis for Apps DBAs

Concurrent Processing Performance Analysis for Apps DBAs Maris Elsins Oracle Applications DBA 07.12.2011

© 2011 Pythian Few words about me... • 9y  Oracle: • 3y – PL/SQL Developer • 6y – Oracle [Apps] DBA • Certificates: • 10g OCM, 9i/10g/11g OCP, • 11i Apps DBA OCP, 11i System Administrator OCE • Working at Pythian since July 2011 • Speaker at conferences: • 5* , 3* , 2* • How to find me? • Blog – http://www.pythian.com/news/author/elsins/ • Earlier blog posts – http://appsdbalife.wordpress.com/ • LinkedIn – http://lv.linkedin.com/in/mariselsins • OCM profile – http://education.oracle.com/education/otn/melsins.html • Twitter – @MarisElsins • Email – elsins@pythian.com 2

© 2011 Pythian Why Companies Trust Pythian • Recognized Leader: • Global industry-leader in remote database administration services and consulting for Oracle, Oracle Applications, MySQL and SQL Server • Work with over 150 multinational companies such as Forbes.com, Fox Sports, Nordion and Western Union to help manage their complex IT deployments • Expertise: • One of the world’s largest concentrations of dedicated, full-time DBA expertise. Employ 7 Oracle ACEs/ACE Directors. • Hold 7 Specializations under Oracle Platinum Partner program, including Oracle Exadata, Oracle GoldenGate & Oracle RAC. • Global Reach & Scalability: • 24/7/365 global remote support for DBA and consulting, systems administration, special projects or emergency response 3

© 2011 Pythian Few concurrent processing problems addressed by Apps DBAs • «My concurrent request is taking too long to complete» • SOLUTION: Trace it and give it to the developers to tune the code :) • «My concurrent request is failing» • SOLUTION: Trace it and give it to the developers to fix the code or data  It is a bit more complicated if DBA=Developer or Developers ask for help from DBAs • «I’ve scheduled my concurrent program for 1PM but it’s still not running at 2PM» • TROUBLESHOOTING: Open requests form, check the status of the request, if it’s : • Pending / Standby – There are incompatible requests running, Click «Diagnostics» button to find out which they are • Pending / Normal – Waiting to be ran by a concurrent manager. Check the concurrent managers, check number of pending requests and if there are free manager processes. • SOLUTION: Heavily depends on the situation... (change priority of the request, terminate some requests, increase number of manager processes, etc... ) 5

© 2011 Pythian What if we face a more complex problem... • «My concurrent program X was pending for a long time during last 3 days at ~8PM, please make sure it doesn’t happen today» • How do we check the situation that happened in the past? • «Concurrent program Y is really important, we need to make sure it starts running in 20 seconds after it’s submitted» • How do we check trends and how do we know when the issue about to happen? • CM queries that check the requests queue are top CPU consumers. Do we need all the managers/processes we have configured? • How do we estimate the utilization level of each concurrent manager? What if you like to be proactive and want to find out about the problems before they happen? 6

© 2011 Pythian A Quick reminder on how things work: Lifecycle of a Concurrent Request • Timings are stored in FND_CONCURRENT_REQUESTS 8 pending for execution running CR is submitted Time to run the CR CR completesCR is started Waiting for scheduled time Pending for CRM Pending for CM Released by CRM

© 2011 Pythian A Quick reminder on how things work: Typical activity of Concurrent Managers • Activity of multiple manager (CM) processes • Proc1 • Proc2 • Proc3 • CM specifics • No coordination between processes • «Fight for Work» 9 check CR1 Run CR1 Query the requests queue Read up to «cache size» requests (3 in this case) Startup of CM proc Read settings, read the specialization rules check CR2 check CR3 Run CR3 Verify if the request still needs to be executed Run the request if it’s stil in the «queue» Skip the request if it’s not on the queue anymore Query the «queue» immediately after the last request has been processed check ... sleep seconds CM process idle time

© 2011 Pythian The problem • Concurrent processing (concurrent managers/ concurrent requests) is a key service in any e-Business suite environment. • Configuration can be very different on each environment: • Custom settings «sleep seconds» and «cache size» • Customized concurrent managers (slow, fast, etc...), lots of specialization rules • Customizable number of manager processes, work shifts • Seeded / customized incompatibilities between requests • No «out of the box» way to health-check and troubleshoot the configuration of concurrent managers. • Are there enough concurrent managers / are there too many concurrent managers • Are there any requests that are pending for too long before they are started? • What were the root causes for the issues identified? (back in time diagnostics) 10

© 2011 Pythian What are the requirements? • Analyze the pending time of the concurrent programs • Custom reporting period with a drill-down possibility • Pending for CRM / Pending for CM • Analyze the utilization level of the concurrent managers • Custom reporting period with a drill-down possibility • Check the utilization % of the CM? • Finding out the root causes • Which concurrent managers (CM) could have axecuted the request? • Why the CM didn’t execute my request sooner? • Why wasn’t my request released by CRM sooner? • Identify which requests did cause the usage peak for the particular Manager • Easy to use • Compact output to be able to run it in SQL*Plus 12

© 2011 Pythian What Challenges do we have? • Lack of detailed documentation • Pending time – not possible to trace something that is not running • Complex data model • Lots of things surrounding concurrent requests – programs, sets, stages, queues, incompatibilities, specialization rules, processes, ... • 5 types of specialization rules (prog / req. type / oracle user / apps user / advanced) • Inconsistencies in behaviour and represenatation of configuration • Parent requests – some parents go into paused state, some stay running • Specific concurrent programs that ignore specialization rules (Spawned) • Pointers to concurrent program/set/stage names • ... • Lack of information • Historical information has to be reverse-engeneered if it’s possible • Not possible for specializations, incompatibilities, ... • Possible for CM utilization and statuses of the request at each moment of time • which manager can execute the particual program – only specialization rules stored • ... 13

© 2011 Pythian Warning! 14 Test the scripts before using, pay attention to the performance FND_CONCURRENT_REQUESTS is being scanned heavily to provide the results The results may not be 100% accurate as some information is not available anymore DOWNLOAD the scripts from the UKOUG site

© 2011 Pythian Analyzing the pending time (1) APPS@TEST > @cr_pend_time_1.sql 01082011_000000 01092011_000000 AP_ID PR_ID SG_ID PROGRAM REQ_CNT PEND_TOT PEND_CRM PEND_CM EX ------ ------- --------------- ------------------------------------------- --------- --------- --------- --------- --- ... 0 31659 140-620 Report Set (Depreciation Run Request Set 1 7 0 7 (Corporate Book - Close Period) (1)) ... 702 32763 "" Overhead Cost Worker 1409 34 4 29 702 31888 "" Resource Cost Worker 1338 34 4 29 401 33262 "" Create Intercompany AP Invoices 41 35 32 3 401 33263 "" Create Intercompany AR Invoices 1298 37 35 2 160 20392 "" Check Periodic Alert 725 37 5 31 222 20428 "" Autoinvoice Import Program 183 38 34 4 702 32516 "" Copy Item Costs Across Organizations 2156 40 35 5 702 46580 "" Collect Revenue Recognition Information 16 45 41 3 702 46565 "" Record Order Management Transactions 16 46 41 5 706 31915 "" WIP Move Transaction Manager 3415 51 4 47 702 46564 "" Generate COGS Recognition Events 16 91 88 3 724 39450 "" Auto-Release Planned Orders 28 447 444 2 542 44365 "" Install Base Error Correction and 4 597 595 2 Synchronization Program 0 46777 "" FNDIRLOAD 1 1442 1408 34 0 44412 "" Compile Non-Compiled Flexfields 1 1443 0 1443 570 rows selected. Elapsed: 00:00:10.13 16 • Listing average pending/execution times for all concurrent programs (level 1) @cr_pend_time_1.sql <DDMMYYYY_HH24MISS> <DDMMYYYY_HH24MISS>

© 2011 Pythian Analyzing the pending time (2) APPS@TEST > @cr_pend_time_2.sql 01082011_000000 01092011_000000 706 31915 "" DD Session altered. Elapsed: 00:00:00.37 INT_START INT_END AP_ID PR_ID SG_ID REQ_CNT PEND_TOT PEND_CRM PEND_CM EXE_TIME --------------- --------------- ------ ------ --------------- --------- --------- --------- --------- --------- 29082011_000000 29082011_235959 706 31915 "" 250 45 4 41 0 28082011_000000 28082011_235959 706 31915 "" 248 48 3 44 0 21082011_000000 21082011_235959 706 31915 "" 248 48 4 43 0 25082011_000000 25082011_235959 706 31915 "" 247 49 4 45 0 31082011_000000 31082011_235959 706 31915 "" 247 50 4 45 0 23082011_000000 23082011_235959 706 31915 "" 246 50 4 45 0 18082011_000000 18082011_235959 706 31915 "" 226 50 4 46 0 24082011_000000 24082011_235959 706 31915 "" 246 50 4 46 0 26082011_000000 26082011_235959 706 31915 "" 245 52 4 47 0 19082011_000000 19082011_235959 706 31915 "" 244 52 3 49 0 30082011_000000 30082011_235959 706 31915 "" 244 53 5 48 0 22082011_000000 22082011_235959 706 31915 "" 244 54 5 49 0 27082011_000000 27082011_235959 706 31915 "" 243 55 4 51 0 20082011_000000 20082011_235959 706 31915 "" 237 64 5 59 0 14 rows selected. Elapsed: 00:00:00.62 17 • Drill down into «WIP Move Transaction Manager» which has TOP «PEND_CM», see averages per day. @cr_pend_time_2.sql <DDMMYYYY_HH24MISS> <DDMMYYYY_HH24MISS> <AP_ID> <PR_ID> <SG_ID> <DD|HH|MI|SS>

© 2011 Pythian Analyzing the pending time (3) APPS@TEST > @cr_pend_time_2.sql 20082011_000000 20082011_235959 706 31915 "" HH Session altered. Elapsed: 00:00:00.37 INT_START INT_END AP_ID PR_ID SG_ID REQ_CNT PEND_TOT PEND_CRM PEND_CM EXE_TIME --------------- --------------- ------ ------ --------------- --------- --------- --------- --------- --------- ... 20082011_130000 20082011_135959 706 31915 "" 10 60 3 57 0 20082011_140000 20082011_145959 706 31915 "" 10 60 5 55 0 20082011_150000 20082011_155959 706 31915 "" 10 60 6 53 0 20082011_160000 20082011_165959 706 31915 "" 10 60 5 55 0 20082011_170000 20082011_175959 706 31915 "" 10 60 4 56 0 20082011_180000 20082011_185959 706 31915 "" 10 60 3 56 0 20082011_040000 20082011_045959 706 31915 "" 10 60 6 53 0 20082011_030000 20082011_035959 706 31915 "" 10 60 5 55 0 20082011_020000 20082011_025959 706 31915 "" 10 60 6 54 0 20082011_230000 20082011_235959 706 31915 "" 10 60 7 53 0 20082011_000000 20082011_005959 706 31915 "" 10 60 1 58 0 20082011_010000 20082011_015959 706 31915 "" 10 60 4 56 0 20082011_220000 20082011_225959 706 31915 "" 10 60 6 54 0 20082011_200000 20082011_205959 706 31915 "" 6 316 18 298 0 24 rows selected. Elapsed: 00:00:00.78 18 • Drill down even more, see averages per hour. @cr_pend_time_2.sql <DDMMYYYY_HH24MISS> <DDMMYYYY_HH24MISS> <AP_ID> <PR_ID> <SG_ID> <DD|HH|MI|SS>

© 2011 Pythian Analyzing the pending time (4) APPS@TEST > @cr_pend_time_3.sql 20082011_200000 20082011_205959 706 31915 "" Session altered. Elapsed: 00:00:00.36 AP_ID PR_ID SG_ID REQUEST_ID REQUESTED_START PEND_TOT PEND_CRM PEND_CM EXE_TIME ------ ------ --------------- ---------- --------------- --------- --------- --------- --------- 706 31915 "" 16914027 20082011_204843 0 0 0 0 706 31915 "" 16914097 20082011_205844 0 0 0 0 706 31915 "" 16914065 20082011_205343 1 0 1 0 706 31915 "" 16913876 20082011_203732 5 1 4 0 706 31915 "" 16913971 20082011_204237 66 65 1 0 706 31915 "" 16913806 20082011_200204 1826 43 1783 2 6 rows selected. Elapsed: 00:00:00.58 19 • As only 6 requests there, list the requests @cr_pend_time_3.sql <DDMMYYYY_HH24MISS> <DDMMYYYY_HH24MISS> <AP_ID> <PR_ID> <SG_ID> • We Identify 1 request that had been pending for a long time. What next?

© 2011 Pythian Analyzing the pending time (5) APPS@TEST > @which_cm_can_exec_cr.sql 16913806 Session altered. Elapsed: 00:00:00.37 QUEUE_ID CONCURRENT_QUEUE_NAME USER_CONCURRENT_QUEUE_NAME SLEEP_SECONDS --------- ------------------------------ --------------------------------- ------------- 10 INVMGR Inventory Manager 60 1 row selected. Elapsed: 00:00:00.58 20 • Report which concurrent managers could have executed this request @which_cm_can_exec_cr.sql <request_id>

© 2011 Pythian Analyzing the pending time (6) APPS@TEST > @wait_for_cm.sql 16913806 Session altered. Elapsed: 00:00:00.38 This reports manager activity during time when request 16913806 was waiting for a free concurrent manager. 16913806 was waiting for a free manager during this time interval: REQUEST_ID RELEASED_BY_CRM ACTUAL_START_DA ---------- --------------- --------------- 16913806 20082011_200247 20082011_203230 1 row selected. Elapsed: 00:00:00.56 Summary of concurrent manager process activity, only managers that could have executed request 16913806 are listed CONC_Q CONC_PROC_ID PROC_START PROC_STOP UPTIME_SEC REQ_CNT BUSY_SEC BUSY_PCT AVG_DELAY SLEEP_SEC ------ ------------ --------------- --------------- ---------- -------- --------- --------- ---------- ---------- INVMGR 321747 09082011_161505 20082011_200305 17 0 0 60 INVMGR 321822 20082011_203229 22082011_195814 1 1 0 0 0 60 2 rows selected. Elapsed: 00:00:01.16 21 • Report the concurrent manager activity that caused our concurrent request to be pending @wait_for_cm.sql <request_id>

© 2011 Pythian • Let’s verify one of concurrent programs with high «pending for conflict resolution manager» statistics @cr_pend_time_1.sql <DDMMYYYY_HH24MISS> <DDMMYYYY_HH24MISS> APPS@TEST > @cr_pend_time_1.sql 01082011_000000 01092011_000000 AP_ID PR_ID SG_ID PROGRAM REQ_CNT PEND_TOT PEND_CRM PEND_CM EX ------ ------- --------------- ------------------------------------------- --------- --------- --------- --------- --- ... 0 31659 140-620 Report Set (Depreciation Run Request Set 1 7 0 7 (Corporate Book - Close Period) (1)) ... 702 32763 "" Overhead Cost Worker 1409 34 4 29 702 31888 "" Resource Cost Worker 1338 34 4 29 401 33262 "" Create Intercompany AP Invoices 41 35 32 3 401 33263 "" Create Intercompany AR Invoices 1298 37 35 2 160 20392 "" Check Periodic Alert 725 37 5 31 222 20428 "" Autoinvoice Import Program 183 38 34 4 702 32516 "" Copy Item Costs Across Organizations 2156 40 35 5 702 46580 "" Collect Revenue Recognition Information 16 45 41 3 702 46565 "" Record Order Management Transactions 16 46 41 5 706 31915 "" WIP Move Transaction Manager 3415 51 4 47 702 46564 "" Generate COGS Recognition Events 16 91 88 3 724 39450 "" Auto-Release Planned Orders 28 447 444 2 542 44365 "" Install Base Error Correction and 4 597 595 2 Synchronization Program 0 46777 "" FNDIRLOAD 1 1442 1408 34 0 44412 "" Compile Non-Compiled Flexfields 1 1443 0 1443 570 rows selected. Elapsed: 00:00:10.13 Analyzing the pending time (7) 22

© 2011 Pythian Analyzing the pending time (8) APPS@TEST > @cr_pend_time_3.sql 01082011_000000 01092011_000000 724 39450 "" Session altered. Elapsed: 00:00:00.36 AP_ID PR_ID SG_ID REQUEST_ID REQUESTED_START PEND_TOT PEND_CRM PEND_CM EXE_TIME ------ ------ --------------- ---------- --------------- --------- --------- --------- --------- ... 724 39450 "" 16907715 20082011_070517 439 438 1 6 724 39450 "" 17045759 28082011_190251 443 440 3 8 724 39450 "" 16951457 23082011_070434 444 444 0 5 724 39450 "" 17029897 27082011_070508 444 444 0 6 724 39450 "" 16964541 23082011_190243 445 445 0 6 724 39450 "" 17053073 29082011_070518 448 444 4 6 724 39450 "" 16890020 19082011_070217 455 454 1 5 724 39450 "" 16913143 20082011_190209 456 455 1 6 724 39450 "" 17024120 26082011_190555 460 452 8 8 724 39450 "" 17035465 27082011_190213 464 462 2 8 724 39450 "" 17004807 25082011_190222 490 490 0 7 724 39450 "" 16984776 24082011_190238 529 526 3 5 724 39450 "" 17105987 31082011_190228 530 525 5 5 724 39450 "" 16882367 18082011_190239 541 536 5 5 28 rows selected. Elapsed: 00:00:00.74 23 • As there’s only 28 requests, will check them all, no interval reporting needed. @cr_pend_time_3.sql <DDMMYYYY_HH24MISS> <DDMMYYYY_HH24MISS> <AP_ID> <PR_ID> <SG_ID>

© 2011 Pythian Analyzing the pending time (9) APPS@TEST > @wait_crm_incomp_crs.sql 16882367 Session altered. Elapsed: 00:00:00.36 Pending request: PEND_REQ_ID PEND_CRM_START PEND_CRM_END ----------- --------------- --------------- 16882367 18082011_190239 18082011_191135 1 row selected. Elapsed: 00:00:00.55 Incompatible requests that were running at the time request 16882367 was pending: INCOMP_REQ_ID INCOMP_REQ_STAR INCOMP_REQ_END GAP_SEC INCOMP_PROGRAM ------------- --------------- --------------- ------- ------------------------------------------------------------ 16882313 18082011_190011 18082011_190259 Memory-Based Snapshot 16882366 18082011_190232 18082011_191128 0 Memory Based Planner 64-bit Linux 2 rows selected. 24 • Check why the CRM did not let the request run for ~9 minutes. Were there any incompatible requests running at that time? @wait_crm_incomp_crs.sql <request id> • Two incompatible requests were running, there might be a need to change some schedules.

© 2011 Pythian Analyzing CM utilization (1) APPS@TEST > @cm_util_1.sql 01082011_000000 01092011_000000 Session altered. Elapsed: 00:00:00.37 Q_ID CONC_Q PROC_ID PROC_START PROC_STOP PROC_CNT UPTIME_SEC REQ_CNT BUSY_SEC BUSY_PCT AVG_DELAY S ------ --------- -------- --------------- --------------- -------- ----------- -------- --------- -------- ---------- -- ... 0 STANDARD 321935 26082011_134152 22092011_200048 1 469087 3502 34491 7.35 124 0 STANDARD 321914 22082011_201336 24082011_022757 1 108861 852 8121 7.46 118 0 STANDARD 321789 17082011_134210 20082011_200257 1 282047 1787 22506 7.98 145 0 STANDARD 321903 22082011_201336 31082011_091803 1 738267 5360 60501 8.20 126 0 STANDARD 321897 22082011_201336 23082011_110447 1 53471 388 4899 9.16 124 0 STANDARD 321941 31082011_093639 22092011_173532 1 51801 519 4872 9.41 90 0 STANDARD 321905 22082011_201336 26082011_134152 1 322095 2658 34604 10.74 108 0 STANDARD 321940 31082011_091803 22092011_200052 1 52917 426 11289 21.33 97 1024 IEU_SH_CS 3 1926041 0 0 .00 1926041 1025 IEU_WL_CS 3 1926041 0 0 .00 1926041 40 PASMGR 3 1925981 0 0 .00 1925981 1063 OAMCOLMGR 3 1926035 107 273 .01 17831 10 INVMGR 3 1926009 10172 2291 .12 189 0 STANDARD 68 36980330 175133 1319097 3.57 203 144 rows selected. Elapsed: 00:00:07.35 26 • Reporting concurrent manager busyness percent (busy = a request is being run by the manager) @cm_util_1.sql <DDMMYYYY_HH24MISS> <DDMMYYYY_HH24MISS>

© 2011 Pythian Analyzing CM utilization (2) APPS@TEST > @cm_util_2.sql 01082011_000000 01092011_000000 0 HH Session altered. Elapsed: 00:00:00.37 INT_START INT_END Q_ID CONCURRENT_QUEUE_NAME PROC_CNT UPTIME_SEC REQ_CNT BUSY_SEC BUSY_PCT --------------- --------------- ------ ------------------------------ -------- ----------- -------- --------- -------- ... 29082011_090000 29082011_095959 0 STANDARD 20 71980 1022 15764 21.90 29082011_100000 29082011_105959 0 STANDARD 20 71980 1132 14707 20.43 29082011_110000 29082011_115959 0 STANDARD 20 71980 1004 13889 19.30 29082011_120000 29082011_125959 0 STANDARD 20 71980 791 4884 6.79 29082011_130000 29082011_135959 0 STANDARD 20 71980 934 3761 5.23 ... 31082011_180000 31082011_185959 0 STANDARD 20 71980 438 1520 2.11 31082011_190000 31082011_195959 0 STANDARD 20 71980 714 6634 9.22 31082011_200000 31082011_205959 0 STANDARD 20 71980 358 4607 6.40 31082011_210000 31082011_215959 0 STANDARD 20 71980 579 8849 12.29 31082011_220000 31082011_225959 0 STANDARD 20 71980 532 7868 10.93 31082011_230000 31082011_235959 0 STANDARD 20 71980 366 3991 5.54 0 STANDARD 68 36970022 175743 1318843 3.57 537 rows selected. Elapsed: 00:00:31.46 27 • I’ll drill down into hourly intervals to create a trend graph @cm_util_2.sql <DDMMYYYY_HH24MISS> <DDMMYYYY_HH24MISS> <CM_queue_ID> <DD|HH|MI|SS>

© 2011 Pythian Analyzing CM utilization (2) 28 0 5 10 15 20 25 19082011_000000 19082011_070000 19082011_140000 19082011_210000 20082011_040000 20082011_110000 20082011_180000 21082011_010000 21082011_080000 21082011_150000 21082011_220000 22082011_050000 22082011_120000 22082011_190000 23082011_020000 23082011_090000 23082011_160000 23082011_230000 24082011_060000 24082011_130000 24082011_200000 25082011_030000 25082011_100000 25082011_170000 26082011_000000 26082011_070000 26082011_140000 26082011_210000 27082011_040000 27082011_110000 27082011_180000 28082011_010000 28082011_080000 28082011_150000 28082011_220000 29082011_050000 29082011_120000 29082011_190000 30082011_020000 30082011_090000 30082011_160000 30082011_230000 31082011_060000 31082011_130000 31082011_200000 BUSY_PCT BUSY_PCT Linear (BUSY_PCT) • Graphing the data can help you identify peaks and trends • Don’t trust trends based on short term data. Activity might be cyclic over month. Get graphs for few months at least

© 2011 Pythian Analyzing CM utilization (2) APPS@TEST > @cm_util_2.sql 29082011_090000 29082011_095959 0 MI Session altered. Elapsed: 00:00:00.42 INT_START INT_END Q_ID CONCURRENT_Q PROC_CNT UPTIME_SEC REQ_CNT BUSY_SEC BUSY_PCT --------------- --------------- ------ ------------- -------- ----------- -------- --------- -------- 29082011_090000 29082011_090059 0 STANDARD 20 1180 24 65 5.51 29082011_090100 29082011_090159 0 STANDARD 20 1180 10 10 .85 29082011_090200 29082011_090259 0 STANDARD 20 1180 5 8 .68 29082011_090300 29082011_090359 0 STANDARD 20 1180 24 115 9.75 ... 29082011_093200 29082011_093259 0 STANDARD 20 1180 15 259 21.95 29082011_093300 29082011_093359 0 STANDARD 20 1180 73 576 48.81 29082011_093400 29082011_093459 0 STANDARD 20 1180 17 411 34.83 29082011_093500 29082011_093559 0 STANDARD 20 1180 77 560 47.46 29082011_093600 29082011_093659 0 STANDARD 20 1180 24 364 30.85 ... 29082011_095700 29082011_095759 0 STANDARD 20 1180 40 458 38.81 29082011_095800 29082011_095859 0 STANDARD 20 1180 30 320 27.12 29082011_095900 29082011_095959 0 STANDARD 20 1180 17 460 38.98 0 STANDARD 20 70800 1375 15502 21.90 61 rows selected. Elapsed: 00:00:08.94 29 • I’ll drill down into minute intervals to identify CM utilization peaks @cm_util_2.sql <DDMMYYYY_HH24MISS> <DDMMYYYY_HH24MISS> <CM_queue_ID> <DD|HH|MI|SS>

© 2011 Pythian Analyzing CM utilization (2) APPS@TEST > @cm_util_3 29082011_093300 29082011_093359 0 Session altered. Elapsed: 00:00:00.42 The following table reports concurrent requests that ran during interval 29082011_093300 and 29082011_093359 Q_ID CONC_QUEUE_NAME PROC_ID REQUEST_ID REQ_START REQ_STOP PROGRAM ------ -------------------- -------- ---------- --------------- --------------- ------------------------------------- 0 STANDARD 321934 17055105 29082011_092621 29082011_093406 Create Accounting - Cost Management 0 STANDARD 321904 17055250 29082011_093130 29082011_100357 Request Set Planning Data Collection 0 STANDARD 321904 17055252 29082011_093131 29082011_094638 Report Set (Planning Data Collection) Stage (Planning Data Pull) ... 0 STANDARD 321903 17055293 29082011_093324 29082011_093324 Refresh Collection Snapshots 0 STANDARD 321903 17055179 29082011_093325 29082011_093326 Create Internal Orders ... 0 STANDARD 321935 17055338 29082011_093341 29082011_093347 Refresh Collection Snapshots 0 STANDARD 321913 17055349 29082011_093342 29082011_093346 Debrief Posting Program 0 STANDARD 321913 17055342 29082011_093342 29082011_093342 Refresh Collection Snapshots 0 STANDARD 321903 17055355 29082011_093357 29082011_093358 Interface Trip Stop 30 • Check what requests where running at that time: @cm_util_3.sql <DDMMYYYY_HH24MISS> <DDMMYYYY_HH24MISS> <CM_queue_ID> • If that doesn’t help, can run analysis of pending time on the same time interval to see if there are issues.

© 2011 Pythian Sleep seconds • Sleep seconds = Time to wait between 2 attempts to fetch the requests queue • If you have multiple manager processes, sleep time is the max time to wait until the new request is picked up from the queue. • e.g. 5 Standard managers, sleep time 60 saeconds = average time to pick up the new request from the queue if all managers are idle: 60/5 = ~12 seconds. • Nothing is guaranteed = works only if at least 1 CM process is idle • Guideline for tuning: • Set «sleep seconds» = avg requirement * processes * (1 - utilization) • Example 1: Req = 20s, util =20%, proc = 3 • 20 * 3 * (1 - 0.2) = 20 * 3 * .8 = 48 seconds • Example 2: Req = 20s, util =90%, proc = 3 • 20 * 3 * (1 - 0.9) = 20 * 3 * .1 = 6 seconds , less then requirement? The problem is somewhere else! 32

© 2011 Pythian Cache size • Cache size = The number of pending requests being fetched for execution from the requests queue • What cache size to choose? • Small cache sizes are better for managers executing long running requests • Small cache sizes are better for managers with lots of processes • Larger cache size is good for managers with few processes executing quick (or both quick and slow) requests • Larger cache size is good if you have a lot of new requests incoming all the time • If at least one request was fetched, the queue will be checked again right after all the cached requests have been processed • This reduces the significance of the cache size • Guideline for tuning: • None! The effects are usually insignifficant 33

© 2011 Pythian Number of processes / Workshifts / Dedicated concurrent managers • Separate different types of requests by creating dedicated managers • Separate processing of slow / fast / critical requests • Adjust sleep seconds / cache sizes / number of processes based on requirements • Consider implementing workshifts if: • There are signifficant differences in utilization levels of managers at differenet periods of time • There’s a need to change priorities of the workloads (batch / oltp) at different periods of time • Guideline for tuning: Have at least 1 idle manager process at any moment of time 34

© 2011 Pythian Summary • There’s no out of the box functionality to • estimate efficiency of the configuration of the concurrent processing • find out the root causes of various concurrent processing related issues • Complex data model requires working around multiple issues to build the custom tools • You don’t have to build these tools – I’ve done that for you! • There are just few tuning options available • Change the configuration bit by bit and monitor the effects of the change. 35

© 2011 Pythian ? Tweet about the event: @MarisElsins and #ukoug2011 Give your feedback by filling the evaluation forms! Ask more questions: elsins@pythian.com Blog posts on this topic will follow: http://www.pythian.com/news/author/elsins/ Follow Pythian on twitter @pythian and LinkedIn http://linkd.in/pythian 36

Concurrent Processing Performance Analysis for Apps DBAs

More Related Content

What's hot

Viewers also liked

Similar to Concurrent Processing Performance Analysis for Apps DBAs

More from Maris Elsins

Recently uploaded

Concurrent Processing Performance Analysis for Apps DBAs