Whenever I diagnose and tune oracle performance issues I am filled with a deep sense of respect for the engineers who designed and implemented the diagnostic capabilities of the Oracle database.
Levels of performance management can be
Oracle is a complex product with a more complex memory and process architecture that a general server side application. This makes the performance management more challenging. On top of it characterization of workload of a general server application is simple while in Oracle DB even if you look at selects they can have so different resource and time foot print and can be of so many types. All this implies that the performance management of Oracle DB is very challenging. This makes the DB diagnostic solutions provided by Oracle even more admirable.
Oracle provides tuning parameters for each subsystem, be it checkpoint process, LWR process etc or the size of shared pool, Buffer cache, SGA, PGA etc. Oracle diagnostic data(AWR Reports) provides rich information to detect inefficiencies in any of the process or memory subsystems or hardware capacity. AWR report contains the top foreground waits which help in identifying which subsystem is becoming bottleneck and needs to be tuned or needs more capacity. The report identifies the high load SQLs that are candidates of tuning and optimization.
Oracle goes beyond just diagnostics to prescriptions of solutions also. ADDM can be used analyse the AWR data and it will provide actionable recommendations to optimize the system. SQL Tuning Advisor can be used get the tuning recommendations for SQL query.
Oracle has achieved all the above three levels(Monitoring, Diagnostics, and Prescription ) where a capable customer can do it all by himself without needing support from Oracle, greatly reducing the mean time to response for performance issues as well as providing rich information for proactive performance management
Hats off to the architects and engineers of Oracle Diagnostics !
In future posts we will discuss how to do root cause analysis of Oracle issues using AWR reports
Other Posts
Levels of performance management can be
- Descriptive
- What happened? ie Monitoring
- Diagnostic
- Why it happened?
- Prescriptive
- What to do?
Oracle is a complex product with a more complex memory and process architecture that a general server side application. This makes the performance management more challenging. On top of it characterization of workload of a general server application is simple while in Oracle DB even if you look at selects they can have so different resource and time foot print and can be of so many types. All this implies that the performance management of Oracle DB is very challenging. This makes the DB diagnostic solutions provided by Oracle even more admirable.
Oracle provides tuning parameters for each subsystem, be it checkpoint process, LWR process etc or the size of shared pool, Buffer cache, SGA, PGA etc. Oracle diagnostic data(AWR Reports) provides rich information to detect inefficiencies in any of the process or memory subsystems or hardware capacity. AWR report contains the top foreground waits which help in identifying which subsystem is becoming bottleneck and needs to be tuned or needs more capacity. The report identifies the high load SQLs that are candidates of tuning and optimization.
Oracle goes beyond just diagnostics to prescriptions of solutions also. ADDM can be used analyse the AWR data and it will provide actionable recommendations to optimize the system. SQL Tuning Advisor can be used get the tuning recommendations for SQL query.
Oracle has achieved all the above three levels(Monitoring, Diagnostics, and Prescription ) where a capable customer can do it all by himself without needing support from Oracle, greatly reducing the mean time to response for performance issues as well as providing rich information for proactive performance management
Hats off to the architects and engineers of Oracle Diagnostics !
In future posts we will discuss how to do root cause analysis of Oracle issues using AWR reports
Other Posts
- Capacity Planning: Working within operational capacity
- Software bottlenecks:- Understanding performance of software systems with examples from road traffic
- Analytical thinking in performance analysis
- Time scale of system latencies
No comments:
Post a Comment