Oracle Database 10g SQL Tuning

Day 1

  • Lesson 1 : Oracle Database Architecture: Overview
  • Lesson 2 : Following a Tuning Methodology
  • Lesson 3 : Designing and Developing for Performance
  • Lesson 4 : Introduction to the Optimizer
  • Lesson 5 : Optimizer Operations

Day 2

  • Lesson 6 : Execution Plans
  • Lesson 7 : Gathering Statistics
  • Lesson 8 : Application Tracing
  • Lesson 9 : Identifying High-Load SQL
  • Lesson 10 : Automatic SQL Tuning

Day 3

  • Lesson 11 : Index Usage
  • Lesson 12 : Using Different Indexes
  • Lesson 13 : Optimizer Hints
  • Lesson 14 : Materialized Views

Lesson 1 : Oracle Database Architecture: Overview

  • Describe the Oracle Database architecture and components
  • Make qualified decisions about your tuning actions

Lesson 2 : Following a Tuning Methodology

  • Determine performance problems
  • Manage performance
  • Describe tuning methodologies
  • Identify goals for tuning
  • Describe automatic SQL tuning features
  • List manual SQL tuning steps

Lesson 3 : Designing and Developing for Performance

  • describe the basic steps involved in designing and developing for performance

Lesson 4 : Introduction to the Optimizer

  • Describe the functions of the Oracle optimizer
  • Identify the factors influencing the optimizer
  • Set the optimizer approach at the instance and session level

Lesson 5 : Optimizer Operations

  • Describe different access paths
  • Optimize sort performance
  • Describe different join techniques
  • Explain join optimization
  • Find optimal join execution plans

Lesson 6 : Execution Plans

  • Use the EXPLAIN PLAN command to show how a statement is processed
  • Use the DBMS_XPLAN package
  • Use the Automatic Workload Repository
  • Query the V$SQL_PLAN performance view
  • Use the SQL*Plus AUTOTRACE setting to show SQL statement execution plans and statistics

Lesson 7 : Gathering Statistics

  • Identify table, index, and column statistics
  • Describe the Automatic Statistics Gathering mechanism
  • Use the DBMS_STATS package to collect statistics manually
  • Identify predicate selectivity calculations

Lesson 8 : Application Tracing

  • Configure the SQL Trace facility to collect session statistics
  • Enable SQL Trace and locate your trace files
  • Format trace files using the TKPROF utility
  • Interpret the output of the TKPROF command

Lesson 9 : Identifying High-Load SQL

  • ADDM
  • Top SQL
  • Dynamic performance views
  • Statspack

Lesson 10 : Automatic SQL Tuning

  • Describe automatic SQL tuning
  • Describe the Automatic Workload Repository
  • Use Automatic Database Diagnostic Monitor
  • View the cursor cache
  • Perform automatic SQL tuning
  • Use the SQL Tuning Advisor
  • Use the SQL Access Advisor

Lesson 11 : Index Usage

  • Identify index types
  • Identify basic access methods
  • Monitor index usage

Lesson 12 : Using Different Indexes

  • Use composite indexes
  • Use bitmap indexes
  • Use bitmap join indexes
  • Identify bitmap index operations
  • Create function-based indexes
  • Use index-organized tables

Lesson 13 : Optimizer Hints

  • Optimizer mode
  • Query transformation
  • Access path
  • Join orders
  • Join methods

Lesson 14 : Materialized Views

  • Identify the characteristics and benefits of materialized views
  • Use materialized views to enable query rewrites
  • Verify the properties of materialized views
  • Perform refreshes on materialized views

In-house Price for 3 days

  • 45,000 baht(THB) : Economic Class : 1 - 5 people
  • 67,000 baht(THB) : Small Class : 6 - 10 people
  • 86,000 baht(THB) : Medium Class : 11 - 20 people
  • 105,000 baht(THB) : Large Class : 21 - 30 people
  • All prices exclude VAT 7 %
  • Public price : 15,000 baht per person

Printable Version ดาวน์โหลดหน้านี้ >>