Oracle性能优化学习心得.docx
《Oracle性能优化学习心得.docx》由会员分享,可在线阅读,更多相关《Oracle性能优化学习心得.docx(20页珍藏版)》请在冰豆网上搜索。
Oracle性能优化学习心得
Oracle性能优化学习心得
一,优化总的原那么
1,查看系统的利用情形
2,查看SGA分派情形,结合系统具体情形进行分析。
3,表的设计分析
4,SQL语句分析
实施要那么
1,查看系统的利用情形,CPU占用,内存,I/O读取等
Oracle10G提供的OracleEnterpriseManager图形化工具中的ADDM和SQLTuningAdvisor等能够方便的查看系统状况
2,OPS上负载均衡,不同查询用不同Instance
3,提供脚本查看SGA利用情形
4,分析SQL执行情形(trace及其他工具)
实施细节
1,外部调整:
咱们应该记住Oracle并非是单独运行的。
因此咱们将查看一下通过调整Oracle效劳器以取得高的性能。
2,Rowre-sequencing以减少磁盘I/O:
咱们应该知道Oracle调优最重要的目标是减少I/O。
3,OracleSQL调整。
OracleSQL调整是Oracle调整中最重要的领域之一,只要通过一些简单的SQL调优规那么就能够够大幅度地提升SQL语句的性能,这是一点都不奇怪的。
4,调整Oracle排序:
排序关于Oracle性能也是有专门大阻碍的。
5,调整Oracle的竞争:
表和索引的参数设置关于UPDATE和INSERT的性能有专门大的阻碍。
二,调优分类:
对Oracle数据库进行性能调整时,应当依照必然的顺序进行,因为系统在前面步骤中进行的调整能够幸免后面的一些没必要要调整或代价专门大的调整。
一样来讲能够从两个时期入手:
一、设计时期:
对其逻辑结构和物理结构进行优化设计,使之在知足需求条件的情形下,系统性能达到最正确,系统开销达到最小;
二、数据库运行时期:
采取操作系统级、数据库级的一些优化方法来使系统性能最正确;
㈠设计时期:
A,数据库设计优化
较多修改较少查询的数据和较多查询较少修改的数据别离对待。
a,结构优化
1,依照应用程序进行数据库设计。
即应用程序采纳的是传统的C/S两层体系结构,仍是B/W/D三层体系结构。
不同的应用程序体系结构要求的数据库资源是不同的。
2,遵循3大范式标准化数据结构,减少没必要要的冗余。
3,反标准设计,增加必要冗余,提高查询速度。
4,针对转变较少的数据,合理创建临时表和视图,需注意对临时表和视图的及时同步更新
5,在效劳器端利用进程和函数,减少网络数据传输
b,I/O优化
1,利用分区,对查询条件字段(主外键)成立索引,依据Rowid物理地址查询,减少I/O的消耗。
应幸免可能参与计算的字段
2,成立适合的索引。
缺省情形下成立的索引是非群集索引,但有时它并非是最正确的;合理的索引设计要成立在对各类查询的分析和预测上。
一样来讲:
1.有大量重复值、且常常有范围查询(between,>,<,>=,<=)和orderby、groupby发生的列,可考虑成立群集索引;
2.②.常常同时存取多列,且每列都含有重复值可考虑成立组合索引;
3.③.组合索引要尽可能使关键查询形成索引覆盖,其前导列必然是利用最频繁的列。
a,按期的重构索引是有必要的.
ALTERINDEXREBUILD
b,幸免在索引列上利用计算,判定或函数处置
3,利用聚簇(对列的选择原那么同索引)。
4,利用存档功能。
5,数据缓冲,快照,视图,远程挪用等,减少I/O和网络消耗
6,I/O调整
这一步是在信息系统开发之前完成的。
数据库治理员能够将组成同一个表空间的数据文件放在不同的硬盘上,做到硬盘之间I/O负载均衡。
在磁盘比较富裕的情形下还应该遵循以下原那么:
将表和索引分开;
制造用户表空间,与系统表空间(system)分开磁盘;
创建表和索引时指定不同的表空间;
创建回滚段专用的表空间,避免空间竞争阻碍事务的完成;
创建临时表空间用于排序操作,尽可能的避免数据库碎片存在于多个表空间中。
7,确信数据块大小和存储参数。
由于数据库的块大小在数据库创建以后就不能在修改(除非重建数据库),因此为了减少数据链接和行迁移,又提高磁盘空间的利用率,在设计数据库时要确信适合的数据块大小和存储参数。
通常咱们是依照样例数据确信数据块大小,而依照业务现状和以后进展趋势确信存储参数。
㈡运行时期:
B,系统优化
许多新手都错误的以为应该第一调整Oracle数据库,而不是先确认外部资源是不是足够。
事实上,若是外部环境显现瓶颈,再多的Oracle调整都是没有帮忙的。
1,多处置器系统调整优化,启用并行查询。
2,减少内存互换,减少分页,使SGA(SystemGlobleArea)可留驻内存。
3,优化系统I/O环境。
4,优化网络环境
C,数据库连接与网络优化
1,利用直接OLEDB连接。
2,利用ConnectionPool(数据连接池)机制
3,优化程序设计,减少网络传输量
D,Oracle参数配置优化
Oracle参数设置,依照具体应用情形而定。
1,SGA的分派及利用效率优化
分派给每一个实例的内存,即SGA的利用效率如何,会大大阻碍数据库系统的性能。
SGA由以下部份组成:
共享池(Sharedpool)、数据块缓冲区(Dbblockbuffer)、重做日记缓冲区(Logbuffer)、大池组成(Largepool)。
2,利用SQL*LoaderDirectPath选项进行大量数据装载
3,DatabaseResourceManager(DRM,数据库资源治理器)来操纵用户的资源分派
4,减少资源竞争
在Oracle中,需要采纳一些机制来保证数据库对象在利用期间的稳固性和数据的一致性,如利用锁存器(latch)、锁(lock)等。
因此争用和这些机制相关的资源会阻碍数据库的性能。
为了减少这种资源竞争,能够通过调整数据库的相关初始化参数(如db_block_lru_latches、dml_locks)来减少资源的争用,优化数据库性能。
在对Oracle数据库进行优化时,需要用到许多的参数,其中有一部份参数对系统性能阻碍较大,这部份参数叫可变参数。
可变参数按其作用能够分为两大类,一大类是起限制作用的,如OPEN_CURSORS;另一大类是阻碍系统性能的,如DB_BLOCK_BUFFERS。
5,可变参数优化
在对Oracle数据库进行优化时,需要用到许多的参数,其中有一部份参数对系统性能阻碍较大,这部份参数叫可变参数。
可变参数按其作用能够分为两大类,一大类是起限制作用的,如OPEN_CURSORS;另一大类是阻碍系统性能的,如DB_BLOCK_BUFFERS。
在进行数据库系统性能优化时,需要熟练把握和了解一些可变参数。
本文讨论了一些对系统性能有较大阻碍的参数。
限制类可变参数
(1)DML_LOCKS
该参数说明多少个用户,可同时能修改多少张表。
例如:
有三个用户同时修改二张表,那么要求表上的总数为6。
假设置为0,那么组织队列不起作用,其性能会稍有提高。
利用该参数时不能用DROPTABLE、CREATEINDEX或显式封锁。
(2)LICENSE_MAX_SESSION
该参数指出许诺并发用户会话的最大数。
假设此参数为0,那么不能实施并发。
假设并发的用户会话数已达到此极限,那么只有具有RESTRICTED SESSION权限的用户才能连接到效劳器。
(3)LICENSE_MAX_USERS
该参数指出在一个数据库上可成立的最大用户数。
当达到最大值时,便不能再建新用户,可改变此值以放松限制。
在LICENSE_MAX_SESSION或LICENSE_MAX_USER为0时,那么并发会话或任何用户都不能用。
假设对不同的实例,此参数不同时,那么以第一个登录的数据库实例的参数为准。
(4)MAX_DUMP_FILE_SIZE
该参数指定操作系统中写跟踪文件的块的最大值。
可用此值来限制跟踪文件的空间。
(5)OPEN_CURSORS
该参数指明一个用户进程能同时打开光标的最大数,它能限制每一个用户进程占用的内存空间量。
(6)OPEN_LINKS
该参数指定并发连接到远程数据库的最大用户进程数。
假设同时引用多个数据库,那么应该增大该值。
例如:
同时交替访问A、B和C三个数据库时,假设OPEN_LINKS设置为2,那么需花费等待连接时刻。
此参数只用于散布事务。
假设该参数设置为0,那么不许诺进行散布事务处置。
(7)PROCESS
该参数指定同时连接到Oracle效劳器上的最大用户进程数。
该参数值包括6个后台进程和一个登录,因此,该参数值为20,那么只能有13或14个并发用户连接到效劳器。
(8)ROW_LOCKING
该参数指定行封锁方式。
假设设置为“ALWAYS”,那么在修改表时只实实施封锁。
假设设置为“INTENT”时,那么行封锁只适用于SELECTFORUPDATE,而在修改时实施表封锁。
阻碍系统性能类可变参数
(1)CHECKPOINT_PROCESS
该参数依照是不是要求检查点而设置成TRUE或FALSE。
当所有缓冲区的信息写到磁盘时,检查点进程(CHPT)成立一个静态的点。
在归档日记文件中做一个记号表示有一个检查点发生。
检查点发生在归档日记转换的时候或当达到log_checkpoint_interval概念的块数的时候。
当设置此参数为TRUE时,后台进程CHPT即可工作。
在检查点期间内,假设日记写进程(LGWR)的性能减低,那么可用CHPT进程加以改善。
(2)DB_BLOCK_CHECKPOINT_BATCH
该参数的值设置得较大时,可加速检查点的完成。
当指定的值比参数DB_BLOCK_CHECKPOINT_BATCH大时,其成效和指定最大值相同。
(3)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操作可读的最大块数,该最大块数取决于操作系统,其值在4至16或32是比较好。
(7)D1SCRETE_TRANSACTION_ENABLED
该参数实现一个更简单、更快的回滚机制,以改良某些事务类型的性能。
当设置为TRUE时,可改善某些类型的事务性能。
(8)LOG_ARCHIVE_BUFFER_SIZE
此参数的值依托于操作系统,它与LOG_ARCHIVE_BUFFER参数一路用于调整有归档日记的运行,使其运行速度尽可能加速,但不能快到降低性能。
仅当直接归档到磁带设备时才需要增加这些参数的值,重做日记缓冲区要等待归档日记缓冲区变得可用。
(9)LOG_ARCHIVE_BUFFER
该参数指定用于归档的日记时的缓冲区数。
(10)LOG_BUFFER
该参数指明分派给SGA中的日记缓冲区的字节数,该参数值较大时,可减少日记I/O的次数。
关于忙碌的系统不宜采纳大于或等于64K的值。
缺省值—般为数据库块的4倍。
(11)LOG_CHECKPOINT_TIMEOUT
该参数指明两个检查点之间的时刻距离,假设指定为0时,那么说明不许诺进行基于时刻的检查点。
(12)LOG_CHECKPOINT_INTERVAL
该参数用来确信检查点进程的执行频率。
那个数值设置成取检查点之前处置的重做缓冲区块的数量。
(13)LOG_FILES
该参数指定运行期间数据库可打开的日记文件数。
假设需要较大的SGA空间,而不需多个日记文件,那么可减少该值。
(14)LOG_SIMULTANEOUS_COPIES
该参数是日记缓冲区副本闩锁的最大数,为同时写日记项所用。
为提高性能,可设置此参数为两倍的CPU数,对单进程系统,该值多数设置为0,现在断开闩锁。
(15)LOG_SMALL_ENTRY_MAX_SIZE
该参数与LOG_SIMULTANEOUS_COPIES参数配合利用。
假设日记项大于此项,那么在给缓冲区分派空间并取得日记复制闩锁以后,用户进程释放日记复制闩锁。
(16)OPTIMIZRER_MODE
假设该参数的值为RULE,那么ORACLE优化器选择基于规那么的优化;假设设置为COST,而且在数据字典中存在有统计信息,那么优化器选择基于代价的优化方式进行优化。
(17)SEQUENCE_CACHE_ENTRIES
该参数指明在SGA中可进行高速缓存的序列数,用于直接存取。
该高速缓存区是基于最近最少利用(LRU)的算法进行治理的。
假设此值设置得较高,那么可达到较高的并发性。
(18)SEQUENCE_CACHE_HASH_BUCKETS
该参数用于加速查看高速缓冲区最近请求的最新序列的桶式地址数,每一个桶式地址占8个字节。
高速缓冲区以散列表排列,该参数应为质数。
(19)SERIALIZEABLE
此参数用于保证重复读的一致性。
当它设置为TRUE时,查询可保证表级读一致,以避免在此查询提交之前的修改。
(20)SHARED_POOL_SIZE
该参数指定共享池的大小,其中包括共享光标及存储进程。
在多用户系统中,较大的SHARED_POOL_SIZE值可改善SQL语句的执行性能,但较小的值可节省内存。
(21)SMALL_TABLE_THRESHOLD
该参数决定SGA顶用于扫描的缓冲区的数量,假设表的数量小于该值,那么该表可整个地读入高速缓存区。
假设表大于该值,那么当即重用该缓冲区。
一样用缺省值可使性能最好。
(22)SORT_AREA_TETAINED_SIZE
这是会话内存的最大数量,用于内存排序。
当从排序空间提出最后—行时,便释放该内存。
假设排序要较大的内存,那么分派一临时段,排序即可在盘上进行。
用于排序的最大总量可由SORT_AREA_SIZE指定,而不用此参数。
能够分派一样大小的多个排序空间,只是一样关于复杂的查询才需要。
(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_column_sizes+(12*numberof(al)]
(25)SQLTRACE
该参数设置为TRUE时,即可跟踪,以取得改善性能的信息。
因为跟踪会增加开销,因此一样仅在搜集信息时才置为TRUE。
在实际利历时,可用ALTERSESSION命令覆盖它。
(26)TRANSACTION
该参数设置并发事务的最大数。
假设此值较大,那么需增加SGA空间和分派的回滚段数量。
缺省值大于PROCESS时,可许诺递归事务。
E,SQL语句优化
不良的SQL往往来自于不适当的索引设计、不充分的连接条件和未优化的where子句。
SQL优化的实质确实是在结果正确的前提下,用优化器能够识别的语句,充份利用索引,减少表扫描的I/O次数,尽可能幸免表搜索的发生。
1,选用适合的Oracle优化器
2,访问Table的方式:
全表扫描,顺率访问全数表的数据,依次读取多个物理数据块。
ROWID扫描,基于记录的物理位置信息进行扫描和检索,索引提供了快速访问ROWID的方式。
3,共享SQL语句
不重复解析相同的SQL语句。
当你执行一个SQL语句(有时被称为一个游标)时,若是它
和之前的执行过的语句完全相同,ORACLE就能够专门快取得已经被解析的语句和最好的执行途径
共享的语句必需知足三个条件:
A.字符级的比较
当前被执行的语句和共享池中的语句必需完全相同.
例如:
SELECT*FROMTEST;
和以下每一个都不同,字母大小写和空格
SELECT*fromTEST;
Select*FromTEST;
SELECT*FROMTEST;
B. 两个语句所指的对象必需完全相同:
例如:
用户对象名如何访问
Jacksal_limitprivatesynonym
Work_citypublicsynonym
Plant_detailpublicsynonym
Jillsal_limitprivatesynonym
Work_citypublicsynonym
Plant_detailtableowner
考虑一下以下SQL语句可否在这两个用户之间共享.
SQL
能否共享
原因
selectmax(sal_cap)fromsal_limit;
不能
每个用户都有一个privatesynonym-sal_limit,它们是不同的对象
selectcount(*0fromwork_citywheresdesclike'NEW%';
能
两个用户访问相同的对象publicsynonym-work_city
不能
用户jack通过privatesynonym访问plant_detail而jill是表的所有者,对象不同.
C. 两个SQL语句中必需利用相同的名字的绑定变量(bindvariables)
例如:
第一组的两个SQL语句是相同的(能够共享),而第二组中的两个语句是不同的(即便在运行时,赋于不同的绑定变量相同的值)
a.
selectpin,namefrompeoplewherepin=:
blk1.pin;
selectpin,namefrompeoplewherepin=:
blk1.pin;
b.
selectpin,namefrompeoplewherepin=;
selectpin,namefrompeoplewherepin=;
4,选择最正确查询顺序(表名顺序)
tab1表记录多,tab2表记录少
Selectcount(*)fromtab1,tab2;tab2表作为基础表
多表连接查询,以其中涉及多张表的关系表为基础表
5,Where子句的条件连接顺序(从最后一个条件子句开始解析)
SELECT*
FROMtestt1
WHERE25<(SELECTCOUNT(*)FROMtest2t2
WHEREt2.name=)
ANDSAL>50000
ANDJOB=‘MANAGER’;
大量减少查询范围的条件放在最后
6,幸免select*全查询,利用条件缩小查询范围
selectaddressfromaddresswhere
upper(name)like'JOHN'AND(namelike'JO%'
ornamelike'jo%'ornamelike'Jo'ornamelike'jO');
注意:
upper()函数对索引字段数据的修改,引发了非索引的全检索。
加入对索引列的额外条件那么可强制启用索引,提高查询效率。
7,减少访问数据库的次数
能1次select得出结果的,尽可能幸免用2次select
8,利用Decode函数来减少处置时刻
利用DECODE函数能够幸免重复扫描相同记录或重复连接相同的表.
例如:
SELECTCOUNT(*),SUM(SAL)
FROM EMP
WHEREDEPT_NO=0020
ANDENAMELIKE ‘SMITH%’;
SELECTCOUNT(*),SUM(SAL)
FROM EMP
WHEREDEPT_NO=0030
ANDENAMELIKE ‘SMITH%’;
你能够用DECODE函数高效地取得相同结果
SELECTCOUNT(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_SAL
FROMEMPWHEREENAMELIKE‘SMITH%’;
类似的,DECODE函数也能够运用于GROUPBY和ORDERBY子句中.
9,整合简单的,无关联的数据库访问
视程序代码要求,能够把简单的查询,能够把无关联的几个表在一次select查询出来,贮存起来待用。
10,删除重复记录
DELETEFROMtestt
WHEREt.ROWID>(SELECTMIN(X.ROWID)
FROMtestX
WHEREX.id=);
因为利用了ROWID,是最高效删除。
11,用Truncate替代delete
不可回滚,提高了执行速度。
12,尽可能多用Commit
COMMIT所释放的资源:
a. 回滚段上用于恢复数据的信息.
b. 被程序语句取得的锁
c. redologbuffer中的空间
d. ORACLE为治理上述3种资源中的内部花费
具体何时COMMIT,需要考虑事务完整性。
13,计算记录条数count(XX)
对索引列执行count效率最高
14,用where代替having
HAVING只会在检索出所有记录以后才对结果集进行过滤.那个处置需要排序,共计等操作.若是能通过WHERE子句限制记录的数量,那就能够减少这方面的开销.
15,减少对表的查询
在含有子查询的SQL语句中,要专门注意减少对表的查询.
例如:
低效
SELECTTAB_NAME
FROMTABLES
WHERETAB_NAME=(SELECTTAB_NAME
FROMTAB_COLUMNS
WHEREVERSION=604)
AND DB_VER=(SELECTDB_VER
FROMTAB_COLUMNS
WHEREVERSION=604)
高效
SELECTTAB_NAME
F