Re: Preventing delayed block cleanout due to loading a datawarehouse

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Mon, 11 Apr 2011 05:05:17 -0700 (PDT)
Message-ID: <0cafc4b5-7313-4793-90ab-382ce9a587e2_at_k22g2000yqh.googlegroups.com>

On Apr 8, 5:02 pm, j.w.vandijk.removet..._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 Jaap,

The next time you see this, check for "transaction table consistent reads - undo records applied" and see if that is increasing during the failed query. Received on Mon Apr 11 2011 - 07:05:17 CDT