Category: Peoplesoft

Categories
Java Languages Peoplesoft PeopleTools Tuning Weblogic

Weblogic Java VM Memory Parameters

When increasing the Java VM memory parameters for a PeopleSoft system under Weblogic (either in setEnv.cmd or manually by editing the cmdline registry entry if that’s your “thing”), be very careful not to increase the -XX:MaxPermSize memory allocation too much.

In fact I would leave it at 128MB or perhaps 256MB. Whatever you do, don’t update all three parameters in step, especially on a 32-bit Java, as you will hit Out of Memory errors on Weblogic startup way earlier than you expect. The MaxPermSize memory is allocated outside of the heap so the total memory you potentially need is more than you expect.

Categories
Peoplesoft PeopleTools

PeopleTools 8.55 to 8.55.01

This is a fairly large patch – I count 475 bugs in the README. Applying the patch through Change Assistant proved problem free. Some manual DataMover steps are required.

Categories
Peoplesoft PeopleTools

PeopleTools 8.55 is Available

As of Friday 4th December 2015, Oracle have released PeopleTools 8.55. A few noteworthy changes that look interesting are shown below.

  • Master-Detail Component type – looks very interesting and has many use-cases I think.
  • Ability of CI to retain state to improve performance in MAP (Mobile Application Platform) applications.
  • Find Definition References – At last it does Application classes and methods!
  • Line numbers in PeopleCode editor.
  • Improved Auto-complete in the PeopleCode editor.
  • Charting improvements – Series class, ToolTipLabelClass (nice!), Rating Gauge charts, Spark charts.
  • Sybase and Informix support is no more!
  • Back button improvement – takes you back to the search results. Breadcrumbs are gone for navigation.
  • Fluid Activity Guides and Dashboards. Looks nice and needed to allow PeoplSoft apps to compete with SAS UI’s like Workday.
  • Various branding and look and feel enhancements.
  • CORS authorized sites list on Web Profile.
  • Log Analyzer for Application Engine – looks like my perl script(s) may be redundant!
  • Additional locales (17) and currencies (8).
  • Log Correlation adds fields to web server and application server logs to make it easier to link related entries. Previously only the timestamp could be used and that depended on exact time synchronization across servers.
  • You can create a PIA from PSAdmin. Useful for scripted builds of VMs.
  • Support for WITH and MERGE in SQL Access Manager. I like WITH but am wary of MERGE – I have seen some horrible things attempted with it.
  • Materialized views for SQL server and DB2 z/OS. At last!
  • Support for Oracle 12c In-Memory tables/columns.
  • AccessId and ConnectId extended to 32 characters.
  • SHA-256/4096 key size.
  • New “My Preferences Framework” to replace “My Personalizations”
  • PeopleTools Health Center gives you a Nagios-like system health dashboard. Part of PPM. Includes log access/browsing from the web browser. Hmmm … can you search across the logs using (say) a RegEx?
  • Lots of reporting changes to Pivot Grids, BI Publisher and PeopleSoft Query/Connected Query. COUNT DISTINCT is a new aggregate option – long overdue.
  • Some nice PTF changes like allowing sign-on thru branded/customized logon page and multiple browser support (but sadly only for playback – recording tests still needs IE).
  • Application Designer Upgrade now tracks cloning of and changes to permission lists, roles and menus. This information is used when applying PUM updates to also apply them to customized versions. Cool.

Note this is not a complete list – refer to the PeopleTools 8.55 New Features Overview PDF for full details.

Categories
CRM Peoplesoft Performance Tuning

RC_BACKLOG_VW in PeopleSoft CRM 9.1

Seriously? Who thought it would be a good idea to create a view to return the DISTINCT list of BUSINESS_UNIT from RC_CASE? Talk about an O (n) performance disaster.

What’s wrong with a SELECT BUSINESS_UNIT FROM PS_BUS_UNIT_TBL_RC WHERE EXISTS (… case sub-query….).

Oh look … Two index seeks … shocker! It scales too …. wow … well I never.

Duh.

Categories
DataMover Peoplesoft PeopleTools Performance

