Home About Courses Schedule Services Webinars Contact Search

Oracle SQL Optimization for Developers and DBA's


Duration: 3 Days

Method: Instructor led, Hands-on workshops

Price: $1920.00

Course Code: OR5480

View Related Video


Oracle Developers and Oracle Database Administrators (DBAs


This course provides students with a thorough, behind the scenes look at Oracle application tuning. A discussion of basic Oracle architecture will provide the foundation for understanding both SQL statement and system performance.

This course covers Oracle versions through Oracle 18c.


OR5450 Oracle SQL or equivalent experience


  • I. Database Tuning Overview
    • Set appropriate tuning goals
    • Apply a tuning methodology
    • Identify potential tuning problems
    • Identify performance bottlenecks
  • II. Understanding the Database Architecture
    • Understand all database architecture components
    • The Database Architecture
      • Memory Structures
      • The SGA
      • The Database Buffer Cache
      • The Shared Pool
      • Other Buffer Caches
      • The KEEP Cache
      • The RECYCLE Cache
      • Non-Standard Block Size Caches
      • The Large Pool
      • The Java Pool
      • The Streams Pool
      • Background Processes
      • Checkpoints
      • Server Process
      • Client Processes
      • Database Files
      • Database Objects
      • Blocks
  • III. Automatic Shared Memory Management (ASMM & AMM)
    • Understanding Memory Management and how it impacts SQL performance
    • Enable Automatic Shared Memory
      • The SGA_TARGET and STATISTICS_LEVEL Parameters
    • The MMAN Background Process
    • Areas Managed by ASMM & AMM
    • Monitoring ASMM & AMM Events
      • Dynamic Performance Views
  • IV. SQL – Behind the Scenes
    • Listing the SQL Statement Processing Steps
      • Parsing
      • Syntax Check
      • Semantic Check
      • Hard Parse vs. Soft Parse
    • Identifying Means to Minimize Hard Parsing
      • SQL Coding Standards
      • Identifying Identical Statements
      • The CURSOR_SHARING Parameter
      • EXACT, SIMILAR, FORCE Values
      • Using Bind Variables
      • Sizing the Shared Pool Memory Structure
      • Pinning SQL in the Shared Pool
    • Monitoring SQL Usage and Parsing
      • Data Dictionary Views
    • Diagnose shared pool problems
    • SQL Tuning and the Shared Pool
  • V. SQL and its Data
    • SQL and its data usage behind the scenes
    • Data performance issues
    • The Buffer Cache
    • Describe the buffer cache architecture
    • Size the buffer cache
      • The DB_CACHE_SIZE Parameter
    • Resolve common performance issues related to the buffer cache
    • Use common diagnostic indicators to suggest a possible solution
    • Other Buffer Caches
      • When to use
      • KEEP, RECYCLE, Non-Standard Block Sizes
      • Setting up
  • VI. Tuning Sorts and Temporary Work Space
    • Private (Process Global Area) PGA vs Public PGA
    • Diagnose PGA memory issues
    • Size the PGA memory
    • PGA Limits
    • Diagnose temporary space issues
    • Specify temporary tablespace parameters for efficient operation
    • Temporary Tablespace Groups (TTG)
      • What they do and what they don’t do for you
      • Creating a TTG
    • Monitoring Sort Efficiencies
      • Dynamic Performance Views
      • V$SYSSTAT
      • V$SESSTAT
      • AUOTRACE Output
  • VII. Tuning Inserts, Updates and Deletes
    • Locking Issues and other wait events
    • Diagnose Checkpoint and Redo Issues
    • Setting Checkpoint Performance Goals
    • Checkpoint Frequency Issues
      • Too Often
      • Not Often Enough
    • The FAST_START_MTTR_TARGET Parameter
    • Other Parameters
    • Multiple database writers
    • Parameters
      • DB_WRITERS
    • Tune the redo chain
    • Monitor Redo Log Group Switches
    • When should we switch
      • Size the redo log files
      • Size the redo log buffer
      • Latch Issues
  • VIII. Understanding the Oracle Optimizer
    • Why object statistics are important
    • Viewing object statistics
    • Dynamic Sampling and its performance impact
    • Generating object statistics
    • Join Methods
      • Nested Loop, Hash, Sort-Merge
      • Influencing a Join Method
    • Indexes
      • Index Types
      • Negating Index Usage
      • Influencing Index Usage
    • Hints
      • When to use
      • When not to use
    • Running Explain Plan
    • Interpreting Explain Plan Output
    • What to look for
      • Order of events
      • When is the filtering done
      • Join order and Join methods
      • Index access methods
    • Reading Autotrace Statistics
  • X. SQL Performance Tips and Techniques
    • SQL Programming Standards
    • Designing for Performance
    • Joins vs. Sub-queries
    • Using Table Partitions
    • Influencing the Optimizer Tips
    • Using PL/SQ to improve SQL Performance
  • XI. Oracle 18c New Features
    • SQL Tuning Advisor Exadata Enhancements
    • New SQL Tuning Set API
    • Concurrent SQL Execution with SQL Performance Analyzer
    • SQL Performance Analyzer Result Set Validation
    • Memoptimized Rowstore
    • Private Temporary Tables
    • Manual termination of Run-Away Queries
    • SQL*Plus Changes
    • SQL Developer Changes
    • Miscellaneous SQL Performance Changes
    • Deprecated Features
    • Desupported Features

ProTech Logo ProTech Themis Logo

We are pleased to announce that effective July 1st, ProTech Training and Themis Education will join forces to become one of the industry's strongest Enterprise IT Training companies. Please click the button below to be redirected or visit our site at ProTechTraining.com.

Contact Information
Themis technical support for classes: themissupport@themisinc.com
ProTech technical support for classes: virtualsupport@protechtraining.com
ProTech/Themis voice: 1 (800) 373-9188 or 412-810-8855
All general inquiries: info@protechtraining.com