Oracle8i Performance Tuning Curriculum CBT Training CDs

Rating:
Summary: Oracle8i Performance Tuning Curriculum CBT Training CDs
Review: Course Overview
The Oracle8i Performance Tuning Curriculum introduces participants to tools and techniques used to improve the performance of the Oracle8i server. Participants will also learn about application tuning issues that will impact database performance and improving performance for sort operations.

# This curriculum consists of 3 courses: Course 63331 Oracle8i Performance Tuning: Strategies and Techniques
# Course 63332 Oracle8i Performance Tuning: Managing Memory and Disk I/O
# Course 63333 Oracle8i Performance Tuning: Optimizing Sorts and Minimizing Contention Certification: This curriculum will help candidates prepare for exam number 1Z0-024.

Learn To
See individual course descriptions for specific course objectives and scope.


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 experience using Oracle Enterprise Manager (OEM). This course will help candidates prepare for the Oracle8i Certified Database Administrator (DBA) Track Exam 4, 1Z0-024.

Deployment Options
e-Learning

Accreditation
NASBA credits: 4 CPE Credits Per Course
CEU credits: 2.10 CEUs

Language Options
US English

Total Learning Time
18 to 24 hours

Oracle 8i Performance Tuning: Strategies and Techniques Training CD
Course Overview
This course is the first in a three-part Oracle8i Performance Tuning series. This interactive course will introduce participants to tools and techniques used to improve the performance of the Oracle8i server. Participants will be introduced to files and events that support the tuning process. In addition, students will learn considerations for tuning applications. Finally, students will overview the operating system tuning issues.

Learn To
# Identify the roles of individuals involved in the tuning process.
# Identify different tuning goals.
# Define the steps associated with the tuning process.
# Identify the location and usefulness of the alert log file.
# Identify the location and usefulness of the background and user process trace files.
# Identify different types of events and manage the event system using OEM.
# Collect statistics using the dynamic troubleshooting and performance views.
# Diagnose statistics using the UTBSTAT/UTLESTAT output report.
# Identify appropriate OEM tuning tools.
# Use the available data access methods to tune the logical design of the database.
# List the primary steps for operating system tuning.
# Identify similarities between operating system and database tuning.


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 experience using Oracle Enterprise Manager (OEM). This course will help candidates prepare for the Oracle8i Certified Database Administrator (DBA) Track Exam 4, 1Z0-024.

Deployment Options
e-Learning

Accreditation
NASBA credits: 4 CPE Credits
CEU credits: 0.70 CEUs

Language Options
US English, French, Chinese, Korean

Total Learning Time
6 to 8 hours

Objectives
Unit 1: Tuning the Oracle8i Server: Overview 1.5 - 2 hours
# Match the tuning goals with the appropriate job role.
# Identify appropriate tuning goals.
# Sequence the steps associated with tuning an Oracle8i database.


Unit 2: Alert Log, Trace Files, and Events 1.5 - 2 hours
# Identify key information recorded by the alert log.
# Identify the parameters that control the location and format of the alert log file.
# Identify the purpose of generating background trace files.
# Identify the parameters that control the location and format of background trace files.
# Identify the different methods available for generating user process trace files.
# Identify the parameters that control the location and format of user process trace files.
# Identify the V$ views used to obtain wait event information.
# Identify key statistics found in event views.


Unit 3: Utilities and Dynamic Performance Views 1 - 2 hours
# Identify the functions of the DBA views used to provide data storage statistics.
# Match the dynamic performance views with the statistics they provide.
# Match the V$ views used to collect system-wide statistics with their functions.
# Match the session-related views with the statistics they provide.
# Identify the functions performed by the utlbstat.sql and utlestat.sql scripts.
# Match sections of the report.txt output with potential problems and areas of focus for tuning.
# Locate Oracle supplied scripts used to provide additional statistics.
# Order the key steps in the Oracle Expert Tuning Methodology.


