Oakton Community College- CIS

I.

Course Prefix	Course Number	Course Name	Credit	Lecture	Lab
CIS	247	Performance and Tuning	3	3	1

II.

Prerequisite:

See recommendation in Course (Catalog) Description

III.

Course (Catalog) Description:

Course continues developing the knowledge needed by the database administrator to maintain a database. Content includes methods and techniques to maximize performance of the database from the design to using the database in a production environment; recognizing, troubleshooting and resolving common performance-related problems. Course prepares student for corresponding Oracle Database Administrator certification exam. Recommended: CIS 245 or comparable knowledge.

IV.

Learning Objectives:

Upon completion of this course, the student will be able to:

1. Configure Resource Manager to control resource usage.

2. Configure an Oracle database focusing on good performance.

3. Detect and resolve latch and lock contention problems within the database environment.

4. Diagnose and resolve performance issues associated with Shared Server.

5. Diagnose performance problems

6. Use Enterprise Manager to perform tuning tasks

7. Perform day-to-day monitoring on an operational database using STATSPACK

8. Identify and resolve I/O, data storage, and database configuration problems

9. Configure memory and disk resources to optimize sort operations

10. Reconfigure file structures for improve performance

V.

Academic Integrity:

Students and employees at Oakton Community College are required to demonstrate academic integrity and follow Oakton's Code of Academic Conduct. This code prohibits:

cheating

plagiarism (turning in work not written by you, or lacking proper citation)

falsification and fabrication (lying or distorting the truth)

helping others to cheat

unauthorized changes on official documents

pretending to be someone else or having someone else pretend to be you

making or accepting bribes, special favors, or threats

any other behavior that violates academic integrity

There are serious consequences to violations of the academic integrity policy. Oakton's policies and procedures provide students a fair hearing if a complaint is made against you. If you are found to have violated the policy, the minimum penalty is failure on the assignment and, a disciplinary record will be established and kept on file in the office of the Vice President for Student Affairs for a period of 3 years.

Details of the Code of Conduct can be found in the Student Handbook.

VI.

Outline of Topics:

A. Overview of Performance and Tuning

1. Job roles in tuning

2. Tuning phases

3. Tuning goals and service level agreements

4. Common performance problems

5. Tuning methodology

B. Diagnostic and Tuning Tools

1. Alert log file

2. Background process trace files

3. User trace files

4. Dictionary views providing statistics

5. Dynamic performance views

6. TIMED_STATISTICS parameter to collect statistics

7. STATSPACK procedures

C. Sizing the Shared Pool

1. Overview of the shared pool

2. Library cache tuning

3. Reuse statements

4. Using reserved space

5. Keeping large objects

6. Related tuning issues

7. Data dictionary cache (DDC) tuning

D. Sizing the Buffer Cache

1. Overview of tuning the buffer cache

2. Buffer cache sizing parameters

3. Depreciated buffer cache parameters

4. Buffer cache advisory parameter

5. Dynamically resizing SGA components

6. Granules of allocation

7. Increase the size of a SGA component

8. Resolving techniques

E. Sizing other SGA Structures

1. Sizing the redo log buffer

2. Detecting contention

3. Resolving contention

4. Sizing the Java pool

5. Monitoring Java pool memory

6. Sizing the SGA for Java

7. Sizing Java pool memory

8. Limiting Java session memory usage

F. Database Configuration and I/O issues

1. Distributing files across devices

2. Tablespace usage

3. Detecting improper tablespace usage

4. Moving datafiles

5. Oracle file striping

6. Tuning full table scans

7. Tuning checkpoints

8. Redo log file configuration

G. Optimize Sort Operations

1. What is the sorting process

2. Sort area parameters (New & Old)

3. Tuning sort operations (DB configuration)

4. Tuning temporary segments

5. Reducing sort operations (application tuning)

6. Identify sort operations

7. Avoiding sort operations

8. Diagnostic tools

H. Diagnosing Contention For Latches

1. Oracle's implementation of latches

2. Latch request types - willing-to-wait, immediate

3. Reducing latch contention

4. Significant latches

5. Shared pool and library cache latches

I. Tuning Rollback (or UNDO) Segments

1. Describing rollback segment usage

2. Usage and configuration

3. Detecting problems with rollback segments

4. Resolving problems with rollback segments

5. Creating rollback segments and bringing them online

6. Allocating transactions to a rollback segment

7. Resolving RBS problems

8. Describing system managed undo

J. Monitoring and Detecting Lock Contention

1. Concepts of locking

2. Overview of locking issues

3. Types of DML locks

4. Levels of locks

5. Types of DDL lock modes

6. Detecting blocking locks

7. Monitoring locking activity

8. Deadlocks

K. Tuning Oracle Shared Server

1. Introducing Oracle shared server

2. When are shared servers required in Oracle9i

3. Monitoring dispatcher processes

4. Monitoring shared server processes

5. Increasing or decreasing the number of dispatchers

6. Increasing or decreasing the number of shared servers

7. Monitoring process usage

8. Monitoring memory usage

L. Application Tuning

1. Role of the DBA

2. Oracle data structures

3. Selecting the physical structure

4. Data storage structures

5. Clusters

6. Indexes

7. B-Tree

8. Compressed

M. Using Oracle Blocks Efficiently

1. Database storage hierarchy

2. Allocating extents

3. Monitoring space usage in data segments

4. Recovering space from sparsely populated segments

5. Database blocks

6. Chaining and migration

7. Detecting row chaining and migration

8. Resolving row chaining and migration

N. SQL Statement Tuning

1. Cost-based, rule-based optimizer modes

2. New cost based optimizer option

3. Plan stability

4. Plan equivalence

5. Stored outlines

6. Creating stored outlines

7. Maintaining stored outlines

O. Tuning the Operating System and Using Resource Manager

1. System architecture

2. Virtual and physical memory

3. Paging and swapping

4. CPU Tuning guidelines

5. Process vs. thread

6. Resource manager concepts

VII.

Methods of Instruction:

Lecture, demonstrations, discussion, and hands-on exercises

VIII.

Course Practices Required:

Students are expected to be able to read, write, and speak English.

IX.

Instructional Materials:

TEXTBOOK: TBA

X.

Methods of Evaluating Student Progress:

In-class hands-on exercises, reading assignments, projects, quizzes, and exams will be used.

XI.

Other Course Information:

If you have a documented learning, psychological, or physical disability you may be entitled to reasonable academic accommodations or services. To request accommodations or services, contact the ASSIST office in the Learning Center. All students are expected to fulfill essential course requirements. The College will not waive any essential skill or requirement of a course or degree program.

OAKTON HOME | ACADEMIC PROGRAMS | PREVIOUS PAGE

Copyright © 2006 Oakton Community College
Please direct questions or comments about the Oakton web site to the Webmaster.
For further information or questions about the CIS discipline, please contact the Page Coordinator
and the CIS Coordinator, Michele Reznick, at (847) 635-1904.
Last updated:

I.

Course Prefix

Course Number

Course Name

Credit

Lecture

Lab

CIS

247

Performance and Tuning

3

3

1

II.