oracle 优化方案小记

1. 目前状况

1.1 表空间未合理规划，导致所有的用户下的所有表都创建在默认的表空间下

oracle 使用过程中未针对特定数据表进行特定的表空间规划，导致目前实例中所有的数据库表都存储中默认的表空间文件(dbf)中，从目前高峰时的性能监测可以看出，oracle 数据所在磁盘使用率为 100%，而其他盘空空如也也没有任何 IO 请求，导致了资源浪费。(涝得涝死，旱得旱死)

1.2 存在 IO 需求量大的查询在高峰期执行

从 oracke 自带的性能监测程序统计分析得到在早上 7点至 10 点为查询量高峰时期，而此时有部分 sql 查询带来了大量的 IO 读取。比如 select * 不添加任何分页功能（猜测是数据同步），因此需要针对 oracle 的性能分析来排出 io 量大的查询并尽量修改到非高峰期执行。

2. 优化方案

2.1 提高磁盘利用率

2.1.1 RAID / LVM

使用 raid 或 LVM 方案都可以将多个磁盘合并为一个磁盘来使用，而相关的 IO 请求都会根据特定逻辑最终都会分散到多个磁盘上来操作从而提高整体的 IO。

http://www.itpub.net/thread-787910-2-1.html 这个帖子说了 LVM 几乎不影响 IO, 反而因为多磁盘的加入使得 IO 能提高。

2.1.2 ORACLE 自带表空间拓展

这个博客 列出了四种增加表空间大小的方法，其中一个：

ALTER TABLESPACE app_data ADD DATAFILE
'put dbf file path here' SIZE 500G
AUTOEXTEND ON NEXT 1G MAXSIZE 2048G;

上面说的这种情况就是在其他磁盘创建一个 dbf 文件来直接拓展当前的表空间大小，但是拓展后 oracle 是否会自动进行数据均衡等细节还需要参考官方文档：

oracle dba doc

2.2 解决当前的磁盘热点现象

如果能完美解决 2.1 所说可能当前问题也会被顺便解决。目前状态是已有数据全部都在一块磁盘上，所有的查询 IO 都还是走这一块磁盘，oracle 的表空间自动拓展是否能自动均衡数据呢？如果不能，怎么解决当前的查询压力都在这一块磁盘之上的问题。

2.2.1 partition 迁移

oracle partition 迁移 这篇博客讲了如何将一个指定的 oracle 分区迁移到其他表空间中，参考此文章可以尝试在其他磁盘上创建对应的表空间然后将分区迁移至该表空间中来分散数据。大体操作：

ALTER TABLE [tableName] MOVE PARTITION [partitionId] TABLESPACE [tableSpaceName];

ORACLE: Moving Table Partitions

2.2.2 直接整表迁移

如果想把整个表都迁移过去，那就可以这样操作

-- 迁移
alter table [tableName] move tablespace [spaceName]
-- 重建索引
alter index [index_name] rebuild tablespace [tablespace_name]

3. 综上所述

决定采用 oracle 自带的 move table 来进行数据迁移。

3.1 具体操作

3.1.1 使用 LVM 或 raid 整合多块空闲磁盘为单块磁盘

LVM:

pvcreate /dev/sda{1,2,3,4}
vgcreate ORCL /dev/sda{1}
vgextend ORCL /dev/sda{2,3,4}
lvcreate ...

3.1.2 创建表空间

语法

-- 创建
create tablespace test_user datafile 'f:\test_user\zzg_data.dbf' size 200M;
-- 授权
grant create session,create table,create view,create sequence,unlimited tablespace to test_user;

3.1.3 迁移数据库表(整表迁移) [带分区的不可直接迁移，需要以分区为单位进行迁移]

move table

-- 迁移
alter table [tableName] move tablespace [spaceName]
-- 重建索引
alter index [index_name] rebuild tablespace [tablespace_name];

3.1.4 以分区为单位进行迁移

ORACLE: Moving Table Partitions

批量生成迁移语句并执行

select 'alter table traffic_cloud.'||table_name||' move partition '||partition_name||' tablespace {spaceName};' from user_tab_partitions where table_name = 'ILLEGAL_DATA';

• 不要忘记改动态增加分区的相关命令，让以后添加分区的时候自动添加到新的表空间中 *