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) |
