-
I am currently exporting an Oracle7.3 database every 6 hours. The .dmp file is now at 1.87gigs and takes an hour to export. AND, causes a performance hit to my ssystem.
While reviewing my backup and recovery manuals, I noticed that you can set DIRECT=Y on the export t. THis direct path option is suppose to be significantly faster and does not compete with other resources of the Instance.
It also says this is useful for exporting large large datbases.
Can anyone confirm this before I start testing?
thanks
-
It's true. Setting direct=true bypasses the sga, so it should be faster.
Frank
-
Seems to me like you are expecting a faster EXPORT , in turn the performance.
Would you post the parfile , which you are using to export the full DB ???
Genarally Huge Buffer value makes the export faster as well.
Ditect=Y , will ceratinly improve the speed of export , by bypassing the SQL communication Layer. But it is having some issues...
And its advisable to have the export dump file on a separate disk , where there is less or no I/O activity...
Madhu Reddy
xdollor@yahoo.com
-
Here is the export parameters I am using.
file=nash%1.dmp log=nash%1.log full=y consistent=y
I am taking the default buffer values.
What issues are you seeing with DIRECT?
We are running a raid 5 and I am exporting the file to a seperate logical drive than my data and index files. Only thing on this logical drive is my exports and some historical files.
My box is running 2, p3-400's and i have about 120 users. I am exporting at 10AM which is a busy time, but not our busiest times. We do have no hardware and are going to be upgrading to 9i on it. Just waiting on the vendor for the app.
I just need to reduce the CPU hit and the time it takes to do the export.
-
If you choose conventional mode of export , you will have set huge buffer size than the default value eg: 8192000/4096000
Since you have CONSISTENT=Y and having high DML activity, it will be slow .
If you choose the DIRECT mode: ( DIRECT=Y ), as you asked me, you have the following limitations:
1. NO Interactive mode with DIRECT=Y
2. You Can not export in diect mode with TRANSPORT_TABLESPACES=Y
3. you could not use direct path Export for tables containing objects and LOBs ( In 8i + , uses conventional path )
4. Parametaer QUERY can not be used
5. BUFFER parameter has no effect on direct mode
6. Character set conversion ( session's character set should match with the database character set ).
As you are Exporting the FULL db , you can include the RECORDLENGTH=32*1024 ( 32KB ) along with DIRECT=Y,
This should give better perfromance.
And also DB_BLOCK_SIZE will also matter in export. Larger is the better.
Hope this helps !!!!!
Madhu Reddy
xdollor@yahoo.com
-
I don't know what the default setting for the buffer is. according to my documentaion, its system dependant and is ususally set to a high value(>64000). are you saying I should include a buffer setting in my line of 8megs?
Tried the direct export and got the characeter set error. Trying to figure out now how to work around that.
how does the recordlength help?
items 1-4 of the limitations are not applicable in my 7.3.4 system. Only the recordset is an issue and I have to figure it out. The error I got was exp00041: Export done in serve's US7SASCII, different from user's character set WE8ISO8859P1. Which user character set?
-
For the charachter set errors you may want to try...
When there are character set errors try using
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
export NLS_LANG
from a Unix prompt.
My understanding og the BUFFER parameter is that it should be used in conjunction with COMMIT, Ex..
BUFFER=4000000
COMMIT=Y
With your buffer at 4 million bytes you will be commiting every 4 megs (roughly). This is better than the oracle default which is to try and import the entire table before a commit. You will also run the risk of blowing out your rollback segs, or tablespace if it is too small.
Hop this helps
MH
I remember when this place was cool.
-
The main question I see here is: Why on earth would someone want to take full export of a database every six hours? And with CONSISTENT=Y on top of that!!??
If this is your main mean of backup then your backup-recovery scenario simply sucks. If it is not, then why would you do it 4 times a day?
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
testing the speed of teh direct now.
To fix the error, I went into my Windows system environment and set two variables nls_lang=american, nls_nchar=US7ASCII.
I used these values because a select * from sys.prop$ showed them to be the databases current character set info.
Once I did that, the direct=y worked and database is exporting as I type this.
Interested to see the time comparisons. Granted, its on a new box(4, p3-700's) and I am the only user. Still..
-
Originally posted by Mr.Hanky
COMMIT=Y
With your buffer at 4 million bytes you will be commiting every 4 megs (roughly). This is better than the oracle default which is to try and import the entire table before a commit. You will also run the risk of blowing out your rollback segs, or tablespace if it is too small.
Of course, you realize Mr.H is taking about import here and not export.
And, if we check the docs ( http://technet.oracle.com/docs/produ...ch01.htm#46045 ) we notice that BUFFER does not apply for DIRECT export.
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|