DataMover EXPORT Performance

Some interesting statistics from some recent tests:

  • PeopleTools 8.49.28 EXPORT of a table containing 1.6 million rows (approx 1.6 GB of data): 3 hours 15 minutes
  • PeopleTools 8.53.20 IMPORT of the same data: 34 minutes
  • PeopleTools 8.53.20 EXPORT of the same table containing the identical 1.6 million rows (approx 1.6 GB of data): 24 minutes

Some background info that may be relevant:

  • 8.49 system is non-Unicode and uses CHAR()
  • 8.53 system is Unicode and uses VARCHAR() i.e. NVARCHAR()
  • The EXPORTS were done on identical VM’s in the data centre. The IMPORT was actually done “over the wire” using a drive mapping and via a client PC – I would expect much faster with a local input file and running in the data centre.

The speed difference between 8.49 and 8.53 in terms of EXPORT needs further investigation.

Categories
Peoplesoft PeopleTools Performance SQL Server Tools Tuning Utilities

_WA_Sys Stats or Hand Crafted?

SQL Server generates histogram statistics automatically whenever a column is referenced in a predicate and it is not already the leading column in an existing histogram statistic. By design, indexes create histogram statistics automatically so any column you have that is the leading column in an index will already have histogram statistics.

The autogenerated statistics are prefixed _WA_Sys_ followed by:

  • the column name (in older SQL Server versions) or the column number in hex (for SQL Server 2008 onwards) e.g. EMPLID or 00000001
  • the object_id of the table in hex e.g. 57490C28

separated by an underscore. So auto-generated statistics look like this:

  • _WA_Sys_DESCR_57490C28 or _WA_Sys_00000013_57490C28

The issue for me as a PeopleSoft Administrator/DBA is whether I should keep these statistics or recreate them as “user” statistics with more sensible names. Oddly enough the “old style” naming convention which included the column name was actually better than the “hex” version we have now. After all what is column 00000014 (decimal 20) in table X?

My personal preference is to process the system generated statistics and recreate them as statistic “column name” on table X. I have a perl script that generates the relevant DROP/CREATE STATISTICS commands for an entire PeopleSoft database. The output looks like this:

--- Remove statistic _WA_Sys_AUDITRECNAME_5921A398 for column AUDITRECNAME on table PSRECDEFN
 
DROP STATISTICS [dbo].[PSRECDEFN].[_WA_Sys_AUDITRECNAME_5921A398];
 
--- Create User Statistic for column AUDITRECNAME (+ 1 keys) on table PSRECDEFN
 
CREATE STATISTICS [AUDITRECNAME] ON [dbo].[PSRECDEFN] ([AUDITRECNAME],[RECNAME]) WITH FULLSCAN;

Why do I like to do this? Well there are a number of reasons:

  1. The naming convention is clearer to me – open the statistics tree within a table in object explorer and you get a list of column names plus the statistics created for any indexes.
  2. Once I have changed to “user” statistics I can query for any new _WA_Sys_ statistics (yes I leave auto-generate switched on). This shows me any “new” query activity that accesses via previously unused predicate columns. This can help in deciding if perhaps new indexes are required.
  3. I can ensure the existing statistic are recreated using a “WITH FULLSCAN” not just sampled data. For many PeopleSoft systems this is essential – data can often be very skewed and sampling can miss this on occasion.
  4. I like to add in the “key” columns of the table to the histogram to provide better density information for the optimizer.  This can be essential information when intermediate key fields have a single value e.g. EMPL_RCD in HR is typically 0 in most cases.

If you would like to discuss this in more detail, leave a comment.

Categories
Hints and Tips Peoplesoft PeopleTools Process Scheduler

Process Scheduler and nVision – UseExcelAutomation!

Remember that to get correct formatting in server generated nVision reports you need to ensure the process scheduler config (psprcs.cfg) specifies UseExcelAutomation=1 in PeopleTools 8.5 versions. Without this setting, the output file will be OpenXML format – that will not run the nVision macros. As a result you will see the correct query data in the Excel output but it will lack most formatting.

