11 SQL调优概述

11.3调优程序/减轻负载

如果期望减轻数据库服务器整体的CPU或者I/O负载，可通过以下步骤确定资源密集型的SQL语句：

1.选择在应用程序的处理高峰期检测。

2.在以上周期内，收集操作系统及Oracle统计信息。需要收集的Oracle统计信息至少包括文件的I/O（通过视图V$FILESTAT），系统统计信息（V$SYSSTAT），以及SQL统计信息（V$SQLAREA, V$SQL or V$SQLSTATS, V$SQLTEXT, V$SQL_PLAN, and V$SQL_PLAN_STATISTICS）。

3.通过第二步收集的统计信息来确定使用最多资源的SQL语句，可以通过对V$SQL中的数据排序来断定资源的使用情况。一般关注的资源是：

• Buffer gets (V$SQL.BUFFER_GETS, 较耗CPU的语句)

• Disk reads (V$SQL.DISK_READS, 较耗I/O的语句)

• Sorts (V$SQL.SORTS, 大量排序)

较注重CPU的优化，可以首先从buffer gets值较高的语句入手，否则，对于I/O来说，从DISK_READS较高的SQL语句开始。

11.5.1验证优化器统计信息

查询优化器使用从表、索引收集的统计信息来决定最优的执行计划。如果统计信息未收集或者存储在数据库中数据库的统计信息已不具代表性，则优化没有足够的统计信息来生成最优的执行计划。

1.最好为所有表收集统计信息，特别是SQL语句中表连接涉及的表需要收集统计信息。

2.表中的实际行数（row count）与DBA_TABLES.NUM_ROWS的值做比较，是一个检验数据字典的统计信息是否过时的好方法。

11.5.3索引失效

1.不要在SQL谓词及条件子句中使用SQL函数，定义了基于函数的索引列上使用除外

2.避免在条件字句中的列上使用类型转换。

例如

WHERE TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1))=

TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1))

包括隐式转换，例如

VARCHAR2类型列USERNAME,条件使用了NUMBER值，WHERE USERNAME=1200

正确方式：WHERE USERNAME='1200'

11.5.4EXISTS和IN

例子：

表employees，有唯一索引employee_id字段，索引字段department_id，数据量27000

表orders，索引字段customer_id，数据10000条

employees.employee_id与orders.sales_rep_id关联

子查询有索引列过滤条件，且返回结果较少时，外层条件字句中适合使用IN

例如：

SELECT e.employee_id, e.first_name, e.last_name, e.salary

FROM employees e

WHERE e.employee_id IN (SELECT o.sales_rep_id

FROM orders o WHERE o.customer_id = 144);

要优于

SELECT e.employee_id, e.first_name, e.last_name, e.salary

FROM employees e

WHERE EXISTS (SELECT 1 FROM orders o

WHERE e.employee_id = o.sales_rep_id

AND o.customer_id = 144);

而子查询无索引过滤条件，或者返回数据较大时，外层条件字句中适合使用EXISTS

例如：

SELECT e.employee_id, e.first_name, e.last_name, e.salary

FROM employees e

WHERE e.department_id = 80

AND e.job_id = 'SA_REP'

AND EXISTS (SELECT 1

FROM orders o

WHERE e.employee_id = o.sales_rep_id);

优于

SELECT e.employee_id, e.first_name, e.last_name, e.department_id, e.salary

FROM employees e

WHERE e.department_id = 80

AND e.job_id = 'SA_REP'

AND e.employee_id IN (SELECT o.sales_rep_id FROM orders o);

当然，如果在department_id、job_id字段上建立联合索引，效率将进一步提升。