Latest for the folks who have to deal with Peoplesoft
Dang, been a while since the last posts! A lot of water under the bridge since then.
We've ditched a few people that were not really helping anything, and are now actively looking at cloud solutions, "big data" use, etcetc.
Meanwhile, there is the small detail that business as usual has to continue: it's very easy to parrot about the latest gimmick/feature/funtastic technology that will revolutionize how to boil water.
But if the monthly payroll and invoicing and purchasing fail, I can promise you a few HARD and concrete financial surprises!
Speaking of payroll...
A few years ago I made some posts here on the problem with PAYCALC runs in Peoplesoft and how hard it is to have correct CBO statistics for the multitude of scratchpad tables this software uses to store intermediate results during a pay calculation run - be it daily, weekly or monthly. Those posts are regularly visited and comments added to them by folks experiencing the same problem.
(Fancy that! A problem that is common to others and not caused by yours truly - the "bad dba", according to marketeers from so many companies hellbent on selling us con-sultancy!)
This lack of statistics is mostly caused by the fact that the software truncates the tables at the end of the pay calc process. And does not calculate statistics after re-populating them with intermediate results during the next pay cycle!
With the obvious result that no matter how often and regularly a dba recalculates statistics on these tables, they cannot possibly ever be correct at the most important time!
One way around this I mentioned at the time was to actually turn on debugging for the modules that use those tables - assuming it's easy to find them, not all folks know how to do that!
The end result is Peoplesoft code will detect the debug flag and fire off a ANALYZE just after re-populating those tables. Not perfect ( analyze if anything is a deprecated command!) but better than no stats! At the end of the run, the output of the debug is simply thrown away. Slow, but much better than before!
Another way was to force estimates to be taken for those tables by the CBO. Again, not perfect. But still better than pay calc runtimes in the tens of hours!
Enter our upgrade last year to Peoplesoft HCM and EPM 9.2! Apart from using the "fusion" architecture for screen handling (slow as molasses...) this release introduces a CAPITAL improvement to all pay calc runs!
What Oracle has done is very simple: from 9.2 onwards, they do NOT truncate the scratchpad tables at the end of each pay calc!
So instead of guessing/analyzing stats for empty tables, the dbas can now analyze them with significant, relevant and timely data contents, do histograms if that is their penchant, etcetcetc! With the immediate result that instead of the previous pot luck runtime for the paycalcs, now they are reliably constant in their duration and much, much faster! In simple terms: the CBO finally has SOMETHING accurate to base its "cost" on!
Man! You mean to tell me it took Peoplesoft to become available in Oracle cloud for them to realize what was being done before was eminently STUPID and making it impossible for any dba to have a predictable paycalc execution time?
Apparently when it became their cloud responsibility, the solution was quickly found and implemented...
Of course, if they had actually LISTENED to the frequent and insistent complaints by dbas who actually deal with their software day to day for years on end rather than playing "ace" cards, things would have perhaps progressed a lot faster!...
But let's ignore stupidity and rejoice: there is a light at the end of the tunnel and it's not incoming!
Get those upgrade juices going, folks, and try to convince your management that 9.2 is THE release of Peoplesoft to aim at.
It'll help everyone. (Including Oracle - but I don't think their marketing is smart enough to detect that...)
Catchyalata, folks. Do me a favour: smile and have fun!
We've ditched a few people that were not really helping anything, and are now actively looking at cloud solutions, "big data" use, etcetc.
Meanwhile, there is the small detail that business as usual has to continue: it's very easy to parrot about the latest gimmick/feature/funtastic technology that will revolutionize how to boil water.
But if the monthly payroll and invoicing and purchasing fail, I can promise you a few HARD and concrete financial surprises!
Speaking of payroll...
A few years ago I made some posts here on the problem with PAYCALC runs in Peoplesoft and how hard it is to have correct CBO statistics for the multitude of scratchpad tables this software uses to store intermediate results during a pay calculation run - be it daily, weekly or monthly. Those posts are regularly visited and comments added to them by folks experiencing the same problem.
(Fancy that! A problem that is common to others and not caused by yours truly - the "bad dba", according to marketeers from so many companies hellbent on selling us con-sultancy!)
This lack of statistics is mostly caused by the fact that the software truncates the tables at the end of the pay calc process. And does not calculate statistics after re-populating them with intermediate results during the next pay cycle!
With the obvious result that no matter how often and regularly a dba recalculates statistics on these tables, they cannot possibly ever be correct at the most important time!
One way around this I mentioned at the time was to actually turn on debugging for the modules that use those tables - assuming it's easy to find them, not all folks know how to do that!
The end result is Peoplesoft code will detect the debug flag and fire off a ANALYZE just after re-populating those tables. Not perfect ( analyze if anything is a deprecated command!) but better than no stats! At the end of the run, the output of the debug is simply thrown away. Slow, but much better than before!
Another way was to force estimates to be taken for those tables by the CBO. Again, not perfect. But still better than pay calc runtimes in the tens of hours!
Enter our upgrade last year to Peoplesoft HCM and EPM 9.2! Apart from using the "fusion" architecture for screen handling (slow as molasses...) this release introduces a CAPITAL improvement to all pay calc runs!
What Oracle has done is very simple: from 9.2 onwards, they do NOT truncate the scratchpad tables at the end of each pay calc!
So instead of guessing/analyzing stats for empty tables, the dbas can now analyze them with significant, relevant and timely data contents, do histograms if that is their penchant, etcetcetc! With the immediate result that instead of the previous pot luck runtime for the paycalcs, now they are reliably constant in their duration and much, much faster! In simple terms: the CBO finally has SOMETHING accurate to base its "cost" on!
Man! You mean to tell me it took Peoplesoft to become available in Oracle cloud for them to realize what was being done before was eminently STUPID and making it impossible for any dba to have a predictable paycalc execution time?
Apparently when it became their cloud responsibility, the solution was quickly found and implemented...
Of course, if they had actually LISTENED to the frequent and insistent complaints by dbas who actually deal with their software day to day for years on end rather than playing "ace" cards, things would have perhaps progressed a lot faster!...
But let's ignore stupidity and rejoice: there is a light at the end of the tunnel and it's not incoming!
Get those upgrade juices going, folks, and try to convince your management that 9.2 is THE release of Peoplesoft to aim at.
It'll help everyone. (Including Oracle - but I don't think their marketing is smart enough to detect that...)
Catchyalata, folks. Do me a favour: smile and have fun!
posted by Noons at 3/04/2014 10:51:00 pm
2 Comments:
We upgraded to 9.2 in October 2014 and are seeing various paycalc times between environments and in the same environment. Anything from 12 mintues to 70 minutes for around 6000 checks. We would expect this time to be around 16 minutes consistently.
We are also on 11.2.0.3 for the database (patched up to the 2014 PSU) AND - most importantly! - bind variable peeking for the optimizer is off in our systems.
Ie: alter system set _optim_peek_user_binds=false scope=both;
Without that I've found things could still go South every once in a while. That "amazing feature" has proved to be absolutely disastrous in the vast majority of cases...
As always: don't just assume this is a silver bullet. Test before making any changes!
Post a Comment
<< Home