eygle.com   eygle.com
eygle.com  
 

« April 20, 2005 | Blog首页 | April 22, 2005 »


April 21, 2005

Jonathan Lewis: Can we have a sensible debate ?

作者:eygle

出处:http://blog.eygle.com

看来Don Burleson从来都没打算停止狡辩，最近Don又提出了一个 “Empirical” vs. “Research” DBAs的论题，邀请Jonathan Lewis等人参与，feel free to join the discussion,我已经不止一次见Don搬出这句话来。

中间一段非常有意思:
Jonathan说一开始Don就提出:
Remember the rule here that everyone treat the others with respect and dignity, even if you strongly disagree. . . .

可是Don却引用毫无根据的第三人言论意图说明:(Tom) Kyte had very little real-world experience

这本身就违背了他自己提出的with respect and dignity。

Don总是那么前后矛盾，死不认输。不过这些文章读起来还是有些意思的。

建议阅读:
Can we have a sensible debate ?

Posted by eygle at 4:27 PM | Comments (5)

Oracle中大批量删除数据的方法

作者:eygle

出处:http://blog.eygle.com

批量删除海量数据通常都是很复杂及缓慢的，方法也很多，但是通常的概念是:分批删除，逐次提交。
下面是我的删除过程，我的数据表可以通过主键删除，测试过Delete和For all两种方法，for all在这里并没有带来性能提高，所以仍然选择了批量直接删除。

首先创建一下过程，使用自制事务进行处理：


create or replace procedure delBigTab
(
p_TableName       in    varchar2,
p_Condition       in    varchar2,
p_Count        in    varchar2
)
as
pragma autonomous_transaction;
n_delete number:=0;
begin
while 1=1 loop
EXECUTE IMMEDIATE
'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'
USING p_Count;
if SQL%NOTFOUND then
exit;
else
n_delete:=n_delete + SQL%ROWCOUNT;
end if;
commit;
end loop;
commit;
DBMS_OUTPUT.PUT_LINE('Finished!');
DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');
end;

以下是删除过程及时间:


SQL> create or replace procedure delBigTab
  2  (
  3    p_TableName       in    varchar2,
  4    p_Condition       in    varchar2,
  5    p_Count        in    varchar2
  6  )
  7  as
  8   pragma autonomous_transaction;
  9   n_delete number:=0;
 10  begin
 11   while 1=1 loop
 12     EXECUTE IMMEDIATE
 13       'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'
 14     USING p_Count;
 15     if SQL%NOTFOUND then
 16        exit;
 17     else
 18              n_delete:=n_delete + SQL%ROWCOUNT;
 19     end if;
 20     commit;
 21   end loop;
 22   commit;
 23   DBMS_OUTPUT.PUT_LINE('Finished!');
 24   DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');
 25  end;
 26  /

Procedure created.

SQL> set timing on
SQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;

MIN(NUMDLFLOGGUID)
------------------
          11000000

Elapsed: 00:00:00.23
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11100000','10000');

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.54
SQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;

MIN(NUMDLFLOGGUID)
------------------
          11100000

Elapsed: 00:00:00.18
SQL> set serveroutput on
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11200000','10000');
Finished!
Totally 96936 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.61
10万记录大约19s

SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11300000','10000');
Finished!
Totally 100000 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.62
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11400000','10000');
Finished!
Totally 100000 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.85
SQL> 
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 13000000','10000');
Finished!
Totally 1000000 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:03:13.87

100万记录大约3分钟
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 20000000','10000');
 
Finished!
Totally 6999977 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:27:24.69
700万大约27分钟

以上过程仅供参考.

Posted by eygle at 2:15 PM | Comments (16)

Control SCN of Undo Segments

作者:eygle

出处:http://blog.eygle.com

在回滚段头有一个重要的数据结构称为:Control SCN.
这个SCN是最近一个被重用的事务槽的SCN(重用是按事务的先后顺序重用的)。如果Control SCN比查询的Snapshot SCN新，那么Oracle不会试图去构造前镜像，而是马上返回ORA-01555错误，因为这个UNDO信息肯定已经被覆盖了。
这个Control SCN也会被用于delayed logging cleanout的提交SCN(仅当历史事务的UNDO信息已经被覆盖)，在ITL中这个SCN被标记为U，代表"upper bound commit".
我们来看一下这个数据结构:
SQL> create table ud ( n number );

Table created

SQL> insert into ud values(1);

1 row inserted

SQL> insert into ud values(2);

1 row inserted

SQL> commit;

Commit complete

SQL> 
SQL> select * from ud;

         N
----------
         1
         2

SQL> update ud set n=1000 where n=2;

1 row updated

SQL> select * from ud;

         N
----------
         1
      1000

SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         2         30      11407        251          2         10

SQL> select usn,name from v$rollname where usn=2;

       USN NAME
---------- ------------------------------
         2 _SYSSMU2$

SQL> alter system dump undo header '_SYSSMU2$';

System altered
检查trace文件(摘录):
  TRN CTL:: seq: 0x02cd chd: 0x002e ctl: 0x0018 inc: 0x00000000 nfb: 0x0000
            mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x008000fb.02cd.0a scn: 0x0000.0e21169a
这里TRN CTL部分的scn就是前面我们所说的Contrl SCN.
参考:
http://www.ixora.com.au/q+a/cr.htm

Posted by eygle at 1:57 PM | Comments (1)

升级MT到3.1.6版本

作者:eygle

出处:http://blog.eygle.com

今天看到MT有升级了，而且是18号发布的版本，这又是一个: highly recommended upgrade for all users.

所以赶快升吧，对于3.1版及更高版本用户，只需要覆盖相应文件即可，不需要运行任何脚本。

升级看看先。
官方网页:
http://www.sixapart.com/movabletype/news/2005/04/movable_type_316_release.html

Posted by eygle at 10:51 AM | Comments (0)

CopyRight © 2004-2008 eygle.com, All rights reserved.