SQL优化_SQL常见问题

SQL性能差的原因：

过期或缺失优化器统计信息(收集统计信息)

缺失访问结构(加索引)

次优的执行计划选择：CBO有时为SQL语句选择了一个次优的执行计划，可能对SQL语句的某些属性的不正确的评估导致，比如成本，基数性或预选择性

SQL结构差：如果SQL语句设计烂，优化器没太多可以优化的空间。一个连接条件缺失会导致一个笛卡尔积，或使用类似union等成本更高的SQL结构来替代union all。

 

烂SQL的举例1:

select count(*) from products p

where prod_list_price <

 1.15 * (select avg(unit_cost) from costs c

                     where c.prod_id = p.prod_id);

该查询确定有多少产品的列表价格比产品的平均成本少15%。该查询有一个关联的子查询，这意味着outer查询发现的每行记录，子查询都要运行一次。

更好的书写方式是：

select count(*) from products p,

(selectprod_id, avg(unit_cost) ac from costs group by prod_id)c

where p.prod_id = c.prod_id;

 

烂SQL的举例2:

select * from job_history jh, employees e

         wheresubstr(to_char(e.employee_id),2) = substr(to_char(jh.employee_id),2)

这个查询在连接列上使用了函数，限制了索引使用。尽可能使用简单的等式。你也可以使用基于函数的索引。

 

烂SQL的举例3:

select * from orders where order_id_char =1205;

这个查询有一个条件，强制隐式数据类型。

order_id_char是字符串类型，常量是数字类型。你应该让文字匹配列类型。

修改SQL为：

select * from orders where order_id_char = ‘1205’;

 

烂SQL的举例4:

select * from employees

 where to_char(salary) = :sal;

使用了一个数据类型转换函数来使得对比的两端数据类型匹配。问题是to_char函数应用到了列值，而不是常量上。这意味着表中的每行都需要调用函数。如果转换常量一次，而不是转换列值会更好。

修改为

select * from employees

 where salary = to_char(:sal)；

 

烂SQL的举例5:

select * from parts_old

union

select * from parts_new

相比Union all操作符，union操作符确保结果中没有重复行。但是，这需要额外的一步，一个唯一排序，来消除所有的重复。如果你清楚地知道2个Union查询中间不存在公共部分，使用Uion all替代union。这样避免了不必要的排序。

 

 

组合SQL语句

select count(*) from myemp where salary< 2000;

select count(*) from myemp where salarybetween 2000 and 4000;

select count(*) from myemp where salary> 4000;

上面需要查询3次才能得到想要的结果

select count ( case when salary < 2000

                                     then1 else null end) count1,

count ( case when salary between 2001 and 4000

                                     then1 else null end) count2,

count ( case when salary > 2000

                                     then1 else null end) count3

from myemp;

上面需要查询1次就能得到想要的结果

n   低效的SQL语句设计
如果SQL的编写使其执行了非必须的工作，那么优化器也无法对提升性能有所帮助。低效设计包括：

l   忽视连接条件，导致笛卡尔基连接

l   使用hints来指定大表作为join的驱动表

l   指定了union而非unionall

l   使得子查询为外部查询结果的每行都执行一次

低效的SQL结构使用

可能是使用Not in替换了not exists, 或者union替换union all。union相比unionall，使用了唯一排序来确保结果集中没有重复行。如果你直到2个查询不会返回重复行，使用Union all.

数据类型不匹配，可能存在数据类型转换

设计错误，经典错误是缺失连接条件，n个表关联，需要n-1个连接条件避免笛卡尔基

主动调优方法：

简单化设计：

如果设计看起来不错，应用就会运行良好。如果表设计太复杂无人能理解，表设计可能很糟糕。如果SQL语句太长，没有优化器能够实时地有效优化它，这可能是一个糟糕的SQL，糟糕的潜在事务，或糟糕的表设计。如果一个表有很多索引并且相同的列被重复索引，这可能是糟糕的索引设计。

表和索引设计：

表设计很大程度上是核心事务在性能和灵活性之间的妥协。虽然索引设计也是一个大的反复过程，基于应用的SQL。但是通过创建强制外键约束(降低主键表和外键表链接响应时间)的索引，或在经常访问数据上创建索引是一个明智的起点。主键和唯一键是自动索引的，除非disable validate和disable novalidate rely约束。索引数据应该是经常查询的selectlist中的列，使用SQL作为索引设计的指导。

最易加速查询的一个方法是通过从执行计划中消除一个表扫描来降低逻辑I/O的数量。可以通过在表的被查询引用的所有列上创建一个索引来实现。这些列是select list列，where子句列，和需要Join或sort的列。这个技巧在加速一个联机应用响应时间时特别有用。

使用视图

视图能加速和简化应用设计。一个简单视图定义能够掩盖数据模型复杂度。视图经常用于提供简单的行和列级别访问限制。

通过视图虽然能提供纯净编程接口，但是当嵌套太深时，他们也会导致次优，资源紧张查询。视图的最坏使用类型是创建链接到引用其他视图的视图，视图又接着引用其他视图。在很多案例中，开发者能够直接从表而不使用视图来满足查询。由于他们的固有属性，优化器一般很难为视图生成最优的执行计划。

良好的游标使用和管理

由于解析应尽可能少，应用设计者应该设计它们的应用解析SQL语句一次，之后执行很多次。通过游标可以实现。有经验的SQL编程人员应该熟悉打开和重新执行游标的概念。

应用开发人员也必须确保SQL语句在共享池内可被共享。通过使用绑定变量来替换多次执行变化的查询部分来实现。为了确保SQL被共享，在SQL语句中使用绑定变量并且不使用字符串文字。

验证所有SQL语句是最优的并理解它们的资源使用

验证SQL语句有效地使用游标。每个SQL语句应该解析一次之后执行多次。当没有恰当使用绑定变量，where子句为此以字符常量发送时经常发生。

使用SQL Tuning Advisor需要advisor权限

手工创建SQL tuning Task

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'SELECT /*+ ORDERED */ * ' ||
'FROM employees e, locations l, departments d ' ||
'WHERE e.department_id = d.department_id AND ' ||
'l.location_id = d.location_id AND ' ||
'e.employee_id < :bnd';
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
sql_text => my_sqltext
, bind_list => sql_binds(anydata.ConvertNumber(100))
, user_name => 'HR'
, scope => 'COMPREHENSIVE'
, time_limit => 60
, task_name => 'STA_SPECIFIC_EMP_TASK'
, description => 'Task to tune a query on a specified employee'
);
END;
/

传递绑定变量为100，SQL tuning Advisor执行SQL profiling，运行不超过60秒

查看task的状态

COL TASK_ID FORMAT 999999
COL TASK_NAME FORMAT a25
COL STATUS_MESSAGE FORMAT a33
SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE
FROM USER_ADVISOR_LOG;

输出类似：

TASK_ID TASK_NAME                STATUS STATUS_MESSAGE
------- ------------------------- ----------- --------------
884     STA_SPECIFIC_EMP_TASK      INITIAL

执行task

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'STA_SPECIFIC_EMP_TASK');
END;
/

查看结果

SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'STA_SPECIFIC_EMP_TASK' )
FROM DUAL;