SQL性能调节

一、SQL的执行过程

1.创建游标(Create a Cursor)

2.分析语句(Parse the Statement)

3.描述查询结果

4.定义查询的输出数据

5.绑定变量

6.并行执行语句

7.执行语句

8.取出查询的行

9.关闭游标

 

 

二、CBO规则

 CBO=I/0 +CPU + NetWork

 

三、四种索引扫描

 1.索引唯一扫描(index unique scan)

 2.索引范围扫描(index range scan)

 3.索引全扫描(index full scan)

 4.索引快速扫描(index fast full scan)

 

四、组合索引使用条件

使用组合索引时，必须有引导列的参与，才使用组合索引。

 

五、表连接

 1.连接类型

  排序----合并连接

  嵌套循环

  哈希连接

 

  2.驱动表和探查表

   驱动表一般是where条件返回记录少的表，做为驱动表。

  目的是减少I/O读取次数。

  哈希连接中，构建HashTable与bitmap的表，作为驱动表。

 select /*+ use_hash(emp) */ empno from emp,dept where emp.deptno=dept.deptno;

要使哈希连接有效，需设置HASH_JOIN_ENBALED=TRUE

补充可设置：hash_area_size

 

3.选择

 

 排序---合并连接

  a)对于非等值连接，这种连接方式的效率比较好。

  b)如果在关联的列上都有索引，效果更好。

  c)对于将2个较大的row source做链接，改连接比NL连接要好。

  d)如果是sort merge 返回的row source过大，则又会导致使用过多的rowid，在表中查询数据时，数据库性能下降，因为过多的I/o;

 

嵌套循环(NL)

 a)如果驱动表比较小，并且在探索表上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效果。

 b)NL有其它连接没有的一个优点是：可以先返回已经连接的行，且不必等待所有的连接处理完毕后，才返回数据。

 

哈希连接

 a)在2个较大的row source之间连接时会取得相对较好的效率，在一个row source较小时，则取得更好的效率。

 b)只能用在等值连接中。

 

六、产生执行计划的三种方式

 

1.set autotrace on   set autotrace traceonly

  a)如果出错，则执行 ?/rdbms/admin/utlplan.sql

  b)用sys登录，执行 ?/sqlplus/admin/plustrce.sql

  c)grant plustrace to user_name

 

2.用explain plan命令

3.用dbms_system存储过程生成执行计划

 

 

七、干预执行计划-------hints

 

hints可实现：

 1)使用优化器的类型。

 2)基于代价的优化器的优化目标，是all_rows还是first_rows

 3)表的访问路径，是全表扫描，还是索引扫描，还是直接利用rowid

 4)表之间的连接类型

 5)表之间的连接顺序

 6)语句的并行程度。

 

指示优化器的方法与目标的hints:

  ALL_ROWS  ---基于代价的优化器，以吞吐量为目标。

  FIRST_ROWS(n)----基于代价的优化器，以相应时间为目标。

  CHOOSE    ----------根据是否有统计信息，选择不同的优化器。

   RULE  -------------   使用基于规则的优化器

 

select /*+ FIRST_ROWS(10) */ employee_id,last_name,salary job_id from employees where department_id = 20;

select /*+ CHOOSE */ employee_id,last_name,salary job_id from employees where department_id = 20;

select /*+ RULE */ employee_id,last_name,salary job_id from employees where department_id = 20;

 

指示存储路径的hints:

 FULLS   /*+ FULL (table) */

 ROWID  /*+ ROWID(table) */

 INDEX   /*+ INDEX(table[index] */

 INDEX_FFS /*+ INDEX_FFS(table[index] */

 NO_INDEX /*+ NO_INDEX(table[index] */

 

指示连接顺序的hints:

  ORDER /*+ ORDER */

  START  /*+ START */

 

指示连接类型的hints:

  USE_NL  /*+ USE_NL(table[table,...]) */   嵌套连接

  USE_MERGE  /*+ USE_MERGE(table[table,...]) */ 使用排序-- 合并连接

  USE_HASH  /*+ USE_HASH(table[table,...]) */使用HASH连接