基于ORACLE数据库的SQL性能优化

岳彩云 赖晓风

摘要：数据库系统是任何信息系统最重要的组成部分，它涉及信息系统运行效率，影响系统性能。随着现代信息技术的进步，数据库的规模越来越庞大，对数据处理的操作也越来越复杂。在Oracle数据库系统中，查询操作是最基本、最复杂、最频繁的操作，SQL的查询语句的效率直接影响数据库的整体性能。该文主要介绍了SOL的语句所用优化技术，简要分析了数据库逻辑结构的优化、数据库物理存储结构的优化、使用分区。同时深入研究SOL性能分析及优化，其中，手动进行SOLprofile绑定主要涉及要执行的SOL文本、计划出问题后的表现、采用sql lprofile绑定;索引调优涉及要执行的SOL文本、SQL执行的相关统计信息、SQL执行计划、创建索引等。经过研究得出，若想将ORACLE数据库性能提高，必须多角度优化SQL语句。

关键词：数据库;SQL;索引;查询

中图分类号：TP311 文献标识码：A

文章编号：1009-3044（2020）10-0017-03

1数据库结构优化

1.1数据库逻辑结构的优化

逻辑数据库设计不合理往往易产生数据冗余、更新异常、插入异常、删除异常等问题，所以逻辑数据库设计至少应满足规范化BC范式或第三范式。

为降低数据冗余、减少用于存储数据的页，可遵循高级别的范式来减少每张表的列数，但这将产生更多表，且表间关系会更复杂，这样会降低系统的性能，特别是查询性能。从某种意义上说，非规范化能提高系统效率，非规范化过程可以结合性能考虑用多种手段实现，所以进行数据库逻辑结构设计时应综合考虑数据冗余和基于连接的查询性能问题。

1.2数据库物理存储结构的优化

因为數据文件和日志文件的位置、分布直接影响到数据库系统性能，所以数据库设计应遵循：一是将序列访问的文件和数据文件分别存放在不同磁盘上，一般，序列访问文件宜存储于高速专用磁盘上，数据文件分散存储到不同的磁盘上而实现并行I/O，从而提高访问速度;二是数据类型应尽量使用所需的最小存储空间，特别是索引列，如能使用Smallint类型的就不用Int型，这样数据页就能存放更多的数据行，以减少I/O操作。

1.3使用分区

对于数据量超过PB级、TB级甚至更大的大型数据库，某些单表的记录数往往多达亿条，巨大的数据量将严重影响数据库的运行效率和运维难度。为解决这一问题，可对表进行合理分区。把大表分为多个更小、更容易管理的部分，充分利用数据库系统中的多个CPU或多个磁盘子系统，以改善数据库系统的运行效率。可以按照业务数据本身性质进行表分区，也可按时间进行表分区，或其他业务的维度进行分区。

2SQL性能分析及优化

不同的业务场景、数据库类型、数据逻辑结构及不同的网络、服务器等硬件环境，实验结果或有微小差异。以下所有实验结果都是针对某市某业务信息系统后台数据库进行的实验，且所用数据库为Oracle 12C版本，相关SQL处理后的效果只是一个大致的结果。

2.1手动进行SQL profile绑定

当Oracle面对执行计划失效或跑偏时，执行效率就会大大降低，影响数据库的查询性能甚至整个数据库性能，此时，最好的优化手段就是进行SQL profile人工绑定。例如：

2.1.1要执行的SQL文本

2.1.2计划出问题后的表现sql单次执行时间需要消耗204秒，单次运行产生逻辑读消耗995k块次，物理读323块次，按照标准块8k计算，需要消耗逻辑读7.9G，物理读2.5G。

系统消耗主要表现在ID=6，ac82_110共约6.4亿行数据，idex_ac82 110_7的distinct值为509。

2.1.3采用sql profile绑定

绑定后，SQL执行时间由207秒缩短至4秒，执行效率提高约50倍;物理读消耗由324k减少至4k，物理读消耗减少约80倍;逻辑读消耗由1M块次减少至108k，逻辑读减少约10倍。

2.2索引调优

能否有效使用索引是数据库是否取得高性能的关键。因为查询主要性能开销是磁盘I/O，而全表扫描会产生大量的磁盘I/O，而使用索引直接指向数据存放位置，则只需少量的磁盘读取操作，避免了全表扫描带来的性能开销，从而加速数据的查询过程。但是，索引也会使数据库在执行增、删、改等操作时增加额外的系统开销，并且索引本身也会占用数据库的空间。因此，索引并不是越多越好，只有建立合理有效的索引才有助于改善数据库性能。合理有效的索引是建立在对各种业务场景熟悉，科学的查询分析和预测基础上的。

2.2.1要执行的SQL文本

2.2.2 SQL执行的相关统计信息

平均逻辑读达87k块次，采样期为两天，两天执行18799次，属高执行频次，致使总逻辑读达1.6G块次。

2.2.3 SQL执行计划

2.2.4创建索引

从上述执行计划可看到，执行计划中存在索引跳跃扫描，而该索引得前导列yaz040的基数达15万多，这样的跳跃扫描很消耗性能，所以建议在AAZ288列上单独创建索引。创建索引语句如下：

2.2.5 SQL创建后的效果

通过创建索引后，执行时间只有0.01s，平均逻辑读只有几百块次甚至更低。

2.3 SQL语句本身优化

在数据库系统中，使用SQL不能仅仅关注执行结果的正确性，更应关注在不同的软、硬件及网络环境和业务场景下存在的性能差异，这种性能差异在大型甚至超大型数据库环境中尤为明显。本人在工作和学习的实践中发现，性能低下的SQL往往来自滥用索引、连接的误用和无法优化的where子句。通过避免以上问题，可以明显提高SQL运行效率。

2.3.1要执行的SQL文本

2.3.2 SQL执行的相关统计信息

SQL在统计单次执行时间约40分钟，单次逻辑读8.9M块次，按标准块8k计算，需要消耗约70G的逻辑读。

2.3.3执行计划

注意到id=8，10，7，id=8通过id=9即KB05K1的自链接条件返回了851条结果集，然后作为驱动表同id=10做了一个filter，这意味着要对KB05K1做约800次的扫描。性能消耗就出在这里。

2.3.4 SQL语句优化调整

2.3.5 SQL语句优化后的执行效果

将源语句标亮部分造成的多次大表扫描，用分析函数替代，可只走一次扫描。逻辑读由原来的8900k块次变为137k块次，缩小65倍。查询由原来的40分钟变为3秒。

3结束语

文章对Oracle数据库性能调整和优化进行了简要的分析和研究，对数据库设计、SQL的性能等的优化进行了探讨。但在实际工作中，针对不同的数据量级、不同的软件、硬件环境以及网络环境，需要综合考虑各种方法和制定多种措施。Oracle数据库性能优化是一项系统工程，需要对数据库系统的运行状态做出全面的评估并根据工作实际情况系统地动态调整数据库以得到最优的性能。