System Performance

Over my career, I have focused on Oracle Performance and System Performance in general. I worked at Hotsos which was one of the best companies in the world on the topic of Oracle performance. Hotsos pioneered a product around Workload Characterization which was a mainframe approach and Method-R pioneered by Cary Millsap.

At Hotsos, I came up with this approach for System Performance and hope this helps.

Workload Characterization:

Workload characterization is the process of logging and tracking work done in the database by users and its associated computing costs. This data than can be studied to understand the nature of the workload and its overall impact.

Workload can be handled in one of five ways.
Eliminate – Clearly the easiest option to pursue due to the minimal effort involved and simple procedures needed to accomplish this step. We estimate that 25-50% of workload in any system is waste and can be easily eliminated and processes put in place to avoid the recurrence of the workload

Tune – If the workload is a useful workload (i.e. work is accomplished by the user), we can then assess if it needs to be tuned by using Method R. This usually involves tuning of SQL statements to consume less Logical I/O’s and computing resources like parsing etc. If the resource consumption of computing is still high, an assessment needs to be made on whether the complexity of the business requirement is worth the cost of the computing necessary to accomplish this workload. This is a hard business question and needs to be answered keeping all the stakeholders interest in mind.

Train – We find that well tuned programs can suffer from runaway workload by improper desktop procedures or user not trained to use the functionality of the software to efficiently accomplish their work. This is true in Order Management where a blind search for Orders and Customers can potentially take down the database with its demand of resources.

Reschedule – Sometimes, large batch workload runs during peak hours creating a competition for resources and slows all the workload on the system. In this case, this workload will need to be rescheduled to a non-peak time. This can be difficult in global system where there are no non-peak windows except for a small time on the weekend.

Relocate – Ad-Hoc Reports and Batch reports can be potentially made to run on a copy of the production database thereby offloading the workload demand off to the reporting database. This can significantly reduce the competition for resources and improve overall workload throughput.


The order in which to approach it economically is

1.) Eliminate

2.) Train

3.) Re-schedule

4.) Tune/Optimize Code

5.) Re-locate


Thanks
Mahesh