EXP, consistent=y equals large rollback segs, why?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: EXP, consistent=y equals large rollback segs, why?

  1. #1
    Join Date
    Aug 2002
    Location
    Brighton, England
    Posts
    93

    EXP, consistent=y equals large rollback segs, why?

    Re: Export utility and option CONSISTENT

    http://download-west.oracle.com/docs...ch01.htm#20622

    Extract:
    If you use CONSISTENT=Y and the volume of updates is large, the rollback segment will be large. In addition, the export of each table will be slower because the rollback segment must be scanned for uncommitted transactions.



    I don't understand why the rollback seg should be large since if CONSISTENT=Y this should block changes taking place on the relevant nested/partitioned table until that table has been exported. Or are these changes put on hold/queued and then applied immediately after the export of the relevant object. Perhaps someone could clarify.



    Gus

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    Hey Gus;

    Not sure what you mean by queued. I think any updates on those tables would generate (and be stored in) rollback. Than applied after the table is released. I think that is what Oracle is saying here.

    "If you use CONSISTENT=y and the volume of updates is large, the rollback segment usage will be large. In addition, the export of each table will be slower because the rollback segment must be scanned for uncommitted transactions."

    MH
    Last edited by Mr.Hanky; 01-05-2004 at 01:28 PM.
    I remember when this place was cool.

  3. #3
    Join Date
    Aug 2002
    Location
    Brighton, England
    Posts
    93
    So if an export was taking place and CONSISTENT=Y, then you are saying a user who wanted to update a partition of a partitioned table would be allowed to submit a txn updating the table but that the new data from the txn would be stored in the rollback seg until it was clear (export of the partitioned table had completed) to apply the changes to the buffer held copy (req by the export) and ultimately the datafile.

    Regds
    Gus

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If a consistent export is started then the export session has to read data as it was at the beginning of the export, regardless of any changes made. Therefore any changes made to the tables since the start of the export must be preserved in rollback segments until the export finishes.

    An inconsistent export does a similar thing, but only for one table at a time -- ie. Changes made to table B while table A is being exported need not be preserved in rollback. Table B's changes only need to be preserved once table B is being exported.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Aug 2002
    Location
    Brighton, England
    Posts
    93
    Thanks guys, that seems to make sense with what the doc was saying, just wanted to be sure I was reading it correctly.

    Gus

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by Gus
    . . . the new data from the txn would be stored in the rollback seg until it was clear . . . . to apply the changes to the buffer held copy . . . . and ultimately the datafile.
    This doesn't read right to me (I hope I haven't changed your meaning by stripping off bits.)

    My understanding is that the changes are always applied the same way, independant of any process requiring a consistant view (an export or any other long running select). Which includes updating the buffer immediately and the datafile whenever Oracle sees fit (check-pointing etc). There is no block nor hold nor queue that I know of.

    The process requiring the consistant view needs to know not to "believe" what it sees in buffers or on disk, but instead take any relevant before images from the rollback segments. If the rollback is not big enough, I presume this the process that will get an error ("snapshot to old"?) - rather than blocking the updates because you have run out of rollback.
    Last edited by DaPi; 01-06-2004 at 10:09 AM.

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: EXP, consistent=y equals large rollback segs, why?

    CONSISTENT=Y triggers Oracle to run SET TRANSACTION READ ONLY before exporting every single table or a partition of a table. That's all. Thus, for partitioned tables, even CONSISTENT=Y will not garantee that export is consistent.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: Re: EXP, consistent=y equals large rollback segs, why?

    Originally posted by julian
    CONSISTENT=Y triggers Oracle to run SET TRANSACTION READ ONLY before exporting every single table or a partition of a table. That's all. Thus, for partitioned tables, even CONSISTENT=Y will not garantee that export is consistent.
    I believe that it's "serializable",not "read only".
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: Re: Re: EXP, consistent=y equals large rollback segs, why?

    Originally posted by slimdave
    I believe that it's "serializable",not "read only".
    It's READ ONLY, not SERIALIZABLE.

    SET TRANSACTION READ ONLY does two things, it inititiates a transaction, and it assigns a start SCN for the transaction. That start SCN will be used for any query executed in the course of the transaction.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

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