Informatica Bulk Mode behavior 2004-04-26 - By Thomas Jeff
Waleed,
Indeed you were on the right track. We got the following msg from
Informatica
Support. "Oracle design limitation "??????
Problem:
In 6.x version running session targeting to Oracle using bulk mode
creates new extents
at every commit. This sometimes leads to the following error: "ORA-01568 (See ORA-01568.ora-code.com)
unable to create
initial extent. "
Solution:
This is a known issue (CR 53791) that occurs when using Oracle bulk
loading. To resolve this
issue increase the commit interval to very high value. SUPPORTING
INFORMATION: This issue is
due to the following design limitation in Oracle: Direct Path Loading
(using same code as bulk
loading) is going to use only blocks above the high water mark. Also
Direct Path loading does
not check for Partial blocks are not used, so no reads are needed to
find them and fewer writes
are performed. In a conventional data load the bind array buffer is
filled and passed to Oracle
with a SQL INSERT command which results in the space management by the
SQL Command Processing to
be utilized. Therefore bulk loading will allocate more extents.
-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of Khedr, Waleed
Sent: Thursday, April 22, 2004 11:34 AM
To: oracle-l@(protected)
Subject: RE: Informatica Bulk Mode behavior
I would ask the developers how they configured the commit interval (if =
it 's set to a very low value)
Waleed
-- --Original Message-- --
From: Thomas Jeff [mailto:jeff.thomas@(protected)]
Sent: Thursday, April 22, 2004 10:33 AM
To: oracle-l@(protected)
Subject: Informatica Bulk Mode behavior
List,
Is anyone familiar with how Informatica 's bulk mode works? =20
We have a situation where I created a 4GB tablespace for a new and
simple=20 truncate/load operation from Informatica, around 7 million
rows estimated=20 to take up 1.5GB. =20 =20 Using bulk mode, which
appears to be a direct load (in the SQL cache, the=20 INSERT statement
has a hint that I 've never seen before: SYS_DL_CURSOR which=20 I assume
stands for Direct Load), they run out of space in the tablespace
after=20 about 200K rows have been inserted.
If I then manually rebuild the table, the 200K rows gets compressed back
down=20 to one extent.
So there 's a lot of either empty or preallocated space. Thinking
somehow=20
the high-water mark was the culprit, I manually truncated the table
before=20 they reran their job. =20
Still the same problem. =20
If the job runs in 'normal ' mode, which is row-by-row processing, it
runs fine,=20 although of course, performance is quite poor.
Any ideas/experience as to how this bulk mode operates?
Thanks.
-- ---- ---- ---- ---- ---- ---- ---- ------
Jeffery D Thomas
DBA
Thomson Information Services
Thomson, Inc.
Email: jeff.thomas@(protected)
Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba
-- ---- ---- ---- ---- ---- ---- ---- ------
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected) put
'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected) put
'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
