Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Informatica Bulk Mode behavior

RE: Informatica Bulk Mode behavior

From: Thomas Jeff <jeff.thomas_at_thomson.net>
Date: Fri, 23 Apr 2004 07:43:02 -0500
Message-ID: <358728A276824E419580403633AABFD0031E121A@INDYSMAIL03.am.thmulti.com>


That's not the case here though --- we eliminated the indexes entirely to try and
isolate the culprit.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rajesh.Rao_at_jpmchase.com
Sent: Friday, April 23, 2004 5:32 AM
To: oracle-l_at_freelists.org
Subject: RE: Informatica Bulk Mode behavior

Well Mohan, You are under the impression, I am stating the obvious. But If you read my email again, you would see that I said "twice the space" and "temporary" segments.

Say, you have an index of size 2Gb, and you insert 2 Mb of index keys, Oracle will create a NEW TEMPORARY segment of size 2 Gb+2 MB in the same tablespace. So, at one point, the index will occupy 2Gb(old space)+2Gb(new TEMPORARY segments)+2Mb of space(new index keys). Once the new index is built, the old one will be dropped, and you should now the index occupying only 2Gb+2Mb of space. Hence, my reasoning that the indexes could be the reason for the tablespace filling up.

I DIRECT you to read Note 50592.1 ;-)

Regards

=20

                    "Mohan, Ross"

                    <RMohan_at_thexchange.       To:
<oracle-l_at_freelists.org>                                         =20
                    com>                      cc:

                    Sent by:                  Subject:     RE:
Informatica Bulk Mode behavior                          =20
                    oracle-l-bounce_at_fre

                    elists.org

=20

=20

                    04/22/2004 04:28 PM

                    Please respond to

                    oracle-l

=20

=20

Whereas with regular indexes they don't take space? ;-) Believe if you have, as one example, a FULLY packed, fresh index on tab(col) with pctfree 0 and defaulted freelisting and add a few thousand rows via APPEND you are going to get the same resultant index size if you added the rows via conventional means.

...your experiences may well be different, but....just a wayt of saying that mostly the pctfree of the idx and its freelists config will drive a lot of the space (mis?)usage.

HTH -----Original Message-----
From: Rajesh.Rao_at_jpmchase.com [mailto:Rajesh.Rao_at_jpmchase.com] Sent: Thursday, April 22, 2004 2:34 PM
To: oracle-l_at_freelists.org
Subject: RE: Informatica Bulk Mode behavior

One thing of note that I have encountered with Direct Load Inserts is that the Indexes on the table will require twice the space. Say, you have indexes of size 2 Gb, then after the direct insert, the exisiting index is merged onto a "temporary" segment along with the new index keys. So, check if you are running out of space coz of the indexes.

Regards
Raj

                    "Khedr, Waleed"

                    <Waleed.Khedr_at_FMR.C       To:
<oracle-l_at_freelists.org>
                    OM>                       cc:

                    Sent by:                  Subject:     RE:
Informatica
Bulk Mode behavior
                    oracle-l-bounce_at_fre

                    elists.org





                    04/22/2004 01:22 PM

                    Please respond to

                    oracle-l









Multithread is a way Informatica can parallelism the load into the =3D target using many concurrent sessions. Since it's direct load, each session will try to allocate its own =3D extents to load into.

I would check if they have a very high number of threads.

But also since you said the job failed and the table was loaded with =3D some rows (200K), this indicates that they have low setting for the =3D commit interval otherwise the table will be empty.

Commit interval should be very high 1 million or higher.

Waleed

-----Original Message-----
From: Thomas Jeff [mailto:jeff.thomas_at_thomson.net] Sent: Thursday, April 22, 2004 12:23 PM
To: oracle-l_at_freelists.org
Subject: RE: Informatica Bulk Mode behavior

Waleed,

The tablespace is locally-managed, 128MB extents. I'll have to ask about the multithread, can you
tell me the ramifications of it?

Thanks.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Khedr, Waleed
Sent: Thursday, April 22, 2004 10:24 AM
To: oracle-l_at_freelists.org
Subject: RE: Informatica Bulk Mode behavior

I use it. It's using direct load OCI.
Never had your problem.

What is the extent size (initial/next ) for the table? Are they using =
=3D
=3D3D =3D3D3D multithread in the mapping?

Waleed

-----Original Message-----
From: Thomas Jeff [mailto:jeff.thomas_at_thomson.net] Sent: Thursday, April 22, 2004 10:33 AM
To: oracle-l_at_freelists.org
Subject: Informatica Bulk Mode behavior

List,
Is anyone familiar with how Informatica's bulk mode works? =3D3D3D20

We have a situation where I created a 4GB tablespace for a new and simple=3D3D3D20 truncate/load operation from Informatica, around 7 = million
rows estimated=3D3D3D20 to take up 1.5GB. =3D3D3D20 =3D3D3D20 Using = bulk =3D
mode, =3D3D which appears to be a direct load (in the SQL cache, the=3D3D3D20 INSERT =3D statement has a hint that I've never seen = before:
SYS_DL_CURSOR which=3D3D3D20 I =3D =3D3D assume stands for Direct Load), = they
run out of space in the tablespace after=3D3D3D20 about 200K rows have been inserted.

If I then manually rebuild the table, the 200K rows gets compressed back down=3D3D3D20 to one extent.

So there's a lot of either empty or preallocated space. Thinking somehow=3D3D3D20
the high-water mark was the culprit, I manually truncated the table before=3D3D3D20 they reran their job. =3D3D3D20

Still the same problem. =3D3D3D20

If the job runs in 'normal' mode, which is row-by-row processing, it runs fine,=3D3D3D20 although of course, performance is quite poor.

Any ideas/experience as to how this bulk mode operates?

Thanks.



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org 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_at_freelists.org
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
-----------------------------------------------------------------
Received on Fri Apr 23 2004 - 07:40:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US