Re: Informatica Bulk Mode behavior
- From: Tim Gorman <tim@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 26 Apr 2004 13:16:16 -0600
Jeff and Waleed,
I think Informatica is blowing smoke on this error message, or at least they
are smoking something...
Each time you initiate a direct-path load operation, it will allocate a new
extent, but the arraysize or data save frequency has no bearing on size or
number of extents, whether using "direct-path" (a.k.a. "bulk") loads or
"conventional" SQL INSERT loads. In other words, based on the behavior
described, it is quite likely that Informatica has a bug in their bulk-load
functionality resulting from improper use of the Oracle Call Interface (OCI)
application programming interface (API). I don't think that the cause of
the ORA-01658 error has to do with a problem of any kind within Oracle at
all, no matter whose perspective you use.
Programs like Informatica can only use the OCI functions for direct-path
loading (i.e. Functions named "OCIDirPath...()") and none of these OCI
functions presents any controls for extent sizing.
The only explanation is that Informatica is doing something silly and
pointless like calling "OCIDirPathFinish()" instead of
"OCIDirPathDataSave()" after each data save.
The "OCIDirPathFinish()" function closes a direct-path load session,
requiring that a new direct-path load session be initiated, causing a new
extent to be allocated. The "OCIDirPathDataSave()" function merely saves
the rows to blocks on disk, but does not initiate a new extent or have any
control over extents.
In which case, the Informatica documentation should read something like:
Informatica code bug: Due to a misreading of Oracle's documentation
and sample OCI code, Informatica is improperly performing data
saves during bulk loads using the "direct-path" API.
Solution: Set the commit interval very high, to mask the fact that
Informatica is improperly ending the direct-path load with each
data save. This will result in fewer extents being used and
less space being wasted, depending on the table's extent size.
Just my $0.02 as a long-time OCI programmer. I don't know for sure if this
is what is happening, but it sure as heck sounds like it. It would be
interesting to "truss" the Informatica load process, to see what OCI
function calls it is issuing... :-)
Hope this helps...
-Tim
P.S. It would be interesting to find out if Informatica v7.x
includes any "performance enhancements" for bulk loading
over v6.x. If so, the only "enhancement" I can think of is
that they detected their own bug and fixed it... :-)
After all, nothing new has been added to the "OCIDirPath...()"
functions in the way of a performance enhancement since they
became available in v8.0.x, I'm pretty sure...
on 4/26/04 6:48 AM, Thomas Jeff at jeff.thomas@xxxxxxxxxxx wrote:
> Waleed, =20
>
> Indeed you were on the right track. We got the following msg from
> Informatica
> Support. "Oracle design limitation"?????? =20
>
>
> Problem:
>
> In 6.x version running session targeting to Oracle using bulk mode
> creates new extents=20
> at every commit. This sometimes leads to the following error: "ORA-01568
> unable to create=20
> initial extent."=20
>
> Solution:
>
> This is a known issue (CR 53791) that occurs when using Oracle bulk
> loading. To resolve this=20
> issue increase the commit interval to very high value. SUPPORTING
> INFORMATION: This issue is=20
> due to the following design limitation in Oracle: Direct Path Loading
> (using same code as bulk=20
> loading) is going to use only blocks above the high water mark. Also
> Direct Path loading does=20
> not check for Partial blocks are not used, so no reads are needed to
> find them and fewer writes=20
> are performed. In a conventional data load the bind array buffer is
> filled and passed to Oracle=20
> with a SQL INSERT command which results in the space management by the
> SQL Command Processing to=20
> be utilized. Therefore bulk loading will allocate more extents.=20
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Khedr, Waleed
> Sent: Thursday, April 22, 2004 11:34 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: RE: Informatica Bulk Mode behavior
>
>
> I would ask the developers how they configured the commit interval (if =
> =3D
> it's set to a very low value)
>
> Waleed
>
> -----Original Message-----
> From: Thomas Jeff [mailto:jeff.thomas@xxxxxxxxxxx]
> Sent: Thursday, April 22, 2004 10:33 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Informatica Bulk Mode behavior
>
>
> List,
> Is anyone familiar with how Informatica's bulk mode works? =3D20
>
> We have a situation where I created a 4GB tablespace for a new and
> simple=3D20 truncate/load operation from Informatica, around 7 million
> rows estimated=3D20 to take up 1.5GB. =3D20 =3D20 Using bulk mode, =
> which
> appears to be a direct load (in the SQL cache, the=3D20 INSERT statement
> has a hint that I've never seen before: SYS_DL_CURSOR which=3D20 I =
> assume
> stands for Direct Load), they run out of space in the tablespace
> after=3D20 about 200K rows have been inserted.
>
> If I then manually rebuild the table, the 200K rows gets compressed back
> down=3D20 to one extent.
>
> So there's a lot of either empty or preallocated space. Thinking
> somehow=3D20
> the high-water mark was the culprit, I manually truncated the table
> before=3D20 they reran their job. =3D20
>
> Still the same problem. =3D20
>
> If the job runs in 'normal' mode, which is row-by-row processing, it
> runs fine,=3D20 although of course, performance is quite poor.
>
> Any ideas/experience as to how this bulk mode operates?
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
