Asia Presentations

SQL tuning is one of the essential and effective methods in improving system performance. Payroll calculation is sometimes a challenge for companies requiring to process large amount of transactions with complicated business logic. A fast responding and efficient payroll system is crucial for business continuity.
The University of Hong Kong has been using PeopleSoft HCM since 2011, which supports the core HR and payroll processes. With the increase of data volume and complexity over time, the payroll processing time has increased significantly and become unpredictable. After a thorough study and investigation of the payroll processes, SQL tuning was found to be one of the most useful means in speeding up and stabilizing the payroll calculation process without affecting the calculation result. The tuning techniques may be applied to other enterprise Oracle applications as well.
In this session, the steps to analyze the performance issues will be described and the practical skills of SQL tuning will be illustrated through a case study in improving the system performance of the payroll calculation of the University of Hong Kong. The following steps will be discussed:
-Maintaining database statistics
-Identifying and analyzing problematic SQLs
-Analyzing and choosing suitable execution plans
-Replicating issues and testing in development environment
-Verifying the post-tuning calculation results
-Reviewing outcome in production environment after changes applied