Oracle Database 10g: SQL Tuning

Request more details:

Description:
This course will equip developers to build efficient SQL statements and to tune applications from the standpoint of an application developer. When this effort is complemented by database server and possibly PL/SQL application tuning, then a highly efficient environment has been built. The target audience for this course is both senior application developers and database administrators. Developers who will be building, debugging and tuning complex SQL statements will benefit from this course. Database administrators responsible for tuning the database will need to collaborate with application developers in order to implement the techniques discussed herein.

View other Oracle Courses available
Prerequisites:
Oracle Database 10G: Introduction to SQL - Complete Library (Level 1 and 2)
Objectives:
This course will equip developers to build efficient SQL statements and to tune applications from the standpoint of an application developer. When this effort is complemented by database server and possibly PL/SQL application tuning, then a highly efficient environment has been built. Major subject areas to be explored are:
Consider the unique and differing tuning issues in OLTP database and data warehouse environments.
Learn the phases of SQL statement execution within the database instance.
Use various techniques to examine SQL execution plans which are produced.
Learn about the operation of the Oracle Cost-Based Optimizer, including optimizer statistics and histograms.
Influence the behavior of the Optimizer and tune SQL statement execution.
Utilize the database advisory framework and those Advisors specifically intended for SQL tuning.
Use private and public stored outlines to achieve absolute execution plan stability.
Understand the self-tuning infrastructure and the automatic SQL tuning capabilities found within the database.
CONTEMPLATING THE TUNING ISSUES:
Differences between applications
The challenges of tuning
The impact of the systems infrastructure
Using the management & advisory framework
SQL STATEMENT EXECUTION:
The execution phases
Sql execution modes
VIEWING THE EXECUTION PLAN:
More about the execution plan
Viewing the execution plan
More interpretation of the plan
UNDERSTANDING THE COST BASED OPTIMIZER (CBO):
Optimization goals
Optimizer_features_enable parameter
Cbo components
OPTIMIZER OPERATIONS:
Table access operations
Join operations
Index operations
MANAGING OPTIMIZER STATISTICS:
more about optimizer statistics
automatic database statistics collection
statistics_level parameter
gather_stats_job
manually gathering statistics
using the dbms_stats() package
gather_table_stats()
gather_index_stats()
gather_schema_stats()
gather_database_stats()
using the em interface
collecting & managing system statistics
managing system statistics
create_stat_table()
Gather_system_stats()
get_system_stats()
set_system_stats()
import_system_stats()
export_system_stats()
delete_system_stats()
drop_stat_table()
PERFORMANCE MONITORING & PROACTIVE MAINTENANCE:
The management & advisory framework
Real-time monitors
Managing the automatic workload repository
Automatic database diagnostic monitor
Metrics & alert thresholds
USING ADDM & THE SQL TUNING ADVISOR:
Application tuning setup
Application monitoring & tuning with em
Sql tuning advisor
PROGRAMMATIC ACCESS TO THE SQL TUNING ADVISOR:
About sql tuning advisor tasks
Sql profile management
accept_sql_profile()
alter_sql_profile()
drop_sql_profile()
sql tuning set management
USING THE SQL ACCESS ADVISOR:
Using the sql access advisor
Performing a quick tune task
Index operation database parameters
Optimizer_index_cost_adj
Optimizer_index_caching
Skip_unusable_indexes
OPTIMIZER HINTS:
About hints
Hint explanations
HISTOGRAMS:
Why are histograms needed?
Histograms internal structure
Manually managing histograms
USING PLAN STABILITY:
about plan stability
Preparing for stored outlines
Creating & using stored outlines
Data dictionary storage
Managing stored outlines
Drop outline
Alter outline
dbms_outln()
EDITING PRIVATE OUTLINES:
About private outlines
Editing private outlines
Utilizing private outlines
EXPLOITING & MANAGING CURSOR SHARING:
About cursor sharing
Bind variables & cursor peeking
Using the cursor_sharing parameter
USING TKPROF & AUTOTRACE:
Using tkprof
Using autotrace
MANAGING SCHEMA OBJECTS:
About chained & migrated rows
Validating database objects