数据库性能调整PPT课件

1、 调整概述 调整Oracle的内存使用 调整Oracle的磁盘利用 调整数据库的应用数据库性能调整 数据库配置和I/O问题 有效利用Oracle数据块 调整排序操作 调整回滚段调整Oracle的磁盘利用不同类型Oracle文件的I/O统计ProcessCKPTDBWnLGWRARCnSERVEROracle File I/OData Files Log Archive Control Write Write Write Write Read Write Read/write Read表空间的利用仅为系统数据字典对象保留SYSTEM表空间创建本地管理的表空间，避免空间管理的问题发生将表和索引放在

2、不同的表空间中存放创建单独的回滚表空间在各自的表空间中存储非常大的数据库对象建立一个或多个临时表空间在不同的设备上分布文件数据文件与重做日志文件分开存放表数据划分减少磁盘的I/O评价裸设备的使用Oracle文件的划分操作系统划分:使用操作系统划分软件或RAID 确定合适的划分大小手工划分:使用CREATE TABLE或ALTER TABLE ALLOCATE命令并行查询值得利用调整全表扫描操作了解全表扫描的需求指定初始化参数DB_FILE_MULTIBLOCK_READ_COUNT： 确定服务器进程一次读取数据块的个数 对cost-based优化器的执行计划有影响使用V$SESSION_LON

3、GOPS视图来监测长时间运行的全表扫描操作检查I/O统计的诊断工具V$FILESTATV$DATAFILEData files report.txtOracle I/O 利用利用System I/O 利用利用Performancetools I/O统计 SQL Rem I/O should be spread evenly across drives. A big difference between phys_reads and phys_blks_rd implies table scans are going on.SQL select table_space, file_name, p

4、hys_reads reads, phys_blks_rd 2 blks_read, phys_rd_time read_time, phys_writes writes, phys_blks_wr 3 blks_wrt, phys_wrt_tim write_time 4 from stats$files order by table_space, file_name;TABLE_SPACE FILE_NAME READS BLKS_ READ_ WRITES BLKS_ WRITE_ READ TIME WRT TIME- - - - - - - -RBS /DISK2/rbs01.dbf

5、 26 26 50 257 257 411SCOTT_DATA /DISK4/scott_dat.dbf 65012 416752 38420 564 564 8860SCOTT_INDEX /DISK4/scott_ind.dbf 8 8 0 8 8 0SYSTEM /DISK1/sys01.dbf 806 1538 1985 116 116 1721TEMP /DISK1/temp01.dbf 168 666 483 675 675 0USER_DATA /DISK3/user01.dbf 8 8 0 8 8 06 rows selected.Redo Log 组和成员MemberMemb

6、erMemberMember LGWR联机重做日志文件的配置可以进行如下配置： 适当设置重做日志文件的大小，减少冲突 设置足够的重做日志组，防止等待 重做日志文件单独存放，并最好是快速设备 可以查询动态性能视图V$LOGFILE和V$LOG归档日志文件的配置允许LGWR进程写重做日志到与ARCn进程正在读的不同磁盘上共享归档作业：改变归档速度：LOG_ARCHIVE_MAX_PROCESSES, LOG_ARCHIVE_DEST_n, (LOG_ARCHIVE_DUPLEX_DEST, LOG_ARCHIVE_MIN_SUCCEED_DEST)ALTER SYSTEM ARCHIVE LOG

7、ALL TO 诊断工具 V$ARCHIVE_DESTV$ARCHIVED_LOGV$ARCHIVE_PROCESSESArchived logsLOG_ARCHIVE_DEST_STATE_n检查点 检查点导致： DBWn进程执行I/O CKPT进程更新数据文件头和控制文件检查点频繁发生： 减少实例恢复的时间 降低运行时间的性能原则适当设置联机重做日志文件的大小，减少检查点的发生次数增加联机重做日志组来提高LGWR进程开始重写的时间与检查点控制有关的初始化参数： FAST_START_IO_TARGET LOG_CHECKPOINT_INTERVAL LOG_CHECKPOINT_TIMEOU

8、T DB_BLOCK_MAX_DIRTY_TARGET调整DBWn的I/O影响DBWn进程更经常地写脏缓冲区的参数是DB_BLOCK_MAX_DIRTY_TARGET如果脏缓冲区的个数低于计算的低限，DBWn进程不写检查点缓冲区如果脏缓冲区的个数介于计算的低限和高限之间，DBWn进程从检查点队列中写，直至检查点缓冲区的个数低于计算的低限如果脏缓冲区的个数高于计算的高限，DBWn进程则写检查点缓冲区缺省值为(2*32)-1Tablespace数据库存储的层次结构SegmentsExtentsExtentsExtents分配一个范围TablespaceSegmentsExtentsExtentsF

