Oracle Database 11g R2: SQL Tuning

This course has been superseded

We suggest the following instead:
Oracle SQL Performance Tuning Tips & Techniques - 2 Days

Course Description

This course will equip database administrators and application developers to build efficient SQL statements and to tune database applications. When this effort is complemented by database server and PL/SQL application tuning, then a highly efficient application execution environment is created. One will learn about the internals of SQL statement execution, how to monitor the performance of such execution, and how one can influence the behavior of the database to achieve performance gains. This course is a mandatory reference for any database administrator or SQL database application developer.
3-4 Days
Contact us for pricing
 

Prerequisites

• ORACLE DATABASE 11G R2: SQL FUNDAMENTALS – COMPLETE LIBRARY
• ORACLE DATABASE 11G R2: ARCHITECTURE & INTERNALS

TUNING & THE ORACLE DATABASE ADVISORY FRAMEWORK

• THE CHALLENGES OF TUNING
• PERFORMANCE METRICS
• MANAGEMENT & ADVISORY FRAMEWORK
• ADDM & AWR
• SQL TUNING PRIVILEGES

VIEWING & MONITORING THE EXECUTION PLAN

• ABOUT THE EXECUTION PLAN
• COLLECTING PERFORMANCE STATISTICS
• VIEWING THE EXECUTION PLAN
• REAL-TIME SQL MONITORING

UNDERSTANDING THE OPTIMIZER

• OPTIMIZATION METHODS
• OPTIMIZATION GOALS
• OPTIMIZER_MODE
• OPTIMIZER_FEATURES_ENABLE
• OPTIMIZER COMPONENTS
• EXECUTION PLAN OPERATIONS

EXECUTION PLAN METHODS & OPERATIONS

• TABLE ACCESS METHODS
• JOIN METHODS
• INDEX OPERATIONS
• DATA OPERATIONS

MANAGING OPTIMIZER STATISTICS

• MORE ABOUT OPTIMIZER STATISTICS
• AUTOMATIC MAINTENANCE TASKS
• MANUALLY GATHERING STATISTICS
• GATHER_TABLE_STATS()
• GATHER_INDEX_STATS()
• GATHER_SCHEMA_STATS()
• GATHER_DATABASE_STATS()
• GATHER_SYS Parameter
• GATHER_DICTIONARY_STATS()
• GATHER_FIXED_OBJECTS_STATS()
• USING HISTORICAL STATISTICS
• DYNAMIC SAMPLING
• LOCKING STATISTICS

ENHANCED OPTIMIZER STATISTICS

• ABOUT OPTIMIZER SYSTEM STATISTICS
• MANAGE SYSTEM STATISTICS
• CREATE_STAT_TABLE(), DROP_STAT_TABLE()
• CREATE_STAT_TABLE()
• DROP_STAT_TABLE()
• GATHER_SYSTEM_STATS()
• GET_SYSTEM_STATS()
• SET_SYSTEM_STATS()
• IMPORT_SYSTEM_STATS(), EXPORT_SYSTEM_STATS()
• IMPORT_SYSTEM_STATS()
• EXPORT_SYSTEM_STATS()
• DELETE_SYSTEM_STATS()
• PENDING & PUBLISHED STATISTICS

HISTOGRAMS & EXTENDED STATISTICS

• WHY ARE HISTOGRAMS NEEDED?
• HISTOGRAMS INTERNAL STRUCTURE
• MANUALLY MANAGING HISTOGRAMS
• EXPRESSION STATISTICS
OracleOracle 11gOracle Database 11gSQL Tuning