Oracle Database 10g: SQL Tuning
Oracle Training Overview
This Oracle 10g courseware training guide book 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.
Oracle Training Prerequisites
The following course is a mandatory prerequisite: ORACLE DATABASE 10G: INTRODUCTION TO SQL – COMPLETE LIBRARY.
Oracle Training Audience
This course considers subjects which 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.
Oracle Training Objectives
Major subject areas to be explored are:
The context in which the subjects considered within this course is important to understand to see the specific areas of focus in this course and the tuning and problem areas addressed by the other courses within the tuning curriculum:
CONTEMPLATING THE TUNING ISSUES • DIFFERENCES BETWEEN APPLICATIONS • THE CHALLENGES OF TUNING • THE IMPACT OF THE SYSTEMS INFRASTRUCTURE • USING THE MANAGEMENT & ADVISORY FRAMEWORK
Oracle Training Course duration
4 Days
Oracle Training Course outline
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
