Oracle Database 11g: SQL Tuning

Certification

Objectives

The following specific topics are among those considered:
•    Consider the unique and differing tuning issues found in online database applications, enterprise resource and data warehouse environments and the important metrics of SQL statement performance.
•    Learn about the internal mechanisms use for SQL statement execution within a database instance and how these can affect performance for good or bad, including the Optimizer facilities known as the Transformation Engine, Estimator and Plan Generator.
•    Use a variety of techniques to examine the details of SQL statement execution, spotting trouble areas and bottlenecks which require tuning.
•    Learn about the Auto-Task framework and how to manage the automatic collection of Optimizer statistics and automatic SQL tuning using both the programmatic and Enterprise Manager interfaces.
•    Learn how statistic deficiencies can dramatically degrade performance, and how these problems are resolved through customized Optimizer statistics collection procedures using the DBMS_STATS() package, system statistics, histograms, expression statistics and MultiColumn statistics.
•    Influence the behavior of the Optimizer by setting database parameters and other SQL tuning techniques.
•    Utilize the database advisory framework and the SQL Tuning and SQL Access advisors.
•    Use plan management to achieve plan stability which is adaptive and even dynamic.
•    Understand the self-tuning infrastructure and the automatic SQL tuning capabilities found within the database.
•    Employ SQL hints embedded into the statement text to resolve unique tuning challenges.
•    Learn to identify poorly performing SQL statements using real-time SQL monitoring and application tracing techniques such as DBMS_MONITOR(), trcsess and tkprof. 

Contents

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
• MULTICOLUMN STATISTICS

APPLICATION TRACING
• APPLICATION TRACING PACKAGES
• SET_SQL_TRACE()
• TRACE_ENABLE()
• SET_IDENTIFIER()
• EM APPLICATION MONITORING & TUNING
• REAL-TIME MONITORING
• USING TRCSESS & TKPROF

ADDM & THE SQL TUNING ADVISOR
• ADDM PERFORMANCE ANALYSIS
• USING THE SQL TUNING ADVISOR
• AUTOMATIC SQL TUNING
• CONFIGURING AUTOMATIC SQL TUNING

THE SQL ACCESS ADVISOR
• USING THE SQL ACCESS ADVISOR
• SQL ACCESS ADVISOR TEMPLATES
• PERFORMING A QUICK TUNE TASK
• INDEX DATABASE PARAMETERS
• OPTIMIZER_INDEX_COST_ADJ
• OPTIMIZER_INDEX_CACHING
• SKIP_UNUSABLE_INDEXES
• OPTIMIZER_USE_INVISIBLE_INDEXES

PLAN MANAGEMENT
• SQL MANAGEMENT BASE ARCHITECTURE
• LOAD_PLANS_FROM_SQLSET()
• LOAD_PLANS_FROM_CURSOR_CACHE()
• USING PLAN BASELINES
• PLAN MANAGEMENT USING EM

MANAGING CURSOR SHARING
• ABOUT CURSOR SHARING
• BIND VARIABLES & CURSOR SHARING
• THE CURSOR_SHARING PARAMETER
• FINDING CANDIDATES FOR REWRITE

OPTIMIZER HINTS
• WHAT ARE HINTS?
• HINT EXPLANATIONS