Home / Online Oracle DBA Performance tuning Training – Database administration

Online Oracle DBA Performance tuning Training – Database administration

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

Oracle DBA Online Training

Latest Oracle Downloads


Your information will never be shared
Sign-up for exclusive content. Be the first to hear about DBA Centre Blogs.