Oracle 10G Performance Tuning for DBAs

Course

In Bath

Price on request

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

Bath (Somerset)
See map
11 Kingsmead Square, BA1 2AB

Start date

On request

About this course

Attendees should be knowledgeable in Oracle DBA (preferably at 9i or 10G) to get the best out of this course.

Questions & Answers

Add your question

Our advisors and other users will be able to reply to you

Who would you like to address this question to?

Fill in your details to get a reply

We will only publish your name and question

Reviews

Course programme

AUDIENCE: This course is designed for: Database administrators, Developers, Data warehouse support team members.
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

Price on request