tag:blogger.com,1999:blog-257403362024-03-11T12:45:08.186+00:00The PeopleSoft DBA BlogThis blog contains things about PeopleSoft, mostly performance related, that DBAs might find interesting.<br>Or then again they might not! The non-PeopleSoft Oracle stuff is at <a href="http://blog.go-faster.co.uk">blog.go-faster.co.uk</a>.David Kurtzhttp://www.blogger.com/profile/00468908370233805717noreply@blogger.comBlogger172125tag:blogger.com,1999:blog-25740336.post-78269418303781631912024-03-11T11:55:00.003+00:002024-03-11T12:44:36.241+00:00PSFT_PLAN: A Sample Oracle Database Resource Manager Plan for PeopleSoft<div style="text-align: left;">In the cloud (or any virtualised environment), performance is instrumented as cost. This is also true in any other on-premises environment, but it takes a lot longer to feedback!</div><div><ul style="text-align: left;"><li>If you never run out of CPU, then you have probably bought/rented/allocated/licensed too many CPUs.</li><li>If you do run out of CPU, then you should use the database resource manager to prioritise the processes that are most important to the business.</li><li>If you don't enable the resource manager, you will have less visibility of when you do run out of CPU.</li></ul></div><div>At the very least, you can use one of the sample resource manager plans installed in the Oracle database by default. </div><div>This article proposes a <a href="https://github.com/davidkurtz/psscripts/blob/master/psft_resource_plan_simple.sql" target="_blank">resource plan for PeopleSoft</a> systems. It can be used as a starting point before enhancing it with your own specific requirements. </div><div><ul style="text-align: left;"><li>See also Go-Faster Blog: <a href="https://blog.go-faster.co.uk/2023/06/more-bang-for-your-buck-in-cloud-with.html">More Bang for your Buck in the Cloud with Resource Manager</a></li></ul></div><p class="MsoListParagraph" style="mso-list: l0 level1 lfo1; text-indent: -18pt;"><o:p></o:p></p><h3 style="text-align: left;">Resource Plan Design Goals</h3><div><div>The purpose of a database resource plan is to prioritise important/urgent processes over less important/less urgent processes by allocating CPU, to the higher priority processes, and by restricting CPU, other resources, and the degree of parallelism for lower priority processes.</div><div>The design of a resource plan should reflect what the business defines as important. </div></div><div><h3 style="text-align: left;">Consumer Groups</h3><div>A resource plan consists of several resource groups with different priorities, and resource allocations. Each priority level defined by the business becomes a consumer group in the resource plan. A consumer group can be allocated to one of 8 priority levels in a resource plan. Multiple consumer groups can exist at the same priority level with different CPU guarantees (adding up to not more than 100%) and can include other limits.</div><div>I have made some assumptions about process priorities in a typical PeopleSoft system, and have grouped and ranked them in the table below starting with the highest priority. Not all customers run all these processes. Consumer groups and mappings that are not needed can be omitted. There are gaps in the priority levels to allow for other definitions to be introduced.</div></div>
<table border="1" bordercolor="#808080" cellspacing="0" style="width: 100%;" valign="top">
<tbody><tr><th style="width: 9%;">Priority Level</th>
<th style="width: 10%;">Consumer Group</th>
<th style="width: 11%;">%CPU Guarantee</th>
<th>Comment</th></tr>
<tr><td align="center">1</td><td>SYS<br />_GROUP</td><td align="center">100%</td><td>
Oracle system processes. Defined automatically.</td></tr>
<tr><td align="center">2</td><td>PSFT<br />_GROUP</td><td align="center">100%</td><td>
Any process that connects to the database as either SYSADM (the default
PeopleSoft owner ID) or PS has higher priority than other processes unless other rules apply. The online application (other than ad hoc query) falls into this category so that the online user experience is safeguarded before other PeopleSoft processes. <br />
This includes remote call Cobol processes, but not remote call Application
Engine that should be run in the component processor.</td></tr>
<tr><td align="center">4</td><td>BATCH<br />_GROUP</td><td align="center">100%</td><td>
Process scheduler processes, and processes run by the process schedulers</td></tr>
<tr><td align="center">5</td><td>NVISION<br />_GROUP</td><td align="center">100%</td><td>
nVision (NVSRUN) and nVision report book (RPTBOOK) processes</td></tr>
<tr><td align="center" rowspan="3">6</td><td>PSQUERY<br />_ONLINE<br />_GROUP</td><td align="center">90%</td><td>Ad hoc queries are allocated to one of three consumer groups with the same priority, but different CPU guarantees, comprising:
<ul><li>on-line PS/Queries, </li><li>nVision reports run through the PIA,</li></ul></td></tr>
<tr><td>PSQUERY<br />_BATCH<br />_GROUP</td><td align="center">9%</td><td>
<ul><li>PS/Queries run on the process scheduler using the PSQUERY application engine. A 4-hour maximum runtime limit is defined.</li></ul></td></tr>
<tr><td>NVSRUN<br />_GROUP</td><td align="center">1%</td><td><ul><li>nVision through the 3-tier nVision client</li></ul></td></tr>
<tr><td align="center" rowspan="3">8</td><td>LOW<br />_GROUP</td><td style="text-align: center;">1%</td><td>Other low-priority processes</td></tr>
<tr><td>LOW<br />_LIMITED<br />_GROUP</td><td style="text-align: center;">1%</td><td>Other low-priority processes, but whose maximum query time is limited.</td></tr>
<tr><td>OTHER<br />_GROUPS</td><td style="text-align: center;">1%</td><td>All other processes. Defined automatically.</td></tr>
</tbody></table>
<div>See also <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_RESOURCE_MANAGER.html#GUID-5641221D-A03C-46DE-92D9-1DA400668385" target="_blank">DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE</a></div><div><h3 style="text-align: left;">Consumer Group Mapping Priority</h3><div>Sessions are allocated to the consumer groups. They can be allocated explicitly, or via mapping rules that use various session attributes. As the attributes are set or changed, the consumer group will be set according to the matching rules. </div><div>I have set the following attributes to be mapped in the following order of precedence. The more specific mappings take precedence over the more generic ones.</div></div>
<table border="1" bordercolor="#808080" cellspacing="0" style="width: 100%;" valign="top">
<tbody><tr><th style="width: 9%;">Priority</th><th style="width: 10%;">Mapping
Attribute</th><th style="width: 80%;">
Comment</th></tr>
<tr><td align="center">2</td><td>Module, Action</td><td>The PIA
instrumentation sets attributes MODULE to the component name and ACTION
to the page name. Specific component pages are allocated to specific
consumer groups</td></tr>
<tr><td align="center">3</td><td>Module</td><td>Specific scheduled processes are allocated by name to specific consumer groups. PeopleSoft instrumentation puts this name in the MODULE attribute.</td></tr>
<tr><td align="center">4</td><td>Client Program</td><td>Batch and query processes are identified by program name and allocated to certain consumer groups. </td></tr>
<tr><td align="center">5</td><td>Oracle User</td><td>Anything that connects to the database as either SYSADM or PS is allocated to the PSFT_GROUP. So other mapping rules must take precedence over this mapping.</td></tr>
</tbody></table>
<div><div>See also <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_RESOURCE_MANAGER.html#GUID-32AA7BD1-5884-4C25-A67A-22F48983851B" target="_blank">DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI</a></div><h3 style="text-align: left;">Required PeopleSoft Configuration</h3><div>The PSFT_PLAN sample resource manager plan relies on MODULE and ACTION being set by the PeopleSoft Application. Therefore, the following additional configuration is required.</div><div><ul style="text-align: left;"><li>Enable PeopleSoft instrumentation: Set <a href="https://docs.oracle.com/cd/E92519_02/pt856pbr3/eng/pt/tadm/task_MonitoringPeopleSoftDatabaseConnections-077989.html#u711504d9-3503-43cc-90ae-8c0b800be515_s" target="_blank">EnableAEMonitoring=1</a> in ALL PeopleSoft application server and process scheduler domains so that PeopleSoft processes set MODULE and ACTION information in the session attributes (using <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_APPLICATION_INFO.html" target="_blank">DBMS_APPLICATION_INFO</a>). </li></ul></div></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div><div style="text-align: left;">See also:</div></div></blockquote><div><div><ul style="text-align: left;"><ul><li><a href="https://docs.oracle.com/cd/E92519_02/pt856pbr3/eng/pt/tadm/task_MonitoringPeopleSoftDatabaseConnections-077989.html#u711504d9-3503-43cc-90ae-8c0b800be515_s" target="_blank">PeopleTools PeopleBooks: Administration Tools -> Data Management -> Administering PeopleSoft Databases on Oracle -> Monitoring PeopleSoft MODULE and ACTION Information, Press Enter to collapse</a></li><li>PeopleSoft DBA Blog: <a href="https://blog.psftdba.com/2015/03/undocumented-application-engine.html" target="_blank">Undocumented (until PeopleTools 8.55) Application Engine Parameter: EnableAEMonitoring</a> </li><li>PeopleSoft DBA Blog: <a href="https://blog.psftdba.com/2010/11/peopletools-850-uses.html" target="_blank">PeopleTools 8.50 uses DBMS_APPLICATION_INFO to Identify Database Sessions</a></li><li>Go-Faster Blog: <a href="https://blog.go-faster.co.uk/2016/09/dbmsapplicationinfo.html" target="_blank">One of my Favourite Database Things: DBMS_APPLICATION_INFO</a></li></ul></ul><ul style="text-align: left;"><li>Install instrumentation trigger for PeopleSoft (<a href="https://github.com/davidkurtz/psscripts/blob/master/psftapi.sql" target="_blank">psftapi.sql</a>). Not all PeopleSoft processes are instrumented. COBOL, SQR, and nVision do not set MODULE or ACTION. When a PeopleSoft process is started by the process scheduler, the first thing it does is set its own status to 7, meaning that it is processing. This script creates a database trigger that fires on that DML and sets the session attributes MODULE to the name of the process and ACTION to the process instance number. Application Engine processes may then subsequently update these values again.</li></ul></div><h3 style="text-align: left;">Consumer Group Mappings</h3><div>Consumer groups are matched to session attributes. The highest priority matching mapping is applied. Mappings can be matched to literal values, or with LIKE or REGEXP_LIKE operations.</div></div>
<table border="1" bordercolor="#808080" cellspacing="0" style="width: 100%;" valign="top">
<tbody><tr><th style="width: 8%;">Mapping Priority</th>
<th style="width: 16%;">Attribute</th>
<th style="width: 30%;">Value</th>
<th style="width: 9%;">Consumer<br />Group<br />Priority</th>
<th style="width: 24%;">Consumer Group</th></tr>
<tr><td align="center">2</td><td>MODULE_ACTION</td><td>QUERY_MANAGER.QUERY_VIEWER</td><td align="center">6</td><td>PSQUERY_ONLINE_GROUP</td></tr>
<tr><td align="center" rowspan="3">3</td><td rowspan="3">MODULE</td><td>RPTBOOK<br />NVSRUN</td><td align="center">5</td><td>NVISION_GROUP</td></tr>
<tr><td>PSQRYSRV%</td><td align="center">6</td><td>PSQUERY_ONLINE_GROUP</td></tr>
<tr><td>PSAE.PSQUERY.%</td><td align="center">6</td><td>PSQUERY_BATCH_GROUP</td></tr>
<tr><td align="center" rowspan="5">4</td><td rowspan="5">CLIENT_PROGRAM</td><td>PSRUNRMT</td><td align="center">2</td><td>PSFT_GROUP</td></tr>
<tr><td>psae%<br />PSAESRV%<br />PSDSTSRV%<br />PSMSTPRC%<br />PSRUN@%<br />PSSQR%<br />pssqr%<br />sqr%</td><td align="center">4</td><td>BATCH_GROUP</td></tr>
<tr><td>PSQRYSRV%</td><td align="center">6</td><td>PSQUERY_ONLINE_GROUP</td></tr>
<tr><td>PSNVSSRV%</td><td align="center">6</td><td>NVSRUN_GROUP</td></tr>
<tr><td>SQL Developer<br />sqlplus%<br />Toad%</td><td align="center">8</td><td>LOW_GROUP / LOW_LIMITED_GROUP</td></tr>
<tr><td align="center">5</td><td>ORACLE_USER</td><td>PS<br />SYSADM</td><td align="center">2</td><td>PSFT_GROUP</td></tr>
</tbody></table>
<div><div style="text-align: left;">See also <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_RESOURCE_MANAGER.html#GUID-7B9B9B1D-53E3-4BF4-8BE6-858256702877" target="_blank">DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING</a></div><h3 style="text-align: left;">Resource Plan Script</h3><div>Two SQL scripts are available on GitHub</div><div><ul style="text-align: left;"><li><a href="https://github.com/davidkurtz/psscripts/blob/master/psft_resource_plan_simple.sql" target="_blank">psft_resource_plan_simple.sql</a> creates the resource plan. This is intended to be a starting point to which either unwanted parts can be removed, or additional requirements can be added</li><li><a href="https://github.com/davidkurtz/psscripts/blob/master/resource_plan_report.sql" target="_blank">resource_plan_report.sql</a> reports on all the resource plan metadata.</li></ul></div><h3 style="text-align: left;">Other Options</h3><p style="text-align: left;">There are other resource manager options that are either not illustrated in the sample plan, or that are commented out. They may be worth considering in some situations.</p><div><p></p><ul style="text-align: left;"><li>PeopleSoft does not use parallel query by default, but if you do use it, you may well want to limit which processes use how much parallelism. Consumer groups can specify a limit to the parallel query degree.</li><ul><li>If you use the resource plan to restrict the degree of parallelism, and you also plan to vary the number of CPUs in a cloud environment, then I suggest creating a resource plan for each number of CPUs and switch between the plans by changing the setting of the <a href="https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/RESOURCE_MANAGER_PLAN.html#GUID-BFEF14A4-4822-4410-9B51-DCB3376268B3" target="_blank">RESOURCE_MANAGER_PLAN</a> parameter.</li></ul></ul><p></p></div>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: xx-small;"><code> DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
'PSFT_PLAN', 'NVISION_GROUP', 'nVision Reports.'
,mgmt_p5 => 100
<b> ,parallel_degree_limit_p1=>2</b>
);
</code></span></pre><div><ul style="text-align: left;"><li>A parallel query may queue waiting to obtain sufficient parallel query server processes. A timeout can be specified to limit that wait and to determine the behaviour when the timeout is reached. The query can either be cancelled raising error ORA-07454, or run at a reduced parallelism).</li></ul></div></div><div>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: xx-small;"><code>
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
'PSFT_PLAN', 'PSQUERY_ONLINE_GROUP'
,mgmt_p6 => 90
<b> ,parallel_queue_timeout=>900
,pq_timeout_action=>'RUN'
</b> );
</code></span></pre>
<div><ul style="text-align: left;"><li>A consumer group can restrict queries that run for a long time, or that are expected to run for a long time based on their optimizer cost. They can be switched to the CANCEL_SQL group after a number of seconds and they will terminate with <i>ORA-00040: active time limit exceeded - call aborted:</i>. This has only specified for the LOW_LIMITED_GROUP, and the PSQUERY_BATCH_GROUP for scheduled queries because the message is captured by the process scheduler and logged. It has not been specified for PSQUERY_ONLINE_GROUP because this error is not handled well by the online application. Just the Oracle error message will be displayed to the user without further explanation, which is neither friendly nor helpful. Instead, there are PeopleSoft configuration options to limit query runtime.</li></ul></div>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: xx-small;"><code>DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
'PSFT_PLAN', 'PSQUERY_BATCH_GROUP'
,mgmt_p6 => 1
<b> ,switch_group => 'CANCEL_SQL'
,switch_time => 14400
,switch_estimate => TRUE
,switch_for_call => TRUE
</b> );
</code></span></pre>
<div><ul style="text-align: left;"><li>Sometimes customers may have different priorities and different priorities at different times that cannot be satisfied by a single resource plan. In which case, different resource plans can be activated at different times by different scheduler windows. </li></ul></div><h3 style="text-align: left;">Other Online Resources</h3><div><ul style="text-align: left;"><li>Oracle White Paper: <a href="https://www.oracle.com/technetwork/database/performance/resource-manager-twp-133705.pdf" target="_blank">Using Oracle Database ResourceManager</a></li><li>Documentation: <a href="https://oracle-base.com/articles/10g/resource-manager-enhancements-10g" target="_blank">Oracle Resource Manager Enhancements in Oracle Database 10g</a> </li><li>Documentation: <a href="https://oracle-base.com/articles/12c/resource-manager-enhancements-12cr1" target="_blank">Oracle Resource Manager Enhancements in Oracle Database 12c Release 1 (12.1)</a></li></ul></div></div><div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com0tag:blogger.com,1999:blog-25740336.post-90766701753034766012024-02-20T12:05:00.002+00:002024-02-20T12:10:12.764+00:00What PS/Query is that?<p>Sometimes, performance analysis will turn up a problem SQL query that is probably a PS/Query. However, I need to know which PS/Query it is should I wish to alter it or talk to the user who wrote it. </p><h3 style="text-align: left;">Is it a PS/Query?</h3><p>It is quite easy to spot SQL queries that are generated from queries defined in the PS/Query tool. These are typical characteristics:</p><p></p><ul style="text-align: left;"><li>Single character row source aliases (eg. A, B, D) </li><li>The same row source with a suffix 1 (eg. D1) for query security records.</li><li>Effective date/sequence subqueries are always correlated back to the same table.</li><li>Order by column position number rather than column names or aliases.<br /></li></ul>Sometimes, you may find SQL that looks like a PS/Query coming from other parts of PeopleSoft because a developer has copied the text of a PS/Query, usually into an Application Engine step.<br />
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: xx-small;"><code>SELECT A.EMPLID, A.ATTENDANCE, A.COURSE, B.DESCR, D.NAME, A.SESSION_NBR,
TO_CHAR(A.STATUS_DT,'YYYY-MM-DD'),B.COURSE
FROM PS_TRAINING <b>A</b>, PS_COURSE_TBL <b>B</b>, PS_PERSONAL_DTA_VW <b>D</b>, PS_PERS_SRCH_QRY <b>D1</b>
WHERE D.EMPLID = D1.EMPLID
AND D1.ROWSECCLASS = 'HCDPALL'
AND ( A.COURSE = :1
AND A.ATTENDANCE IN ('S','W')
AND A.COURSE = B.COURSE
AND A.EMPLID = D.EMPLID )<br /></code></span></pre>
<p style="text-align: left;">The text of a PS/Query is not stored in the database. Instead, as with other objects in PeopleSoft, it is held as various rows in PeopleTools tables. The <a href="https://www2.go-faster.co.uk/peopletools/_ptindex.htm#PSQ" target="_blank">PSQRY%</a> tables are used to generate the SQL on demand. We can query these tables to identify the query. </p><div style="text-align: left;"><a href="https://www2.go-faster.co.uk/peopletools/psqryrecord.htm" target="_blank">PSQRYRECORD</a> holds a row for every record referenced in the query (not including effective date/sequence subqueries). My usual tactic is to write a SQL query on PSQRYRECORD, like the one below, that looks for PS/Queries that reference these tables with these table aliases (see <a href="https://www.go-faster.co.uk/p/peoplesoft-for-oracle-dba.html" target="_blank">PeopleSoft for the Oracle DBA</a>, Chapter 11). </div>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: xx-small;"><code>REM <a href="http://findqry.sql" target="_blank">findqry.sql</a>
REM (c)Go-Faster Consultancy 2012
SELECT a.oprid, a.qryname
FROM psqryrecord a
, psqryrecord b
, psqryrecord d
WHERE a.oprid = b.oprid
AND a.qryname = b.qryname
AND a.oprid = d.oprid
AND a.qryname = d.qryname
AND a.corrname = 'A'
AND a.recname = 'TRAINING'
AND b.corrname = 'B'
AND b.recname = 'COURSE_TBL'
AND d.corrname = 'D'
AND d.recname = 'PERSONAL_DTA_VW'
/
</code></span></pre>
<div style="text-align: left;">The example PS/Query above is TRN003__COURSE_WAITING_LIST from the HCM demo database. However, my query on PSQRYRECORD found another PS/Queries with the same 3 records using the same row source aliases. It is worth looking at queries on the same tables as they often suffer from the same problems, and you might want to make the same fix. </div><div style="text-align: left;">Another source of results for this query (though not this time) can be when users copy a public PS/Query to a private one so they can alter it in isolation.</div>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: xx-small;"><code>OPRID QRYNAME
------------------------------ ------------------------------
TRN002__SESSION_ROSTER
TRN003__COURSE_WAITING_LIST
</code></span></pre><p>Writing the query on PSQRYRECORD to find queries, which always is slightly different each time, is quite boring. So I have written a <a href="https://github.com/davidkurtz/psscripts/blob/master/findqry.sql" target="_blank">script</a> that will dynamically generate the SQL to identify a PS/Query.</p><h3 style="text-align: left;">Start with a SQL_ID</h3><div style="text-align: left;">A SQL tuning activity will usually identify the SQL_ID and plan hash value of a statement. If you are lucky, AWR will have captured the text and execution plan. If not, you may have to try looking for a different SQL_ID that produces the same execution plan. From the statement text, it is easy to see whether it might be a PS/Query. </div><div style="text-align: left;">In this example, I have cut the SQL statement and execution plan back to show just the tables and indexes referenced.</div>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: xx-small;"><code>SQL_ID c3h6vf2w5fxgp
--------------------
SELECT …
FROM PSTREELEAF B, PSTREENODE C, PS_OPER_UNIT_TBL A, PS_PRODUCT_TBL G
…
UNION SELECT …
FROM PSTREENODE D,PS_TREE_NODE_TBL E, PSTREELEAF F
…
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------------
| * 7 | INDEX STORAGE FAST FULL SCAN | PSBPSTREELEAF | 426K| 19M| | 1178 (1)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID BATCHED| PS_PRODUCT_TBL | 1 | 41 | | 3 (0)| 00:00:01 |
| * 11 | INDEX RANGE SCAN | PS_PRODUCT_TBL | 1 | | | 2 (0)| 00:00:01 |
| * 14 | INDEX RANGE SCAN (MIN/MAX) | PS_PRODUCT_TBL | 1 | 21 | | 2 (0)| 00:00:01 |
| * 15 | TABLE ACCESS STORAGE FULL | PSTREENODE | 135K| 5709K| | 663 (1)| 00:00:01 |
| * 17 | INDEX STORAGE FAST FULL SCAN | PS_OPER_UNIT_TBL | 1791 | 35820 | | 4 (0)| 00:00:01 |
| * 20 | INDEX RANGE SCAN (MIN/MAX) | PS_PSTREENODE | 1 | 33 | | 3 (0)| 00:00:01 |
| * 23 | INDEX RANGE SCAN (MIN/MAX) | PSAPSTREELEAF | 1 | 32 | | 3 (0)| 00:00:01 |
| * 26 | INDEX RANGE SCAN (MIN/MAX) | PS_OPER_UNIT_TBL | 1 | 20 | | 2 (0)| 00:00:01 |
| 33 | TABLE ACCESS INMEMORY FULL | PS_TREE_NODE_TBL | 35897 | 1647K| | 6 (0)| 00:00:01 |
| * 35 | TABLE ACCESS STORAGE FULL | PSTREENODE | 167K| 9670K| | 663 (1)| 00:00:01 |
|- * 36 | INDEX RANGE SCAN | PS_PSTREELEAF | 1 | 39 | | 1267 (1)| 00:00:01 |
| 37 | INDEX STORAGE FAST FULL SCAN | PS_PSTREELEAF | 480K| 17M| | 1267 (1)| 00:00:01 |
| * 40 | INDEX RANGE SCAN (MIN/MAX) | PS_PSTREENODE | 1 | 33 | | 3 (0)| 00:00:01 |
| * 43 | INDEX RANGE SCAN (MIN/MAX) | PS_TREE_NODE_TBL | 1 | 28 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
…
7 - SEL$1 / B@SEL$1
10 - SEL$1 / G@SEL$1
11 - SEL$1 / G@SEL$1
…
15 - SEL$1 / C@SEL$1
17 - SEL$1 / A@SEL$1
…
33 - SEL$6 / E@SEL$6
35 - SEL$6 / D@SEL$6
36 - SEL$6 / F@SEL$6
37 - SEL$6 / F@SEL$6
…
</code></span></pre><p>I use this query on <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DBA_HIST_SQL_PLAN.html" target="_blank">DBA_HIST_SQL_PLAN</a> to extract the tables that have single-character row source aliases that correspond to PeopleSoft records, and put them into <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/PLAN_TABLE.html" target="_blank">PLAN_TABLE</a>. I use this table because it is delivered by Oracle as a global temporary table, so it is always there and I can make use of it even if I only have read-only access.</p>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: xx-small;"><code>INSERT INTO plan_table (object_name, object_alias)
with p as ( <i>--plan lines with single letter aliases</i>
SELECT DISTINCT object_owner, object_type, object_name, regexp_substr(object_alias,'[[:alpha:]]',2,1) object_alias
from dba_hist_sql_plan p
, ps.psdbowner d
where p.sql_id = '&&sql_id' <i>--put SQL ID here--</i>
and p.object_name IS NOT NULL
and p.object_owner = d.ownerid
and regexp_like(object_alias,'"[[:alpha:]]"') <i>--single character aliases</i>
), r as ( --PeopleSoft table records and the table name
select r.recname, DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) sqltablename
from psrecdefn r
where r.rectype = 0 --PeopleSoft table records
)
select r.recname, object_alias <i>--referenced table</i>
from p, r
where p.object_type like 'TABLE%'
and p.object_name = r.sqltablename
union --a query plan may reference an index and not the table
select r.recname, object_alias <i>--table for referenced index</i>
from p, r
, all_indexes i
where p.object_type like 'INDEX%'
and i.index_name = p.object_name
and i.owner = p.object_owner
and i.table_name = r.sqltablename
order by 2,1
/
</code></span></pre>
I now have a list of records and row source aliases aliases
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: xx-small;"><code>RECNAME O
--------------- -
OPER_UNIT_TBL A
PSTREELEAF B
PSTREENODE C
PSTREENODE D
TREE_NODE_TBL E
PSTREELEAF F
PRODUCT_TBL G
</code></span></pre>
<p>Next, I can run this anonymous PL/SQL block to dynamically build the SQL query on PSQRYRECORD (one reference for every table) and execute it to find the matching PS/Queries</p>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: xx-small;"><code>DECLARE
l_sep1 VARCHAR2(20);
l_sep2 VARCHAR2(20);
l_counter INTEGER := 0;
l_sql CLOB := 'SELECT r1.oprid, r1.qryname';
l_where CLOB;
TYPE t_query IS RECORD (oprid VARCHAR2(30), qryname VARCHAR2(30));
TYPE a_query IS TABLE OF t_query INDEX BY PLS_INTEGER;
l_query a_query;
BEGIN
FOR i IN(
SELECT *
FROM plan_table
ORDER BY object_alias
) LOOP
l_counter := l_counter + 1;
dbms_output.put_line(i.object_alias||':'||i.object_name);
IF l_counter = 1 THEN
l_sep1 := ' FROM ';
l_sep2 := ' WHERE ';
ELSE
l_sep1 := ' ,';
l_sep2 := ' AND ';
l_where := l_where||' AND r1.oprid = r'||l_counter||'.oprid AND r1.qryname = r'||l_counter||'.qryname';
END IF;
l_sql := l_sql||l_sep1||'psqryrecord r'||l_counter;
l_where := l_where||l_sep2||'r'||l_counter||'.corrname = '''||i.object_alias||''' AND r'||l_counter||'.recname = '''||i.object_name||'''';
END LOOP;
l_sql := l_sql||l_where||' ORDER BY 1,2';
dbms_output.put_line(l_sql);
EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_query;
FOR indx IN 1 .. l_query.COUNT
LOOP
DBMS_OUTPUT.put_line (indx||':'||l_query(indx).oprid||'.'||l_query(indx).qryname);
END LOOP;
END;
/
</code></span></pre>
<p>The seven records found in my execution plan become a query of PSQRYRECORD 7 times, one for each record, joined on operator ID and query name.</p><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: xx-small;"><code>SELECT r1.oprid, r1.qryname
FROM psqryrecord r1 ,psqryrecord r2 ,psqryrecord r3 ,psqryrecord r4 ,psqryrecord r5 ,psqryrecord r6 ,psqryrecord r7
WHERE r1.corrname = 'A' AND r1.recname = 'OPER_UNIT_TBL'
AND r1.oprid = r2.oprid AND r1.qryname = r2.qryname AND r2.corrname = 'B' AND r2.recname = 'PSTREELEAF'
AND r1.oprid = r3.oprid AND r1.qryname = r3.qryname AND r3.corrname = 'C' AND r3.recname = 'PSTREENODE'
AND r1.oprid = r4.oprid AND r1.qryname = r4.qryname AND r4.corrname = 'D' AND r4.recname = 'PSTREENODE'
AND r1.oprid = r5.oprid AND r1.qryname = r5.qryname AND r5.corrname = 'E' AND r5.recname = 'TREE_NODE_TBL'
AND r1.oprid = r6.oprid AND r1.qryname = r6.qryname AND r6.corrname = 'F' AND r6.recname = 'PSTREELEAF'
AND r1.oprid = r7.oprid AND r1.qryname = r7.qryname AND r7.corrname = 'G' AND r7.recname = 'PRODUCT_TBL'
ORDER BY 1,2
</code></span></pre><div style="text-align: left;">The query finds several queries. I can look at the public PS/Queries in the Query Manager tool. I can also see which users' private queries exist.<br />NB. You can only open public queries (where OPRID is a single space) or your own private queries. In the Query Manager, you cannot see a private query owned by another user.</div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: xx-small;"><code>…
3: .PS_TREE_PRODUCT
4: .QUERY_PRODUCT_TREE
5: .RM_TREE_PRODUCT
6:XXXXXX.PS_TREE_PRODUCT_XX
…
</code></span></pre>
The new <a href="https://github.com/davidkurtz/psscripts/blob/master/findqry.sql" target="_blank">findqry.sql script is available on Github</a>.<div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com0tag:blogger.com,1999:blog-25740336.post-63094448750875791932024-01-25T14:38:00.000+00:002024-01-25T14:38:29.719+00:00Reducing the Operating System Priority of PeopleSoft Processes<p style="text-align: left;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://media.springernature.com/w306/springer-static/cover-hires/book/978-1-4302-3708-2" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="PeopleSoft for the Oracle DBA" border="0" data-original-height="376" data-original-width="306" height="100" src="https://media.springernature.com/w306/springer-static/cover-hires/book/978-1-4302-3708-2" /></a></div><p style="text-align: left;">I wrote about controlling the operating system priority of processes in PeopleSoft Tuxedo domains in Chapters 13 of 14 of <a href="https://www.go-faster.co.uk/p/peoplesoft-for-oracle-dba.html">PeopleSoft for the Oracle DBA</a>, but I think it is worth a note here.</p><div><p style="text-align: left;">On Linux and Unix systems, the nice command can be used to lower the operating system scheduling priority of a process (or a privileged can increase the priority). When a server has no free CPU, processes with a lower priority get less time on the CPU. However, when there is free CPU available, the scheduling priority does not affect the amount of CPU that the process can utilise. </p><div><p style="text-align: left;">On Unix, the priority of a Tuxedo server process can be adjusted using the <a href="https://docs.oracle.com/cd/E13203_01/tuxedo/tux80/atmi/rf523.htm#1003316" target="_blank">-n server command line option</a> in the configuration. The parameters to this option are simply passed through to the <a href="https://docs.oracle.com/cd/E26502_01/html/E29032/nice-2.html#REFMAN2nice-2" rel="nofollow" target="_blank">nice(2)</a> function. Hence, this option does not work on Windows.
</p><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: xx-small;"><code>PSPRCSRV SRVGRP=BASE
SRVID=101
MIN=1
MAX=1
RQADDR="SCHEDQ"
REPLYQ=Y
CLOPT="<b>-n 4</b> -sInitiateRequest -- -C psprcs.cfg -CD HR88 -PS PSUNX -A start -S PSPRCSRV"</code></span></pre>
The operating system priority of a process is inherited from its parent. Therefore, lowering the priority of the Process Scheduler running under Tuxedo will also lower the priority of the batch processes that it spawns. </div><div><ul style="text-align: left;"><li>Therefore Stand-alone Application Engine processes (<i>psae</i>) and Cobol processes inherit the priority of the process scheduler server process (<i>PSPRCSRV</i>).</li><li>However, if the Application Engine server process (<i>PSAESRV</i>) is used, its priority can be set directly. </li></ul>There are some potential uses for this approach.<br /><ul style="text-align: left;"><li>If the process scheduler is co-resident with the application server, then it could be run at a lower priority to ensure the online users get preferential allocation of CPU, and that online performance does not suffer excessively at the hands of the batch.</li><li>A system might have two websites: one for self-service and the other for the 'back-office' users. You could configure separate application servers for each site, and run the self-service application server is run at a lower priority. </li></ul><p style="text-align: left;">In PeopleSoft, I prefer to create additional variables in the configuration file (<i>psprcs.cfg</i>).</p></div><div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: x-small;"><code><b>[Process Scheduler]</b>
;=========================================================================
; General settings for the Process Scheduler
;=========================================================================
PrcsServerName=PSUNX
;-------------------------------------------------------------------------
;Reduce priority of Process Scheduler server process, set to 0 if not needed
<b>Niceness=4</b>
...</code></span></pre>From PeopleTools 8.4, the Application Engine server process is configured by default. The priority of the AE server processes can then be controlled independently of the process scheduler by creating a separate variable in the PSAESRV section of the configuration file. However, it is generally better to use standalone PSAE, unless you have many short-lived application engine processes, as in CRM (see <a href="https://blog.psftdba.com/2018/04/application-engine-in-process-scheduler.html">Application Engine in Process Scheduler: PSAESRV Server Process -v- Standalone PSAE executable</a>). </div><div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: x-small;"><code><b>[PSAESRV]</b>
;=========================================================================
; Settings for Application Engine Tuxedo Server
;=========================================================================
;-------------------------------------------------------------------------
;Reduce priority of application engine server process, set to 0 if not needed
<b>Niceness=5</b>
...</code></span></pre><div>In this example, I have reduced the priorities of both the process scheduler and AE servers, but the process scheduler is left with a higher priority than the AE servers. The new variables can then be referenced Tuxedo template file (<i>psprcsrv.ubx</i>).</div></div><div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: 60%;"><code>{APPENG}
#
# PeopleSoft Application Engine Server
#
PSAESRV SRVGRP=AESRV
SRVID=1
MIN={$PSAESRV\Max Instances}
MAX={$PSAESRV\Max Instances}
REPLYQ=Y
CLOPT="<b>-n {$PSAESRV\Niceness}</b> -- -C {CFGFILE} -CD {$Startup\DBName} -S PSAESRV"
{APPENG}
...
PSPRCSRV SRVGRP=BASE
SRVID=101
MIN=1
MAX=1
RQADDR="SCHEDQ"
REPLYQ=Y
CLOPT="<b>-n {$Process Scheduler\Niceness}</b> -sInitiateRequest -- -C {CFGFILE} -CD {$Startup\DBName} -PS {$Process Scheduler\PrcsServerName} -A start -S PSPRCSRV"
</code></span></pre>
When the domain is configured in <i>psadmin</i>, the variables are resolved in the Tuxedo configuration file (<i>psprcsrv.ubb</i>). The -n option can be seen in the server command-line options (<i>CLOPT</i>).<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: xx-small;"><code>#
# PeopleSoft Application Engine Server
#
PSAESRV SRVGRP=AESRV
SRVID=1
MIN=1
MAX=1
REPLYQ=Y
CLOPT="<b>-n 5</b> -- -C psprcs.cfg -CD HR88 -S PSAESRV"
...
PSPRCSRV SRVGRP=BASE
SRVID=101
MIN=1
MAX=1
RQADDR="SCHEDQ"
REPLYQ=Y
CLOPT="<b>-n 4</b> -sInitiateRequest -- -C psprcs.cfg -CD HR88 -PS PSUNX -A start -S PSPRCSRV"
</code></span></pre>
</div></div><div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com0tag:blogger.com,1999:blog-25740336.post-59874695835060091752023-11-10T11:13:00.003+00:002024-02-22T09:00:37.006+00:00Prioritising Scheduled Processes by Operator ID/Run Control<p style="text-align: left;">Batch processing is like opera (and baseball) - <span style="text-align: center;"><a href="https://en.wikipedia.org/wiki/It_ain%27t_over_till_the_fat_lady_sings" style="font-style: italic;" target="_blank">"It ain't over till the fat lady sings"</a>. Users care about when it starts and when it finishes. If the last process finishes earlier, then that is an improvement in performance. </span></p><p style="text-align: left;">This note describes a method of additionally prioritising processes queued to run on the process scheduler in PeopleSoft by their requesting operator ID and run control. Where processing consists of more instances of the same process than can run concurrently, it can be used to make the process scheduler run longer-running processes before shorter-running processes that were scheduled earlier, thus completing batch processing earlier.</p><p style="text-align: left;">In PeopleSoft, without customisation, it is only possible to prioritise processes queued to run on the process scheduler by assigning a priority to the process definition or their process category. Higher priority processes are selected to be run in preference to lower priorities. Otherwise, processes are run in the order of the time at which they are requested to run.</p><h3 style="text-align: left;">Problem Statement</h3><div>During an overnight batch, many nVision report books are scheduled to run on the Windows process schedulers by one of several specific batch operator IDs. Many more reports are scheduled than can run concurrently, so some execute while others queue. Inevitably, the reports have widely varying execution times. The maximum concurrency of the <a href="https://docs.oracle.com/cd/F44947_01/pt858pbr3/eng/pt/tnvs/task_UsingReportBooks-074eb6.html?pli=ul_d58e178_tnvs" target="_blank">nVision report book</a> (RPTBOOK) process definition has been set, and the Oracle database resource manager has also been configured, to prevent too many of these processes from overloading the database.</div><p><a href="https://blogger.googleusercontent.com/img/a/AVvXsEiggKvNhw0KjfvuxFQfGwu7QCK3zlFFmvN6ISImdpay5JqUrcx367iGgdNJtsc8c2yg1Y7HkZoSIXOteaMSR2kxD5gGtlO5NILnhNpYljhFG5FCSx8EnaTCs5Yhio1YzzwZZS8w3NlTaEs6URis_s0UGU1UJKDwFzHPO_KYdcS2dRtLoeBzpGpI" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em; text-align: center;"><img alt="CPU Utilisation of Batch" data-original-height="3990" data-original-width="6108" height="210" src="https://blogger.googleusercontent.com/img/a/AVvXsEiggKvNhw0KjfvuxFQfGwu7QCK3zlFFmvN6ISImdpay5JqUrcx367iGgdNJtsc8c2yg1Y7HkZoSIXOteaMSR2kxD5gGtlO5NILnhNpYljhFG5FCSx8EnaTCs5Yhio1YzzwZZS8w3NlTaEs6URis_s0UGU1UJKDwFzHPO_KYdcS2dRtLoeBzpGpI=w320-h210" title="CPU Utilisation of Batch" width="320" /></a>This chart shows the database activity when the batch runs. We often see what has come to be called the 'long tail' while we wait for just a few long-running processes to complete.</p><p></p><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><br /></div></div><div class="separator">The next chart shows the processing time of each nVision report process. The blue bars run from when the started to when it ended, ordered by start time. The clear boxes below run from the time when it was requested to when it started, thus showing the period for which the processes were queued on a process scheduler, but were blocked because the maximum number of processes were already processing.</div><div class="separator"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjpccI5vcNIF4IMtIrG1-gQp-GrqLlIT4eEWw3FiqZIR2ZOXW5rhVSmqeNoR3riNtNizgSncyE6LGf3S7mRXG6d3WbQ6AOxVX6PDug9j8FwFzWJ0jv0LTTfbXbkLptGri7OHdE3x4hlU9oTPncd0m1ps9ziiohsEPth0JT9sX6VyAVqnGg61oCR" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img alt="Process Map (without prioritisation)" data-original-height="3990" data-original-width="6108" height="261" src="https://blogger.googleusercontent.com/img/a/AVvXsEjpccI5vcNIF4IMtIrG1-gQp-GrqLlIT4eEWw3FiqZIR2ZOXW5rhVSmqeNoR3riNtNizgSncyE6LGf3S7mRXG6d3WbQ6AOxVX6PDug9j8FwFzWJ0jv0LTTfbXbkLptGri7OHdE3x4hlU9oTPncd0m1ps9ziiohsEPth0JT9sX6VyAVqnGg61oCR=w400-h261" title="Process Map (without prioritisation)" width="400" /></a></div>All these processes run with the same priority because they are the same process definition. Some long-running jobs execute earlier in the batch simply because they were scheduled earlier, but others that started later, run on beyond the end of the batch. <div>It would be better if the longest-running processes were executed earlier, irrespective of the order in which they were requested. Thus the shorter processes can run later as slots on the scheduler become free, and thus all processes should finished both closer together and earlier.</div><div>There is nothing delivered in the PeopleSoft process scheduler configuration that will let you assign different priorities to different executions of the same process. In PeopleSoft, only three priorities are defined <a href="https://www2.go-faster.co.uk/peopletools/prcsdefn.htm#prcspriority" target="_blank">PRCSPRIORITY</a> (1=Low, 5=Medium, 9=High) on the process definition and the server category. These priorities are transferred to the process request queue (<a href="https://www2.go-faster.co.uk/peopletools/psprcsque.htm#prcsprty" target="_blank">PSPRCSQUE.PRCSPRTY</a>). </div><div>If we could put our own priority into that column we could control the priority of the request. <h3 style="text-align: left;">Solution</h3><div>Introduce a database trigger that fires on insert into PSPRCSQUE and sets a priority specified on a new metadata table. PRCSPRTY is not validated by PeopleSoft, and therefore any value can be specified. </div><div>The files are available in a Github repository <a href="https://github.com/davidkurtz/psprcsprty" target="_blank">davidkurtz/psprcspty</a>. The exact metadata will vary with the use case and requirements, but I provided some examples of how it might be generated.</div><p></p><ul style="text-align: left;"><li><a href="https://github.com/davidkurtz/psprcsprty/blob/main/process_prioritisation_by_cumulative_runtime.sql" target="_blank">process_prioritisation_by_cumulative_runtime.sql</a> - master script that creates metadata table and trigger and then:</li><ul><li><a href="https://github.com/davidkurtz/psprcsprty/blob/main/nvision_prioritisation_by_cumulative_runtime.sql" target="_blank">nvision_prioritisation_by_cumulative_runtime.sql</a> - example script to create a procedure to populate metadata for nVision batch.</li><li><a href="https://github.com/davidkurtz/psprcsprty/blob/main/gppdprun_prioritisation_by_cumulative_runtime.sql" target="_blank">gppdprun_prioritisation_by_cumulative_runtime.sql</a> - example script to create a procedure to populate metadata for Payroll/Absence calculation batch. </li><li><a href="https://github.com/davidkurtz/psprcsprty/blob/main/process_prioritisation_by_cumulative_runtime_test.sql" target="_blank">process_prioritisation_by_cumulative_runtime_test.sql</a> - test trigger by inserting dummy data into process queue table.</li></ul><li><a href="https://github.com/davidkurtz/psprcsprty/blob/main/process_prioritisation_by_cumulative_runtime_report.sql" target="_blank">process_prioritisation_by_cumulative_runtime_report.sql</a> - example of a report to compare median execution time with last execution time.</li></ul><p></p><h3 style="text-align: left;">Metadata Table: PS_XX_GFCPRCSPRTY</h3>
<p>We need a table that will hold the priority for each combination of process type, process name, operation ID, and run control ID. A corresponding record should be created using the Application Designer <a href="https://github.com/davidkurtz/psprcsprty/tree/main/XX_GFCPRCSPRTY" target="_blank">project in the GitHub repository</a>.</p>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: x-small;"><code>create table sysadm.ps_xx_gfcprcsprty
(prcstype VARCHAR2(30 CHAR) NOT NULL
,prcsname VARCHAR2(12 CHAR) NOT NULL
,oprid VARCHAR2(30 CHAR) NOT NULL
,runcntlid VARCHAR2(30 CHAR) NOT NULL
,prcsprty NUMBER NOT NULL
--------------------optional columns
,avg_duration NUMBER NOT NULL
,med_duration NUMBER NOT NULL
,max_duration NUMBER NOT NULL
,cum_duration NUMBER NOT NULL
,tot_duration NUMBER NOT NULL
,num_samples NUMBER NOT NULL
) tablespace ptapp;
create unique index sysadm.ps_xx_gfcprcsprty
on sysadm.ps_xx_gfcprcsprty(prcstype, prcsname, oprid, runcntlid)
tablespace psindex compress 3;</code></span></pre><h3 style="text-align: left;">Trigger Before Insert into PSPRCSQUE</h3><p>As processes are scheduled in PeopleSoft, a row is inserted into the process scheduler queue table <a href="https://www2.go-faster.co.uk/peopletools/psprcsque.htm" target="_blank">PSPRCSQUE</a>. A trigger will be created on this table that fires after the insert. It will look for a matching row on the metadata table, PS_XX_GFCPRCSPRTY for the combination of process type, process name, operator ID, and run control ID. If found, the trigger will assign the specified priority to the process request. Otherwise, it will take no action.</p>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: x-small;"><code>CREATE OR REPLACE TRIGGER sysadm.psprcsque_set_prcsprty
BEFORE INSERT ON sysadm.psprcsque
FOR EACH ROW
WHEN (new.prcsname = 'RPTBOOK')
DECLARE
l_prcsprty NUMBER;
BEGIN
SELECT prcsprty
INTO l_prcsprty
FROM ps_xx_gfcprcsprty
WHERE prcstype = :new.prcstype
AND prcsname = :new.prcsname
AND oprid = :new.oprid
AND runcntlid = :new.runcntlid;
:new.prcsprty := l_prcsprty;
EXCEPTION
WHEN no_data_found THEN NULL;
WHEN others THEN NULL;
END;
/
show errors</code></span></pre>
<p>In this case, I am only assigning priorities to RPTBOOK processes, so I have added a when clause to the trigger so that it only fires for RPTBOOK process requests. This can either be changed for other processes or removed entirely.</p><h3 style="text-align: left;">Priority Metadata</h3><p>How the priorities should be defined will depend on the specific use case. In some cases, you may choose to create a set of metadata that remains unchanged.</p><p>In this case, the objective is that the processes to take the longest to run should be executed first. Therefore, I decided that the priority of each nVision report book process (by operator ID and run control ID) will be determined by the median elapsed execution time in the last two months. The priorities are allocated such that the sum of the median execution times for each priority will be as even as possible. </p><p>I have created a PL/SQL procedure GFCPRCSPRIORITY to truncate the metadata table and then repopulate it using a query on the process scheduler table (although, an Application Engine program could have been written to do this instead). Some details of that query will vary with the exact use case. The procedure is executed daily, thus providing a feedback loop so if the run time varies over time, or new processes are added to the batch, it will be reflected in the priorities.</p>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: x-small;"><code>REM <a href="https://github.com/davidkurtz/psprcsprty/blob/main/nvision_prioritisation_by_cumulative_runtime.sql" target="_blank">nvision_prioritisation_by_cumulative_runtime.sql</a>
set serveroutput on
create or replace procedure sysadm.gfcprcspriority as
PRAGMA AUTONOMOUS_TRANSACTION; --to prevent truncate in this procedure affecting calling session
l_hist INTEGER := 61 ; --consider nVision processes going back this many days
begin
EXECUTE IMMEDIATE 'truncate table ps_xx_gfcprcsprty';
--populate priorty table with known nVision processes
insert into ps_xx_gfcprcsprty
with r as (
select r.prcstype, r.prcsname, r.prcsinstance, r.oprid, r.runcntlid, r.runstatus, r.servernamerun
, CAST(r.rqstdttm AS DATE) rqstdttm
, CAST(r.begindttm AS DATE) begindttm
, CAST(r.enddttm AS DATE) enddttm
from t, psprcsrqst r
inner join ps.psdbowner p on r.dbname = p.dbname -- in test exclude any history copied from another database
where r.prcstype like 'nVision%' --limit to nVision processes
and r.prcsname like 'RPTBOOK' -- limit to report books
and r.enddttm>r.begindttm --it must have run to completion
and r.oprid IN('NVISION','NVISION2','NVISION3','NVISION4') --limit to overnight batch operator IDs
and r.begindttm >= TRUNC(SYSDATE)+.5-l_hist --consider process going back l_hist days from midday today
and r.runstatus = '9' --limit to successful processes
and r.begindttm BETWEEN ROUND(r.begindttm)-5/24 AND ROUND(r.begindttm)+5/24 --started between 7pm and 5am
), x as (
select r.*, CEIL((enddttm-begindttm)*1440) duration -–rounded up to the next minute
from r
), y as (
select prcstype, prcsname, oprid, runcntlid
, AVG(duration) avg_duration
, MEDIAN(CEIL(duration)) med_duration
, MAX(duration) max_duration
, SUM(CEIL(duration)) sum_duration
, COUNT(*) num_samples
from x
group by prcstype, prcsname, oprid, runcntlid
), z as (
select y.*
, sum(med_duration) over (order by med_duration rows between unbounded preceding and current row) cum_duration
, sum(med_duration) over () tot_duration
from y
)
select prcstype, prcsname, oprid, runcntlid
, avg_duration, med_duration, max_duration, cum_duration, tot_duration, num_samples
--, CEIL(LEAST(tot_duration,cum_duration)/tot_duration*3)*4-3 prcsprty --3 priorities
, CEIL(LEAST(tot_duration,cum_duration)/tot_duration*9) prcsprty --9 priorities
--, DENSE_RANK() OVER (order by med_duration) prcsprty --unlimited priorities
from z
order by prcsprty, cum_duration;
dbms_output.put_line(sql%rowcount||' rows inserted');
commit;
end gfcprcspriority;
/
show errors</code></span></pre><div style="text-align: left;"><span style="font-weight: 400;">In testing, I found that using just the 3 delivered levels of priority was not sufficiently granular to prioritise the jobs adequately, so I chose to use 9 levels (1 to 9). The process priority on PRCSQUE is not validated, so I can use any value. I also found I could just rank the processes from 1 to <i>n</i> by duration, and that would also work.</span></div><div style="text-align: left;"><span style="font-weight: 400;"><br /></span></div><div style="text-align: left;"><span style="font-size: x-small; font-weight: 400;"><i>It is possible to create additional priority levels for process categories (see also <a href="https://blog.psftdba.com/2011/03/more-process-priority-levels-for.html">More Process Priority Levels for the Process Scheduler</a>), but that still only works for prioritising different processes over each other.</i></span></div><h3 style="text-align: left;">Metadata</h3><p>This is the metadata produced on a test system by the above query. It will vary depending on what has been run recently, and how it performed. There are more, shorter processes in the lower priority groups, and fewer, longer processes in the higher priority groups. </p>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: x-small;"><code>PRCSTYPE PRCSNAME OPRID RUNCNTLID PRCSPRTY
------------------------------ ------------ ------------ ------------------------------ ----------
…
nVision-ReportBook RPTBOOK NVISION2 NVS_RPTBK_XXX1 1
nVision-ReportBook RPTBOOK NVISION2 NVS_RPTBK_XXX3 1
nVision-ReportBook RPTBOOK NVISION2 NVS_RPTBK_LLLL8 1
…
nVision-ReportBook RPTBOOK NVISION2 NVS_RPTBK_LLLL9 2
nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_STAT8 2
nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_STAT1 2
…
nVision-ReportBook RPTBOOK NVISION2 NVS_RPTBK_TEMPXX 6
nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_3 6
nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_17 6
nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_STAT4 7
nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_28 7
nVision-ReportBook RPTBOOK NVISION2 NVS_RPTBK_INCXXX 8
nVision-ReportBook RPTBOOK NVISION2 NVS_RPTBK_MORYYY1 8
nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_24 9
nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_16 9<br /></code></span></pre><h3 style="text-align: left;">A Test Script</h3><p>This test script inserts some dummy rows into PSPRCSQUE to check whether a priority is assigned by the trigger. The insert is then rolled back.</p>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: x-small;"><code>INSERT INTO psprcsque (prcsinstance, prcstype, prcsname, oprid, runcntlid)
VALUES (-42, 'nVision-ReportBook', 'RPTBOOK', 'NVISION','NVS_RPTBOOK_17');
INSERT INTO psprcsque (prcsinstance, prcstype, prcsname, oprid, runcntlid)
VALUES (-43, 'nVision-ReportBook', 'RPTBOOK', 'NVISION','NVS_RPTBOOK_STAT1');
select prcsinstance, prcstype, prcsname, oprid, runcntlid, prcsprty from psprcsque where prcsinstance IN(-42,-43);
rollback;</code></span></pre>
<p>You can see that it was successful because priorities 2 and 7 were assigned.</p>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: x-small;"><code>PRCSINSTANCE PRCSTYPE PRCSNAME OPRID RUNCNTLID PRCSPRTY
------------ ------------------------------ ------------ ------------ ------------------------------ ----------
-43 nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_STAT1 2
-42 nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_17 7</code></span></pre><h3 style="text-align: left;">Monitoring Script</h3><p>This query in script <a href="https://github.com/davidkurtz/psprcsprty/blob/main/process_prioritisation_by_cumulative_runtime_report.sql" target="_blank">process_prioritisation_by_cumulative_runtime_report.sql</a> reports on the average, median, and cumulative median execution time for each nVision process that ran to success during the overnight processing window as calculated by the package GFCPRCSPRIORITY and stored in PS_XX_GFCPRCSPRTY<i>. </i>It also compares that to the priority and last actual run time for that process.</p>
<h4 style="text-align: left;">Example Output</h4>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: 66%;"><code> Cum.
Average Median Median Total Last Run Actual
Prcs Duration Duration Duration Duration Duration Num Process Duration Duration Duration Priorty
PRCSTYPE PRCSNAME OPRID RUNCNTLID Prty (mins) (mins) (mins) (mins) (mins) Samples Priority (mins) Diff % Diff Diff
-------------------- ---------- ------------ -------------------- ---- -------- -------- -------- -------- -------- ------- -------- -------- -------- -------- -------
nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_4 9 90.65 131 209 1834 1997 23 6 189 58 44 3
nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_16 9 159.17 163 209 1997 1997 23 9 177 14 9 0
nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_14 8 89.26 127 215 1703 1997 23 6 167 40 31 2
nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_24 8 115.87 117 165 1576 1997 23 9 144 27 23 -1
nVision-ReportBook RPTBOOK NVISION2 NVS_RPTBK_MORYYY1 7 93.13 85 165 1459 1997 23 8 158 73 86 -1
nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_28 7 88.30 80 172 1374 1997 23 8 108 28 35 -1
nVision-ReportBook RPTBOOK NVISION2 NVS_RPTBK_INCXXX 6 83.61 79 149 1294 1997 18 7 118 39 49 -1
nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_17 6 70.96 69 105 1143 1997 23 7 81 12 17 -1
nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_STAT4 6 68.00 72 81 1215 1997 8 7 81 9 13 -1
nVision-ReportBook RPTBOOK NVISION2 NVS_RPTBK_MMMMMM 5 52.45 46 119 914 1997 22 5 91 46 100 0
nVision-ReportBook RPTBOOK NVISION2 NVS_RPTBK_TEMPXX 5 50.48 49 104 963 1997 23 5 94 45 92 0
nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_3 5 55.52 55 99 1018 1997 23 6 79 24 44 -1
nVision-ReportBook RPTBOOK NVISION NVS_RPTBOOK_1 5 47.70 56 137 1074 1997 23 4 56 0 0 1
…
</code></span></pre>
<h3 style="text-align: left;">Monitoring Query</h3><p>The query in <a href="https://github.com/davidkurtz/psprcsprty/blob/main/prcsmap.sql" target="_blank">prcsmap.sql</a> is used to produce the data for a map of the processes, showing request time, time spent queuing, and time spent executing. It is the basis of the second chart above. I normally run this in SQL Developer and export the data as an Excel workbook. There is an <a href="https://github.com/davidkurtz/psprcsprty/blob/main/prcsmap.xlsx" target="_blank">example spreadsheet in the Github repository</a>.</p><h3 style="text-align: left;">9 levels of Prioritisation</h3><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEj4uC3vZPQvBsodQhxPIHMCj4q5GKboYLlEKdEslW7v6VPeLd3dAqel3qAoxwJYit4ZrWlfotaJo4tyEYhW88teYE5FlDryka1xdZGMDo1X6kmQ8zPsqrZESofGgy7bnwCFpCCrYEeribZBpxnyIJzVtzoXvkLwlOgxcLXVAo85LVsqCyLjT9Si" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img alt="" data-original-height="997" data-original-width="1526" height="209" src="https://blogger.googleusercontent.com/img/a/AVvXsEj4uC3vZPQvBsodQhxPIHMCj4q5GKboYLlEKdEslW7v6VPeLd3dAqel3qAoxwJYit4ZrWlfotaJo4tyEYhW88teYE5FlDryka1xdZGMDo1X6kmQ8zPsqrZESofGgy7bnwCFpCCrYEeribZBpxnyIJzVtzoXvkLwlOgxcLXVAo85LVsqCyLjT9Si" width="320" /></a></div>With prioritisation, we can see that the long-running jobs with higher priority ran earlier. </div><div><br /></div><div>We can also see that some of the higher-priority jobs that are scheduled later are running earlier than those scheduled earlier, and are thus finishing earlier.<p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhWAITPHsw8eakcqeFQPgYdbeLeoVskoSqkkroE42PNtOsVtqTciTpww6AqmsANNBWBmqJaXqP0TYLuU3PuEgfWj2YRrqQymVJfircSzb0TuSQv6o4zBQ8zSmp3TIsfucw7wsDCrwlZRfn_vkFGn8wZMIeI1uF7LFTEJ1iAum6kfQi1j_QQtyX2" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img alt="" data-original-height="997" data-original-width="1524" height="210" src="https://blogger.googleusercontent.com/img/a/AVvXsEhWAITPHsw8eakcqeFQPgYdbeLeoVskoSqkkroE42PNtOsVtqTciTpww6AqmsANNBWBmqJaXqP0TYLuU3PuEgfWj2YRrqQymVJfircSzb0TuSQv6o4zBQ8zSmp3TIsfucw7wsDCrwlZRfn_vkFGn8wZMIeI1uF7LFTEJ1iAum6kfQi1j_QQtyX2=w320-h210" width="320" /></a></div>There is no longer any tail of processing. Instead, load drops quickly at the end of the batch, and the batch as a whole finishes earlier.<p></p><p></p><div class="separator" style="clear: both; text-align: center;"><br /><br /></div><br /><br /><p></p><div class="separator" style="clear: both; text-align: center;"><br /><br /></div><br /><br /><p></p><p><br /></p></div><div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com0tag:blogger.com,1999:blog-25740336.post-73183539551030220572023-04-27T16:41:00.002+01:002023-05-10T17:03:21.340+01:00Querying the PeopleSoft Message Log with SQL<p>It is easy to access the PeopleSoft message log in Process Monitor component, but it can be a little difficult to work with in SQL because it is stored in multiple tables.</p><p>This started when I wanted to generate a PeopleSoft log message as a single string of text, so I could investigate shared pool memory errors by searching for ORA-04031 errors. Ultimately, the string 'ORA-04031' is stored in PS_MESSAGE_LOGPARM, but I wanted to see the whole error message.</p><p></p><ul style="text-align: left;"><li>Each process, has a request record on <a href="http://www2.go-faster.co.uk/peopletools/psprcsrqst.htm" target="_blank">PSPRCSRQST</a>, it can have many messages.</li><li>Each message is stored in the message log table <a href="http://www2.go-faster.co.uk/peopletools/message_log.htm" target="_blank">PS_MESSAGE_LOG</a></li><li>The text of each message is stored in the message catalogue table <a href="http://www2.go-faster.co.uk/peopletools/psmsgcatdefn.htm" target="_blank">PSMSGCATDEFN</a>. It can have up to 9 substitution strings (%1, %2, etc).</li><li>These correspond to up to 9 parameters stored on <a href="http://www2.go-faster.co.uk/peopletools/message_logparm.htm" target="_blank">PS_MESSAGE_LOGPARM</a> that are substituted into the message string.</li></ul><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjLjZPoCVNo7yy1owGeiBmQxclZ-owQ40hzMflXxZh_L5cz4SahsYAj2uhZlot98EZbhvsyXiBp-H5okKZALm9FDL3GEapygofD6IG3UaN9QK8eRWhQRq4c3xdE57Qx8MnuMIEznLZqYiyr2IyK3cUIvMANmBlAlrTNVBtZ32o6LKA7I6mchA" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="256" data-original-width="485" height="169" src="https://blogger.googleusercontent.com/img/a/AVvXsEjLjZPoCVNo7yy1owGeiBmQxclZ-owQ40hzMflXxZh_L5cz4SahsYAj2uhZlot98EZbhvsyXiBp-H5okKZALm9FDL3GEapygofD6IG3UaN9QK8eRWhQRq4c3xdE57Qx8MnuMIEznLZqYiyr2IyK3cUIvMANmBlAlrTNVBtZ32o6LKA7I6mchA" width="320" /></a></div><p>I assemble the message text in a PL/SQL function exactly as PeopleTools programs do, substituting the variables in the message string from the message catalogue with the parameter values. The PL/SQL function is put into the SQL query as a common table expression so that I don't have to create a function or package in the database. The function returns the full message text in a <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-0EAC5929-0674-429C-AF42-2D454C982F8F" target="_blank">CLOB</a>, thus I can then easily manipulate the message string in SQL.</p><p>In this case, I wrote a SQL query to search for ORA-04031 (see <a href="https://github.com/davidkurtz/psscripts/blob/master/psmsglogora4031.sql" target="_blank">psmsglogora4031.sql</a> on <a href="https://github.com/davidkurtz/psscripts" target="_blank">Github</a>), but the same PL/SQL function can be used in various queries. </p><p>It can be slow to search the generated message string. It can be faster to search PS_MESSAGE_LOGPARM directly. </p><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: x-small;"><code>WITH <b>FUNCTION psmsgtext(p_process_instance INTEGER, p_message_seq INTEGER) RETURN CLOB IS
l_message_log ps_message_log%ROWTYPE;
l_message_text CLOB;
BEGIN
SELECT *
INTO l_message_log
FROM ps_message_log
WHERE process_instance = p_process_instance
AND message_seq = p_message_seq;
SELECT message_text
INTO l_message_text
FROM psmsgcatdefn
WHERE message_set_nbr = l_message_log.message_set_nbr
AND message_nbr = l_message_log.message_nbr;
--dbms_output.put_line(l_message_text);
FOR i IN (
SELECT *
FROM ps_message_logparm
WHERE process_instance = p_process_instance
AND message_seq = p_message_seq
ORDER BY parm_seq
) LOOP
--dbms_output.put_line(i.message_parm);
l_message_text := REPLACE(l_message_text,'%'||i.parm_seq,i.message_parm);
END LOOP;
--and tidy up the unused replacements at the end
RETURN REGEXP_REPLACE(l_message_text,'%[1-9]','');
END;</b>
x as (
select r.prcstype, r.prcsname, r.oprid, r.runcntlid
, l.*, psmsgtext(l.process_instance, l.message_seq) message_text
from ps_message_log l
LEFT OUTER JOIN psprcsrqst r ON r.prcsinstance = l.process_instance
WHERE …
)
select *
from x
ORDER BY dttm_stamp_sec
/</code></span></pre>
Now, I can easily produce a report of messages, like this:
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: 70%;"><code>
Process Process Operator Process Msg Msg Msg
Type Name ID Run Control Instance Seq JOBID PROGRAM_NAME Set# Msg# Sev DTTM_STAMP_SEC
-------------------- --------------- ---------- ---------------------- ---------- ---- --------------- --------------- ----- ----- ----- ----------------------------
MESSAGE_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------
nVision-ReportBook RPTBOOK VP1 NVS_XXXXXXX_99 1234567 1 PRCS SCHDL psprschd 65 70 0 01/04/2023 20.37.21
Process Request shows status of 'INITIATED' or 'PROCESSING' but no longer running
nVision-ReportBook RPTBOOK VP1 NVS_XXXXXXX_99 1234567 2 PRCS SCHDL psprschd 65 73 0 01/04/2023 20.37.23
PSNT1 failed to post files to the report repository. Server scheduled to try again on 2023-04-01-20.37.41.239539. See log
...</code></span></pre><div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com1tag:blogger.com,1999:blog-25740336.post-81659047786479140902023-04-14T09:49:00.005+01:002023-04-27T14:23:58.415+01:00Purging PeopleTools Physical Cache Files after Database Restore or Database Flashback<p>I have <a href="https://blog.psftdba.com/2008/08/how-to-clear-application-server-cache.html">written previously</a> about how to clear the physical cache files on a PeopleTools process, but I have found myself explaining it a few times recently, so I am going to post about it again.</p>
<p>When you refresh the database of a PeopleSoft system, you need to clear the physical cache files of the PeopleTools processes. The files are outside the database and they no longer reflect what is inside the database. This includes all application servers and process schedulers and anywhere where Application Designer or a client process is used.</p>
<p>It is common to refresh a database when testing a PeopleSoft system. For example, to copy production to a performance test environment. It is also increasingly common to use Oracle database flashback during testing. A guaranteed restore point is taken, a test is performed, and then the database is flashed back to that restore point. Flashback returns the whole database not just to the same logical state, but also the same physical state. Block for block, the entire database is physically the same as when the restore point was taken. Thus a test is completely repeatable with the same initial conditions. Although the database instance will have been restarted during the flashback so the content of the database memory will have been cleared.</p>
<p>It is also common, after the flashback to then make small changes or corrections, take a new restore point and repeat the test. Some of those changes might include Application Designer projects that will then be loaded into the physical cache. Flashing the database back won't change the physical cache files stored outside the database, so they need to be cleared too. Otherwise, they may have higher version numbers than the objects in the database, and caching won't work correctly. When you retest, your changes may not be loaded and executed by PeopleTools processes.</p>
<p>The officially approved method is to go around each server and use either the purge option in the <i>psadmin</i> utility or manually delete the files. See:</p><p></p><ul style="text-align: left;"><li>Oracle Support Note: <a href="https://support.oracle.com/epmos/faces/DocContentDisplay?id=753215.1" target="_blank">E-WS: How to Delete / Purge Cache for the PeopleSoft Application (Doc ID 753215.1)</a></li><li>PeopleBooks: System and Server Admin ► <a href="https://docs.oracle.com/cd/F75887_01/pt860pbr2/eng/pt/tsvt/UsingtheApplicationServerAdministrationMenu-c07e84.html" target="_blank">Using the Application Server Administration Menu</a> </li></ul><p></p>
<p>However, since at least PeopleTools 5, it has been possible to invalidate all physical cache files on all servers by updating the LASTREFRESHDTTM on the single row in table <a href="http://www2.go-faster.co.uk/peopletools/psstatus.htm" target="_blank">PSSTATUS</a>. Any cached object older than the value of LASTREFRESHDTTM will be purged from the cache when the process that reference that cache is started. Therefore, if immediately after a restore or flashback that value is updated to the current system time, all caches will be purged as the processes are restarted.</p>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: small;"><code>UPDATE PSSTATUS
SET LASTREFRESHDTTM = SYSDATE
/
COMMIT
/</code></span></pre>
<div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com0tag:blogger.com,1999:blog-25740336.post-5886102348168693142023-04-12T11:26:00.006+01:002023-04-13T12:57:42.630+01:00Programmatically Suspending and Restarting the Process Scheduler<div>I found this question on a message forum, wrote a note, and forgot about it:</div><div><br /></div><div><i>Anyone have any tricks on suspending the process schedulers programmatically? I just tried using the following but the weird thing was I saw at least one of my process schedulers unsuspend itself, so I must be missing an update to a table.</i></div><div><i><br /></i></div><div><i>UPDATE <a href="http://www2.go-faster.co.uk/peopletools/psserverstat.htm" target="_blank">PSSERVERSTAT</a> set SERVERSTATUS = '2' where SERVERNAME like '%PSUNX%'</i></div><div><br /></div><div>The process scheduler <u>writes</u> its status to SERVERSTATUS so that it can be seen in the Process Monitor. Instructions to the process scheduler are <u>read</u> from SERVERACTION, so this is the column that must be updated. Both columns have a set of XLAT values that translate the status.</div><div><br /></div>
<style>table { border-collapse: collapse; width: 100%;} td, th { border: 1px solid black; text-align: left; vertical-align: top; padding: 1px;}</style>
<table style="width: 100%;">
<tbody><tr><th>PeopleSoft Field Name</th><th>Description</th>
</tr><tr>
<td>SERVERSTATUS</td><td>Server Status<br />0=Error<br />1=Down<br />2=Suspended<br />3=Running<br />4=Purging<br />5=Running With No Report Node<br />6=Suspended - Disk Low<br />7=Suspended - Offline<br />8=Running - Report Rep. Full<br />9=Overloaded</td>
</tr><tr>
<td>SERVERACTION</td><td>Process Server Action<br />0=None<br />1=Stop<br />2=Suspended<br />3=Restart<br />4=Purge</td>
</tr></tbody></table>See <a href="http://www2.go-faster.co.uk/peopletools/psserverstat.htm" target="_blank">PSSERVERSTAT</a>.<div><br /></div>You can see how PeopleSoft does this by tracing the Process Monitor component as it issues commands to the process scheduler. Hence you can issue commands as follows:<div><ul style="text-align: left;"><li>Stop</li></ul><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: x-small;"><code>update psserverstat
set serveraction = 1 /*Stop*/
where serverstatus = 3 /*Running*/
and servername = …
/
commit
/
</code></span></pre><ul style="text-align: left;"><li>
Suspend</li></ul><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: x-small;"><code>update psserverstat
set serveraction = 2 /*Suspend*/
where serverstatus = 3 /*Running*/
and servername = …
/
commit
/
</code></span></pre><ul style="text-align: left;"><li>Restart (after suspension)</li></ul><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: x-small;"><code>update psserverstat
set serveraction = 3 /*Restart*/
where serverstatus = 2 /*Suspended*/
and servername = …
/
commit
/
</code></span></pre><ul style="text-align: left;"><li>Startup (if the Tuxedo domain is running)</li></ul><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: x-small;"><code>update psserverstat
set serveraction = 3
where servername = …
/
commit
/
</code></span></pre>
</div><div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com0tag:blogger.com,1999:blog-25740336.post-53897653520803696572023-04-11T14:35:00.003+01:002023-04-13T12:58:02.785+01:00Oracle SQL Tracing Processes from StartupSometimes, ASH and AWR are not enough. SQL may not be sampled by ASH if it is short-lived, and even if it is sampled, the SQL may not be captured by AWR. Sometimes, in order to investigate a problem effectively, it is necessary to use database session SQL trace. </p><p>It is easy to trace a process initiated by the process scheduler with a trigger (see <a href="https://blog.psftdba.com/2008/05/enabling-oracle-database-trace-on.html">Enabling Oracle Database Trace on PeopleSoft processes with a Trigger</a>).</p><p>Another tactic is to use an <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/CREATE-TRIGGER-statement.html#GUID-AF9E33F1-64D1-4382-A6A4-EC33C36F237B" target="_blank">AFTER LOGON</a> trigger with logic to look at the program name. The program name can be read using <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SYS_CONTEXT.html" target="_blank">SYS_CONTEXT()</a>. If it matches what I am looking for, I can enable session trace.</p><p>Here is an example I used for the OpenXML nVision server PSNVSSRV</p><p></p><ul style="text-align: left;"><li>I want to trace SQL and not any wait events or bind variables. Therefore, I will set event <a href="https://antognini.ch/2012/08/event-10046-full-list-of-levels/" target="_blank">10046</a> at level 1.</li><li>I also set a <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/TRACEFILE_IDENTIFIER.html" target="_blank">tracefile_identifier</a> that will be included in the trace file name, so I can more easily identify the trace file.</li></ul>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: x-small;"><code>REM additional SQL trace triggers
CREATE OR REPLACE TRIGGER sysadm.gfc_nvision_trace_on_logon
AFTER LOGON
ON sysadm.schema
DECLARE
l_process_instance INTEGER;
l_program VARCHAR2(64 CHAR);
l_sql VARCHAR2(100);
BEGIN
SELECT sys_context('USERENV', 'CLIENT_PROGRAM_NAME')
INTO l_program
FROM dual;
IF l_program like 'PSNVSSRV%' THEN --then this is a NVISION session
EXECUTE IMMEDIATE 'ALTER SESSION SET tracefile_identifier = ''PSNVSSRV''';
EXECUTE IMMEDIATE 'ALTER SESSION SET events ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 1''';
END IF;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
show errors
ALTER TRIGGER sysadm.gfc_nvision_trace_on_logon ENABLE;</code></span></pre>
See also <a href="https://blog.go-faster.co.uk/2023/04/reading-trace-files-with-sql.html">Reading Trace files with SQL</a><div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com0tag:blogger.com,1999:blog-25740336.post-26906458756607841212022-10-17T12:41:00.004+01:002022-10-17T12:45:38.537+01:00Adding Flags to Trace Level Overrides in Process Definitions<p>A trace level is set in a process definition in <a href="https://www2.go-faster.co.uk/peopletools/prcsdefn.htm" target="_blank">PS_PRCSDEFN</a> precedence over a trace level set in the process scheduler configuration file (<a href="https://docs.oracle.com/cd/F52213_01/pt859pbr3/eng/pt/tprs/EditingthePeopleSoftProcessSchedulerConfigurationFile-dc0773.html#u49020ce3-19e3-4439-99a6-257034c5a3d5" target="_blank">psprcs.cfg</a>).</p><p>I often set the process scheduler trace level for Application Engine to 1152 to enable batch timings to both the database batch timings tables and the AE trace file, but then I often find that a trace is left enabled on a few processes to aid performance analysis of a troublesome process.</p>
<p>This script updates the trace level set in the parameter list in the process definition to include the bit flags set by 1152 (to enable batch timings). </p><p></p><ul style="text-align: left;"><li>The current trace level is extracted with regular expression substring functions.</li><li>A bitwise OR is performed between the current trace level and the desired settings. There is no single function to do this in Oracle SQL, but it can be calculated simply (see <a href="https://blog.go-faster.co.uk/2022/10/there-is-no-bitor-in-oracle-sql.html">Oracle blog: There is no BITOR() in Oracle SQL</a>). </li><li>The old trace value is replaced with the new one in the parameter list with a regular expression replace function.</li><li>The version number on the process definition is also updated as it would be if updated by the process definition component in the PIA. Thus it is correctly re-cached by the process scheduler, the scheduler does not need to be recycled, nor does the cache need to be cleared</li></ul><p></p><p>The script is available on <a href="https://github.com/davidkurtz/psscripts/blob/master/fixprcstracelevel.sql" target="_blank">Github</a>.</p><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: x-small;"><code>REM fixprcstracelevel.sql
set pages 99 lines 200 serveroutput on
spool fixprcstracelevel append
ROLLBACK;
DECLARE
l_counter INTEGER := 0;
l_trace_expr VARCHAR2(20); /*expression containing TRACE keyword and value*/
l_req_trace_level INTEGER := 1152; /*trace value set in the scheduler config*/
l_cur_trace_level INTEGER; /*current trace level*/
l_new_trace_level INTEGER; /*new calculated trace level*/
l_parmlist ps_prcsdefn.parmlist%TYPE;
BEGIN
for i in (
SELECT t.*
FROM ps_prcsdefn t
WHERE UPPER(t.parmlist) LIKE '%-%TRACE%'
AND prcstype LIKE 'Application Engine'
-- AND parmlisttype IN('1','2','3')
) LOOP
l_trace_expr := REGEXP_SUBSTR(i.parmlist,'\-trace[ ]*[0-9]+',1,1,'i');
l_cur_trace_level := TO_NUMBER(REGEXP_SUBSTR(l_trace_expr,'[0-9]+',1,1,'i'));
l_new_trace_level := l_req_trace_level+l_cur_trace_level-bitand(l_cur_trace_level,l_req_trace_level);
l_parmlist := REGEXP_REPLACE(i.parmlist,l_trace_expr,'-TRACE '||l_new_trace_level,1,1,'i');
IF l_new_trace_level = l_cur_trace_level THEN
dbms_output.put_line(i.prcstype||':'||i.prcsname||':'||i.parmlist||'=>No Change');
ELSE
l_counter := l_counter + 1;
IF l_counter = 1 THEN
UPDATE psversion
SET version = version+1
WHERE objecttypename IN('SYS','PPC');
UPDATE pslock
SET version = version+1
WHERE objecttypename IN('SYS','PPC');
END IF;
dbms_output.put_line(l_counter||':'||i.prcstype||' '||i.prcsname||':'||i.parmlist||'=>'||l_parmlist);
UPDATE ps_prcsdefn
SET version = (SELECT version FROM psversion WHERE objecttypename = 'PPC')
, parmlist = l_parmlist
WHERE prcstype = i.prcstype
AND prcsname = i.prcsname;
END IF;
END LOOP;
COMMIT;
END;
/
spool off</code></span></pre>
The script reports the old and new parameter list setting for each process definition altered. <div>Below is a sample output:<div>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; text-align: left; width: 95%;"><span style="font-size: xx-small;"><code>Application Engine:GL_JEDIT:-TRACE 1159=>No Change
1:Application Engine PTDEFSECINRL:-toolstacepc 2048 -toolstracesql 15 -TRACE 15=>-toolstacepc 2048 -toolstracesql 15 -TRACE 1167</code></span></pre>
</div></div><ul><li>TRACE for GL_JEDIT is already 1159, so no change is required.</li><li>TRACE for PTDEFSECINRL was changed from 15 to 1167.</li></ul><div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com0tag:blogger.com,1999:blog-25740336.post-49990109758282740912021-08-11T16:31:00.004+01:002022-09-13T22:30:21.542+01:00Reporting View HierarchiesIt is a characteristic of PeopleSoft that it uses lots of views to present data within the application, and frequently views reference other views. There are examples of this design going 5 levels deep in HR and deeper in Financials. When faced with a SQL execution plan for such a view you often wonder which view referenced which table.<p>However, the Oracle database describes <i><a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/ALL_DEPENDENCIES.html#GUID-F9EA7DFB-5471-4B07-BDEF-FDE5DF57D1F4" target="_blank">"dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user, including dependencies on views created without any database links"</a></i> in the view <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/ALL_DEPENDENCIES.html#GUID-F9EA7DFB-5471-4B07-BDEF-FDE5DF57D1F4" target="_blank">ALL_DEPENDENCIES</a>.</p>
<p>This hierarchical query (<i><a href="https://github.com/davidkurtz/psscripts/blob/master/depend_hier.sql" target="_blank">depend_heir.sql</a></i>) on this view will report the structure of views within views. </p>
<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: x-small;"><code>REM <a href="https://github.com/davidkurtz/psscripts/blob/master/depend_hier.sql" target="_blank">depend_hier.sql</a>
undefine view_name
set pages 999 lines 176 long 50000
break on name skip 1 on owner
ttitle 'Dependency Hierarchy'
column my_level format a5 heading 'Level'
column owner format a12
column name format a18
column type format a7
column referenced_type format a7 heading 'Refd|Type'
column referenced_owner format a6 heading 'Refd|Owner'
column referenced_name format a18 heading 'Refd|Name'
column referenced_link_name format a10 heading 'Refd|Link'
column dependency_type heading 'Dep|Type'
column text heading 'View Text' format a80 wrap on
spool depend_hier.&&view_name..lst
with d as (
select * from all_dependencies
union all
select null, null, null, owner, view_name, 'VIEW', null, null
from all_views
where owner = 'SYSADM' and view_name = UPPER('&&view_name')
)
select LPAD(TO_CHAR(level),level,'.') my_level
, d.type, d.owner, d.name
, d.referenced_type, d.referenced_owner, d.referenced_name, d.referenced_link_name
, d.dependency_type
, v.text
from d
left outer join all_views v
on v.owner = d.referenced_owner
and v.view_name = d.referenced_name
connect by nocycle
d.name = prior d.referenced_name
and d.owner = prior d.referenced_owner
start with d.owner IS NULL and d.name IS NULL
/
spool off
ttitle off</code></span></pre>For example, this is the report for PS_POSN_HISTORY3 from a demo HCM database. It is only three levels deep. <i>"POSN_HISTORY3 is the third of three nested views which retrieve position incumbent history. It selects job records with effective dates before position exits to obtain exit salaries."
</i><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: 50%;"><code>Wed Aug 11 page 1
Dependency Hierarchy
Refd Refd Refd Refd Dep
Level TYPE OWNER NAME Type Owner Name Link Type View Text
----- ------- ------------ ------------------ ------- ------ ------------------ ---------- ---- --------------------------------------------------------------------------------
1 VIEW SYSADM PS_POSN_HISTORY3 SELECT A.Position_Nbr ,A.Position_Entry_Dt ,A.Emplid ,A.EMPL_RCD ,B.EFFDT ,B.EFF
SEQ ,B.Sal_Admin_Plan ,B.Grade ,B.Step ,B.Comprate ,B.Comp_Frequency ,B.Currency
_Cd ,' ' ,' ' ,' ' ,' ' FROM PS_POSN_HISTORY2 A ,PS_JOB B WHERE B.EmplID = A.Emp
lID AND B.EMPL_RCD = A.EMPL_RCD AND B.Position_Nbr = A.Position_Nbr AND B.EffDt
= ( SELECT MAX(C.EffDt) FROM PS_JOB C WHERE C.EmplID = B.EmplID AND C.EMPL_RCD =
B.EMPL_RCD AND (C.EffDt < A.Position_End_Dt OR (C.EffDt = A.Position_End_Dt AND
C.EffSeq = A.EffSeq - 1))) AND B.Effseq = ( SELECT MAX(C.Effseq) FROM PS_JOB C
WHERE C.EmplID = B.EmplID AND C.EMPL_RCD = B.EMPL_RCD AND (C.EffDt < A.Position_
End_Dt OR (C.EffDt = A.Position_End_Dt AND C.EffSeq = A.EffSeq - 1)))
.2 VIEW SYSADM PS_POSN_HISTORY3 TABLE SYSADM PS_JOB HARD
.2 VIEW VIEW SYSADM PS_POSN_HISTORY2 HARD SELECT A.Position_Nbr , A.Position_Entry_Dt , A.Emplid , A.EMPL_RCD , B.EffDt ,
B.EffSeq , B.Action FROM PS_POSN_HISTORY A , PS_JOB B WHERE A.EmplID = B.EmplID
AND A.Empl_Rcd = B.Empl_Rcd AND B.EffDt = ( SELECT MIN(C.EffDt) FROM PS_JOB C WH
ERE C.EmplID = B.EmplID AND C.Empl_Rcd = B.Empl_Rcd AND (C.EffDt > A.Position_En
try_Dt OR (C.EffDt = A.Position_Entry_Dt AND C.EffSeq > A.EffSeq)) AND ((C.Posit
ion_Nbr <> A.Position_Nbr) OR (C.HR_STATUS <> 'A'))) AND B.EffDt<=TO_DATE(TO_CHA
R(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') AND B.EffSeq = ( SELECT MIN(D.EffSeq) FROM
PS_JOB D WHERE D.EmplID = B.EmplID AND D.Empl_Rcd = B.Empl_Rcd AND D.EffDt = B.
EffDt AND ((D.Position_Nbr <> A.Position_Nbr) OR (D.HR_STATUS <> 'A')))
..3 VIEW SYSADM PS_POSN_HISTORY2 TABLE SYSADM PS_JOB HARD
..3 VIEW VIEW SYSADM PS_POSN_HISTORY HARD SELECT A.Position_Nbr ,A.Position_Entry_Dt ,A.Emplid ,A.EMPL_RCD ,A.EFFSEQ ,A.EF
FDT ,A.Sal_Admin_Plan ,A.Grade ,A.Step ,A.CompRate ,A.Comp_Frequency ,A.Currency
_Cd ,' ' ,' ' ,' ' ,' ' FROM PS_Job A WHERE A.Position_Entry_Dt = A.Effdt AND A.
Effseq = ( SELECT MIN(B.Effseq) FROM PS_Job B WHERE B.Emplid = A.Emplid AND B.EM
PL_RCD = A.EMPL_RCD AND B.Effdt = A.Effdt AND B.Position_Nbr = A.Position_Nbr)
...4 VIEW SYSADM PS_POSN_HISTORY TABLE SYSADM PS_JOB HARD</code></span></pre>
We can see from the report that view <i>PS_POSN_HISTORY3</i> calls view <i>PS_POSN_HISTORY2</i> that in turn calls view <i>PS_POSN_HISTORY</i>. <div>Each of the views also contains multiple references to <i>PS_JOB</i> that perform various effective date/sequence sub-queries. Where there are multiple references to the same object, there is still only one dependency.
</div><div>The script is available on <a href="https://github.com/davidkurtz" target="_blank">Github</a> as a part of my <a href="https://github.com/davidkurtz/psscripts" target="_blank"><i>psscripts</i></a> repository.</div><div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com0tag:blogger.com,1999:blog-25740336.post-70149824046909971952021-08-06T14:50:00.000+01:002021-08-06T14:50:36.069+01:00Oracle Active Data Guard in PeopleSoft with Oracle 19c DML Redirection<p>Active Data Guard is a configuration option that became available in Oracle 11g where a standby database is maintained as a synchronised physical replica of the primary database and is also open for read-only SQL queries.</p><p>PeopleSoft added configuration to direct certain read-only components and processes to an ADG standby using secondary connections in the application servers and process schedulers. However, in PeopleSoft, all scheduled processes update at least the process scheduler request tables, even if they make no updates to application tables. This cannot be done on a read-only standby database and must be directed back to the primary database.</p>
<p>PeopleBooks sets out a method for <a href="https://docs.oracle.com/cd/F30998_01/pt858pbr2/eng/pt/tadm/task_ImplementingOracleActiveDataGuard-3b7d04.html" target="_blank">Implementing Active Data Guard</a> (this link is to the PeopleTools 8.58 documentation). It uses a second schema to which PeopleSoft application servers and process schedulers connect using a second access profile. The second schema contains synonyms for each table and view that either point to the corresponding object in the original schema on the ADG standby, or if the object is going to be updated by the application then via database links to the corresponding object in the primary database. This approach requires knowledge of which tables are updated during otherwise read-only processing, a lot of scripting to generate all the synonyms and grants, and ongoing maintenances as new objects are added to the database.</p>
<p>However, that approach is rendered obsolete by Active Data Guard DML redirection, a new feature in 19c. This post explains how to configure PeopleSoft to make use of Active Data Guard on Oracle 19c with DML redirect.</p>
<p>With DML redirection enabled, updates on the secondary database are automatically redirected back to the primary database via a SQL*Net connection between the databases (not unlike a database link), and then they will be replicated back to the standby database like any other change. PeopleSoft no longer needs to be configured specially to handle updated tables differently. Consequently, the PeopleSoft ADG configuration is massively simplified.</p><p>Processes no longer have to be strictly read-only to run on the ADG database. If there are only a small quantity of updates the redirect can handle it.</p><h3 style="text-align: left;">Database Initialisation Parameters</h3><div>The parameter <i>ADG_REDIRECT_DML</i> should be set to true on both the primary and active data guard standby databases.</div>
<h3 style="text-align: left;">PeopleSoft Connection Configuration</h3>
<div>A second row must be added to <i>PS.PSDBOWNER</i> to map the TNS name of the standby database to the database owner ID, in this case <i>SYSADM</i>.</div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: small;"><code>SQL>select * from ps.psdbowner;
DBNAME OWNERID
-------- --------
FINPRD SYSADM
FINADG SYSADM</code></span></pre><h3 style="text-align: left;">Application Server Configuration</h3><div>To facilitate read-only components in the PIA (such as the query manager) redirecting to the standby database, all Application Server domains should still be configured to connect to both the primary database and the standby database, as described in the PeopleSoft documentation<pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: small;"><code>[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
<b>DBName=FINPRD</b>
DBType=ORACLE
UserId=PSAPPS
UserPswd={V2}FEhk7rIFt2f0GRYaH6B9la8DXXMNtsz1kPZ+
ConnectId=PEOPLE
ConnectPswd={V2}Mw3RFr0MHFBpJHbqXh7Dx9qCsO7TFT4G
<b>StandbyDBName=FINADG
</b>StandbyDBType=ORACLE
StandbyUserId=PSAPPS
StandbyUserPswd={V2}Ski/r2xYCvbTbBhXOGfH8HO7zCRxoDFK5rmb</code></span></pre><h3 style="text-align: left;">Process Scheduler Configuration</h3></div><div>The approach for Process Schedulers is slightly different.</div><h4 style="text-align: left;">Application Engine Limitation in Active Data Guard</h4><div>It is a documented limitation of Application Engine that only <i>PSAESRV</i> Application Engine server processes can connect via the second connection to the standby database. If a standalone PSAE process attempts this the connection will fail. </div><div>See:</div><div><ul style="text-align: left;"><li>Oracle Support Note: E-AE: Application Engine Process Might Stay in Initiated Status if PSAESRV Disabled on PeopleSoft ADG Enabled Environment (Doc ID <a href="https://support.oracle.com/epmos/faces/DocContentDisplay?id=1641764.1" target="_blank">1641764.1</a>)</li><ul><li>This was raised as Bug <a href="https://support.oracle.com/epmos/faces/BugDisplay?parent=DOCUMENT&sourceId=1641764.1&id=18482301" target="_blank">18482301</a>: PSAE may stay in initiated status on ADG configured environment. It was closed as 'not a bug'.</li></ul><li><a href="http://www2.go-faster.co.uk/docs/PSFT.Two Temporary Tablespaces.pdf" target="_blank">Using Two Temporary Tablespace in PeopleSoft</a>.</li></ul></div>
<div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: x-small;"><code>Error in sign on
Database Type: 7 (ORACLE)
Database Name: HCM91
Server Name:
OperID:
ConnectID: people
Process Instance: 0
<b> Reason: Invalid user ID or password for database signon. (id=)</b>
<b> Note: Attempt to authenticate using GUID 6a1ced41-2fe0-11e2-9183-be3e31d6e740</b>
Invalid command line argument list.
process command line: -CT ORACLE -CD HCM91 -GUID 6a1ced41-2fe0-11e2-9183-be3e31d6e740 -SS NO -SN NO
GUID command line : -CT ORACLE -CD HCM91 -CO "PS" -CP Unavailable -R 1 -I 852 -AI AEMINITEST -OT 6 -FP
"C:\app\pt\appserv\prcs\HCM91PSNT\log_output\AE_AEMINITEST_852\" -OF 1</code></span></pre>To continue to use stand-alone PSAE processes, as recommended in the PeopleTools Performance Guidelines Red Paper (Doc ID: <a href="https://support.oracle.com/epmos/faces/DocContentDisplay?id=747389.1" target="_blank">747389.1</a>), it is necessary to configure separate process schedulers that connect only to the Active Data Guard standby database, and processes will need to be redirected appropriately by process categories to these schedulers.</div>
<h4 style="text-align: left;">System Settings</h4>
<div>Process Categories can be used to restrict certain processes to certain processes schedulers. In the Process Scheduler System Settings component, create a new category ADGOnly.</div>
<div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiObw5uB3WCHDgJcrvk3qG24jW_ArJzvfAYKgHntZWnmxu1vcSHBjNzjYK93VNdPxvdmusHvYqjALhKtxwf-HOUD-Z0haZGDKPWjxTeo9VtEmcsW6YWwthA2zdFrL-zinYE36ZH/" style="margin-left: 1em; margin-right: 1em;"><img alt="Process Scheduler Process Category Admin" data-original-height="319" data-original-width="506" height="253" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiObw5uB3WCHDgJcrvk3qG24jW_ArJzvfAYKgHntZWnmxu1vcSHBjNzjYK93VNdPxvdmusHvYqjALhKtxwf-HOUD-Z0haZGDKPWjxTeo9VtEmcsW6YWwthA2zdFrL-zinYE36ZH/w400-h253/image.png" title="Process Scheduler Process Category Admin" width="400" /></a></div>
<h4 style="text-align: left;">New Process Schedulers for ADG</h4>
<div>New process schedulers should be created for running ADG only jobs. They will <b>only </b>be connected to the standby database. The standby connection should be left blank. All of the SQL updates made by the Process Scheduler processes will be handled by DML redirection. You will need to have at least 2 so they do not become a single point of failure.</div></div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: small;"><code>[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
<b>DBName=FINADG
</b>DBType=ORACLE
UserId=PSAPPS
UserPswd={V2}YoAQq7Ut4WBHJL89N9gv9E0AWwLaecGZ4qep
ConnectId=PEOPLE
ConnectPswd={V2}OlSYHuFMZa2c8uonfYkKk+3+APYvTU9N
<b>StandbyDBName=</b>
StandbyDBType=
StandbyUserId=
StandbyUserPswd=</code></span></pre>These schedulers will ONLY run:<div><ul style="text-align: left;"><li>Processes in the new ADGOnly process category. </li><li>A single LOADCACHE category process will be permitted.</li><li>The max concurrence of all other categories will be 0.</li><li>Other process types such as SQR might be needed if they are to be run on the ADG standby</li></ul></div><div>They should not run master process scheduler processes. This process should only run schedulers connected to the primary database.</div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQ1H1xx2fanrgLHw9Oc_ppdnj1ME-ebP-Q6mBjZ9nY8nhzMzweo1eO6CRzRCwiizrxE5U_OLm1wK9DsRsNb-ruV5Q7uQJGgvHcF1WSFu-QnJCoT_kJiUpdJA0rL8qC8G2HK-AA/" style="margin-left: 1em; margin-right: 1em;"><img alt="ADG Process Scheduler Configuration" data-original-height="435" data-original-width="506" height="550" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQ1H1xx2fanrgLHw9Oc_ppdnj1ME-ebP-Q6mBjZ9nY8nhzMzweo1eO6CRzRCwiizrxE5U_OLm1wK9DsRsNb-ruV5Q7uQJGgvHcF1WSFu-QnJCoT_kJiUpdJA0rL8qC8G2HK-AA/w640-h550/image.png" title="ADG Process Scheduler Configuration" width="640" /></a></div><h4 style="text-align: left;">Existing Process Schedulers</h4><div>All process categories apply to all process schedulers. The concurrency of the ADG category should be set to 0 on all other process schedulers to prevent it from running there. These schedulers will remain connected to the primary database only. They will not be connected to the secondary database.</div></div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: small;"><code>[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
<b>DBName=FINPRD
</b>DBType=ORACLE
UserId=PSAPPS
UserPswd={V2}EcxeV3mit3GMT5kDfz/z+s0L9B1aUb6ix04f
ConnectId=PEOPLE
ConnectPswd={V2}NtXafW7hlcGY016bhazl2kqqvlSNYMK1
<b>StandbyDBName=
StandbyDBType=
StandbyUserId=
StandbyUserPswd=</b></code></span></pre>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRgOpz-bttQ6rFUshCfQIBqlr1fcg48nL7qP3l4xY7arB2Rt3iklkdT5wcMN_Vo6AUTtS8j7iQ3-l7zhp-0VHLlH3Pw95DAaVAzXoxQ5Bfuvd4PfcBPBa1Sq-aKoU0Dl3U6_52/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="319" data-original-width="506" height="404" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRgOpz-bttQ6rFUshCfQIBqlr1fcg48nL7qP3l4xY7arB2Rt3iklkdT5wcMN_Vo6AUTtS8j7iQ3-l7zhp-0VHLlH3Pw95DAaVAzXoxQ5Bfuvd4PfcBPBa1Sq-aKoU0Dl3U6_52/w640-h404/image.png" width="640" /></a></div><h4 style="text-align: left;">Process Definition</h4><div>Normally, the processes that are to be run on ADG should be marked read-only and then they will be scheduled using the secondary connection. However, Application Engine cannot be run in standalone PSAE mode via the second connection. So, the ADG process schedulers are configured to connect directly to the ADG database via the primary connection. Thus, the read-only flag has no effect. Instead, all read-only processes should be moved to the ADGOnly category.</div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhaCNuDZS0bq2dJy6utKfNdvOw01RH0dNOGRgmc9QFkd0jnB5OumLVa8IGQa2eTOZ7J-ekng09DfcFdz7oUFofHO4eHXrP631WTWOoWLzQGDoitUWRXelhUs-IBW5EdrU70nE-/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="442" data-original-width="508" height="557" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhaCNuDZS0bq2dJy6utKfNdvOw01RH0dNOGRgmc9QFkd0jnB5OumLVa8IGQa2eTOZ7J-ekng09DfcFdz7oUFofHO4eHXrP631WTWOoWLzQGDoitUWRXelhUs-IBW5EdrU70nE-/w640-h557/image.png" width="640" /></a></div><h4 style="text-align: left;">Configuration by SQL</h4><div>It may be easier to update the process scheduler configuration by SQL</div></div><div><ul style="text-align: left;"><li>ADGOnly concurrency will be 0, or it will be the maximum API aware concurrency on ADG process schedulers.</li><li>PSQUERY, any read-only processes, and any processes in <i>ADGOnly</i> category, will all be marked as being both read-only and in the <i>ADGOnly</i> category.</li></ul></div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: small;"><code>update pslock
set version = version + 1
where objecttypename IN('SYS','PPC')
/
update psversion
set version = version + 1
where objecttypename IN('SYS','PPC')
/
update ps_servercategory c
set maxconcurrent = CASE WHEN servername like 'PSUNX_A%'
THEN (SELECT s.maxapiaware FROM ps_serverdefn s
WHERE s.servername = c.servername)
ELSE 0 END
where prcscategory = 'ADGOnly'
/
update ps_serverdefn
set version = (SELECT version from psversion where objecttypename = 'PPC')
, lastupddttm = systimestamp
/
update ps_prcsdefn
set version = (SELECT version from psversion where objecttypename = 'PPC')
, prcsreadonly = 1
, prcscategory = 'ADGOnly'
, lastupddttm = systimestamp
where prcsreadonly = 1
or prcscategory = 'ADGOnly'
or prcsname = 'PSQUERY'
/
select prcstype, prcsname, prcscategory, prcsreadonly
from ps_prcsdefn
where prcsreadonly = 1
or prcscategory = 'ADGOnly'
or prcsname = 'PSQUERY'
/
select * from ps_servercategory
where prcscategory IN('ADGOnly')
order by 2,1
/
commit
/</code></span></pre><h4 style="text-align: left;">
Sample Output</h4><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: small;"><code>PSOFT-FINADG>Select prcstype, prcsname, prcscategory, prcsreadonly
2 From ps_prcsdefn
3 where prcsreadonly = 1
4 or prcscategory = 'ADGOnly'
5 or prcsname = 'PSQUERY'
6 /
PRCSTYPE PRCSNAME PRCSCATEGORY P
------------------------------ ------------ ------------------------------ -
Application Engine AEMINITEST ADGOnly 1
COBOL SQL PTPDBTST ADGOnly 1
SQR Report PTSQRTST ADGOnly 1
Application Engine FB_GEN_EXTR ADGOnly 1
SQR Report XRFWIN ADGOnly 1
SQR Report SWPAUDIT ADGOnly 1
SQR Report SYSAUDIT ADGOnly 1
SQR Report XRFAPFL ADGOnly 1
SQR Report XRFAEPL ADGOnly 1
SQR Report XRFPGDZ ADGOnly 1
SQR Report DDDAUDIT ADGOnly 1
SQR Report XRFEVPC ADGOnly 1
SQR Report XRFFLPC ADGOnly 1
SQR Report XRFFLPN ADGOnly 1
SQR Report XRFFLRC ADGOnly 1
SQR Report XRFIELDS ADGOnly 1
SQR Report XRFMENU ADGOnly 1
SQR Report XRFPANEL ADGOnly 1
SQR Report XRFPCFL ADGOnly 1
SQR Report XRFPNPC ADGOnly 1
SQR Report XRFRCFL ADGOnly 1
SQR Report XRFRCPN ADGOnly 1
Application Engine PSQUERY ADGOnly 1
Application Engine PSCONQRS ADGOnly 1
SYSADM-FINADG>select * from ps_servercategory
2 where prcscategory IN('ADGOnly')
3 order by 2,1
4 /
SERVERNA PRCSCATEGORY P MAXCONCURRENT
-------- ------------------------------ - -------------
…
PSNT ADGOnly 5 0
PSUNX ADGOnly 5 0
PSUNX_A1 ADGOnly 5 5
…</code></span></pre>
<h3 style="text-align: left;">PSQUERY Application Engine Amendment</h3>
<div>If you have PS/Queries that reference a remote database via a database link then you may experience errors when you run them via the scheduled Application Engine process PSQUERY on an ADG database.</div>
<div>Using a database link, even if only for a query, starts a database transaction, and then you can't get DML redirect to work because it also starts a transaction. However, if you already have a transaction open through DML redirect you can't start the transaction associated with the database link. The two transactions become mutually exclusive.</div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: 50%;"><code>PeopleTools 8.58.07 - Application Engine
Copyright (c) 1988-2021 Oracle and/or its affiliates.
All Rights Reserved
PeopleTools SQL Trace value: 159 (0x9f): /cs/psoft/cfg/FINPRD/appserv/prcs/FINPRDA1/log_output/AE_PSQUERY_xxxxxxxx/AE_PSQUERY_xxxxxxxx.trc
PeopleTools PeopleCode Trace value: 64 (0x40): /cs/psoft/cfg/FINPRD/appserv/prcs/FINPRDA1/log_output/AE_PSQUERY_xxxxxxxx/AE_PSQUERY_xxxxxxxx.trc
<b>File: /vob/peopletools/src/pssys/qpm.cppSQL error. Stmt #: 8495 Error Position: 3055 Return: 16000 - ORA-16000: database or pluggable database open for read-only access </b>
Failed SQL stmt: SELECT …
<a a="" database="" link="" query="" references="" remote="" that="" via=""><i><a query that references a remote database via a database link></i>
…
<b>Error in running query because of SQL Error, Code=16000, Message=ORA-16000: database or pluggable database open for read-only access (50,380)</b>
PeopleCode Exit(1) Abort invoked by Application at PSQUERY.MAIN.ExecQry. (108,543)
Process xxxxxxxx ABENDED at Step PSQUERY.MAIN.ExecQry (PeopleCode) -- RC = 16 (108,524)
Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s</a></code></span></pre>So we need to close the transactions by making the PSQUERY application engine commit. Application Engine steps explicitly commit after each step. I have added an extra step added that does nothing, but Application Engine issues a commit<div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiW_OFpbFx95tu4Fh4uwUzEWAixzyuupg76Jpjq5h36FOqGSVlF3PFF8Nh1b1gO7CNPy-jnGnWtr1CBeJHRQNweKBBy8Y-FBvQojXj7lgGrHCQzqedb31doEvrIJat37Evo0EV9/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="88" data-original-width="496" height="114" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiW_OFpbFx95tu4Fh4uwUzEWAixzyuupg76Jpjq5h36FOqGSVlF3PFF8Nh1b1gO7CNPy-jnGnWtr1CBeJHRQNweKBBy8Y-FBvQojXj7lgGrHCQzqedb31doEvrIJat37Evo0EV9/w640-h114/image.png" width="640" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhi8xf2a9fpU5HdptND7CenkNmSPTL7DyHwC3huDP-1IQwvOzn8XLn2OecYq-8EDAfn8zEGfF3nvKIUn41O8Nq4IfVox3uHPdHaaWKr4qXi-mgO4zqHgBWKfxW7tlycneXmdNfG/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="283" data-original-width="508" height="356" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhi8xf2a9fpU5HdptND7CenkNmSPTL7DyHwC3huDP-1IQwvOzn8XLn2OecYq-8EDAfn8zEGfF3nvKIUn41O8Nq4IfVox3uHPdHaaWKr4qXi-mgO4zqHgBWKfxW7tlycneXmdNfG/w640-h356/image.png" width="640" /></a></div><div><h3 style="text-align: left;">Disable Query Statistics</h3><div>I have found that query statistics can cause a problem when the query is run on ADG.</div></div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: 50%;"><code>PeopleTools 8.58.07 - Application Engine
Copyright (c) 1988-2021 Oracle and/or its affiliates.
All Rights Reserved
PeopleTools SQL Trace value: 159 (0x9f): /cs/psoft/cfg/FINPRD/appserv/prcs/FINPRDA2/log_output/AE_PSQUERY_19356674/AE_PSQUERY_19356674.trc
File: /vob/peopletools/src/pssys/qdmutil.cppSQL error. Stmt #: 4608 Error Position: 91 Return: 16397 - <b>ORA-16397: statement redirection from Oracle Active Data Guard standby database to primary database failed
Failed SQL stmt: SELECT EXECCOUNT, AVGEXECTIME, AVGFETCHTIME, LASTEXECDTTM, AVGNUMROWS, OPRID, QRYNAME FROM <a href="https://www2.go-faster.co.uk/peopletools/psqrystats.htm" target="_blank">PSQRYSTATS</a> WHERE OPRID = :1 AND QRYNAME = :2 FOR UPDATE OF EXECCOUNT</b>
Application Engine program PSQUERY ended normally</code></span></pre><div>They can be disabled in the PIA</div><div><ul style="text-align: left;"><li>Navigate to: PeopleTools, Utilities, Administration, Query Administration, </li><li>Go to the last tab (Settings), </li><li>Uncheck Run Query Statistics.</li></ul><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgk6UuxDE41BKeH2bmJN4vdGzBPlDPzeAUVQwRUQoGUEjAZzm0UIueVmccy_KXj5HKAl_7lZTooWqWcshxWIC3gG_oUZuXZJvG-6yiMQvUHHw9AhnMFhLbbYzO-f7Y7mG3eXbwQ/" style="margin-left: 1em; margin-right: 1em;"><img alt="" data-original-height="193" data-original-width="345" height="224" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgk6UuxDE41BKeH2bmJN4vdGzBPlDPzeAUVQwRUQoGUEjAZzm0UIueVmccy_KXj5HKAl_7lZTooWqWcshxWIC3gG_oUZuXZJvG-6yiMQvUHHw9AhnMFhLbbYzO-f7Y7mG3eXbwQ/w400-h224/image.png" width="400" /></a></div><h3 style="text-align: left;">Enable Query Execution Logging</h3><div>Query stats (logged in the table <a href="https://www2.go-faster.co.uk/peopletools/psqrystats.htm" target="_blank">PSQRYSTATS</a>) are not particularly useful as they only compute average metrics over a period that is not defined. Instead, query execution logging (to the table <a href="https://www2.go-faster.co.uk/peopletools/psqryexeclog.htm" target="_blank">PSQRYEXECLOG</a>) is far more useful for performance analysis because you know who ran the query when it ran, and how many rows it retrieved. It is enabled for individual queries. It can be enabled across the board with the following SQL.</div></div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: small;"><code>update pslock
set version = version + 1
where objecttypename IN('SYS','QDM')
/
update psversion
set version = version + 1
where objecttypename IN('SYS','QDM')
/
update psqrydefn
set version = (SELECT version from psversion where objecttypename = 'QDM')
, execlogging = 'Y'
, lastupddttm = systimestamp
where execlogging != 'Y'
/</code></span></pre></div><div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com0tag:blogger.com,1999:blog-25740336.post-58532565684388847512021-04-12T11:58:00.090+01:002021-04-21T21:22:52.510+01:00Running nVision in a Mixed OpenXML/Excel mode<div style="text-align: left;"><i style="background-color: white; color: #333333; font-family: Georgia, serif; font-size: 13px;">This blog post follows on from a <a href="http://blog.psftdba.com/2017/10/nvision-performance-tuning.html" style="color: #5588aa; text-decoration-line: none;" target="_blank">series</a> that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.</i></div><div style="text-align: left;"></div><div style="text-align: left;"><span style="font-size: xx-small;">Updated 20.4.2021:</span> OpenXML is an alternative method for running PeopleSoft nVision introduced in PeopleTools 8.54. It is recommended by Oracle and is the default configuration in the process scheduler. It generally outperforms Excel because it is a lightweight process and it evades Excel's concurrency limitations.</div>
<div style="text-align: left;">Only one Excel nVision will run concurrently on a Windows server because Excel itself is a single-threaded process since Microsoft Excel 2010. If you need to run 10 concurrent nVision reports you would need 10 windows servers each running a process scheduler that will run a single nVision report in Excel mode. One of the major advantages of OpenXML is that it is not subject to this restriction. See also:</div>
<div style="text-align: left;"><ul style="text-align: left;"><li>Support Note: <a href="https://support.oracle.com/epmos/faces/DocContentDisplay?id=1537491.1" target="_blank">E-NV: nVision Performance using Microsoft Office 2010 and above (Doc ID 1537491.1)</a></li>
<li>Oracle's PeopleSoft Technology Blog: <a href="https://blogs.oracle.com/peopletools/openxml-mode-in-nvision" target="_blank">OpenXML Mode in
Peoplesoft nVision Looks Hard, but it Isn't!</a></li>
<li>PeopleSoft DBA Blog: <a href="https://blog.psftdba.com/2017/11/nvision-performance-tuning-11-excel-v.html">nVisionPerformance Tuning: 11. Excel -v- OpenXML</a></li></ul>
<h3 style="text-align: left;">What is the Problem?</h3>
<div>I have worked with PeopleSoft Financials customers where it became apparent that some of their nVision layouts did not run correctly on OpenXML, and until they could be redeveloped they had to run on Excel. The problems seem to be around pagination and nPlosion. Also, some variables are not populated when the layouts are run in OpenXML. For example, the report on the left was run in OpenXML, and the one on the right was run in Excel.</div>
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCDNau5mhiijE1eh_yzIz0liJBNK9fiim8Fy6920W7WIo4GHDhZE81leX8TiTccFyOIQvT5qRcNh71lR5fEdA3VmbGuVEXiIzX9vT2-qFUNclX80VWqwXjKagAdwyTxsPS2fBc/s789/nvision1.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="46" data-original-width="789" height="38" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCDNau5mhiijE1eh_yzIz0liJBNK9fiim8Fy6920W7WIo4GHDhZE81leX8TiTccFyOIQvT5qRcNh71lR5fEdA3VmbGuVEXiIzX9vT2-qFUNclX80VWqwXjKagAdwyTxsPS2fBc/w640-h38/nvision1.png" width="640" /></a></div>
<div>I have also found a very few layouts, that contrary to expectation, perform better on Excel than OpenXML.</div>
<div>Reverting to Excel is the only other option, but to do so across the board would have a significant performance impact. However, my experience suggests that customers can run the majority of their layouts perfectly successfully on OpenXML, but a relatively small proportion still has to be run on Excel. </div>
<div>A mixed mode of operation is therefore a good option, where nVision is run on OpenXML by default, but certain reports and report books are run on Excel. It avoids having to train end-users to run certain reports on certain process schedulers. </div>
<div>This blog sets out a method by which reports using certain nVision layouts are automatically redirected to certain process schedulers that run Excel nVision by a combination of configuring process categories and two database triggers to adjust the request records before they are picked up by the process schedulers.</div>
<h3 style="text-align: left;">Excel nVision Process Schedulers and OpenXML nVision process schedulers</h3>
<div>Whether nVision is run in Excel or OpenXML mode is determined by the <a href="https://docs.oracle.com/cd/F30998_01/pt858pbr2/eng/pt/tprs/task_EditingthePeopleSoftProcessSchedulerConfigurationFile-dc0773.html#u2c2cfb9a-4035-4d29-aae5-6c48d4fbf316" target="_blank"><i>UseExcelAutomation</i></a> variable in the nVision section of the process scheduler configuration file (<a href="https://docs.oracle.com/cd/F30998_01/pt858pbr2/eng/pt/tprs/task_EditingthePeopleSoftProcessSchedulerConfigurationFile-dc0773.html?pli=ul_d58e208_tprs" target="_blank">psprcs.cfg</a>). It applies to all nVision process run by that scheduler. However, this variable is not documented well in the PeopleTools manual. It takes the following values:</div>
<div><ul style="text-align: left;"><li>0 = Open XML Mode (see <a href="https://support.oracle.com/epmos/faces/DocContentDisplay?id=1317246.1" target="_blank">Oracle Support Note 1317246.1</a>)</li>
<li>1 = Excel Mode </li>
<li>2 = Cross-Platform OpenXML (see <a href="https://support.oracle.com/epmos/faces/DocContentDisplay?id=1304907.1" target="_blank">Oracle Support Note 1304907.1</a>)</li></ul></div>
<div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: xx-small;"><code>…
[nVision]
;=========================================================================
; General settings for nVision
;=========================================================================
…
<b>UseExcelAutomation</b>=2
…</code></span></pre></div>
Today, most PeopleSoft systems run most of their batch processes on Linux/Unix servers. nVision is one of the few PeopleSoft process types that had to run on Windows, and that is still the case for Excel nVision. It is typical for these PeopleSoft systems to have several Windows servers dedicated to nVision execution. To run in a mixed-mode, it is necessary to have an Excel nVision process scheduler and an OpenXML nVision process scheduler on each server.<div><h4 style="text-align: left;">Process Categories </h4><div>In the past, I have suggested creating additional process types, but I think it is easier and more in keeping with the process scheduler structure to create additional process definitions and process categories. It is necessary to create two new process categories (in <i>PeopleTools->Process Scheduler->System Settings</i>)</div></div>
<div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsOll5pSFPNtOquTK51giRA-KyB6I5-ZoIlQO41D7gesh8MeUo6vlkKW2yL_7XQvw7Su15Iv8YnlmUGbx2cNEyBeqcnioAuY6YYLbIlvyXVb83gWnwB24geVTd_LVWmZq-hmoB/" style="margin-left: 1em; margin-right: 1em;"><img alt="" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsOll5pSFPNtOquTK51giRA-KyB6I5-ZoIlQO41D7gesh8MeUo6vlkKW2yL_7XQvw7Su15Iv8YnlmUGbx2cNEyBeqcnioAuY6YYLbIlvyXVb83gWnwB24geVTd_LVWmZq-hmoB/w400-h283/image.png" width="300" /></a></div>
<h4 style="text-align: left;">Servers</h4><div>Process Schedulers (or Servers) should be created in pairs for each physical server available. In this example, I have created PSNT_X1 to run the OpenXML category and PSNT_E1 to run the Excel category. </div>
<div>Note that:</div>
<div><ul style="text-align: left;"><li>Maximum concurrency for the Excel server has been reduced to 1.</li>
<li>The maximum number of API aware processes for PSNT_E1 has also be reduced to 1.</li>
<li>The 'Default' category is disabled on both PSNT_E1 and PSNT_X1.</li>
<li>These servers only run nVision and PSJob process types,</li>
<li>They load balance within the same operating system (although this is not appropriate for PSNT_X1 if you also run OpenXML on other non-Windows process schedulers).</li></ul><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: left;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNGuiO-MMC-MsiRsfN1UMFFusNqcZAY-pbL-cTdqraLcSK0YpDdUy2i-6qmmTCKNBK1meREDIjnaIJZi0CDn8vHoiQBbK18i6TiF-Bqzj5ItRvfFwkOdC3gy3NjOFRnaO-9gTp/s907/nv1.png" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em; text-align: left;"><img border="0" data-original-height="834" data-original-width="907" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNGuiO-MMC-MsiRsfN1UMFFusNqcZAY-pbL-cTdqraLcSK0YpDdUy2i-6qmmTCKNBK1meREDIjnaIJZi0CDn8vHoiQBbK18i6TiF-Bqzj5ItRvfFwkOdC3gy3NjOFRnaO-9gTp/w400-h368/nv1.png" width="320" /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUNRtm68WGZ-dl4KrW4fp-24jc3Dnep7TE2m-mwcPdHEELwqN03IACXnHpHMe305xpB6H9QAgT5YGyKsG2C8RYtfnHtn0wmSh6-EF4_gPl6n9hWbaNzxrZEd5-YteG8n3y45ft/s907/nv2.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="838" data-original-width="907" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUNRtm68WGZ-dl4KrW4fp-24jc3Dnep7TE2m-mwcPdHEELwqN03IACXnHpHMe305xpB6H9QAgT5YGyKsG2C8RYtfnHtn0wmSh6-EF4_gPl6n9hWbaNzxrZEd5-YteG8n3y45ft/w400-h371/nv2.png" width="320" /></a></div></div><h4 style="text-align: left;">Process Definitions</h4>
<div style="text-align: left;">The various nVision process definitions are allocated to the
<i>nVisionOpenXML </i>process category so they run on those process schedulers.</div>
<div style="text-align: left;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhv_qF_bmCSvzLXPo6xvz73e0MouHUGTGzrp3uEkZD-fSrI2nVwI2dGQV1AzTcaD7GxyiZmowyQWnDF7byDuWrDYQpfk9WS2UYdeXFQ3D8nOYVOCa8xnJuj_xrc0YAYUmZXQI43/s874/nv3.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="475" data-original-width="874" height="217" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhv_qF_bmCSvzLXPo6xvz73e0MouHUGTGzrp3uEkZD-fSrI2nVwI2dGQV1AzTcaD7GxyiZmowyQWnDF7byDuWrDYQpfk9WS2UYdeXFQ3D8nOYVOCa8xnJuj_xrc0YAYUmZXQI43/w400-h217/nv3.png" width="400" /></a></div><br /></div></div></div></div><div style="text-align: left;"><div style="text-align: left;"><div style="clear: both; text-align: justify;"><span style="text-align: left;">The whole configuration can be set up manually through the Process Scheduler components. However, I have also written a couple of scripts to manage the configuration that should ensure consistency.</span></div></div></div><div style="text-align: left;"><div style="text-align: left;">
<div><ul style="text-align: left;"><li><a href="https://github.com/davidkurtz/nVision/blob/master/nvisionprcsdefnclone.sql" target="_blank"><i>nvisionprcsdefnclone.sql</i></a> updates process definitions and creates the cloned server definitions. It can also be configured to create multiple pairs of server definitions etc.</li>
<li><a href="https://github.com/davidkurtz/nVision/blob/master/nvision_prcscategory.sql" target="_blank"><i>nvision_prcscategory.sql</i></a> updates the concurrency on existing process definitions.</li></ul>
<h3 style="text-align: left;">Scripts</h3><div>When a process request is submitted either in the PeopleSoft application or an enterprise scheduling tool (such as AutoSYS or Control-M), request records are inserted into:</div>
<div><ul style="text-align: left;"><li><i><a href="http://www2.go-faster.co.uk/peopletools/psprcsque.htm" target="_blank">PSPRCSQUE</a> </i>is polled by the process schedulers when looking for work.</li>
<li><a href="http://www2.go-faster.co.uk/peopletools/psprcsrqst.htm" target="_blank"><i>PSPRCSRQST</i></a> reports the status of processes and is exposed in the Process Monitor component in PeopleSoft.</li></ul></div>
<div>These two tables are siblings, both keyed on <i>PRCSINSTANCE</i>. Both have the following columns:</div>
<div><ul style="text-align: left;"><li><i>PRCSNAME</i> determines the name of the process to be run.</li>
<li><i>PRCSCATEGORY</i> specifies the category of the process in the request.</li></ul></div>
<h4>Triggers</h4><div>I have created two pairs of almost identical database triggers that fire on insert into each of these tables that will make certain changes to request for nVision layouts that should be run on Excel:</div>
<div><ul style="text-align: left;"><li>One pair of triggers updates requests for nVision-Report and nVisionReportBook process types. The other pair update the PSJob request record where nVision reports are run in a Jobset.</li><li>The triggers change the process category from <i>nVisionOpenXML</i> to <i>nVisionExcel </i>where necessary. It is changing the process category rather than the name of the process that makes the difference to which process scheduler picks up the request.</li>
<li>If the process request specifies a particular process scheduler (specified in <i>SERVERNAMERQST</i>) that cannot run the updated process category, then the requested server name is blanked out so that the master process scheduler reassigns the request.</li></ul></div>
<div>If any one layout in a ReportBook is listed as requiring to be run on Excel, then the whole ReportBook is moved to Excel because the report book runs as a single process. This may become a reason to split ReportBooks where only some layouts require to run on Excel.</div>
<div>Script <a href="https://github.com/davidkurtz/nVision/blob/master/gfc_nvsion_excel_redirect_triggers.sql" target="_blank"><i>gfc_nvsion_excel_redirect_triggers.sql</i></a> creates the meta-data table <i>PS_NVS_REDIR_EXCEL</i>, and the triggers on <i>PSPRCSRQST</i> and <i>PSPRCSQUE</i>.</div>
<div><ul style="text-align: left;"><li>The corresponding triggers on <i>PSPRCSRQST </i>and <i>PSPRCSQUE </i>are almost identical except for the name of the table and one additional column that is updated on <i>PSPRCSQUE </i>(apart from the conditionally compiled debug code). I will only exhibit the trigger on <i>PSPRCSRQST </i>in this post.</li>
<li>Normally, one would not code WHEN OTHERS THEN NULL exceptions handlers in PL/SQL. However, I do this on triggers on process scheduler tables to prevent any error being raised causing the scheduler to crash.</li>
<li>Conditionally compiled debug code in the trigger is not compiled if the <i>mydebug</i> flag is set to FALSE.</li></ul></div></div></div><p></p></div>
<div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: xx-small;"><code>ALTER SESSION SET PLSQL_CCFLAGS = 'mydebug:FALSE';
CREATE OR REPLACE TRIGGER sysadm.gfc_nvision_excel_redirect_rqst
BEFORE INSERT ON s.psprcsrqst
FOR EACH ROW
<b>WHEN (new.prcstype IN('nVision-Report','nVision-ReportBook')</b>)
DECLARE
l_excel INTEGER := 0;
l_maxconcurrent INTEGER := 0;
k_prcscategory CONSTANT VARCHAR2(15) := 'nVisionExcel';
BEGIN
$IF $$mydebug $THEN dbms_output.put_line('Entering Trigger psoft.gfc_nvision_excel_redirect_rqst'); $END
IF :new.prcstype = 'nVision-ReportBook' THEN
<i> --check for reportbook running report that uses layout on Excel list</i>
SELECT 1
INTO l_excel
FROM psnvsbookrequst b
, ps_nvs_report n
, ps_nvs_redir_excel e
<b> WHERE b.oprid = :new.oprid
AND b.run_cntl_id = :new.runcntlid
</b> AND b.eff_status = 'A'
AND n.business_unit = b.business_unit
AND n.report_id = b.report_id
AND n.layout_id = e.layout_id
AND e.eff_status = 'A'
AND rownum=1;
ELSE
<i> --look in command line for report running layout on Excel list</i>
SELECT 1
INTO l_excel
FROM psprcsparms p
, ps_nvs_report n
, ps_nvs_redir_excel e
WHERE p.prcsinstance = :new.prcsinstance
AND n.report_id = <b>substr(regexp_substr(p.parmlist,'-NRN[^ ]+'),5)</b>
AND n.layout_id = e.layout_id
AND e.eff_status = 'A'
AND rownum=1;
END IF;
<i>--set category of request</i>
:new.prcscategory := k_prcscategory;
<i> --get max concurrency of new category on new server</i>
SELECT maxconcurrent
INTO l_maxconcurrent
FROM ps_servercategory
WHERE prcscategory = :new.prcscategory
AND servername = :new.servernamerqst;
<i> --if request assigned to server where it cannot run blank out server assignment and allow load balancing to determine it</i>
IF l_maxconcurrent = 0 THEN
:new.servernamerqst := ' ';
END IF;
$IF $$mydebug $THEN dbms_output.put_line('set process name to '||:new.prcsname); $END
EXCEPTION
WHEN no_data_found THEN
$IF $$mydebug $THEN dbms_output.put_line('No excel redirect found'); $ELSE NULL; $END
WHEN others THEN
$IF $$mydebug $THEN dbms_output.put_line('Other Error'); $ELSE NULL; $END
END gfc_nvision_excel_redirect_rqst;
/
show errors</code></span></pre></div>
<h4 style="text-align: left;">MetaData</h4><div>The script <i><a href="https://github.com/davidkurtz/nVision/blob/master/gfc_nvsion_excel_redirect_metadata.sql">gfc_nvsion_excel_redirect_metadata.sql</a></i> populates a list of nVision layouts that must run on Excel into the metadata table <i>PS_NVS_REDIR_EXCEL</i>. You have to enter your own list of nVision layouts here.</div><div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: xx-small;"><code>REM gfc_nvsion_excel_redirect_metadata.sql
REM (c)Go-Faster Consultancy 2021
REM load metadata of layouts that have to run on Excel rather than OpenXML
spool gfc_nvsion_excel_redirect_metadata
<b>INSERT INTO ps_nvs_redir_excel VALUES ('EXCELNVS','A');</b>
commit;
spool off</code></span></pre></div>
<h4 style="text-align: left;">Other Scripts</h4><div><ul style="text-align: left;"><li><a href="https://github.com/davidkurtz/nVision/blob/master/excel_only_reportbooks.sql"><i>excel_only_reportbooks.sql</i></a> determines which nVision ReportBooks contain only some layouts that require to be run on nVision. These are candidates to be split up.</li></ul></div>
<div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: 66%;"><code> ReportBooks with both Excel and OpenXML nVision layouts
Number of
All Excel
OPRID RUN_CNTL_ID Layouts Layouts Excel Layouts OpenXML Layouts
---------- ------------------------------ ------- ---------- ------------------------------ ----------------------------------------------------------------------
BATCH ADHOC_NVISION 8 1 GLXXXO21 GLXYZD03, GLXXXO03, GLXXXO05, GLXXXO22, GLXXXO23, GLXXXO31, GLXXXO32
BATCH ADHOC_09062016 3 1 ZYXVIS14 ZYXVBS14, ZYXVIS12
BATCH GLXXX_GLXXXO02_ABDC_YYY 2 1 GLXXXO02 GLXXXO28
BATCH GLXXX_GLXXXO21_ABDC_YYY 3 2 GLXXXO21, GLXXXO98 GLXXXO71
BATCH GLXXX_ZYXB4080_M000_ZZZ 10 2 ZYXVBS64, ZYXVIS14 ZYXVBS04, ZYXVBS14, ZYXVBS14_LED, ZYXVBS16, ZYXVBS16_LED, ZYXVBS54, ZB
UVIS04, ZYXVIS16</code></span></pre></div>
<div><li><a href="https://github.com/davidkurtz/nVision/blob/master/nvision_processsmonitor.sql"><i>nvision_processsmonitor.sql</i></a> reports on nVision ReportBooks scheduled in the last 24 hours, their process category and the excel layouts within them, so that you can verify that the configuration is working.</li></div>
<div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: 66%;"><code> Process Server Server Server
PRCSINSTANCE RUNDTTM PRCSNAME Category Request Run Assign RUNCNTLID STA EXCEL_LAYOUT_IDS
------------ ---------------------------- ------------ --------------- -------- -------- -------- ------------------------------ --- ------------------------------
12345680 31-MAR-21 07.42.51.000000 AM RPTBOOKE nVisionExcel PSNT_E1 PSNT_E1 GLXXX_GLXXXO10_ABDC_YYY OK GLXXXO10
12345681 31-MAR-21 07.43.25.000000 AM RPTBOOKE nVisionExcel PSNT_E1 PSNT_E1 GLXXX_GLXXXO21_ABDC_YYY OK GLXXXO21, GLXXXO98
12345683 31-MAR-21 08.06.42.000000 AM RPTBOOKE nVisionExcel PSNT_E1 PSNT_E1 GLXXX_GLXXXO98_ADHOC OK GLXXXO98
12345684 31-MAR-21 08.32.12.000000 AM RPTBOOKE nVisionExcel PSNT_E1 PSNT_E1 GLXXX_GLXXXO21_ABDC_YYY OK GLXXXO21, GLXXXO98
12345685 31-MAR-21 09.18.23.000000 AM FBRPTBK nVisionOpenXML PSNT_X1 PSNT_X1 GLXXX_GLXYAD06_ABDC_YYY OK
12345686 31-MAR-21 09.20.01.000000 AM RPTBOOK nVisionOpenXML PSNT_X1 PSNT_X1 GLXXX_GLXYBP00_ABDC_YYY OK
12345687 31-MAR-21 09.22.21.000000 AM RPTBOOK nVisionOpenXML PSNT_X1 PSNT_X1 GLXXX_GLXYAD03_ABDC_YYY OK
12345688 31-MAR-21 09.23.11.000000 AM RPTBOOK nVisionOpenXML PSNT_X1 PSNT_X1 GLXXX_GLVLAD03_ABDC_XXX OK
12345689 31-MAR-21 09.24.18.000000 AM RPTBOOK nVisionOpenXML PSNT_X1 PSNT_X1 GLXXX_ZYXAB001_M000_ZZZ OK</code></span></pre></div>
<div>All the scripts mentioned in this blog have been included in my <a href="https://github.com/davidkurtz/nVision" target="_blank">nVision Github repository</a>.</div><div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com2tag:blogger.com,1999:blog-25740336.post-66142174492771941722020-12-01T10:58:00.000+00:002020-12-01T10:58:14.351+00:00Tuning Dynamically Generated SQL from PeopleSoft COBOL and Application Engine<p>When working on a performance issue, you may reach the point where you want to introduce one or more hints into a SQL statement. It is often tempting to simply add the hints to the source code. Doing so may appear to be simpler. That is often the case with Application Engine programs, however, it is much more difficult in PeopleSoft COBOL programs. </p><p>
A strong argument against such code change is that having made it, you have also to functionally test the change and push it through the entire release management process to get it into production. Then, should you ever want to change or remove the hints, you have to go through the entire process again.</p><p>
Oracle provides several mechanisms to define a database object containing a hint or set of hints and to apply them to matching SQL statements. These mechanisms work best with SQL that uses bind variables rather than literals. If an otherwise identical SQL statement has different literal values then it has a different SQL_ID and is treated by the database as a new statement. SQL Plan Baselines, SQL Patches and SQL Profiles match to the specific text of a SQL statement. Different literal values will prevent matching and defeat these mechanisms. These techniques must still be tested and migrated in a controlled manner, but they have no functional impact and so only testing of performance is needed.</p><p>
SQL Profiles can also perform forced matching, where statements that are similar except for literal values are matched. However, note that they also require licencing of Tuning Pack.</p><p>Some parts of PeopleTools and the PeopleSoft applications are better than others at producing sharable SQL with bind variables. Most of the SQL generated by the component processor uses bind variables. In Application Engine, if the <a href="https://docs.oracle.com/cd/F30998_01/pt858pbr2/eng/pt/tape/task_ReusingStatements-07720c.html?pli=ul_d26e60_tape" target="_blank">ReUse Statement</a> property is not set, which it is not by default, the %BIND fields are substituted with their literal values in the SQL statement. However, if the property is set then %BIND fields become bind variables. Over the years much more PeopleSoft code has been delivered with this attribute enabled. Doing so has significant performance benefits (see <a href="https://blog.psftdba.com/2009/02/performance-benefits-of-reuse-statement_27.html">Performance Benefits of ReUse Statement Flag in Application Engine</a>). </p><p>
Where, under normal circumstances, I might use a baseline or patch to inject a hint or profile of hints into a particular SQL statement (i.e. where I am dealing with a single SQL_ID), if the statement has literal values that change, then each statement has a different SQL_ID. I have experimented with setting <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/CURSOR_SHARING.html#GUID-455358F8-D657-49A2-B32B-13A1DC53E7D2" target="_blank">CURSOR_SHARING</a> to FORCE at session-level for a specific scheduled process, but I have always had very poor experiences with that approach. It invariably causes more problems than it solves. Instead, I use force matched SQL Profiles.</p><p>
The PeopleTools documentation sets out situations where ReUse Statement cannot be set. This includes dynamically generated code where <i>%BIND(…,NOQUOUTES)</i> is used to embed a piece of SQL held in a variable. This is a very common technique in PeopleSoft; often dynamically generated code is driven by the application configuration. </p><p>
We also see a similar design in PeopleSoft's COBOL programs. Static statements are loaded from the stored SQL statements table (<a href="http://www2.go-faster.co.uk/peopletools/sqlstmt_tbl.htm" target="_blank">PS_SQLSTMT_TBL</a>) and do use bind variables, but dynamic statements are assembled at runtime from static fragments in the COBOL code and any variable values are embedded as literals rather than using bind variables.</p><p>
Forced matching will allow a SQL profile to match a statement that is the same except for different literal values. However, dynamic SQL statements can change in ways that are beyond that, including:</p><p></p>
<ul style="text-align: left;"><li>Different instances of working storage tables can be used by different instances of the same process.</li><li>Different columns can be used in select and group by clauses.</li>
<li>Different criteria can be introduced. </li><li>A different number of terms in an IN() condition.</li></ul><p></p><p>
Occasionally, and I really mean very occasionally when I have exhausted other alternatives, I have dynamically created groups of SQL Profiles (still with forced matching) to cover every permutation of the variations of the dynamic SQL statement.</p>
<h3 style="text-align: left;">Example</h3><div><div>
Here is a dynamic statement from such a COBOL process, FSPCCURR. This <i>General Ledger Revaluation process adjusts the base currency value of the account balances by creating adjusting entries for the accounts being revalued. It creates corresponding entries for any gain or loss that results from the revaluation.</i> It was captured by AWR, and I have extracted it with <a href="http://DBMS_XPLAN.DISPLAY_AWR" target="_blank">DBMS_XPLAN.DISPLAY_AWR</a>.</div><div>
The ledger table (PS_LEDGER) is joined to a working storage table of tree selectors (PS_TREE_SEL10_R001) and working storage table (PS_CURR_WRK_RT001) and the result is put into another working storage table (PS_CURR_WRK_TBL001).</div></div>
<div><div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: x-small;"><code>INSERT INTO <b>PS_CURR_WRK_TBL001</b> (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFI
LIATE_INTRA1,AFFILIATE_INTRA2,ALTACCT,BOOK_CODE,BUDGET_REF,CHARTFIELD1,C
HARTFIELD2,CHARTFIELD3,CLASS_FLD,CURRENCY_CD,DATE_CODE,DEPTID,FISCAL_YEA
R,FUND_CODE,GL_ADJUST_TYPE,LEDGER,OPERATING_UNIT,POSTED_BASE_AMT,POSTED_
TOTAL_AMT,POSTED_TOTAL_CR,POSTED_TOTAL_DR,POSTED_TRAN_AMT,POSTED_TRAN_CR
,POSTED_TRAN_DR,PROCESS_INSTANCE,PRODUCT,PROGRAM_CODE,PROJECT_ID,STATIST
ICS_CODE,RATE_DIV,RATE_MULT,GAINLOSS_ACCT,RESTATED_AMT,REVAL_ADJUSTMENT,
TARGET_CURRENCY) SELECT A.ACCOUNT,003,AFFILIATE,' ',' ','
',A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,'
',A.CURRENCY_CD,DATE_CODE,DEPTID,2020,FUND_CODE,GL_ADJUST_TYPE,'X_UKCORE
',OPERATING_UNIT,SUM(A.POSTED_BASE_AMT),SUM(POSTED_TOTAL_AMT),0,0,0,0,0,
0002858795,PRODUCT,PROGRAM_CODE,PROJECT_ID,' ',R.RATE_DIV,R.RATE_MULT,'
',ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02
),ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02 )
- SUM(A.POSTED_BASE_AMT),'USD' FROM PS_LEDGER A , <b>PS_TREE_SEL10_R001</b> B
, <b>PS_CURR_WRK_RT001</b> R WHERE A.LEDGER='X_UKCORE' AND A.FISCAL_YEAR =
2020 AND A.ACCOUNTING_PERIOD IN ( 0, <b>1, 2, 3</b>) AND B
.PROCESS_INSTANCE=0002858795 AND B .CHARTFIELD='ACCOUNT' AND
A.ACCOUNT>=B .RANGE_FROM_10 AND A.ACCOUNT<=B .RANGE_TO_10 AND
A.BUSINESS_UNIT='96013' AND A.CURRENCY_CD <> 'GBP' AND FROM_CUR =
A.CURRENCY_CD AND TO_CUR = 'GBP' AND R.PROCESS_INSTANCE = 0002858795
GROUP BY A.ACCOUNT,AFFILIATE,A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIE
LD2,CHARTFIELD3,A.CURRENCY_CD,DATE_CODE,DEPTID,FUND_CODE,GL_ADJUST_TYPE,
OPERATING_UNIT,PRODUCT,PROGRAM_CODE,PROJECT_ID,RATE_DIV,RATE_MULT</code></span></pre></div></div>
However there are several copies of each of these working storage tables, and different concurrent instances of this process may be allocated different copies. <div>There is also an in clause that lists the accounting periods to be processed. </div><div>So the statement can vary. Here is another version of what is essentially the same statement with different literal values and different tables and for a different accounting period (this time period 5). The parts in bold a the ones that vary from statement to statement that are not literal values
<div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span><code><span style="font-size: x-small;">INSERT INTO <b>PS_CURR_WRK_TBL001</b> (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFI
LIATE_INTRA1,AFFILIATE_INTRA2,ALTACCT,BOOK_CODE,BUDGET_REF,CHARTFIELD1,C
HARTFIELD2,CHARTFIELD3,CLASS_FLD,CURRENCY_CD,DATE_CODE,DEPTID,FISCAL_YEA
R,FUND_CODE,GL_ADJUST_TYPE,LEDGER,OPERATING_UNIT,POSTED_BASE_AMT,POSTED_
TOTAL_AMT,POSTED_TOTAL_CR,POSTED_TOTAL_DR,POSTED_TRAN_AMT,POSTED_TRAN_CR
,POSTED_TRAN_DR,PROCESS_INSTANCE,PRODUCT,PROGRAM_CODE,PROJECT_ID,STATIST
ICS_CODE,RATE_DIV,RATE_MULT,GAINLOSS_ACCT,RESTATED_AMT,REVAL_ADJUSTMENT,
TARGET_CURRENCY) SELECT A.ACCOUNT,005,AFFILIATE,' ',' ','
',A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,'
',A.CURRENCY_CD,DATE_CODE,DEPTID,2020,FUND_CODE,GL_ADJUST_TYPE,'XXXX',OP
ERATING_UNIT,SUM(A.POSTED_BASE_AMT),SUM(POSTED_TOTAL_AMT),0,0,0,0,0,0002
991789,PRODUCT,PROGRAM_CODE,PROJECT_ID,' ',R.RATE_DIV,R.RATE_MULT,'
',ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02
),ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02 )
- SUM(A.POSTED_BASE_AMT),'AUD' FROM PS_LEDGER A , <b>PS_TREE_SEL10_R</b> B ,
<b>PS_CURR_WRK_RT002</b> R WHERE A.LEDGER='XXXX' AND A.FISCAL_YEAR = 2020 AND
A.ACCOUNTING_PERIOD IN ( 0, <b>1, 2, 3, 4, 5</b>) AND B
.PROCESS_INSTANCE=0002991789 AND B .CHARTFIELD='ACCOUNT' AND
A.ACCOUNT>=B .RANGE_FROM_10 AND A.ACCOUNT<=B .RANGE_TO_10 AND
A.BUSINESS_UNIT='13051' AND A.CURRENCY_CD <> 'AUD' AND FROM_CUR =
A.CURRENCY_CD AND TO_CUR = 'AUD' AND R.PROCESS_INSTANCE = 0002991789
GROUP BY A.ACCOUNT,AFFILIATE,A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIE
LD2,CHARTFIELD3,A.CURRENCY_CD,DATE_CODE,DEPTID,FUND_CODE,GL_ADJUST_TYPE,
OPERATING_UNIT,PRODUCT,PROGRAM_CODE,PROJECT_ID,RATE_DIV,RATE_MULT</span></code></span></pre></div>
If I want to use SQL Profiles to introduce hints to control the execution plan, then I will need a different profile for every possible permutation.</div><div><div>
I start by using Carlos Sierra's <a href="http://coe_xfr_sql_profile.sql" target="_blank">coe_xfr_sql_profile.sql</a> script. This is a part of Oracle's <a href="https://support.oracle.com/epmos/faces/DocContentDisplay?id=215187.1" target="_blank">SQLTEXPLAIN (SQLT)</a> tool. It generates a SQL script that generates a SQL profile to reproduce a given execution plan for a given SQL statement that was captured by AWR.</div><div>
From there is not a big jump to add a SQL statement to generate all the permutations of the variations in the SQL (other than for bind variables) and create a profile inside a loop. The exact details will vary depending on the behaviour of the program. However, in this particular example I need:</div></div><div><ul style="text-align: left;"><li>Different SQL profiles will be needed for each accounting period because there will be a different list of accounting periods in the <i>IN()</i> condition. Subquery factors <i>n</i> and <i>n1</i> produce a list of accounting periods.</li></ul><div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: x-small;">WITH n AS (SELECT rownum n FROM dual CONNECT BY level<=12
), n1 AS (
SELECT n1.n period, LISTAGG(n2.n,', ') WITHIN GROUP (ORDER BY n2.n) periods
FROM n n1, n n2
WHERE n2.n <= n1.n
AND n1.n <= 12
GROUP BY n1.n
)
SELECT * FROM n1
/
PERIOD PERIODS
---------- ----------------------------------------
1 1
2 1, 2
3 1, 2, 3
4 1, 2, 3, 4
5 1, 2, 3, 4, 5
6 1, 2, 3, 4, 5, 6
7 1, 2, 3, 4, 5, 6, 7
8 1, 2, 3, 4, 5, 6, 7, 8
9 1, 2, 3, 4, 5, 6, 7, 8, 9
10 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
11 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11
12 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12</span></pre></div></div><ul style="text-align: left;"><li>Lists of the various tables used for working storage can be queried from the PeopleSoft data dictionary, PSRECDEFN. I can see that there are 5 versions of the current work table that the process can choose from. Note that these are ordinary tables, so there are 5 different records in PSRECDEFN. </li></ul><div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: x-small;">SELECT TO_NUMBER(substr(recname,13)) seq
, decode(sqltablename,' ','PS_'||recname,sqltablename) table_name
FROM psrecdefn
WHERE recname like 'CURR_WRK_TBL%'
/
SEQ TABLE_NAME
---------- ------------------
PS_CURR_WKK_TBL
1 PS_CURR_WKK_TBL001
2 PS_CURR_WKK_TBL002
3 PS_CURR_WKK_TBL003
4 PS_CURR_WKK_TBL004</span>
</pre></div><div><ul style="text-align: left;"><li>However, if I was working on a temporary record used in an Application Engine program, I would need to look up the number of instances of that record. </li><ul><li>The number of non-shared temporary record tables is the sum of all the instances defined on each application engine program to which the record is allocated, plus the number of global instances, up to a maximum of 99. Instance 0 is the shared instance. The number can be altered in development and the additional tables built by Application Designer. This can require additional SQL Profiles be built. </li></ul></ul></div>
<div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: x-small;"><span>WITH n AS (
S</span><span>ELECT rownum-1 n FROM dual CONNECT BY LEVEL<=100
), c AS (
SELECT c.recname
, n.n instance
, DECODE(r.sqltablename, ' ', 'PS_'||r.recname,r.sqltablename)||DECODE(n.n,0,'',n.n) table_name
FROM n
, pstemptblcntvw c
, psrecdefn r
, psoptions o
WHERE r.recname = c.recname
AND n.n <= c.temptblinstances+o.temptblinstances
)
SELECT instance, table_name
FROM c
WHERE recname = 'WRK_XREF_CAL'
/
INSTANCE TABLE_NAME
---------- ----------------------------------------------------------
0 PS_WRK_XREF_CAL
1 PS_WRK_XREF_CAL1
2 PS_WRK_XREF_CAL2
3 PS_WRK_XREF_CAL3
4 PS_WRK_XREF_CAL4
5 PS_WRK_XREF_CAL5
6 PS_WRK_XREF_CAL6</span></span></pre></div><div><ul style="text-align: left;"><li>In this particular example, I know that every permutation of all three tables could occur in all accounting period, so I simply Cartesian join all the subquery factors. </li><ul><li>In other cases, only some permutations may occur. This must be handled in the code that is written. Literal values do not need to be considered because the profile will be created with force matching.</li><li>In Application Engine, although you often see the same instance of different temporary records used in the same process, there is nothing to prevent different instances of different records being used, and so all permutations must be considered.</li></ul><li>I will also concatenate the ID for each table, and also the accounting period to produce an ID string that I can use in the name of the SQL profile.</li></ul></div>
<div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: x-small;"><code>'TS'||ts.seq||'_RT'||rt.seq||'_TBL'||wk.seq||'_'||n1.period</code></span></pre></div><div>The profile generated by <i>coe_xfr_sql_profile.sql</i> will contain the complete profile of hints for the SQL statement captured by AWR. That is officially the only way to guarantee a particular execution plan. However, as in this example, I could specify the just that I want to introduce in the statement, effectively treating a SQL Profile as if it was a SQL Patch. See also:</div>
<div><ul style="text-align: left;"><li>Nigel Bayliss: <a href="https://blogs.oracle.com/optimizer/adding-and-disabling-hints-using-sql-patch" target="_blank">Adding and Disabling Hints Using SQL Patch</a>.</li><li>Kerry Osborne: <a href="http://kerryosborne.oracle-guy.com/2010/07/24/sqlt-coe_xfr_sql_profilesql/" target="_blank">Oracle Support Sanctions Manually Created SQL Profiles!</a></li></ul></div><div>Here is the modified script. Note the sections in bold.</div>
<div><ul style="text-align: left;"><li>The SQL statement in the FOR clause returns all the permutations of the variations in the SQL statement in an implicit cursor. </li>
<li>Table names are concatenated into the SQL text from the columns in the implicit cursor.</li><li>Single quotation marks are doubled so that the string contains the single quotation mark.</li><li>It is important not to add or remove any spaces when introducing these changes.</li><li>Profiles are dropped and created inside the loop. Force Matching is enabled.</li></ul></div><div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: x-small;">REM coe_xfr_sql_profile_FSPCCURR_tree_rate_curr_wrk.sql
SPO coe_xfr_sql_profile_FSPCCURR_tree_rate_curr_wrk.log;
WHENEVER SQLERROR CONTINUE
REM WHENEVER SQLERROR EXIT SQL.SQLCODE;
VAR signature NUMBER;
DECLARE
sql_txt CLOB;
h SYS.SQLPROF_ATTR;
e_no_sql_profile EXCEPTION;
PRAGMA EXCEPTION_INIT(e_no_sql_profile, -13833);
BEGIN
FOR i IN(
<b> WITH n AS (SELECT rownum n FROM dual CONNECT BY level<=12
), n1 AS (
SELECT n1.n period, LISTAGG(n2.n,', ') WITHIN GROUP (ORDER BY n2.n) periods
FROM n n1, n n2
WHERE n2.n <= n1.n
GROUP BY n1.n
), ts AS (
SELECT TO_NUMBER(substr(recname,13)) seq
, decode(sqltablename,' ','PS_'||recname,sqltablename) table_name
FROM psrecdefn
WHERE recname like 'TREE_SEL10_R%'
), rt AS (
SELECT TO_NUMBER(substr(recname,12)) seq
, decode(sqltablename,' ','PS_'||recname,sqltablename) table_name
FROM psrecdefn
WHERE recname like 'CURR_WRK_RT%'
), wk AS (
SELECT TO_NUMBER(substr(recname,13)) seq
, decode(sqltablename,' ','PS_'||recname,sqltablename) table_name
FROM psrecdefn
WHERE recname like 'CURR_WRK_TBL%'
)
SELECT 'TS'||ts.seq||'_RT'||rt.seq||'_TBL'||wk.seq||'_'||n1.period id
, ts.table_name ts_table_name
, rt.table_name rt_table_name
, wk.table_name wk_table_name
, n1.period, n1.periods
FROM n1, ts, rt, wk
</b> ) LOOP
sql_txt := 'INSERT INTO '<b>||i.wk_table_name||</b>' (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFILIATE_INTRA1,AFFILIATE_INTRA2,ALTACCT,
BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,CLASS_FLD,CURRENCY_CD,DATE_CODE,DEPTID,FISCAL_YEAR,
FUND_CODE,GL_ADJUST_TYPE,LEDGER,OPERATING_UNIT,POSTED_BASE_AMT,POSTED_TOTAL_AMT,POSTED_TOTAL_CR,POSTED_TOTAL_DR,
POSTED_TRAN_AMT,POSTED_TRAN_CR,POSTED_TRAN_DR,PROCESS_INSTANCE,PRODUCT,PROGRAM_CODE,PROJECT_ID,STATISTICS_CODE,
RATE_DIV,RATE_MULT,GAINLOSS_ACCT,RESTATED_AMT,REVAL_ADJUSTMENT,TARGET_CURRENCY)
SELECT A.ACCOUNT,011,AFFILIATE,
'' '','' '','' '',A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,'' '',A.CURRENCY_CD,DATE_CODE,DEPTID,2016,
FUND_CODE,GL_ADJUST_TYPE,''X_UKCORE'',OPERATING_UNIT,SUM(A.POSTED_BASE_AMT),SUM(POSTED_TOTAL_AMT),0,0,0,0,0,
0001234567,PRODUCT,PROGRAM_CODE,PROJECT_ID,'' '',R.RATE_DIV,R.RATE_MULT,'' '',
ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02
),ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02 )
- SUM(A.POSTED_BASE_AMT),<b>''GBP''</b> FROM PS_LEDGER A , '<b>||i.ts_table_name||</b>' B
, '<b>||i.rt_table_name||</b>' R WHERE A.LEDGER=''X_UKCORE'' AND A.FISCAL_YEAR =
2016 AND A.ACCOUNTING_PERIOD IN ( 0, '<b>||i.periods||</b>')
AND B .PROCESS_INSTANCE=0001234567 AND B .CHARTFIELD=''ACCOUNT'' AND
A.ACCOUNT>=B .RANGE_FROM_10 AND A.ACCOUNT<=B .RANGE_TO_10 AND
A.BUSINESS_UNIT=''12345'' AND A.CURRENCY_CD <> ''GBP''
AND FROM_CUR = A.CURRENCY_CD
AND TO_CUR = ''GBP''
AND R.PROCESS_INSTANCE = 0001234567
GROUP BY A.ACCOUNT,AFFILIATE,A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,A.CURRENCY_CD,DATE_CODE,
DEPTID,FUND_CODE,GL_ADJUST_TYPE,OPERATING_UNIT,PRODUCT,PROGRAM_CODE,PROJECT_ID,RATE_DIV,RATE_MULT';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[ALL_ROWS]',
<b>q'[LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1" "R"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "A"@"SEL$1")]',
q'[SWAP_JOIN_INPUTS(@"SEL$1" "R"@"SEL$1")]',
</b>q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
dbms_output.put_line(i.id||':'||:signature);
BEGIN
DBMS_SQLTUNE.drop_SQL_PROFILE (name => 'FSPCCURR_'||i.id);
EXCEPTION
WHEN e_no_sql_profile THEN NULL;
END;
IF 1=1 THEN
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text => sql_txt,
profile => h,
name => 'FSPCCURR_'||i.id,
description => 'coe FSPCCURR '||i.id||' @ '||:signature||'',
category => 'DEFAULT',
validate => TRUE,
replace => TRUE,
<b>force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL).
FALSE:EXACT (similar to CURSOR_SHARING) */ );</b>
END IF;
END LOOP;
END;
/
column name format a30
select name, status, sql_text
from dba_sql_profiles
where name like '%FSPCCURR%'
order by 1
/
SPO OFF;</span>
</pre></div><div>When I implemented this particular example, it created 900 SQL profiles. Each one has a different force matching signature. Having this many SQL Profiles is not a problem for the database because they are looked up by the signature. </div><div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: x-small;">…
TS1_RT4_TBL4_12:1278395827722096644
TS2_RT4_TBL4_12:4126874017730826984
PL/SQL procedure successfully completed.</span>
</pre></div><div>However, managing that many profiles could become a cause for concern by the DBA. Therefore, I think it is important that they have a sensible naming convention so that it is obvious to what they relate.</div><div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: xx-small;">NAME STATUS SQL_TEXT
------------------------------ -------- --------------------------------------------------------------------------------
…
FSPCCURR_TS1_RT4_TBL4_12 ENABLED INSERT INTO PS_CURR_WRK_TBL004 (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFILIATE_IN
…
FSPCCURR_TS2_RT4_TBL4_12 ENABLED INSERT INTO PS_CURR_WRK_TBL004 (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFILIATE_IN
…
900 rows selected.</span></pre></div><h4 style="text-align: left;">Further reading</h4><div><ul style="text-align: left;"><li>Go-Faster Oracle Blog: <a href="https://blog.go-faster.co.uk/2017/12/hints-patches-force-matching-and-sql.html" target="_blank">Hints, Patches, Force Matching and SQL Profiles</a>. This post also demonstrates dynamically generating SQL Profiles, on this occasion for nVision queries.</li><li>Jonathan Lewis: <a href="https://jonathanlewis.wordpress.com/2018/06/26/hacking-profiles/" target="_blank">Hacking Profiles</a></li></ul></div><div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com0tag:blogger.com,1999:blog-25740336.post-89558558372639416312020-11-24T13:54:00.000+00:002020-11-24T13:54:13.147+00:00PeopleSoft Financials Ledger Partitioning Recommendations<p>I have written previously about partitioning the PS_LEDGER table in Financials for the benefit of General Ledger (GL) reporting. I have always recommended top-level range partitioning on the combination of FISCAL_YEAR and ACCOUNTING_PERIOD with separate partitions for each accounting period. It also leaves sub-partitioning available to use another column, usually LEDGER.</p><p></p><ul style="text-align: left;"><li>PeopleSoft DBA blog: <a href="https://blog.psftdba.com/2017/10/nvision-performance-tuning-4.html">nVision Performance Tuning: 4. Partitioning of Ledger, Ledger Budget, and Summary Ledger Tables</a></li><li>Go-Faster blog: <a href="https://blog.go-faster.co.uk/2020/11/retrofitting-partitioning-41.html">Retrofitting Partitioning into Existing Applications: Example 1. General Ledger</a></li></ul><p></p><p>However, recent research into partition change tracking (PCT) and materialized views has made me question that advice. The decision is not as clear cut.</p><p></p><ul style="text-align: left;"><li>Go-Faster blog: <a href="https://blog.go-faster.co.uk/2020/11/partition-change-tracking-during.html">Partition Change Tracking During Materialized View Refresh and Query Rewrite</a></li></ul><p></p><h3 style="text-align: left;">Summary Ledgers or Materialized Views</h3><p>You can create summary ledger tables in PeopleSoft Financials that are incrementally maintained by batch processes, and then nVision reports can reference the summary ledgers instead. If the summary ledgers are not up to date, then the reports will produce out of date results.</p><p>Similarly, materialized views can be used in an Oracle database to create pre-generated reporting tables. An application can reference a materialized view directly, or the database can dynamically rewrite submitted SQL queries to use the materialized view if they are sufficiently similar to the query that created the materialized view and if the optimizer judges that it is cheaper to query the materialized view. By default, the database will check that the materialized view is up to date, that is no change has been made to the underlying tables since the last refresh commenced, before it can rewrite the SQL query. So the query will always return the same data, but if the materialized view is out of date you don't get the performance improvement.</p><p>You can optionally choose to configure the database to write SQL queries to use stale materialized views by setting <i>QUERY_REWRITE_INTEGRITY=stale_tolerated</i> at either database or session-level. </p><p>Materialized views can be created for the nVision queries that you wish to optimise, and no further code change is required because the database will rewrite the SQL. You can see a typical example of this in my blog about <a href="https://blog.go-faster.co.uk/2020/11/partition-change-tracking-during.html">PCT</a>.</p><p>Partition Change Tracking is a mechanism the Oracle database uses to 'track freshness to a finer grain than the entire materialized view'. It can identify which partitions and subpartitions are fresh and can be used for query rewrite, and to refresh just the partitions that are stale or that contain stale sub-partitions. </p><h3 style="text-align: left;">Alternatives for Partitioning PS_LEDGER</h3><p>If you wish to create materialized views on the main ledger table, and rely upon query rewrite, and keep the materialized views up to date with respect to the ledger table, and only use them when they are up to date, then you probably want PCT to help with both rewrite and refresh.</p><h4 style="text-align: left;">1. Multi-column composite partitioning </h4><p></p><p>I usually like to range partition PS_LEDGER on the combination of FISCAL_YEAR and ACCOUNTING_PERIOD to produce a separate range partition for each accounting period. Partition pruning works very effectively with the SQL generated by nVision. It also allows sub-partitioning on another column, usually LEDGER. This is desirable when a system has multiple actuals ledgers, and especially since 9.2 where the budget data is also stored in PS_LEDGER rather than PS_LEDGER_BUDG. </p><p>However, amongst the documented restrictions of PCT is that partitioned tables must use either range, or list partitioning, or composite partitioning with range or list as the top-level partitioning strategy. Also, the top-level partition key must consist of only a single column (see <a href="https://docs.oracle.com/database/121/DWHSG/advmv.htm#DWHSG00324" target="_blank">Database Data Warehousing Guide -> Advanced Materialized Views -> About Partition Change Tracking</a>)</p><p>If I want to use query rewrite to materialized views for queries on LEDGER table then I have a few choices.</p><p></p><ul style="text-align: left;"><li>If I stick with multi-column range partitioning, then I cannot use PCT. I must either keep the materialized views fresh, or the queries remain on the ledger table. Any update to any partition in the ledger table will render the entire materialized view stale and prevent query rewrite. Many customers run a suite of nVision reportbooks overnight. I could set <i>QUERY_REWRITE_INTEGRITY=stale_tolerated</i> at session-level for the report books processes using a trigger on the process scheduler request table (<a href="http://www2.go-faster.co.uk/peopletools/psprcsrqst.htm" target="_blank">PSPRCSRQST</a>) - see <a href="https://blog.psftdba.com/2018/03/setting-oracle-session-parameters-for.html">Setting Oracle Session Parameters for Specific Process Scheduler Processes</a>. In this case, I would have to take responsibility for refreshing the materialized views prior to running, say, a suite of report processes. This is effectively the same situation as using summary ledgers, but without code change to the reports.</li><ul><li>I have created materialized views on summary ledger tables in order to provide compressed copies of the summary ledger. Again, in this case, the materialized views had to be refreshed after the summary ledger maintenance process.</li></ul><li>Or, I have to produce a simpler partitioning strategy for the ledger table that is still compatible with PCT.</li></ul><p></p><h4 style="text-align: left;">2.<span style="white-space: pre;"> </span>Composite Range-Range Partitioning on FISCAL_YEAR and ACCOUNTING_PERIOD</h4><p>I could composite partition both the LEDGER table and the materialized views by FISCAL_YEAR and ACCOUNTING_PERIOD, but then I cannot further subpartition by other columns. This would degrade queries on smaller ledgers that could not be rewritten to dedicated materialized views.</p>
<div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: xx-small;"><code>CREATE TABLE ps_ledger
(business_unit VARCHAR2(5) NOT NULL
…
) PCTFREE 10 PCTUSED 80
<b>PARTITION BY RANGE (FISCAL_YEAR)
SUBPARTITION BY RANGE (ACCOUNTING_PERIOD)
</b>SUBPARTITION TEMPLATE
(SUBPARTITION ap_bf VALUES LESS THAN (1)
,SUBPARTITION ap_01 VALUES LESS THAN (2)
,SUBPARTITION ap_02 VALUES LESS THAN (3)
,SUBPARTITION ap_03 VALUES LESS THAN (4)
,SUBPARTITION ap_04 VALUES LESS THAN (5)
,SUBPARTITION ap_05 VALUES LESS THAN (6)
,SUBPARTITION ap_06 VALUES LESS THAN (7)
,SUBPARTITION ap_07 VALUES LESS THAN (8)
,SUBPARTITION ap_08 VALUES LESS THAN (9)
,SUBPARTITION ap_09 VALUES LESS THAN (10)
,SUBPARTITION ap_10 VALUES LESS THAN (11)
,SUBPARTITION ap_11 VALUES LESS THAN (12)
,SUBPARTITION ap_12 VALUES LESS THAN (13)
,SUBPARTITION ap_cf VALUES LESS THAN (MAXVALUE))
(PARTITION ledger_2018 VALUES LESS THAN (2019) PCTFREE 0 COMPRESS
,PARTITION ledger_2019 VALUES LESS THAN (2020) PCTFREE 0 COMPRESS
,PARTITION ledger_2020 VALUES LESS THAN (2021)
,PARTITION ledger_2021 VALUES LESS THAN (2022)
)
ENABLE ROW MOVEMENT NOLOGGING
/</code></span></pre></div>
The materialized view will be similarly composite partitioned. Note that I have created the materialized view for specific fiscal years and for a specific ledger. I would create materialized views for each combination of ledger and each distinct set of analysis columns that are regularly reported upon.
<div><pre style="background-color: #eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;"><span style="font-size: xx-small;"><code>CREATE MATERIALIZED VIEW mv_ledger_2020
<b>PARTITION BY RANGE (FISCAL_YEAR)
SUBPARTITION BY RANGE (ACCOUNTING_PERIOD)
</b>SUBPARTITION TEMPLATE
(SUBPARTITION ap_bf VALUES LESS THAN (1)
,SUBPARTITION ap_01 VALUES LESS THAN (2)
,SUBPARTITION ap_02 VALUES LESS THAN (3)
,SUBPARTITION ap_03 VALUES LESS THAN (4)
,SUBPARTITION ap_04 VALUES LESS THAN (5)
,SUBPARTITION ap_05 VALUES LESS THAN (6)
,SUBPARTITION ap_06 VALUES LESS THAN (7)
,SUBPARTITION ap_07 VALUES LESS THAN (8)
,SUBPARTITION ap_08 VALUES LESS THAN (9)
,SUBPARTITION ap_09 VALUES LESS THAN (10)
,SUBPARTITION ap_10 VALUES LESS THAN (11)
,SUBPARTITION ap_11 VALUES LESS THAN (12)
,SUBPARTITION ap_12 VALUES LESS THAN (13)
,SUBPARTITION ap_cf VALUES LESS THAN (MAXVALUE))
(PARTITION ledger_2019 VALUES LESS THAN (2020)
,PARTITION ledger_2020 VALUES LESS THAN (2021)
) PCTFREE 0 COMPRESS PARALLEL
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year >= 2019
AND ledger = 'ACTUALS'
AND currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/</code></span></pre></div>
<p>Note that I have equality criteria on LEDGER and CURRENCY_CD in the materialized view, but I have not included those columns in the select clause, and so they are not in the view. Oracle can still rewrite queries to use this materialized view and that specify the same criteria on PS_LEDGER</p><p>PCT will determine whether any unpruned partitions or subpartitions are stale and if so prevent query rewrite. It is documented behaviour that the materialized view refresh will truncate and rebuild the whole top-level partitions, in this case each fiscal year, where the partition or any constituent sub-partitions are stale. So even if just one subpartition, for one accounting period is stale, the whole fiscal year is refreshed.</p><h4 style="text-align: left;">3.<span style="white-space: pre;"> </span>Composite Range-Range Partitioning on ACCOUNTING_PERIOD and FISCAL_YEAR</h4><p>I investigated making ACCOUNTING_PERIOD the top-level partitioning key, and sub-partitioning on FISCAL_YEAR. </p><p></p><ul style="text-align: left;"><li>Partitioning pruning works correctly, so the query performance will be unaffected, </li><li>PCT refresh processed all years for the single accounting period, rather than all accounting periods for the single year. That is less work if you have fewer fiscal years than accounting periods. Generally, this is the case. I usually see systems that contain 3 to 6 fiscal years of data.</li></ul><p></p><p>However, it has a number of problems.</p><p></p><ul style="text-align: left;"><li>I can't specify storage options or compression attributes on sub-partitions in the create table DDL command, so I have to come along afterwards with PL/SQL to alter the sub-partitions.</li><li>It is not possible to interval range sub-partition an object, so I can't automatically add partitions for future fiscal years on demand. Instead, I am going to have to add new fiscal year subpartitions to each of the 14 range partitions.</li></ul><p></p><p>On balance, I don't think I would choose to implement this.</p><h3 style="text-align: left;">Conclusion</h3><p>There is no single clear recommendation for partitioning and PCT. It will depend on the circumstances.</p><p></p><ul style="text-align: left;"><li>If I don't need to introduce materialized views on PS_LEDGER then I would usually stick with the multi-column composite partitioning with the top-level range partition on FISCAL_YEAR and ACCOUNTING_PERIOD.</li><ul><li>PeopleSoft provides summary ledgers to improve the performance of the ledger queries and compressed materialized views can be built on these.</li></ul><li>If you only have a single actuals ledger then composite range-range partition on FISCAL_YEAR and ACCOUNTING_PERIOD is attractive. </li><ul><li>I do not recommend interval partitioning on FISCAL_YEAR because this affects the scope of the refresh process. It processes a second top-level range partition.</li></ul><li>If the budget data is stored in the PS_LEDGER table rather than LEDGER_BUDG then consider building separate materialized views for each value of LEDGER. </li><ul><li>If you can manage to build materialized views to support rewrite of most the queries on the smaller ledgers, then the lack of sub-partitioning by LEDGER is not going to be a problem unless the query doesn't get rewritten because the materialized views are stale. Keeping the materialized views up to date is going to be a bigger challenge.</li></ul></ul><p></p><div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com0tag:blogger.com,1999:blog-25740336.post-64875812715079375112019-12-02T22:41:00.003+00:002020-11-24T15:02:18.838+00:00Practical Application Performance Tuning: An nVision Case Study<div class="separator" style="clear: both; text-align: left;">
I gave this presentation at the <a href="https://ukoug.org/page/techfest19" target="_blank">UKOUG Techfest 19 conference</a>. It is closely based on a previous presentation about PeopleSoft <a href="http://www2.go-faster.co.uk/psftpres.htm#GL.nVision.Performance.Tuning.pptx" target="_blank">nVision performance tuning</a>, and uses the experience of a PeopleSoft project as a case study, so I am also posting both here on my PeopleSoft blog, and also on my <a href="https://blog.go-faster.co.uk/2019/12/" target="_blank">Oracle blog</a>.</div>
<div class="separator" style="clear: both; text-align: left;">
This video was produced as a part of the preparation for this session. The slide deck is also available on <a href="http://www2
.go-faster.co.uk/ukougpres.htm#PracticalApplicationPerformanceTuning.pub.pptx" target="_blank">my website</a>.</div>
<iframe allow="accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture" allowfullscreen="" frameborder="0" height="360" src="https://www.youtube.com/embed/5JDtWMTHLj8" width="640"></iframe><br />
Learning about and understanding the principles and mechanics of the Oracle database is fundamentally important for both DBAs and developers. It is one of the reasons we still physical conferences.
<br />
This presentation tells the story of a performance tuning project for the GL reporting on a Financials system on an engineered system. It required various techniques and features to be brought to bear. Having a theoretical understanding of how the database and various features work allowed us to make reasonable predictions about whether they would be effective in our environment. Some ideas were discounted, some were taken forward.
<br />
We will look at instrumentation, ASH, statistics collection, partitioning, hybrid columnar compression, Bloom filtering, SQL profiles. All of them played a part in the solution, some added further complications that had to be worked around, some had to be carefully integrated with the application, and some required some reconfiguration of the application into order to work properly.
<br />
Ultimately, performance improvement is an experimental science, and it requires a similar rigorous thought process.<div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com0tag:blogger.com,1999:blog-25740336.post-34220277811708136912019-11-12T19:12:00.000+00:002019-12-14T20:58:20.300+00:00nVision Bug in PeopleTools 8.55/8.56 Impacts PerformanceI have recently come across an interesting bug in nVision that has a significant performance impact on nVision reports in particular and can impact the database as a whole.<br />
<h4>
Problem nVision SQL</h4>
This is an example of the problematic SQL generated by nVision. The problem is that all of the SQL looks like this. There is never any <i>group by </i>clause, nor any grouping columns in the select clause in from of the <i>SUM()</i>.<br />
<pre style="background-color: #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%; line-height:1.0"><code>SELECT SUM(A.POSTED_BASE_AMT)
FROM PS_LEDGER A, PSTREESELECT10 L2, PSTREESELECT10 L1
WHERE A.LEDGER='ACTUAL' AND A.FISCAL_YEAR=2018 AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 8
AND L2.SELECTOR_NUM=159077 AND A.ACCOUNT=L2.RANGE_FROM_10
AND (A.BUSINESS_UNIT='10000')
AND L1.SELECTOR_NUM=159075 AND A.DEPTID=L1.RANGE_FROM_10
AND A.CURRENCY_CD='GBP' AND A.STATISTICS_CODE=' '</code></pre>
Each query only returns a single row, that only populates a single cell in the report, and therefore a different SQL statement is generated and executed for every cell in the report. Therefore, more statements are parsed and executed, and more scans of the ledger indexes and look-ups of the ledger table and performed. This consumes more CPU, more logical I/O.<br />
<h4>
Normal nVision SQL</h4>
This is how I would expect normal nVision SQL to look. This example, although obfuscated, came from a real customer system. Note how the query is grouped by TREE_NODE_NUM from two of the tree selector tables, so this one query now populates a block of cells.
<br />
<pre style="background-color: #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%; line-height:1.0"><code>SELECT <b>L2.TREE_NODE_NUM,L3.TREE_NODE_NUM</b>,SUM(A.POSTED_TOTAL_AMT)
FROM PS_LEDGER A, PSTREESELECT05 L2, PSTREESELECT10 L3
WHERE A.LEDGER='S_UKMGT'
AND A.FISCAL_YEAR=2018
AND A.ACCOUNTING_PERIOD BETWEEN 0 AND 12
AND (A.DEPTID BETWEEN 'A0000' AND 'A8999' OR A.DEPTID BETWEEN 'B0000' AND 'B9149'
OR A.DEPTID='B9156' OR A.DEPTID='B9158' OR A.DEPTID BETWEEN 'B9165' AND 'B9999'
OR A.DEPTID BETWEEN 'C0000' AND 'C9999' OR A.DEPTID BETWEEN 'D0000' AND 'D9999'
OR A.DEPTID BETWEEN 'G0000' AND 'G9999' OR A.DEPTID BETWEEN 'H0000' AND 'H9999'
OR A.DEPTID='B9150' OR A.DEPTID=' ')
AND L2.SELECTOR_NUM=10228
AND A.BUSINESS_UNIT=L2.RANGE_FROM_05
AND L3.SELECTOR_NUM=10231
AND A.ACCOUNT=L3.RANGE_FROM_10
AND A.CHARTFIELD1='0012345'
AND A.CURRENCY_CD='GBP'
GROUP BY <b>L2.TREE_NODE_NUM,L3.TREE_NODE_NUM</b></code></pre>
<h4>
The Bug</h4>
This Oracle note details an nVision bug:<br />
<ul>
<li><a href="https://support.oracle.com/epmos/faces/DocContentDisplay?id=2417622.1" target="_blank">E-NV: nVision Performance issues on both Client and WEB showing up starting 8.55.19 producing Extra SQL statements in trace (Doc ID 2417622.1)</a></li>
<ul>
<li>It is also referenced in <a href="https://support.oracle.com/epmos/faces/DocContentDisplay?id=2481835.1" target="_blank">E-NV: nVision Performance which performs better Excel Automation or OPENXML via Process Scheduler (Doc ID 2481835.1)</a></li>
</ul>
</ul>
<i>"<b>UPTO SET2A-C Fixes</b> - Details-only nPlosion not happening for Single Chart-field nPlosion Criteria.</i><br />
<i>And also encountered a performance issue when enabled details-only nPlosion for most of the row criteria in the same layout</i><br />
<i>Issue was introduced on build 8.55.19.</i><br />
<i>Condition: When most of the row filter criteria enabled Details-only nPlosion. This is solved in 8.55.22 & 8.56.07.</i><br />
<i><b>UPTO SET3 Fixes</b> - Performance issue due to the SET2A-C fixes has solved but encountered new one. Performance issue when first chart-field is same for most of the row criteria in the same layout.</i><br />
<i>Issue was introduced on builds 8.55.22 & 8.56.07.</i><br />
<i>Condition: When most of the filter criteria’s first chart-field is same. The issue is solved in 8.55.25 & 8.56.10."</i><br />
<div>
<h4>
In summary</h4>
<ul>
<li>Bug introduced in PeopleTools 8.55.19, fully resolved in 8.55.25.</li>
<li>Bug introduced in PeopleTools 8.56.07, fully resolved in 8.56.10.</li>
</ul>
</div>
<div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com2tag:blogger.com,1999:blog-25740336.post-88541907659644681782019-10-15T16:15:00.002+01:002020-11-16T14:51:59.969+00:00Which Version Number Corresponds To Which PeopleTools Object?Recently somebody asked me <i>"Our “CRM” values in PSLOCK and PSVERSION are growing tremendously and we don’t know why. We will be opening a case with Oracle but … do you know what that “CRM” value is related to? We are not using the CRM product in our PeopleSoft installation."</i><br />
There is some documentation on the Oracle Support website, but it is not exhaustive and may not be completely up to date.<br />
<ul>
<li><a href="https://support.oracle.com/epmos/faces/DocContentDisplay?id=664848.1" target="_blank">E-AD: What is OBJECTTYPENAME in PSVERSION and PSLOCK for? (Doc ID 664848.1)</a> contains a document that lists the object type names and the PeopleTools table they relate to. This document was originally published in 2013 and relates to PeopleTools 8.49.</li>
<li><a href="https://support.oracle.com/epmos/faces/DocContentDisplay?id=1278867.1" target="_blank">E-AD: What Tables Are Associated With The New Objecttypenames In PSVERSION In PT 8.50? (Doc ID 1278867.1)</a> lists the new object types in PeopleTools 8.50.</li>
</ul>
The page in the <a href="https://github.com/davidkurtz/PTRef" target="_blank">PTRef</a> utility that describes the relationship of <a href="http://www2.go-faster.co.uk/static/objecttypename.htm" target="_blank">version numbers</a> to PeopleTools tables is one of the few static pages in the tool. I have now updated it with the information in the above Oracle support notes, but there are other version numbers.<br />
In the <a href="https://blog.psftdba.com/2019/10/understanding-peoplesoft-version-numbers.html" target="_blank">previous blog post</a>, I showed how to increment version numbers before updating PeopleTools objects. I knew RDM (the Record Definition Manager) is the OBJECTTYPENAME for PSRECDEFN because I worked that out by tracing Application Designer while it saved a record change. That remains the only completely reliable way to determine the relationship.<br />
However, version number matching is also a useful technique, though when it does not provide a completely definitive answer, it dramatically cuts down the amount of work then necessary.<br />
I have written a little PL/SQL <a href="https://github.com/davidkurtz/PTRef/blob/master/sql/psversionmatch.sql" target="_blank">code</a>, that is delivered with PTRef, that extracts the maximum version number for each PeopleTools table in PeopleSoft (other than the PS%DEL) tables and stores it on a working storage table (I used the PLAN_TABLE because it is always there on an Oracle database). Then you can compare the version number on PSLOCK/PSVERSION with the maximum version on the PeopleTools object.<br />
If the version number is 1, you can’t do the match because the version number has never been incremented, at least since it was last reset by the <a href="https://support.oracle.com/epmos/faces/DocContentDisplay?id=611565.1" target="_blank">VERSION</a> application engine.<br />
If the version is only updated occasionally you may have some trouble with duplicate matches. In my example, 3 tables have a maximum version number of 80, while only one version number is 80.<br />
<pre style="background-color: #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%; line-height:1.0"><code>RECNAME MAX CNT
------------------ ---------- ----------
PSAEAPPLDEFN 80 3504
PSMSGNODEDEFN 80 78
PSOPRVERDFN 80 1468</code></pre>
I would guess that OPRVM matches PSOPRVERDFN, and the above support notes confirm this, but otherwise, you would have to check that manually with trace.<br />
<pre style="background-color: #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%; line-height:1.0"><code>OBJECTTY VERSION
-------- ----------
OPRVM 80</code></pre>
Higher version numbers are easier to match because they are less likely to have duplicate matches.<br />
So to return to the original question, what is CRM? In my sample system, version numbers CRM and SCATM are both 3.<br />
<pre style="background-color: #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%; line-height:1.0"><code>OBJECTTY VERSION
-------- ----------
CRM 3
SCATM 3</code></pre>
However, only PSCONTDEFN has a maximum version of 3.<br />
<pre style="background-color: #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%; line-height:1.0"><code>RECNAME MAX CNT
------------------ ---------- ----------
PSCONTDEFN 3 7567</code></pre>
Again not a perfect match, but again <a href="https://support.oracle.com/epmos/faces/DocContentDisplay?id=664848.1" target="_blank">Oracle Support Note 664848.1</a> confirms that CRM corresponds to PSCONTDEFN. CRM stands for Content Registry Manager.<br />
So the question now becomes what is updating the content definitions, and hence increasing the version number? It turned out to be an entity registry synchronisation process that was run daily.<br />
It is perfectly legitimate for many updated rows on the PeopleTools table can be given the same version number. The version number does not need to be incremented again for each row being updated, but then the row-level locks on PSVERSION and PSLOCK created by their updates must be held until the update on the PeopleTools table is committed. That can increase contention on the version number update. The alternative is to commit after each update and then increment the version numbers again. Many PeopleSoft processes do exactly that, and it can, in turn, lead to massive increase in some version numbers.<div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com1tag:blogger.com,1999:blog-25740336.post-50764374883298298472019-10-14T19:36:00.004+01:002021-04-27T11:56:13.923+01:00Understanding PeopleTools Object Version NumbersI was recently asked a question about PeopleSoft version numbers, but before I address that directly, I think it would be useful to explain what is their general purpose.<br />
<h4>
Caching</h4>
The PeopleSoft data model and application are mostly stored in the database in PeopleTools tables. These tables are queried as the application executes. For example, when you open a component, the component and pages, including all the PeopleCode, the definition of any records used, and so on have to be loaded into the component buffer. Ultimately this information comes from the PeopleTools tables. To save the overhead of repeatedly querying these tables, PeopleSoft caches this data locally in physical files the application server and process scheduler domains. The application servers also cache some of this information in memory to save visiting the local physical cache. Application Designer also maintains a physical cache.<br />
Over time, as the application executes, the cache files build up. Occasionally, when it is necessary to delete the cache files and then it becomes clear just how significant is the overhead of the PeopleTools queries as a period of poor performance is seen as the application builds up fresh cache files.<br />
Physical cache files are created in directories in the application server and process scheduler Tuxedo domains. By default, each process maintains its own private cache. Separate directories of cache files are created for each type of PeopleSoft server process in each domain. Pairs of cache files are created in each directory for each object type as needed. There is also a CACHE.LOK file in each directory that is used to ensure that only one process is accessing that cache directory concurrently.<br />
It is possible to run with a shared physical cache, but then it is read-only and must be pre-generated. It is very rare to see this implemented, because everyone expects to continuously deliver changes over time, and if you had a shared cache you would have to deliver an updated set of shared cache file to every domain every time you delivered a new PeopleTools object.<br />
The cache files come in pairs. The name of the cache files is the Object Type Name. This corresponds to the <a href="http://www2.go-faster.co.uk/static/objecttypename.htm#PSVERSION" target="_blank">OBJECTTYPENAME</a> on the <a href="http://www2.go-faster.co.uk/peopletools/pslock.htm" target="_blank">PSLOCK</a> and <a href="http://www2.go-faster.co.uk/peopletools/psversion.htm" target="_blank">PSVERSION</a> tables. The .DAT file contains the data to be cached. The .KEY file is an index for the .DAT file, and it also holds the version number of the cached object.<br />
<pre style="background-color: #eeeeee; border: 0px solid rgb(0, 0, 0); font-family: "courier new"; font-size: 100%; line-height: 1; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;"><code>…
-rw------- 1 psadm2 oracle 5228492 Jun 12 06:37 RDM.DAT
-rw------- 1 psadm2 oracle 69120 Jun 12 06:37 RDM.KEY
-rw------- 1 psadm2 oracle 0 Oct 26 2015 ROLM.DAT
-rw------- 1 psadm2 oracle 24192 Oct 26 2015 ROLM.KEY
-rw------- 1 psadm2 oracle 0 Oct 26 2015 RSM.DAT
-rw------- 1 psadm2 oracle 24192 Oct 26 2015 RSM.KEY
…</code></pre>
<h4>
Version Numbers</h4>
<div>
Version numbers track when a cached PeopleTools object has been changed, either by Application Designer, or a change in configuration, or the application. The version numbers are sequences generated from two PeopleTools tables PSLOCK and PSVERSION that hold the highest version number for each type of object. These two tables have the same structure.</div>
<pre style="background-color: #eeeeee; border: 0px solid rgb(0, 0, 0); font-family: "courier new"; font-size: 100%; line-height: 1; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;"><code>SQL> desc psversion
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECTTYPENAME NOT NULL VARCHAR2(8 CHAR)
VERSION NOT NULL NUMBER(38)</code></pre>
There are now over 100 different version numbers, each with a specific object type name that each track a specific PeopleTools object. There is a global version number, with the object type name of SYS, that is incremented whenever any other version number is incremented.<br />
I have no idea why two identical tables of version numbers were created. I can see no reason for this, but it has been like this since the version numbers were changed (if I remember correctly) in PeopleTools 7. In early versions of PeopleTools, not all version numbers were on both tables, but in at least PeopleTools 8.55 only one object type appears on PSVERSION and not PSLOCK.<br />
When an object is changed, the object and global version numbers are incremented, and the incremented object version number is recorded on the object in the PeopleTools table. The version number on the object is also stored in the physical cache files when the object is cached. If the version on the database is higher than that in the cache file, then the PeopleSoft process knows it must query the latest version from the PeopleTools table and update the cache file.<br />
<div>
<h4>
How to Update Version Numbers</h4>
<div>
It is not generally recommended, nor strictly speaking, supported to update PeopleTools tables directly with SQL. Apart from the risk of updating them incorrectly, or to invalid values, you also need to ensure that the changes are picked up by PeopleTools processes and that they do not simply continue to read the cached data. However, occasionally, it is the pragmatic way to doing something. </div>
<div>
Here is an example from Chapter 5 of <a href="https://www.apress.com/gp/book/9781430237075" target="_blank">PeopleSoft for the Oracle DBA</a> that shows how to maintain version numbers so the change is picked up by PeopleTools processes. I want to mark alternate search key indexes as unique where there is a unique key on a record because they are unique because the unique key is a subset of their columns. Then Application Designer will build the indexes as unique. </div>
<pre style="background-color: #eeeeee; border: 0px solid rgb(0, 0, 0); font-family: "courier new"; font-size: 100%; line-height: 1; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;"><code>UPDATE psversion SET version = version + 1
WHERE objecttypename IN('SYS','RDM');
UPDATE pslock SET version = version + 1
WHERE objecttypename IN('SYS','RDM');
UPDATE psrecdefn
SET version = (
SELECT version FROM psversion WHERE objecttypename = 'RDM')
WHERE recname = '<record name="">';
UPDATE psindexdefn a
SET a.uniqueflag = 1
WHERE a.uniqueflag = 0
AND a.indextype = 3
AND EXISTS(
SELECT 'x'
FROM psindexdefn k
WHERE k.recname = a.recname
AND k.indexid = '_'
AND k.indextype = 1
AND k.uniqueflag = 1)
AND a.recname = '<record name="">';</record></record></code></pre>
I am updating a PeopleTools object (<a href="http://www2.go-faster.co.uk/peopletools/psindexdefn.htm" target="_blank">PSINDEXDEFN</a>) that doesn't have a version number, but its parent is <a href="http://www2.go-faster.co.uk/peopletools/psrecdefn.htm" target="_blank">PSRECDEFN</a> that does have a version number. I happen to know that object type <a href="http://www2.go-faster.co.uk/static/objecttypename.htm#RDM" target="_blank">RDM</a> (the Record Definition Manager) generates the version number for PSRECDEFN. I found that out by tracing Application Designer while it saved a record change. That is the only completely reliable method to determine which sequence is used for which record. However, I will discuss another less onerous matching method in a <i>subsequent blog post</i>.</div>
<div>
I must increment the RDM and SYS version numbers and write the new RDM version number to the updated rows on PSRECDEFN. Next time a PeopleSoft process needs to read a record definition it will check the version numbers. The increment of the SYS object tells PeopleSoft than an object number has changed, and then it will detect that the RDM version number has changed so it has to reload and cache objects with version numbers greater than the last cached version number for that object.
</div>
<div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com10tag:blogger.com,1999:blog-25740336.post-39440861901461597492019-06-09T21:46:00.001+01:002020-09-23T20:48:31.149+01:00PeopleTools Table Reference GeneratorLike many other PeopleSoft professionals, I spend a lot of time looking at the PeopleTools tables because they contain meta-data about the PeopleSoft application. Much of the application is stored in PeopleTools tables. Some provide information about the Data Model. Many of my utility scripts reference the PeopleTools tables, some of them update them too. Therefore, it is very helpful to be able to understand what is in these tables. In <a href="http://www.psftdba.com/" target="_blank">PeopleSoft for the Oracle DBA</a>, I discussed some tables that are of regular interest. I included the tables that correspond to the database catalogue and that are used during the PeopleSoft login procedure. The tables that are maintained by the process scheduler are valuable because they contain information about who ran what process when, and how long they ran for.<br />
I am not the only person to have started to document the PeopleTools tables on their website or blog, most people have picked a few tables that are of particular interest. I wanted to produce a complete reference. However, with over 4000 PeopleTools tables and views, it is simply not viable to do even a significant number of them manually. So, I wrote some SQL and PL/SQL to dynamically generate a page for each PeopleTools table and views and put the generated pages on my website. If you use Google to search for a PeopleTools name you will probably find the reference page.<br />
I have now revisited that code and made a number of changes and improvements.<br />
<ul>
<li>I have used a later version of PeopleTools to generate the reference on my website. The list of PeopleTools tables is no longer defined in PeopleTools by object security, so I have used an independent and somewhat broader definition: Table or View records that are either owned by PPT or whose SQLTABLENAME is the same as the record name.</li>
<li>There is much less reliance on static pages. There are now only 3 such pages everything else is generated. Instead, additional data is loaded from static scripts into the PLAN_TABLE that should always be present in an Oracle database and so it doesn't have to be created. It should be a global temporary table so there is no problem with debris being left behind or interference with other processes. That data is then combined with data in the PeopleTools tables during the generation process.</li>
<li>The utility has been rewritten as a PL/SQL package that should be created in the PeopleSoft Owner ID schema (usually SYSADM).</li>
<li>The generated HTML is simply tidier, and more consistent.</li>
</ul>
The source is available on GitHub at <a href="https://github.com/davidkurtz/PTRef" target="_blank">https://github.com/davidkurtz/PTRef</a>, so you can download and generate your own reference on your own current version of PeopleTools. An example of the generated output can be found on my <a href="http://www2.go-faster.co.uk/peopletools/_ptindex.htm" target="_blank">website</a>.<br />
The idea of the original PeopleTools tables reference was the people could contribute additional descriptions and information that were not in the PeopleTools tables. That can still happen, and indeed should be easier, by making changes to the scripts that load the additional data and uploading new versions to GitHub.<br />
<br />
<br /><div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com1tag:blogger.com,1999:blog-25740336.post-17362554819306755832019-06-07T15:08:00.004+01:002023-04-13T13:37:48.841+01:00PS360 enhancement: Added report of DDL modelsI have written several blogs and presentations recently about how and how not to collect statistics in PeopleSoft.<br />
<ul>
<li><a href="http://blog.psftdba.com/2018/06/psft-cbo-stats.html">Managing Cost-Based Optimizer Statistics for PeopleSoft</a> recommends</li>
<ul>
<li>If you are going to continue to use <i>DBMS_STATS</i> in the DDL model then</li>
<ul>
<li>Do not specify <i>ESTIMATE_PERCENT</i> because it disables the hash-based number-of-distinct-values calculation, forcing it to go back to the <i>COUNT(DISTINCT …)</i> method that requires a sort, and may not produce accurate values because it only samples data.</li>
<li>Do not specify <i>METHOD_OPT='FOR ALL INDEXED COLUMNS SIZE 1' </i>because this will not collect histograms on indexed columns, and will not update column statistics on unindexed columns.</li>
<li>Do specify <i>FORCE=>TRUE</i> so that you can lock and delete statistics on temporary records.</li>
</ul>
<li>However, the alternative is to use <i>GFCSTATS11</i> package to collects these statistics. This package is controlled by a metadata table so you can defined statistics collection behaviour for specific records.</li>
</ul>
<li><a href="http://blog.psftdba.com/2018/09/how-not-to-collect-optimizer-statistics.html">How Not to Collect Optimizer Statistics in an Application Engine Program</a></li>
<ul>
<li>This blog explains why you should not explicitly code <i>DBMS_STATS</i> calls into Application Engine programs.</li>
</ul>
</ul>
This has prompted me to add a new report to the <a href="http://blog.psftdba.com/2016/04/ps360-utility-to-extract-and-present.html" target="_blank">PS360</a> utility that simply reports the various DDL models for Oracle. Thus during a health check, I can see how statistics are collected during batch processes.<br />
<br />
<a href="http://blog.psftdba.com/2016/04/ps360-utility-to-extract-and-present.html" target="_blank">PS360</a> can be download from <a href="https://github.com/davidkurtz/ps360">https://github.com/davidkurtz/ps360</a>.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div style="display: inline-block; float: left; overflow-x: scroll; width: 100%;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEge9z-fLnmCr2-N5S9Lwv-efjxm3mqMEZOTJuGXDv5DHSSeeZpBoX8fsuxRzgptFyqQ664PVIxAEFyzZHeoEBUxR-3Z5gbHr0U-tUNBBI1jflveUpG-JFstEw-b1dI5lTYCXQiU/s1600/psddlmodel.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="514" data-original-width="1600" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEge9z-fLnmCr2-N5S9Lwv-efjxm3mqMEZOTJuGXDv5DHSSeeZpBoX8fsuxRzgptFyqQ664PVIxAEFyzZHeoEBUxR-3Z5gbHr0U-tUNBBI1jflveUpG-JFstEw-b1dI5lTYCXQiU/s1600/psddlmodel.PNG" /></a>
</div>
<br />
<br />
<br /><div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com0tag:blogger.com,1999:blog-25740336.post-84170329378148443592019-05-29T12:42:00.000+01:002019-09-10T16:36:02.944+01:00PeopleSoft Adminstrator Podcast: #184 – nVision PerformanceI recorded a second podcast with Dan Iverson and Kyle Benson for the <a href="http://psadmin.io/category/podcast/" target="_blank">PeopleSoft Administrator Podcast</a>, this time about nVision.<br />
<h4>
<span style="font-size: xx-small;">(10 May 2019) </span><a href="https://psadmin.io/2019/05/10/184-nvision-performance/" target="_blank">#184 – nVision Performance</a></h4>
<iframe allowfullscreen="" height="100" mozallowfullscreen="" msallowfullscreen="" oallowfullscreen="" scrolling="no" src="//html5-player.libsyn.com/embed/episode/id/9374513/height/100/theme/custom/thumbnail/yes/direction/forward/render-playlist/no/custom-color/04a6ff/" style="border: none;" webkitallowfullscreen="" width="100%"></iframe>
You can listen to the podcast on <a href="http://psadmin.io/blog/" target="_blank">psadmin.io</a>, or subscribe with your <a href="http://psadminio.libsyn.com/rss" target="_blank">favourite podcast player</a>, or <a href="feed://psadminio.libsyn.com/rss">in </a><a href="https://itunes.apple.com/us/podcast/psadminios-podcast/id1057833205">iTunes</a>.<div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com0tag:blogger.com,1999:blog-25740336.post-61783221190979244822019-05-08T20:09:00.003+01:002019-09-10T16:35:33.632+01:00PeopleSoft Administrator Podcast: #183 – Effective Performance MonitoringI recently recorded a podcast with Dan Iverson and Kyle Benson for the <a href="http://psadmin.io/category/podcast/" target="_blank">PeopleSoft Administrator Podcast</a>, this time about instrumentation, monitoring the performance of PeopleSoft system, and Performance Monitor. There is also just a little about cursor sharing.<br />
<h4>
<span style="font-size: xx-small;">(3 May 2019) </span><a href="https://psadmin.io/2019/05/03/183-effective-performance-monitoring/" target="_blank">#183 – Effective Performance Monitoring</a></h4>
<iframe allowfullscreen="" height="100" mozallowfullscreen="" msallowfullscreen="" oallowfullscreen="" scrolling="no" src="//html5-player.libsyn.com/embed/episode/id/9374480/height/100/theme/custom/thumbnail/yes/direction/forward/render-playlist/no/custom-color/04a6ff/" style="border: none;" webkitallowfullscreen="" width="100%"></iframe>
You can listen to the podcast on <a href="http://psadmin.io/blog/" target="_blank">psadmin.io</a>, or subscribe with your <a href="http://psadminio.libsyn.com/rss" target="_blank">favourite podcast player</a>, or <a href="feed://psadminio.libsyn.com/rss">in </a><a href="https://itunes.apple.com/us/podcast/psadminios-podcast/id1057833205">iTunes</a>.<div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com0tag:blogger.com,1999:blog-25740336.post-43019391199552616882019-03-08T18:00:00.004+00:002023-10-17T09:00:46.286+01:00Effective PeopleSoft Performance MonitoringThis advice note describes how to configure PeopleSoft systems on Oracle so that performance metrics are collected that are useful performance monitoring and subsequent performance tuning.<br />
<h4>
Contents</h4>
<ul>
<li>Oracle RDBMS Instrumentation</li>
<ul>
<li>On-Line Component/Page Information</li>
<li>Application Engine Step Information</li>
<li>Cobol/nVision Instrumentation</li>
<li>nVision Layout Instrumentation</li>
<li>2nd Database Connection Instrumentation</li>
</ul>
<li>PeopleTools Performance Metrics</li>
<ul>
<li>Cobol and Application Engine Batch Timings</li>
<li>PeopleSoft Performance Monitor</li>
</ul>
</ul>
<h4>
Summary of Recommendations</h4>
<ul>
<li>Set <i>EnableAEMonitoring=1</i> in all Application Server and process scheduler domains in order to enable PeopleSoft instrumentation on-line, in the integration broker, and in Application Engine programs.</li>
<li>Implement the <a href="http://www2.go-faster.co.uk/scripts.htm#psftapi.sql" target="_blank">PSFTAPI</a> package and trigger described above to set module and action at the start of all processes.</li>
<li>Implement Fine-Grained Audit policy and handler to instrument nVision processes if required.</li>
<li>Reduce AWR snapshot frequency from the default of 1 hour to every 15 minutes.</li>
<li>Enable Application Engine batch timings to at least database and preferably also file by setting <i>AETrace=1152</i> in all Application Server and Process Scheduler domain configuration files.</li>
<li>Enable Cobol statement timings report should be written to log file by setting <i>TraceSQL=128</i> in all Process Scheduler domain configuration files.</li>
<li>Performance Monitor is complex to set up and the delivered analytics are limited. Nonetheless, the information that can be obtained from the data collected can be worth the effort of configuration to address on-line configuration and performances issues.</li>
</ul>
<h3>
Oracle RDBMS Instrumentation</h3>
Oracle provides the <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_APPLICATION_INFO.html" target="_blank">dbms_application_info</a> package to set certain attributes on the current database session. These attributes are visible in some of the dynamic performance version, and are picked up by Active Session History (ASH) and can also be seen in AWR reports, Enterprise Manager screens, SQL trace files, and other performance utilities such as <a href="https://mauro-pagano.com/2018/06/11/introducing-sqldb360-merging-edb360-and-sqld360-while-rising-the-bar-to-community-engagement/" target="_blank">EDB360</a>. The package was first documented in Oracle 7.3.3<br />
<blockquote class="tr_bq">
<i>"Application developers can use the DBMS_APPLICATION_INFO package to record the name of the executing module or transaction in the database for use later when tracking the performance of various modules… System administrators can also use this information to track resource usage by module. When an application registers with the database, its name and actions are recorded in the V$SESSION and V$SQLAREA views. Your applications should set the name of the module and name of the action automatically each time a user enters that module. The module name could be the name of a … code segment in an … application. The action name should usually be the name or description of the current transaction within a module."</i></blockquote>
<blockquote class="tr_bq">
•<span style="white-space: pre;"> </span><i>Oracle 7 Tuning, release 7.3.3 ©Oracle 1997, Chapter 23 Registering Applications</i></blockquote>
•<span style="white-space: pre;"> </span>See also <a href="https://blog.go-faster.co.uk/2016/09/dbmsapplicationinfo.html" target="_blank">One of my Favourite Database Things: DBMS_APPLICATION_INFO</a><br />
If module and action are consistently set to meaningful values as the application executes it is then possible to determine from where SQL statements originated and how different parts of the application are performing.<br />
ASH is separately licenced as part the Diagnostics Pack, that is only available on Enterprise Edition of the database. However, most PeopleSoft customers running on Oracle do so on Enterprise Edition and are licenced for the diagnostics pack.<br />
PeopleSoft has set <i>client_info</i> since PeopleTools 7.53 so the PeopleSoft operator ID can be associated with the database session, mainly to allow auditing to be done with database triggers. However, this attribute is not persisted to the ASH data. However, <i>client_id</i> is also set to the operator ID, and this is collected by ASH.<br />
PeopleTools has set module and action since PeopleTools 8.50, though the exact values used have changed sometimes with the PeopleTools version.<br />
A new application server/process scheduler domain parameter <i>EnableAEMonitoring</i> was introduced in PeopleTools 8.54 to control this behaviour and it is not enabled by default. If monitoring is not enabled module defaults to the program name and action remains blank.<br />
<b><i>Recommendation: Set EnableAEMonitoring=1 in all application server and process scheduler domains in order to enable PeopleSoft instrumentation online, in the integration broker, and in Application Engine programs.</i></b><br />
See also:<br />
<ul>
<li><a href="http://blog.psftdba.com/2010/11/peopletools-850-uses.html" target="_blank">PeopleTools 8.50 uses DBMS_APPLICATION_INFO to Identify Database Sessions</a></li>
<li><a href="https://blog.psftdba.com/2012/11/peopletools-852-application-engine-sets.html" target="_blank">PeopleTools 8.52 Application Engine sets MODULE and ACTION</a> </li>
<li><a href="https://blog.psftdba.com/2015/03/undocumented-application-engine.html" target="_blank">Undocumented (until PeopleTools 8.55) Application Engine Parameter: EnableAEMonitoring</a> </li>
</ul>
<h4>
On-Line Component/Page Information</h4>
In the online application, module and action are set to the component and page name respectively. In a search dialogue, Action is set to 'xyzzy'.<br />
In the Integration Broker module and action are set to service name and queue name.<br />
<h4>
Application Engine Step Information</h4>
In Application Engine, module is set to a string that includes the name of the Application Engine main program the was called and the sessionid_num for the current process instance number recorded on the process scheduler request table PSPRCSQUE. For example: <i>PSAE.PSPMCSOSUM.1448</i><br />
Later on, it may be necessary to use regular expressions in SQL to process this string before profiling the ASH data.<br />
Action is set to string concatenated from the Application Engine program, section, step name, and step type. For example: <i>PSPMCSOSUM.GETCNT.CNT.P</i><br />
The program name may be different from that shown in module if one AE program calls another.<br />
Note: Application Engine still doesn't reset ACTION on DO SELECT steps<br />
<ul>
<li>See <a href="https://blog.psftdba.com/2012/11/peopletools-852-application-engine-sets.html" target="_blank">PeopleTools 8.52 Application Engine sets MODULE and ACTION</a>).</li>
</ul>
<h4>
Cobol/nVision Instrumentation</h4>
Cobol, nVision, and SQR do not set module and action. Instead, they can be set at the start of every program initiated by Process Scheduler using a trigger on PSPRCSRQST. The first thing a process does when it is initiated is to set the run status on its scheduler request record to 7, indicating that it is processing. This is visible in the Process Monitor component. A trigger on this transition can set module and action for the session to the program and process instance number on that row of data.<br />
This technique was used prior to PeopleSoft adding instrumentation to PeopleTools. It can still be applied to all processes, including Application Engine because any PeopleSoft instrumentation will simply overwrite the value set by the trigger.<br />
<ul>
<li>See <a href="http://www2.go-faster.co.uk/scripts.htm#psftapi.sql" target="_blank">PSFTAPI package and trigger</a></li>
</ul>
<b><i>Recommendation: Implement the PSFTAPI package and trigger described above in order to set module and action at the start of all pr</i>ocesses.</b><br />
The same technique was also used prior to the introduction of ASH to enable Oracle SQL Trace if a particular run control was specified, and is still occasionally useful.<br />
<ul>
<li><span style="white-space: pre;">See </span><a href="https://blog.psftdba.com/2008/05/enabling-oracle-database-trace-on.html" target="_blank">Enabling Oracle Database Trace on PeopleSoft processes with a Trigger (improved)</a></li>
</ul>
<h4>
nVision Layout Instrumentation</h4>
One of the challenges of tuning and monitoring nVision is to be able to identify each report being run. nVision reports always run as the same process name, either NVSRUN for a single report, RPTBOOK for a report book of many reports, or DRILLDWN for a nVision drill-down query. Knowing the process instance is useful because then we can look up the operator and run control ID<br />
However, it would also be useful to know the report ID being run. When each individual nVision report starts it queries the runtime parameters from the PS_NVS_REPORT PeopleTools table. There is no update, so it is not possible to capture this with a DML trigger. Instead, it is possible to define a fine-grained audit policy on the query and set module within a PL/SQL handler package that is invoked by the fine-grained audit.<br />
<ul>
<li>See<span style="white-space: pre;"> </span><a href="https://blog.psftdba.com/2017/11/nvision-performance-tuning-5-additional.html" target="_blank">nVision Performance Tuning: 5. Additional Instrumentation of nVision</a></li>
</ul>
<b><i>Recommendation: Implement Fine-Grained Audit policy and handler to instrument nVision processes if required</i></b><br />
<h4>
2nd Database Connection Information</h4>
PeopleSoft programs use a second database connection to increment sequence numbers to minimise the row-level locking on such tables. It is like an AUTONOMOUS_TRANSACTION in PL/SQL. There is no PeopleSoft instrumentation on this session. It is possible to use an AFTER LOGON trigger to set client_info, module and action.<br />
<ul>
<li>See also <a href="https://blog.psftdba.com/2008/07/sequence-number-allocation-in.html" target="_blank">Sequence Number Allocation in PeopleSoft</a></li>
</ul>
<h3>
Oracle Automatic Workload Repository (AWR) Snapshots</h3>
PeopleSoft generates a lot of non-shareable SQL.<br />
<ul>
<li>Dynamically generated SQL, often in PeopleCode, concatenates strings of SQL with bind variables, thus the bind variables become literals in the final SQL statement. Statements with different literal values are considered to be different statements with different SQL_IDs.</li>
<li>Similarly, dynamic Cobol statements result in literal values in the SQL statement.</li>
<li><i>%BIND()</i> variables in Application Engine will also become literal values in the SQL Statement unless the <i>ReUseStatement</i> attribute is set on the AE step, however, this cannot be set on statements with dynamic fragments code are introduced with <i>%BIND(…,NOQUOTES)</i>.</li>
<li>Application Engine programs that use temporary records can use different non-shared instances of the record in different executions, and this also results in different statements with different SQL_IDs.</li>
<li>See also <a href="https://blog.psftdba.com/2009/02/performance-benefits-of-reuse-statement_27.html" target="_blank">Performance Benefits of ReUse Statement Flag in Application Engine</a></li>
</ul>
Consequently, the library caching is not particularly efficient in PeopleSoft, and dynamic SQL statements are often quickly aged out of the library cache. AWR snapshots can only capture the SQL that is in the library cache at the time of the snapshot. If the SQL statement, or at least a statement with the same force matching signature or plan hash value, cannot be found in the AWR it cannot be identified or analysed. Therefore, it is advantageous to increase the snapshot frequency on PeopleSoft systems.<br />
<i><b>Recommendation: Reduce AWR snapshot frequency from the default of 1 hour to every 15 minutes. </b><b>This change results in only a modest increase in overhead in processing and space on AWR, but it is worth the additional information that is captured.</b></i><br />
This advice also applies to Statspack that may be used if you are not licenced for the Diagnostics Pack.<br />
<h3>
PeopleTools Performance Metrics</h3>
<h4>
Batch Timings</h4>
PeopleSoft Application Engine and Cobol programs can emit batch timings reports on successful completion.<br />
<h5>
Application Engine</h5>
Application Engine batch timings are controlled by the AETrace flag in the Process Scheduler domain configuration file and for on-line AE programs in the Application Server domain configuration files.<br />
<ul>
<li>AETrace=128: batch timings report is written to the AE Trace file to</li>
<li>AETrace=1024: batch timings are written to PS_BAT_TIMINGS% tables in the database</li>
</ul>
The overhead of batch timings is negligible while the program is running because it is accounted in memory and only written to a file or the database when the process completes successfully.<br />
<b><i>Recommendation: Enable Application Engine batch timings to at least database and preferably also file by setting AETrace=1152 in all Application Server and Process Scheduler domain configuration files.</i></b><br />
The trace setting in the process scheduler configuration can be overridden by setting process-specific command line parameter overrides in the process definition. This is often done to set other trace settings, it is also common to see these unintentionally left in place longer than necessary. If trace is enabled in this way, the batch timings flags should also be set.<br />
See <a href="https://docs.oracle.com/cd/E66686_01/pt855pbr1/eng/pt/tape/task_EnablingApplicationEngineTracing-077149.html" target="_blank">PeopleBooks -> Development Tools -> Application Engine -> Enabling Application Engine Tracing</a><br />
<h5>
Cobol</h5>
PeopleSoft Cobol programs can only write batching timings reports to file and not to the database. This is controlled by a different parameter.<br />
<ul>
<li>TraceSQL = 128: Enable Cobol statement timings report </li>
</ul>
<b><i>Recommendation: Enable Cobol statement timings report should be written to log file by setting TraceSQL=128 in all Process Scheduler domain configuration files.</i></b><br />
This trace setting can also be overridden by setting process-specific command line parameter overrides in the process definition. If trace is enabled in this manner, then it should always also set the batch timings flags.<br />
<h3>
PeopleSoft Performance Monitor</h3>
This provides information about the performance of the PIA including response times for the online transactions. Metrics are stored in a separate monitoring PeopleSoft system to minimize the effect of measurement intrusion. It optionally samples the state of each web server, application server and process scheduler collecting operating system and Tuxedo metrics. It also has a PIA session trace capability.<br />
The sampled data includes the number of busy application server processes and length of inbound Tuxedo service queues. This data can be used to validate the sizing of the application servers.<br />
<b><i>Recommendation: Performance Monitor is complex to set up and the delivered analytics are limited. Nonetheless, the information that can be obtained from the data collected can be worth the effort of configuration to address online configuration and performance issues.</i></b><br />
<div>
<br />
Related post:<br />
<br />
<a href="https://blog.psftdba.com/2019/12/practical-application-performance.html" target="_blank"></a><ul><a href="https://blog.psftdba.com/2019/12/practical-application-performance.html" target="_blank"></a>
<li><a href="https://blog.psftdba.com/2019/12/practical-application-performance.html" target="_blank"></a><a href="https://blog.psftdba.com/2019/12/practical-application-performance.html" target="_blank">Practical Application Performance Tuning: An nVision Case Study (Video)</a></li>
</ul>
</div>
<div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com0tag:blogger.com,1999:blog-25740336.post-30085722078306424442018-09-06T11:24:00.001+01:002020-10-30T15:14:44.483+00:00How Not to Collect Optimizer Statistics in an Application Engine ProgramI was told about a PeopleSoft customer experiencing an Oracle error when collecting statistics during an Application Engine.<br />
<pre style="background-color: #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%; line-height:1"><code>ORA-06533: Subscript beyond count
ORA-06512: at "SYS.DBMS_STATS"…</code></pre>
It is possibly a manifestation of a database bug. The workaround was not to use <i>AUTO_SAMPLE_SIZE</i>, so instead, this customer initially coded an explicit call to <i>DBMS_STATS.GATHER_TABLE_STATS</i> with a specific sample size.<br />
This blog is not about the bug, but how to manage the workaround.<br />
<h3>
<span style="color: red;">DO NOT TRY THIS AT HOME!</span></h3>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxKjZal_Xi9YGEB43V2ONH9kvYBabVhQ57LCwncc4tZw1r9kArhm8CiU7Jm52YCEOgxgsHvc1NLXQhU7hGInH4izyt33bUzxdyfEPH9g367rtGX8lyFj0rNCLXEL24tDqpKRyW/s1600/1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="" border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxKjZal_Xi9YGEB43V2ONH9kvYBabVhQ57LCwncc4tZw1r9kArhm8CiU7Jm52YCEOgxgsHvc1NLXQhU7hGInH4izyt33bUzxdyfEPH9g367rtGX8lyFj0rNCLXEL24tDqpKRyW/s640/1.png" title="" width="100%" /></a></div>
<br />
I think that there are a number of problems with this approach<br />
<ol>
<li>Using a fixed sample size rather than <i>AUTO_SAMPLE_SIZE</i> should only be considered as a temporary workaround. The new hash-based number-of-distinct-values (NDV) algorithm in Oracle 12c only works with <i>AUTO_SAMPLE_SIZE</i>, and it produces more accurate statistics and runs faster because saves a large sort operation. Coding a fixed sample size into an Application Engine requires a managed change to be made, tested and released into production, and then when the underlying problem is resolved the customisation needs to be removed by the same managed process. </li>
<li><i>DBMS_STATS.GATHER_TABLE_STATS</i> implies a commit. That can lead to problems that PeopleSoft avoids by only calling statistics via the <i><a href="https://docs.oracle.com/cd/E92519_01/pt856pbr2/eng/pt/tape/langref_UsingApplicationEngineMeta-SQL-0771d9.html#u09970f4c-647e-4d70-95ef-71b76988d24b_s" target="_blank">%UpdateStats</a></i> macro and controlling when that macro does and does not execute. </li>
</ol>
<h4>
Committing and Restart Checkpointing in Application Engine</h4>
If a restartable Application Engine program fails it rolls back to the last commit point and it can then be restarted from that point. Committing is managed by Application Engine at section and step levels where the program state record is updated accordingly. If an error occurs in a step after the implicit commit in DBMS_STATS, it can result in the data in the application tables being different to where the state record indicates the program can be restarted. The program may not restart, or it could conceivably execute but produce erroneous results.<br />
Committing inside a do while loop, including any other Application Engine program called from inside the loop is suppressed at Application Engine section/step level and therefore the execution of <i>%UpdateStats</i> macro is also suppressed. Otherwise, you could get rogue <i>ORA-01555 Snapshot Too Old</i> errors. Suppression of <i>%UpdateStats</i> is reported in the Application Engine step trace.<br />
<pre style="background-color: #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%; line-height:1"><code>-- 19:07:37.199 .(AEMINITEST.MAIN.LOOP) (Do Select)
%Select(recname) SELECT recname FROM psrecdefn WHERE recname = 'JOB'
/
-- Buffers:
-- 1) JOB
-- 19:07:37.204 Iteration 1 of .(AEMINITEST.MAIN.LOOP) <b>(Do Select) loop</b>
-- 19:07:37.208 .(AEMINITEST.MAIN.LOOP) (SQL)
RECSTATS PS_JOB LOW
/
-- 19:07:37.211 <b>UpdateStats ignored - COMMIT required</b>
-- 19:07:37.212 .(AEMINITEST.MAIN.LOOP) <b>(Do Fetch)</b></code></pre>
Even a previously uncommitted SQL step can lead to <i>%UpdateStats</i> being suppressed.<br />
<pre style="background-color: #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%; line-height:1"><code>-- 19:07:35.205 .(AEMINITEST.MAIN.Step01) (SQL)
<b>UPDATE PS_AETEMPTBLMGR SET AE_DEDICATED = 1 WHERE 0 = 1
</b>/
-- Row(s) affected: 0
-- 19:07:35.213 .(AEMINITEST.MAIN.Step02) (PeopleCode)
-- 19:07:35.220 .(AEMINITEST.MAIN.US1) (SQL)
RECSTATS PS_JOB LOW
/
-- 19:07:35.227 <b>UpdateStats ignored - COMMIT required</b></code></pre>
If you code <i>DBMS_STATS.GATHER_TABLE_STATS</i> explicitly, Application Engine will not recognise the step as having committed. In the following example, you can see the <i>%UpdateStats</i> on the last step has been suppressed because it Application Engine does not recognise that the update in the first step has been committed by the call to DBMS_STATS.<br />
<pre style="background-color: #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%; line-height:1"><code>-- 19:12:06.690 .(AEMINITEST.MAIN.Step01) (SQL)
UPDATE PS_AETEMPTBLMGR SET AE_DEDICATED = 1 WHERE 0 = 1
/
-- Row(s) affected: 0
-- 19:12:06.696 .(AEMINITEST.MAIN.Step02) (PeopleCode)
-- 19:12:06.700 .(AEMINITEST.MAIN.GTS) (SQL)
<b>%Execute(/) BEGIN
dbms_stats.gather_table_stats('SYSADM','PS_JOB',estimate_percent=>1); END; /
/
</b>-- Row(s) affected: 1
-- 19:12:09.871 .(AEMINITEST.MAIN.US1) (SQL)
RECSTATS PS_JOB LOW
/
-- 19:12:09.877 <b>UpdateStats ignored - COMMIT required</b>
/</code></pre>
Perhaps, the safest form of this workaround would be to have the step with <i>DBMS_STATS</i> and the immediately preceding step explicitly commit as in the following example. I have also made the program restartable. Now restart data is checkpointed, and the <i>%UpdateStats</i> macro executes at step US1.<br />
<pre style="background-color: #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%; line-height:1"><code><b>Restart Data CheckPointed</b>
/
<b>COMMIT</b>
/
-- 19:20:24.792 .(AEMINITEST.MAIN.GTS) (SQL)
<b>%Execute(/) BEGIN
dbms_stats.gather_table_stats('SYSADM','PS_JOB',estimate_percent=>1); END; /
/</b>
-- Row(s) affected: 1
/
/
<b>Restart Data CheckPointed
</b>/
<b>COMMIT</b>
/
-- 19:20:29.153 .(AEMINITEST.MAIN.US1) (SQL)
RECSTATS PS_JOB LOW
/
-- Row(s) affected: 1
/</code></pre>
However, you have to consider the state the application data after an error, whether you wish to restart or cancel the Application Engine because you can no longer rollback.<br />
<h3>
Doing the Right Thing</h3>
<div>
I recommend that:<br />
<ul>
<li>You should only ever collect stats in Application Engine with the <i>%UpdateStats</i> macro that in turn executes the command in the DDL model.</li>
<li>From Oracle 11g both PeopleSoft statistics gathering DDL models should be the same and should ultimately call <i>DBMS_STATS</i> without any parameters other than the table name. The default value of <i>ESTIMATE_PERCENT</i> is <i>AUTO_SAMPLE_SIZE</i>.</li>
</ul>
There are two DDL models in PeopleSoft because <i>%UpdateStats</i> can be invoked with a second parameter to collect the statistics HIGH or LOW. This dates back to Oracle's <i>ANALYZE</i> command that could either compute or estimate statistics (and other database platforms had similar options). Collecting optimizer statistics with <i>ANALYZE</i> has been deprecated for many years, but the command still has other valid uses. It was superceded by <i>DBMS_STATS</i> in Oracle 8i (released in 1998).<br />
Automatic sample size was introduced in Oracle 9i. In Oracle 9i and 10g, it was usual to use automatic sample size in the high statistics gathering model and a small fixed sample size in the low model for use on very large tables. The <i>LOW</i> parameter was specified on <i>%Updatestats</i> in Application Engine programs as necessary.<br />
This approach became redundant from Oracle 11g with the introduction of table preferences. If you need to collect statistics with a specific rather than the automatic sample size or specify any other parameters, then a table preference should be created. Preferences apply wherever statistics are gathered on that table and not overridden in the call of <i>DBMS_STATS., </i>including schema and database-wide operations such as the maintenance window. If there are multiple places where statistics are collected on a table, a preference assures that the statistics will always be collected will be consistently.<br />
From Oracle 12c, as the new NDV algorithm only works with <i>AUTO_SAMPLE_SIZE</i>, you should always use the default unless you have an overarching reason to the contrary, and then you should use a table preference. This approach does not require any application code change because the preference is an attribute of a table in the database.<br />
I recommend using <a href="https://github.com/davidkurtz/gfcpsstats">GFCPSSTATS</a> package, calling it from the DDL model (see previous blog <a href="http://blog.psftdba.com/2018/06/psft-cbo-stats.html">Managing Cost-Based Optimizer Statistics for PeopleSoft</a>). The package also includes a mechanism to specify table preferences in metadata, so that they are automatically instantiated when a table is created or altered by Application Designer, and are applied to every instance of a temporary record.</div>
<pre style="background-color: #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%; line-height:1"><code>gfcpsstats11.ps_stats(p_ownname=>[DBNAME], p_tabname=>[TBNAME]);</code></pre>
Alternatively, just call DBMS_STATS with no additional parameters (other than FORCE, in case you lock statistics on temporary tables) but then you must manage table preferences manually.<br />
<pre style="background-color: #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%; line-height:1"><code>DBMS_STATS.GATHER_TABLE_STATS(ownname=>[DBNAME], tabname=>[TBNAME], force=>TRUE);</code></pre>
<h4>
Further Reading</h4>
<div>
<ul>
<li>Oracle Optimizer Blog: <a href="https://blogs.oracle.com/optimizer/how-does-auto_sample_size-work-in-oracle-database-12c">How does AUTO_SAMPLE_SIZE work in Oracle Database 12c?</a></li>
<li>Oracle Optimizer Blog: <a href="https://blogs.oracle.com/optimizer/how-does-autosamplesize-work-in-oracle-database-11g">How does AUTO_SAMPLE_SIZE work in Oracle Database 11g</a></li>
<li>Oracle Optimizer Blog: <a href="https://blogs.oracle.com/optimizer/understanding-dbmsstatssetprefs-procedures">Understanding DBMS_STATS.SET_*_PREFS procedures</a> </li>
<li>PeopleSoft DBA Blog: <a href="http://blog.psftdba.com/2008/01/oraclepeoplesoft-have-mixed-up-ddl.html">Oracle/PeopleSoft have mixed up DDL Models used by %UpdateStats from PeopleTools 8.48</a> </li>
<li>PeopleBooks PT8.56: <a href="https://docs.oracle.com/cd/E15645_01/pt850pbr0/eng/psbooks/tape/chapter.htm?File=tape/htm/tape06.htm">Development Tools -> Application Engine -> Using Application Engine MetaSQL -> %UpdateStats</a></li>
</ul>
</div>
<div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/08139761793598085235noreply@blogger.com0tag:blogger.com,1999:blog-25740336.post-44281072379222419872018-06-29T12:05:00.002+01:002020-09-23T20:54:20.894+01:00Managing Cost-Based Optimizer Statistics for PeopleSoft<a href="http://www2.go-faster.co.uk/pres/CBO_Stats_PSFT.pdf" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;" target="_blank"><img alt="" border="0" data-original-height="720" data-original-width="1280" height="180" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhj7YOM3yZmsXNrZyMGUgkB1RtYoWfMZ6gp6IeOAUlxZ3jLmI45GCBsZnO8ievUxyyhLz-WyZOK0vxI646zskC0IlBFh-is2rPRCdvorobZX52-kg2plwBnp5KSwq35zikELAHnKg/s320/CBO_Stats_PSFT.20180619.jpg" title="" width="320"></a>I gave this <a href="http://www2.go-faster.co.uk/pres/CBO_Stats_PSFT.pdf" target="_blank">presentation</a> to <a href="http://www.ukoug.org/2018-events/ukoug-peoplesoft-roadshow-2018/home/" target="_blank">UKOUG PeopleSoft Roadshow 2018</a><br />
<br />
PeopleSoft presents some special challenges when it comes to collecting and maintaining the object statistics used by the cost-based optimizer.<br />
<br />
I have previously <a href="http://www2.go-faster.co.uk/docs.htm#Managing.Statistics.11g" target="_blank">written</a> and <a href="http://blog.psftdba.com/2012/09/maintaining-optimizer-statistics-on.html" target="_blank">blogged</a> on this subject. This presentation focuses exclusively on the Oracle database and draws together the various concepts into a single consistent picture. It makes clear recommendations for Oracle 12c that will help you work with the cost-based optimizer, rather than continually fight against it.<br />
<br />
It looks at collecting statistics for permanent and temporary working storage tables and considers some other factors that can affect optimizer statistics.<br />
<br />
This presentation also discusses <a href="https://support.oracle.com/knowledge/PeopleSoft%20Enterprise/1322888_1.html" target="_blank">PSCBO_STATS</a>, that is going to be shipped with PeopleTools, and compares and contrasts it with <a href="https://github.com/davidkurtz/gfcpsstats" target="_blank">GFCPSSTATS11</a>.<br />
<br /><div class="blogger-post-footer"><a href="http://www.go-faster.co.uk/">©David Kurtz</a></div>David Kurtzhttp://www.blogger.com/profile/00468908370233805717noreply@blogger.com0