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.

Oracle Training Audience

The primary target audiences for this course are:

• Senior application designers and database developers
• PL/SQL developer
• Database administrators
• Web server administrators
• System administrators
• Implementation specialists
• Data center support engineers

Oracle Training Certification

This course covers information relevant for the certification test: ORACLE DATABASE 11G R2: PERFORMANCE TUNING.

When delivered as a standalone instructor-led training (ILT) or live virtual training session, the suggested length of this module is between 3 – 4 days. It is most often joined with the mandatory prerequisite course ORACLE DATABASE 11G R2: ARCHITECTURE & INTERNALS for a combined 5-day presentation.

Oracle Training Prerequisites

Specific prerequisites for this course are the following titles, or equivalent experience:

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

Suggested Next Courses

You may wish to consider the following courses which are also related to the subject of Oracle database performance tuning:

• ORACLE DATABASE 11G R2: RESOURCE MANAGER & SCHEDULER
• ORACLE DATABASE 11G R2: ADVANCED PL/SQL PROGRAMMING & TUNING
• ORACLE DATABASE 11G R2: PERFORMANCE TUNING
• ORACLE DATABASE 11G R2: IMPLEMENT PARALLEL SQL & PARTITIONING

Oracle Training Course duration

3-4 Days

Oracle Training Course outline

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

---

Please contact your training representative for more details on having this course delivered onsite or online

Training Outlines - the one stop shopping center for IT training.
© Training Outlines All rights reserved