ORA10g003 - Oracle Database 10g: SQL Tuning

Course ORA10g003

Table of Contents

Introduction
Audience
At Course Completion
Prerequisites
Certified Professional Exams
Student Materials
Course Outline

Introduction

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.

Return to Top

Audience

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.

Return to Top

At Course Completion

At the end of this course, the student will:

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

Return to Top

Prerequisites

Before attending this course, students must have:

• Completed ora10g008, Oracle Database 10g: Introduction to SQL I, or equivalent.
• Completed ora10g009, Oracle Database 10g: Introduction to SQL II, or equivalent.
• Completed ora10g101, Oracle Database 10g: Program With PL/SQL, or equivalent.
• Completed ora10g002, Oracle Database 10g: Architecture For Developers, or equivalent.

Return to Top

Certified Professional Exams

This course considers subjects that pertain to the certification for an Oracle Certified Developer. Following the training sessions, Oracle Corporation recommends six months production experience with the technology prior to taking the certification examinations. While this is good practice, it may not be necessary for all individuals.

Return to Top

Student Materials

The student kit includes a comprehensive workbook and other necessary materials for this class.

Return to Top

Course Outline

CONTEMPLATING THE TUNING ISSUES

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

Return to Top