This is the last in a series of 4 posts about the selective use of cursor sharing in scheduled processes in PeopleSoft.
- Introduction
- What happens during SQL Parse? What is a 'hard' parse? What is a 'soft' parse? The additional overhead of a hard parse.
- How to set CURSOR_SHARING for specific scheduled processes.
- How to identify candidate processes for cursor sharing.
In this article, I look at a method to identify candidate processes for cursor sharing. Then it is necessary to test whether cursor sharing actually is beneficial.
My example is based on nVision reports in a PeopleSoft Financials system, but the technique can be applied to other processes and is not even limited to PeopleSoft. nVision reports example because they vary from report to report, depending upon how they are written, and the nature of the reporting trees they use. Some nVision reports benefit from cursor sharing, others it makes little difference, and for some it is detrimental.
As always Active Session History (ASH) is your friend. First, you need to know which ASH data relates to which process, so you need to enable PeopleSoft instrumentation (see Effective PeopleSoft Performance Monitoring), and install my psftapi package and trigger to enable instrumentation of Cobol, nVision and SQR.
Candidates for Cursor Sharing
Use ASH for a given process to identify candidate processes by calculating the following measures.
- Elapsed time of the process from the first to the last ASH sample. This is not the elapsed duration of the client process, but it will be a reasonable approximation. Otherwise, you can get the exact duration from the process request record (PSPRCSRQST).
- Total database time for a process (all ASH samples).
- Total time that a process is restrained by the resource manager (where EVENT is 'resmgr: CPU quantum')
- Total database time spent on CPU (where EVENT is null).
- Total database time spent on SQL parse (where IN_PARSE flag is set to Y)
- Number of distinct SQL IDs.
- Number of distinct force matching signatures.
Look for processes with high elapsed time, of which a significant proportion is spent on both CPU and SQL parse. This should correlate with processes where there are many more SQL IDs than force matching signatures.
Is Cursor Sharing Enabled Already?
It is possible to determine whether cursor sharing is already set for a process, although this is not explicitly recorded.
- If cursor sharing is not enabled then the number of distinct SQL_IDs should be greater than the number of distinct force-matching signatures. This may not be the case if you don't have enough ASH samples, but then the program probably doesn't consume enough time for it to be worth considering cursor sharing.
- If the number of SQL_IDs is equal to the number of force matching signatures then cursor sharing is probably enabled, but again this could be unreliable if the number of ASH samples is low (and close to the number of SQL IDs).
- It should be impossible for the number of distinct SQL IDs to be less than the number of distinct force matching signatures, but it can happen due to quirks in ASH sampling.
Sample Queries and Output
I have written a couple of queries that I have published on GitHub. They happen to be specific to nVision, but can easily be extended to other processes.
- The first query calculates average values for each process/run control ID combination within the AWR retention period (high_parse_nvision_avg.sql)
Having implemented cursor sharing for a particular process it is necessary to watch it over time and decide whether the change has been effective. The metrics shown below come from a real system (although actual run control IDs have been changed).
- All the timings for NVS_RPTBOOK_1 have come down significantly. The number of SQL_IDs has dropped from 238 to 11. The number of force matching signatures has also dropped, but that is because we have fewer ASH samples and some statements are no longer sampled at all. Cursor sharing is beneficial and can be retained.
- However, this is not the case for the second process. Although NVS_RPTBOOK_2 looked like a good candidate for cursor sharing, and the parse time has indeed come down, all the other durations have gone up. The cursor sharing setting will have to be removed for this report.
Cursor Avg StdDev Avg StdDev Avg StdDev Avg StdDev Avg StdDev Avg
Cursor Sharing Num Elap Elap ASH ASH ResMgr ResMgr Parse Parse CPU CPU SQL Avg
OPRID RUNCNTLID Sharing Setting Procs Secs Secs Secs Secs Secs Secs Secs Secs Secs Secs IDs FMS
---------- ------------------------ ------- ------- ----- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ----- ----
…
NVISION NVS_RPTBOOK_1 EXACT FORCE 33 3691 1062 2687 1071 741 702 2232 932 1791 479 238 16
FORCE FORCE 13 1623 377 664 394 357 373 43 19 353 85 11 12
…
NVS_RPTBOOK_2 EXACT EXACT 39 3696 1435 3316 1431 1038 927 1026 661 2042 611 137 27
FORCE EXACT 7 4028 2508 3676 2490 1333 1563 17 12 2275 939 19 19
It is always worth looking at individual process executions.
- The second script (high_parse_nvision.sql) runs a similar query, but it reports each process individually.
We can see that cursor sharing was introduced on 31st July. Even though there is a lot of variance in runtimes due to variances in data volumes and other system activities, it is clear that cursor sharing is beneficial for this process.
Cursor
Process R Elap ASH ResMgr Parse CPU SQL ASH Sharing Cursor Parse S:F
OPRID RUNCNTLID Instance S MIN_SAMPLE_TIME MAX_SAMPLE_TIME Secs Secs Secs Secs Secs IDs FMS Samp Setting Sharing % Ratio
---------- ---------------- --------- -- -------------------- -------------------- ------ ------ ------ ------ ------ ----- ---- ----- ------- ------- ----- -----
NVISION NVS_RPTBOOK_1 12447036 9 21.07.2024 21.03.25 21.07.2024 21.47.02 2645 1543 174 1297 1277 145 17 150 FORCE EXACT 84 8.5
12452568 9 22.07.2024 21.02.04 22.07.2024 21.41.03 2373 1413 123 1188 1250 133 13 138 FORCE EXACT 84 10.2
12458455 9 23.07.2024 21.07.15 23.07.2024 21.52.25 2759 1587 51 1372 1423 152 14 155 FORCE EXACT 86 10.9
12465042 9 24.07.2024 20.58.08 24.07.2024 21.50.19 3154 2100 369 1782 1557 201 18 205 FORCE EXACT 85 11.2
12471732 9 25.07.2024 21.25.34 25.07.2024 22.46.32 4885 3861 1946 3318 1843 333 14 377 FORCE EXACT 86 23.8
12477118 9 26.07.2024 22.41.07 26.07.2024 23.26.07 2730 1791 113 1526 1586 173 14 174 FORCE EXACT 85 12.4
12479163 9 27.07.2024 23.13.40 28.07.2024 00.01.23 2917 1688 161 1513 1260 156 14 164 FORCE EXACT 90 11.1
12480710 9 28.07.2024 21.47.44 28.07.2024 22.29.08 2529 1586 205 1320 1238 149 12 154 FORCE EXACT 83 12.4
12487744 9 29.07.2024 21.47.44 29.07.2024 22.51.05 3834 2815 797 2292 1843 248 16 273 FORCE EXACT 81 15.5
12495417 9 30.07.2024 22.57.13 30.07.2024 23.46.48 3015 2084 307 1869 1592 200 15 203 FORCE EXACT 90 13.3
…
12501446 9 31.07.2024 21.27.51 31.07.2024 21.51.18 1478 461 72 31 389 10 11 45 FORCE FORCE 7 0.9
12507769 9 01.08.2024 21.44.01 01.08.2024 22.05.56 1387 357 100 21 246 7 8 34 FORCE FORCE 6 0.9
12513527 9 02.08.2024 21.02.27 02.08.2024 21.27.47 1538 635 236 31 400 11 12 62 FORCE FORCE 5 0.9
12515368 9 03.08.2024 22.12.50 03.08.2024 22.40.03 1682 686 143 51 532 9 10 67 FORCE FORCE 7 0.9
12516959 9 04.08.2024 21.38.01 04.08.2024 21.57.00 1263 266 51 266 8 9 26 FORCE FORCE 19 0.9
12522863 9 05.08.2024 21.14.36 05.08.2024 21.48.40 2082 1167 727 51 430 14 13 114 FORCE EXACT 4 1.1
12529263 9 06.08.2024 21.02.59 06.08.2024 21.39.47 2223 1300 900 51 389 12 13 126 FORCE FORCE 4 0.9
12535782 9 07.08.2024 21.08.23 07.08.2024 21.37.48 1774 974 585 52 379 12 13 94 FORCE FORCE 5 0.9
12541727 9 08.08.2024 21.07.43 08.08.2024 21.40.54 2014 1085 809 51 276 16 17 106 FORCE FORCE 5 0.9
12547232 9 09.08.2024 21.27.28 09.08.2024 21.47.08 1213 236 31 236 8 9 23 FORCE FORCE 13 0.9
…
Note that on 5th August the report erroneously claims that cursor sharing went back to EXACT. This is because there are more SQL_IDs than force matching signatures. Again, this is a quirk of ASH sampling.
@go-faster.co.uk on BlueSky
Posts
No comments :
Post a Comment