ORACLE 性能优化

ORACLE 数据库性能优化数据库性能优化 参考书目 ORACLE 9i Database Performance Tuning Guide and Reference ORACLE 9i Database Reference ORACLE 9i SQL Reference ORACLE 9i Database Administrator s Guide 一 一 数据库实例创建过程参数确定数据库实例创建过程参数确定 在创建数据库实例过程中 需要确定以下几个参数 1 数据块大小 数据块大小 DB BLOCK SIZE 该参数指明了 ORACLE 所处理的数据存贮于数据文档以及 SGA 内存中的 数据块大小 该参数的可选择的范围为 4k 8k 16k 32k 64k 对于 OLTP 系统而言 取值可 以为 4K 或 8K 对于 DSS 系统而言 则可以取较大的数据 如 32K 或 64K 建议统一取 8K 即 8192 说明 DB BLOCK SIZE 的大小将影响创建表时的 EXTENT 的大小 例如指定 db block size 16K 某表空间的 EXTENT MANAGEMENT 为 local autoallocate 则其 系统将 extent 的大小最小指定为 1M 所以将可能导致空间的浪费 2 字符集字符集 Character set 该参数确定数据库以何种字符集来存贮 CHAR 以及 VARCHAR VARCHAR2 等字符类型的值 对于 ORACLE 数据字典中的字符 如表及字段的 COMMENT 内容 具有同样的作用 因此需要考虑如字符集 的使用 对于国际项目 因为数据库中的 comment 内容 包括表及字符 存贮 过程中的中文字符等内容 可能性需要以中文存贮 而用户业务数据使用的字 符可能性是使用本地的语言 基于此 该参数需要选择支持 UNICODE 的字符 编码的字符集 目前 ORACLE9i 支持以下二种 UNICODE 字符集 UTF8 AL32UTF8 建议统一取 AL32UTF8 3 扩展段管理扩展段管理 EXTENT MANAGEMENT 该参数指明表空间中的扩展段的管理方式 对于 ORACLE9i 以前的数据库 其表空间中的扩展段的分配 回收是通过系 统字典表中的信息来分配的 对于该种扩展段的管理方式 ORACLE9i 做了根 本性的改动 现在可以不通过系统表中的信息来管理扩展段 改为通过位图方 式 即在各表空间中通过位图方式记录自己所属的 EXTENT 信息 该种方式管 理表空间中的 EXTENT 的分配其效果好 所以 ORACLE 公司建议使用这各位 图方式来管理表空间中的 EXTENT 如果选择这各方式 则参数为 LOCAL 对于使用这种 LOCAL 方式本分配 EXTENT 的情况下 还有一种参数 即 EXTENT 的大小的分配方式 AUTOALLOCATE UNIFORM 如果选择 UNIFORM 分配大小 则可以根据该表空间的使用特性 确定一 个值 如某表空间上存贮的基本上都是代码类的表 则可以定义 UNIFORM 大 小为 64K 而对于清单类表空间 则可以定义 UNIFORM 大小为 2M 或更大 如果选择 AUTOALLOCATE 则 ORACLE 会自动确定其每个 EXTENT 的 大小 注 ORACLE 的系统表空间 SYSTEM 的管理方式不能选择 UNIFORM 方 式分配大小 只能选择 AUTOALLOCATE 方式分配 建议 所有业务表空间使用 EXTENT MANAGEMENT LOCAL 方式来管理 EXTENT 对于清单类表空间 其 UNIFORM SIZE 2M 注意 如果指明表空间的管理方式是EXTENT MANAGEMENT LOCAL 则必须指表 系统默认表空间 指定为系统缺省的 TEMP 表空间即可 4 最大数据文件数最大数据文件数 MAXDATAFILES 指明了该实例最大的数据文件数 如果系统中数据文件使用数据大于这个 值 则表空间创建或扩展将无法进行 系统默认时该值比较小 需要更改这个 参数 建议取值 1000 或更大 5 最大重做日志文件数最大重做日志文件数 MAXLOGFILES 该参数确定系统最大的 REDO 文件数据 同样的 系统默认时取值有点小 需要更改 建议取值 20 6 重做日志重做日志 redo log 文件大小文件大小 重做日志文件的大小将影响数据库的性能 大的日志文件能够提高数据库 的性能 因为数据库在切换不同的日志文件时将做检查点操作 如果日志文件 太小 则检查点操作将会太频繁而导致性能的降底 但是太大的日志文件将给 数据可恢复性带来不利 例如当一个日志组中的所有日志不可用的情况发生时 则数据库的恢复将只能恢复到上一次检查点的时刻 即在大多数情况下是恢复 到上一个日志文件结束时所做的检查点操作的时刻 实际情况会因为数据库的 相关参数的设置不同而有所不一样 如 log checkpoint interval FAST START MTTR TARGET 等参数 所以理想的情况是 将重做日志文件的位置存放于快速 独立的物理设备 上 这样可以有效减少 LGWR 与 DBWR 进程的冲突等 重做日志文件的大小因系统规模及业务特性不同而有所不同 如计费数据 库的重做日志文件应比营收的要大 设置其大小有一个经验值 即观察日志文 件切换的时间间隔 控制其时间间隔在 20 分钟左右 如果当前系统的日志切换 时间间隔比这值小 则可能需要增大每组日志的大小 否则做相反的改动 注意 调整系统的日志大小只能通过增加新的日志组 将然后再删除以前旧的日志组的方 式进行 ORACLE 不支持直接更改日志文件的大小 建议 对于 100 万用户量的系统 计费数据库 500M 1G 分 5 10 组 每组两个 LOG 成员文件 营收数据库 300M 1G 分 5 10 组 每组两个 LOG 成员文件 7 表空间的数据段管理表空间的数据段管理 SEGMENT SPACE MANAGEMENT 当创建表空间的 EXTENT 的管理方式是 LOCAL 且为非临时表空间时 需 要指定数据段 如表 索引 分区等 的管理方式 系统有两种选择 MANUAL AUTO 如果指定为 MANUAL 方式 则 ORACLE 使用系统字典表中的 FREE LIST 的相关信息来分配 SEGMENT 空间 如果选择 AUTO 则系统通过表空间上的 BITMAP 信息来分配空间 建议 使用 AUTO 方式来管理 SEGMENT 的分配 注意 当设置成 AUTO 方式时 建表时的 PCTUSED FREELIST 等参数将不再有效 二 二 内存调整内存调整 1 内存调整的意义内存调整的意义 ORACLE 将其数据存贮于内存以及磁盘中 对内存的访问速度将远快于对 磁盘的访问速度 因此 数据库性能调优的一个最基本目标就是如何有效地减 少对磁盘的访问 物理 IO 加大内存的容量并根据业务特性有效地分配内存给 ORACLE 的不同内存结构使用将可以大大提高系统的性能 2 ORACLE 的内存结构的内存结构 Buffer cache ORACLE 数据块的缓存 ORACLE 在访问数据时 首先从该缓存在查 找所需数据 如果该缓存中没有数据 则系统试图从磁盘中获取数据 Shared pool 该部分内存存贮 ORACLE 的许多方面的数据 包括被执行过的 SQL 语 句以及其执行计划 系统数据字典以及其他数据 Large pool 这部分主要用来保存并行查询时候的一些信息 还有就是 RMAN 在备 份的时候可能会使用到 如果设置了 MTS 则由于 UGA 部分要移入这里 则需要具体根据 session 最大数量和 sort ares size 等相关会话内存参数的 设置来综合考虑这部分大小的设置 Log buffer ORACLE 在写 REDO LOG 文件之前 是先将 LOG 数据存于此处 当 LOG BUFFER 的大小充满度达到 1 3 时 将会触发物理的写日志操作 说明 1 如果系统中设置了 buffer pool keep buffer pool recycle 两个缓存的话 其内存的分配并不是从 buffer cache 中获取 而是从整个 SGA 的内存区 获取 具体相关说明见以下相关章节 2 在项目中 数据库一般不安装成 MTS 方式 3 SGA 内存之外的内存内存之外的内存 在设置与调整 SGA 内存之前 需要了解在数据库相同的主机上还有什么其 他应用程序也需要较大的内存 如计费数据库主机上 很可能同时也在跑计费 程序 另外就是操作系统本身出需要一定的内存 虽然各类操作系统都有虚拟内存的支持 也就是说可以将 SGA 设置成比物 理内存还要大的数据值 但这样做已完成违背了性能调优的意义 从而导致大 量的内存页面的切换以及物理 IO 操作 说明 可以通过操作系统的命令来查看是否存在大量的页面切换 HP UX swapinfo vmstat 等 AIX lsps vmstat 等 4 SGA 内存的取值范围内存的取值范围 确定分配给 ORACLE 的内存的大小需要考虑的主要因素有 主机上其他应 用的内存的需求 操作系统的内存需求 数据库的使用特性等 通常情况下 可以将 SGA 内存的最大使用值确定在 ORACLE 可用物理内 存的 70 以内 即从物理内存中去除各类非 ORACLE 应用所需内存所剩余的内 存的 70 以内 说明 设置 SGA 最大可用内存可通过修改参数 max sga size 5 设置设置 BUFFER CACHE a 确定确定 BUFFER CACHE 大小大小 在安装一个新的实例时 不可能比较准确知道设置多大的 BUFFER CACHE 是合适的 所以通常的做法是先根据经验设置一个大小 然后启动 ORACLE 的 相关统计开关 然后在该数据库上执行典型的应用或模拟实际应用 最后检查 ORACLE 的相关系统计数据 做出相应的调整 可以通过以下两个方面了解 BUFFER CACHE 的设置 检查系统表 V DB CACHE ADVICE 获取 BUFFER CACHE 的大小 的建议以及不同 BUFFER CACHE 大小对系统性能的影响 检查当前系统的 BUFFER CACHE 的命中率 为了获取 V DB CACHE ADVICE 中的数据 需要将数据库的参数 DB CACHE ADVICE 设置成 TRUE 可以通过以下可以通过以下 SQL 语句来获得语句来获得 BUFFER CACHE 的大小的建议的大小的建议 SELECT size for estimate buffers for estimate estd physical read factor estd physical reads FROM V DB CACHE ADVICE WHERE name DEFAULT AND block size SELECT value FROM V PARAMETER WHERE name db block size AND advice status ON 以下是从测试机中采集下来的统计数据 SIZE FOR ESTIMATESIZE FOR ESTIMATEESTD PHYSICAL READ FACTORESTD PHYSICAL READ FACTORESTD PHYSICAL READSESTD PHYSICAL READS 161 0352133313381 321128782380 480 546570380811 640 519866944627 800 500564460735 960 481161958561 1120 467260167511 1280 388350011600 1440 3747644259 1600 35745980325 1760 346444612701 1920 335443190507 2080 32141333401 2240 294337897133 2400 27134903244 2560 264334034453 2720 258133244488 2880 252832560815 3040 248431983589 3200 245431607853 以下是其统计图表 d db b c ca ac ch he e s si iz ze e大大小小与与物物理理读读次次数数的的估估计计 0 20000000 40000000 60000000 80000000 100000000 120000000 140000000 16 48 80 112 144 176 208 240 272 304 从以上数据得知 当前的 DB CACHE SIZE 大小约为 32M 如果在有空闲 物理内存的情况下 将 DB CACHE SIZE 调成 240M 将会有效减少系统的物理 IO 读的次数 但时 如果再增大其大小 对读的性能的提高将不会再有明显的 改善 另外 还可能通过了解系统另外 还可能通过了解系统 BUFFER CACHE 的命中率来检查当前系统的的命中率来检查当前系统的 DB CACHE SIZE 的设置是否太小 的设置是否太小 其 SQL 语句为 SELECT NAME VALUE FROM V SYSSTAT WHERE NAME IN session logical reads physical reads physical reads direct physical reads direct lob 以下也是从公司测试机中查询得到的数据 NAMEVALUE session logical reads784384 physical reads18442 physical reads direct87 physical reads direct lob 0 从中可知 其率中率为 1827258196 144517211 78 0 1827258196 100 92 1 说明 ORACLE 的命中率统计常用的有 BUFFER CACHE 命中率以及 SHARE POOL 命中 率 在一生产系统中 应尽量保证命中率达到以下水平 BUFFER CACHE 命中率 95 SHARE POOL 命中率 99 b 考虑使用多个考虑使用多个 BUFFER CACHE 池池 I 多多 BUFFER CACHE 使用场景使用场景 ORACLE 提供了多个 BUFFER CACHE 用于缓存数据块 但系统默认情况 下 只有 default buffer pool 一个 ORACLE 对于所有 buffer pool 都是使用 LRU 算法来决定何时将哪部分数据 换出 cache 因此 如果系统只有一个 buffer pool 的情况下 假设某个 segment 数据不被频繁地使用 但一但使用时 需要有较快的响应 速度 则如果只有 default buffer pool 情况下 这种 segment 是很容易被换出 cache 的 假设某个 segmetn 数据量巨大 如果系统也只有一个 defautl buffer pool 的情 况下 则为了将该部分的数据存放到 cache 中 则系统将会从 default fuffer pool 中换出非常多的使用较为频繁的数据块 从而导致系统性能的下降 基于以上数据的不同应用需求 ORACLE 提供了 keep buffer pool 以及 recycle buffer pool 两种 db cache 分别用于存放以下情况的数据 KEEP BUFFER POOL 用于存放使用频繁的数据以及希望常驻内存的数据 RECYCLE BUFFER POOL 用于存放数据量大的 SEGDMENT 的数据 DEFAULT BUFFER POOL 用于存放未指定存放位置的 segment 的数据 II 设置使用多设置使用多 buffer pool KEEP BUFFER POOL 修改系统参数 db keep cache size 定义或修改表或索引的存贮参数 RECYCLE BUFFER POOL 修改系统参数 db recycle cache size 定义或修改所期望的表或索引的存贮参数 CREATE TABLE CC AA C1 VARCHAR2 10 NOT NULL C2 VARCHAR2 10 NOT NULL TABLESPACE TAB CC STORAGE INITIAL 12K NEXT 2K BUFFER POOL KEEP ALTER TABLE CC TMP SKQ STORAGE BUFFER POOL RECYCLE 注意 无论是 KEEP BUFFER 还是 RECYCLE BUFFER 其内存的分配都不是 db cache size 的子集 6 设置设置 shared Pool a 设置好设置好 SHARE POOL 的意义的意义 share pool 主要存放系统执行过的 SQL 文本 SQL 执行计划 即不需要再做 执行解析的系统已编绎过的 SQL 可执行内容 系统数据字典信息等内容 虽 然 share pool 的大小不会象 db cache 那样大 但如果 share pool 没置不足的话 对系统性能的影响将会异常的明显 所以 ORACLE 公司建议 share pool 的命中 率需要保持 99 b SHARE POOL 的组成的组成 SHARE POOL 包括两部分的内容 Dictionary Cache 该部分主要存放系统数据字典的信息 包括 用户名 表及索引信息 表 空间信息等 也就是说 该部分存贮 ORACLE 的系统的元数据信息 Library Cache 该部分存贮被系统执行过的 SQL 语句 存贮过程 JAVA 存贮过程等的可 执行信息 系统接收到一个新的 SQL 语句时 首先从该 cache 中查找相匹配的 语句 如果查找成功 则使用现成的执行计划 而不需要重新解析 即软解析 如果查找不成功 则系统须重新对该语句进行解析并将解析结果存到 library cache 中 即硬解析 c 如何提高软解析的命中率如何提高软解析的命中率 数据库系统在安装好后 其默认的方式是强匹配 即需要从 library cache 中 查找与当前 SQL 完全相同的语句后才能执行软解析 如以下的语句在默认设置 方式下都不能进行软解析 SELECT employee id FROM employees WHERE department id 10 SELECT employee id FROM employees WHERE department id 20 SELECT employee id FROM employees WHERE department id id select employee id FROM employees WHERE department id 20 为了提高软解析的命中率 一来可以增加 share pool 的大小 但最重要的是 需要应用程序中的 SQL 使用绑定变更的方式 而且变更的名子也需要一样 在应用程序已开发好的情况下 更改应用将变得异常因难 ORACLE 提供 了一个参数 用于模糊方式的匹配 CURSOR SHARING SIMILAR EXACT FORCE 在默认情况下为 EXACT d 确定确定 SHARE POOL 的大小的大小 I 了解当前了解当前 Library Cache 的命中率的命中率 SELECT namespace pins pinhits reloads invalidations round pinhits pins 100 2 Library Cache Hit Ratio FROM V LIBRARYCACHE where pinhits 0 ORDER BY namespace NAMESPACEPINSPINHITSRELOADSINVALIDATIONSLibrary Cache Hit Ratio BODY126950075 4 CLUSTER8708560098 39 INDEX240923040095 64 SQL AREA12249411988816120497 87 TABLE PROCEDURE167541478411088 24 TRIGGER2952900098 31 II 了解当前了解当前 Dictionary Cache 的命中率的命中率 SELECT parameter sum gets sum getmisses round 100 sum gets getmisses sum gets 2 pct succ gets sum modifications updates FROM V ROWCACHE WHERE gets 0 GROUP BY parameter PARAMETERSUM GETS SUM GETMISSES PCT SUCC GETSUPDATES dc constraints69634949 86696 dc database links257199 611 dc files141121 433 dc global oids8362 50 dc histogram defs49619660 480 dc object ids928366792 81188 dc objects913294889 62362 dc profiles75198 670 dc rollback segments15321199 2821 dc segments741666491 05192 dc sequences75187675 dc tablespace quotas1121586 61100 dc tablespaces35131399 632 dc user grants82772099 760 dc usernames28021399 542 dc users101282199 793 III 从从 V SHARED POOL ADVICE 中获取中获取 share pool size 的建的建 议议 select shared pool size for estimate shared pool size factor estd lc size estd lc memory objects estd lc time saved estd lc time saved factor estd lc memory object hits from V SHARED POOL ADVICE SHARED POOL S IZE FOR ESTIM ATE SHARED POOL S IZE FACTORESTD LC SIZE ESTD LC MEM ORY OBJECTS ESTD LC TIM E SAVED ESTD LC TIME SAVED FACTOR ESTD LC MEMORY OBJE CT HITS 1600 5556411296549731596146 1920 6667411296549731596146 2240 7778411296549731596146 2560 8889411296549731596146 2881411296549731596146 3201 1111411296549731596146 3521 2222411296549731596146 3841 3333411296549731596146 4161 4444411296549731596146 4481 5556411296549731596146 4801 6667411296549731596146 5121 7778411296549731596146 5441 8889411296549731596146 5762411296549731596146 注 从上表的统计来看 可以减少其 share pool size 原因是该数据是从测 试机上获取而来 可以看出 其应用并不活跃 7 设置设置 Redo Log Buffer I 了解了解 REDO LOG BUFFER Redo Log Buffer 用于暂时存贮变化的数据内容 并在适当的时机由 LGWR 进程将其永久保存于 REDO LOG 文件中 也就是说 变化的数据先存贮于 redo log buffer 的内存块中 在以下三种情况下 LGWR 进程将该 buffer 中的数 据写入 redo log 文件中 Redo Log Buffer 中的数据达到 1 3 充满度 系统进行了 COMMIT 或 ROLLBACK 的操作 DBWR 进程触发了 LGWR 的写 REDO LOG 文件操作 其于此 在理想的情况下 应用的操作应尽量 使用批量的 COMMIT 或 ROLLBACK 操作 这样能够有效减少写磁盘 动作 在进行大数据量的载入或删除时 NOLOGGING 方式操作 注意 如果仅简单地修改一个表的定义 alter table mytab nologging 则使用 insert 方式时 系统还是写 redo log 的 除非使用并行 insert 或使用 sql loader 等方式 II 检查检查 REDO LOG BUFFER 是否存在分配的竞争是否存在分配的竞争 如果发生重做 REDO LOG BUFFER 竞争 对数据库的性能影响也将是很大 的 为了减少重做日志缓冲区竞争 可以通过查询 V sysstat 表判定 redo log 文 件缓冲区是否足够 SELECT name value FROM V sysstat WHERE name redo log space requests 此处 value 的值应接近于 0 否则 应增大初始化参数文件的 Log buffers 的 值 III 设置设置 REDO LOG BUFFER 的大小的大小 能常情况下 REDO LOG BUFFER 的大小设置成 1M 即能满足要求 可以 在项目实施时先设置成 1M 然后按上节中所述检查是否存在 REDO LOG BUFFER 的况争 8 设置设置 PGA 内存内存 I 概念概念 PGA 是指 ORACLE 服务器上的用户进程所需的内存 该部分的内存是在 SGA 以外 有以下几中操作将会增加对 PGA 的需求 排序操作 如 GROUP BY UNIQUE CREATE INDEX 等 HASH JOIN 操作 BITMAP MERGE BITMAP CREATE 在 ORACLE 9i 以前 需要根据不同的操作来调整不同部分的参数 如执行 SORT 操作 则可能需要增加 sort area size 参数 对于 ORACLE 9i 只要调整 好一个参数即可 pga aggregate target ORACLE 会根据不同的操作自动地分 配不同部分的内存 II 检查当前系统中检查当前系统中 PGA 是否够用是否够用 能过查询系统字典表 V PGASTAT 来了解当前系统中的 PGA 内存使用情况 SELECT FROM V PGASTAT NAMEVALUEUNIT 说明 aggregate PGA target parameter25165824bytes pga aggregate target 参数大小 aggregate PGA auto target14699520bytes 当前系统中可用的 PGA 大小 global memory bound1257472bytes total PGA inuse9293824bytes total PGA allocated20218880bytes 当前系统各用户 session 已分配的 PGA maximum PGA allocated44770304bytes total freeable PGA memory65536bytes PGA memory freed back to OS663224320bytes total PGA used for auto workareas0bytes maximum PGA used for auto workareas7434240bytes total PGA used for manual workareas0bytes maximum PGA used for manual workareas536576bytes over allocation count0 PGA 实际分配量超出 pga aggregate target 参数据大小 的次数 bytes processed2512636928bytes extra bytes read written4936576000bytes cache hit percentage33 73percent III 调整调整 PGA 大小大小 ORACLE 提供动态视图来帮助确定 PAG 的调整 v pga target advice 可 以能过以下语句来获得建议 SELECT round PGA TARGET FOR ESTIMATE 1024 1024 target mb ESTD PGA CACHE HIT PERCENTAGE cache hit perc ESTD OVERALLOC COUNT FROM v pga target advice 其查询结果示例如下 TARGET MBCACHE HIT PERCESTD OVERALLOC COUNT 1229104 183113 24430 29430 34430 38430 43440 48440 72470 96480 144650 192660 统计图如下 CACHE HIT PERC 0 10 20 30 40 50 60 70 12182429343843487296144192 CACHE HIT PERC pga aggregate target参数大小 由上图可见 如果将系统的 pga aggregate target 参数调整到 144MB 时 将 可以大大提高 PGA 的命中率 注意 就象上图所统计的那样 如果 PGA 的命中率较底 则说明该系统存在较大的外部 排序等操作 这将严重影响 SESSION 的响应及性能 三 三 IO 调优调优 四 四 UNDO TABLESPACE 管理管理 五 五 TEMP 表空间管理表空间管理 六 六 SQL 调优调优 七 七 分区分区 分区原则 1 表分区的指南 a 表的大小 对于大表进行分区 将有益于大表操作的性能和大表的数据维护 通常当表的大小超过 1 5GB 2GB 或对于 OLTP 系统 表的记录超过 1000 万 都应考虑对表进行分区 b 数据访问特性 基于表的大部分查询应用 只访问表中少量的数据 对于这样表进行分区 可充分利用分 区排除无关数据查询的特性 c 数据维护 某些表的数据维护 经常按时间段删除成批的数据 例如按月删除历史数据 对于这样的 表需要考虑进行分区 以满足维护的需要 因为删除 Delete 大量的数据 对系统开销 很大 有时甚至是不可接受的 d 只读数据 如果一个表中大部分数据都是只读数据 通过对表进行分区 可将只读数据存储在只读表 空间中 对于数据库的备份是非常有益的 e 并行数据操作 Parallel DML 对于经常执行并行操作 如 Parallel Insert Parallel Update 等 的表应考虑进行分区 f 表的可用性 当对表的部分数据可用性要求很高时 应考虑进行表分区 此文从以下几个方面来整理关于分区表的概念及操作 1 表空间及分区表的概念 2 表分区的具体作用 3 表分区的优缺点 4 表分区的几种类型及操作方法 5 对表分区的维护性操作 1 表空间及分区表的概念 表空间 是一个或多个数据文件的集合 所有的数据对象都存放在指定的表空间中 但主要存 放的是表 所以称作表空间 分区表 当表中的数据量不断增大 查询数据的速度就会变慢 应用程序的性能就会下降 这时就 应该考虑对表进行分区 表进行分区后 逻辑上表仍然是一张完整的表 只是将表中的数 据在物理上存放到多个表空间 物理文件上 这样查询数据时 不至于每次都扫描整张表 2 表分区的具体作用 Oracle 的表分区功能通过改善可管理性 性能和可用性 从而为各式应用程序带来了极大 的好处 通常 分区可以使某些查询以及维护操作的性能大大提高 此外 分区还可以极大 简化常见的管理任务 分区是构建千兆字节数据系统或超高可用性系统的关键工具 分区功能能够将表 索引或索引组织表进一步细分为段 这些数据库对象的段叫做分区 每个分区有自己的名称 还可以选择自己的存储特性 从数据库管理员的角度来看 一个 分区后的对象具有多个段 这些段既可进行集体管理 也可单独管理 这就使数据库管理 员在管理分区后的对象时有相当大的灵活性 但是 从应用程序的角度来看 分区后的表 与非分区表完全相同 使用 SQL DML 命令访问分区后的表时 无需任何修改 什么时候使用分区表 1 表的大小超过 2GB 2 表中包含历史数据 新的数据被增加都新的分区中 3 表分区的优缺点 表分区有以下优点 1 改善查询性能 对分区对象的查询可以仅搜索自己关心的分区 提高检索速度 2 增强可用性 如果表的某个分区出现故障 表在其他分区的数据仍然可用 3 维护方便 如果表的某个分区出现故障 需要修复数据 只修复该分区即可 4 均衡 I O 可以把不同的分区映射到磁盘以平衡 I O 改善整个系统性能 缺点 分区表相关 已经存在的表没有方法可以直接转化为分区表 不过 Oracle 提供了在线重 定义表的功能 4 表分区的几种类型及操作方法 一 范围分区 范围分区将数据基于范围映射到每一个分区 这个范围是你在创建分区时指定的分区键决 定的 这种分区方式是最为常用的 并且分区键经常采用日期 举个例子 你可能会将销 售数据按照月份进行分区 当使用范围分区时 请考虑以下几个规则 1 每一个分区都必须有一个 VALUES LESS THEN 子句 它指定了一个不包括在该分区中的 上限值 分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分 区中 2 所有分区 除了第一个 都会有一个隐式的下限值 这个值就是此分区的前一个分区的 上限值 3 在最高的分区中 MAXVALUE 被定义 MAXVALUE 代表了一个不确定的值 这个值高于其 它分区中的任何分区键的值 也可以理解为高于任何分区中指定的 VALUE LESS THEN 的值 同时包括空值 例一 假设有一个 CUSTOMER 表 表中有数据 200000 行 我们将此表通过 CUSTOMER ID 进行分区 每个分区存储 100000 行 我们将每个分区保存到单独的表空间中 这样数据文件就可以跨 越多个物理磁盘 下面是创建表和分区的代码 如下 CREATE TABLE CUSTOMER CUSTOMER ID NUMBER NOT NULL PRIMARY KEY FIRST NAME VARCHAR2 30 NOT NULL LAST NAME VARCHAR2 30 NOT NULL PHONE VARCHAR2 15 NOT NULL EMAIL VARCHAR2 80 STATUS CHAR 1 PARTITION BY RANGE CUSTOMER ID PARTITION CUS PART1 VALUES LESS THAN 100000 TABLESPACE CUS TS01 PARTITION CUS PART2 VALUES LESS THAN 200000 TABLESPACE CUS TS02 例二 按时间划分 CREATE TABLE ORDER ACTIVITIES ORDER ID NUMBER 7 NOT NULL ORDER DATE DATE TOTAL AMOUNT NUMBER CUSTOTMER ID NUMBER 7 PAID CHAR 1 PARTITION BY RANGE ORDER DATE PARTITION ORD ACT PART01 VALUES LESS THAN TO DATE 01 MAY 2003 DD MON YYYY TABLESPACEORD TS01 PARTITION ORD ACT PART02 VALUES LESS THAN TO DATE 01 JUN 2003 DD MON YYYY TABLESPACE ORD TS02 PARTITION ORD ACT PART02 VALUES LESS THAN TO DATE 01 JUL 2003 DD MON YYYY TABLESPACE ORD TS03 例三 MAXVALUE CREATE TABLE RangeTable idd INT PRIMARY KEY iNAME VARCHAR 10 grade INT PARTITION BY RANGE grade PARTITION part1 VALUES LESS THEN 1000 TABLESPACE Part1 tb PARTITION part2 VALUES LESS THEN MAXVALUE TABLESPACE Part2 tb 二 列表分区 该分区的特点是某列的值只有几个 基于这样的特点我们可以采用列表分区 例一 CREATE TABLE PROBLEM TICKETS PROBLEM ID NUMBER 7 NOT NULL PRIMARY KEY DESCRIPTION VARCHAR2 2000 CUSTOMER ID NUMBER 7 NOT NULL DATE ENTERED DATE NOT NULL STATUS VARCHAR2 20 PARTITION BY LIST STATUS PARTITION PROB ACTIVE VALUES ACTIVE TABLESPACE PROB TS01 PARTITION PROB INACTIVE VALUES INACTIVE TABLESPACE PROB TS02 例二 CREATE TABLE ListTable id INT PRIMARY KEY name VARCHAR 20 area VARCHAR 10 PARTITION BY LIST area PARTITION part1 VALUES guangdong beijing TABLESPACE Part1 tb PARTITION part2 VALUES shanghai nanjing TABLESPACE Part2 tb 三 散列分区 这类分区是在列值上使用散列算法 以确定将行放入哪个分区中 当列的值没有合适的条 件时 建议使用散列分区 散列分区为通过指定分区编号来均匀分布数据的一种分区类型 因为通过在 I O 设备上进 行散列分区 使得这些分区大小一致 例一 CREATE TABLE HASH TABLE COL NUMBER 8 INF VARCHAR2 100 PARTITION BY HASH COL PARTITION PART01 TABLESPACE HASH TS01 PARTITION PART02 TABLESPACE HASH TS02 PARTITION PART03 TABLESPACE HASH TS03 简写 CREATE TABLE emp empno NUMBER 4 ename VARCHAR2 30 sal NUMBER PARTITION BY HASH empno PARTITIONS 8 STORE IN emp1 emp2 emp3 emp4 emp5 emp6 emp7 emp8 hash 分区最主要的机制是根据 hash 算法来计算具体某条纪录应该插入到哪个分区中 hash 算法中最重要的是 hash 函数 Oracle 中如果你要使用 hash 分区 只需指定分区的数量即 可 建议分区的数量采用 2 的 n 次方 这样可以使得各个分区间数据分布更加均匀 四 组合范围散列分区 这种分区是基于范围分区和列表分区 表首先按某列进行范围分区 然后再按某列进行列 表分区 分区之中的分区被称为子分区 CREATE TABLE SALES PRODUCT ID VARCHAR2 5 SALES DATE DATE SALES COST NUMBER 10 STATUS VARCHAR2 20 PARTITION BY RANGE SALES DATE SUBPARTITION BY LIST STATUS PARTITION P1 VALUES LESS THAN TO DATE 2003 01 01 YYYY MM DD TABLESPACE rptfact2009 SUBPARTITION P1SUB1 VALUES ACTIVE TABLESPACE rptfact2009 SUBPARTITION P1SUB2 VALUES INACTIVE TABLESPACE rptfact2009 PARTITION P2 VALUES LESS THAN TO DATE 2003 03 01 YYYY MM DD TABLESPACE rptfact2009 SUBPARTITION P2SUB1 VALUES ACTIVE TABLESPACE rptfact2009 SUBPARTITION P2SUB2 VALUES INACTIVE TABLESPACE rptfact2009 五 复合范围散列分区 这种分区是基于范围分区和散列分区 表首先按某列进行范围分区 然后再按某列进行散 列分区 create table dinya test transaction id number primary key item id number 8 not null item description varchar2 300 transaction date date partition by range transaction date subpartition by hash transaction id subpartitions 3 store in dinya space01 dinya space02 dinya space03 partition part 01 values less than to date 2006 01 01 yyyy mm dd partition part 02 values less than to date 2010 01 01 yyyy mm dd partition part 03 values less than maxvalue 5 有关表分区的一些维护性操作 一 添加分区 以下代码给 SALES 表添加了一个 P3 分区 ALTER TABLE SALES