1 / 65

数据库性能调整

数据库性能调整. 调整概述 调整 Oracle 的内存使用 调整 Oracle 的磁盘利用 调整 数据库的应用. 调整 Oracle 的磁盘利用. 数据库配置和 I/O 问题 有效利用 Oracle 数据块 调整排序操作 调整回滚段. 不同类型 Oracle 文件的 I/O 统计. Process CKPT DBW n LGWR ARC n SERVER.

hide
Download Presentation

数据库性能调整

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 数据库性能调整 • 调整概述 • 调整Oracle的内存使用 • 调整Oracle的磁盘利用 • 调整数据库的应用

  2. 调整Oracle的磁盘利用 • 数据库配置和I/O问题 • 有效利用Oracle数据块 • 调整排序操作 • 调整回滚段

  3. 不同类型Oracle文件的I/O统计 Process CKPTDBWnLGWRARCn SERVER Oracle File I/OData Files Log Archive Control Write Write Write Write Read Write Read/write Read

  4. 表空间的利用 • 仅为系统数据字典对象保留SYSTEM表空间 • 创建本地管理的表空间，避免空间管理的问题发生 • 将表和索引放在不同的表空间中存放 • 创建单独的回滚表空间 • 在各自的表空间中存储非常大的数据库对象 • 建立一个或多个临时表空间

  5. 在不同的设备上分布文件 • 数据文件与重做日志文件分开存放 • 表数据划分 • 减少磁盘的I/O • 评价裸设备的使用

  6. Oracle文件的划分 • 操作系统划分: • 使用操作系统划分软件或RAID • 确定合适的划分大小 • 手工划分: • 使用CREATE TABLE或ALTER TABLE ALLOCATE命令 • 并行查询值得利用

  7. 调整全表扫描操作 • 了解全表扫描的需求 • 指定初始化参数DB_FILE_MULTIBLOCK_READ_COUNT： • 确定服务器进程一次读取数据块的个数 • 对cost-based优化器的执行计划有影响 • 使用V$SESSION_LONGOPS视图来监测长时间运行的全表扫描操作

  8. Data files 检查I/O统计的诊断工具 Oracle I/O 利用 • System I/O 利用 • V$FILESTAT • V$DATAFILE • Performancetools report.txt

  9. 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, phys_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 26 26 50 257 257 411 SCOTT_DATA /DISK4/scott_dat.dbf 65012 416752 38420 564 564 8860 SCOTT_INDEX /DISK4/scott_ind.dbf 8 8 0 8 8 0 SYSTEM /DISK1/sys01.dbf 806 1538 1985 116 116 1721 TEMP /DISK1/temp01.dbf 168 666 483 675 675 0 USER_DATA /DISK3/user01.dbf 8 8 0 8 8 0 6 rows selected.

  10. Member Member Member Member Redo Log 组和成员 LGWR Group 1 Group 2 Group 3 Disk 1 Member Member Disk 2

  11. 联机重做日志文件的配置 • 可以进行如下配置： • 适当设置重做日志文件的大小，减少冲突 • 设置足够的重做日志组，防止等待 • 重做日志文件单独存放，并最好是快速设备 • 可以查询动态性能视图V$LOGFILE和V$LOG

  12. 归档日志文件的配置 • 允许LGWR进程写重做日志到与ARCn进程正在读的不同磁盘上 • 共享归档作业： • 改变归档速度： • LOG_ARCHIVE_MAX_PROCESSES, • LOG_ARCHIVE_DEST_n, • (LOG_ARCHIVE_DUPLEX_DEST, • LOG_ARCHIVE_MIN_SUCCEED_DEST) ALTER SYSTEM ARCHIVE LOG ALL TO <log_archive_dest>

  13. Archived logs 诊断工具 • V$ARCHIVE_DEST • V$ARCHIVED_LOG • V$ARCHIVE_PROCESSES LOG_ARCHIVE_DEST_STATE_n

  14. 检查点 • 检查点导致： • DBWn进程执行I/O • CKPT进程更新数据文件头和控制文件 • 检查点频繁发生： • 减少实例恢复的时间 • 降低运行时间的性能

  15. 原则 • 适当设置联机重做日志文件的大小，减少检查点的发生次数 • 增加联机重做日志组来提高LGWR进程开始重写的时间 • 与检查点控制有关的初始化参数： • FAST_START_IO_TARGET • LOG_CHECKPOINT_INTERVAL • LOG_CHECKPOINT_TIMEOUT • DB_BLOCK_MAX_DIRTY_TARGET

  16. 调整DBWn的I/O • 影响DBWn进程更经常地写脏缓冲区的参数是DB_BLOCK_MAX_DIRTY_TARGET • 如果脏缓冲区的个数低于计算的低限，DBWn进程不写检查点缓冲区 • 如果脏缓冲区的个数介于计算的低限和高限之间，DBWn进程从检查点队列中写，直至检查点缓冲区的个数低于计算的低限 • 如果脏缓冲区的个数高于计算的高限，DBWn进程则写检查点缓冲区 • 缺省值为(2*32)-1

  17. Tablespace Extents Extents Extents 数据库存储的层次结构 Segments Blocks

  18. Tablespace Freespace Segments Extents Extents Fullextents Incrementalextent Blocks 分配一个范围 • 避免动态地分配范围: • 创建本地管理的表空间 • 适当设置段的大小 • 监测段的存储，并准备扩展

  19. 避免动态分配 显示空闲数据块小于10%的段: SQL> SELECT owner, table_name, blocks, empty_blocks 2 FROM dba_tables 3 WHERE empty_blocks / (blocks+empty_blocks) < .1; OWNER TABLE_NAME BLOCKS EMPTY_BLOCKS ------ ---------- ---------- ------------ HR EMP 1450 50 HR REGION 460 40 避免动态分配: SQL> ALTER TABLE hr.emp ALLOCATE EXTENT; Table altered.

  20. 避免动态分配 • 建立本地管理的表空间: CREATE TABLESPACE user_data_1 DATAFILE ‘oracle8/oradata/db1/lm_1.dbf’ SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;

  21. 大范围的优缺点 • 优点： • 较少地动态扩展 • 带来一些性能方面的好处 • 可以克服操作系统关于文件大小的限制 • 缺点： • 空闲空间可能减少 • 未使用的空间

  22. 调整Oracle的磁盘利用 • 数据库配置和I/O问题 • 有效利用Oracle数据块 • 调整排序操作 • 调整回滚段

  23. Tablespace Extents Extents Extents 数据块的大小 • 减少数据块的访问: • 使用大的数据块大小 • 行的存储紧密打包 • 防止行的迁移 Segments Blocks

  24. DB_BLOCK_SIZE • 当数据库创建时设置 • 是数据文件读写的最小I/O单位 • 缺省为2KB或4KB，允许到64KB • 不容易改变 • 应当是操作系统块大小的倍数 • 操作系统I/O大小大于或等于DB_BLOCK_SIZE

  25. 小数据块的优缺点 • 优点： • 减少数据块的冲突 • 适合于小的行 • 适合于随机访问 • 缺点： • 相对来说，开销较大 • 每个数据块容纳较少的行 • 可能会导致读取更多的索引块

  26. 大数据块的优缺点 • 优点： • 较小的开销 • 适合于非常大的行 • 适合于顺序访问 • 利用索引读取数据会有更好的性能 • 缺点： • 数据块冲突增加 • 占用buffer cache更多的空间

  27. PCTFREE和PCTUSED Inserts Inserts 1 2 Inserts Inserts 3 4

  28. 原则 • PCTFREE • 缺省为10 • 如果没有UPDATE操作，可以设置为0 • PCTFREE = 100 x upd / (upd + ins) • PCTUSED • 缺省为40 • 如果有删除的行可以设置 • PCTUSED = 100 - PCTFREE - 100 x rows x (ins + upd) / blocksize

  29. Migration和Chaining Index Table

  30. 检测迁移和链接 使用ANALYZE命令检测迁移的和链接的行: SQL> ANALYZE TABLE sales.order_hist COMPUTE STATISTICS; Table analyzed. SQL> SELECT num_rows, chain_cnt FROM dba_tables 2 WHERE table_name=‘ORDER_HIST’; NUM_ROWS CHAIN_CNT --------- --------- 168 102 从report.txt的输出检测迁移的和链接的行: Statistic Total Per transaction ... ------------------------- ----- --------------- ... table fetch continued row 495 .02

  31. 选择迁移的行 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 ORDER_HIST AAAAluAAHAAAAA1AAA SALES ORDER_HIST AAAAluAAHAAAAA1AAB ...

  32. 消除迁移的行 1. 运行ANALYZE TABLE ... LIST CHAINED ROWS命令 2. 将有关的行从原表复制到另外的临时表中 3. 删除原表中的有关行 4. 从另外的临时表将有关行插入原表中 说明：第4步即可以消除迁移的行，因为迁移 行的产生有由于UPDATE语句造成的，而非INSERT语句

  33. 高水位标志 High- water mark Empty blocks (rows deleted) Extent 1 Extent 2 Segment header block Empty blocks (never used) • 记录在段头块中 • 在创建时，设置在段的开始处 • 在插入行时，以5个数据块为单位增长 • 由TRUNCATE命令复位，而不是DELETE命令

  34. 表的统计 查询来自ANALYZE命令的关于表的统计信息: SQL> ANALYZE TABLE 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 184

  35. DBMS_SPACE包 declare owner VARCHAR2(30); name VARCHAR2(30); seg_type VARCHAR2(30); tblock NUMBER; ... BEGIN dbms_space.unused_space ('&owner','&table_name','TABLE', tblock,tbyte,ublock,ubyte,lue_fid,lue_bid,lublock); dbms_output.put_line(... END; /

  36. 索引重组织 • 在经常变动的表上建立的索引可能会引起性能问题 • 空的索引块可以回到free list中 • 即使一个数据块只包含一个项目，它也必须维护 • 可以对索引执行重新建立的操作

  37. 监测索引 SQL> 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 ----------- 24 SQL> ALTER INDEX acct_no_idx REBUILD; Index altered.

  38. 调整Oracle的磁盘利用 • 数据库配置和I/O问题 • 有效利用Oracle数据块 • 调整排序操作 • 调整回滚段

  39. 需要排序的有关操作 • 创建索引 • 包括索引维护的并行插入操作 • ORDER BY或GROUP BY子句 • DISTINCT值选择 • UNION、INTERSECT或MINUS操作 • 排序合并的连接 • ANALYZE命令的执行

  40. 排序空间需求大于SORT_AREA_SIZE: • Sort run 1 • Sort run 2 TEMPORARY tablespace Serverprocess 临时段可以在服务器操作其它排序run时，保留数据 • Temporary segment • Sort run 2 排序处理 SORT_MULTIBLOCK_READ_COUNT可以强制在排序的合并阶段一次读入大量排序run数据到内存

  41. 排序区和参数 • 专用服务器连接在PGA 排序空间： • PGA • UGA • Shared pool • Stack • space • User session data • Cursor state • Sort area SORT_AREA_SIZE = 64000 • 多线索服务器连接在共享池 • PGA • UGA • Stack • space • User session data • Cursor state • Sort • area SORT_AREA_RETAINED_SIZE = 64000

  42. temp01.dbf 2M temp02.dbf 2M 排序处理和临时空间 临时表空间 临时的表空间是执行CREATE TABLESPACE ... DATAFILE ... TEMPORARY命令创建的 Permanent Objects One single sort segment temp03.dbf 2M

  43. 临时空间段 • 由第一次排序操作创建 • 在需要时进行扩展 • 由多个范围构成，不同的排序操作都可以使用 • 在SGA的排序范围池(SEP)中说明

  44. 调整排序操作 • 无论何时，尽量避免排序操作 • 尽可能使排序操作在内存中完成，减少分页及交换 • 减少空间分配的调用：适当地分配临时空间

  45. 避免排序操作 无论何时，都应当尽量避免排序操作: • 创建索引时使用NOSORT选项 • 使用UNION ALL，而不是UNION • 在表的连接时，使用索引进行访问 • 为在ORDER BY子句中引用的列创建索引 • 为分析选择列 • 对于大的对象分析时，使用ESTIMATE，而不是COMPUTE

  46. 诊断工具 • V$SORT_USAGE • V$SORT_SEGMENT V$SYSSTAT Serverprocess Sort area (UGA) PCTINCREASE INITIAL NEXT TEMPORARY tablespace (sorts(memory)) (sorts(disk)) SORT_AREA_SIZE SORT_AREA_RETAINED_SIZE SORT_MULTIBLOCK_READ_COUNT report.txt

  47. 诊断和原则 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 mem.name = 'sorts (memory)' 5 and disk.name = 'sorts (disk)'; Disk Mem Ratio --------- --------- --------- 23 206 11.165049 • 磁盘排序与内存排序的比率应当小于5% • 如果比率大于5%时，可以提高SORT_AREA_SIZE参数的值

  48. 监测临时表空间 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_EXTENTS USED_EXTENTS EXTENT_HITS MAX_USED_BLOCKS MAX_SORT_BLOCKS --------------- ------------- ------------- ------------ ----------- --------------- --------------- TEMP 2 4 3 20 200 200 • 缺省的存储参数适用于排序段 • 排序段不限制范围数

  49. 调整Oracle的磁盘利用 • 数据库配置和I/O问题 • 有效利用Oracle数据块 • 调整排序操作 • 调整回滚段

  50. Data files 回滚段: 使用 Transaction rollback Read consistency Rollback segment Control files Redo logfiles Transaction recovery

More Related