Solution : https://service.sap.com/sap/support/notes/853576 (SAP Service marketplace login required)
Summary :
The SAP Note outlines necessary assessments of workload, space requirements, and performance optimizations for Oracle Database Version 10g and higher using Oracle's latest features. Key tools include ASH for historical session analysis, AWR for performance data collection, and ADDM for tuning recommendations. Segment Advisor and SQL Advisors further aid in optimizing SQL statements and segment space usage. Licensing for Oracle Tuning and Diagnostics Packs is required. Practical application involves queries on SYSAUX tablespace and generating ASH and AWR reports from predefined scripts within Oracle's directory. This assists in identifying and mitigating issues through temporal analysis and SQL optimization.
Key words :
oracle 10g database performance tuning guide-> 5 automatic performance statistics-> automatic workload repositoryoracle 10g database performance tuning guide-> 6 automatic performance diagnostics-> automatic database diagnostic monitor solution 1, information buffer busy wait statistics enqueue statistics latch statistics segment statistics profile parameters, database performance data automatic database diagnostic monitor, oracle sessions automatic workload repository, oracle database version 10g, buffer busy waits dba_hist_enqueue_stats, historical monitoring data ash shows, oracle diagnostics pack, database performance, system-wide statistical information dba_hist_active_sess_history
Related Notes :
| 1462435 | Composite SAP note: Variations between ASH and AWR |
| 927813 | Oracle 10g: Using Oracle Segment Advisor to optimize space |
| 766349 | FAQ: Oracle SQL optimization |
| 740897 | Info about the Oracle license scope; Required Oracle options |
| 717484 | FAQs: Oracle statspack |
| 619188 | FAQ: Oracle wait events |
| 618868 | FAQ: Oracle performance |