Sql Statement Turing part1

Optimizer Modes
RBO
CBO


The query optimizer performs the following steps:


1.The optimizer generates a set of potential plans for the SQL statement based on available access paths and hints.


2.The optimizer estimates the cost of each plan based on statistics in the data dictionary for the data distribution and storage characteristics of the tables,indexes,and partitions accessed by the statement.
The cost is an estimated value proportional 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 the estimated computer resources,which inclueds I/O,CPU,and memory.


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


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


推荐书籍:Cost-Based Oracle fundamentals -by Jonathan Lewis


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


/*thoughtout是吞吐率，是一秒钟内客户端与服务器之间的传送数据量;
reponse time是指响应时间，如查询1000条数据，返回第一条的时间;
以上两种对应着不同的优化目标与方法*/


Choose a goal for the optimizer based on the needs of your application.
* For applications performed in batch,such as Oracle Reports applications,optimize for best throughout.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 or first few rows accessed by the statement.


/*betch是批处理，interacive是交互的*/


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.


*At the instance level /*即alter system set optimizer_mode=*/
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


三个级别，第三个能覆盖第二个，第二个能覆盖第一个；


All_ROWS
The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput(minimum resource use to complete the entire statement).This is the default value /*获得最大的吞吐率为目标*/


FIRST_ROWS_n
The optimizer uses a cost-based approach,regardless of the presence of statistics,and optimizes with a goal of best response time to return the first n number of rows;n can equal 1,10,100,1000.


FIRST_ROWS:10g,11g不用管，为了向后兼容而设。


Using Hints in SQL
CREATE index gen_idx on customers(cust_gender);
select /*+ INDEX(customers gen_idx)*/
cust_last_name,cust_street_address,
cust_postal_code
FROM sh.customers
WHERE UPPER(cust_gender) = 'M';