FAQ
I've got a massive import that takes a long time. This is one I'd love
to drop and recreate the index, but that alone would probably be almost
a terabyte, so that is out of the question.



This is a table partitioned by month and a normal partition is about
20-24GB. As it's coming in, it's getting a lot of I/O wait on the index
datafiles, but only the datafiles of that partition. The index is also
partitioned.



This is now 9.2.0.6, so just how high can you set up the buffer?



Are there any other "tricks"?



As I get to the "end" of the partition my import rate is getting to be
2-3GB per day.



Yuck!!!





Michael Kline
Database Administration
SunTrust Technology Center
1030 Wilmer Avenue
Richmond, Virginia 23227
Outside 804.261.9446
STNet 643.9446

Cell 804.744.1545
michael.kline_at_suntrust.com




LEGAL DISCLAIMER

The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer.


Seeing Beyond Money is a service mark of SunTrust Banks, Inc.
[ST:XCL]

Search Discussions

  • Connor McDonald at Jul 21, 2005 at 9:32 am
    Hi all,

    In a level 4 buffer dump, in v9 I get

    (WS_REPL_LIST)

    (WS_WRITE_LIST)

    (WS_XOBJ_LIST)

    (WS_XRNG_LIST)

    whereas in v10 I get

    (WS_REPL_LIST)

    (WS_WRITE_LIST)

    (WS_XOBJ_LIST)

    (WS_XRNG_LIST)

    (WS_REQ_LIST)

    Does anyone know what the "REQ" list is ?

    Cheers
    Connor
  • Michael McMullen at Jul 21, 2005 at 9:42 am
    Are they local indexes?
    How about importing into another table, add the indexes and use exchange
    partition, it's probablly your best bet?
    I don't know if you would be able to import right into the table by marking
    the local index unusable.
    ----- Original Message -----
    From: Kline.Michael
    To: oracle-l_at_freelists.org
    Sent: Thursday, July 21, 2005 10:00 AM
    Subject: Huge import takes a long time

    I've got a massive import that takes a long time. This is one I'd love to
    drop and recreate the index, but that alone would probably be almost a
    terabyte, so that is out of the question.

    This is a table partitioned by month and a normal partition is about
    20-24GB. As it's coming in, it's getting a lot of I/O wait on the index
    datafiles, but only the datafiles of that partition. The index is also
    partitioned.

    This is now 9.2.0.6, so just how high can you set up the buffer?

    Are there any other "tricks"?

    As I get to the "end" of the partition my import rate is getting to be 2-3GB
    per day.

    Yuck!!!

    Michael Kline
    Database Administration
    SunTrust Technology Center
    1030 Wilmer Avenue
    Richmond, Virginia 23227
    Outside 804.261.9446
    STNet 643.9446
    Cell 804.744.1545
    michael.kline_at_suntrust.com

    LEGAL DISCLAIMER

    The information transmitted is intended solely for the individual or entity
    to which it is addressed and may contain confidential and/or privileged
    material. Any review, retransmission, dissemination or other use of or
    taking action in reliance upon this information by persons or entities other
    than the intended recipient is prohibited. If you have received this email
    in error please contact the sender and delete the material from any
    computer.

    Seeing Beyond Money is a service mark of SunTrust Banks, Inc.
    [ST:XCL]

    --
    http://www.freelists.org/webpage/oracle-l
  • Powell, Mark D at Jul 21, 2005 at 9:48 am
    And the import parameters in use are?


    Mark D Powell --

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Kline.Michael
    Sent: Thursday, July 21, 2005 10:00 AM
    To: oracle-l_at_freelists.org
    Subject: Huge import takes a long time

    I've got a massive import that takes a long time. This is one I'd love
    to drop and recreate the index, but that alone would probably be almost
    a terabyte, so that is out of the question.



    This is a table partitioned by month and a normal partition is about
    20-24GB. As it's coming in, it's getting a lot of I/O wait on the index
    datafiles, but only the datafiles of that partition. The index is also
    partitioned.



    This is now 9.2.0.6, so just how high can you set up the buffer?



    Are there any other "tricks"?



    As I get to the "end" of the partition my import rate is getting to be
    2-3GB per day.



    Yuck!!!





    Michael Kline
    Database Administration
    SunTrust Technology Center
    1030 Wilmer Avenue
    Richmond, Virginia 23227
    Outside 804.261.9446
    STNet 643.9446

    Cell 804.744.1545
    michael.kline_at_suntrust.com






    LEGAL DISCLAIMER

    The information transmitted is intended solely for the individual or
    entity to which it is addressed and may contain confidential and/or
    privileged material. Any review, retransmission, dissemination or other
    use of or taking action in reliance upon this information by persons or
    entities other than the intended recipient is prohibited. If you have
    received this email in error please contact the sender and delete the
    material from any computer.


    Seeing Beyond Money is a service mark of SunTrust Banks, Inc.

    [ST:XCL]
  • Marquez, Chris at Jul 21, 2005 at 10:22 am
    Without knowing or looking at the specifics of your situation or the limitations of partition IMPorts, these are small and large things that have helped me and I have used many times in the past and even within the last week to complete a large db migration to a new platform (boy, I will be glad when RMAN can just move/copy/clone (data) files from OS-a to OS-b!!!)

    Be forewarned about using "_[parameter]" parameters!
    Using them and having a db/server crash during use, means corrupt database for sure.
    Un-set them when finished *AND* make sure you can switch / cycle all redo (arch logs)...test, test, test.
    You have been warned.

    Good luck...hth

    Chris Marquez
    Oracle DBA

    Faster IMPORTS

    Set IMP parameter COMMIT = Y.
    Set IMP parameter RECORDLENGTH >= EXP RECORDLENGTH value.
    Set IMP parameter BUFFER = MB (Set in the MB range not KB range)

    Set IMP parameter STATISTICS = NONE (9i, n/a 8i)

    Set IMP parameter RECALCULATE_STATISTICS = N. (8i, n/a 9i)
    Set IMP parameter ANALYZE = N. (8i, n/a 9i)

    Set IMP parameter INDEXES = N (Import them later...separately)

    Set init.ora parameter LOG_ARCHIVE_START = FALSE *&* alter database noarchivelog;
    Set init.ora parameter _disable_logging = TRUE (Warning, this could be dangerous and unrecoverable in failure. Backup immediately after import - * Redo records (to disk) will NOT be generated (redo WAS generated in the log buffer.) [Even with _disable_logging you still "switch" logs when they get "full"])
    Set init.ora parameter _wait_for_sync = FALSE (Warning, this could be dangerous and unrecoverable in failure. Backup immediately after import - _wait_for_sync: Wait_for_sync is an oracle generic parameter which, when set to false, will allow the system to complete commits without waiting for the redo-log buffer flushes to complete.)

    Make redo logs enormous; 500MB, 1GB, etc.

    Use Locally Managed Tablespaces on target database.

    Chris Marquez
    Oracle DBA
    C-(703)507-1421
    cmarquez_at_capwiz.com

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org on behalf of Kline.Michael
    Sent: Thu 7/21/2005 10:00 AM
    To: oracle-l_at_freelists.org
    Subject: Huge import takes a long time


    I've got a massive import that takes a long time. This is one I'd love
    to drop and recreate the index, but that alone would probably be almost
    a terabyte, so that is out of the question.



    This is a table partitioned by month and a normal partition is about
    20-24GB. As it's coming in, it's getting a lot of I/O wait on the index
    datafiles, but only the datafiles of that partition. The index is also
    partitioned.



    This is now 9.2.0.6, so just how high can you set up the buffer?



    Are there any other "tricks"?



    As I get to the "end" of the partition my import rate is getting to be
    2-3GB per day.



    Yuck!!!





    Michael Kline
    Database Administration
    SunTrust Technology Center
    1030 Wilmer Avenue
    Richmond, Virginia 23227
    Outside 804.261.9446
    STNet 643.9446

    Cell 804.744.1545
    michael.kline_at_suntrust.com




    LEGAL DISCLAIMER

    The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer.


    Seeing Beyond Money is a service mark of SunTrust Banks, Inc.
    [ST:XCL]









    --
    http://www.freelists.org/webpage/oracle-l
  • Mohan, Ross at Jul 21, 2005 at 11:07 am
    I smell a global/nonpartitioned index on a partitioned table.


    Yuck.


    First thing I'd be doing (did, here) is get rid of that thing.
    It may well take some application love, but really really really
    worth it.


    We had a 1.2Billion row index on a partitioned table, and I slept
    sooo much better after we partitioned it.



    -----Original Message-----

    From: oracle-l-bounce_at_freelists.org On Behalf Of Marquez, Chris
    Sent: Thursday, July 21, 2005 11:17 AM
    To: Michael.Kline_at_SunTrust.com; oracle-l_at_freelists.org
    Subject: RE: Huge import takes a long time

    Without knowing or looking at the specifics of your situation or the limitations of partition IMPorts, these are small and large things that have helped me and I have used many times in the past and even within the last week to complete a large db migration to a new platform (boy, I will be glad when RMAN can just move/copy/clone (data) files from OS-a to OS-b!!!)

    Be forewarned about using "_[parameter]" parameters!
    Using them and having a db/server crash during use, means corrupt database for sure.
    Un-set them when finished *AND* make sure you can switch / cycle all redo (arch logs)...test, test, test.
    You have been warned.

    Good luck...hth

    Chris Marquez
    Oracle DBA

    -----------------------
    Faster IMPORTS
    -----------------------
    Set IMP parameter COMMIT = Y.
    Set IMP parameter RECORDLENGTH >= EXP RECORDLENGTH value.
    Set IMP parameter BUFFER = MB (Set in the MB range not KB range)

    Set IMP parameter STATISTICS = NONE (9i, n/a 8i)

    Set IMP parameter RECALCULATE_STATISTICS = N. (8i, n/a 9i)
    Set IMP parameter ANALYZE = N. (8i, n/a 9i)

    Set IMP parameter INDEXES = N (Import them later...separately)

    Set init.ora parameter LOG_ARCHIVE_START = FALSE *&* alter database noarchivelog;
    Set init.ora parameter _disable_logging = TRUE (Warning, this could be dangerous and unrecoverable in failure. Backup immediately after import - * Redo records (to disk) will NOT be generated (redo WAS generated in the log buffer.) [Even with _disable_logging you still "switch" logs when they get "full"])
    Set init.ora parameter _wait_for_sync = FALSE (Warning, this could be dangerous and unrecoverable in failure. Backup immediately after import - _wait_for_sync: Wait_for_sync is an oracle generic parameter which, when set to false, will allow the system to complete commits without waiting for the redo-log buffer flushes to complete.)

    Make redo logs enormous; 500MB, 1GB, etc.

    Use Locally Managed Tablespaces on target database.

    Chris Marquez
    Oracle DBA
    C-(703)507-1421
    cmarquez_at_capwiz.com

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org on behalf of Kline.Michael
    Sent: Thu 7/21/2005 10:00 AM
    To: oracle-l_at_freelists.org
    Subject: Huge import takes a long time

    I've got a massive import that takes a long time. This is one I'd love
    to drop and recreate the index, but that alone would probably be almost
    a terabyte, so that is out of the question.

    This is a table partitioned by month and a normal partition is about
    20-24GB. As it's coming in, it's getting a lot of I/O wait on the index
    datafiles, but only the datafiles of that partition. The index is also
    partitioned.

    This is now 9.2.0.6, so just how high can you set up the buffer?

    Are there any other "tricks"?

    As I get to the "end" of the partition my import rate is getting to be
    2-3GB per day.

    Yuck!!!

    Michael Kline
    Database Administration
    SunTrust Technology Center
    1030 Wilmer Avenue
    Richmond, Virginia 23227
    Outside 804.261.9446
    STNet 643.9446

    Cell 804.744.1545
    michael.kline_at_suntrust.com

    LEGAL DISCLAIMER
    The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer.

    Seeing Beyond Money is a service mark of SunTrust Banks, Inc.
    [ST:XCL]

    --
    http://www.freelists.org/webpage/oracle-l
  • Kline.Michael at Jul 22, 2005 at 5:32 am
    The bummer is, while they call it a month, they set the same date to all
    transactions. But there may be some other way to do this.

    I exported the partition from production and then tried to import into
    test, which had a matching partition/table.

    The index is partitioned, and it seems that ONLY that index partition is
    getting hit as it is in a separate tablespace.

    Yet, curiously enough, the tax on the syatem is pretty much nothing.

    I also thought if they could have found some "division", they may have
    been able to use Pl/Sql routines and do "batch inserts" from one box to
    the other, but then again, may still have had the index problem. Not
    sure.

    Michael Kline
    Database Administration
    Outside 804.261.9446
    Cell 804.744.1545
    3-9446

    -----Original Message-----
    From: Dennis Williams
    Sent: Thursday, July 21, 2005 8:37 PM
    To: Kline.Michael
    Cc: oracle-l_at_freelists.org
    Subject: Re: Huge import takes a long time

    Michael,

    Partition by day, a month is too large. You can direct path import
    into a small partition, build the local index on that partition, then
    exchange partition that partition into your real partitioned table.

    Dennis Williams
    On 7/21/05, Kline.Michael wrote:





    I've got a massive import that takes a long time. This is one I'd
    love to
    drop and recreate the index, but that alone would probably be almost
    a
    terabyte, so that is out of the question.



    This is a table partitioned by month and a normal partition is about
    20-24GB. As it's coming in, it's getting a lot of I/O wait on the
    index
    datafiles, but only the datafiles of that partition. The index is
    also
    partitioned.



    This is now 9.2.0.6, so just how high can you set up the buffer?



    Are there any other "tricks"?



    As I get to the "end" of the partition my import rate is getting to
    be 2-3GB
    per day.



    Yuck!!!





    Michael Kline
    Database Administration
    SunTrust Technology Center
    1030 Wilmer Avenue
    Richmond, Virginia 23227
    Outside 804.261.9446
    STNet 643.9446

    Cell 804.744.1545
    michael.kline_at_suntrust.com





    LEGAL DISCLAIMER
    The information transmitted is intended solely for the individual or
    entity
    to which it is addressed and may contain confidential and/or
    privileged
    material. Any review, retransmission, dissemination or other use of
    or
    taking action in reliance upon this information by persons or
    entities other
    than the intended recipient is prohibited. If you have received this
    email
    in error please contact the sender and delete the material from any
    computer.

    Seeing Beyond Money is a service mark of SunTrust Banks, Inc.
    [ST:XCL]

    --
    http://www.freelists.org/webpage/oracle-l
  • Dennis Williams at Jul 22, 2005 at 8:13 am
    Michael
    On 7/22/05, Kline.Michael wrote:
    The bummer is, while they call it a month, they set the same date to all
    transactions. But there may be some other way to do this.
    There has to be some criteria you can use to reduce the partition size.
    The index is partitioned, and it seems that ONLY that index partition is
    getting hit as it is in a separate tablespace.
    We know inserts with a partition present take longer. This is proven.
    I'm suggesting you find a way to avoid inserting into the index, like
    direct path load into a separate table, build the index, then use
    exchange partition to swap it into the large partitioned table. Many
    people have saved enormous amounts of time with a scheme like this.

    Dennis Williams

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 21, '05 at 9:01a
activeJul 22, '05 at 8:13a
posts8
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase