Oracle 9i Certification Training
Oracle Certification Training 9i Performance Tuning: Strategies, Techniques
Includes:
Oracle 9i Training for Certification Performance Tuning: Strategies, Techniques Course on CD-ROM
|
Intro. to Oracle Certification Training 9i - SQL Curriculum CD
|
n61130d
|
$387.00
|
|
|
Database Admin. Fundamentals I CD
|
n64311d
|
$645.00
|
|
|
Database Admin. Fundamentals II CD
|
n64321d
|
$387.00
|
|
|
Oracle 9i Performance Tuning CD
|
n64331d
|
$387.00
|
|
|
Complete Oracle Certification Training 9i CBT CD
includes SQL Curriculum, Database Admin. Fundamentals I
& II Curriculum and Oracle 9i Performance Tuning Curriculum
|
n61131d
|
$1100.00
|
|
Oracle 9i Certification Training Performance Tuning: Strategies, Techniques
 In this course, you are introduced to the strategies and
techniques used for tuning the Oracle9i server.
Learn To
• Match the tuning goals with the appropriate job role.
• Identify common tuning problems that directly affect database performance.
• Identify appropriate tuning goals.
• Identify key information recorded by the alert log.
• Identify the parameters that control the location and format of the alert log file.
• Identify the different methods available for generating user process trace files.
• 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.
• Identify the factors taken into consideration in selecting the physical structure for data.
• Match the index types with their benefits.
• Identify the key usage of index organized tables.
• 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.
• Identify the measures that can be taken to tune memory performance.
• Identify the I/O tuning areas that can improve overall system performance.
• Identify the CPU tuning practices that can improve overall system performance.
Objectives
Unit 1: Tuning the Oracle9i Server: Overview 1 - 2 hours
• Match the tuning goals with the appropriate job role.
• Identify common tuning problems that directly affect database performance.
• Identify appropriate tuning goals.
• Sequence the steps associated with tuning an Oracle9i database.
Unit 2: Alert Log, Trace Files, and Events 1 - 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 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 hour
• 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.
• Identify the functions of the STATSPACK package.
• Locate Oracle supplied scripts used to provide additional statistics.
• Order the key steps in the Oracle Expert Tuning Methodology.
• Match the performance manager charts with their monitored information.
Unit 4: Considerations for Tuning Applications 1 hour
• Identify the factors taken into consideration in selecting the physical structure for data.
• Match the index types with their benefits.
• Identify the key usage of index organized tables.
• Match the cluster types with their implementation.
• Identify the key usage of histograms.
• Identify the functions of materialized views.
• 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.
Unit 6: Other Tuning Considerations 1 hour
• Identify the measures that can be taken to tune memory performance.
• Identify the I/O tuning areas that can improve overall system performance.
• Identify the CPU tuning practices that can improve overall system performance.
• Identify the benefits of the GET_DDL function of the DBMS_METADATA package.
• Match the performance areas with their focus areas.
top
Oracle9i Performance Tuning: Managing Memory and Disk I/O
This course introduces students to the importance of good initial database design, and the methods used to tune an Oracle9i production database. Students learn how to use the available
Oracle tools to recognize, troubleshoot, and resolve common performance-related problems in administering an Oracle database.
Learn To
• 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.
Objectives
Unit 1: SQL Statement Tuning 1 - 2 hours
• 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 1 - 2 hours
• 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 2 hours
• 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 2 hours
• 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
top
Oracle9i Performance Tuning: Optimizing Sorts and Minimizing Contention
The content of this course includes details about each component of an Oracle database instance, such as data blocks, disks, memory, CPU, and dynamic views. Students learn about
migration and chaining, reorganizing indexes, optimizing sort operations, tuning rollback segments, managing automatic undo, monitoring activities, implementing the Oracle shared server, and troubleshooting.
Learn To
• 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.
• Sequence the steps in the sort process.
• Set the SORT_AREA_SIZE parameter by using the ALTER SESSION command.
• Identify the tuning sort goals.
• Match the uses of rollback segments with their definitions.
• Select the characteristics of rollback segment activity.
• Identify the characteristics of rollback segment growth.
• Identify the locking mechanism characteristics.
• Select the statements that characterize DML and DDL locks.
• Identify the DML lock characteristics.
• Sequence the process steps of the Oracle shared server configuration.
• Identify the characteristics of the Oracle shared server.
• Query a dispatcher process to determine the process status using the V$DISPATCHER view.
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.
• 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.
• Match the actions for recovering space with their description.
• 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.
• Identify the guidelines for setting the PCTUSED parameter.
• 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.
• 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
• Sequence the steps in the sort process.
• Set the SORT_AREA_SIZE parameter by using the ALTER SESSION command.
• Identify the tuning sort goals.
• Match the properties of temporary space segments with their descriptions.
• Identify the SQL operations that require sorts.
• Identify the SQL operations that avoid sorts.
• Query the system to diagnose the ratio of disk-sorts to memory-sorts by using the SORTS() statistic.
• 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.
• 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.
• Identify the actions that reduce the amount of rollback.
• Identify the actions that reduce the amount of rollback.
• Identify the causes of problems associated with rollback segments.
• Identify the SQL clauses that alter an undo tablespace.
• Identify guidelines for switching undo tablespaces.
• Identify the properties of the DROP TABLESPACE command.
• Identify the properties of the parameters supported by the automatic undo management feature.
• Monitor undo segments using the V$UNDOSTAT view.
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 DDL lock types with their associated characteristics.
• Display the object ID of a locked object by querying the V$LOCKED_OBJECT view.
• Kill a session to resolve lock contention by using the ALTER SYSTEM KILL SESSION SQL command.
• Identify the characteristics of deadlocks.
Unit 5: Oracle Shared Server 1 hour
• Sequence the process steps of the Oracle shared server configuration.
• Identify the characteristics of the Oracle shared server.
• Query a dispatcher process to determine the process status using the V$DISPATCHER view.
• Query the database to determine the shared server status using the V$SHARED_SERVER view.
• Query a current process to determine the usage status using the V$SESSION view.
• Identify guidelines for resolving possible problems with the Oracle shared server.
• Match dynamic performance views with their view contents.
top
|
