Re: Preventing delayed block cleanout due to loading a datawarehouse
Date: Sun, 17 Apr 2011 04:27:15 -0700 (PDT)
Message-ID: <f5ceca1a-54b0-4469-87df-b4ae2731f508_at_q40g2000prh.googlegroups.com>
On Apr 16, 9:43 pm, j.w.vandijk.removet..._at_hetnet.nl (Jaap W. van Dijk) wrote:
> 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:
Well, the basic idea is that you clean the blocks out while the required information is still in the undo available, so it's all about timing in principle.
But how about providing more details about your particular case:
- Do you have indexes enabled during the load or not?
- Are you sure that the insert is actually a direct-path insert? There are various reasons why Oracle might silently fall back to conventional insert without any notice.
You can check the latter by looking at the amount of undo generated since a direct-path insert will not generate undo (provided that no indexes are enabled during the load, otherwise undo will be generated by the necessary index maintenance) or by simply attempting to query the table after the insert but before the commit - any attempt to access a table after a direct-path insert within the same transaction will error out with "ORA-12838: cannot read/modify an object after modifying it in parallel"
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Sun Apr 17 2011 - 06:27:15 CDT