Oracle Database 11g R2 – SQL Tuning, Architecture and Internals 5 days | | $2,695 PLUS TAX

THE BROADER ORACLE ENTERPRISE ARCHITECTURE

About Enterprise Architectures

• » About Parallelization
• » SMP
• » The Database Server Stack
• » The Database Server Software
• » V$Version

EXPLORING THE ORACLE DATABASE INSTANCE

• » Database Instance Configurations
• » About the Sga
• » Using V$Sga_Dynamic_Components
• » About the Pga
• » Managing Instance Memory

EXPLORING THE INSTANCE PROCESSES

• » Instance Background Processes
• » Sql Execution Processes
• » Using V$Session
• » About the Pga
• » Sql Statement Execution

EXPLORING LOGICAL STORAGE: DATABASE OBJECTS

• » Application Database Objects
• » Schema & Ownership Context
• » Editions Context & Redefinition

APPLICATION DATABASE OBJECTS

• » About the Database Files
• » Database Server Parameter File
• » Control Files
• » Alert Log & Trace Files

EXPLORING PHYSICAL STORAGE: TABLESPACES

• » Tablespaces & Data Files
• » AUTOEXTEND Options
• » About Temporary Segments
• » About Index Segments

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

USING THE SQL ACCESS ADVISOR

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