Oracle性能优化学习心得.docx

1、Oracle性能优化学习心得Oracle性能优化学习心得一，优化总的原则1， 查看系统的使用情况2， 查看SGA分配情况，结合系统具体情况进行分析。3， 表的设计分析4， SQL语句分析实施要则1， 查看系统的使用情况，CPU占用，内存，I/O读取等Oracle10G提供的Oracle Enterprise Manager图形化工具中的ADDM 和 SQL Tuning Advisor等可以方便的查看系统状况2， OPS上负载均衡，不同查询用不同Instance3， 提供脚本查看SGA使用情况4， 分析SQL执行情况（trace及其他工具）实施细节1， 外部调整：我们应该记住Oracle并不是

2、单独运行的。因此我们将查看一下通过调整Oracle服务器以得到高的性能。2， Row re-sequencing以减少磁盘I/O：我们应该懂得Oracle调优最重要的目标是减少I/O。3， Oracle SQL调整。Oracle SQL调整是Oracle调整中最重要的领域之一，只要通过一些简单的SQL调优规则就可以大幅度地提升SQL语句的性能，这是一点都不奇怪的。4， 调整Oracle排序：排序对于Oracle性能也是有很大影响的。5， 调整Oracle的竞争：表和索引的参数设置对于UPDATE和INSERT的性能有很大的影响。二，调优分类： 对Oracle数据库进行性能调整时，应当按照一定的

3、顺序进行，因为系统在前面步骤中进行的调整可以避免后面的一些不必要调整或者代价很大的调整。一般来说可以从两个阶段入手：1、设计阶段：对其逻辑结构和物理结构进行优化设计，使之在满足需求条件的情况下,系统性能达到最佳，系统开销达到最小；2、数据库运行阶段：采取操作系统级、数据库级的一些优化措施来使系统性能最佳； 设计阶段：A，数据库设计优化较多修改较少查询的数据 和 较多查询较少修改的数据 分别对待。a，结构优化1，根据应用程序进行数据库设计。即应用程序采用的是传统的C/S两层体系结构，还是B/W/D三层体系结构。不同的应用程序体系结构要求的数据库资源是不同的。2，遵循3大范式规范化数据结构，减少不

4、必要的冗余。3，反规范设计，增加必要冗余，提高查询速度。4，针对变化较少的数据，合理创建临时表和视图，需注意对临时表和视图的及时同步更新5，在服务器端使用过程和函数，减少网络数据传输b，I/O优化1， 使用分区，对查询条件字段（主外键）建立索引，依据Rowid物理地址查询，减少I/O的消耗。应避免可能参与计算的字段 2， 建立合适的索引。缺省情况下建立的索引是非群集索引，但有时它并不是最佳的;合理的索引设计要建立在对各种查询的分析和预测上。一般来说:1. 有大量重复值、且经常有范围查询(between, ,=, =)和order by、group by发生的列，可考虑建立群集索引;2. .经常

5、同时存取多列，且每列都含有重复值可考虑建立组合索引;3. .组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。a,定期的重构索引是有必要的. ALTER INDEX REBUILD b,避免在索引列上使用计算，判断或函数处理3，使用聚簇（对列的选择原则同索引）。4，使用存档功能。5，数据缓冲，快照，视图，远程调用等，减少I/O和网络消耗6，I/O调整这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O负载均衡。在磁盘比较富裕的情况下还应该遵循以下原则： 将表和索引分开； 创造用户表空间，与系统表空间（system）

6、分开磁盘； 创建表和索引时指定不同的表空间； 创建回滚段专用的表空间，防止空间竞争影响事务的完成； 创建临时表空间用于排序操作，尽可能的防止数据库碎片存在于多个表空间中。7，确定数据块大小和存储参数。由于数据库的块大小在数据库创建以后就不能在修改（除非重建数据库），因此为了减少数据链接和行迁移，又提高磁盘空间的利用率，在设计数据库时要确定合适的数据块大小和存储参数。通常我们是根据样例数据确定数据块大小，而根据业务现状和未来发展趋势确定存储参数。运行阶段：B，系统优化许多新手都错误的认为应该首先调整Oracle数据库，而不是先确认外部资源是否足够。实际上，如果外部环境出现瓶颈，再多的Oracle

7、调整都是没有帮助的。1，多处理器系统调整优化，启用并行查询。2，减少内存交换，减少分页，使SGA(System Globle Area)可留驻内存。3，优化系统I/O环境。4，优化网络环境C，数据库连接与网络优化1，使用直接OLE DB连接。2，使用Connection Pool（数据连接池）机制3，优化程序设计，减少网络传输量D，Oracle参数配置优化Oracle参数设置，根据具体应用情况而定。1， SGA的分配及使用效率优化分配给每个实例的内存，即SGA的使用效率如何，会大大影响数据库系统的性能。SGA由下列部分组成：共享池（Shared pool）、数据块缓冲区（Db block bu

