78-生产系统不改代码解决SQL性能问题的几种方法

数据库性能很大一部分跟SQL写法有关, 有些SQL是必须改代码才能优化的, 如果不能改代码, 需要删历史记录,回收空间,让表始终保持相对较小,才能消耗较少的资源,得到能够接受的响应时间.如前公众号文章所述: 必须通过改写SQL才能提升性能的一些情况 (列举了10个需要改写的SQL).

出于各种原因, 需要在不改SQL代码的情况下对数据库进行优化,下面就列举了一些情况,可以在不改程序代码的情况下, 改善SQL的执行效率:

1-增加索引(没有风险)

普通索引:

这个不多解释, 大部分sql性能提升立竿见影, 几百倍,上千倍甚至上万倍的提升都是正常的.

函数索引

解决隐式类型转换:

字段类型是varchar2类型, 变量类型是number类型, 比如 phone_nbr=13812345678 , phone_nbr(varchar2类型)字段上的索引是用不上的(不要抬杠说index fast full scan还有可能), 这就需要再创建一个 to_number(phone_nbr)的函数索引来提高效率;

类似的情况还有 varchar2类型(和char类型)的字段, 变量类型是nvarchar2(和nchar),这种情况需要创建to_nchar函数索引;

注意: date类型的字段, 遇上timestamp的变量, 不能通过创建to_timestamp函数索引来解决,需要使用方法3.

解决字段上使用函数,做运算:

to_char(cdate,'yyyymmdd')=:b1

可以创建to_char(cdate,'yyyymmdd')函数索引

xxx is null的写法,可以让xxx与常量0组成联合索引

2-调整执行计划(没有风险)

sql执行计划选择错误,不需要在程序代码的sql中增加hint, 强大的oracle有办法在后台控制SQL的执行计划;

情况1:执行计划有好有差, 直接使用sql profile或sql plan baseline固定好的执行计划;

情况2:sql没有好的执行计划,需要手工加hint生成好的执行计划,然后再用sql profile或sql plan baseline固定;

上面两种情况都可以使用coe_load_sql_profile.sql脚本完成,都是简单输入几个参数即可.

对于情况2,很多书上介绍使用coe_xfr_sql_profile.sql, 这种方法比较麻烦,还容易出错,建议大家抛弃这个方法,改用简单的coe_load_sql_profile.sql

补充一个hint使用特例, /*+ bind_aware */这个hint, 需要使用sql patch进行追加.

写这篇公众号文章的起因就是源于前同事的一个问题:

一个系统工具(logminer)使用的sql, 使用了并行度为208的 parallel_index , 这么高的并行度是不可接受的,想取消sql的并行. 因为无法修改SQL,尝试使用no_parallel_index(t) 不生效,最终通过sql patch应用了一个full(T@SEL$1)的hint,避免了使用index fast full scan,也就没有了并行:

3-改字段类型 (有一定风险,建议做好充分测试)

主要针对date类型字段, 遇到timestamp类型的变量,做隐式类型转换无法使用索引的情况,如:

select count(*) from tt where created>=:b1 and created<=:b2;

如果created字段是date类型,b1和b2是timestamp类型, 会发生隐式类型转换,无法使用created字段上的索引; 而且这种情况无法创建to_timestamp函数索引来补救;

如果不改代码解决,只能修改字段类型date为timestamp(0):

alter table tt modify created timestamp(0);

这个语句只修改数据字典, 不需要修改每行记录(如果是反过来timestamp(0)改成date类型,就要逐行修改每条记录,大表时间会比较长)

timestamp(0) 与 date 类型的主要区别在于 两个timestamp类型相减得到的是interval类型; 而两个date类型相减, 得到的是number类型, 如果没有这种谓词条件或返回列, 可以尝试这个方法.

4-改表结构(没有风险)

改成分区表

适用: 统计分析类SQL,如果是对一个月的数据做统计分析, 表中有5年的数据量, 分区后, 数据访问量为原来的1/60

5-改参数(有一定风险,建议做好充分测试)

OLTP高并发环境没有使用绑定变量, 大量硬解析, 修改代码的工作量是巨大的, 简单方法就是修改数据库初始化参数 cursor_sharing=force (默认值为exact), 有一定的风险,做好测试.(有一些bug需要注意,公众号文章: 11.2.0.3版本升级到18c之前的各版本，可能遇到严重性能问题 )

6-特殊案例

原SQL:

select PM_JOB_SEQUENCE.nextval job_id

from (select 1 from all_objects where rownum <= 13);

借用数据字典视图all_objects生成一段sequence 序列, 频繁的执行,消耗系统大量的CPU,平均每次执行平均buffer gets 169~1362(6个执行计划):

如果能改代码,可以改成:

select PM_JOB_SEQUENCE.nextval job_id from dual connect by level<=13;

