[bbk2229] 第42集 Chapter 11SQL Statement Tuning(01)

Understanding Optimizer

The query optimizer performs the following steps:

• The optimizer generates a set fo potential plans for the SQL statement based on available access paths and hints.
• The optimizer estimates the cost of each plan based o statistics in the daa dictionary for the data distribution and storage characteristics of the tables,indexes,and partitions accessed by the statements.

　　The cost is an estimated value proportioal to the expected resource use needed to execute the statement with a particular plan.The optimizer calculates the cost of access paths and join orders based on te estimated computer resources,which includes I/O,CPU,and memory.

　　Serial plans with higher costs take more time to execute than those with smaller costs.When using a parallel plan,however,resource use is not directly related to elapsed time.

• The optimizer compares the costs of the plans and chooses the one with the lowest cost.

Optimizer Goal

By default,the goal of the query optimizer is the best throughput.This means that it choose the least amount of resources necessary to process all rows accessed by the statement.Oracle can also optimizer a statement with the goal of best response time.This means that it uses the least amount of resources necessary to process the first row accessed by a SQL statement.

• For applications performed in batch,such as Oracle Reports applications,optimize for best throughput.Usually,throughput is more important in batch applications,because the user initiating the application is only concerned with the time necessary for the application to complete.Response time is less important ,because the user does not examine the results of individual statements while the application is running.
• For interactive applications,such as oracle forms applications or SQL*Plus queries,optimize for best response time.Usually,response time is important in interactive applications,because the interactive user is waiting to see the first row of first rows accessed by the statement.

Setting the Mode

The optimizer`s behavior when choosing an optimization approach and goal for a SQL statement is affected by the following factors:

• OPTIMIZER_MODE Initialization Parameter
• Optimizer SQL Hints for changing the Query Optimizer Goal
• Query Optimizer Statistics in the Data Dictionary.

Setting the Mode

• At the instance level:
  • -optimizer_mode={Choose|Rule|First_rows|First_rows_n|All_rows}
• At the session level:
  • -ALTER SESSION SET optimizer_mode = {Choose|Rule|First_rows|First_rows_n|All_rows}
• At the statement level:
  • -Using hints

注意:statement level覆盖session level覆盖instance level;言外之意，statement level 的优先级大于session level，session level的优先级大于instance level

OPTIMIZER_MODE

 

注意：FIRST_ROWS是为了向后兼容而设计的，如果用户现在使用的10g or 11g ，建议最好不要使用FIRST_ROWS参数，建议选择ALL_ROWS或者FIRST_ROWS_n

SQL> show parameter optimizer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.2.0.1
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

Statistics for Optimizer

 Using Hints in SQL

 上图解析：一般情况下而言，优化器不见得跟人一样智能.开发人员则可以通过手工嵌入hint的方式，直接提示oracle server 使用指定的索引进行查询，来直接影响改变oracle server optimizer 的优化策略，执行思路.属于人为干预.