oracle 11g SQL performance tuning

By attending Oracle SQL Performance Tuning workshop, Participants will learn:

  • Tuning Database Applications for Optimal Performance
  • Managing Statistics
  • Creating and Using Indexes
  • Structuring SQL Statements for Performance
  • Using the SQL Optimizers
  • Examining the Execution Plan of a SQL Statement using EXPLAIN PLAN
  • Examining the Efficiency of SQL Statements using SQL Trace and Autotrace
  • Using Hints to Influence Execution Plan
  • Identifying Unused Indexes

Oracle SQL Performance Tuning Training Course introduces Participants to the main concepts of Oracle SQL performance tuning. This course is designed to give practical experience in analyzing and tuning the performance of SQL. This course is suitable for users of Oracle Database 10g and Oracle Database.

  • Oracle SQL knowledge
  • Oracle PL/SQL knowledge

Oracle SQL Performance Tuning class is designed for SQL programmers, application developers/designers and technical support professionals who are required to tune the performance of an Oracle application running under Oracle 9i, 10g or.

COURSE AGENDA

  • Physical Retrieval of Data
  • Full Table Scan versus Index Reads
  • Performance Diagnostic Tools
  • Explain Plan
  • SQL Trace Facility
  • TKPROF
  • Autotrace
  • Join Methods
  • Sort/Merge Joins
  • Nested Loops
  • Hash Joins
  • Hash Join Example Data Access Methods
  • Data Design Phase
  • Data Model Design
  • Online Transaction Processing
  • Decision Support Systems
  • Multi-purpose Applications
  • Cause of Performance Problems
  • Setting Performance Goals
  • The Tuning Cycle
  • ORACLE Architecture
  • Logical Storage Structures
  • Physical Structures
  • Memory Structures
  • The Shared Pool
  • Processes
  • Some Administration Terminology
  • SQL Troubleshooting Tips and Techniques
    • Index Review/Tips & Techniques
      • Identify good and poor indexes
      • Monitor Index Usage
      • Using Bit-map Indexes
    • A close look at sub-query coding techniques
  • Basic Indexes
  • B-Tree Indexes
  • Bitmap Index
  • Comparing B-Tree and Bitmap Indexes
  • Reverse Key Index
  • Index-Organised Tables
  • Invisible index
  • Creating Monitoring and Maintaining Indexes
  • Query Optimizer Modes Types of Tuning Analysis
  • Automatic workload repository SQL Tuning Advisor SQL Tuning Sets Top SQL
  • Identify high-load SQL Dynamic Performance views
  • Describe SQL Performance Analyzer process and benefits
  • Use SQL Performance Analyzer
  • The ORACLE Optimizer
  • SQL statement parsing
  • Initialisation parameters
  • Rule Based Optimizer
  • Cost Based Optimizer
  • Rule / Cost Comparisons
  • Choosing an Approach
  • Multiple WHERE Clauses
  • Using Indexes for Sorts
  • Multiple Table Joins
  • Disabling Indexes
  • Hints
  • Sharing SQL Statements
  • Sharing Cursors
  • Adaptive Cursors Sharing
  • Other SQL Tuning Tips
  • Analyzing Statistics
  • DBMS_STATS
  • Automatic Optimizer Statistics Collection
  • Histograms
  • How to Generate Histograms Statistics
    • Tuning the SGA (including new topics)
    • Additional Oracle Hash and Sort Trace information
    • Oracle Internals: How Oracle writes
      • Various Space Management Issues discussed
      • Monitoring Sorting
    • Finding Problem SQL using V$ information
  • Tuning the network/Tuning the client
  • SQL outlines
  • SQL profiles
  • SQL Access Advisor
  • Set up and use SQL plan baseline
  • Star Queries
  • Materialized Views
  • Refreshing Views
  • Materialized View Logs
  • SQL Result Cache
  • Temporary Tables
  • Identify dynamic performance views useful in tuning
  • Identify key tuning components of the alert log file
  • Identify key tuning components of user trace files
  • Use dynamic performance views to view statistics and wait events
  • View metrics using the metrics history views
  • Create metric thresholds
  • View alerts
  • Create metric baselines
  • Enable adaptive thresholds
  • Install Statspack
  • Create Statspack snapshots
  • Generate Statspack reports
  • Identify the major sections of the Statspack report
  • Create and manage AWR snapshots
  • Generate AWR reports
  • Create snapshot sets and compare periods
  • Generate ADDM reports
  • Generate ASH reports
  • Using the SQL Tuning Advisor