One of the new features of PeopleTools 8.54 is the support of Oracle Database Materialized Views. In a nutshell, Materialized Views can be seen as a snapshot of a given view. When you query a Materialized View, the data is not necessarily accessed online, but instead it is retrieved from the latest snapshot. This can greatly contribute to improve query performance, particularly for complex SQLs or Pivot Grids.
Apart from the performance benefits associated with them, one of the most interesting features of Materialized Views is how the data refresh is handled. Oracle Database supports two ways of refreshing data:
In case you choose the On Demand method, the data refresh can actually be done following two different methods:
Before PeopleTools 8.54, Materialized Views could be used as an Oracle Database feature, but the DBA would need to be responsible of editing the Application Designer build scripts to include the specific syntax for this kind of views. On top of that, the DBA would need to schedule the data refresh directly from the database.
PeopleTools 8.54 introduces support within PeopleSoft tools. In first place, Application Designer will now show new options for View records:
We have already seen what Refresh Mode and Refresh Method mean. The Build Options indicate to Application Designer whether the Materialized View date needs to be calculated upon its build is executed or if it could be delayed until the first refresh is requested from the Materialized View Maintenance page.
This page is used to determine when to refresh the Materialized Views. The refresh can be executed for multiple views at once and scheduled using the usual PeopleTools Process Scheduler recurrence features. Alternatively, the Refresh Interval [seconds] may be used to indicate the database that this view needs to be refreshed every n seconds.
The main disadvantage of using Materialized Views is that they are specific to Oracle Database. They will not work if you are using any other platform, in which case the view acts like a normal view, which keeps a similar functional behaviour, but without all the performance advantages of Materialized Views.
All in all, Materialized Views provide a very interesting feature to improve the system performance, while keeping the information reasonably up to date. Personally, I wish I've had this feature available for many of the reports I've built in all these years... :-)
Materialized Views Features
Apart from the performance benefits associated with them, one of the most interesting features of Materialized Views is how the data refresh is handled. Oracle Database supports two ways of refreshing data:
- On Commit: data is refreshed whenever a commit takes place in any of the underlying tables. In a way, this method is equivalent to maintaining through triggers a staging table (the Materialized View) whenever the source table changes, but all this complexity is hidden from the developer. Unfortunately, this method is only available with join-based or single table aggregate views.
Although it has the benefit of almost retrieving online information, normally you would use the On Commit for views based on tables that do not change very often. As every time a commit is made, the information is refreshed in the Materialized View, the insert, update and delete performance on the source tables will be affected.
Hint: You would normally use On Commit method for views based on Control tables, not Transactional tables.
- On Demand: data is refreshed on demand. This option is valid for all types of views, and implies that the Materialized View data is only refreshed when requested by the administrator. PeopleTools 8.54 include a page named Materialized View Maintenance where the on demand refreshes can be configured to be run periodically.
In case you choose the On Demand method, the data refresh can actually be done following two different methods:
- Fast, which just refreshed the rows in the Materialized View affected by the changes made to the source records.
- Full, which fully recalculated the Materialized View contents. This method is preferable when large volume changes between refreshes are usually performed against the source records. Also, this option is required after certain types of updates on the source records (ie: INSERT statements using the APPEND hint). Finally, this method is required when one of the source records is also a Materialized View and has been refreshed using the Full method.
How can we use them in PeopleTools?
Before PeopleTools 8.54, Materialized Views could be used as an Oracle Database feature, but the DBA would need to be responsible of editing the Application Designer build scripts to include the specific syntax for this kind of views. On top of that, the DBA would need to schedule the data refresh directly from the database.
PeopleTools 8.54 introduces support within PeopleSoft tools. In first place, Application Designer will now show new options for View records:
We have already seen what Refresh Mode and Refresh Method mean. The Build Options indicate to Application Designer whether the Materialized View date needs to be calculated upon its build is executed or if it could be delayed until the first refresh is requested from the Materialized View Maintenance page.
This page is used to determine when to refresh the Materialized Views. The refresh can be executed for multiple views at once and scheduled using the usual PeopleTools Process Scheduler recurrence features. Alternatively, the Refresh Interval [seconds] may be used to indicate the database that this view needs to be refreshed every n seconds.
Limitations
The main disadvantage of using Materialized Views is that they are specific to Oracle Database. They will not work if you are using any other platform, in which case the view acts like a normal view, which keeps a similar functional behaviour, but without all the performance advantages of Materialized Views.
Conclusions
All in all, Materialized Views provide a very interesting feature to improve the system performance, while keeping the information reasonably up to date. Personally, I wish I've had this feature available for many of the reports I've built in all these years... :-)
6 comments:
Hi Javier , how have you been?
Nice write up regarding the new materialized view feature.
By any chance do you have a tools upgrade experience ? I had some questions about the new PeopleTools 8.54 upgrade. Your help would be highly appreciated. :)
Hi Nikhil
Yes, sure. We have done quite a few PeopleTools upgrades already ;), including to release 8.54. Let me know if you have any question or need.
Cheers!
Hi Javier , I am noew to PeopleSoft and trying to inplace upgrade from 8.53.11 to PT 8.54.06, Will it be possible for you to share upgrade steps, I've read the documents from Oracle support but still not sure. our help would be highly appreciated.
Thanks
Sorry, but I've just had time to check this notification. The PeopleTools upgrade is a somewhat complex process to describe. If you look at the documentation, it covers a couple of hundred pages :).
Probably it would be easier if you tell me where you are stuck, and we can start from there. Does it sound a good idea?
Thanks
How many days will it take to upgrade from 8.53 to 8.54
How many resources do we require? Only psadmin/dba is sufficient to complete this upgrade?
Hi Ram
Well, it depends on several factors, ranging from the platform to the current architecture. Typically, you should be able to conduct the upgrade on the non-Production environments in around 5 days, but then you should allocate some time for testing (this really depends on the complexity of the business processes you have implemented at your site).
The Production upgrade should normally be completed in less than a day, but again, it depends on the architecture and if you need special steps such as database or operating system upgrade.
I hope this helps.
Post a Comment