Develop the advanced skills of performance and tuning to effectively administer an Oracle Server Database

Course Description:

This course provides you with the knowledge and hands-on experience of SQL tuning, database tuning and advanced performance design.

You can learn to…

• Understand the role of the DBA in performance tuning

• Design and build appropriate index and other storage structures for optimal performance

• Use a variety of tools to analyze the performance of SQL statements

• Generate statistics for and use the cost-based optimizer

• Use a variety of database analysis and tuning tools

• Configure a database for optimal performance

• Tune database memory, sorts, file I/O, and contention

Who Needs to Attend:

Application Developers and Designers, Managers, Database Administrators, Systems Administrators, Network Managers and Technical Support Personnel

Prerequisites:

Oracle Foundation Classes, Oracle DBA I or equivalent experience

Course Labs:

1. Logical Structure Tuning

2. SQL Statement Tuning

3. Statistics and Execution Plans

4. SQL_Trace, Outlines, TKPROF, Hints

5. Tuning Tools

6. Tuning Memory

7. Tuning I/O

8. Performance Tuning Workshop

Course Content:

Performance Tuning Duties of the DBA

• DBA’s Performance Tuning Duties

• Steps for Tuning

• Tuning goals for different types of applications

• The Performance Tuning Team

• Tuning Tools

• Overview

• Table / Tablespace Considerations

• Free Space Management

• Chained and Migrated Rows

• Index Types

• Materialized Views / Snapshots

• Query Rewrite

• SQL Parsing and Execution

• Shared SQL Statements

• Contents of the SQL Area

• Segment Space Management

• Automatic Undo Management

• Understanding Statement Transformation

• Optimizing SQL Syntax

• Rule Based and Cost Based Optimization

• Table and Index Statistics

• Gathering Statistics

• Execution Plans

• SQL*Plus and AUTOTRACE

• SQL_TRACE and TKPROF

• Hints

• Stored Outlines / Plan Stability

• SPFILE / Init.Ora

• V$ Views

• UTLBSTAT / UTLESTAT

• STATSPACK

• Tune OS Memory

• Tune Database Buffer Cache

• Memory Subcaches

• Tune Redo Log Buffer

• Tune Private SQL and PL/SQL Areas

• Tune the Shared Pool

• Tune Sorts

• Tuning the Shared Server

• Tuning the Java Pool

• SGA / PGA Tuning

• RAID

• Oracle File I/O

• Partitioning Tables and Indexes

• Checkpointing

Tuning Contention and Understanding the Resource Manager

• Shared Server Contention

• Tune Lock Contention

• Tune Contention for Undo and Temporary Segments, and for Latches

• Introduction to the Resource Manager

• Issues associated with managing users in a Shared Server environment

• Diagnose and resolve performance problems

• Configure shared servers to optimize performance

• Define locking levels

• Describe causes of contention

• Use Oracle tools to detect lock contention

• Emergency lock contention resolution

• Prevent locking problems

• Recognize deadlocks

• DBMS_APPLICATION_INFO

• DBMS_IOT

• DBMS_ORACLE_TRACE_AGENT

• DBMS_ORACLE_TRACE_USER

• DBMS_PROFILER

• DBMS_RESOURCE_MANAGER

• DBMS_RESOURCE_MANAGER_PRIVS

• DBMS_SESSION

• DBMS_SHARED_POOL

• DBMS_SPACE

• DBMS_STATS

• DBMS_TRACE

• DBMS_UTILITY

Performance Tuning Checklist & Workshop