基于ERP系统的数据库性能优化分析(许培洪)).docx

南 阳 理 工 学 院 本 科 毕 业 设 计（论文）基于ERP系统的数据库性能优化分析Makes database performance optimazition analysis base on ERP system学 院（系）： 计算机科学与技术系 专 业： 计算机科学与技术 学 生 姓 名： 许培洪 学 号： 64107077 指 导 教 师（职称）： 王秋芬(讲师) 评 阅 教 师： 完 成 日 期： 2011年5月 南阳理工学院Nanyang Institute of Technology基于ERP系统的数据库性能优化分析计算机科学与技术专业许培洪摘要随着信息技术的不断发展，中小型企业信息化建设越来越重要，采用先进的企业资源计划（ERP）系统已势在必行。ERP是顺应时代要求的信息技术与企业管理新思想相结合的产物。对商业套装软件进行性能优化是比较困难的,但仍有机会对它进行调优.只要对应用系统有正确的理解,提供时间和相关资源,IT团队就能够改善复杂关键应用的性能。本文在系统分析研究ERP原理的基础上，通过对系统开发与实现过程中所涉及的理论和技术的研究，分析了ERP系统的功能模块结构，对后台oracle数据库做性能优化分析，并做持续跟踪优化。关键字 ERP; IO，负载均衡，AWR，响应时间，并发 Makes database performance optimazition analysis base on ERP systemComputer science&technology majorXuPei hongAbstract:With the further development of information technology,the informatization construction of small and middle-size enterprises becomes more and more important.The use of advansed ERP is on the right way.ERP is the the combination of information technology and new enterprise management complied with the demand of era.It is comprasively difficult to performance optimazition of the commercial software package,but aslo has chance to make it perform better.If only with the correct comprehension of application system and providing time and related resources,IT groups can improve the perfomance of complex and key application. Based on the systematic analysis research on the theory of ERP system,this article analyses the function module structure of ERP system through the research on theory and technology related to system development and implementation process.Tt also makes performance optimazition analysis to the backstage oracle database and does the continuous tracking optimization.Key word: ERP; IO;AWR;Outbound Load Balancing; paratera;目 录 1.ERP系统的现状12.ORACLE数据库体系结构22.1物理结构22.2逻辑结构22.2.1块(block)32.2.2区(extent)32.2.3段(segment)42.2.4表空间(tablespace)43.主机性能调优43.1内存分配43.2CPU响应时间53.3IO和并发53.4其他磁盘优化64.参数调优65.SQL调优75.1通过HINT来强制执行计划75.2变量绑定95.3使用索引105.3.1管理组织索引105.3.2聚簇的使用115.4使用分析函数115.5利用or代替 union all126.总结和建议146.1要有ORACLE优化意识156.2优化有步骤可遵循156.3要做大基准测试166.4避免重复发明轮子166.5力求使用简单方法166.6设计非常重要177.结束语17参考文献181. ERP系统的现状随着信息技术的不断发展，中小型企业信息化建设越来越重要，采用先进的企业资源计划（ERP）系统已势在必行。ERP是顺应时代要求的信息技术与企业管理新思想相结合的产物。目前国内外的ERP系统是一类高度集成的软件，其涉及到众多的计算机技术。而ERP系统又不仅仅是一个软件，更重要的是一个管理思想，它实现了企业内部资源和外部资源的整合通过软件把企业的人、财、物、产、供、销及相应的物流、资金流、管理流、增值流紧密地集成起来。ERP系统的开发需要依靠具有一定的开发经验和很好的技术基础的开发公司来完成。企业所处的环境是不断变化的：企业的产品种类、产品所处生命周期的阶段、企业的计划模式、分销模式都不断变化，企业不断地进行业务流程的再造，企业的规模不断地缩小或者扩展，总之企业的变化是绝对的。对于国内的ERP软件供应商来说，即使软件的开发是对国情深入了解的前提下，即使他们的软件系统功能再全、适应性再强，当面对不通企业千差万别的具体情况和不同企业千变万化的特殊需求时，也不可能以以千变应万变。因而，客观行要求ERP系统具备适应各种变化的能力。而另外一方面，随着时间的推移，系统负载的增加，系统性能将下降，企业业务可能受到影响。因此不管企业采用国内还是国外的软件，都面临着系统的二次开发和性能优化问题。对商业套装软件进行性能优化是比较困难的,但仍有机会对它进行调优.只要对应用系统有正确的理解,提供时间和相关资源,IT团队就能够改善复杂关键应用的性能。以oracle ERP 为例，ORACLE应用系统充分采用了数据库上的先进技术，将有些系统功能放到数据库中去实现，而不是通过编程的方式，因而大大简化了程序，提高了效率。ORACLE 电子商务套件已经脱离了传统的ERP软件模式，提供了集成的商业智能、个性化管理界面、工作流和告警等全新的功能。传统的ERP软件，用户需要进入层层菜单，运行查询或报表，才能得到业务数据。而使用ORACLE，用户可以在个性化的企业门户网页中，自由定义所需的智能报表，就能迅速了解企业、相关业务的执行情况。系统还能够对非正常业务自动告警。ORACLE 系统以人为本，帮助企业的管理人员充分利用ERP的业务数据，更高效地管理企业。本文在系统分析研究ORACLE ERP原理的基础上，通过对系统开发与实现过程中所涉及的理论和技术的研究，通过对后台ORACLE数据库的架构进行分析研究，提出基于ERP系统的数据库性能优化模型，并做持续跟踪优化。2. ORACLE数据库体系结构Oracle数据库在存储数据的时候并不是简单地进行数据堆砌，而是由一整套严谨，高效的逻辑结构来管理数据库的存储，因此数据库的存储结构也可以分为两大类，物理结构和逻辑结构。物理存储结构对应一系列的不同格式、类型、作用的文件，用来存储对象及物理数据，逻辑结构则是oracle内存存储机制。2.1 物理结构数据库由一系列物理文件组成，其中包括控制文件，数据文件，日志文件，临时文件等，他们在DBMS中充当不同的角色，共同协调DBMS的正常运行。我们可以建立一个模型。DBMS相当于一个公司，而控制文件是老板，只负责发号施令，数据文件是忠实的员工，只负责执行任务，而临时文件相当于公司的公用资产，谁都可以使用，日志文件了，就相当于公司买的保险了，用的上的时候才能用上。本论文调优涉及到数据文件的，我概要介绍一下数据的存储机制。数据库中每条数据都存储在数据文件中，一个数据库拥有很多数据文件，一个数据文件在物理上对应一个操作系统文件。Oracle 在创建数据文件时，是通过操作系统在指定路径下分配一块磁盘空间并将其格式化。操作系统把这块存储区域分配给这个数据文件，并赋予其写磁盘的权限。但是我们存储数据的时候，数据会被随机存储到数据文件中，这是因为数据文件是一个物理的概念，我们不能指定在创建对象的时候指定它到那个数据文件中去，只能指定到哪个表空间。当然我么也可以通过动态视图来查看一个数据文件中拥有哪些对象。selectb.segment_name ,A.FILE_NAME,b.BYTES,b.BLOCKS ,a.BYTES,a.USER_BYTES from dba_data_files a,dba_extents b where a.file_id=b.file_id and a.file_name = 'D:LMISDATAFILEDATA27.DBF'2.2 逻辑结构数据库的物理存储结构对应一系列的物理存储文件，而数据是如何存储的？以什么机构存储到数据文件中的？这要取决于逻辑存储结构。Oracle数据库执行的每次操作都是从逻辑上定义一组结构，操作的数据可以一步步细分为不同的存储单元，oracle操作数据的过程，实际上就是对不同级别的存储单元进行维护和管理的过程，下面让我们来了解一下数据库的存储单元。按照如下从小到大顺序，逻辑存储单元可以做如下划分。2.2.1 块(block)块是oracle存储结构中的最小存储单元，所以数据的存储都是以块为单位进行存取的，块的大小可以通过出初始化参数db_block_size来设置。我们知道所有的读写操作都反映在磁盘IO上，最终的操作单位是字节，如果每次读写都是以字节为单位进行，将会是非常慢的，不同文件的默认块大小也不一样，般都设置成8KB或者是16KB. 下图为数据块的剖面图：数据块头行记录行目录表目录空闲记录图2-1 数据块剖面图我们简要介绍行记录和空闲空间，当行记录有写入数据的时候就存储在行记录中，当数据被删除时这部分空间又会转换成空闲空间。空闲空间是当前块的可用空间，当对现有数据进行update和Insert的时候就是从这部分空间分配容量来写入数据，如果执行UPDATE的时候，块中的空间不足以存储被修改的数据，那么记录就将被存储到另外一个拥有足够空间的块中，而只在原块中保留一条指向新块的rowid，这种现象就是传说中的行迁移(Row Migration)。12.2.2 区(extent)区是oracle 最小的分配单位，有一组连续的块组成，这些块可能物理上并不连续，但是要属于同一个物理文件。单个区在分配时候不能跨文件分配，而我们数据库创建对象的时候至少为为该对象初始化一个分区初始化分配的空间叫走初始区。随着对象大小的不断增加，操作初始区后oracle还再为对象分配扩展区。（Incremental Extent），扩展区不一定药与初始化分区连续存放。2.2.3 段(segment)一个段又很多分区组成，以前段可以理解为一个对象，但是随着软件版本的演进，存储一个对象可以存储到不同的段中。比如一个对象包含索引，LOB类型，那么该对象会分别存储到表段，索引段，LOB段。如果一个单纯的堆组织表，那么该表只存储到一个段中，不管该表包含多少的数据。2.2.4 表空间(tablespace)一个表空间从逻辑上定义，是有多个段组成的从物理上定义，是由多个数据文件组成的。表空间是oracle逻辑上分配的最大存储单位i，我们平常做的创建对象操作都在表空间一级进行，如创建存储对象的时候只能指定在哪个表空间进，而不能指定存储到更细粒度的存储单元了，更不能指定存储到哪个数据文件中。分析数据库的体系结构是为了更好地建立数据库优化模型下图为整体优化的模型： 图2-2 数据库优化模型3. 主机性能调优3.1 内存分配我们知道在创建数据库的时候给ORACLE分配一个SGA（system global area），SGA越大，数据库可用的内存就越大。我们操作系统一般是32位的，最大寻址空间为2的32次方，即4G的内存大小。当我们的操作系统是64位的时候，最大寻址空间变为了2的64次方了。在创建数据库的过程我们一般是手动设置内存分配大小，合理地设置data buffer、share pool、 log buffer 等大小能使系统的性能提升更快。3.2 CPU响应时间数据库服务器响应时间由CPU处理时间和等待时间组成，其中等待时间往往和某种瓶颈有关，如ORACLE数据的写出需等待日志先写出（lgwr进程），如果日志所在磁盘出现异常，那数据写出进程（dbw进程）再快也需等待！CPU处理时间中，重点在SQL执行时间，我总结出“访问量”除以“吞吐量”的方式，并将访问量细化为“执行次数”和“单次访问量”，这样目的在于，优化可从减少访问量入手，也可从降低执行次数着手！数据库响应时间如图二所示：图2-3 数据库响应时间模型3.3 IO和并发数据库的硬件设计包含了数据库服务器的架构和数据存储，这些因素在数据库设计阶段将作为重点的考虑因素，我们应该充分考虑到数据库可能达到的最大并发数，对于OLTP系统，并发将是一件非常重要的事.如果没有在设计阶段考虑到，可能会出现以下后果： a.系统资源严重被用，系统过负荷运行。 b.严重的等待时间，可能造成系统频繁锁及热块的产生。对应IO问题，可以通过以下的语句来判断：Select name phyrds,phywrts,readtim,writetim from v$filestat a,v$datafile b where a.file#=b.file# order by read time desc3.4 其他磁盘优化在对ERP系统做优化的过程中还总结了一下提高性能的方法A 分离表空间、oracle的安装目录、联机重做日志、经常被访问的数据文件、索引表空间，避免磁盘竞争。B 增大日志文件的大小，从而增加处理大型insert,update,delete 操作的比例。C 增大日志组成员，提倡一个主日志文件加一个多路利用文件。D 不要在系统表空间中执行排序。4. 参数调优我们来认识以下数据库的一些重要的初始化参数SGA_MAX_SIZE SGA_TARGETPGA_AGGREGATE_TARGETDB_CACHAE_SIZESHORE_POOL_SIZE4.1 调整DB_CACHE_SIZE来提高性能这个参数设定了用来存储和处理内存中的数据大小，我们知道从内存中读取数据比磁盘中快1000倍左右，DB_CACHE_SIZE越大能增大数据读取的缓存命中率。4.2 设定DB_BLOCK_SIZE来反映数据读取量大小OLTP一般为8K，OLAP一般为16K或者32K。4.3 调整SHARE_POOL_SIZE来优化性能此参数越大，说明共享的SQL越多，使得在内存中便能够找到使用过的SQL语句，为了减少硬解析次数，优化对共享SQL区域的使用需尽量使用存储过程、使用绑定变量。4.4 调整PGA_AGGREGATE_TARGET以优化对内存的应用OLTP:总内存*80%*20%。OLAP总内存*80%50%。5. SQL调优 系统正在运行的时候，我们做系统调优应该尽量避免改写sql语句，改写SQL语句意味着一些代码要重新编写，调试，运行，测试。这样会给系统带来一定的风险，但是我们日常维护中，涉及到一些查询模块的操作我们可以在查询维护工具里面改写SQL.下面我们从几方面来SQL调优5.1 通过HINT来强制执行计划HINT是oracle提供的一种sql语法，他允许用户在sql语句中插入相关的语法，从而影响SQL的执行方式。下面来看一条系统里面的查询语句 Select count(* )from ck_rwd_mx_old Select /* + index(t IDX_CK_RWD_MX_BHLSH)*/ count(*)from ck_rwd_mx_old两者一共返回7161482 第一个sql执行时间为 21秒，第二个执行时间为3.75秒，很明显我们看到加了HINT的，强制进行索引扫描，效率明显提高了。当然，我们也可以根据应用的需要添加HINT，比如有些应用只是需要找到符合条件的第一条数据这时我么可以使用如下HINT:SELECT /*+FIRST_ROWS(1)*/ STORID,FROM TAL_SOTEOracle 默认的 OPTIMIZER_MODE 是ALL_ROW,基于规则的优化器 ，加了FIRST_ROW后强制OPTIMIZER_MODE为FIRST_ROW;FIRST_ROW还可以指定参数FIRST_ROW(n)在日常维护中还发现一个问题，比如我用select /*+index(t ckt_spkfk)*/ count(*) from spkfk;执行计划如下：Execution Plan- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=23 Card=1) 1 0 SORT (AGGREGATE) 2 1 INDEX (FAST FULL SCAN) OF 'PK_SPKFK' (INDEX (UNIQUE) (C ost=23 Card=31883)而我执行select /*+index(t ckt_spkfk)*/ count(xslb) from spkfk;执行计划如下：Execution Plan- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=444 Card=1 Bytes=3 ) 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'SPKFK' (TABLE) (Cost=444 Card=31 883 Bytes=95649)当我执行 select /*+index(t ckt_spkfk)*/ count(spbh) from spkfk;执行计划如下：Execution Plan- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=22 Card=1 Bytes=12 ) 1 0 SORT (AGGREGATE) 2 1 INDEX (FAST FULL SCAN) OF 'CKT_SPKFK' (INDEX (UNIQUE) ( Cost=22 Card=31883 Bytes=382596)我们看到第一个求count(*) 变相选择PK_SPKFK做为索引扫描，第二个HINT被忽略了，而做全表扫描，只有第三个求count(spbh),选择索引扫描，为什么呢？因为，要对表的记录求总数，HINT的索引是唯一索引且不为空，如果CBO选择在索引上做了COUNT,当索引字段有空值时，count字段必然不准确，当做COUNT(*)的时候oracle会默认选择不为空的的索引做扫描，这就是基于代价的优化器（CBO）的好处了。而第二个求count(xslb)因为改字段可为空，oracle找不到合适的索引做扫描，而HINT使用的CKT_SPKFK是建在spbh字段的索引，做count的时候结果必然不准确。比如T表实际上有一千条数据，spbh字段有100空，xslb有200个空，这样求count(xslb)使用索引扫描得出的是900个，显然数据是错误的。所以这种情况下CBO是不会做索引的，因为他会导致错误的结果。而当在索引上创建了主键pk_spkfk，这样就保证了该字段不为空。当再次地表做count(*)的时候指定的HINT就起作用了，因为索引不为空，对索引的COUNT和对表的COUNT值是相同的。还有在日常维护中，发现表关联的操作比较多，经过对系统的实验得出了一个结论：大表间的关联操作使用HASH JOIN会比 NESTED JOIN效率高一点，小表间的关联通常选择NESTED JOIN 比HASH JOIN效率高些，而一般能用MERGE JOIN提高性能的地方，HASH JOIN都会发现更出色的性能。5.2 变量绑定变量绑定是OLTP数据库系统中一个非常重要的技术点。良好的变量绑定会是OLTP系统数据库中的SQL跑的飞快，内存效率极高；不绑定变量可能会使OLTP数据库不堪重负，资源被SQL解析严重消耗，系统显得滞重而缓慢。我们知道一条sql在数据中是怎么被执行的，一条SQL被发送到服务器中，会做一个HASH函数运算，得到一个HASH值，然后到共享池中找到是否有何这个HASH值匹配的SQL存在，如果找到了，oracle直接使用已经存在的执行计划去执行这条SQL，然后将结果返回给用户，如果在共享池中没有找到，则把他当做一条新的SQL,将会按照以下顺序执行，语法分析->语义分析->生成执行计划->SQL的执行。下面对比一个一条SQL执行了10000次时，绑定变量和非绑定变量在资源上的消耗情况例一：PARSING IN CURSOR #1 len=119 dep=0 uid=55 oct=47 lid=55 tim=1017528684 hv=293694880 ='c6937c78'beginfor x in 1.10000 loopexecute immediate 'select * from all_objects where object_name=:x' using x;end loop;end;END OF STMTPARSE 1:c=0,e=1536,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1017528682执行时间：0.71+0.48=1.19秒CPU时间：0.6+0.2=0.8秒分析次数：34次执行次数：10084次例二 PARSING IN CURSOR #1 len=113 dep=0 uid=0 oct=47 lid=0 tim=4150023581 hv=1876790916 ad='c66d8260'beginfor x in 1.10000 loopexecute immediate 'select * from all_objects where object_name='|x; end loop;end;END OF STMTPARSE 1:c=0,e=1701,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=4150023579执行时间：93.98+1.93=95.91秒CPU时间：89.64+1.92=91.56秒分析次数：30002次执行次数：30003次两种情况对比的结果显示，绑定变量SQL的资源消耗要远远少于未绑定变量的SQL资源消耗，sql的执行次数越多，这种差距就越明显，未绑定变量SQL的资源主要消耗在产生递归SQL中，这些SQL主要是对SQL语句做硬分析时使用的。如果我们让所有用户在数据库操作中传给ORACLE的都是这样一个由变量代替常量的SQL,那么oracle只需要硬分析第一条sql就可以了，这样省掉了前面很耗资源的硬分析过程。5.3 使用索引5.3.1 管理组织索引索引可以大大加快数据库的查询速度，索引把表中的逻辑值映射到安全的RowID，因此索引能进行快速定位数据的物理地址。但是有些DBA发现，对一个大型表建立的索引，并不能改善数据查询速度，反而会影响整个数据库的性能。这主要是和SGA的数据管理方式有关。ORACLE在进行数据块高速缓存管理时，索引数据比普通数据具有更高的驻留权限，在进行空间竞争时，ORACLE会先移出普通数据。对一个建有索引的大型表的查询时，索引数据可能会用完所有的数据块缓存空间，ORACLE不得不频繁地进行磁盘读写来获取数据，因此在对一个大型表进行分区之后，可以根据相应的分区建立分区索引。如果对这样大型表的数据查询比较频繁，或者干脆不建索引。另外，DBA创建索引时，应尽量保证该索引最可能地被用于where子句中，如果对查询只简单地制定一个索引，并不一定会加快速度，因为索引必须指定一个适合所需的访问路径25.3.2 聚簇的使用Oracle提供了另一种方法来提高查询速度，就是聚簇（Cluster）。所谓聚簇，简单地说就是把几个表放在一起，按一定公共属性混合存放。聚簇根据共同码值将多个表的数据存储在同一个Oracle块中，这时检索一组Oracle块就同时得到两个表的数据，这样就可以减少需要存储的Oracle块，从而提高应用程序的性能。优化设置的索引，就必须充分利用才能加快数据库访问速度。ORACLE要使用一个索引，有一些最基本的条件：a、where子名中的这个字段，必须是复合索引的第一个字段；b、where子名中的这个字段，不应该参与任何形式的计算。5.4 使用分析函数分析函数可以减少表扫描的次数，下面先看一个ERP系统中分析函数使用提高数据库性能的案例：select distinct dsl1.peer_id peer_id, nvl(ne_disconnect_info.ne_state, 1) ne_state from dcc_sys_log dsl1, (select distinct dnl.peer_id peer_id, decode(action, 'disconnect', 0, 'connect', 0, 1) ne_state from dcc_sys_log dsl, dcc_ne_log dnl where dsl.peer_id = dnl.peer_id and (dsl.action = 'disconnect' and dsl.cause = '关闭对端') or (dsl.action = 'connect' and dsl.cause = '连接主机失败') and log_type = '对端交互' and dsl.log_time = (select max(log_time) from dcc_sys_log where peer_id = dnl.peer_id and log_type = '对端交互') ne_disconnect_info where dsl1.peer_id = ne_disconnect_info.peer_id(+)原先至少需要扫描2次！现在根据KEEP结合DENSE_RANK的方式，将表扫描从2次变为了1次，具体代码改写如下：SELECT a.peer_id,CASE WHEN dnl.peer_id IS NOT NULL AND str IN ('disconnect关闭对端','connect连接主机失败') THEN '0' ELSE '1' END ne_stateFROM (SELECT peer_id,MIN(action|cause) KEEP(DENSE_RANK LAST ORDER BY log_time) str FROM dcc_sys_log dsl WHERE log_type = '对端交互' GROUP BY peer_id) a,(SELECT DISTINCT peer_id FROM dcc_ne_log) dnl WHERE a.peer_id = dnl.peer_id(+)5.5 利用or代替 union allselect peer_id 对端标识, null 源域名, null 目标域名, alert_type 告警类型, log_time 告警时间, cause 告警内容, deal_log 处理状态, deal_staff 处理人, deal_time 处理时间, remark 备注 from dcc_sys_log where action = 'disconnect' and cause like '对端被关闭%' and deal_log = 'deal_log' and alert_type = 'alert_type' and log_time >= TO_DATE('2010-08-02', 'YYYY-MM-DD') and log_time < TO_DATE('2010-08-03', 'YYYY-MM-DD') + 1union (select peer_id 对端标识, origin_host 源域名, dest_host 目标域名, alert_type 告警类型, log_time 告警时间, cause 告警内容, deal_log 处理状态, deal_staff 处理人, deal_time 处理时间, remark 备注 from dcc_ne_log where result = 0 and cause like 'parser失败%' and deal_log = 'deal_log' and alert_type = 'alert_type' and log_time >= TO_DATE('2010-08-02', 'YYYY-MM-DD') and log_time < TO_DATE('2010-08-03', 'YYYY-MM-DD') + 1)union (select peer_id 对端标识, origin_host 源域名, dest_host 目标域名, alert_type 告警类型, log_time 告警时间, cause 告警内容, deal_log 处理状态, deal_staff 处理人, deal_time 处理时间, remark 备注 from dcc_ne_log where result_code = 'DIAMETER_UNABLE_TO_DELIVER' and svcctx_id like 'SR-Timeout%' and deal_