QMON不执行用ASSM或用freelist组管理本地表空间进行管理
故障信息。问题是如何证明其自己依赖于数据库和配置查中活动AQ数量
症状可能包括以下方面:
1、观察入队与出队的索引组织表或者是索引。
2、出队性能降低随着IOT/index的不断增大
3、在10850 2级队列跟踪形势的kwqitremb条目,跳跃的<OWNER> AQ $ _ <QUEUE_TABLE_NAME> _I将被记录下来。
再此之前,本地管理的表空间使用自动空间管理(ASSM)或者定义为freelist组QMON将不执行出队的IOT,它将慢慢增大,
解决方案
1、 创建ASSM本地管理的表空间对于AQ
2、 不要指定freelist组
3、 在10gr2中起了AQ的对象动态可以收缩
4、 定期执行合并操作,无论用job还是手动,对所有AQ出队IOTs在本地使用ASSM
alter table AQ$_ < QUEUE_TABLE_NAME > _I coalesce;
alter index AQ$_ < QUEUE_TABLE_NAME > _I rebuild;
下面是一个存储过程重建/合并ASSM驻留在这些队列的IOTs
create or replace procedure deqiotcoalesce
as
v_rebuild_statement VARCHAR2(1000);
err_msg VARCHAR2(100);
cursor c1 is
select qt.owner owner, qt.queue_table queuetable from dba_queue_tables qt, dba_tablespaces ts, dba_tables t where ts.segment_space_management='AUTO' and qt.queue_table=t.table_name and ts.tablespace_name=t.tablespace_name and qt.owner=t.ownerand qt.recipients='SINGLE' and qt.compatible LIKE '8.1%';
cursor c2 is
select qt.owner owner, qt.queue_table queuetable from dba_queue_tables qt, dba_tablespaces ts, dba_tables t where ts.segment_space_management='AUTO' and qt.queue_table=t.table_name and ts.tablespace_name=t.tablespace_name and qt.owner=t.owner and qt.recipients='MULTIPLE' and qt.compatible LIKE '8.1%';
BEGIN
sys.dbms_output.enable(1000000);
sys.dbms_system.ksdwrt(1,'Dequeue IOT/index coalesce procedure starting execution at ' ||
to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
BEGIN
FOR c1_rec IN c1 LOOP
v_rebuild_statement := 'ALTER INDEX '||c1_rec.owner||'.AQ$_'||c1_rec.queuetable||'_I REBUILD';
sys.dbms_system.ksdwrt(1,'Executing '||v_rebuild_statement||'.');
EXECUTE IMMEDIATE v_rebuild_statement;
sys.dbms_system.ksdwrt(1,'Executed successfully.');
END LOOP;
EXCEPTION
WHEN OTHERS THEN
sys.dbms_system.ksdwrt(1,'Dequeue IOT/index coalesce procedure ended execution unsuccessfully at ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
raise_application_error(-20000,err_msg);
END;
BEGIN
FOR c2_rec IN c2 LOOP
v_rebuild_statement := 'ALTER TABLE '||c2_rec.owner||'.AQ$_'||c2_rec.queuetable||'_I COALESCE';
sys.dbms_system.ksdwrt(1,'Executing '||v_rebuild_statement||'.');
EXECUTE IMMEDIATE v_rebuild_statement;
sys.dbms_system.ksdwrt(1,'Executed successfully.');
END LOOP;
EXCEPTION
WHEN OTHERS THEN
sys.dbms_system.ksdwrt(1,'Dequeue IOT/index coalesce procedure ended execution unsuccessfully at ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
raise_application_error(-20000,err_msg);
END;
sys.dbms_system.ksdwrt(1,'Dequeue IOT/index coalesce procedure ended successful execution at '|| to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
EXCEPTION
WHEN OTHERS THEN
sys.dbms_system.ksdwrt(1,'Dequeue IOT/index coalesce procedure ended execution unsuccessfully at ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
raise_application_error(-20000,err_msg);
END;
/
下面是一个工作计划,演示如何实行工作计划
首先定义一个变量jobno
然后运行
execute dbms_job.submit(:jobno, 'deqiotcoalesce;', trunc(sysdate) + 1, 'sysdate+1');
commit;