数据库优化之高效率调优oracle亿级别表
2017年在省公司做一个项目，涉及到一个亿级别的大表操作，过程中遇到了很多坑，走过后记录如下，方便今后回忆。
Oracle数据库是一种事务性数据库，对删除、修改、新增操作会产生undo和redo两种日志，当一次提交的数据量过大时，数据库会产生大量的日志写文件IO操作，导致数据库操作性能下降，尤其是对一张记录过亿的表格进行操作时需要注意以下事项：
1、操作大表必须知道表有多大
select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_name=upper('ipdetail');
通过这条命令可以在大表导入及使用时实时查询进度和该表所占的实际存储空间。
知道一个用户下的所有表各占多大存储空间
select 'select sum(bytes)/(1024*1024) as "size(M)" from user_segments where segment_na
me=upper('''||tname||''')' from tab;
2、大于1000w记录的表格最好分区或分表处理，如果分表涉及的业务调整量太大，可以先考虑分区保存
比如可以按照IP数字的范围大概将每2000万条记录放入一个分区表空间中进行保存。
create table testipdetail (ipadd varchar2(20),numip number,isp varchar2(255),country varchar2(255),province varchar2(255),city varchar2(255),used varchar2(255),create_date date default sysdate,creater_id number,sts number)
partition by range (numip)
(partition ip1 values less than (1000000000),
partition ip2 values less than (1930000000),
partition ip3 values less than (2285000000),
partition ip4 values less than (3080000000),
partition ipmax values less than (3774873599)
)
通过分区处理后，9G的表数据基本均匀的存储在5个表空间中，经过测试大表分区后比表保存在单一分区上的insert效率高40%，但是update效率没有显著提升。
3、尽量不要使用全表update操作
如果有对一个字段进行整体修改的计划，建议使用新建表格的方式。
--新建一张和待修改表格一样表结构的新表
create table t2 as select * from ipdetail where 1=2
--修改表格的属性为不归档模式，实际效果不大
alter table t2 nologging;
--已不记录日志的形式进行批量插入/*+ APPEND */
insert /*+ APPEND */ into T2 select ipadd,numip,isp,country,province,city,used,create_date,1,0 from ipdetail
--插入结束后一定要记得提交，否则查询会报错
Commit
4、大表update数据前先建索引
比如下面一条语句通过先建好t2表格的numip字段索引以及hbipdetail表格的numip字段索引，通过引入并行操作机制，这条涉及530多万行的更新语句只需要195秒执行完毕。
update /*+parallel(t2,4)*/ t2 i set city=(select /*+parallel(hbipdetail,4)*/ city from hbipdetail h where i.numip=h.numip and rownum=1),sts=1 where i.province='湖北'
--执行完后必须先提交，否则报错。
Commit
5、更新语句加入rownum数量限制，切分一次更新的数量集
这种方法首先需要编程实现，将更新总量除以每次更新的数据量集，做循环打印日志，这种处理方案的好处是可以知道更新的具体进度，但是对于提高处理效率没有帮助。
更新500000条，共更新500000条记录！2017-10-14 13:05:28
更新500000条，共更新1000000条记录！2017-10-14 13:05:44
更新500000条，共更新1500000条记录！2017-10-14 13:06:00
更新500000条，共更新2000000条记录！2017-10-14 13:06:15
更新500000条，共更新2500000条记录！2017-10-14 13:06:29
更新500000条，共更新3000000条记录！2017-10-14 13:06:43
更新500000条，共更新3500000条记录！2017-10-14 13:06:58
无论如何调整每次更新的数据量，时间的变化总是线性关系
6、大表操作表锁死后如何处理
首先查出锁死的具体进程，主要关注删除号
SELECT dob.OBJECT_NAME Table_Name,lo.SESSION_ID||', '||vss.SERIAL# 删除号,
lo.locked_mode,lo.SESSION_ID, vss.SERIAL#,vss.action Action,vss.osuser OSUSER, vss.LOGON_TIME,
oracle选择数据库vss.process AP_PID, VPS.SPID DB_PID ,vss.*
From v$locked_object lo, dba_objects dob, v$session vss, V$PROCESS VPS
Where lo.OBJECT_ID = dob.OBJECT_ID
and lo.SESSION_ID = vss.SID
AND VSS.paddr = VPS.addr
order by 2,3,DOB.object_name
删除锁死进程
ALTER system kill session ‘sessionid,serialid'
总结：
对大表操作的原则是：
1、索引和表不建议建在同一个表空间；
2、表本身最好通过业务规则分布保存在多个不同的表空间中，提高并发访问效率；
3、insert操作前先将表设置为nologging状态，语句中注意添加/*+ APPEND */标识符；
4、update操作尽量不要做全表，如果非要做全表不如转为insert操作，效率提高十几倍；
5、加入并行操作符可以小量提升update性能，/*+parallel(4)*/，操作符中的数值代表使用cpu线程数；
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论