Oracle 9i Database Performance Tuning

Who should attend | Prerequisites | Content | Sample Notes | Next Class | Printer Friendly 

Class Summary

The Oracle9i Database Performance Tuning class offers delegates the opportunity to discuss and examine potential tuning bottlenecks that may impact performance of an Oracle database. The class focuses on the tuning required for a production level database instance and describes memory, disk and process tuning in detail.

Who Should Attend

This in-depth class is intended for technical users of the Oracle database system who are responsible for ensuring peak performance in an intensive production environment. This event can also be delivered over a shorter period for more experienced Oracle DBA's - please see our FastTrack performance tuning class outline.

Prerequisites

Experience with the role of an Oracle Database Administrator, including practical expertise in using SQL and PL/SQL.

Each student will require their own copy of Oracle9i Enterprise Edition, with the default 'ORCL' database as created during installation by the Database Configuration Assistant. Setup assistance can be provided if required - this may involve additional cost if a site visit is required prior to the course.

Content

• Oracle RDBMS Architecture: Objectives, Memory, Process and Disk Structure, Disk Structure, Parameter File, Control File, Data Files, Redo Log Files, Password File, Logical / Physical Structures, Segments, Extents, Blocks, Memory Structure, Shared Pool, Buffer Cache, Redo Log Buffer, Process Structure, User and Shadow Processes, Database Writer, Log Writer, System Monitor, Process Monitor, Archiver, Processing a query, Processing an Update, Processing a transaction.
  
  
• Tuning Principles: Objectives, Planning to Tune, What is to be tuned?, What is needed?, What is the tuning target?, How to achieve your goals.
  
  
• Tuning Tools and Utilities: Objectives, Tools Overview, Oracle Enterprise Manager, OEM Packs, Alert Log, Trace Files, Static Dictionary Tables, Dynamic Performance Views, UTLBSTAT/UTLESTAT, Statspack.
  
  
• Application and SQL Tuning: Objectives, DBA Role, Application and SQL Tuning Tools, Oracle SQL Optimiser, Rule-based v. Cost-based optimiser, Optimiser mode setting, Hints, EXPLAIN PLAN Statement, Generating an Execution Plan, Displaying Explain Plan Results, OEM SQL Analyze Tool, Understanding an Execution Plan, SQL Tracing, User Trace File, Analysis with TKPROF, AUTOTRACE Setting, OEM SQL Analyze Tool.
  
  
• Indexes and Access Methods: Objectives, ROWID, Indexing Options, B-Tree Indexes, B-Tree Index Commands, Bitmap Indexes, Bitmap Index Commands, Indexing Strategies, Foreign Key Indexes, Index Usage Monitoring, Index Usage Monitoring Operations, Skip scan index accessing, Skip scan operations, Index Compression, Index Efficiency, Index Organised Tables.
  
  
• Understanding Statistics: Objectives, Table and Index Statistics, System Statistics, Statistics Gathering Mechanisms, DBMS_STATS Package, DBMS_STATS Procedures, System Statistics Procedures, Executing System Stats Procedures, Dictionary Statistics Procedures.
  
  
• Shared Pool Tuning: Objectives, Instance Memory Structures, Memory Management, Dynamic Memory Allocation, OEM Shared Pool Advisor, Shared Pool Management, DBMS_SPOOL package, Shared Pool Statistics, Shared Pool Statistics Terminology, Shared Pool Library Cache Hit Ratios, Library Cache Cursor Sharing, Checking running SQL, Dictionary Cache, Interpreting and Actioning Statistics, Determining Library Cache Size, Exploring Shared Pool Resizing, Adjusting Shared Pool, Large Pool, Java Pool, Performance Manager Overview.
  
  
• Buffer Cache Tuning: Objectives, Buffer Cache Overview, Multiple Block Sizes, Buffer Cache Tuning Outline, Running Cache Advisory Statistics, Sample Cache Advisory Output, OEM Buffer Cache Advisory, Measuring Buffer Cache Hit Ratio, Split Blocks By Access Frequency, Identifying KEEP/RECYCLE Candidates, Other Buffer Cache Tuning Tips.
  
  
• Redo Log Buffer Tuning: Objectives, Redo Log Buffer, Redo Log Buffer Size, Redo Log Buffer Tuning Goals, Using the Redo Log Buffer, Measuring Waits, Resolving Waits, Other Tuning Methods.
  
  
• Disk File Tuning: Objectives, Data Files, Processes and Data Files, Data File Tuning Goals, Optimise disk I/O by tablespaces, Using Tablespaces Effectively, Separate busy tables from each other, Tablespace Types, Extent Management, Segment Space Management, Setting Up ASSM, Checking Tablespace Usage, Plan UNDO/ROLLBACK tablespace usage, Redo Log File Tuning Goals, Checkpoints.
  
  
• Undo Segment Tuning: Objectives, Using Rollback Segments, Rollback Segment I/O, Rollback Segment Extensions, Rollback Segment Tuning Goals, Avoiding Waits, Avoiding Extentions, Avoiding running out of space (Rollback Segments), Avoiding loss of read-consistent images, Automated Undo Segments, Converting from RBS to AUM.
  
  
• Data Block Tuning: Objectives, Database Storage Ladder, Block Tuning Guidelines, Tablespace types, Dynamic extent allocation, Database Block Sizing, Block Packing Factor, Chaining and Migration, High Water Mark, Space Recovery Mechanisms, Index Reorganisation, Index Reorganisation Views.
  
  
• Process Tuning: Objectives, Sorting Overview, Sorting Mechanisms, Sort Operations, Sort Tuning Goals, Measuring Sorts, Oracle Latches, Investigating Latches, Latch Issue Resolution, Locking Overview, Lock Types, Locking Issues, Locking Diagnostics, Resolving Locking Issues.
  
  
• Operating System Tuning: Objectives, OS Architectures, Virtual Memory and Paging, OS Tuning Guidelines, OS Specific Issues, Resource Manager Overview, Tuning Methods and Defaults, System Plan, Consumer Groups, Resource Manager Packages, Plan Structure, Plan Activation, Resource Manager Procedure, Managing Resource Plans, Monitoring Resource Manager.
  
  
• Q & A.
  
  

Sample notes

Lesson 6 - Indexing

Suggested next class

Please contact us for further details of our ad-hoc consultancy and individualised training programmes.