在Oracle SQL优化中经常使用的方案

在Oracle SQL优化中我们会经常用到固定optimizer plan，在固定optimizer plan中我们要进行stored outline的创建，以及如何使用stored outline等相关实际操作，以下就是相关内容的具体介绍。

1、优化器模式

Oracle9i有两种优化器模式可以选择：

• Rule-based:  
– Uses a ranking system  
– Syntax- and data dictionary–driven  
• Cost-based:  
– Chooses least-cost path  
– Statistics-driven 

Rule-based模式满足向后兼容，而Cost-based模式中的成本大部分来自于逻辑读的次数，推荐使用Cost-based模式。

2、固定optimizer plan

1）概念

对于每一个查询，optimizer都会准备一个定义了操作执行顺序和方法的操作树（执行计划），oracle server根据这个执行计划执行语句。通过固定执行计划，可以强制应用通过一种理想的方式访问数据，并且一个稳定的执行计划可以经历数据库的变化而保持不变。固定执行计划通过创建stored outline实现，outline使用cost-based的optimizer，因为其由一系列的hints组成。

执行计划的固定依赖于当判定一个查询是否存在stored outline时查询语句是否完全一致，与判定shared pool里一个执行计划是否可以重用时的匹配方式是一致的。

Outline被保存在outln schema中。

2） 创建stored outline

alter session set CREATE_STORED_OUTLINES = train;  
create or replace OUTLINE co_cl_join  
FOR CATEGORY train ON  
select co.crs_id, ...  
from courses co，classes cl  
where co.crs_id = cl.crs_id; 

stored outline通过category组织，相同的sql语句可以在多个category同时拥有stored outline，如果categoey没有指定，缺省是default category。

当CREATE_STORED_OUTLINES等于true或category名时，oracle会为所有被执行的sql语句创建stored outline，也可以通过create outline手工创建。

3） 使用stored outline

将USE_STORED_OUTLINES设置为true或category名。

alter session set USE_STORED_OUTLINES = train; 

当为一个查询寻找stored outline时，查询语句与stored outline里的语句必须完全一致，在outline里的hints也必须在查询语句中出现。

3、private outline

在Oracle SQL优化中Private outline是当前保存的stored outline的副本，可以被编辑而不影响正在运行的系统，一个private outline只能被当前session看到，它的数据被保存在当前被解析的schema里。，知道显示的将其公布。

当USE_PRIVATE_OUTLINES=TRUE时，一个已有outline的sql被提交时，optimizer会检查是否存在private outline，如果不存在，optimizer就不使用optimizer编译语句，而不会去检查公布的stored outline。

4、在sql中使用hints

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(gender)=’M’; 

5、EXPLAIN PLAN

可以不通过tracing，需要建立plan_table表：

Sql>@oracle_home/rdbms/admin/utlxplan;  

建立explain plan：

Explain plan for select last_name from hr.emp;  

查询plan_table中的explain plan，可以直接查询，也可以通过脚本utlxplx.sql(隐藏并行查询信息)、utlxplp.sql（显示并行查询信息）查询。

6、管理统计信息

利用analyize命令收集或删除信息。

参数：

Compute：统计精确的数据；

Estimate：估计的统计数据。

各类统计数据的位置：

表：dba_tables;

索引：dba_indexes;

列：user_tab_col_statistics;

柱状图（histogram）详细的描述了一个特定列中数据的分布情况，可以通过analyize table … for columns… 命令创建，保存在dba_histogram/dba_tab_histograms中。

上述的相关内容就是对Oracle SQL优化的描述，希望会给你带来一些帮助在此方面。

【编辑推荐】

1. Oracle应用的程序开发趋于向Web的缘由
2. Oracle RAC中错误观点出现的原因
3. Oracle客户关系管理论坛的内容简介
4. Oracle用户密码的修改
5. Oracle常用的命令如何查看表的结构