Re: Preventing delayed block cleanout due to loading a datawarehouse
Date: Sat, 16 Apr 2011 19:43:23 GMT
Message-ID: <4da9ebc2.1556750_at_news.hetnet.nl>
On Fri, 08 Apr 2011 21:02:28 GMT, j.w.vandijk.removethis_at_hetnet.nl (Jaap W. van Dijk) wrote:
>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.
Hi guys,
What an in-depth discussion! Thank you all for increasing my knowledge. As far as I know this stuff is not easily found, not even at the Oracle support site. I make good use of it.
On thing is still puzzling me: to prevent an ORA-01555 due to delayed block cleanout, in case of the rollback transaction slot being overwritten, in Note 40689.1 on the Oracle Support site the following is recommended:
alter session set optimizer_goal = rule; select count(*) from table_name;
for the same reason for which Randolf Geist recommends gathering statistics I guess, forcing a visit and cleaning out all the blocks. But why won't this run into the same ORA-01555? Or is discovering that the rollback transaction slot isn't there anymore somehow enough when doing a count(*) or when gathering statistics?
Regards,
Jaap
Received on Sat Apr 16 2011 - 14:43:23 CDT