9、ullextentsFreespaceIncrementalextent避免动态分配SQL SELECT owner, table_name, blocks, empty_blocks 2 FROM dba_tables 3 WHERE empty_blocks / (blocks+empty_blocks) ALTER TABLE hr.emp ALLOCATE EXTENT;Table altered.避免动态分配建立本地管理的表空间:CREATE TABLESPACE user_data_1DATAFILE oracle8/oradata/db1/lm_1.dbfSIZE 100MEXT

10、ENT MANAGEMENT LOCAL UNIFORM SIZE 2M;大范围的优缺点优点： 较少地动态扩展 带来一些性能方面的好处 可以克服操作系统关于文件大小的限制缺点： 空闲空间可能减少 未使用的空间 数据库配置和I/O问题 有效利用Oracle数据块 调整排序操作 调整回滚段调整Oracle的磁盘利用数据块的大小TablespaceSegmentsExtentsExtentsExtentsDB_BLOCK_SIZE当数据库创建时设置是数据文件读写的最小I/O单位缺省为2KB或4KB，允许到64KB不容易改变应当是操作系统块大小的倍数操作系统I/O大小大于或等于DB_BLOCK_SIZ

11、E小数据块的优缺点优点： 减少数据块的冲突 适合于小的行 适合于随机访问缺点： 相对来说，开销较大 每个数据块容纳较少的行 可能会导致读取更多的索引块大数据块的优缺点优点： 较小的开销 适合于非常大的行 适合于顺序访问 利用索引读取数据会有更好的性能缺点： 数据块冲突增加 占用buffer cache更多的空间PCTFREE和PCTUSED1234原则PCTFREE 缺省为10 如果没有UPDATE操作，可以设置为0 PCTFREE = 100 x upd / (upd + ins)PCTUSED 缺省为40 如果有删除的行可以设置 PCTUSED = 100 - PCTFREE - 100

12、x rows x (ins + upd) / blocksize Migration和Chaining检测迁移和链接使用使用ANALYZE命令检测迁移的和链接的行命令检测迁移的和链接的行:Statistic Total Per transaction . - - - .table fetch continued row 495 .02 从从report.txt的输出的输出检测迁移的和链接的行检测迁移的和链接的行:SQL ANALYZE TABLE sales.order_hist COMPUTE STATISTICS;Table analyzed.SQL SELECT num_rows, ch

13、ain_cnt FROM dba_tables 2 WHERE table_name=ORDER_HIST; NUM_ROWS CHAIN_CNT- - 168 102选择迁移的行SQL ANALYZE TABLE sales.order_hist LIST CHAINED ROWS;Table analyzed.SQL SELECT owner_name, table_name, head_rowid 2 FROM chained_rows 3 WHERE table_name = ORDER_HIST;OWNER_NAME TABLE_NAME HEAD_ROWID - - -SALES

14、ORDER_HIST AAAAluAAHAAAAA1AAASALES ORDER_HIST AAAAluAAHAAAAA1AAB.消除迁移的行1. 运行ANALYZE TABLE . LIST CHAINED ROWS命令2. 将有关的行从原表复制到另外的临时表中3. 删除原表中的有关行4. 从另外的临时表将有关行插入原表中说明：第4步即可以消除迁移的行，因为迁移行的产生有由于UPDATE语句造成的，而非INSERT语句高水位标志记录在段头块中在创建时，设置在段的开始处在插入行时，以5个数据块为单位增长由TRUNCATE命令复位，而不是DELETE命令表的统计SQL ANALYZE TABLE

15、 hr.emp COMPUTE STATISTICS;Table analyzed.SQL SELECT num_rows, blocks, empty_blocks as empty, 2 avg_space, chain_cnt, avg_row_len 3 FROM dba_tables 4 WHERE owner = HR 5 AND table_name = EMP;NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN- - - - - - 13214 615 35 1753 0 184DBMS_SPACE包declare own

