Preventing delayed block cleanout due to loading a datawarehouse
From: Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl>
Date: Fri, 08 Apr 2011 21:02:28 GMT
Message-ID: <4d9f72ff.324390_at_news.hetnet.nl>
Hi,
Date: Fri, 08 Apr 2011 21:02:28 GMT
Message-ID: <4d9f72ff.324390_at_news.hetnet.nl>
Hi,
I'm in charge of a datawarehouse which we update every weekend. That means loading lots of tables with
TRUNCATE
INSERT /*+ APPEND PARALLEL */ ... SELECT ... FROM ...
COMMIT
for every table. The volume of the total load is about half to one TB.
Afterwards, when the tables are queried, every now and again an ORA-01555 happens, sometimes even after days. In my opinion this can only be because of delayed block cleanout, because loading and then querying a table is *always* done serially.
If I would load the tables with
DROP TABLE
CREATE TABLE ... AS SELECT ... FROM ...
would the blocks be written pre-cleaned out, thus preventing the
ORA-01555 from happening?
Regards,
Jaap.
Received on Fri Apr 08 2011 - 16:02:28 CDT