Oracle Database 10g: SQL Tuning

Course details

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. 

• 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:

SQL STATEMENT EXECUTION:

VIEWING THE EXECUTION PLAN:

UNDERSTANDING THE COST BASED OPTIMIZER (CBO):

OPTIMIZER OPERATIONS:

MANAGING OPTIMIZER STATISTICS:

PERFORMANCE MONITORING & PROACTIVE MAINTENANCE:

USING ADDM & THE SQL TUNING ADVISOR:

PROGRAMMATIC ACCESS TO THE SQL TUNING ADVISOR:

USING THE SQL ACCESS ADVISOR:

OPTIMIZER HINTS:

HISTOGRAMS:

USING PLAN STABILITY:

EDITING PRIVATE OUTLINES:

EXPLOITING & MANAGING CURSOR SHARING:

USING TKPROF & AUTOTRACE:

MANAGING SCHEMA OBJECTS: