Oracle 10G Performance Tuning for DBAs
Course
In Bath
Description
-
Type
Course
-
Location
Bath
-
Duration
3 Days
The Oracle RDBMS is typically deployed throughout a business, from the corporate mainframe, to enterprise UNIX servers, down to departmental level Intel platforms. For a database of any significant size, performance immediately becomes an issue. Users may resent a query taking 3.5h, for instance. Or on the other hand, an update locks a table for 30 minutes while it modifies. Suitable for: This course is designed for: Database administrators, Developers, Data warehouse support team members.
Facilities
Location
Start date
Start date
About this course
Attendees should be knowledgeable in Oracle DBA (preferably at 9i or 10G) to get the best out of this course.
Reviews
Course programme
PREREQUISITES: Attendees should be knowledgeable in Oracle DBA (preferably at 9i or 10G) to get the best out of this course.
DURATION: 3 days. Hands on.
OBJECTIVES: The Oracle RDBMS is typically deployed throughout a business, from the corporate mainframe, to enterprise UNIX servers, down to departmental level Intel platforms. For a database of any significant size, performance immediately becomes an issue. Users may resent a query taking 3.5h, for instance. Or on the other hand, an update locks a table for 30 minutes while it modifies data, which denies application access to the table(s) concerned.
There will always be a platform ceiling set by hardware and o/s kernel performance but wouldn''t it be nice to use the gap between what we are getting today, and what we could get from our platform investment. Learn to find out where this performance ceiling is and at what level the current system is running.
Essentially there are two main avenues for RDBMS optimisation. Strategies deployed by the DBA, such as cache management and DBWR and LGWR optimisation, is one of them. The other approach is to investigate how the SQL is running, to change either the SQL or its execution environment and see if that made a difference.
The subject of this course is the administration of the database.
The classical bottlenecks on an Oracle platform are memory, i/o, network and CPU. On a loaded system, one of these areas will cause a bottleneck. Which one is it? This is what we aim to show you how to find out. Remove the bottleneck and we hit another one. Learn how to measure the systems performance in several areas, how to stress test it and examine the effect of changes you may make.
COURSE CONTENT:
Overview of Oracle Performance Tuning
Job Roles in Tuning
Tuning phases
Tuning goals and Service Level Agreements
Common performance problems
Tuning Methodology
Diagnostic and Tuning Tools
Alert log file
Background process trace files
User trace files
Dictionary views providing statistics
Dynamic performance views
TIMED_STATISTICS parameter to collect statistics
Statistics Package
STATSPACK procedures
Database events
Sizing the Shared Pool
Overview of the shared pool
Library cache tuning
Reuse statements
Using Reserved Space
Keeping Large Objects
Related tuning issues
Data Dictionary Cache (DDC) Tuning
Sizing the Buffer Cache
Overview of tuning the buffer cache
Buffer Cache Sizing Parameters in Oracle9i/10G
Buffer Cache Advisory Parameter
Dynamically resizing SGA components
Granules of Allocation
Increase the size of a SGA component
Sizing other SGA Structures
Sizing the redo log buffer
Detecting contention
Resolving contention
Sizing the Java Pool
Monitoring Java Pool Memory
Sizing the SGA for Java
Sizing Java Pool Memory
Limiting Java Session Memory Usage
Database Configuration and I/O Issues
Sources of I/O
Disk performance
Assessing physical reads
Segment statistics
Segment wait events
Longops facility
Disk I/O
DBWR slaves
Optimize Sort Operations
Fundamentals of sorts
Recognisng sorts have occurred
Automated PGA memory management
PGA 9i views
Tuning considerations
Diagnosing contention
Latches and internal locks
Freelist contention
Explicit (manual) data locking
Tuning Oracle Shared Server
Shared server concepts
Setting up the shared server
Monitoring shared servers
Application Tuning
Query optimization
EXPLAIN PLAN
ANALYZE
AUTOTRACE
TKPROF
Histograms
Stored outlines Cached execution plans
Automatically gathering stats on tables
Materialized views
Query rewrite
Unused indexes
Data storage
Tables and indexes
Identifying unused indexes
Partitioning
Clustering
Temporary tables
Large objects (LOBS)
Fragmentation of extents
Row management
Structure of a block
Chained rows problem
Appendix 1
Managing the lab environment
Appendix 2
Guidelines for monitoring the Unix o/s
CPU Monitoring
Memory Monitoring
Swapping Statistics
Process Queuing Statistics
Disk Capacity Statistics
Disk Performance Statistics
MB07/01
Oracle 10G Performance Tuning for DBAs