Re: Preventing delayed block cleanout due to loading a datawarehouse
Date: Fri, 8 Apr 2011 17:34:31 -0700 (PDT)
Message-ID: <07659dd8-3d63-4b4a-8d03-d1a3f91eee95_at_d19g2000yql.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.
how long does the truncate process take?
How long does the "insert into.. " processing take?
if it takes more than the weekend, then you need to do this
differently.
Have you tried to increase your undo_retention?
Have you tried to add more rollback segments?
ORA-01555 can only be caused by insufficient rollback segments. Make
your transactions MUCH smaller.
A good explanation can be found at: <http://asktom.oracle.com/pls/
asktom/f?p=100:11:0::::p11_question_id:1441804355350>
I am not sure I would agree with truncating a"warehouse" on a weekly basis.?.? From your description, it sounds like the base tables are also in the same database??? Why? And how does making a copy in these "warehouse" tables help your overall system performance?
You might consider breaking it into smaller pieces.
insert into ... select * from oldtab where somedatefield between date0
and date1;
commit;
insert into ... select * from oldtab where somedatefield between date1
and date2;
commit;
insert into ... select * from oldtab where somedatefield between date2
and date3;
commit;
insert into ... select * from oldtab where somedatefield between date3
and date4;
commit;
etc...
and start enough of them at a time in parallel where you get the
throughput but don't saturate your I/O or CPU bandwidth - and make
sure your indexing satisfies the "where" clause.
Or, if you are really good at DCL try using exp/imp
NOT TESTED:
exp test1/test1 file = SYS$OUTPUT log = exp_XXX.log tables = XXX
feedback = 1000000 buffer = 40960000 grants = n constraints = n
indexes = n
compress = n direct = y | imp test2/test2 file=SYS$PIPE tables= XXX
rows=y ignore=y
With 10g and above, using impdp you can do this with NETWORK_LINK without doing the expdp first. Received on Fri Apr 08 2011 - 19:34:31 CDT