产销系统后台Oracle数据库优化设计与实现

刘泽辉

（天津天铁冶金集团技术中心，河北涉县056404）

0 引言

天铁集团内先后建设的大型系统，如产销、生产系统以及财务nc系统，为节约管理成本、提高数据共享度，业务数据一般都比较集中，导致数据库中存放的数据量很大。如何对其进行快速访问，提高系统响应时间就显得十分重要。在实际应用中，往往采用各种优化措施，使SQL查询经过数据库优化器的处理，得到最佳执行计划，即数据访问路径，提高响应速度。本文根据Oracle的特点，并结合SQL语句查询特点，有针对性地提出了一些优化方法，从而达到了缩短相应时间，提高响应速度的目的。

1 目前产销系统现状

为了不影响系统的正常使用，搭建了运销系统的测试环境，其后台数据库为正式环境最近的完整数据库，以此来模拟用户使用的真实场景。针对数据量多的形式发票表、材料履历表、发货材料表、码单表、结齐单等十几个表进行了模拟，发现使用目前的SQL语句进行查询、更新等操作得到结果集，少则需要5～6 s，一般需要10 s左右，有的甚至达到了20 s以上，这个响应速度难以满足用户使用要求，因此必须对其进行优化，以提高响应速度。

2 设计思路及技术实现

对Oracle进行如下优化：实例调整、SQL调整。其中实例调整主要是结合Oracle的特点，针对Oracle的一些参数进行优化；SQL语句调整则是对具体语句进行分析，找出消耗资源多的，通过优化器等工具，设置正确的索引，避免不必要的全表扫描。

2.1 实例调整

首先进行实例调整。实例涉及到SGA内存区和一组Oracle后台处理进程。对实例的调整就是对SGA内存区和Oracle后台处理进程的调整。因为SGA在oracle中的重要性，对该问题的解决中，主要是针对SGA内存区的调整。

2.1.1 内存区结构

SGA就是系统全局区，是指内存中允许多个进程相互通信的区域。在Oracle中，SGA对所有进程来说都是全局可用的。图1为SGA结构图。

2.1.2 调整结构

由于Oracle的一些重要操作均在SGA中进行，如语句解析、临时数据存储等，SGA的大小会影响数据的查询。据实验统计，在Oracle对数据过程中，同样的数据从内存中读取要比从磁盘中读取速度要快的多，一般是磁盘读取的十几倍，因此要尽可能地将数据保存在内存中，减少磁盘的I/O操作。但若SGA太大，会减少操作系统的内存，使得一些应用程序不能运行，出现系统崩溃现象，一般SGA大小为物理内存的50%～60%。从运销系统Oracle数据库的SGA参数上看，其大小为所使用操作系统的40%，设置上偏小，需要增加SGA所占内存的比重。

修改SGA的部分参数。Oracle的SGA由buffer cache、shared pool、large pool等几部分组成，当多用户查询数据量增大时，会出现内部争用内存的现象。为此Oracle10g的版本新增一个参数SGA_TARGET，来实现内部的自动调整。而产销系统所使用的数据库版本也为Oracle10g，而其在当初设计时，并没有使用该参数，使得内部争用内存的现象时有发生，影响了查询的相应速度，因此将该参数启用，实现Oracle的SGA自动管理。

图1 SGA结构图

2.2 SQL语句调整

2.2.1 索引设置

在对大表进行扫描时，首先要避免不必要的全表扫描。最常见的做法是给大表添加索引。给大表添加索引后，可以通过访问索引的方式获得记录的物理位置，从而达到访问表的目的。对于一个拥有大量字段的表，如果只需要返回其中少量字段，那么在这些字段上建立索引，通过索引访问获得记录，将大大降低物理磁盘读写次数，从而降低了整个查询响应时间。通过对运销系统典型响应速度慢的几个画面进行查看，发现共同特点就是其用来查询的某些SQL语句进行了全表扫描，相当于一个上千万的数据表，要一行行读取，即使计算机速度再快，其响应时间也至少在10s甚至几十秒以上，且不考虑数据远距离传输的问题。针对这些语句进行分析，查看使用的SQL查询语句是否有条件限制，并结合表结构分析语句引用条件是否为索引。通过对表结构进行查看，发现部分表虽然设置了索引，但在SQL语句中并没有引用，也就是说索引并没有起作用，有的表甚至没有索引，这导致了在进行操作时，出现了数据库查询中最应避免的全表扫描现象发生，从而造成查询速度降低。针对这些情况，对产销系统内经常使用的表，特别是一些大表经常使用的字段上添加或修改了索引，对一些没有使用条件限制的语句，通过分析PC文件以及运销系统上下之间的流程，对语句进行修改，增加索引的引用。

2.2.2 删除不必要的索引

在Oracle数据库中，索引也并不是万能的，在对运销系统的材料履历表进行分析时，发现虽然查询语句引用了索引，但通过模拟试验结果发现该查询花费了比全表扫描更多的响应时间，这是由于O－racle对这个索引进行了全索引扫描，造成物理读写数量与全表扫描相比，不仅没有减少，反而大幅上升。究其根本是这个表中的记录值几乎都不相同，而查询时不仅需要近乎全表扫描，而且还需要按索引进行排序，于是出现了速度更慢的问题。因此采用删除索引，对数据量大的表采取分区的方法，来提高查询效率。

2.2.3 将调整持久化

Oracle的执行计划是依据各种情况，如表的统计资料而变化的，但有时这种变化是不需要的。为了将已经调整好的SQL执行计划固定，可以用Oracle的工具将执行计划持久化存储。

3 结束语

调整后再次进入测试环境，对一些画面进行测试，发现响应时间有了很大提高，绝大部分从十几秒缩短到10 s以下，有的甚至降低至2～3 s，基本实现了产销系统后台Oracle数据库的优化目的，达到了预想效果。在经过多方测试后，将优化方法应用于正式环境，提高了用户满意率。