高扬制丝SPC分析系统Oracle数据库优化探索

【摘　要】高扬制丝SPC分析系统长时间运行之后，性能下降，为满足用户需求，必须针对其后台Oracle数据库根据数据采集系统的特点实施合理的优化,一方面对Oracle数据库的缓存区、系统进程进行调整，另一方面对数据的物理模型进行恰当的修改,使应用系统处于良好的运行状态。本文描述了优化的全过程。 【关键字】SPC　数据采集　Oracle　优化　性能调整 前言 　　高扬制丝统计过程控制(SPC)系统于2004年3月正式投入运行，制丝线全面实施统计过程控制为完善整条制丝线的集中监控，进一步挖掘出历史数据蕴涵的价值，提高制丝线的过程控制水平构建了良好的平台。而SPC系统分析所用到的数据是由位于制丝控制室的上位机负责传送到Oracle数据库中的，每隔六秒就采集一次全线五个工艺段的22个质量特性值。由于采集频率较大，系统运行较长一段时间之后，积累数据已达4GB，数据库负荷较重，响应时间较长，达不到用户需求，必须对数据库实施优化，在满足数据采集速度的基础上尽可能的保证用户查询分析操作的正常进行。 　　Oracle公司提供了许多优化工具，数据库管理员利用这些工具进行周期性的调整可以防止出现数据瓶颈，使数据库达到最佳性能以满足用户的需要。本文拟对在优化过程中遇到的问题进行分析，并阐述优化方案实施的细节。 　　 一、背景简介 　　1.1　软硬件环境 　　高扬SPC系统后台数据库为9.2.0.4版Oracle数据库，运行于DELL POWEREDGE 4600 NT服务器上，服务器操作系统为Win2000 Server、内存4G、双Intel P4 Xeon CPU，存储设备为Dell PowerVault 220S磁盘阵列。 　　1.2　应用类型 　　高扬SPC系统作为生产现场数据采集系统，属于比较典型的决策支持系统(DSS),用户对数据基本只查询不修改，负责上传送数据的上位机也基本只添加数据，对历史的数据不会发生修改。 　　1.3　数据字典 　　高扬SPC系统数据库包含2张业务表、11张代码表、5个视图，其中，最重要的是GY_AUTOSAMPLE表，它记录了所有自动采集的22个质量特性值的数据，目前共有3120 批次的2362万行数据，该表上建立了5个索引。 表1　业务主表GY_AUTOSAMPLE的字段定义情况 表名   行数 占用空间 gy_autosample 23624029 1243M 字段名 类型 描述 QualityIndexCode VarChar2(2) 质量指标代码 BrandCode VarChar2(2) 牌号 ShiftTypeCode VarChar2(1) 班别代码 ShiftGroupCode VarChar2(1) 班次代码 BatchNo VarChar2(12) 批次号 SampleTime VarChar2(14) 采样时间 QualityIndexValue Number(8,2) 质量指标值 ProductionStatus VarChar2(1) 生产状态 表2　业务主表GY_AUTOSAMPLE的索引定义情况 类型 索引名 索引列 唯一 占用空间 主键 PK_AUTOSAMPLE SampleTime  QualityIndexCode 是 413M 外键 BRAND_AUTOSAMPLE_FK BrandCode 否 350M 外键 SHIFTGROUP_AUTOSAMPLE_FK ShiftGroupCode 否 378M 外键 SHIFTTYPE_AUTOSAMPLE_FK ShiftTypeCode 否 364M 外键 QUAINDEX_AUTOSAMPLE_FK QualityIndex 否 302M 合计     1807M 　　1.4　数据模型 　　用户在使用SPC系统过程中最常用到的功能是数采历史信息菜单下的批次综合查询和详细信息查询 。因这两个查询功能响应时间较慢，而成为此次优化的重点攻关内容。模块内嵌SQL语句涉及到的业务表、代码表的物理模型如图1所示：

