博客访问： 45142
博文数量： 21
注册时间：
幸福快乐的IT小菜逼
ITPUB论坛APP
ITPUB论坛APP
APP发帖 享双倍积分
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Oracle
客户要求我协助清理不要的历史数据，软件开发商提供了两条sql如下
delete from t_aging_analysis_history where fk_organization_id in (select pk_id from t_organization where organization_id like '114%')
delete from t_format_item_value_history where fk_organization_id in (select pk_id from t_organization where organization_id like '114%')
客户先是自己用plsql developer去执行，由于2条sql平均每条删除1.8亿条数据，undo表空间难以支持，而且事务过大，删除速度很慢，客户的操作以失败告终。
之前自己研究过这种场景的处理方法。
1.我先暂停了要大量删除数据的表的日志记录
alter table t_aging_analysis_history nologging;
2.参考网上的脚本，写自己的pl/sql
cursor mycursor is select ROWID from t_aging_analysis_history where fk_organization_id in (select pk_id from t_organization where organization_id like \'114%\')
type rowid_table_type is
of rowid index by pls_
rowid_table_
mycursor bulk collect into v_rowid
limit 1000;
exit when v_rowid.count=0;
forall i in v_rowid.first..v_rowid.last
delete from t_aging_analysis_history
where rowid=v_rowid(i);
打开一个终端去执行，虽然拆分成多个事务，1000行提交一次，但是感觉速度很慢。
3.尝试使用parallel
SQL& ALTER TABLE t_aging_analysis_history PARALLEL(3);
ALTER TABLE t_aging_analysis_history PARALLEL(3)
ERROR at line 1:
ORA-12818: invalid option in PARALLEL clause
SQL& alter session
Session altered.
开启parallel的语句，当时没有记住，我让取消的存储过程继续执行，然后去翻官方文档。这时parallel没有真正的开启。
4.在官方文档发现如下内容，吓一跳
Parallel DML operations are not performed when the table lock of the target table is disabled.
字面上看，开启parallel是要获得表级锁，那么其他session是不是就不能做dml了呢？这个系统虽然业务量不大，可以白天清理，但是业务不能停啊。
5.是时候开启我的虚拟机了，使用了自制的test表，这个表的数据都是来源于emp表
第一个终端下：
SQL& alter table test parallel
Table altered.
SQL& alter session
Session altered.
SQL& delete test where empno=7900;
1 row deleted.
第二个终端下：
SQL& delete test where empno=7902;
此时第二个终端会卡住。
开启第三个终端，看看：
SQL& select distinct BLOCKING_SESSION from v$session where blocking_
BLOCKING_SESSION
----------------
真的有锁，开并行的session把test表整个锁住了，其他的session不能正常做dml了。
6.实验做完了，parallel最终没开，数据清理了，表还要开启日志记录的
alter&table&t_aging_analysis_history logging;
尝试了一把没用过的，差点影响业务。
骚年们，是时候查查官方文档了。
阅读(1770) | 评论(0) | 转发(0) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。当SqlServer数据量很大时,如何优化表格能加快处理速度_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
当SqlServer数据量很大时,如何优化表格能加快处理速度
上传于|0|0|文档简介
&&当SqlServer数据量很大时,如何优化表格能加快处理速度
阅读已结束，如果下载本文需要使用0下载券
想免费下载更多文档？
定制HR最喜欢的简历
你可能喜欢15335人阅读
oracle相关（53）
如果业务无法停止的话，主要有这三种思路：
=======================================================================================================
思路1：根据ROWID分片、再利用Rowid排序、批量处理、回表删除。
& && &&&在业务无法停止的时候，选择这种方式，的确是最好的。一般可以控制在每一万行以内提交一次，不会对回滚段造成太大压力（我在做大DML时，通常选
择一两千行一提交）。选择业务低峰时做，对应用也不至于有太大影响。
& && &&&感谢htyansp，在49楼提供了一个很简捷的脚本，如果你对这种方式不熟悉，可以参考此脚本：
& &cursor mycursor is SELECT&&ROWID FROM TEST WHERE&&XXX=XXXX&&& &&--------按ROWID排序的Cursor，删除条件是XXX=XXXX，根据实际情
& &type rowid_table_type is&&table&&of rowid index by pls_
& &v_rowid& &rowid_table_
& &&&fetch& &mycursor bulk collect into v_rowid&&limit 5000;& &&--------每次处理5000行，也就是每5000行一提交
& &&&exit when v_rowid.count=0;
& &&&forall i in v_rowid.first..v_rowid.last
& && &&&delete from test&&where rowid=v_rowid(i);
& && &&&这种方法的缺点是排序有可能会消耗太多临时表空间。还有一种方式，先根据Rowid分片。将一个大表用Rowid划分成多个部分，每部分单独根据Rowid排
序。这种方式的另一个优点就是还可以并行。
& && &&&有一次我需要删除DW库一个大表中满足条件的行。应用方保证不会再出现此条件的行，我只需要在几天内，将所有满足条件的行删除完即可。此表所在
的表空间有几十个数据文件（每个文件32G），我用如下的命令生成表在每个文件中行的ROWID范围：
select dbms_rowid.ROWID_CREATE(1,12227,file_id,MIN(BLOCK_ID),0),dbms_rowid.ROWID_CREATE(1,12227,file_id,MAX(BLOCK_ID+BLOCKS-1),8192) from
dba_extents where segment_name='DML_TST' group by file_id order by file_
此命令中DATA_OID是dba_objects 中data_object_id列值。
& && &&&然后，根据上面得到的ROWID范围操作目标表。其实就是将htyansp的存储过程中第二行，根据生成的ROWID修改如下：
& && &&&cursor mycursor is SELECT&&ROWID FROM TEST WHERE&&rowid between 'ROWID' and&&'ROWID' and XXX=XXXX
& && &&&存储过程其他行基本不变。
& && &&&搞几十个这样的存储过程，开几个会话并行着跑。
& && &&&另外，TOM在9i&10G编程艺术 648页到652页有一个很好的例子，其中650页自动生成ROWID部分，可以参考。
& && &&&使用这种方式最大的优点就是性能可控，需要快点的话，可以多设几个并行。想慢点的，并行就少点。而且，一次处理的行数有限，对ROWID的排序不会
撑爆临时表空间。
案例参考：
=======================================================================================================
思路二：根据ROWID分片、非批量处理、回表删除
& && &&&比如，要删除dml_tst中ID等于Value的行，最基本的存储过程如下：
& && &&&CURSOR test2_cs(value number,rid1 rowid,rid2 rowid)
& && && & IS SELECT id from dml_tst
& && && & where id=value and rowid between rid1 and&&rid2
& && && & FOR UPDATE ;
& && &&&k number:=0;
& &FOR c1_rec IN test2_cs(3338,'AAAC/DAAEAAAABJAAA','AAAC/DAAEAAAABQCAA') LOOP
& && && && &delete dml_tst where CURRENT OF test2_
& &END LOOP;
& && &&&这种方式也可以根据ROWID分片，只会对表进行一次扫描。但没有批量处理，性能反而不如上面。
=======================================================================================================
思路三： ON PREBUILT物化视图方法
这种方式，阿里迁移数据的确使用较多，也是一种不错的方式。速度没有方法一快，但比较简单，而且对业务基本上没有影响。另外，对于删除操作，可以释放
删除过的空间。缺点就是需要有主键。
假设目标表是P3，主键列是ID1，要删除ID2列于小1000的行:
步1，建立中间表p3_m：
create table p3_m as select * from p3 where 0=1;
步2，建产和中间表同名的物化视图，一定要有ON PREBUILT选项：
CREATE MATERIALIZED VIEW p3_m
ON PREBUILT TABLE AS
select * from p3 where id2&=1000;&&&--------将不满足删除条件的行放入物化视图
步3：添加物化视图日志：
CREATE MATERIALIZED VIEW LOG ON p3 WITH PRIMARY KEY,sequence (id2,id3,cc1,cc2) INCLUDING NEW VALUES;
步4：在数据库空闲的时候，进行一次完全刷新：
exec dbms_mview.refresh('P3_M','C');
完全刷新后，可以在中间表上创建和目标表一样的索引、约束等等
步5：进行个一、两次增量刷新：
exec dbms_mview.refresh('P3_M','F');
步6：将原表锁住，最后进行一次增量刷新，然后马上Rename目标表为其他名字
lock table p3 in EXCLUSIVE
exec dbms_mview.refresh('P3_M','F');
drop MATERIALIZED VIEW LOG ON p3;
alter table p3 rename to p3_n;
步7：删除物化视图，修改中间表为原目标表的名字：
drop MATERIALIZED VIEW p3_m;
alter table p3_m rename to p3;
步8：确定原表如果没有用了，可以删除改过名的原表
也可以使用再线重定义，思路和这个类似。
在线重定义案件：
======================================================================================
& && &&&如果不影响应用的话，常规方法也就这些了。这三种思路，也可以用于Update。
& && &&&根据这三种思路，我们可以结合自身应用情况加以改变。总能找到一款适合应用的方法。
& && &&&注意事项是
& && &&&1、注意备份
& && &&&2、千万注意不要太猛，曾经有一次同事因为Update的太猛，影响了我们一个重要的前台应用。一定要注意，一次提交的行数不能太高。
& && &&&如果应用可以停，哪方法就太多了。
1、CTAS的方法创建一个新表，排除要DELETE的数据，再改名。为提高速度，还可以禁用索引，DML完再重建。
2、只导出不删除的数据，再导入，再改名
3、如果表空间可以设为只读，还有坛友的表空间迁移，迁到测试平台，慢慢删除再导入回来的方法
等等。可以停应用的方法就很多了。
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：202845次
积分：2470
积分：2470
排名：第12633名
原创：46篇
转载：58篇
(1)(1)(1)(1)(1)(1)(1)(2)(1)(7)(3)(3)(2)(2)(1)(2)(4)(4)(2)(5)(1)(9)(5)(1)(7)(2)(6)(4)(4)(5)(1)(1)(2)(4)(7)关于删除表数据时速度慢的测试_oracle数据库_ThinkSAAS
关于删除表数据时速度慢的测试
关于删除表数据时速度慢的测试
内容来源： 网络
关于删除表数据时速度慢的测试
最近删除的时候无聊掐了一下手表，发现删除数据远远没有查询的时候速度快。于是乎，便做了下实验，实验内容如下，水平有限，希望能起到抛砖引玉的作用让让这个问题尽可能的搞明白！希望各位前辈能明确、无情的指正小弟操作不合理和思路不正确的地方。
发现delete的速度不如select的速度快。
导致delete速度慢的原因有很多，比如：
1.写大量回滚段，在RAID5上，写回滚段速度相对更慢，因为需要写校验位。
2.外键影响
3.在线用户访问过多，可以查看v$session_wait
4.没有表分区
基础环境建设：
创建tom用户的表空间：
create tablespace tom datafile &/opt/ora10g/oradata/jssbook/jssbook/tom01.dbf&
autoextend on
next 32m maxsize 2048m
确定创建成功：
select name from v$datafile where name like &%tom%&
创建tom用户：
create user tom identified by "jackson"
default tablespace tom
profile default
给用户授权：
grant dba to tom
查看临时表空间：
select * from v$tempfile
创建时没有声明，则用默认表空间。
查看test表大小：
select segment_name, bytes
from user_segments
where segment_type = &TABLE&;
查看test数据量：
select count(*) from test
向test表中注入数据：（该语句执行了N遍，不停地刷，不停地刷，最后达到150万条数据之后进行下一步---delete）
insert /* +append */ into
select * from all_objects
删除表中数据：
下面是监控语句，这些语句分别在刚注入150万条数据之后执行了一下，抓了下状态，然后又在执行过程中抓了一下。（只抓取相应变化比较大的字段）
1.查看回滚段信息（tom用户使用）
select * from V$ROLLSTAT
删除之前信息：
删除之后信息：
_SYSSMU10$
2.查看回滚段的统计信息：
SELECT n.name, s.extents, s.rssize, s.optsize, s.hwmsize, s.xacts, s.status
　　FROM v$rollname n, v$rollstat s
　　WHERE n.usn = s.
删除之前信息：
_SYSSMU10$
删除之后信息：
_SYSSMU10$
3.查看回滚段用户征用情况：
SELECT s.username, u.name
　　FROM v$transaction t, v$rollstat r, v$rollname u, v$session s
　　WHERE s.taddr = t.addr
　　AND t.xidusn = r.usn
　　AND r.usn = u.usn
　　ORDER BY s.
只有一个用户使用回滚段：
结论：用delete删除确实产生了大量回滚段，是造成delete速度慢的因素之一。
作者 jackson198574
PHP开发框架
开发工具/编程工具
服务器环境
ThinkSAAS商业授权：
ThinkSAAS为用户提供有偿个性定制开发服务
ThinkSAAS将为商业授权用户提供二次开发指导和技术支持
让ThinkSAAS更好，把建议拿来。
开发客服微信