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
Hints and Tips SQL SQL Server

GETDATE()-1

Ever noticed some “odd” predicates in execution plans using GETDATE()-n where “n” is the number of days? Things like:

SOME_DATETIME_FIELD >= GETDATE()-‘1900-01-02 00:00:00.000’

It may look a bit odd but it does make sense where you think about the data type precedence rules in SQL Server. In this case, as GETDATE() is a DATETIME, there is an implicit conversion of the “n” into a DATETIME. So, if we run:

SELECT CAST(1 AS DATETIME)

what do we get? ‘1900-01-02 00:00:00.000’

And yes, “2” gives ‘1900-01-03 00:00:00.000’ as you might expect.

As the subtraction of the two dates is actually just using decimal subtraction, the GETDATE()-1 does work to give you “yesterday”. It’s just not quite as clear (in my view) as using:

DATEADD(day,-1,GETDATE())

Categories
Hints and Tips Security

Cisco AnyConnect – Disable ICS

If you are having issues using Cisco AnyConnect on Windows and are getting errors such as:

“Connection attempt has failed”

or

“AnyConnect was not able to establish a connection to the specified secure gateway”

then be sure to confirm you have stopped or disabled the Windows Internet Connection Sharing (ICS) service.

ICS

Also, make 100% sure you haven’t shared the wireless device itself.

Related Post: Lenovo laptops have a HotSpot feature that requires ICS (see Lenovo Support Article).

Categories
App Engine Hints and Tips Oracle PeopleTools PSVERSION Tools VERSION

VERSION 3.0 Application Engine!

If you are running a pre-8.53 version of PeopleTools you will (hopefully) be aware of the issues and requirements of using the VERSION Application Engine to correct problems with object versioning in PeopleSoft.

To summarise, the older VERSION App Engine just resets all the counter to 1! This is known as “Classic Mode” in the later release of the VERSION App Engine and is to be avoided at all costs really. You need everyone out of PeopleSoft, have to take all Application and Process Schedulers down, clear cache and run the App Engine from the command line to be 100% safe using it. Ugh.

Oracle recommends downloading and installing the most current VERSION Application Engine on all systems running PeopleTools >= 8.44 up to the latest release. Why? Because the later release is safer and better in all respects.

It has three execution modes:

  • Report Only Mode – Prefix your Run Control Id with the string “REPORTONLY” and you will get a report of the problems you have (if any) with versioning.
  • Classic Mode – Prefix your Run Control Id with the string “RESETVERSIONS” and the App Engine will run the “old way” – with all of it’s limitations (servers down, everybody out, cache cleared and run from command line). DO NOT USE THIS!
  • Enhanced Mode – Use and other Run Control prefix and the App Engine will just fix the versions that are wrong. It won’t reset back to 1 – just make everything line up correctly. You can even run this via Process Scheduler and with the servers up. What’s not to like about that?

Oracle Support Document 611565.1 (E-AS: Instructions Regarding the Use of the VERSION Application Engine Program) can be found at: Oracle Support Document 611565.1