ORACLE DATABASE 11G R2: SQL TUNING– GTD7

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 behaviour of the database to achieve performance gains.

^^

Duration

3-4 days

^^

Target 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 centre support engineers

^^

Course Prerequisites

Those who wish to attend this course require attendance of the courses below, similar courses, or have the equivalent experience.

If you have any questions or doubts as to whether you meet the pre-requisites for this course, or indeed are wondering which course best suits you, please consult with us to discuss your suitability for course attendance.

^^

Suggested Follow on Courses

^^

Course Content

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

© 2011 Sideris Courseware Corporation

^^

See more Database Technologies courses