8、ffer）、重做日志缓冲区（Log buffer）、大池组成（Large pool）。2， 使用SQL*Loader Direct Path选项进行大量数据装载3， Database Resource Manager（DRM,数据库资源管理器）来控制用户的资源分配4， 减少资源竞争在Oracle中，需要采用一些机制来保证数据库对象在使用期间的稳定性和数据的一致性，如使用锁存器(latch)、锁(lock)等。因此争用和这些机制相关的资源会影响数据库的性能。为了减少这种资源竞争，可以通过调整数据库的相关初始化参数（如db_block_lru_latches、dml_locks）来减少资源的争用，

9、优化数据库性能。在对Oracle数据库进行优化时，需要用到许多的参数，其中有一部分参数对系统性能影响较大，这部分参数叫可变参数。可变参数按其作用可以分为两大类，一大类是起限制作用的，如OPEN_CURSORS;另一大类是影响系统性能的，如DB_BLOCK_BUFFERS。5，可变参数优化在对Oracle数据库进行优化时，需要用到许多的参数，其中有一部分参数对系统性能影响较大，这部分参数叫可变参数。可变参数按其作用可以分为两大类，一大类是起限制作用的，如OPEN_CURSORS;另一大类是影响系统性能的，如DB_BLOCK_BUFFERS。在进行数据库系统性能优化时，需要熟练掌握和了解一些可变参

10、数。本文讨论了一些对系统性能有较大影响的参数。限制类可变参数(1)DML_LOCKS该参数表明多少个用户，可同时能修改多少张表。例如:有三个用户同时修改二张表，则要求表上的总数为6。若置为0，则组织队列不起作用，其性能会稍有提高。使用该参数时不能用DROP TABLE、CREATE INDEX或显式封锁。(2)LICENSE_MAX_SESSION该参数指出允许并发用户会话的最大数。若此参数为0，则不能实施并发。若并发的用户会话数已达到此极限，则只有具有RESTRICTEDSESSION权限的用户才能连接到服务器。(3)LICENSE_MAX_USERS该参数指出在一个数据库上可建立的最大用户

11、数。当达到最大值时，便不能再建新用户，可改变此值以放松限制。在LICENSE_MAX_SESSION或LICENSE_MAX_USER为0时，则并发会话或任何用户都不能用。若对不同的实例，此参数不同时，则以第一个登录的数据库实例的参数为准。(4)MAX_DUMP_FILE_SIZE该参数指定操作系统中写跟踪文件的块的最大值。可用此值来限制跟踪文件的空间。(5)OPEN_CURSORS该参数指明一个用户进程能同时打开光标的最大数，它能限制每个用户进程占用的内存空间量。(6)OPEN_LINKS该参数指定并发连接到远程数据库的最大用户进程数。若同时引用多个数据库，则应该增大该值。例如:同时交替访问

12、A、B和C三个数据库时，若OPEN_LINKS设置为2，则需花费等待连接时间。此参数只用于分布事务。若该参数设置为0，则不允许进行分布事务处理。(7)PROCESS该参数指定同时连接到Oracle服务器上的最大用户进程数。该参数值包括6个后台进程和一个登录，因此，该参数值为20，则只能有13或14个并发用户连接到服务器。(8)ROW_LOCKING该参数指定行封锁方式。若设置为“ALWAYS”，则在修改表时只实施行封锁。若设置为“INTENT”时，则行封锁只适用于SELECT FOR UPDATE，而在修改时实施表封锁。影响系统性能类可变参数(1)CHECKPOINT_PROCESS该参数根据

13、是否要求检查点而设置成TRUE或者FALSE。当所有缓冲区的信息写到磁盘时，检查点进程(CHPT)建立一个静态的点。在归档日志文件中做一个记号表示有一个检查点发生。检查点发生在归档日志转换的时候或当达到log_checkpoint_interval定义的块数的时候。当设置此参数为TRUE时，后台进程CHPT便可工作。在检查点期间内，若日志写进程(LGWR)的性能减低，则可用CHPT进程加以改善。(2)DB_BLOCK_CHECKPOINT_BATCH该参数的值设置得较大时，可加速检查点的完成。当指定的值比参数DB_BLOCK_CHECKPOINT_BATCH大时，其效果和指定最大值相同。(3)

