Oracle Database 11g Performance & Tuning

Level
Total time

Oracle Database 11g Performance & Tuning

Perpetual Solutions
Logo Perpetual Solutions
Provider rating: starstarstarstarstar 10 Perpetual Solutions has an average rating of 10 (out of 1 reviews)

Need more information? Get more details on the site of the provider.

Starting dates and places

There are no known starting dates for this product.

Description

Prerequisites:

Delegates should have a working knowledge of SQL*Plus and Oracle Database Administration. This can be obtained by attendance on the ?¢‚Äöá®?¨Oracle SQL and ?¢‚Äöá®?¨Oracle Database 11g DBA Part I" or Oracle Database 10g DBA Part I" courses. Knowledge of PL/SQL is desirable. Some operating system experience is essential.

Course Description:

Delegates will be introduced to the main concepts of Oracle performance tuning and will gain practical experience in analysing and tuning database performance. They will learn to identify tuning contentions and be able to take corrective action. By the end of the course, delegates will be able to use different tuning methodologies; use the data…

Read the complete description

Frequently asked questions

There are no frequently asked questions yet. If you have any more questions or need help, contact our customer service.

Didn't find what you were looking for? See also: Oracle 11G, Oracle 10G, Oracle, CompTIA A+ / Network+ / Security+, and IT Security.

Prerequisites:

Delegates should have a working knowledge of SQL*Plus and Oracle Database Administration. This can be obtained by attendance on the ?¢‚Äöá®?¨Oracle SQL and ?¢‚Äöá®?¨Oracle Database 11g DBA Part I" or Oracle Database 10g DBA Part I" courses. Knowledge of PL/SQL is desirable. Some operating system experience is essential.

Course Description:

Delegates will be introduced to the main concepts of Oracle performance tuning and will gain practical experience in analysing and tuning database performance. They will learn to identify tuning contentions and be able to take corrective action. By the end of the course, delegates will be able to use different tuning methodologies; use the database advisors to proactively tune a database; use AWR tools to tune a database; detect and tune common database performance problems and use Enterprise Manager to monitor an Oracle database. This course covers the main topics of the Oracle beta test examination 1Z1-054 Oracle Database 11g Performance Tuning Certified Expert,

This course includes the following modules:

Introduction to Oracle Performance Tuning

  • Tuning Overview
  • Resolving Performance Problems
  • The Oracle Approach to Tuning
  • Items to Evaluate
  • Where to Look
  • Plan a Routine Monitoring Regime
  • Build a New Database for Performance
  • Tune an Existing Database for Performance
  • Set Suitable Goals

Diagnosing Problems

  • Diagnostic Sources
  • Log a Performance Service Request (SR)
  • Tips for Avoiding Problematic Queries

Tools For Evaluating SQL Statements

  • Overview of SQL Statement Tuning
  • Explain Plan and autotrace
  • SQL Trace and the tkprof Program
  • Interpreting SQL Trace

The Optimizer

  • The SQL Optimizer
  • The Rule Based Optimizer
  • The Cost Based Optimizer
  • The Analyze Command
  • Manage Statistics with DBMS_STATS

Indexes

  • Index Overview
  • B*Tree Indexes
  • Access Paths with Indexes
  • Index Scans
  • Conditions which Stop Indexes Being Used
  • Parameters Affecting Optimizer Index Choice

Advanced Indexes

  • Bitmap Indexes
  • Key Compressed Indexes
  • Index Organized Tables
  • Function-Based Indexes
  • Descending Indexes

Types Of Table Joins

  • Joining Tables
  • Nested Loops Join
  • Cluster Join and Hash Join
  • Anti Join and Semi Join
  • Outer Join
  • Star Join

Metrics, Alerts and Baselines

  • System Generated Alerts
  • Threshold Based Alerts
  • Event Based Alerts
  • Retrieve Threshold Information
  • View Alert Data
  • Manage Metrics Using EM
  • Blackouts
  • Baselines and Adaptive Thresholds

Statspack

  • Overview of Statspack
  • Statspack Architecture
  • Installing Statspack
  • Adjust the Statspack Collection Level
  • Create, View and Delete Snapshots
  • Generate a Report
  • Analyze the Reports to Generate an Execution Plan for a Given SQL Statement
  • Resolve Wait Events
  • Remove Statspack from the Database

Automatic Workload Repository

  • Automatic Workload Repository
  • Automatic Workload Repository
  • Create Snapshots and Compare Snapshots
  • Use Baselines
  • Generate AWR Reports
  • Compare AWR Periods Reports
  • Generate Active Session History (ASH) Report
  • Automatic Database Diagnostic Monitor (ADDM)
  • Generate and Analyze ADDM Reports

