By using our website you agree to our use of cookies.

Oracle 9i Database Performance Tuning

5 Day Course
Code OCO9IPT

This course has been retired. Please view currently available Oracle DBA Training Courses.

Modules

Collapse all

Overview of Oracle 9i Database Performance Tuning (1 topic)

  • Tuning questions, phases, and goals, Common performance problems, Tuning during development, Tuning steps for a production database Performance and availability trade-offs

Diagnostic and Tuning Tools (1 topic)

  • Log and trace files, Tuning with Oracle Enterprise Manager, Statspack, Statistics, troubleshooting data, and performance data in the data dictionary, Database and self-built performance statistics collection, Collecting performance statistics with assorted tools

Database Configuration and I/O Issues (1 topic)

  • Distributing files across devices, Tablespace considerations, Full table scan performance, Checkpoint performance, Redo management and performance, Analyzing I/O activity, Analyzing checkpoint performance

Tuning the Shared Pool (1 topic)

  • Shared pool components and latches, Monitor and tune the library cache Manage SQL statements and PL/SQL blocks to improve library cache performance, Monitor and tune the data dictionary cache, Reserved pool and large pool management, Using diagnostic tools to monitor and tune the shared pool

Tuning the Buffer Cache (1 topic)

  • Buffer cache sizing parameters, Buffer cache advisory

Dynamic Instance Resizing (1 topic)

  • Dynamic allocation units in the SGA, Resize SGA components, Resizing the buffer cache and the shared pool

Sizing Other SGA Structures (1 topic)

  • Manage redo log buffer, Control redo operations, Manage Java pool memory, Managing the redo log buffer

Tuning the Oracle Shared Server (1 topic)

  • Use and performance of shared server processes, Shared servers and memory usage, Monitor and troubleshoot shared server activity

Optimizing Sort Operations (1 topic)

  • Automatic PGA memory management, PGA sizing advisor, Sorts and temporary segments, Tuning, diagnostics, and guidelines for sorts, Reduce sorting, Configuring and monitoring sort space options, Configuring automatic PGA memory management

Using Resource Manager (1 topic)

  • Database Resource Manager concepts, Administering the Database Resource Manager, Define resource plans limits, Assigning users to consumer groups, Manual and automatic consumer group switching, Examine resource plan information in the data dictionary

SQL Statement Tuning (1 topic)

  • Optimizer modes, Hints, Plan stability and stored outlines, SQL tuning diagnostic tools, Interpret results from SQL tuning diagnostic tools

Managing Statistics (1 topic)

  • Automatic segment-level statistics collection, Dynamic sampling, Table statistics, index statistics, and index tuning wizard, Column statistics and histograms, Estimated statistics and automatic statistics collection, Optimizer cost model, System statistics, Copying statistics between databases

Using Oracle Blocks Efficiently (1 topic)

  • Database storage components, Extent management, High water-mark, Space management for tables, Index management

Using Oracle Data Storage Structures Efficiently (1 topic)

  • Data storage structures, Selecting physical storage characteristics for performance, Data access methods and structures

Application Tuning (1 topic)

  • Role of the database administrator, Table movement and redefinition, Creating and managing indexes and index-organized tables, Statistics collection, Considerations for OLTP systems, Considerations for decision support systems and data warehouses Considerations for hybrid systems

Using Materialized Views (1 topic)

  • Create and refresh materialized views, Nested materialized views, Query rewrites, The DBMS_MVIEW package

Monitoring and Detecting Lock Contention (1 topic)

  • Locking mechanisms and management, Data concurrency, Enqueues and lock types, Lock monitoring and resolution of conflicts, Deadlocks, Use diagnostic tools to monitor lock contention

Prerequisites

Oracle 9i DBA Fundamentals
Oracle DBA Training Courses

Course PDF

Print

Sections