14、DB_BLOCK_BUFFERS该参数是在SGA中可作缓冲用的数据库块数。该参数决定SGA的大小，对数据库性能具有决定性因素。若取较大的值，则可减少I/O次数，但要求内存空间较大。每个缓冲区的大小由参数DB_BLOCK_SIZE决定。(4)DB_BLOCK_SIZE该参数表示Oracle数据库块的大小，以字节为单位，典型值为2048或4096。该值一旦设定则不能改变。它影响表及索引的FREELISTS参数的最大值。(5)DB_FILES该参数为数据库运行时可打开的数据文件最大数目。(6)DB_FILE_MULTIBLOCK_READ_COUNT该参数表示在顺序扫描时一次I/O操作可读的最大块数

15、，该最大块数取决于操作系统，其值在4至16或者32是比较好。(7)D1SCRETE_TRANSACTION_ENABLED该参数实现一个更简单、更快的回滚机制，以改进某些事务类型的性能。 当设置为TRUE时，可改善某些类型的事务性能。(8)LOG_ARCHIVE_BUFFER_SIZE此参数的值依赖于操作系统，它与LOG_ARCHIVE_BUFFER 参数一起用于调整有归档日志的运行，使其运行速度尽量加快，但不能快到降低性能。仅当直接归档到磁带设备时才需要增加这些参数的值，重做日志缓冲区要等待归档日志缓冲区变得可用。(9) LOG_ARCHIVE_BUFFER该参数指定用于归档的日志时的缓冲区

16、数。(10) LOG_BUFFER该参数指明分配给SGA中的日志缓冲区的字节数，该参数值较大时，可减少日志I/O的次数。对于繁忙的系统不宜采用大于或等于64K的值。缺省值般为数据库块的4倍。(11)LOG_CHECKPOINT_TIMEOUT该参数指明两个检查点之间的时间间隔，若指定为0时，则说明不允许进行基于时间的检查点。(12)LOG_CHECKPOINT_INTERVAL该参数用来确定检查点进程的执行频率。这个数值设置成取检查点之前处理的重做缓冲区块的数量。(13)LOG_FILES该参数指定运行期间数据库可打开的日志文件数。若需要较大的SGA空间，而不需多个日志文件，则可减少该值。(1

17、4)LOG_SIMULTANEOUS_COPIES该参数是日志缓冲区副本闩锁的最大数，为同时写日志项所用。为提高性能，可设置此参数为两倍的CPU数，对单进程系统，该值多数设置为0，此时断开闩锁。(15)LOG_SMALL_ENTRY_MAX_SIZE该参数与LOG_SIMULTANEOUS_COPIES参数配合使用。若日志项大于此项，则在给缓冲区分配空间并获得日志复制闩锁之后，用户进程释放日志复制闩锁。(16)OPTIMIZRER_MODE若该参数的值为RULE，则ORACLE优化器选择基于规则的优化;若设置为COST，并且在数据字典中存在有统计信息，则优化器选择基于代价的优化方法进行优化。(

18、17)SEQUENCE_CACHE_ENTRIES该参数指明在SGA中可进行高速缓存的序列数，用于直接存取。该高速缓存区是基于最近最少使用(LRU)的算法进行管理的。若此值设置得较高，则可达到较高的并发性。(18)SEQUENCE_CACHE_HASH_BUCKETS该参数用于加速查看高速缓冲区最近请求的最新序列的桶式地址数，每个桶式地址占8个字节。高速缓冲区以散列表排列，该参数应为质数。(19)SERIALIZEABLE此参数用于保证重复读的一致性。当它设置为TRUE时，查询可保证表级读一致，以防止在此查询提交之前的修改。(20)SHARED_POOL_SIZE该参数指定共享池的大小，其中包

19、括共享光标及存储过程。在多用户系统中，较大的SHARED_POOL_SIZE值可改善SQL语句的执行性能，但较小的值可节省内存。(21)SMALL_TABLE_THRESHOLD该参数决定SGA中用于扫描的缓冲区的数目，若表的数目小于该值，则该表可整个地读入高速缓存区。若表大于该值，则立即重用该缓冲区。一般用缺省值可使性能最好。(22)SORT_AREA_TETAINED_SIZE这是会话内存的最大数量,用于内存排序。当从排序空间提出最后行时，便释放该内存。若排序要较大的内存，则分配一临时段，排序便可在盘上进行。用于排序的最大总量可由SORT_AREA_SIZE指定，而不用此参数。可以分配同样

20、大小的多个排序空间，不过一般对于复杂的查询才需要。(23) SORT_AREA_SIZE该参数用于指定进行外排序(磁盘)时所需PGA内存的最大数量，以字节为单位。当排序行写入磁盘时，该内存被释放。增大该参数的值，可改进排序效率。一般不调整该参数，除非排序量很大时才调整。(24) SORT_SPACEMP_SIZE该参数仅在排序量很大时才调整该参数。可用下式设置该参数，使排序能最佳地使用盘空间:(total_sort_bytes)/(SORT_AREA_SIZE)十64其中，total_sort_bytes为:(number_of_records)*sum_of_aver_average_col