Unit 4: Considerations for Tuning Applications 1 hour
# Identify the key usage of b-tree indexes.
# Identify the key usage of bitmap indexes.
# Identify the key usage of reverse-key indexes.
# Identify the key usage of index organized tables.
# Identify the key usage of index clusters.
# Identify the key usage of hash clusters.
# Identify the key usage of histograms.
# Identify the features of OLTP.
# Identify appropriate storage allocation requirements for an OLTP system.
# Identify appropriate index and cluster implementations for an OLTP system.
# Identify application issues associated with an OLTP implementation.
# Identify the features of DSS.
# Identify appropriate storage allocation requirements for a DSS implementation.
# Identify appropriate index and cluster implementations for a DSS implementation.
# Identify considerations associated with access path optimization for DSS implementation.
# Identify the features of hybrid systems.
# Identify appropriate parameters required for hybrid systems.
# Identify application issues associated with hybrid systems.


Unit 5: Managing a Mixed Workload 1 hour
# Identify the features of the Database Resource Manager.
# Identify the guidelines used to administer resource consumer groups.
# Identify the processes implemented by resource plans and resource plan directives.
# Grant resource privileges required to administer the Database Resource Manager.
# Identify the features of the temporary workspace used by the Database Resource Manager.
# Create the resource consumer groups used by the Database Resource Manager.
# Create resource plans and resource plan directives used to distribute processing resources among different users.
# Retrieve specific information on existing objects being used by the Database Resource Manager.


Oracle 8i Performance Tuning: Managing Memory and Disk I/O Training CD
Course Overview
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.

Learn To
# 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.


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.

Deployment Options
e-Learning

Accreditation
NASBA credits: 4 CPE Credits
CEU credits: 0.70 CEUs

Language Options
French, Korean, US English, Chinese

Total Learning Time
6 to 8 hours

Objectives
Unit 1: Tuning SQL 1.5 - 2 hours
# 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 1.5 - 2 hours
# 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 1 - 2 hours
# 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 1 hour
# 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 1 hour
# 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.


Oracle 8i Performance Tuning: Optimizing Sorts and Minimizing Contention Training CD
Course Overview
This course is the third in a three-part Oracle8i Performance Tuning series. This interactive course introduces the participants improving performance for sort operations. The student will be able to identify the SQL operations that require sorts, ensure that sorting is done in memory where possible, use direct writes for large sorts and allocate temporary space appropriately. The student will use Oracle tools to diagnose and resolve contention, given the possible causes of contention. Finally, the participants will be introduced to the features of Oracle Expert, create a tuning session, gather, view and edit the input data, analyze the collected data using rules, review tuning recommendations and implement tuning recommendations.

Learn To
# Determine the appropriate data block size.
# Optimize space usage within blocks.
# Detect and resolve row migration.
# Monitor and tune indexes.
# Recognize, diagnose, and resolve performance problems associated with sorts.
# Recognize, diagnose, and resolve performance problems associated with rollback segments.
# Define levels of locking and list the possible causes of contention.
# Use Oracle utilities to detect and resolve lock contention.
# Prevent locking problems and recognize errors arising from deadlock.
# Gather data and tune a database using Oracle Expert.


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) and Oracle8i Performance Tuning: Managing Memory and Disk I/O (63332). This course will help candidates prepare for the Oracle8i Certified Database Administrator (DBA) Track Exam 4, 1Z0-024.

Deployment Options
e-Learning

Accreditation
NASBA credits: 4 CPE Credits
CEU credits: 0.70 CEUs

Language Options
French, Korean, Chinese, US English

Total Learning Time
6 to 8 hours

Objectives
Unit 1: Using Oracle Data Blocks Efficiently 1.5 - 2 hours
# Match the component of database storage with its definition.
# Avoid dynamic allocation of extents by using the ALTER TABLE command.
# Avoid the dynamic allocation disadvantages by creating a locally managed tablespace.
# Select the advantages of incorporating large extents into the database design.
# Identify the characteristics of the Oracle block size.
# Identify the advantages and disadvantages of small data block and large data block sizes.
# Identify the functions of the PCTFREE and PCTUSED parameters.
# Identify the guidelines for setting the PCTFREE parameter.
# Calculate and set the PCTFREE parameter when creating a table.
# Identify the guidelines for setting the PCTUSED parameter.
# Calculate and set the PCTUSED parameter when creating a table.
# Identify the characteristics of row migration and chaining.
# Query a table to detect chained and migrated rows by using the ANALYZE TABLE command.
# Remove the chained and migrated rows from a given table by using a four-step procedure.
# Reclaim the space from the high water mark by using an ALTER TABLE command.
# Query the results of an ANALYZE command to evaluate Oracle data block usage.
# Monitor the space used for an index by using the ANALYZE INDEX command.
# Rebuild an index without writing the redo log entries by using the ALTER INDEX command.


