SQL优化指南

作者: Arvin Chen 分类: oracle 来源: Break易站(www.breakyizhan.com)

SQL的DDL规范

大表创建索引切记要加ONLINE参数

大表创建索引未加ONLINE参数,导致大量阻塞session,数据库连接突增90%,应用实例异常

这几天在做数据库的优化,有个2亿记录的表,发现需要添加一个联合索引,结果就采用普通的create index index_name on tablename (entp_id,sell_date),结果悲剧了,把所有的DML语句都阻塞了,导致系统不能正常使用。

处理方案:非在线创建索引时,会上共享锁,阻塞任何dml操作。在线创建索引,create index xxx ONLINE;

大表新增字段有default值的处理方案

大表新增字段有default值,导致大量阻塞session,数据库连接突增90%,应用实例异常

处理方案:增加字段有default值,会更新历史数据为default值,运行时间较长,同时会上排他锁,阻塞查询和任何dml操作

11g中 增加 not null 参数 ,alter table xxx add column default‘aa' NOT NULL;

11g之前需要分步操作 1)alter table xxx add column 2) alter table xxx modify table default‘aa'

创建或重建索引增加parallel,必须有重置noparallel

Parallel是一个并行操作的命令,是用来加快命令的速度,所以在用完之后,必须重置noparallel.

处理方案:

--新增索引

Create index indexname on table initrans 16 parallel 4;

--重置noparallel

alter index xxx noparallel;

谨慎授权

DB中,授权是非常严谨的。严禁将非select权限授权给qry角色或者dev用户

处理方案:四个角色对应不同的授权xxx_qry、 xxx_dev_qry、 xxx_dml、 xxx_exec

SQL并行parallel使用

Oracle的并行技术在下面的场景中可以使用:

(1)Parallel Query(并行查询)

(2)Parallel DDL(并行DDL操作,如建表,建索引等)

(3)Parallel DML(并行DML操作,如insert,update,delete等)

Oracle的并行技术使用的注意事项:

1)索引应该建在选择性高的字段上。

2)并行的使用场景是大表的全表扫描,还有就是大的索引的快速全扫描

3 ) 标量子查询或是DB link,增大并行基本上无效果

4)正确写法/*+ parallel (a 4) parallel(b 4) */ 。

/*+ parallel */ 或 /*+ parallel 4*/是错误的写法 。

5)并行度选择为2、4、8

SQL并行parallel案例分析:

系统中高频全表扫描加并发,产生大量等待事件,导致资源争用。表有千万级的数据, 表大小达到几个G,一个小时全表扫描上千次,如果用并行parallel的话,则会导致资源争用,触发生产异常。

SQL并行parallel解决方案:

找到表中区分度比较大的字段,创建该字段的索引,清除并行,而且创建索引。

SQL的高频全表扫描

有下面这几种情况导致高频全表扫描的sql

缺少表的索引

缺少表的索引的解决方案:

找到表中区分度比较大的字段,创建该字段的索引。

Sql语句缺少过滤条件

Sql语句缺少过滤条件的解决方案:

整个sql,根据业务需求增加过滤条件。

Sql语句存在两个执行计划

Sql语句存在两个执行计划的解决方案:

通过PL/SQL developer查看该sql是否存在两个执行计划,如果存在两个执行计划,需要根据业务逻辑进行sql的整改,避免有两个执行计划的sql。

表数据频繁删除,导致索引非常大。

表数据频繁删除,导致索引非常大的解决方案:

因为表的删除比较频繁,导致有多索引的表性能非常差,特别是数据量变大之后,性能更是直线下降。如果表的数据维度比较大,而且查询一般都是在一定的时间维度的话,比如只查询当天的数据。那么,我们可以通过对表进行分区改造成每天一分区,来避免一张表有大量的数据。

SQL的笛卡尔积

笛卡尔积:在多表查询中不指定连接条件(关联条件),就会出现一个表中的所有行都连接到另一个表中的所有行。这个结果就是笛卡尔积;

比如;一个表中有10条数据,另一张表中有20条数据,那么笛卡尔积就有200条数据。

查询SQL缺少关联条件

查询SQL缺少关联条件,且高并发,导致数据库CPU100%。这种现象应该极力避免,在sql写完之后应该仔细检查两张表的关联条件。

更新SQL缺少关联条件

update SQL缺少关联条件,导致SQL大量异常更新,同时数据库出现异常等待事件。

SQL的索引列函数

案例:

SELECT *

FROM TABLE

WHERE TO_DATE(DATE, 'YYYY-MM-DD') >= #DATE#

这里在索引列上增加函数,导致SQL未走使用索引,走全表扫描。

SQL的索引列函数的解决方案:

应该调整变量为to_char,不需要创建函数索引,上面的sql可以调整为:

SELECT *

FROM TABLE

WHERE TO_CHAR(DATE, 'YYYY-MM-DD') >= #DATE#

SQL的绑定变量

拼接SQL未限制量

拼接SQL未限制量,导致SQL存在大量绑定变量,绑定变量个数超过65535,引发数据库宕机

insert all 写法大量异常

绑定变量过多

绑定变量过多,产生大量cursor: mutex X/S等待,数据库连接数暴增,引发数据库宕机

高频未使用绑定变量

SQL的统计信息和固化

统计信息不准导致SQL执行计划异常,导致SQL执行快照过旧

Oracle统计信息不准(谓词越界)造成的性能问题 - Break易站

统计信息不准导致SQL执行计划异常,占用大量临时表空间

  •   本文标题:SQL优化指南 - Break易站
    转载请保留页面地址:https://www.breakyizhan.com/oracle/15730.html
      微信返利机器人
      免费:淘宝,京东,拼多多优惠券
      腾讯,爱奇艺,优酷的VIP视频免费解析,免费看
      即刻扫描二维码,添加微信机器人!

    发表笔记

    电子邮件地址不会被公开。 必填项已用*标注