1、SUPPORT SERVICESOracle数据库性能 调整和优化Oracle技术专题讲座SUPPORT SERVICES内容提要n1.oracle 性能调整概述 n2.磁盘I/O的调整n3.oracle 内存分配与调整n4.SQL优化概述n5.Statspack概述SUPPORT SERVICES1.oracle 性能调整概述n磁盘磁盘I/On内存争用内存争用nCPU占用时间占用时间n数据库资源争用数据库资源争用影响数据库性能的几大问题影响数据库性能的几大问题SUPPORT SERVICES1.oracle 性能调整概述n建立合理的数据库建立合理的数据库n解决内存问题解决内存问题n解决磁盘解
2、决磁盘I/O争用问题争用问题n解决其他相关问题解决其他相关问题,如回滚段等如回滚段等优化的目的优化的目的SUPPORT SERVICES2.磁盘I/O的调整n.1.1 使用磁盘阵列使用磁盘阵列n.2.2 在可用硬盘之间分布关键数据文件在可用硬盘之间分布关键数据文件n.3.3 使用本地管理表空间使用本地管理表空间(LMT)(LMT)自动段空间管理自动段空间管理 （ASSM)ASSM)n.4.4 在大型表上使用表分区在大型表上使用表分区n.5.5 管理回滚段管理回滚段n.6.6 通过内存排序来减少磁盘通过内存排序来减少磁盘I/OI/On.7.7 其他注意事项其他注意事项SUPPORT SERVIC
3、ES2.1 使用磁盘阵列使用磁盘阵列nRAID0:允许自动磁盘分段允许自动磁盘分段(STRIPING).表空间所对应的数据文件表空间所对应的数据文件可扩展到多个磁盘可扩展到多个磁盘,并可同时对其进行访问并可同时对其进行访问(节省大量节省大量I/O).nRAID1:主要想得到更高的可用性主要想得到更高的可用性.nRAID5:有大量有大量 读操作的系统可获得最大性能读操作的系统可获得最大性能.有大量写操作的系统有大量写操作的系统,其效率并不高其效率并不高.nRAID1+0:支持镜像的磁盘和分段支持镜像的磁盘和分段.2.1.1 可用的可用的RAID级别级别建议建议:在一个系统大部分进行读取操作使用在
4、一个系统大部分进行读取操作使用RAID5.优先使用优先使用RAID1+0SUPPORT SERVICES2.2在可用硬盘之间分布关键数据文件在可用硬盘之间分布关键数据文件需要注意的的文件需要注意的的文件:nSYSTEM 表空间表空间nTEMPORARY表空间表空间n回滚段和回滚段和UNDO表空间表空间n联机重做日志文件（最好放在最快的磁盘上）联机重做日志文件（最好放在最快的磁盘上）nORACLE_HOME下的文件下的文件n经常被访问表的文件经常被访问表的文件n经常被访问的索引的数据文件经常被访问的索引的数据文件n操作系统操作系统SUPPORT SERVICES2.2在可用硬盘之间分布关键数据文
5、件在可用硬盘之间分布关键数据文件/:Operating System/u01:Oracle software/u02:Temporary Tablespace,Control File1/u03:Undo Segments,Control File2/u04:Redo Logs,Archive Logs,Control File4/u05:System Tablespace/u06:Data1,Control File3/uo7:Redo Log Mirror,Index3/u08:Data2/u09:Index2/u10:Data3/u11:Index1SUPPORT SERVICES2.2
6、.1 分开存储数据和索引文件分开存储数据和索引文件nSelect col1,col2.from CUST_HEADER,CUST_DETAIL where .;Disk1:CUST_HEADER Table Disk5:CUST_HEADER Index Disk8:CUST_DETAIL Table Disk12:CUST_DETAIL IndexSUPPORT SERVICES2.3 避免避免I/O 磁盘争用磁盘争用n磁盘争用通常发生在有多个进程试图同时访问同一个物磁盘争用通常发生在有多个进程试图同时访问同一个物理磁盘的情况下，因此把磁盘的理磁盘的情况下，因此把磁盘的I/O均匀的分布在多个
7、均匀的分布在多个可用的磁盘上，可有效减少磁盘的竞用可用的磁盘上，可有效减少磁盘的竞用n通过查询通过查询V$filestat和和v$dbfile,以查看均衡数据文件后的以查看均衡数据文件后的效果效果SUPPORT SERVICES2.3 避免避免I/O 磁盘争用磁盘争用Select name,phyrds,phywrts,readtim,writetimFrom v$filestat a,v$dbfile bWhere a.file#=b.file#NAME PHYRDS PHYWRTS READTIM WRITEIM/d01/psindex_1.dbf 48,310 51,798 200,56
8、4 903,199/d02/psindex_2.dbf 34,520 40,224 117,925 611,121/d03/psdata_01.dbf 35,189 36,904 97,474 401,290/d04/rbs01.dbf 1,320 11,725 1,214 39,892/d05/system01.dbf 1,454 10 10 9561-3磁盘经常被使用，使用较少，因此须移动数据文件或使用分区以均衡I/O.SUPPORT SERVICES2.3通过移动数据文件来均衡通过移动数据文件来均衡I/O(1)使与数据文件有关的表空间脱机使与数据文件有关的表空间脱机ALTER TABLE
9、SPACE ORDERS OFFLINE;(2)把数据文件复制到磁盘的新位置上把数据文件复制到磁盘的新位置上$cp/disk1/orders1.dbf /disk2/orders1.dbf(3)重命名数据文件重命名数据文件ALTER TABLESPACE ORDERS RENAME DATAFILE/disk1/orders1.dbf TO/disk2/orders1.dbf;(4)使表空间联机使表空间联机ALTER TABLESPACE ORDERS ONLINE;(5)删除旧数据文件删除旧数据文件SUPPORT SERVICES2.4 使用本地管理表空间使用本地管理表空间(LMT)自动段空
10、间管理自动段空间管理（ASSM）在在920以前，表的剩余空间的管理与分配都是由链接列以前，表的剩余空间的管理与分配都是由链接列 表表 freelist来完成的，因为来完成的，因为freelist存在串行的问题因此容易引起往往存在串行的问题因此容易引起往往容易引起段头的争用与空间的浪费（其实这一点并不明显），最容易引起段头的争用与空间的浪费（其实这一点并不明显），最主要的还是因为需要主要的还是因为需要DBA 花费大量的精力去管理这些争用并监花费大量的精力去管理这些争用并监控表的空间利用。控表的空间利用。自动段空间管理（自动段空间管理（ASSM），它首次出现在），它首次出现在Oracle920里。
11、有了里。有了ASSM，链接列表，链接列表freelist被位图所取代，它是一个二进制的数组被位图所取代，它是一个二进制的数组，能够迅速有效地管理存储扩展和剩余区块（，能够迅速有效地管理存储扩展和剩余区块（free block），因），因此能够改善分段存储本质，此能够改善分段存储本质，ASSM表空间上创建的段还有另外一表空间上创建的段还有另外一个称呼叫个称呼叫Bitmap Managed Segments（BMB 段）。段）。SUPPORT SERVICES2.4 使用本地管理表空间使用本地管理表空间(LMT)自动段空间管理自动段空间管理（ASSM）create tablespace demo
12、datafile/ora01/oem/demo01.dbf size 5m EXTENT MANAGEMENT LOCAL-Turn on LMT SEGMENT SPACE MANAGEMENT AUTO-Turn on ASSM;SUPPORT SERVICES2.4 使用本地管理表空间使用本地管理表空间(LMT)自动段空间管理自动段空间管理（ASSM）n 带有带有ASSMASSM的本地管理的本地管理tablespacetablespace会略掉任何为会略掉任何为PCTUSEDPCTUSED、NEXTNEXT和和FREELISTSFREELISTS所指定的值。所指定的值。当表格或者索引被分
13、配到这个当表格或者索引被分配到这个tablespacetablespace以后，用于以后，用于独立对象的独立对象的PCTUSEDPCTUSED的值会被忽略，而的值会被忽略，而Oracle9iOracle9i会使用会使用位图数组来自动地管理位图数组来自动地管理tablespacetablespace里表格和索引的里表格和索引的freelistfreelist。对于在。对于在LMTLMT的的tablespacetablespace内部创建的表格和内部创建的表格和索引而言，这个索引而言，这个NEXTNEXT扩展子句是过时的，因为由本地管扩展子句是过时的，因为由本地管理的理的tablespacetab
14、lespace会管理它们。但是，会管理它们。但是，INITIALINITIAL参数仍然参数仍然是需要的，因为是需要的，因为OracleOracle不可能提前知道初始表格加载的不可能提前知道初始表格加载的大小。大小。SUPPORT SERVICES2.4 使用本地管理表空间使用本地管理表空间(LMT)自动段空间管理自动段空间管理（ASSM）n 新的管理机制用位图来跟踪或管理每个分配到对象的新的管理机制用位图来跟踪或管理每个分配到对象的块，每个块有多少剩余空间根据位图的状态来确定，如块，每个块有多少剩余空间根据位图的状态来确定，如75%,50%-75%,25%-50%75%,50%-75%,25%
15、-50%和和25%95%SUPPORT SERVICES3.3 内存参数的调整内存参数的调整n2.共享池的命中率共享池的命中率nSelect sum(pins)/sum(pins)+sum(reloads)*100“hit radio”From v$librarycache;n命中率命中率95%SUPPORT SERVICES3.3 内存参数的调整内存参数的调整n3.排序部分排序部分SUPPORT SERVICES3.3 内存参数的调整内存参数的调整n4.log_bufferSUPPORT SERVICES4.SQL优化概述n1.ORACLE 优化器优化器n2.SQL索引和优化索引和优化n3查
16、询的优化查询的优化n4.Optimizer HintsSUPPORT SERVICES4.1 ORACLE 优化器优化器nCost Based Optimizer(简称简称CBO)基于统计信息的优化基于统计信息的优化nRule Based Optimizer(简称简称RBO)基于数据字典的优化，在以后的版本中将不支基于数据字典的优化，在以后的版本中将不支持持SUPPORT SERVICES4.1 ORACLE 优化器优化器n根据数据字典根据数据字典查询有无可用的索引，如果有则使用，否则不查询有无可用的索引，如果有则使用，否则不使用使用n不同的访问方法有预定好的优先级，选择优先不同的访问方法有预
17、定好的优先级，选择优先级高的执行方法级高的执行方法RBOSUPPORT SERVICES4.1 ORACLE 优化器优化器n需要收集统计信息需要收集统计信息n表有多少行，占用多少数据块表有多少行，占用多少数据块n列有多少个列有多少个Null值、不同值值、不同值n列的最大值和最小值，及值的分布情况列的最大值和最小值，及值的分布情况n索引的层次、结点数、叶结点数，及行的分布状况索引的层次、结点数、叶结点数，及行的分布状况(Cluster)n根据一定算法算出一个成本值，选择成本值最低的执行根据一定算法算出一个成本值，选择成本值最低的执行方法，不一定使用索引。方法，不一定使用索引。CBOSUPPORT
18、 SERVICES4.1 ORACLE 优化器优化器n新的应用应当基于新的应用应当基于CBO开发开发n用用dbms_stats收集信息收集信息n在语句中用在语句中用hint指定指定rule choose first_rows all_rowsn在在session中用中用alter session指定指定optimizer_mode参数参数n在参数文件中指定在参数文件中指定optimizer_mode参数参数使用使用CBOSUPPORT SERVICES5.Statspack概述STATSPACK 概况介绍概况介绍配置配置STATSPACK运行运行STATSPACK数据采集数据采集生成报告生成报
19、告设置设置STATSPACK的参数文件的参数文件SUPPORT SERVICESSUPPORT SERVICESSUPPORT SERVICESSUPPORT SERVICESSUPPORT SERVICESSUPPORT SERVICESSUPPORT SERVICESSUPPORT SERVICESSUPPORT SERVICESSUPPORT SERVICESSUPPORT SERVICESSUPPORT SERVICESSUPPORT SERVICESSUPPORT SERVICESSUPPORT SERVICESSUPPORT SERVICESSUPPORT SERVICESSUPPORT SERVICESSUPPORT SERVICESSUPPORT SERVICES4.1 ORACLE 优化器优化器SUPPORT SERVICES4.1 ORACLE 优化器优化器SUPPORT SERVICES4.1 ORACLE 优化器优化器SUPPORT SERVICES4.1 ORACLE 优化器优化器SUPPORT SERVICES
