Performance Tuning

I.     Course Prefix/Number: CIS 247

       Course Name: Performance Tuning

       Credits: 4 (4 lecture; 1 lab)

II.    Prerequisite

Recommended: CIS 245 or comparable knowledge.

III.   Course (Catalog) Description

Course focuses on maximizing the performance of the database from the design to using the database in a production environment.   Course focuses observing, defining, and diagnosing the problem, and implementing a solution using various methods, techniques, and diagnostic tools.  Students will learn how to observe, monitor, identify, troubleshoot, tweak, and resolve common performance-related problems.  This course leads to the Oracle Database Administrator Certified Professional certification.

IV.   Learning Objectives

Upon completion of this course, the student will be able to:
  1. Diagnose and tune common SQL-related performance problems
  2. Diagnose and tune common Instance related performance problems
  3. Configure an Oracle database focusing on good performance.
  4. Diagnose performance problems and implement solutions
  5. Use the tools based on the Automatic Workload Repository to tune the database
  6. Use Enterprise Manager to monitor an Oracle database
  7. Identify, tune, and resolve Input/Output and data storage problems
  8. Utilize database advisors to proactively tune an Oracle Database instance
  9. Monitor an application’s performance
  10. Use the Optimizer to improve performance
  11. Identify, tune, and resolve Buffer cache performance  problems
  12. Identify best practices in performance tuning

V.    Academic Integrity and Student Conduct

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, and
• 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.

Please review the Code of Academic Conduct and the Code of Student Conduct, both located online at

