Oracle8 Performance Tuning: Optimizing Sorts and Minimizing Contention

Audience

Database administrators system administrators applications designers and developers and technical support professionals. Prior to taking this course participants should familiar with administering an Oracle8 database or have taken the Oracle8 Database Administration series (courses 61311-61315). In addition they should understand key strategies and techniques involved in tuning an Oracle8 database or have taken the first two parts of the Oracle8 Performance Tuning series (courses 61331 and 61332).

Objective

• Determine an appropriate block size optimize space usage within blocks detect and resolve row migration and monitor and tune indexes.
• 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.
• Reconfigure and monitor rollback segments define the number and sizes of rollback segments and allocate rollback segments to transactions appropriately.
• Define levels of locking list possible causes of contention use Oracle utilities to detect lock contention resolve contention in an emergency prevent locking problems and recognize Oracle errors arising from deadlocks.
• Use Oracle tools to diagnose and resolve contention.
• Launch Oracle Expert and identify the different types of tuning.

Topics Include

Unit 1: Using Oracle Data Blocks Efficiently

• Match components of database storageunits to their definition.
• Query the dba_tables to display segments with less than 10% free blocks.
• Manually allocate an extent.
• Choose the advantages of incorporating large extents into database design.
• Identify characteristics of Oracle data block size.
• Identify characteristics of small data block and large data block sizes.
• Run scripts to create and view the report.txt.
• Identify characteristics of PCTFREE and PCTUSED parameters.
• Calculate and set the PCTFREE parameter when creating a table.
• Calculate and set the PCTUSED parameter.
• Select the situations in which Oracle8 Server coalesces the free space of a data block.
• Identify characteristics of row migration and chaining.
• Query a table to display chained rows.
• Remove migrated rows from a given table.
• Reclaim space above the high water mark.
• Query the results of a table analysis to display important information.
• Use DBMS_SPACE package to display space use in segments.
• Assess index structure and display the percentage of deleted entries.
• Rebuild an index.

Unit 2: Optimizing Sort Operations

• Identify the operations that will cause a sort.
• Sequence the steps in the sort process.
• Change the SORT_AREA_SIZE and SORT_AREA_SIZE_RETAINED parameters.
• Identify goals for tuning sorts.
• Choose the actions that avoid unnecessary sorts.
• Query the V$SYSSTAT view and display memory disk and row sort value.
• Calculate the ratio of disk sorts to memory sorts and display the results.
• Set the SORT_DIRECT_WRITES parameter to true.
• Identify situations that would improve database performance when using direct writes.
• Create a temporary tablespace.
• Query the number of extents currently allocated to sorts and the maximum number of blocks used by an individual sort.
• After analyzing temporary tablespace determine if a second tablespace for temporary sorts is needed.

Unit 3: Tuning Rollback Segments

• Match uses of rollback segments with their definitions.
• Identify where a new transaction will start placing its rollback entries given a graphic representation of rollback segment activity.
• Identify which system has well tuned rollback segments.
• Match appropriate characteristics associated with read only read write and serializable transactions.
• Select actions that improve rollback segment performance occurring on a parallel server.
• Identify situations that contribute to optimum rollback segment performance.
• Match the dynamic view names with their definition.
• Access the report.txt file and view information associated with tuning rollback segments
• Diagnose rollback segment header contention.
• Calculate the number of waits for each of block with the total number of requests for data over the same period of time.
• Calculate the number of rollback segments required when executing OLTP transactions.
• Identify storage parameters settings that increase rollback segment efficiency.
• Query the V$TRANSACTION and V$SESSION views to help estimate the volume of rollback data.
• Estimate the volume of rollback data.
• Identify actions that reduce the amount of rollback.
• Select the appropriate actions that increase the probability of successful completion when executing a large transaction.
• Adjust the configuration of your rollback segments to resolve the snapshot too old error.

Unit 4: Monitoring and Detecting Lock Contention

• Identify locking attributes.
• Label the list of characteristics as either DML or DDL lock attributes.
• Identify DML lock characteristics.
• Label the list of characteristics as either Row Exclusive or Row Share table lock modes.
• Lock a table.
• Identify table partition lock attributes.
• Match DDL locks with the correct definition.
• Identify situations that contribute to lock contention.
• View the name of locked table.
• Launch Oracle Performance Manager and display the locks currently held.
• Kill a session.
• Examine a trace file in the USER_DUMP_DEST directory.

Unit 5: Resolving Contention Issues

• Identify characteristics of contention.
• Identify which areas the DBA can tune.
• Identify different the characteristics of latch types.
• Select redo latch tuning goals.
• Identify latch contention situations.
• Identify courses of action that minimize redo latch contention.
• Identify LRU attributes.
• Identify goals associated with LRU tuning.
• Query V$LATCH and V$LATCHNAME to display information regarding latches.
• Calculate the number of hits on LRU latches and increase the number of latches.
• Identify free lists attributes.
• View information relevant to free list contention problems.
• Reduce free list contention.

Unit 6: Tuning with Oracle Expert

• Sequence the Oracle Expert tuning methodology process.
• Identify the different types of tuning.
• Launch Oracle Expert.
• Match the tuning session scope type with its definition.
• Set the scope of an instance tuning session.
• Set the scope of an application tuning session.
• Set the scope of a structure tuning session.
• View collected data in the Oracle Expert repository.
• Specify the options to display the database class data.
• Change the duration and sample frequency of collecting instance statistics.
• Access schema class data.
• View environment class data.
• View the workload class data.
• View the display and edit options for collected data.
• Locate the default rules.
• View the tuning analysis.
• View recommendations suggested by Oracle Expert.
• Select categories to be printed in a session data report.
• View a sample instance recommendation file.

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

CDROM