Ref: Doc ID 1317246.1 on Oracle support details this, but the support document confusingly mentions 8.51 the in title even though the issue applies through to 8.53.

Categories
HRMS Peoplesoft Performance SQL Tuning

HOLIDAY_SCHEDULE SQL

If you come across this as one of your top (if not THE top) SQLs in your HR system:

SELECT HOLIDAY_SCHEDULE, HOLIDAY, 
(CONVERT(CHAR(10),HOLIDAY,121)), DESCR 
 FROM PS_HOLIDAY_DATE 
WHERE HOLIDAY_SCHEDULE=@P1 
  AND HOLIDAY=@P2 
ORDER BY HOLIDAY_SCHEDULE, HOLIDAY

then look no further than the ABSW_WRK page which is a hidden page on most absence related components. There is a scroll showing the holiday dates. Unfortunately, some developer decided to put a related display in the grid, resulting in “n” executes of the above SQL – where “n” is the number of rows you have in PS_HOLIDAY_DATE for the given employees’s HOLIDAY_SCHEDULE.

In the case I came across there were some 200+ rows in the table and as a result after just 3 working days the above SQL had been executed 21 million times. This actually makes this more frequent than the PSVERSION check!

Personally, I’d remove the related display field from the scroll completely. It isn’t referenced nor visible so why take the overhead of 200+ SQL lookups?

Update: Further analysis revealed an issue with a cartesian product on a customized scroll based on the standard ABSW_WRK page. That was causing the majority of the 21 million lookups by filling the scroll with “n” times the 200+ rows in the holiday table. But my point is still very valid – why lookup something you never show the users? If you really need it – put it in the view used in the scroll so it comes “for free” with the population of the grid.

Categories
Peoplesoft PeopleTools Performance Tuning Upgrade

PT 8.51.25 SYS DB Sizing – NCHAR() vs NVARCHAR()

Some quick statistics from a PeopleTools 8.51.25 SYS database on SQL Server:

  • Unicode with CHAR (i.e. NCHAR fields)       – DB size 1166 MB
  • Unicode with VARCHAR (i.e. NVARCHAR fields) – DB size 363 MB

That is almost a 70% reduction. Probably not that surprising given the number of character fields, but what is more interesting is the significant improvement in performance due to the increased number of rows per page for many tables.

An extreme example of this can be seen in PSAUDIT – using NCHAR this is close to 1200 characters wide due to 15 NCHAR(65) KEYn columns. Typically, the vast majority of the KEYn columns are empty since it is relatively rare for audited tables to have more than 5 key fields. Using NVARCHAR I have seen an average row width of 150 characters – meaning that a page holds 6 times as many rows on average.

Note: A value of 4 in the DATABASE_OPTIONS column of PSSTATUS will enable VARCHARs on SQL Server.

Categories
Peoplecode Peoplesoft PeopleTools

PeopleCode Techniques 1

Here is some sample code RowInit PeopleCode:

Declare Function BuildHTMLString PeopleCode MY_FUNCLIB.MY_FIELD FieldFormula;

BuildHTMLString(GetField(WORK_RECORD.MY_TEXTFIELD), …. some other parameters);

The underlying Function is defined as you might expect:

 

BuildHTMLString

On the surface there is not a lot wrong with this code but it contains a technique that can hurt performance – passing around and using object references when you really don’t need to. This technique comes from the OO programming world and is absolutely valid but all this function is doing is building a string value. Changing the code so the Function RETURNS a string and using simple assignment in the RowInit logic is another option:

BuildHTMLStringReturnString

But is this really faster? And by how much? In my benchmarking of 10000 rows, the first version took some 14 seconds elapsed time to populate. The string assignment version took under 2 seconds. Now this was on a laptop of limited resources, but I think this illustrates a valid point – think about the cost of passing objects around and consider the impact on performance in medium to high volume situations. Anything that releases the app server to do other work quicker is good in my view.

Of course building the string in the underlying SQL is an even better option from a performance perspective – but only if all of the logic in the function can be coded in SQL. Sometimes that just isn’t feasible and PeopleCode is the best way to go.