|
Description
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.
Audience
The intended audience for this course includes database administrators and application developers, regardless of role or experience level.
Prerequisites
(Currently no course prerequisite information)
Objective
- 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.
Topics Include
Unit 1: Using Oracle Data Blocks Efficiently - 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 - 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 - 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 - 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 - 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.
Duration
8 Hours
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).
The network 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 and XXMB available hard disk space or file server space.
- At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).
Media
|
