Export Direct=Y
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 39

Thread: Export Direct=Y

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Posts
    99
    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

  2. #2
    Join Date
    Sep 2000
    Posts
    19
    It's true. Setting direct=true bypasses the sga, so it should be faster.

    Frank

  3. #3
    Join Date
    Feb 2001
    Posts
    290
    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

  4. #4
    Join Date
    Feb 2001
    Posts
    99
    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.

  5. #5
    Join Date
    Feb 2001
    Posts
    290


    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

  6. #6
    Join Date
    Feb 2001
    Posts
    99
    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?

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    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.

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  9. #9
    Join Date
    Feb 2001
    Posts
    99
    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..

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width