Reactive Tuning

  • Use Enterprise Manager to Identify Common Problems
  • Eliminate Operating System Contentions

Tuning The Shared Pool

  • Overview of the Shared Pool
  • Alter and Tune the Shared Pool Size
  • Tune the Data Dictionary Cache and the Library Cache
  • Shared Pool Advisory Statistics
  • Pin Objects in the Shared Pool
  • Cursor Sharing
  • Using the Large Pool
  • Latches and Mutexes

Tune The Buffer Cache

  • Memory Structures
  • Data Block Buffer Pool
  • Multiple Buffer Caches
  • Manage Free Buffers
  • Establish the Buffer Pool Efficiency
  • Cache Tables in the Buffer Pools
  • Common Performance Issues and the Buffer Cache

Automatic Memory Management

  • Automatic Memory Management
  • Automatic Shared Memory Management
  • Memory Allocation Advisors
  • The Memory Advisor
  • The Shared Pool and Buffer Cache Advisors

Tune Pga and Temporary Space

  • PGA Overview
  • Automatic PGA Mode
  • Configure the PGA for a New Instance
  • The Program Global Area Advisor
  • PGA Management Data Dictionary Views
  • Tune the PGA_AGGREGATE_TARGET
  • SQL Operations that Use Sorts
  • Sorting to Disk
  • Multiple Temporary Tablespaces
  • Allocate Temporary Disk Space for Sorting
  • Temporary Tablespace Shrink
  • Data Dictionary Views and Sort Segments

Checkpoint and Redo Tuning

  • Checkpoint and Redo Issues
  • Instance Recovery Performance Tuning
  • Redo Log File Size Advisor
  • Size the Redo Log Buffer
  • Set Multiple Database Writers

Tune I/O

  • Database I/O Management
  • Layout the Files Using OS or Hardware Striping
  • Manually Distribute Files to Reduce I/O Contention
  • When to Separate Files
  • Sample Configurations
  • Select the Data Block Size
  • Synchronous I/O Benefits
  • FILESYSTEMIO_OPTIONS Parameter
  • Automatic Storage (ASM) Monitoring

Use Oracle Blocks Efficiently

  • Determine the Database Block Size
  • Tablespace, Table and Index Storage Parameters
  • Automatic Segment Space Management
  • Detect and Resolve Freelist Contention
  • Rebuild Tables and Indexes
  • The DBMS_SPACE Package
  • Row Migration
  • Chained Rows
  • Bigfile Versus Smallfile Tablespaces
  • Extended ROWID Format
  • Convert from Dictionary Managed Tablespaces
  • Convert to Automatic Segment Space Management

Materialized Views

  • Overview of Materialized Views
  • Materialized View Creation
  • Materialized View Refresh
  • Query Rewrite
  • Materialized View Data Dictionary Views

Parallel Execution

  • Overview of Parallel Execution
  • Enabling and Using Parallel Execution
  • The Degree of Parallelism
  • SQL Hints
  • Restrictions on the Use of Parallel Execution
  • Dictionary Views on Parallel Execution
  • Specific Automatic Parallel Query Optimization
  • Nologging Options

Tracing Services

  • End to End Tracing
  • Enable and Disable Tracing
  • Check for Tracing Enabled
  • The trcess Utility
  • Session Based Tracing
  • Enable and View SQL Tracing with Enterprise Manager
  • Analyze Trace Results

New 11g Sql and Pl/Sql Tuning Features

  • SQL Query Result Cache
  • PL/SQL Function Cache
  • Adaptive Cursors
  • Invisible Indexes

Database Replay

  • Overview of Database Replay
  • The Database Replay Process
  • Capture a Workload
  • Pre-process the Captured Workload
  • Replay the Workload
  • Database Replay Usage
  • The DBMS_WORKLOAD Packages

Sql Performance Management And Analysis

  • Statistics Gathering
  • SQL Performance Analyzer
  • SQL Plan Management
  • Automatic SQL Tuning
  • Manual Creation and Use of SQL Tuning Sets

For online live training advice please visit our Learning Advice Centre on our website. Be sure to follow us on Twitter to receive special course offers, news and updates!

There are no reviews yet.

Share your review

Do you have experience with this course? Submit your review and help other people make the right choice. As a thank you for your effort we will donate £1.- to Stichting Edukans.

There are no frequently asked questions yet. If you have any more questions or need help, contact our customer service.