图1　自动数据采集Schema物理模型 　　1.5　优化前性能 　　这次针对SPC系统的Oracle数据库优化方案是建立在Oracle提供的状态收集和性能诊断工具StatsPack连续一周采样的数据基础之上的，Statspack除了查找实例中的性能问题外，还可以查找应用程序中高负荷的SQL语句，很容易确定Oracle 数据库的瓶颈所在，并且记录数据库性能状态。这次Statspack监测快照级别选择的是LEVEL 5(一般性能统计 SQL语句)。快照门限根据决策支持系统的特点做了调整，目的是缩小范围，快速定位运行效率较低的SQL语句。 表3　Oracle StatsPack快照门限参数 快照门限参数 参数意义 设置值 默认值 executions_th sql语句执行的次数 5 10 Disk_reads_th sql语句执行的磁盘读取数量 3000 1000 parse_calls_th sql语句执行的解析调用数量 2000 1000 buffers_gets_th sql语句执行的缓冲区获取的数据量 20000 10000 StatsPack输出的性能分析报告见结束语中优化前后性能比较表。　 　　 二、优化方案 　　根据StatsPack输出的分析报告，对数据库进行了以下四个方面的调整。 　　2.1　大表分区 　　由于GY_AUTOSAMPLE表占用空间已达4GB,且StatsPack报告反映出文件离散读取(DB File Scattered Read)等待事件比较显著，分析后得出如下几个主要原因：一是22个关键特性值每隔6秒同时插入到数据库的同一普通表中，这些数据存放在同一个Segment、Extent乃至同一个DB块的可能性极大,不同特性值的数据混杂在一起，不能从物理上加以区分。而用户每次查询时只会关心一种特性值的数据，将质量标准上下限不同的特性值数据放在一起画CPK图是毫无意义的。这就势必造成在查询一个特性值的历史趋势时，Oralce同时读取了用户并不需要的其它21个关键特性值(Oracle读取最小的单位是DB块)。二是init.ora参数db_file_multiblock_read_count设为16，即在读取一个DB块时同时读入邻近的16个DB块，Oracle进一步读取了无用的数据。三是使用CBO优化器模式,在统计更新不及时的情况下， Oracle可能会自动跳过索引，执行全表扫描。这些原因都造成了文件离散读取等待时间过高。 　　解决的办法是利用Oracle提供的分区技术，将一个很大的表根据一定的规则分别存储到不同的区域，这样可将逻辑上的一张表物理存储在多张小表中，分区的优势在于：一、增强可用性，如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍然可以使用；二、减少关闭时间：如果系统故障只影响表的一部分分区，那么只有这部分分区需要修复，故能比修复整个大表花费的时间更少；三、维护轻松：如果需要重建表，独立管理每个分区比管理单个大表要轻松得多；四、均衡I/O：可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能；五、改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快；六、分区对用户透明，最终用户感觉不到分区的存在，避免了应用程序的修改升级。 　　Oracle9i中提供了三种分区方式，列表分区(List)、范围分区(Range)、散列分区(Hash)，其中,根据特性值代码进行列表分区方式非常适合SPC系统的实际需求，即先为每个关键特性值建立单独的表空间,然后定义分区规则，将不同的关键特性值移入对应的分区中，最后出于兼容性考虑，建立一个默认分区(GYWEB_P23)存放将来可能出现的新的特性值，创建分区的SQL命令如表4： 表4　建立分区表SQL语句

　　在创建分区之后，应根据实际情况调整存储参数使运行效率进一步提升。可有两种选择，一种是改变空间管理方式为自动区段空间管理：原来的本地管理表空间(LMT)是通过把EXTENT MANAGEMENT LOCAL子句添加到tablespace的定义句法而实现的。和旧的字典管理表空间(DMT)不同，LMT实现了扩展管理自动化。而9i第2版中新添加的自动区段空间管理（ASSM）是通过将SEGMENT SPACE MANAGEMENT AUTO子句添加到tablespace的定义句法里而实现的。它使用位图freelist取代传统单向的链接列表freelist，ASSM的表空间会将freelist的管理自动化，并取消为独立的表和索引指定PCTUSED、FREELISTS和FREELIST GROUPS存储参数的能力。换言之，ASSM就是将存储管理的决定权完全交给Oracle控制；另一种选择是仍使用LMT方式，但修改PCTFREE参数由原来的20%改为2%，依据是数据只插入不修改删除，为了使数据存储更加紧密，每一次Disk Read读入的数据行数更多，可将每个DB块写满98%才停止插入。在查阅了Oracle官方文档和在测试环境进行实验之后，发现LMT方式占用空间相对节省，同时也能保证数据采集的速度，是更理想的选择。 表5　修改存储参数 ALTER TABLE GYWEB.GY_AUTOSAMPLE MODIFY DEFAULT ATTRIBUTES PCTFREE 2 　　在正式环境将数据迁移至新建的分区表并创建本地分区索引之后，追踪详细信息查询模块相关SQL语句的执行计划，发现查询的数据范围已由原来1.2GB范围缩小到60M，提高了运行效率。 　　2.2　索引调整 　　索引对于提高检索数据的速度起着至关重要的作用，在分析了StatsPack报表中利用率最高的30句SQL语句及监控所有的索引利用情况之后，发现SPC系统的索引存在着一些性能问题。 表6　监控索引使用情况 ALTER INDEX BRAND_AUTOSAMPLE_FK MONITORING USAGE; select owner,index_name,table_name,used from all_object_usage where used = "NO"; 　　1）GY_AUTOSAMPLE表中四个分别指向牌号,班别代码,班次代码，质量指标值代码表的