Oracle9i: Database Performance Tuning

What you will learn:
Database performance tuning is critical to any effective information system. Reducing wait times, increasing users' productivity and maximizing the performance of your Oracle database are critical to success. Learn from the experts the value of intelligent database design and methods for tuning an Oracle database for maximum performance. This course will introduce participants to the importance of good initial database design, and the method used to tune a production Oracle 9i database.

The focus is on Database and Instance tuning rather than specific operating system performance issues. Participants will gain practical experience tuning an Oracle database. Using the available Oracle tools, such as Oracle Enterprise Manager (with the Diagnostics and Tuning Packs) and STATSPACK participants also learn how to recognize, troubleshoot and resolve common performance related problems in administering an Oracle database.

Why Oracle 9i training from Mapple Edusoft :

• Official Course-ware from Oracle University.
  
• Authorized Structured Oracle Training in Jaipur.
• Highly Qualified Java Industry Experts as Trainer.
• Hands on experience on Oracle Software.
  
• Course Completion Certificate From Oracle University.
  
• Discount Vouchers for International Exams.
• 10 Full-length Online Mock Tests (Real-life Oracle® Exam) per Module.
  

Prerequisites :

• Oracle 9i: Introduction to SQL
  Oracle 9i Database: Administration Fundamentals 1
  Oracle 9i Database: Administration Fundamentals 2
  

Course Topics :
Overview of Oracle 9i Performance Tuning

• Job Roles in Tuning
  
• Tuning phases
  
• Tuning goals and Service Level Agreements
  
• Common performance problems
  
• Tuning Methodology
  

Diagnostic and Tuning Tools

• Alert log file
  
• Background process trace files
  
• User trace files
  
• Dictionary views providing statistics
  
• Dynamic performance views
  
• TIMED_STATISTICS parameter to collect statistics
  
• Statistics Package
  
• STATSPACK procedures
  

Sizing the Shared Pool

• Overview of the shared pool
  
• Library cache tuning
  
• Reuse statements
  
• Using Reserved Space
  
• Keeping Large Objects
  
• Related tuning issues
  
• Data Dictionary Cache (DDC) Tuning
  

Sizing the Buffer Cache

• Overview of tuning the buffer cache
  
• Buffer Cache Sizing Parameters in Oracle9i
  
• Depreciated Buffer Cache Parameters
  
• Buffer Cache Advisory Parameter
  
• Dynamically resizing SGA components
  
• Granules of Allocation
  
• Increase the size of a SGA component
  
• Resolving Techniques
  

Sizing other SGA Structures

• Sizing the redo log buffer
  
• Detecting contention
  
• Resolving contention
  
• Sizing the Java Pool
  
• Monitoring Java Pool Memory
  
• Sizing the SGA for Java
  
• Sizing Java Pool Memory
  
• Limiting Java Session Memory Usage
  

Database Configuration and I/O Issues

• Distributing files across devices
  
• Tablespace usage
  
• Detecting improper tablespace usage
  
• Moving datafiles
  
• Oracle File Striping
  
• Tuning Full Table Scans
  
• Tuning checkpoints
  
• Redo log file configuration
  

Optimize Sort Operations

• What is the sorting process
  
• Sort Area Parameters (New & Old)
  
• Tuning sort operations (DB Configuration)
  
• Tuning Temporary segments
  
• Reducing sort operations (application tuning)
  
• Identify sort operations
  
• Avoiding sort operations
  
• Diagnostic Tools
  

Diagnosing Contention For Latches

• Oracle's Implementation of latches
  
• Latch request types - willing-to-wait, immediate
  
• Reducing Latch Contention
  
• Significant Latches
  
• Shared Pool and Library Cache Latches
  

Tuning Rollback (or UNDO) Segments

• Describing Rollback segment usage
  
• Usage and configuration
  
• Detecting Problems with Rollback Segments
  
• Resolving Problems with Rollback Segments
  
• Creating Rollback Segments and bringing them online
  
• Allocating transactions to a rollback segment
  
• Resolving RBS problems
  
• Describing System Managed Undo
  

Monitoring and Detecting Lock Contention

• Concepts of Locking
  
• Overview of Locking issues
  
• Types of DML locks
  
• Levels of locks
  
• Types of DDL lock modes
  
• Detecting Blocking Locks
  
• Monitoring locking activity
  
• Deadlocks
  

Tuning Oracle Shared Server

• Introducing Oracle Shared Server
  
• When are Shared Servers required in Oracle9i
  
• Monitoring dispatcher processes
  
• Monitoring shared server processes
  
• Increasing or decreasing the number of Dispatchers
  
• Increasing or decreasing the number of Shared Servers
  
• Monitoring Process Usage
  
• Monitoring Memory Usage
  

Application Tuning

• Role of the DBA
  
• Oracle Data Structures
  
• Selecting the physical structure
  
• Data storage structures
  
• Clusters
  
• Indexes
  
• B-Tree
  
• Compressed
  

Using Oracle Blocks Efficiently

• Database Storage hierarchy
  
• Allocating extents
  
• Monitoring Space Usage in Data Segments
  
• Recovering space from sparsely populated segments
  
• Database Blocks
  
• Chaining and migration
  
• Detecting row chaining and migration
  
• Resolving row chaining and migration
  

SQL Statement Tuning

• Cost-based, Rule-based Optimiser Modes
  
• New Cost Based Optimiser option
  
• Plan Stability
  
• Plan Equivalence
  
• Stored Outlines
  
• Creating Stored Outlines
  
• Maintaining Stored Outlines
  
• Hints
  

Tuning the Operating System and Using Resource Manager

• System Architecture
  
• Virtual and Physical Memory
  
• Paging and Swapping
  
• CPU Tuning Guidelines
  
• Process Vs Thread
  
• Resource Manager Concepts
  

Certificate From Oracle University :

Oracle Training in Jaipur - DBA, Developer - OCA -OCP

Authorized Oracle 9i Training in Jaipur @ Mapple Edusoft