Home About Courses Schedule Services Webinars Contact Search

Oracle SQL Optimization for Developers and DBA's

SEE SCHEDULE

Duration: 3 Days

Method: Instructor led, Hands-on workshops

Price: $1800.00

Course Code: OR5480



View Related Video

Audience

Oracle Developers and Oracle Database Administrators (DBAs

Description

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. Students will use EXPLAIN PLAN and AUTOTRACE for evaluating execution strategies and understand the importance of object statistics. Also discussed is how to influence the behavior of the optimizer with hints, physical schema changes, and alternative SQL statement syntax. Factors that affect overall system performance such as the buffer cache, other SGA structures, indexes and waits due to locks and latches are presented. Hands-on workshops provide students with a solid understanding of the concepts presented in the lectures.

Prerequisites

OR5450 Oracle SQL or equivalent experience

Topics

  1. Database Tuning Overview
    • Set appropriate tuning goals
    • Apply a tuning methodology
    • Identify potential tuning problems
    • Identify performance bottlenecks
  2. 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
  3. 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
      • V$SGA_DYNAMIC_COMPONENTS
      • V$SGA_DYNAMIC_FREE_MEMORY
      • V$SGA_CURRENT_RESIZE_OPS
      • V$SGA_RESIZE_OPS
  4. 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
      • V$SQL, V$SQLAREA, V$SQLTEXT
      • V$SYSSTAT, V$SESSTAT
    • Diagnose shared pool problems
    • SQL Tuning and the Shared Pool
  5. 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
  6. Tuning Sorts and Temporary Work Space
    • Private (Process Global Area) PGA vs Public PGA
    • Diagnose PGA memory issues
    • Size the PGA memory
      • SORT_AREA_SIZE vs PGA_AGGREGATE_TARGET
    • 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
  7. 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
      • LOG_CHECKPOINT_TIMEOUT
      • LOG_CHECKPOINT_INTERVAL
      • LOCK_CHECKPOINTS_TO_ALERT
      • FAST_START_IO_TARGET
    • Multiple database writers
    • Parameters
      • DB_WRITERS
      • DBWR_IO_SLAVES
    • 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
  8. 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
  9. Using EXPLAIN PLAN and AUTOTRACE
    • 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
  10. SQL Performance Tips and Techniques
    • SQL Programming Standards
    • Designing for Performance
    • Joins vs. Sub-queries
    • EXISTS/NOT EXISTS vs. IN/NOT IN
    • Using Table Partitions
    • Influencing the Optimizer Tips
    • Using PL/SQ to improve SQL Performance