Home » SQL & PL/SQL » SQL & PL/SQL » snapshot too old (PLSQL 11g)
snapshot too old [message #662769] |
Thu, 11 May 2017 19:32 |
|
kmnainani
Messages: 6 Registered: May 2017
|
Junior Member |
|
|
I have the following code where the cursor returns 6985604 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 12 hours to come back and eventually returns "SNAPSHOT TOO OLD" ! 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');
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 audited = a_auditid(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 ;
--
EXIT WHEN c_DMLAUDIT%NOTFOUND ;
END LOOP ;
CLOSE c_DMLAUDIT ;
END ;
/
The DELETE part is taking the longest and running it outside for just 1000 rows using a simple Cursor FOR LOOP also takes 1 hour
[b]Is the error due to the COMMIT being in the Wrong place ?
Why does DELETING only a 1000 rows take that long ?
[mod-edit: code tags added by bb; next time please add them yourself]
[Updated on: Thu, 11 May 2017 20:23] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
Re: snapshot too old [message #662777 is a reply to message #662769] |
Fri, 12 May 2017 01:03 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
If you adjust your 7 million row cursor to include an ORDER BY clause, that may help your ora-1555 problem as Oracle will have to materialize the query right away to sort it (a tip from MC). This might introduce other problems later.
Alternatively, it looks as though you could throw out the PL/SQL and do it with just an INSERT and a DELETE statement. The BS is usually correct about that sort of thing.
|
|
|
Re: snapshot too old [message #662800 is a reply to message #662777] |
Fri, 12 May 2017 07:01 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
An hour to delete 1000 rows indicates something is very wrong.
Doing 1000 individual deletes in a for loop shouldn't take more than a few seconds:
Connected to:
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
SQL> set timing on
SQL> create table bob as select a.*, rownum as row_id from user_tables a, user_tables b where rownum <= 1000;
Table created.
Elapsed: 00:00:02.06
SQL> select count(*) from bob;
COUNT(*)
----------
1000
Elapsed: 00:00:00.04
SQL> begin
for rec in (select * from bob) loop
delete from bob where row_id = rec.row_id;
end loop;
end; 2 3 4 5 6 7 8 9
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.30
SQL> select count(*) from bob;
COUNT(*)
----------
0
Elapsed: 00:00:00.00
SQL>
You need to see where all that time is being spent - trace the session.
Do you have triggers on the table?
|
|
|
|
|
|
Re: snapshot too old [message #662887 is a reply to message #662875] |
Mon, 15 May 2017 02:56 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
And I just spotted that where clause. Is audited a Y/N flag? If it is then doing it in a FORALL is pretty pointless, the first execution will delete all the rows. Also if it is indexing it isn't going to do any good.
If not what does it contain?
|
|
|
Re: snapshot too old [message #662926 is a reply to message #662887] |
Mon, 15 May 2017 18:37 |
|
kmnainani
Messages: 6 Registered: May 2017
|
Junior Member |
|
|
Thank you for all your responses
Sorry about the typo - audited is AUDITID
AUDITID is INDEXED
There are triggers on the table but just PK and Not null triggers
CREATE TABLE "SPAUS"."DMLAUDIT"
( "AUDITID" NUMBER(9,0) NOT NULL ENABLE,
"STRUCTID" NUMBER(9,0) NOT NULL ENABLE,
"OPERATION" VARCHAR2(1) NOT NULL ENABLE,
"PRIMARYKEY" VARCHAR2(64) NOT NULL ENABLE,
"USERID" NUMBER(9,0),
"TIMESTAMP" DATE NOT NULL ENABLE,
"APPSIG" VARCHAR2(30),
"DATA" CLOB,
"ROWVERSION" CHAR(1) DEFAULT '0' NOT NULL ENABLE,
CONSTRAINT "XPKDMLAUDIT" PRIMARY KEY ("AUDITID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "HUB_IDX_A" ENABLE,
CONSTRAINT "DMLOP48" CHECK (Operation in ('I','U','D')) ENABLE,
CONSTRAINT "FK_AUDITTABSTRUCT_DMLAUDIT" FOREIGN KEY ("STRUCTID")
REFERENCES "SPAUS"."AUDITTABSTRUCT" ("STRUCTID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "HUB_DATA_A"
LOB ("DATA") STORE AS (
TABLESPACE "HUB_DATA_A" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;
CREATE UNIQUE INDEX "SPAUS"."SYS_IL0000091031C00008$$" ON "SPAUS"."DMLAUDIT" (
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "HUB_DATA_A"
PARALLEL (DEGREE 0 INSTANCES 0) ;
CREATE INDEX "SPAUS"."XIE1DMLAUDIT" ON "SPAUS"."DMLAUDIT" ("STRUCTID", "PRIMARYKEY")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "HUB_DATA_A" ;
CREATE INDEX "SPAUS"."XIE2DMLAUDIT" ON "SPAUS"."DMLAUDIT" ("STRUCTID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "HUB_IDX_A" ;
CREATE INDEX "SPAUS"."XIE3DMLAUDIT" ON "SPAUS"."DMLAUDIT" ("TIMESTAMP")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "HUB_IDX_A" ;
CREATE UNIQUE INDEX "SPAUS"."XPKDMLAUDIT" ON "SPAUS"."DMLAUDIT" ("AUDITID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "HUB_IDX_A" ;
ALTER TABLE "SPAUS"."DMLAUDIT" ADD CONSTRAINT "DMLOP48" CHECK (Operation in ('I','U','D')) ENABLE;
ALTER TABLE "SPAUS"."DMLAUDIT" MODIFY ("AUDITID" NOT NULL ENABLE);
ALTER TABLE "SPAUS"."DMLAUDIT" MODIFY ("STRUCTID" NOT NULL ENABLE);
ALTER TABLE "SPAUS"."DMLAUDIT" MODIFY ("OPERATION" NOT NULL ENABLE);
ALTER TABLE "SPAUS"."DMLAUDIT" MODIFY ("PRIMARYKEY" NOT NULL ENABLE);
ALTER TABLE "SPAUS"."DMLAUDIT" MODIFY ("TIMESTAMP" NOT NULL ENABLE);
ALTER TABLE "SPAUS"."DMLAUDIT" MODIFY ("ROWVERSION" NOT NULL ENABLE);
ALTER TABLE "SPAUS"."DMLAUDIT" ADD CONSTRAINT "XPKDMLAUDIT" PRIMARY KEY ("AUDITID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "HUB_IDX_A" ENABLE;
|
|
|
|
|
Re: snapshot too old [message #662929 is a reply to message #662928] |
Mon, 15 May 2017 20:10 |
|
kmnainani
Messages: 6 Registered: May 2017
|
Junior Member |
|
|
CREATE TABLE "SPAUS"."DMLAUDIT"
(
"AUDITID" NUMBER(9,0) NOT NULL ENABLE,
"STRUCTID" NUMBER(9,0) NOT NULL ENABLE,
"OPERATION" VARCHAR2(1) NOT NULL ENABLE,
"PRIMARYKEY" VARCHAR2(64) NOT NULL ENABLE,
"USERID" NUMBER(9,0),
"TIMESTAMP" DATE NOT NULL ENABLE,
"APPSIG" VARCHAR2(30),
"DATA" CLOB,
"ROWVERSION" CHAR(1) DEFAULT '0' NOT NULL ENABLE,
CONSTRAINT "XPKDMLAUDIT" PRIMARY KEY ("AUDITID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 nologging compute STATISTICS STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT) TABLESPACE "HUB_IDX_A" ENABLE,
CONSTRAINT "DMLOP48" CHECK (operation IN ('I',
'U',
'D')) ENABLE,
CONSTRAINT "FK_AUDITTABSTRUCT_DMLAUDIT" FOREIGN KEY ("STRUCTID") REFERENCES "SPAUS"."AUDITTABSTRUCT" ("STRUCTID") ENABLE
)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS logging STORAGE
(
INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT
)
TABLESPACE "HUB_DATA_A" lob
(
"DATA"
)
store AS
(
TABLESPACE "HUB_DATA_A" ENABLE STORAGE IN ROW chunk 8192 pctversion 10 NOCACHE logging STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT)
) ;CREATE UNIQUE INDEX "SPAUS"."SYS_IL0000091031C00008$$"
ON "SPAUS"."DMLAUDIT"
(
PCTFREE 10 INITRANS 2 MAXTRANS 255 compute STATISTICS STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT) TABLESPACE "HUB_DATA_A" PARALLEL (degree 0 instances 0) ;CREATE INDEX "SPAUS"."XIE1DMLAUDIT"
ON "SPAUS"."DMLAUDIT"
(
"structid",
"primarykey"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 nologging compute STATISTICS STORAGE
(
INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT
)
TABLESPACE "HUB_DATA_A" ;CREATE INDEX "SPAUS"."XIE2DMLAUDIT"
ON "SPAUS"."DMLAUDIT"
(
"structid"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 nologging compute STATISTICS STORAGE
(
INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT
)
TABLESPACE "HUB_IDX_A" ;CREATE INDEX "SPAUS"."XIE3DMLAUDIT"
ON "SPAUS"."DMLAUDIT"
(
"timestamp"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 compute STATISTICS STORAGE
(
INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT
)
TABLESPACE "HUB_IDX_A" ;CREATE UNIQUE INDEX "SPAUS"."XPKDMLAUDIT"
ON "SPAUS"."DMLAUDIT"
(
"auditid"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 nologging compute STATISTICS STORAGE
(
INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT
)
TABLESPACE "HUB_IDX_A" ;ALTER TABLE "SPAUS"."dmlaudit" ADD CONSTRAINT "DMLOP48" CHECK (operation IN ('I',
'U',
'D')) ENABLE;ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("auditid" NOT NULL ENABLE);ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("structid" NOT NULL ENABLE);ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("operation" NOT NULL ENABLE);ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("primarykey" NOT NULL ENABLE);ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("timestamp" NOT NULL ENABLE);ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("rowversion" NOT NULL ENABLE);ALTER TABLE "SPAUS"."DMLAUDIT" ADD CONSTRAINT "XPKDMLAUDIT" PRIMARY KEY ("AUDITID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 nologging compute STATISTICS STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT) TABLESPACE "HUB_IDX_A" ENABLE;
|
|
|
Re: snapshot too old [message #662932 is a reply to message #662929] |
Tue, 16 May 2017 04:19 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You seem to have lost some carriage returns there. so fix that please.
Also post the exact code you used to test how long 1000 took.
|
|
|
|
Re: snapshot too old [message #662936 is a reply to message #662933] |
Tue, 16 May 2017 06:17 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
You wrote your code in the most ineffective way possible. The whole thing can be done by using the following code
INSERT INTO ARCH_DMLAUDIT
(
AUDITID
,STRUCTID
,OPERATION
,PRIMARYKEY
,USERID
,TIMESTAMP
,APPSIG
,DATA
,ROWVERSION
,ARCH_TIMESTAMP
)
select AUDITID,STRUCTID,OPERATION,PRIMARYKEY,USERID,TIMESTAMP,APPSIG,
DATA,ROWVERSION,ROWID,PARTDATE
FROM DMLAUDIT a
WHERE a.TIMESTAMP < to_date('18082016','DDMMYYYY');
delete from DMLAUDIT a
WHERE a.TIMESTAMP < to_date('18082016','DDMMYYYY');
commit;
An easier way to do it is to have the DMLAUDIT partitioned by timestamp with an interval of a month. Also the ARCH_DMLAUDIT would be partitioned. Then simply move the partition from the active table to the archive table. Very quick. The following link is one way to do it. To give a version specific answer we would need your database version.
https://www.akadia.com/services/ora_exchange_partition.html
[Updated on: Tue, 16 May 2017 06:25] Report message to a moderator
|
|
|
Re: snapshot too old [message #662938 is a reply to message #662929] |
Tue, 16 May 2017 07:52 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
kmnainani wrote on Mon, 15 May 2017 21:10
CREATE TABLE "SPAUS"."DMLAUDIT"
(
"AUDITID" NUMBER(9,0) NOT NULL ENABLE,
"STRUCTID" NUMBER(9,0) NOT NULL ENABLE,
"OPERATION" VARCHAR2(1) NOT NULL ENABLE,
"PRIMARYKEY" VARCHAR2(64) NOT NULL ENABLE,
"USERID" NUMBER(9,0),
"TIMESTAMP" DATE NOT NULL ENABLE,
"APPSIG" VARCHAR2(30),
"DATA" CLOB,
"ROWVERSION" CHAR(1) DEFAULT '0' NOT NULL ENABLE,
CONSTRAINT "XPKDMLAUDIT" PRIMARY KEY ("AUDITID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 nologging compute STATISTICS STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 buffer_pool DEFAULT) TABLESPACE "HUB_IDX_A" ENABLE,
...
ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("auditid" NOT NULL ENABLE);
ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("structid" NOT NULL ENABLE);ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("operation" NOT NULL ENABLE);ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("primarykey" NOT NULL ENABLE);ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("timestamp" NOT NULL ENABLE);ALTER TABLE "SPAUS"."dmlaudit" MODIFY ("rowversion" NOT NULL ENABLE);ALTER TABLE "SPAUS"."DMLAUDIT" ADD CONSTRAINT "XPKDMLAUDIT" PRIMARY KEY ("AUDITID")
These will fail. NEVER put columns in double quotes, especially lower case identifiers.
SQL> create table g123 (foo1 varchar2(10));
Table created.
SQL> alter table g123 modify "foo1" not null;
alter table g123 modify "foo1" not null
*
ERROR at line 1:
ORA-00904: "foo1": invalid identifier
|
|
|
Re: snapshot too old [message #662952 is a reply to message #662938] |
Wed, 17 May 2017 06:27 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
I just noticed that you are saving your rowid into the archive table. Why are you doing that. outside of the immediate transaction there are no guarantees that oracle wont move data and have the rowid change. useless information.
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:29:35 CDT 2024
|