Oracle recommends rebuilding IOTs in AQ to reduce redo
- From: ryan_gaffuri@xxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 05 Jan 2008 00:51:43 +0000
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;
/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
