DBA CENTRE Blog for Database Administrators & Cloud Admins Oracle DBA Performance Tuning
1) Introduction • Utilize database advisors to proactively tune an Oracle Database • Use the tools based on Automatic Workload Repository (AWR) to tune Database • Diagnose and tune common SQL-related performance problems • Diagnose and tune common instance-related performance problems • Use the Enterprise Manager performance-related pages to monitor Database | 2) Basic Tuning Diagnostics • View the top wait events to determine the highest wait • View the time model to diagnose performance issues • Use dynamic performance views to view statistics and wait events • Use Enterprise Manager Monitoring • Identify the key tuning components of the alert logs • Identify the key tuning components of user trace files |
3) Using Automatic Workload Repository • Create and manage AWR snapshots • Generate AWR reports • Create Compare Periods reports | 4) Defining Problems • Identify performance issues • Set tuning priorities • Interpret tuning diagnostics • Tune for life-cycle phase |
5) Using Metrics and Alerts • View metrics by using the metrics history views • Create metric thresholds • View alerts | 6) Using Baselines • Create AWR baselines • Enable adaptive thresholds • Create AWR baselines for future time periods |
7) Using AWR-Based Tools • Describe tuning automatic maintenance tasks • Generate ADDM reports • Generate Active Session History (ASH) reports | 8) Monitoring Applications • Configure and manage services • Use services with client applications • Use services with the Database Resource Manager • Use services with the Scheduler • Set performance-metric thresholds on services • Configure services aggregation and tracing |
9) Identifying Problem SQL Statements • Describe SQL statement processing • Describe the role of the optimizer • View the SQL statement statistics • Identify the SQL statements that perform poorly • Generate and view an execution plan • Generate a tkprof report • Generate an optimizer trace | 10) Influencing the Optimizer • Describe the optimizer’s behaviour • Explain how statistics can affect the optimizer • Describe how data structures affect the optimizer • Adjust parameters to influence the optimizer |
11) Reducing the Cost • Adjust data structures to influence the optimizer • Tune segment space management • Use Segment Advisor • Convert to Automatic Segment Space Management • Tune block space management • Diagnose and correct row migration • Diagnose table fragmentation • Use table compression | 12) Using SQL Performance Analyzer • Identify the benefits of using SQL Performance Analyzer • Describe the SQL Performance Analyzer workflow phases • Use SQL Performance Analyzer to ascertain performance gains in database change • Use SQL Performance Analyzer to test the impact of proposed changes |
13) SQL Performance Management • Manage changes to optimizer statistics • Capture SQL profiles • Use SQL Access Advisor • Set up SQL Plan Management • Set up various SQL Plan Management scenarios | 14) Tuning the Shared Pool • Diagnose and resolve hard-parsing problem • Diagnose and resolve soft-parsing problem • Size the shared pool • Diagnose and resolve shared pool fragmentation • Keep objects in the shared pool • Size the reserved area • Manage the results cache |
15) Tuning the Buffer Cache • Describe the buffer cache architecture • Size the buffer cache • Resolve common performance issues related to the buffer cache • Use common diagnostic indicators to suggest a possible solution | 16) Tuning PGA and Temporary Space • Diagnose PGA memory issues • Size the PGA memory • Diagnose temporary space issues • Specify temporary tablespace parameters for efficient operation |
17) Automatic Memory Management • Use memory advisors to size dynamic memory areas • Enable Automatic Shared Memory Manager • Enable Enterprise Manager memory parameters • Set minimum size of auto-tuned SGA components • Use the SGA advisor to set SGA_TARGET • Enable Automatic Memory Management • Use the Memory Advisor to set overall memory parameters | 18) Tuning I/O • Diagnose database I/O issues • Describe the Stripe and Mirror Everything (SAME) concept • Set filesystemio_options • Choose appropriate I/O solutions • Tune Automatic Storage Management (ASM) |
