Oracle9i Performance Tuning: Managing Memory and Disk I/O

Audience

The intended audience for this course includes Database Administrators Technical Support Professionals and System Administrators.

Objective

• Match the optimizer modes with their descriptions.
• Match the OPTIMIZER_MODE parameter values with their description.
• Identify the methods of creating stored outlines.
• Identify the three major areas of the Shared Pool.
• Identify the parameters used to tune the Shared Pool.
• Identify the goals for tuning the Library Cache.
• Identify the descriptions of redo entries.
• Match the statistics used to diagnose disk I/O contention with their descriptions.
• Identify the two parameters that allow the DBA to limit the amount of memory used for each Java session.
• Identify the performance guidelines to reduce the activity on an overloaded disk.
• Identify the methods of striping.
• Identify the initialization parameter that indicates the size of read calls during full table scans.

Topics Include

Unit 1: SQL Statement Tuning

• Match the optimizer modes with their descriptions.
• Match the OPTIMIZER_MODE parameter values with their description.
• Identify the methods of creating stored outlines.
• Set the value of the USE_STORED_OUTLINES parameter.
• Match the values of the USE_PRIVATE_OUTLINES parameter with their descriptions.
• Identify the procedures in the DBMS_OUTLN package used to manage stored outlines.
• Identify the diagnostic tools available for evaluating the performance of SQL statements and PL/SQL modules.
• Identify the sections of the STATSPACK SQL report.
• Designate the execution plan Oracle follows for executing a SQL statement by creating an EXPLAIN PLAN.
• Sequence the steps to diagnose SQL statement performance with SQL TRACE and TKPROF.
• Identify the advantages of using SQL*PLUS AUTOTRACE instead of SQL TRACE.
• Identify the two methods of collecting statistics.
• Identify the data dictionary views populated by gathering table index and column statistics.
• Sequence the steps in copying statistics by using the DBMS_STATS package.

Unit 2: Tuning the Shared Pool and Buffer Cache

• Identify the three major areas of the Shared Pool.
• Identify the parameters used to tune the Shared Pool.
• Identify the goals for tuning the Library Cache.
• Match the V$ views used as diagnostic tools in the Library Cache with their description.
• Match the parameters used to tune shared cursors with their description.
• Sequence the steps for sizing the Library Cache.
• Identify the view used to compute the amount of shareable memory that has been used.
• Match the columns in the V$SHARED_POOL_RESERVED view used in helping tune the reserved pool with their description.
• Identify which objects should be kept in memory to prevent slow user response time.
• Identify the solutions for eliminating large anonymous PL/SQL blocks.
• Identify the two keywords used in the Data Dictionary Cache.
• Match the three columns in the V$ROWCACHE view used to monitor the Data Dictionary Cache with their description.
• Identify the tuning goals for achieving an ideal Data Dictionary Cache ratio.
• Match the different block states in the Buffer Cache with their description.
• Sequence the steps that the server process performs when managing the Buffer Cache.
• Identify the options available to improve the cache hit ratio.
• Identify dynamic performance views used to tune the Buffer Cache.
• Sequence the steps for measuring the cache hit ratio.
• Match the buffer pools with their descriptions.
• Identify the clause used to define the default buffer pool for an object.
• Identify the tuning goal of the KEEP buffer pool.
• Sequence the steps in sizing the RECYCLE buffer pool.
• Match the V$ views used in tuning buffer pools with their description.
• Sequence the steps for resolving freelist contention.

Unit 3: Dynamic SGA and Other SGA Structures

• Identify the descriptions of redo entries.
• Match the statistics used to diagnose disk I/O contention with their descriptions.
• Identify the two parameters that allow the DBA to limit the amount of memory used for each Java session.
• Identify the init.ora parameter that contains the fixed allocated amount of memory for the Java pool.
• Match methods for computing allocated User Global Area space with the appropriate example.
• Identify the capabilities of the Large Pool.
• Identify the four parameters that most affect SGA size.
• Identify the ways to change the size of the Shared Pool.
• Match parameters that define the buffer cache sizes for the primary block with their descriptions.
• Identify the description of a granule.
• Identify the parameters used with I/O slaves.
• Identify the parameter used to specify multiple DBWR processes.

Unit 4: Configuration and I/O Issues

• Identify the performance guidelines to reduce the activity on an overloaded disk.
• Identify the methods of striping.
• Identify the initialization parameter that indicates the size of read calls during full table scans.
• Identify the factors used by the Cost-Based Optimizer to determine the cost of full table scans.
• Match the types of checkpoints with their description.
• Match the parameters used to control the checkpoint queue with their description.

Duration

6

Minimum Requirements

The CDROM version of this course requires:

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).
• At least a double speed CDROM drive.
• An MPC compliant sound card with attached speakers or headphones is recommended (Currently only the CDROM version supports audio).

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM and 22MB available hard disk space or file server space.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).

Media