Oracle8i Performance Tuning: Managing Memory and Disk I/O

This course is the second in a three-part Oracle8i Performance Tuning series. This course introduces the learner to the application tuning issues that will impact database performance. In addition participants will be introduced to tuning components and functions of the shared pool and the buffer cache. Students will learn how to monitor the contents and usage of the Redo Log buffer. Finally participants will identify database configuration and I/O issues including tablespace usage and guidelines.

This is primarily online training This is an online eLearning or CBT training program Contact Serebra Learning Corporation for more information

Duration: 8 hours Training Presented in: English

Audience

Database Administrators System Administrators Network Administrators and Technical Support Professionals. Participants should have completed the Oracle8i Database Administration Series (courses 63311-63315) prior to taking this course and Oracle8i Performance Tuning: Strategies and Techniques (63331). This course will help candidates prepare for the Oracle8i Certified Database Administrator (DBA) Track Exam 4 1Z0-024.

Objective

• Identify application tuning issues that will impact database performance.
• Identify the contents and functions of the shared pool.
• Identify how the Buffer Cache is managed and tuned.
• Learn how to tune the Redo Log Buffer.
• Learn about database configuration and I/O issues.

Topics Include

Unit 1: Tuning SQL

• Explain the plan for a query by using the EXPLAIN PLAN statement.
• Inspect the performance of a plan by setting up trace at the session level.
• Match the TKPROF options with their respective descriptions.
• Generate an execution plan at the system level for a SQL statement by using AUTOTRACE.
• Identify the characteristics of optimizer modes.
• Set the optimizer mode at the session level by using the OPTIMIZER_MODE parameter.

Unit 2: Tuning the Shared Pool

• Select the contents of the library cache.
• Identify the contents of the data dictionary cache.
• Select the functions of the MultiThreaded Server Connection.
• Identify the goals for tuning the library cache.
• View the sizes of all SGA structures by using the V$SGASTAT view.
• Determine the percentage of parse calls that find a cursor to share by using the GETHITRATIO column of the V$LIBRARYCACHE view.
• Determine the reloads-to-pins ratio by using the V$LIBRARYCACHE view.
• Compute the amount of sharable memory that has been used by using the V$SQLAREA view.
• Keep a large object in memory by using the KEEP procedure of the DBMS_SHARED_POOL package.
• View the reserved pool space within the shared pool by using the V$SHARED_POOL_RESERVED view.
• Identify the actions for tuning the shared pool reserved space.
• Compute the amount of space used for a test connection in the user global area.
• Match the columns found in the V$ROWCACHE view with their definitions.
• Find the ratio of GETSMISSES to GETS from the V$ROWCACHE view.
• Select the features of configuring the large pool as a separate memory area.
• Determine the size of the free memory allocated to the large pool.
• Determine why the server could not allocate the required memory for the large pool.

Unit 3: Tuning the Database Buffer Cache

• Identify the characteristics of the buffer cache.
• Sequence the five steps that the server process performs when managing the buffer cache.
• Identify the events that cause DBWn to write dirty blocks.
• Select the tuning techniques used to improve the performance of the buffer cache.
• Identify the dynamic performance views used to tune the buffer cache.
• Measure the cache hit ratio.
• Match the three types of buffer pools with their functions.
• Match the initialization parameters used when creating multiple buffer pools with their function.
• Define the keep buffer pool by using the BUFFER_POOL KEEP procedure.
• Determine the number of blocks that have been allocated to a table.
• Monitor the number of buffer pool blocks by object by using the V$CACHE view.
• Calculate the hit ratio for multiple pools.
• Display information on all the buffer pools by using the V$BUFFER_POOL dictionary view.
• Determine whether there have been waits for buffers by using the V$SESSION_EVENT performance indicator view.

Unit 4: Tuning the Redo Log Buffer

• Identify the characteristics of the redo log buffer.
• Select the situations in which the LGWR process writes to the redo log files.
• Determine the size of the redo log buffer.
• Tune the redo log buffer by displaying the number of all redo log space requests and all redo entries.
• Calculate the hit ratio of redo log space requests to redo entries.
• Determine if there are any waits for a log switch to occur.
• Determine the number of occurrences of the log file switch completion event.
• Determine the number of occurrences of the log file switch (checkpoint incomplete) event by using the V$SYSTEM_EVENT view.
• Identify the log file switch waits by using the V$SYSTEM_EVENT view.
• Identify the characteristics of the NOLOGGING option.

Unit 5: Configuring and I/O Issues

• Identify the guidelines to consider when using tablespaces in a database.
• Identify the information needed to choose the correct stripe size.
• Determine how many full table scans are occurring by using the V$SYSSTAT view.
• Identify the reasons for setting the DB_FILE_MULTIBLOCK_READ_COUNT parameter when tuning full table scans.
• Determine the number of disk I/O per data file by using the V$FILESTAT view.
• Identify the redo log file configuration guidelines.
• Identify the guidelines for archive log file configuration.
• Match the functions of the initialization parameters used to enable multiple archive destinations with their functions.
• Identify the guidelines for monitoring checkpoint frequency.
• Identify methods for regulating checkpoints.
• Identify the characteristics of I/O slaves.
• Match the names of the parameters used to deploy I/O slaves and control asynchronous I/O with their function.
• Select the characteristics of the DB_BLOCK_MAX_DIRTY_TARGET parameter when tuning multiple DBWn I/O.

Duration

8

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

• Oracle8i Performance Tuning: Optimizing Sorts and Minimizing Contention
• Oracle8i Performance Tuning: Strategies and Techniques