VI.   Sequence of Topics

  1. Basic Tuning Tools
    1. Monitoring tools overview
    2. Enterprise Manager
    3. V$ Views, statistics and metrics
    4. Wait events
  2. Using Automatic Workload Repository
    1. Managing the Automatic Workload RepositoryCreate AWR Snapshots
    2. Real time SQL monitoring
  3. Defining Problems
    1. Defining the problem
    2. Limit the scope and setting the priority
    3. Most useful SQL reports
    4. Common tuning problems
    5. Tuning during the life cycle
    6. ADDM tuning session
    7. Performance versus business requirements
    8. Performance tuning resources
    9. Filing a performance service request
    10. Monitoring and tuning tools
  4. Using Metrics and Alerts
    1. Metrics, alerts, and baselines
    2. Limitation of base statistics and typical delta tools
    3. Benefits of metrics
    4. Viewing metric history information and using EM to view metric details
    5. Statistic histograms and histogram views
    6. Database control usage model and setting thresholds
    7. Server-Generated alerts, creating and testing an alert and metric and alert views
  5. Using Baselines
    1. Comparative performance analysis with AWR baselines
    2. Automatic workload repository baselines
    3. Moving Window baseline
    4. Baselines in performance page settings and baseline templates
    5. AWR baselines and creating AWR baselines
    6. Managing baselines with PL/SQL and baseline views
    7. Performance monitoring and baseline
    8. Defining alert thresholds using a static baseline
    9. Using EM to configure and change adaptive threshold settings
  6. Using AWR Based Tools
    1. Automatic maintenance tasks
    2. ADDM performance monitoring
    3. Active session history
  7. Monitoring an Application
    1. What Is a Service, Service Attributes, and Service Types?
    2. Creating Services and Managing Services in a single-Instance environment
    3. Everything Switches to Services
    4. Using Services with client applications and using Services with the Resource Manager
    5. Services and Resource Manager with EM and using Services with the Scheduler
    6. Using Services with parallel operations and metric thresholds
    7. Service aggregation, tracing, and Service aggregation configuration.
    8. Client Identifier Aggregation and Tracing and Service Performance Views
  8. Identifying Problem SQL Statements
    1. SQL Statement processing phases and role of the Oracle Optimizer
    2. Identifying Bad SQL
    3. Real Time SQL monitoring
    4. Best SQL Reports
    5. What is an Execution Plan?
      1. Methods for viewing Execution Plans
      2. Uses of Execution Plans
    6. DBMS_XPLAN Package: Overview and EXPLAIN PLAN command
    7. Reading an Execution Plan, Using the V$SQL_PLAN View and Querying the AWR
    8. SQL*Plus AUTOTRACE and SQL Trace Facility
    9. How to use the SQL Trace Facility
    10. Generate an Optimizer Trace
  9. Using the Optimizer
    1. Functions of the Query Optimizer, Selectivity, Cardinality and Cost and Changing Optimizer Behavior
    2. Using hints, Optimizer Statistics and Extended Statistics
    3. Controlling the behavior of the Optimizer with parameters
    4. Enabling Query Optimizer Features and influencing the Optimizer approach
    5. Optimizing SQL Statements, access paths and choosing an Access Path
    6. Join and sort Operations
    7. How the Query Optimizer Chooses Execution Plans for joins
    8. Reducing costs
  10. Using SQL Performance Analyzer
    1. Real Application Testing: Overview and Use Cases
    2. SQL Performance Analyzer: Process and Capturing the SQL Workload
    3. Creating a SQL Performance Analyzer Task and SPA (NF Lesson 9) DBMS_SQLTUNE.CREATE_TUNING_TASK
    4. Optimizer Upgrade Simulation and SQL Performance Analyzer Task Page
    5. Comparison Report and Comparison Report SQL Detail
    6. Tuning Regressing Statements and Preventing Regressions
    7. Parameter Change Analysis and Guided Workflow Analysis
    8. SQL Performance Analyzer: PL/SQL Example and Data Dictionary Views
  11. SQL Performance Management
    1. Maintaining SQL Performance and Optimizer Statistics and Automated Maintenance Tasks
    2. Statistic gathering options and setting statistic preferences
    3. Restore statistics
    4. Deferred Statistics Publishing
    5. Automatic SQL Tuning
    6. SQL Tuning Advisor
    7. Using the SQL Access Advisor
    8. SQL Plan Management
  12. Using Database Replay
    1. The Big Picture and System Architecture
    2. Capture and Replay considerations
    3. Replay Options and analysis
    4. Database Replay Workflow in Enterprise Manager
    5. Packages and procedures
    6. Data Dictionary Views: database Replay
    7. Calibrating Replay Clients
  13. Tuning the Shared Pool
    1. Shared Pool architecture and operation
    2. The Library Cache and Latch and Mutex
    3. Diagnostic Tools for Tuning the Shared Pool
    4. Avoiding Hard and Soft Parses
    5. Sizing the Shared Pool and Avoiding Fragmentation
    6. Data Dictionary Cache and SQL Query Result Cache
    7. UGA and Oracle Shared Server
    8. Large Pool and Tuning the Large Pool
  14. Tuning the Buffer Cache
    1. Oracle database Architecture: Buffer Cache
    2. Database buffers
    3. Buffer Hash Table for Lookups
    4. Working sets
    5. Buffer Cache Tuning Goals and Techniques
    6. Buffer Cache Performance Symptoms and Solutions
    7. Automatically Tuned Multiblock Reads
    8. Flushing the Buffer Cache (for Testing Only)
  15. Tuning PGA and Temporary Space
    1. SQL Memory Usage and Performance Impact
    2. SQL Memory Manager
    3. Configuring Automatic PGA Memory and Setting PGA_AGGREGATE_TARGET Initially
    4. Monitoring and Tuning SQL Memory Usage
    5. PGA Target Advice Statistics and Histograms
    6. Automatic PGA and Enterprise Manager and Automatic PGA and AWR Reports
    7. Temporary Tablespace Management: Overview and Monitoring Temporary Tablespace
    8. Temporary Tablespace Shrink and Tablespace Option for Creating Temporary Table
  16. Automatic Memory Management
    1. Oracle database architecture, Dynamic SGA and Memory Advisories
    2. Granule and Manually Adding Granules to Components
    3. Increasing the Size of an SGA Component, SGA Sizing Parameters and Manually Resizing Dynamic SGA Parameters
    4. Automatic Shared Memory Management and Memory Broker Architecture
    5. Behavior of Auto-Tuned and Manually TunedSGA Parameters
    6. Using the V$PARAMETER View and Resizing SGA_TARGET
    7. Disabling, configuring and monitoring Automatic Shared Memory Management (ASMM)
    8. Automatic Memory Management
  17. Tuning Segment Space Usage
    1. Space and Extent Management and Locally Managed Extents
    2. How table data is stored
    3. Anatomy of a database block
    4. Minimize Block Visits
    5. The DB_BLOCK_SIZE Parameter
    6. Small and Large Block Size Considerations
    7. Block Allocation, Free Lists and Block Space Management with Free Lists
    8. Automatic Segment Space Management
    9. Migration and Chaining, Shrinking Segments and Table Compression: Overview
  18. Tuning I/O
    1. I/O Architecture, File System Characteristics, I/O Modes and Direct I/O
    2. Bandwidth Versus Size and Important I/O Metrics for Oracle databases
    3. I/O Calibration and Enterprise Manager, I/O Calibration and the PL/SQL Interface and I/O Statistics and Enterprise Manager
    4. Stripe and Mirror Everything
    5. Using RAID
    6. I/O Diagnostics
    7. Database I/O Tuning
    8. What is Automatic Storage Management?
  19. Identify best practices in performance tuning
  20. Using Statspack
    1. Installing Statspack
    2. Capturing Statspack Snapshots
    3. Reporting with Statspack
    4. Statspack Considerations
    5. Statspack and AWR Reports
    6. Reading a Statspack Report
    7. Statspack and AWR

VII.  Methods of Instruction

Reading, lecture, discussion, group work, demonstrations, hands-on exercises, projects, assignments, quizzes, and/or exams
Course may be taught as face-to-face, hybrid or online course.

VIII. Course Practices Required

A.    Appropriate attendance
B.    Satisfactory completion of lab projects
C.    Completion of assignments and tests.
D.    Read textbook and research appropriate manuals as needed

IX.   Instructional Materials

Note: Current textbook information for each course and section is available on Oakton's Schedule of Classes.

X.    Methods of Evaluating Student Progress

Students will be evaluated on quality of their lab projects, other written and/or oral assignments, quizzes, and exams as specified by the instructor.

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 Access and Disability Resource Center at the Des Plaines or Skokie campus. 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 Community College is committed to maintaining a campus environment emphasizing the dignity and worth of all members of the community, and complies with all federal and state Title IX requirements.

Resources and support for
  • pregnancy-related and parenting accommodations; and
  • victims of sexual misconduct
can be found at

Resources and support for LGBTQ+ students can be found at