21、umn_sizes+(12*number of(al)(25)SQLTRACE该参数设置为TRUE时，便可跟踪，以获得改善性能的信息。因为跟踪会增加开销，所以一般仅在收集信息时才置为TRUE。在实际使用时，可用ALTER SESSION命令覆盖它。(26)TRANSACTION该参数设置并发事务的最大数。若此值较大，则需增加SGA空间和分配的回滚段数量。缺省值大于PROCESS时，可允许递归事务。E，SQL语句优化不良的SQL往往来自于不恰当的索引设计、不充分的连接条件和未优化的where子句。SQL优化的实质就是在结果正确的前提下，用优化器可以识别的语句，充份利用索引，减少表扫描的I/O次数

22、，尽量避免表搜索的发生。1， 选用合适的Oracle优化器2， 访问Table的方式：全表扫描，顺率访问全部表的数据，依次读取多个物理数据块。ROWID扫描，基于记录的物理位置信息进行扫描和检索，索引提供了快速访问ROWID的方法。3， 共享SQL语句不重复解析相同的SQL语句。当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径 共享的语句必须满足三个条件:A. 字符级的比较当前被执行的语句和共享池中的语句必须完全相同. 例如: SELECT * FROM TEST; 和下列每一个都不同，字母大小写和

23、空格 SELECT * from TEST; Select * From TEST; SELECT * FROM TEST;B. 两个语句所指的对象必须完全相同:例如: 用户 对象名 如何访问Jack sal_limit private synonym Work_city public synonym Plant_detail public synonymJill sal_limit private synonym Work_city public synonym Plant_detail table owner 考虑一下下列SQL语句能否在这两个用户之间共享.SQL能否共享原因select

24、max(sal_cap) from sal_limit;不能每个用户都有一个private synonym - sal_limit , 它们是不同的对象select count(*0 from work_city where sdesc like NEW%;能两个用户访问相同的对象public synonym - work_city select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id不能用户jack 通过private synonym访问plant_detail 而jil

25、l 是表的所有者,对象不同.C. 两个SQL语句中必须使用相同的名字的绑定变量(bind variables)例如：第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)a.select pin , name from people where pin = :blk1.pin;select pin , name from people where pin = :blk1.pin;b.select pin , name from people where pin = :blk1.ot_ind;select pin , name fro

26、m people where pin = :blk1.ov_ind;4， 选择最佳查询顺序（表名顺序）tab1表记录多，tab2表记录少Select count(*) from tab1,tab2；tab2表作为基础表多表连接查询，以其中涉及多张表的关系表为基础表5， Where子句的条件连接顺序（从最后一个条件子句开始解析）SELECT *FROM test t1WHERE 25 50000AND JOB = MANAGER;大量减少查询范围的条件放在最后6， 避免select * 全查询，利用条件缩小查询范围select address from address where upper(n

27、ame) like JOHN AND (name like JO% or name like jo% or name like Jo or name like jO );注意：upper（）函数对索引字段数据的修改，引起了非索引的全检索。加入对索引列的额外条件则可强制启用索引，提高查询效率。7， 减少访问数据库的次数能1次select得出结果的，尽量避免用2次select8， 使用Decode函数来减少处理时间使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.例如: SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0020 AND

28、 ENAME LIKESMITH%; SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0030 AND ENAME LIKESMITH%;你可以用DECODE函数高效地得到相同结果SELECT COUNT(DECODE(DEPT_NO,0020,X,NULL) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,X,NULL) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL) D0030

29、_SALFROM EMP WHERE ENAME LIKE SMITH%;类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.9， 整合简单的，无关联的数据库访问视程序代码要求，可以把简单的查询，可以把无关联的几个表在一次select查询出来，储存起来待用。10， 删除重复记录DELETE FROM test tWHERE t.ROWID (SELECT MIN(X.ROWID) FROM test X WHERE X.id = t.id);因为使用了ROWID，是最高效删除。11， 用Truncate替代delete不可回滚，提高了执行速度。12， 尽量多用Com

30、mitCOMMIT所释放的资源:a. 回滚段上用于恢复数据的信息.b. 被程序语句获得的锁c. redo log buffer 中的空间d. ORACLE为管理上述3种资源中的内部花费具体何时COMMIT，需要考虑事务完整性。13， 计算记录条数count(XX)对索引列执行count效率最高14， 用where代替havingHAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.15， 减少对表的查询在含有子查询的SQL语句中,要特别注意减少对表的查询. 例如: 低效 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) ANDDB_VER= ( SE