eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 装修日记-瓷砖我选欧神诺 | Blog首页 | Global_name从何处来? »

Streams散记之一-如何清除流配置

在完成streams部署之后,如果需要重新配置或舍弃配置,可以选择清除这些配置信息。

这个清除工作的步骤非常简单,只需要如下一条命令:
EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();

有朋友问过我这样一个问题:这个Remove过程在进行什么工作?为什么会非常缓慢?

要想了解这个过程也非常简单,只需要跟踪一下就可以了,全部过程可以参考如下步骤:

bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 31 14:53:32 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();

PL/SQL procedure successfully completed.

然后找到这个跟踪文件,仔细研究解读一下就能了解这个操作的内部原理及步骤,以下摘录一些主要步骤。
捕获处理部分:

SELECT CAPTURE_NAME
FROM
DBA_CAPTURE


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 6 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.02 0.02 0 6 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 NESTED LOOPS OUTER (cr=6 pr=0 pw=0 time=870 us)
1 NESTED LOOPS (cr=4 pr=0 pw=0 time=682 us)
1 TABLE ACCESS FULL STREAMS$_CAPTURE_PROCESS (cr=3 pr=0 pw=0 time=555 us)
1 INDEX UNIQUE SCAN LOGMNR_SESSION_PK (cr=1 pr=0 pw=0 time=113 us)(object id 5788)
1 TABLE ACCESS CLUSTER USER$ (cr=2 pr=0 pw=0 time=170 us)
1 INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=29 us)(object id 11)

********************************************************************************

UPDATE sys.streams$_capture_process SET status = :1, status_change_time =
SYSDATE, ruleset_owner = :2, ruleset_name = :3, flags = :4, start_scn = :5,
first_scn = :6, use_dblink = :7, negative_ruleset_owner = :8,
negative_ruleset_name = :9, version = :10, error_message = :11,
error_number = :12, capture_userid = :13, spare3 = :14
WHERE
capture# = :15

移除流规则定义:

begin dbms_streams_adm_utl.remove_streams_rule(NULL, NULL, :1, :2); end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.06 0.06 0 12 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.06 0.06 0 12 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************

DELETE FROM SYS.STREAMS$_RULES R
WHERE
R.RULE_OWNER = NVL(:B4 , R.RULE_OWNER) AND R.RULE_NAME = NVL(:B3 ,
R.RULE_NAME) AND R.STREAMS_NAME = NVL(:B2 , R.STREAMS_NAME) AND
R.STREAMS_TYPE = NVL(:B1 , R.STREAMS_TYPE)

删除流规则信息:

DELETE FROM SYS.STREAMS$_MESSAGE_RULES R WHERE R.RULE_OWNER = NVL(:B4 , R.RULE_OWNER) AND R.RULE_NAME = NVL(:B3 , R.RULE_NAME) AND R.STREAMS_NAME = NVL(:B2 , R.STREAMS_NAME) AND R.STREAMS_TYPE = NVL(:B1 , R.STREAMS_TYPE)

当然还有大量对于LOGMINER的处理过程:

BEGIN DELETE FROM sys.aq$_replay_info t WHERE t.eventid = :1 AND t.agent.name
= :2 AND t.agent.address IS NULL AND t.agent.protocol =
sys.dbms_aqadm.LOGMINER_PROTOCOL; END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.01 0 27 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 27 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************

select toid
from
coltype$ where obj#=:1 and intcol#=:2


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 0 3 0 1

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID COLTYPE$ (cr=3 pr=0 pw=0 time=64 us)
1 INDEX UNIQUE SCAN I_COLTYPE2 (cr=2 pr=0 pw=0 time=37 us)(object id 170)

********************************************************************************

DELETE FROM SYS.AQ$_REPLAY_INFO T
WHERE
T.EVENTID = :B2 AND T.AGENT.NAME = :B1 AND T.AGENT.ADDRESS IS NULL AND
T.AGENT.PROTOCOL = :B3

总之这个清除过程相当复杂,所以类似的操作应当选择在数据库空闲时段来完成,以避免对数据库产生性能冲击,切记切记!

-The End-


历史上的今天...
    >> 2012-10-31文章:
    >> 2011-10-31文章:
    >> 2010-10-31文章:
    >> 2008-10-31文章:
    >> 2006-10-31文章:
    >> 2005-10-31文章:
    >> 2004-10-31文章:

无觅

By eygle on 2007-10-31 16:42 | Comments (3) | Advanced | 1652 |

3 Comments

这样可以删除配置,但是在配置stream时建立的queue不会去除。
sqlplus strmadmin/pw
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
STREAMS_QUEUE_TABLE TABLE
AQ$_STREAMS_QUEUE_TABLE_S TABLE
AQ$_STREAMS_QUEUE_TABLE_T TABLE
AQ$STREAMS_QUEUE_TABLE_S VIEW
AQ$_STREAMS_QUEUE_TABLE_H TABLE
SYS_IOT_OVER_62941 TABLE
AQ$_STREAMS_QUEUE_TABLE_G TABLE
AQ$_STREAMS_QUEUE_TABLE_I TABLE
AQ$_STREAMS_QUEUE_TABLE_C TABLE
AQ$_STREAMS_QUEUE_TABLE_F VIEW
AQ$STREAMS_QUEUE_TABLE VIEW

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
AQ$STREAMS_QUEUE_TABLE_R VIEW

12 rows selected.
我一般的做法是在去除上面的配置后,再把queue去掉:
exec dbms_streams_adm.remove_queue(queue_name => 'streams_queue',cascade => true,drop_unused_queue_table => true);

是有很多东西清理不掉,需要手工处理。

Oracle这样处理是对的,Queue是不属于Stream的配置范畴,
也许还有其它东东要用到这些Queue。
再说了,重启后直接drop user cascade流管理用户岂不更好?


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com