这种写法不消耗buffer gets;

如果不能改代码, 可以创建一个同名表:

create table all_objects (id number not null);

insert into all_objects select 0 from dual connect by level<=100;

commit;

create index idx_all_objects on all_objects(id);

--因为同义词synonym和表同时存在时,优化访问表, 这时只需要2个buffer gets:

select PM_JOB_SEQUENCE.nextval job_id

from (select 1 from all_objects where rownum <= 13);

如果要访问真正的系统视图all_objects,则需要加上sys的schema: sys.all_objects

7- DBMS_ADVANCED_REWRITE (方法6,如果只是换表,也可以用这种方法,但是不支持sequence)

这个方法在10g版本就有了,用一段SQL代码,替换另一段SQL代码(不支持带绑定变量的SQL),功能很强大,大家有兴趣可以在网上搜索更多的案例.

对应的数据字典: DBA_REWRITE_EQUIVALENCES

下面是一个简单例子:

参数说明:

DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (

name VARCHAR2, ---名字

source_stmt CLOB, ---原SQL代码

destination_stmt CLOB, ---替换SQL代码

validate BOOLEAN := TRUE, --默认值true, 会比较两个SQL结果集, 如果不相同, 不会创建成功

rewrite_mode VARCHAR2 := 'TEXT_MATCH' --默认值,简单转换; 还有高级的general, 更高级的recursive (disabled: 禁用)

);

如果要删除:

exec sys.DBMS_ADVANCED_REWRITE.DROP_REWRITE_EQUIVALENCE ('&equ_name');

--例子开始:

--创建REWRITE_EQUIVALENCE

begin

sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (

'tiger_test_equivalence',

'select ename from emp',

'select dname from dept'

,validate => false

);

end;

/

--修改参数才能使用:query_rewrite_integrity 默认值: enforced

SQL> alter session set query_rewrite_integrity = trusted;

--下面SQL,实际上执行的是select dname from dept:

SQL>select ename from emp;

ENAME

--------------

ACCOUNTING

RESEARCH

SALES

OPERATIONS

--把参数query_rewrite_integrity改回默认值

alter session set query_rewrite_integrity = enforced;

--再次执行,返回正常结果:

SQL> select ename from emp;

ENAME

----------

SMITH

ALLEN

......

JAMES

FORD

MILLER

实战案例模拟:

--原SQL,无法使用status字段上的索引(条件写到了having 部分):

select m.object_type, count(1) unread_count

from t1 m

group by m.object_type, m.status, m.owner

having m.status = 'INVALID' and m.owner = 'SYS';

--等价改写SQL,可以使用status字段上的索引(条件在where部分):

select m.object_type, count(1) unread_count

from t1 m where m.status = 'INVALID' and m.owner = 'SYS'

group by m.object_type, m.status, m.owner;

想用等价改写的SQL,替换原SQL,下面是简单的几个步骤:

setup case:

create table t1 as select * from dba_objects;

create index idx_t1_status on t1(status);

--创建REWRITE_EQUIVALENCE

begin

sys.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (

'test_rewrite2'

, q'[select m.object_type, count(1) unread_count

from t1 m

group by m.object_type, m.status, m.owner

having m.status = 'INVALID' and m.owner = 'SYS']'

, q'[select m.object_type, count(1) unread_count

from t1 m where m.status = 'INVALID' and m.owner = 'SYS'

group by m.object_type, m.status, m.owner]'

, validate => true

, rewrite_mode => 'general'

);

end;

/

--执行原SQL, 执行计划还是使用全表扫描(query_rewrite_integrity 参数没有设置)

select m.object_type, count(1) unread_count

from t1 m

group by m.object_type, m.status, m.owner

having m.status = 'INVALID' and m.owner = 'SYS';

--设置参数, 再次执行原SQL:

alter session set query_rewrite_integrity = trusted;

select m.object_type, count(1) unread_count

from t1 m

group by m.object_type, m.status, m.owner

having m.status = 'INVALID' and m.owner = 'SYS';

(相当于执行了改写后的SQL,实现了优化目的)

8- 非常规方法, 慎用!

改二进制代码里面的sql (jar,exe等文件,sql代码一般也是字符串保存) ;大部分情况,这种方法应该也没问题.但是,不到万不得已, 不要用这一招.做好测试.

举个简单例子:

sql代码有各种日期类型的格式转换,下面情况需要创建3个不同的函数索引:

to_char(created,'yyyy-mm-dd') / to_char(created,'yyyy/mm/dd') / to_char(created,'yyyymmdd')

这种情况,可以在二进制代码中,找到这些字符串,统一改成 to_char(created,'yyyymmdd') , 这样只需要创建一个函数索引即可.

前面两个写法, 改完后字符串长度变短, 可以在后面补两个空格.

感谢阅读

(完)