Help - Search - Members - Calendar
Full Version: export to long
Oracle DBA Forums > Oracle > Oracle Forum
soumia
hello
when i do an export for table for 130000 lignes it take 2 h to finish please help me to minimise the time

thanks for all
SteveC
http://www.remote-dba.net/teas_rem_util11.htm
Asad
QUOTE (soumia @ Jul 22 2008, 01:19 PM) *
hello
when i do an export for table for 130000 lignes it take 2 h to finish please help me to minimise the time

thanks for all


is it traditional exp utility export or you're using datapump (expdp) utility. this might help us to help you if you please metion your oracle release & os release.
HAL9000
"when i do an export for table for 130,000 lines it take 2 h to finish"

Can you post your parfile?

The speed of exports is slow if you are exporting consistent=y on a busy database.

How fast is your disk?
soumia
load exp81 user/paww@sid file=oradba:gmao\export\expG.dmp ROWS=Y log=oradba:gmao\export\expG.log FULL=Y COMPRESS=Y

the os is novell and i have 3 disk off 9 GO
Asad
QUOTE (soumia @ Jul 22 2008, 02:24 PM) *
load exp81 user/paww@sid file=oradba:gmao\export\expG.dmp ROWS=Y log=oradba:gmao\export\expG.log FULL=Y COMPRESS=Y

the os is novell and i have 3 disk off 9 GO



ok, why are you using compress=y option - what purpose this serves you?
soumia
I ADD IT WHEN HAVE SEEN THAT EXPORT TAKE LOT OF TIME BUT IT DIDN'T RESOLVE PROBLEME .
SO PLEASE HELP ME TO FIND SOLUTION.
Asad
QUOTE (soumia @ Jul 22 2008, 02:56 PM) *
I ADD IT WHEN HAVE SEEN THAT EXPORT TAKE LOT OF TIME BUT IT DIDN'T RESOLVE PROBLEME .
SO PLEASE HELP ME TO FIND SOLUTION.


please go through following link.

http://www.orafaq.com/wiki/Import_Export_FAQ

also always use any option only when you understand that; just a friendly suggestion.
SteveC
QUOTE (soumia @ Jul 22 2008, 08:56 AM) *
I ADD IT WHEN HAVE SEEN THAT EXPORT TAKE LOT OF TIME BUT IT DIDN'T RESOLVE PROBLEME .
SO PLEASE HELP ME TO FIND SOLUTION.


You were provided (earlier) a link with information detailing exactly what to do. Do you want to be spoonfed the information here on the forum? What effort are you making to research this on your own by reading documentation? If you had Googled "oracle speed up export," I'm sure more than one of the 200,000+ hits would have worked for you.
HAL9000
http://www.remote-dba.net/teas_rem_util11.htm

there are a few things a DBA can do to expedite exports. These include:

Use Direct Path Direct path exports (DIRECT=Y) allow the export utility to skip the SQL evaluation buffer, whereas the conventional path export executes SQL SELECT statements. With direct path, the data is read from disk into the buffer cache, returning rows directly to the export client. This can offer substantial performance gains, depending on the actual data. When using the direct path, the recordlength parameter should also be used to optimize performance.

Use Subsets By subsetting the data using the QUERY option, the export process is only executed against the data that needs to be exported. If tables have old rows that are never updated, the old data should be exported once, and from that point only the newer data subsets should be exported. Subsets cannot be specified with direct path exports since SQL is necessary to create the subset.

Use a Larger Buffer For conventional path exports, a larger buffer will increase the number of rows that are processed between each physical write to the export file. Fewer physical writes equals greater performance.
soumia
HOW TO Use a Larger Buffer please give me more detail because i have used the option direct=y but i have 2 h for export this table
dave
maybe perhaps, 2 hours is the best you will ever get for this table - ever thought of that?

but have you traced the session - to see where the time is being taken instead of random guesses?
SteveC
And what are the table's datatypes? You may be stuck with conventional export if long/raw/clob is involved.
HAL9000
"HOW TO Use a Larger Buffer please give me more detail"

What, are you incapable of reading English?

If you cannot even look up basic syntax, you have no chance of tuning his export.

Unless we do all of the work for you, that is.

If you turn off direct=y, the buffer parameter may help. The buffer parameter specifies the size, in bytes, of the buffer used to fetch the rows. If 0 is specified, only one row is fetched at a time. This parameter only applies to conventional (non direct) exports.

Start with buffer=80960000, 80 meg:

CODE
load exp81 user/paww@sid file=oradba:gmao\export\expG.dmp BUFFER=80960000 ROWS=Y log=oradba:gmao\export\expG.log FULL=Y COMPRESS=Y
andrew kerber
Guys, he has full=y, so he is doing a full database export, not a table export. Maybe that is the problem.
soumia
THANKS FOR ALL THE BROBLEM IS RESOLVED WITH THE PARAMETER buffer=100000
THANKS
burleson
Hi Soumia,

>> RESOLVED WITH THE PARAMETER buffer=100000

Great!

So, how much faster did it run?
soumia
hello

it tikes 5 min
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.