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 Received on Fri Jan 04 2008 - 18:51:43 CST