Unit 2: Optimizing Sort Operations 1.5 - 2 hours
# Identify the operations that cause a sort.
# Sequence the steps in the sort process.
# Set the SORT_AREA_SIZE parameter by using the ALTER SESSION command.
# Identify the tuning sort goals.
# Select the actions that avoid unnecessary sorts.
# Query the V$SYSSTAT view and display the memory, disk, and row sort values.
# Display the ratio of disk sorts to memory sorts using V$SYSSTAT.
# Create a temporary tablespace using the CREATE TABLESPACE command.
# Identify the methods that can be used to configure temporary tablespaces.
# Query the number of extents and the maximum number of blocks for sorts using V$SORT_SEGMENT.
# Join the V$SESSION and V$SORT_USAGE views to obtain information on currently active disk sorts.


Unit 3: Tuning Rollback Segments 1 - 2 hours
# Match the uses of rollback segments with their definitions.
# Select the characteristics of rollback segment activity.
# Identify the characteristics of rollback segment growth.
# Match the transaction-level type with its definition.
# Identify the goals of tuning rollback segments.
# Match the dynamic view name with its content.
# Diagnose rollback segment header contention by using the V$ROLLSTAT view.
# Diagnose rollback segment contention by using the V$WAITSTAT and V$SYSSTAT views.
# Assign a rollback segment to a transaction by using the SET TRANSACTION command.
# Select the guidelines for sizing efficient rollback segments.
# Estimate the volume of rollback data by querying the V$TRANSACTION and V$SESSION views.
# Estimate the rollback data volume by querying the V$ROLLSTAT view before and after a test transaction.
# Identify the actions that reduce the amount of rollback.
# Identify the causes of problems associated with rollback segments.


Unit 4: Monitoring and Detecting Lock Contention 1 hour
# Identify the locking mechanism characteristics.
# Select the statements that characterize DML and DDL locks.
# Identify the DML lock characteristics.
# Identify the characteristics of the automatic table lock modes.
# Match the manual table lock mode with its characteristic.
# Manually lock a table by using the LOCK TABLE command.
# Match the DDL lock types with their associated characteristics.
# Display the object ID of a locked object by querying the V$LOCKED_OBJECT view.
# Monitor lock contention by using the V$LOCK view.
# Display all the locks held by using TopSessions.
# Kill a session to resolve lock contention by using the ALTER SYSTEM KILL SESSION SQL command.
# Identify the characteristics of deadlocks.


Unit 5: Tuning with Oracle Expert 1 hour
# Sequence the steps of the Oracle Expert tuning methodology.
# Match the Oracle Expert database tuning types with the scenarios in which they are used.
# Launch Oracle Expert by using the Tuning Pack drawer icon.
# Create a tuning session by using Oracle Expert.
# Match the tuning session scope option with its use.
# View the collection classes available for data collection by using Oracle Expert.
# Set the Database class collection options by using Oracle Expert.
# Change the duration and sample frequency when collecting instance statistics by using Oracle Expert.
# Set the Schema class data options by using Oracle Expert.
# Identify the characteristics of Environment class data.
# Set the Workload class data by using Oracle Expert.
# View the rules by using Oracle Expert.
# Edit the rules by using Oracle Expert.
# Analyze the collected data by using Oracle Expert.
# View the rationale associated with a tuning recommendation by using Oracle Expert.
# Sequence the steps to save an Oracle Expert report.
# Generate the recommended implementation files by using Oracle Expert.