Oracle性能调整和优化课件

1、Oracle数据库性能 调整和优化Oracle技术专题讲座内容提要1.oracle 性能调整概述 2.磁盘I/O的调整3.oracle 内存分配与调整4.SQL优化概述5.Statspack概述1.oracle 性能调整概述磁盘I/O内存争用CPU占用时间数据库资源争用影响数据库性能的几大问题1.oracle 性能调整概述建立合理的数据库解决内存问题解决磁盘I/O争用问题解决其他相关问题,如回滚段等优化的目的2.磁盘I/O的调整.1 使用磁盘阵列.2 在可用硬盘之间分布关键数据文件.3 使用本地管理表空间(LMT)自动段空间管理 （ASSM).4 在大型表上使用表分区.5 管理回滚段.6 通过

2、内存排序来减少磁盘I/O.7 其他注意事项2.1 使用磁盘阵列RAID0: 允许自动磁盘分段(STRIPING).表空间所对应的数据文件可扩展到多个磁盘,并可同时对其进行访问(节省大量I/O).RAID1: 主要想得到更高的可用性.RAID5: 有大量 读操作的系统可获得最大性能. 有大量写操作的系统,其效率并不高.RAID1+0: 支持镜像的磁盘和分段. 2.1.1 可用的RAID级别建议:在一个系统大部分进行读取操作使用RAID5.优先使用RAID1+02.2在可用硬盘之间分布关键数据文件需要注意的的文件:SYSTEM 表空间TEMPORARY表空间回滚段和UNDO表空间联机重做日志文件（

3、最好放在最快的磁盘上）ORACLE_HOME下的文件经常被访问表的文件经常被访问的索引的数据文件操作系统2.2在可用硬盘之间分布关键数据文件/: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/u

4、08:Data2/u09:Index2/u10:Data3/u11:Index12.2.1 分开存储数据和索引文件Select col1,col2. from CUST_HEADER,CUST_DETAIL where .; Disk1: CUST_HEADER Table Disk5: CUST_HEADER Index Disk8: CUST_DETAIL Table Disk12: CUST_DETAIL Index2.3 避免I/O 磁盘争用磁盘争用通常发生在有多个进程试图同时访问同一个物理磁盘的情况下，因此把磁盘的I/O均匀的分布在多个可用的磁盘上，可有效减少磁盘的竞用通过查询V$f

5、ilestat和v$dbfile,以查看均衡数据文件后的效果2.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,564 903,199/d02/psindex_2.dbf 34,520 40,224 117,925 611,121/d03/psdata_01.dbf 35,189 3

6、6,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.2.3通过移动数据文件来均衡I/O使与数据文件有关的表空间脱机ALTER TABLESPACE ORDERS OFFLINE;(2) 把数据文件复制到磁盘的新位置上$cp /disk1/orders1.dbf /disk2/orders1.dbf重命名数据文件ALTER TABLESPACE ORDERS RENAME DATAFILE

7、/disk1/orders1.dbf TO /disk2/orders1.dbf;使表空间联机ALTER TABLESPACE ORDERS ONLINE;(5) 删除旧数据文件2.4 使用本地管理表空间(LMT)自动段空间管理（ASSM） 在920以前，表的剩余空间的管理与分配都是由链接列 表 freelist来完成的，因为freelist存在串行的问题因此容易引起往往容易引起段头的争用与空间的浪费（其实这一点并不明显），最主要的还是因为需要DBA 花费大量的精力去管理这些争用并监控表的空间利用。 自动段空间管理（ASSM），它首次出现在Oracle920里。有了ASSM，链接列表freel

8、ist被位图所取代，它是一个二进制的数组，能够迅速有效地管理存储扩展和剩余区块（free block），因此能够改善分段存储本质，ASSM表空间上创建的段还有另外一个称呼叫Bitmap Managed Segments（BMB 段）。 2.4 使用本地管理表空间(LMT)自动段空间管理（ASSM） create tablespace demo datafile /ora01/oem/demo01.dbf size 5m EXTENT MANAGEMENT LOCAL - Turn on LMT SEGMENT SPACE MANAGEMENT AUTO - Turn on ASSM; 2.4

9、使用本地管理表空间(LMT)自动段空间管理（ASSM） 带有ASSM的本地管理tablespace会略掉任何为PCTUSED、NEXT和FREELISTS所指定的值。 当表格或者索引被分配到这个tablespace以后，用于独立对象的PCTUSED的值会被忽略，而Oracle9i会使用位图数组来自动地管理tablespace里表格和索引的freelist。对于在LMT的tablespace内部创建的表格和索引而言，这个NEXT扩展子句是过时的，因为由本地管理的tablespace会管理它们。但是，INITIAL参数仍然是需要的，因为Oracle不可能提前知道初始表格加载的大小。 2.4 使用本

10、地管理表空间(LMT)自动段空间管理（ASSM） 新的管理机制用位图来跟踪或管理每个分配到对象的块，每个块有多少剩余空间根据位图的状态来确定，如75%,50%-75%,25%-50%和95%3.3 内存参数的调整2.共享池的命中率Select sum(pins)/sum(pins)+sum(reloads)*100 “hit radio” From v$librarycache;命中率95%3.3 内存参数的调整3. 排序部分3.3 内存参数的调整4.log_buffer4.SQL优化概述1.ORACLE 优化器2.SQL索引和优化3查询的优化4.Optimizer Hints4.1 ORAC

11、LE 优化器Cost Based Optimizer(简称CBO)基于统计信息的优化Rule Based Optimizer(简称RBO)基于数据字典的优化，在以后的版本中将不支持4.1 ORACLE 优化器根据数据字典查询有无可用的索引，如果有则使用，否则不使用不同的访问方法有预定好的优先级，选择优先级高的执行方法RBO4.1 ORACLE 优化器需要收集统计信息表有多少行，占用多少数据块列有多少个Null值、不同值列的最大值和最小值，及值的分布情况索引的层次、结点数、叶结点数，及行的分布状况(Cluster)根据一定算法算出一个成本值，选择成本值最低的执行方法，不一定使用索引。CBO4.1 ORACLE 优化器新的应用应当基于CBO开发用dbms_stats收集信息在语句中用hint指定rule choose first_rows all_rows在session中用alter sess