16、erVARCHAR2(30);name VARCHAR2(30);seg_typeVARCHAR2(30);tblockNUMBER;.BEGIN dbms_space.unused_space (&owner,&table_name,TABLE, tblock,tbyte,ublock,ubyte,lue_fid,lue_bid,lublock); dbms_output.put_line(.END;/索引重组织在经常变动的表上建立的索引可能会引起性能问题空的索引块可以回到free list中即使一个数据块只包含一个项目，它也必须维护可以对索引执行重新建立的操作监测索引SQL

17、 ANALYZE INDEX acct_no_idx VALIDATE STRUCTURE;Index analyzed.SQL SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 2 AS index_usage 3 FROM index_stats;INDEX_USAGE- 24SQL ALTER INDEX acct_no_idx REBUILD;Index altered. 数据库配置和I/O问题 有效利用Oracle数据块 调整排序操作 调整回滚段调整Oracle的磁盘利用需要排序的有关操作创建索引包括索引维护的并行插入操作ORDER BY或GROU

18、P BY子句DISTINCT值选择UNION、INTERSECT或MINUS操作排序合并的连接ANALYZE命令的执行排序处理排序空间需求大于排序空间需求大于SORT_AREA_SIZE:Serverprocess Sort run 1 Sort run 2TEMPORARY tablespace Sort run 2 Temporary segmentMULTIBLOCK排序区和参数排序空间： Stack space User session data Cursor state Stack space PGA 专用服务器连接在专用服务器连接在PGA 多线索服务器连接在共享池多线索服务器连接在

19、共享池 User session data Cursor state PGA UGA UGA Sort area Sort areaSORT_AREA_SIZE = 64000SORT_AREA_RETAINED_SIZE = 64000排序处理和临时空间临时的表空间是执行CREATE TABLESPACE . DATAFILE . TEMPORARY命令创建的temp03.dbf 2Mtemp02.dbf 2Mtemp01.dbf 2MPermanent ObjectsOne single sort segment 由第一次排序操作创建 在需要时进行扩展 由多个范围构成，不同的排序操作都可以

20、使用 在SGA的排序范围池(SEP)中说明临时空间段 无论何时，尽量避免排序操作 尽可能使排序操作在内存中完成，减少分页及交换 减少空间分配的调用：适当地分配临时空间调整排序操作避免排序操作无论何时，都应当尽量避免排序操作: 创建索引时使用NOSORT选项 使用UNION ALL，而不是UNION 在表的连接时，使用索引进行访问 为在ORDER BY子句中引用的列创建索引 为分析选择列 对于大的对象分析时，使用ESTIMATE，而不是COMPUTE诊断工具V$SYSSTATSort area (UGA)V$SORT_SEGMENTV$SORT_USAGEreport.txtSORT_AREA_

21、SIZESORT_AREA_RETAINED_SIZESORT_MULTIBLOCK_READ_COUNTServerprocessTEMPORARYtablespace(sorts(disk)(sorts(memory)PCTINCREASEINITIALNEXT诊断和原则SQL select disk.value Disk, mem.value Mem, 2 (disk.value/mem.value)*100 Ratio 3 from v$sysstat mem, v$sysstat disk 4 where = sorts (memory) 5 and disk.na

22、me = sorts (disk); Disk Mem Ratio- - - 23 206 11.165049 磁盘排序与内存排序的比率应当小于5% 如果比率大于5%时，可以提高SORT_AREA_SIZE参数的值监测临时表空间SQL select tablespace_name, current_users, total_extents, 2 used_extents, extent_hits, max_used_blocks, 3 max_sort_blocks 4 from v$sort_segment;TABLESPACE_NAME CURRENT_USERS TOTAL_EXTENT

23、S USED_EXTENTS EXTENT_HITS MAX_USED_BLOCKS MAX_SORT_BLOCKS - - - - - - - TEMP 2 4 3 20 200 200 缺省的存储参数适用于排序段缺省的存储参数适用于排序段 排序段不限制范围数排序段不限制范围数 数据库配置和I/O问题 有效利用Oracle数据块 调整排序操作 调整回滚段调整Oracle的磁盘利用回滚段: 使用Control files Data files Redo logfiles回滚段的活动4312updateupdateinsertinsertupdateT1T2updateupdateinserti

24、nsertupdateT1T2T3T4回滚段头的活动4312updateupdateinsertinsertupdateT1T2updateupdateinsertinsertupdateT3T4T5T6回滚段头包含每个事务的条目每个事务必须有更新的访问回滚段的增长124312345 由于缺少回滚空间，导致事务处理失败 快照太老的错误发生，可能是由于以下原因： 所需要的块中有关内容已经被重新使用，即块中的SCN比查询开始时的SCN更新 回滚段头中的事务插槽已经被重新使用 回滚段中的撤消数据在提交之后，已经被覆盖调整回滚段调整回滚段事务不应当等待回滚段的访问在正常运行期间不应当扩展回滚段用户和使

25、用持续应当尽量少使用回滚事务不应当用完回滚空间在需要时用户能够看到读一致性的映象调整回滚段的诊断工具4312T3T4T5T6DataHeaderSGAV$ROLLSTATV$ROLLNAMEV$TRANSACTIONV$SESSIONreport.txtV$SYSTEM_EVENTV$ WAITSTATV$SYSSTAT回滚段头冲突的诊断所有等待的合计与获得的合计之比应当小于1%否则，应当创建更多的回滚段SQL select sum(waits)* 100 /sum(gets) Ratio, 2 sum(waits) Waits, sum(gets) Gets 3 from v$rollstat; Ratio Waits Gets- - -0.296736 5 1685回滚段冲突的诊断 SQL select value from v$sysstat 2 where name = consistent gets; VALUE- 71563任何类别的等待数都应当小于请求数的1%否则，应当创建更多的回滚段原则: 回滚段的个数Large rollbackSmall RBSSmall RBSSmall RBSSmall RBSSmall RBSSmall RBSSQL SET TRANSACTION USE