[bbk2228] 第41集 - Chapter 11-SQL Statement Tuning(00)

Objectives

After completing this lesson,you should be able to do the following:

• Control optimizer options
• Use optimizer hints
• Employe plan stability
• Use store outlines(在10g,11g已经过时)
• Use SQL Trace and TKPROF

Overview

The purpose of this lesson is:

• To provide mthods to determine the resources used by SQL statements:
  • -Oracle Enterprise Manager
  • -Statuspack
  • -Explain plan
  • -SQL Trace and TKPROF
  • -Autotrace
• To determine which SQL statements possibly require tuning
• Not to tune the actual SQL statements

Optimizer Modes

There are two types of optimizer modes:

• Rule-based:(Rule based Optimizer 俗称RBO)
  • Uses a ranking system
  • Syntax-and data dictionary-driven(基于语法逻辑和一些数据字典信息进行驱动的优化)
• Cost-based:(Cost based Optimizer 俗称CBO)
  • Chooses the path with lowest cost
  • Statistics-driven(基于分析统计报表数据，进行优化驱动)

备注：目前RBO已经退出历史舞台，不在实际应用中使用;