浅谈Oracle数据库SQL调整与优化

孟津平 李易东

(长春理工大学 计算机科学技术学院,吉林长春 130012)

Oracle数据库作为如今市场占有率最高的数据库,拥有着极为突出的优势最为显著的是高效处理事物的能力。但作为大型数据库难免会存在着较多问题,这些都影响数据库对数据的高效处理。其中大部分问题都出现在数据库应用系统性能方面,涉及到数据库服务器、数据库硬件、数据库内存、数据库参数等方面。但是在实际的数据库操作中SQL语句的错误设计会导致整个查询过程变的低效甚至瘫痪。由此,本文将对数据库的SQL优化进行探索和研究,分析并使用合理的优化方法,从而达到数据库高效处理的目的。

1 Oracle数据库的查询过程

Oracle DBA通过SQL语言对数据库进行通信和操作,数据库应用系统通过执行用户或DBA提交的SQL语句完成查询过程。

(1) 客户端将语句发给服务器端执行,由服务器端的进程处理这些语句。

(2)服务器进程接收到SQL语句并开始解析。查询高速缓存,查看是否存在相同语句的执行计划。进行语法合法性检查,看是否合乎语法规则。语言含义检查,查看涉及的表、索引、视图等进行解析。获得对象解析锁及数据访问权限的核对。确定最佳执行计划。

(3)绑定变量赋值。在SQL语句中如果使用了绑定变量,扫描绑定变量的声明,给绑定变量赋值,将变量值带入执行计划。若在一开始解析时,SQL在高速缓冲中存在,则直接跳到该步骤。

(4)语句执行。

2 索引的合理建立

数据库中的索引是一种排序的数据结构,是通过B树和变形的B+树实现的。在数据库的使用中SQL语句的查询速度会随着数据量的增加变的越来越慢,因此我总结出以下几点关于索引的使用:

(1)经常与其他表进行连接的表,在连接字段上应该建立索引。

(2)经常出现在Where子句中的字段,特别是大表的字段,应该建立索引。

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

(4)索引应该建在小字段上,对于大的文本字段,不要建索引。

(5)复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替。

正确选择复合索引的主列字段,一般是选择性较好的字段。

复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是,则可以建立复合索引;否则考虑单字段索引。

如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引。

如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段。

如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引。

(6)频繁进行数据操作的表,不要建立太多的索引。

(7)删除无用的索引,避免对执行计划造成负面影响。

(8)特殊字段的数据库,如BLOB,CLOB字段不适合建立索引。

建立索引时应该首先考虑表空间和磁盘空间是否足够,其次,在对建立索引的时候要对表进行加锁,因此应当注意操作在业务空闲的时候进行。在建立索引的时候要对表进行全表扫描,同时还要对数据进行大量的排序操作。因此,应当调整排序区的大小。最后,建立索引的时候,可加上nologging选项。以减少在建立索引过程中产生的大量redo,从而提高执行的速度。

3 基于索引的SQL优化的研究

(1)避免在含有索引的条件上使用隐式转换,这会使得索引失效。且SQL语句更加不容易被理解,一旦上下文环境发生改变可能无法正常运行。

(2)避免使用子查询例如:select ename,deptno,(select sum(sal)from emp where deptno=e.deptno)tsal from emp e;执行计划表明,上面的查询进行了两次全表扫描。改为select ename,deptno,sum(sal)over(partition by deptno)tsal from emp;使用分析函数后整个语句只有一次全表扫描速度增加至少一倍。

(3)当针对大量相同的列如:类别、操作员、部门ID,时使用位图索引。在索引块的一个索引行中存储键值和起止Rowid,以及这些键值的位置编码。根据键值查询时可以根据起始Rowid和位图状态快速定位数据、做and,or,或in查询可直接用索引的位图进行或运算快速得出结果。

(4)表建立索引后,不断进行增删改等操作,会使索引中产生大量存储碎片。这就必须要用到如下的两种方法。

合并索引:将B树中叶子节点的存储碎片合并在一起,并不会改变索引的物理组织结构。

重建索引:消除存储碎片并改变索引的全部存储参数设置,以及存储表空间。

4 Oracle优化思路的总结

在对数据库的SQL语句进行优化时不能盲目优化,首先找到该数据库的top sql,因为top sql才是这个数据库最需要完成的工作,其好坏直接影响数据库的使用。首先对这些语句的执行计划进行分析,主要是找出他们的瓶颈所在,看他们是否按照预定的计划进行查询,再看看统计信息是否最新、是否没有、是否不合理。一般执行计划变慢的原因本文认为应该从以下几点进行分析:

(1)访问路径的问题:在查询的时候优化器选择其中自认为是最优化的路径来定位和查询出需要的数据。但是这可能并不是预期的结果所以需要人为干预和修改。

(2)表的链接或索引出现问题:这种情况应该从索引创建是开始分析,哪些需要索引、是否每个索引都利用到。数据库中如果对表频繁操作,索引没有定期的维护或重建索引,就有可能出现地址对应不上、查询慢等问题。

(3)语法使用问题。

(4)物化视图的原因。

[1]刘春菊.Oracle数据库应用系统的性能优化[J].电子技术与软件工程,2017,(17):180.

[2]杨嵩.浅谈Oracle数据库应用系统的性能优化[J].计算机光盘软件与应用,2015,(03):111-112+115.

[3]莫佩宏.Oracle数据库应用系统的性能优化[J].电子制作,2014,(16):54.