Hi,
I have the following code where the cursor returns 2985604 rows. I am using the following PL/SQL procedure to insert and delete rows.
However, although I am using BULK COLLECT, it still takes over 2 hours to come back ! Is there something wrong with the PL/SQL below :
DECLARE
type xml_tbl is table of xmltype;
a_AUDITID DBMS_SQL.NUMBER_TABLE ;
a_STRUCTID DBMS_SQL.NUMBER_TABLE ;
a_OPERATION DBMS_SQL.VARCHAR2_TABLE ;
a_PRIMARYKEY DBMS_SQL.VARCHAR2_TABLE ;
a_USERID DBMS_SQL.NUMBER_TABLE ;
a_TIMESTAMP DBMS_SQL.DATE_TABLE ;
a_APPSIG DBMS_SQL.VARCHAR2_TABLE ;
a_DATA DBMS_SQL.CLOB_TABLE ;
a_ROWVERSION DBMS_SQL.VARCHAR2_TABLE ;
a_ROWID DBMS_SQL.VARCHAR2_TABLE ;
a_PARTDATE DBMS_SQL.DATE_TABLE ;
--
g_ToDate DATE := TO_DATE('18082016','DDMMYYYY') ;
--
l_rows number(9) := 0;
--
CURSOR c_DMLAUDIT IS
SELECT a.*,a.rowid, a.TIMESTAMP
FROM DMLAUDIT a
WHERE a.TIMESTAMP < to_date('18082016','DDMMYYYY');
--AND TIMESTAMP < TRUNC(g_ToDate) + 1 ;
--
BEGIN
--
dbms_output.enable ;
dbms_output.put_line ( 'Archiving Table : DMLAUDIT') ;
dbms_output.put_line ( 'Started : ' || to_char(sysdate,'HH24:MI:SS DD-MON-YYYY') ) ;
--
OPEN c_DMLAUDIT;
LOOP
FETCH c_DMLAUDIT BULK COLLECT INTO
a_AUDITID,
a_STRUCTID,
a_OPERATION,
a_PRIMARYKEY,
a_USERID,
a_TIMESTAMP,
a_APPSIG,
a_DATA,
a_ROWVERSION,
a_ROWID,
a_PARTDATE
LIMIT 500;
--
FOR i IN 1..a_AUDITID.COUNT LOOP
INSERT INTO ARCH_DMLAUDIT
(
AUDITID
,STRUCTID
,OPERATION
,PRIMARYKEY
,USERID
,TIMESTAMP
,APPSIG
,DATA
,ROWVERSION
,ARCH_TIMESTAMP
)
VALUES (
a_AUDITID(i),
a_STRUCTID(i),
a_OPERATION(i),
a_PRIMARYKEY(i),
a_USERID(i),
a_TIMESTAMP(i),
a_APPSIG(i),
a_DATA(i),
a_ROWVERSION(i),
a_PARTDATE(i) ) ;
END LOOP ;
--
BEGIN
FORALL i IN 1..a_AUDITID.COUNT
DELETE FROM DMLAUDIT
WHERE rowid = a_rowid(i) ;
exception
when others then
if sqlcode= -24381 then
for indx in 1..SQL%BULK_EXCEPTIONS.COUNT loop
dbms_output.put_line ( SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX || '- ORA-' || SQL%BULK_EXCEPTIONS(indx).ERROR_CODE );
End loop;
end if;
END;
--
-- COMMIT ;
--
l_rows := l_rows + a_AUDITID.COUNT ;
dbms_application_info.set_client_info ( 'Archiving row ' || least(l_rows,51375962) || ' of 51375962' ) ;
dbms_output.put_line ( 'Archived ' || least(l_rows,51375962) || ' of 51375962' ) ;
--
EXIT WHEN c_DMLAUDIT%NOTFOUND ;
END LOOP ;
CLOSE c_DMLAUDIT ;
--
dbms_output.put_line ( 'Completed : ' || to_char(sysdate,'HH24:MI:SS DD-MON-YYYY')) ;
END ;
/
You didnt share with us *where* the time is being lost.
You have
- a query
- an insert
- a delete
one or more of those things will be taking the time, and your instrumentation should be able to tell you that.
But the questions I have are:
1) why not just
insert into arch_dml_audit select * from dml_audit where ...;
delete from dml_audit...
ie, simple SQL statements
2) why not partition dmlaudit and then just use exchange partition for "instantaneous" archiving