Standard

Posted: 09/06/07

R State Standard

£ Institutionally Developed

College: n/a

CIS 2131 – Oracle Database Performance Turning

 

Course Description:

 

This course enables the database student to be able to fine tune Oracle databases. Topics include: Tuning overview, Oracle Alert and Trace Files, Utilities and Dynamic Performance Views, Tuning the Shared Pool, Tuning the Buffer Cache, Tuning the Redo Log Buffer, Database Configuration and I/O Issues, Using Oracle Blocks Efficiently, Optimizing Sort Operations, Tuning Rollback Segments, Monitoring and Detecting Lock Contention, SQL Issues and Tuning Considerations for different applications, Managing a Mixed Workload, Tuning with Oracle Expert, Multithreaded Server Tuning Issues. This course enables participant to be able to fine tune Oracle databases.

 

Competency Areas:

Hours:

Tuning overview

Class

4

Oracle Alert and Trace Files

D. Lab

6

Utilities and Dynamic Performance Views

P. Lab/O.B.I.

0

Tuning the Shared Pool

Credit

7

Tuning the Buffer Cache

 

 

Tuning the Redo Log Buffer

 

 

Database Configuration and I/O Issues

 

 

Using Oracle Blocks Efficiently

 

 

Optimizing Sort Operations

 

 

Tuning Rollback Segments

 

 

Monitoring and Detecting Lock Contention

 

 

SQL Issues and Tuning Considerations for different applications

 

 

Managing a Mixed Workload

 

 

Tuning with Oracle Expert

 

 

Exam Multithreaded Server Tuning Issues

 

 

 

Prerequisite:

CIS 2128 and CIS 2129

 

 

Corequisite:

 

 

 

 

Course Guide

 

Competency

After completing this section, the student will:

Hours

Class

D. Lab

P. Lab/

O.B.I.

TUNING OVERVIEW

2

3

0

 

Be able to list the roles associated with the database tuning process.

 

 

 

 

Define the steps associated with the tuning process.

 

 

 

 

Identify tuning goals.

 

 

 

ORACLE ALERT AND TRACE FILES

2

3

0

 

Be able to describe the location and usefulness of the Alert log file.

 

 

 

 

Be able to describe the location and usefulness of the background and user process trace files. 

 

 

 

UTILITIES AND DYNAMIC PERFORMANCE VIEWS

4

6

0

 

 Be able to collect statistics through available dynamic troubleshooting and performance views, the UTLBSTAT/UTLESTAT report output, oracle wait events, appropriate Enterprise Manager tuning tools.

 

 

 

 

Be able to define latch types.

 

 

 

TUNING THE SHARED POOL

4

6

0

 

Be able tune the library cache and the data dictionary cache, measure the shared pool hit ratio, size the shared pool appropriately, pin objects in the shared pool, tune the shared pool reserved space.

 

 

 

 

Describe the User Global Area and session memory considerations and configure the large pool.

 

 

 

TUNING THE BUFFER CACHE

4

6

0

 

Describe how the buffer cache is managed, calculate and tune the buffer cache hit ration, tune the buffer cache hit ration by adding or removing buffers, create multiple buffer pools, size multiple pools, monitor buffer cache usage, make appropriate use of table caching, diagnose LRU latch contention, avoid free lis contention.

 

 

 

TUNING THE REDO LOG BUFFER

2

3

0

 

Be able to determine if processes are waiting for space in the redo log buffer, size the redo log buffer appropriately, reduce redo operations.

 

 

 

DATABASE CONFIGURATION AND I/O ISSUES

4

6

0

 

 Diagnose inappropriate use of SYSTEM, RBS, TEMP, DATA, and INDEX tablespaces, use locally managed tablespaces to avoid space management issues, detect I/O problems, use striping, tune checkpoints, tune DBWN process.

 

 

 

USING ORACLE BLOCKS EFFICIENTLY

2

3

0

 

Determine and appropriate block size, Optimize space usage within blocks, detect and resolve row migration, and monitor and tune indexes.

 

 

 

OPTIMIZING SORT OPERATIONS

2

3

0

 

Identify the SQL operations that require sorting, ensure that sorting is done in memory where possible, reduce the number of I/Os required for the sort runs, and allocate temporary space appropriately.

 

 

 

TUNING ROLLBACK SEGMENTS

2

3

0

 

Use the dynamic performance views to check rollback segment performance, reconfigure and monitor rollback segments, define the number and sizes of rollback segments, appropriately allocate rollback segments to transactions.

 

 

 

MONITORING AND DETECTING LOCK CONTENTION

2

3

0

 

Define types and modes of locking, list possible causes of contention, use Oracle utilities to detect, resolve, prevent locking problems.

 

 

 

SQL ISSUES AND TUNING CONSIDERATIONS FOR DIFFERENT APPLICATIONS

4

6

0

 

Identify the role of the DBA in application tuning, use optimizer modes to enhance SQL statement performance, manage stored outlines, tune the design of the database.

 

 

 

 

Identify the demands of OLTP and DSS, reconfigure systems on a temporary basis for particular needs.

 

 

 

MANAGING A MIXED WORKLOAD

2

3

0

 

Be able to describe the features of Database Resource Manager, limit the use of resources using Database Resource Manager.

 

 

 

TUNING WITH ORACLE EXPERT

2

3

0

 

Be able to describe the features of Oracle Expert.

 

 

 

EXAM MULTITHREADED SERVER TUNING ISSUES

4

6

0

 

Be able to identify issues associated with managing users in a multithreaded server environment, diagnose and resolve performance issues, and configure the multithreaded server environment to optimize performance.

 

 

 

 

Suggested Resources

 

Books:

 

Enhanced Guide to Oracle by Joline Morrison and Mike Morrison, Course Technology, ISBN: 0619035498

Oracle SQL Interactive Workbook by Alex Morrison and Alice Rischart, Prentice Hall, ISBN: 0130157457

Oracle SQL Plus: The Definitive Guide by Jonathan Gennick  Deborah Russel, O'Reilly & Associates, Incorporated ISBN: 15659257