OraFAQ Forum: Server Utilities » Expdb with parallel parameter writes files one at a time

Home » RDBMS Server » Server Utilities » Expdb with parallel parameter writes files one at a time (Oracle 11.2.0.4.5)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Expdb with parallel parameter writes files one at a time [message #639184]

Thu, 02 July 2015 10:40

raziq
Messages: 7
Registered: July 2015
Location: Houston

Junior Member

Here are all the details:
Oracle = 11.2.0.4.5 64bit
servername:tcnp%uname -a
SunOS servername 5.11 11.2 sun4v sparc sun4v
Spfile:cpu_count=64; parallel_max_servers=64; processes=250
expdp command used:
expdp username schemas=team_admin dumpfile=team_admin_schema_tcnp_041515_%U.dmp logfile=team_admin_schema_tcnp_041515.log parallel=8 FLASHBACK_TIME=SYSTIMESTAMP version=latest
What this does is it will export to 8 files, but it writes to these files one at a time. Not at the same time.

Thanks for your help.

Report message to a moderator

Re: Expdb with parallel parameter writes files one at a time [message #639190 is a reply to message #639184]

Thu, 02 July 2015 10:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Thanks for sharing your comments

Report message to a moderator

Re: Expdb with parallel parameter writes files one at a time [message #639195 is a reply to message #639184]

Thu, 02 July 2015 11:05

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

There are all sorts of restrictions on parallel export. If I remember correctly, you will only ever get one Data Pump worker process per table, and unless the table is over (I think) 500MB, you will not get PX servers exporting it in parallel. How many tables are there in that schema? How big are they?

Report message to a moderator

Re: Expdb with parallel parameter writes files one at a time [message #639198 is a reply to message #639195]

Thu, 02 July 2015 11:28

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Right, each worker writes in its file and each segment (not necessary table if partitioned) is served by a unique worker.
Note that the segments are chosen by size descending.

Report message to a moderator

Re: Expdb with parallel parameter writes files one at a time [message #639201 is a reply to message #639198]

Thu, 02 July 2015 12:03

raziq
Messages: 7
Registered: July 2015
Location: Houston

Junior Member

Thanks. Just checked and the largest table is 100 MB. Majority of the objects are indexes, lobindexes, lobsegments. Lobsegments = 141 GB. 420 objects.

Report message to a moderator

Re: Expdb with parallel parameter writes files one at a time [message #639202 is a reply to message #639201]

Thu, 02 July 2015 12:13

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Indexes are irrelevant: only DDL text are exported.
If you set STATUS parameter you will periodically know what each worker is doing.

Report message to a moderator

Re: Expdb with parallel parameter writes files one at a time [message #639204 is a reply to message #639202]

Thu, 02 July 2015 13:39

raziq
Messages: 7
Registered: July 2015
Location: Houston

Junior Member

Indexes are irrelevant: only DDL text are exported.
Not sure if I agree. When I do the import from my export files, indexes are there on the target database.

Report message to a moderator

Re: Expdb with parallel parameter writes files one at a time [message #639205 is a reply to message #639204]

Thu, 02 July 2015 13:39

raziq
Messages: 7
Registered: July 2015
Location: Houston

Junior Member

I meant to say not sure if I understand Smile

Report message to a moderator

Re: Expdb with parallel parameter writes files one at a time [message #639206 is a reply to message #639205]

Thu, 02 July 2015 13:44

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Only the DDL to create the indexes are exported, these DDL are executed during import and indexes recreated.
So exporting indexes take a short time.

Report message to a moderator

Re: Expdb with parallel parameter writes files one at a time [message #639207 is a reply to message #639206]

Thu, 02 July 2015 13:45

raziq
Messages: 7
Registered: July 2015
Location: Houston

Junior Member

I see. So that would explain why the import takes longer as it is creating indexes in the target database.

Report message to a moderator

Re: Expdb with parallel parameter writes files one at a time [message #639208 is a reply to message #639205]

Thu, 02 July 2015 13:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Not sure if I agree. When I do the import from my export files, indexes are there on the target database.
actual INDEX entry contains ROWID & FILEID where the row actually resides; which will be DIFFERENT between source & target tablespace.
Propagating cloned copy of source INDEX detail is a foolish exercise; since data rows in target are different from source tablespace.
for INDEX on DDL text exists & resides in exported data file.

Report message to a moderator

Re: Expdb with parallel parameter writes files one at a time [message #639209 is a reply to message #639207]

Thu, 02 July 2015 13:49

Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Longer that what? Than export, yes, and also because writes are slower than reads, you can test it importing without the indexes (and statistics, use EXCLUDE parameter).

[Edit: typo]

[Updated on: Thu, 02 July 2015 13:59]

Report message to a moderator

Re: Expdb with parallel parameter writes files one at a time [message #639210 is a reply to message #639209]

Thu, 02 July 2015 13:51

raziq
Messages: 7
Registered: July 2015
Location: Houston

Junior Member

Thanks.

Report message to a moderator

Previous Topic:	Datapump Import ORA-01555
Next Topic:	IMPDP got hanged

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Feb 07 00:43:15 CST 2025