Oracle recommends rebuilding IOTs in AQ to reduce redo

Note: 271855.1

We are getting massive amounts of redo generation well out of performance of 
the regular DML we have. We are using AQ and we enqueue and dequeue constantly. 
I don't have exact volumes. 

Oracle recommends in that note to run a procedure they have that basically 
rebuilds IOT indexes on the AQ tables. However, they don't say why this would 
help. Anyone know? I think an enqueue is basically an insert to an IOT and a 
dequeue is a delete. 

Here is the code they recommend running.

create or replace procedure aqcoalesce
as
v_rebuild_statement VARCHAR2(1000);
err_msg VARCHAR2(100);
cursor c1 is
select owner, queue_table from dba_queue_tables
where recipients='SINGLE' and owner NOT IN ('SYSTEM') and (compatible LIKE 
'8.%' or compatible LIKE '10.%');
cursor c2 is
select owner, queue_table from dba_queue_tables
where recipients='MULTIPLE' and (compatible LIKE '8.1%' or compatible LIKE 
'10.%');
cursor c3 is
select owner, queue_table from dba_queue_tables
where recipients='MULTIPLE' and compatible LIKE '8.0%';
cursor c4 is
select owner, index_name from dba_indexes where table_name in 
('DEF$_AQCALL','DEF$_AQERROR') and index_type= 'NORMAL';
TABLE_NOTEXIST EXCEPTION;
PRAGMA EXCEPTION_INIT(TABLE_NOTEXIST,-942);

BEGIN

sys.dbms_system.ksdwrt(1,'AQ 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.queue_table||'_I REBUILD';

sys.dbms_system.ksdwrt(1,'Executing '||v_rebuild_statement||'.');

EXECUTE IMMEDIATE v_rebuild_statement;

v_rebuild_statement := 'ALTER INDEX 
'||c1_rec.owner||'.AQ$_'||c1_rec.queue_table||'_T REBUILD';

sys.dbms_system.ksdwrt(1,'Executing '||v_rebuild_statement||'.');

EXECUTE IMMEDIATE v_rebuild_statement;

END LOOP;

EXCEPTION

WHEN OTHERS THEN

sys.dbms_system.ksdwrt(1,'AQ coalesce procedure ended execution unsuccessfully 
at ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
err_msg := SUBSTR(SQLERRM,1,100);
raise_application_error(-20001,err_msg);

END;

BEGIN

FOR c2_rec IN c2 LOOP

v_rebuild_statement := 'ALTER TABLE 
'||c2_rec.owner||'.AQ$_'||c2_rec.queue_table||'_I COALESCE';

sys.dbms_system.ksdwrt(1,'Executing '||v_rebuild_statement||'.');

EXECUTE IMMEDIATE v_rebuild_statement;

v_rebuild_statement := 'ALTER TABLE 
'||c2_rec.owner||'.AQ$_'||c2_rec.queue_table||'_T COALESCE';
sys.dbms_system.ksdwrt(1,'Executing '||v_rebuild_statement||'.');

EXECUTE IMMEDIATE v_rebuild_statement;

v_rebuild_statement := 'ALTER TABLE 
'||c2_rec.owner||'.AQ$_'||c2_rec.queue_table||'_H COALESCE';
sys.dbms_system.ksdwrt(1,'Executing '||v_rebuild_statement||'.');

EXECUTE IMMEDIATE v_rebuild_statement;

BEGIN

--The spillover IOT AQ$_.._D will only exist when spillover occurs when using 
buffered messages
--either in a Streams environment or in an AQ buffered message environment.
--This object may therefore not exist so we handle the situation in the 
exception handler.

v_rebuild_statement := 'ALTER TABLE 
'||c2_rec.owner||'.AQ$_'||c2_rec.queue_table||'_D COALESCE';
EXECUTE IMMEDIATE v_rebuild_statement;

sys.dbms_system.ksdwrt(1,'Executing '||v_rebuild_statement||'.');

EXCEPTION

WHEN TABLE_NOTEXIST THEN
NULL;

END;

END LOOP;

EXCEPTION

WHEN OTHERS THEN

sys.dbms_system.ksdwrt(1,'AQ coalesce procedure ended execution unsuccessfully 
at ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
err_msg := SUBSTR(SQLERRM,1,100);
raise_application_error(-20002,err_msg);

END;

BEGIN

FOR c3_rec IN c3 LOOP

v_rebuild_statement := 'ALTER TABLE 
'||c3_rec.owner||'.AQ$_'||c3_rec.queue_table||'_I COALESCE';

sys.dbms_system.ksdwrt(1,'Executing '||v_rebuild_statement||'.');

EXECUTE IMMEDIATE v_rebuild_statement;

END LOOP;

EXCEPTION

WHEN OTHERS THEN

sys.dbms_system.ksdwrt(1,'AQ coalesce procedure ended execution unsuccessfully 
at ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
err_msg := SUBSTR(SQLERRM,1,100);
raise_application_error(-20003,err_msg);

END;

BEGIN

FOR c4_rec IN c4 LOOP

v_rebuild_statement := 'ALTER INDEX '||c4_rec.owner||'.'||c4_rec.index_name||' 
REBUILD';

sys.dbms_system.ksdwrt(1,'Executing '||v_rebuild_statement||'.');

EXECUTE IMMEDIATE v_rebuild_statement; 

END LOOP;

EXCEPTION

WHEN OTHERS THEN

sys.dbms_system.ksdwrt(1,'AQ coalesce procedure ended execution unsuccessfully 
at ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
err_msg := SUBSTR(SQLERRM,1,100);
raise_application_error(-20004,err_msg);

END;

sys.dbms_system.ksdwrt(1,'AQ coalesce procedure ended successful execution at 
'|| to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'));

EXCEPTION

WHEN OTHERS THEN

sys.dbms_system.ksdwrt(1,'AQ coalesce procedure ended execution unsuccessfully 
at ' || to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS'));
err_msg := SUBSTR(SQLERRM,1,100);
raise_application_error(-20005,err_msg);

END;
/
--
http://www.freelists.org/webpage/oracle-l

Other related posts: