This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Dec 23, 2012 9:48 AM by rp0428 RSS

Insert /*+ append*/ into TableName Select /*+ parallel(t,4,1)*/ * from Tabl

user8941550 Newbie
Currently Being Moderated
Hi,

I am using
Insert  /*+ append */   into TableNew select  /*+ parallel( t,4,1) */  * from TableOld t
to load data into a staging table- TableNew
from another table of same structure - TableOld

It's taking me more than 4-5 hours to perform this operation for around 1.5 million rows with a column defined as XMLType as well.

- Are there any rules for determining the degree of parallelism or is it just hit and trial?
- Or don't define any and leave it to the optimizer?
- Any other rule I can keep in mind for trying to optimize this operation?

Thanks..

Edited by: BluShadow on 06-Dec-2012 11:20
added {noformat}
{noformat} tags for readability.  Read: {message:id=9360002} and learn to do this yourself                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 1. Re: Insert /*+ append*/ into TableName Select /*+ parallel(t,4,1)*/ * from Tabl
    BluShadow Guru Moderator
    Currently Being Moderated
    user8941550 wrote:
    - Or don't define any and leave it to the optimizer?
    That would be my first option... and then if it's not performing well, I would look at the reasons why, and see if something like parallelism would help.

    Not sure how well the direct insert (append hint) will work along with parallel queries.
  • 2. Re: Insert /*+ append*/ into TableName Select /*+ parallel(t,4,1)*/ * from Tabl
    user8941550 Newbie
    Currently Being Moderated
    So I tried,

    Insert /*+ append */ into TableNew select * from TableOld t
    and it executes within one hour now.

    Any explanation for this.
  • 3. Re: Insert /*+ append*/ into TableName Select /*+ parallel(t,4,1)*/ * from Tabl
    Bawer Journeyer
    Currently Being Moderated
    user8941550 wrote:
    So I tried,

    Insert /*+ append */ into TableNew select * from TableOld t
    and it executes within one hour now.

    Any explanation for this.
    I don't recommend this way (insert into .. select ..) for big tables.
    Because you have no possibility to run a commit while inserting (first at the end of insert process you can commit).
    It taking much more UNDO-Space (if you run rollback after insert statement).

    A better way is, define a cursor, read for countered rows (for example 1000 rows), after each 1000 rows run a commit.
    Make sure the new table has no activated constraints or triggers while inserting.
  • 4. Re: Insert /*+ append*/ into TableName Select /*+ parallel(t,4,1)*/ * from Tabl
    user8941550 Newbie
    Currently Being Moderated
    Thanks for the response Bawer.
    But what I am looking for is either complete Insert or No Insert.
    So I can't use cursors in between to Insert.
    Time is very important so I use Append.

    BUT

    If I remove Select /*+ parallel(t,4,1)*/ * from TableName , it gets very faster. I was looking for this answer that why is parallel making is slower here.

    Thanks..
  • 5. Re: Insert /*+ append*/ into TableName Select /*+ parallel(t,4,1)*/ * from Tabl
    Paul Horth Expert
    Currently Being Moderated
    Bawer wrote:
    user8941550 wrote:
    So I tried,

    Insert /*+ append */ into TableNew select * from TableOld t
    and it executes within one hour now.

    Any explanation for this.
    I don't recommend this way (insert into .. select ..) for big tables.
    Because you have no possibility to run a commit while inserting (first at the end of insert process you can commit).
    It taking much more UNDO-Space (if you run rollback after insert statement).

    A better way is, define a cursor, read for countered rows (for example 1000 rows), after each 1000 rows run a commit.
    Make sure the new table has no activated constraints or triggers while inserting.
    No, that's probably the worst possible way.

    Best and quickest way is insert...select.
  • 6. Re: Insert /*+ append*/ into TableName Select /*+ parallel(t,4,1)*/ * from Tabl
    Paul Horth Expert
    Currently Being Moderated
    user8941550 wrote:
    Thanks for the response Bawer.
    But what I am looking for is either complete Insert or No Insert.
    So I can't use cursors in between to Insert.
    Time is very important so I use Append.

    BUT

    If I remove Select /*+ parallel(t,4,1)*/ * from TableName , it gets very faster. I was looking for this answer that why is parallel making is slower here.

    Thanks..
    Who said parallel would make it faster? It may, if you have the resources (CPU and I/O bandwidth), but it may not if you saturate one or both of those resources.

    Looks like your configuration doesn't have the power to handle that level of parallelism.
  • 7. Re: Insert /*+ append*/ into TableName Select /*+ parallel(t,4,1)*/ * from Tabl
    BluShadow Guru Moderator
    Currently Being Moderated
    Bawer wrote:
    user8941550 wrote:
    So I tried,

    Insert /*+ append */ into TableNew select * from TableOld t
    and it executes within one hour now.

    Any explanation for this.
    I don't recommend this way (insert into .. select ..) for big tables.
    Because you have no possibility to run a commit while inserting (first at the end of insert process you can commit).
    It taking much more UNDO-Space (if you run rollback after insert statement).
    But committing in blocks or chunks is not transactionally sound and may cause more problems later on if there is an error in one of the blocks of inserts, as you won't be able to easily rollback the data that has already been committed.
    I very much recommend using a single INSERT .. SELECT .. statement in most cases (there's always going to be rare exceptions), simply to maintain transactional integrity.
    A better way is, define a cursor, read for countered rows (for example 1000 rows), after each 1000 rows run a commit.
    That's a great way to a) slow down the process and b) make it hard to rollback if an issue occurs.
    Each time you issue a commit, it tells Oracle to write the data to the datafiles. That is performed by writer processes. By default the database starts up with N number of writer processes ("N" is depending on the database configuration, so let's assume it 4 writer processes as an example). Each time a commit is issued, a writer process gets allocated the task of writing that set of data to the data files. The workload is shared between these processes, so if another commit is issued and one writer process is busy already, then another process will take up that request. If the database starts getting lots of commits being issued, and finds that all the existing writer processes are busy, it will spawn new writer processes to deal with the new requests, taking up more server resources (memory, file and process handles etc.), and the more writer processes there are the more chance they have of creating wait states between them as they all try and write to the same data files (especially if the data being committed is closely related e.g. same table and tablespace etc.). These resources are not released again when the tasks are complete, until the server is rebooted or Oracle shutdown, as Oracle keeps them there with the expectation it will get the same kind of workload again. Oracle is perfectly capable of processing millions of rows, so suggesting that it is "a better way" to insert in chunks of 1000 records at a time, committing after each, not only slows the process down from a pl/sql code point of view, but also slows things down from a server resources point of view, as well as causing potential file i/o contention issues... even down to the possibility of causing disk i/o contention issues. That certainly is NOT "a better way".

    Such suggestions are generally given by people who do not understand the underlying architecture of Oracle databases, something which is taught on Oracle DBA courses, and that's a course I'd recommend any would-be professional developer attends, even if they have no intention of being a DBA. Knowing how a database's architecture works is undoubtably valuable to knowing how to write good code... not just in Oracle, but in other rdbms' as well (when I was an Ingres database developer, I also attended the Ingres DBA courses and that was also valuable to undersand how to write good code).
  • 8. Re: Insert /*+ append*/ into TableName Select /*+ parallel(t,4,1)*/ * from Tabl
    Bawer Journeyer
    Currently Being Moderated
    BluShadow wrote:
    Bawer wrote:
    user8941550 wrote:
    So I tried,

    Insert /*+ append */ into TableNew select * from TableOld t
    and it executes within one hour now.

    Any explanation for this.
    I don't recommend this way (insert into .. select ..) for big tables.
    Because you have no possibility to run a commit while inserting (first at the end of insert process you can commit).
    It taking much more UNDO-Space (if you run rollback after insert statement).
    But committing in blocks or chunks is not transactionally sound and may cause more problems later on if there is an error in one of the blocks of inserts, as you won't be able to easily rollback the data that has already been committed.
    I didn't mean do this and all is good. of course you can do this only if you know what you do. you musst check errors in any way.

    If constraints aren't important you can drop/deactivate all, which means you don't need to data integrity at this time (at this time = first load)
    before you create the constraints/activate you can check errors because of INSERT along cann't issue a transaction problem if thes are no constraints/pk/fk/trigger activated.
    I very much recommend using a single INSERT .. SELECT .. statement in most cases (there's always going to be rare exceptions), simply to maintain transactional integrity.
    A better way is, define a cursor, read for countered rows (for example 1000 rows), after each 1000 rows run a commit.
    That's a great way to a) slow down the process and b) make it hard to rollback if an issue occurs.
    Each time you issue a commit, it tells Oracle to write the data to the datafiles. That is performed by writer processes...
    do you mean to keep 100+Million rows in UNDO is a better way? I don't think so sorry. we can increate it to 10.000 or 50.000 depends on the system.
    Such suggestions are generally given by people who do not understand the underlying architecture of Oracle databases, something which is taught on Oracle DBA courses, and that's a course I'd recommend any would-be professional developer attends, even if they have no intention of being a DBA. Knowing how a database's architecture works is undoubtably valuable to knowing how to write good code... not just in Oracle, but in other rdbms' as well (when I was an Ingres database developer, I also attended the Ingres DBA courses and that was also valuable to undersand how to write good code).
    peoples can discuss about the methods, but what is this? didn't you exaggerate ?? do you think you are the unique dba on the world and there is only 11g in business world or what?
  • 9. Re: Insert /*+ append*/ into TableName Select /*+ parallel(t,4,1)*/ * from Tabl
    Paul Horth Expert
    Currently Being Moderated
    Bawer wrote:
    BluShadow wrote:
    Bawer wrote:
    user8941550 wrote:
    So I tried,

    Insert /*+ append */ into TableNew select * from TableOld t
    and it executes within one hour now.

    Any explanation for this.
    I don't recommend this way (insert into .. select ..) for big tables.
    Because you have no possibility to run a commit while inserting (first at the end of insert process you can commit).
    It taking much more UNDO-Space (if you run rollback after insert statement).
    But committing in blocks or chunks is not transactionally sound and may cause more problems later on if there is an error in one of the blocks of inserts, as you won't be able to easily rollback the data that has already been committed.
    I didn't mean do this and all is good. of course you can do this only if you know what you do. you musst check errors in any way.

    If constraints aren't important you can drop/deactivate all, which means you don't need to data integrity at this time (at this time = first load)
    before you create the constraints/activate you can check errors because of INSERT along cann't issue a transaction problem if thes are no constraints/pk/fk/trigger activated.
    I very much recommend using a single INSERT .. SELECT .. statement in most cases (there's always going to be rare exceptions), simply to maintain transactional integrity.
    A better way is, define a cursor, read for countered rows (for example 1000 rows), after each 1000 rows run a commit.
    That's a great way to a) slow down the process and b) make it hard to rollback if an issue occurs.
    Each time you issue a commit, it tells Oracle to write the data to the datafiles. That is performed by writer processes...
    do you mean to keep 100+Million rows in UNDO is a better way? I don't think so sorry. we can increate it to 10.000 or 50.000 depends on the system.
    Such suggestions are generally given by people who do not understand the underlying architecture of Oracle databases, something which is taught on Oracle DBA courses, and that's a course I'd recommend any would-be professional developer attends, even if they have no intention of being a DBA. Knowing how a database's architecture works is undoubtably valuable to knowing how to write good code... not just in Oracle, but in other rdbms' as well (when I was an Ingres database developer, I also attended the Ingres DBA courses and that was also valuable to undersand how to write good code).
    peoples can discuss about the methods, but what is this? didn't you exaggerate ?? do you think you are the unique dba on the world and there is only 11g in business world or what?
    No, he's not exaggerating. Your method is wrong for the reasons given.

    The amount of UNDO used by insert is not that great and if you are inserting 100+ million rows you size your system/undo/redo etc. accordingly.

    He is not unique but BluShadow does know what he is talking about.

    Edited by: Paul Horth on Dec 19, 2012 12:59 PM
    I've changed my mind - he is unique of course but there are other DBAs with equivalent knowledge.
  • 10. Re: Insert /*+ append*/ into TableName Select /*+ parallel(t,4,1)*/ * from Tabl
    krzysztof.lorencki Explorer
    Currently Being Moderated
    >
    do you mean to keep 100+Million rows in UNDO is a better way? I don't think so sorry. we can increate it to 10.000 or 50.000 depends on the system
    >
    APPEND hint forces ORACLE to use direct path for INSERT statement. When you use the APPEND hint for INSERT, data is simply appended to a table above the HWM which has the effect of not creating UNDO. Existing free space in blocks is not used and this can be a problem (you can truncate table before INSERT statement to avoid this). As BluShadow wrote:
    >
    Such suggestions are generally given by people who do not understand the underlying architecture of Oracle databases.
  • 11. Re: Insert /*+ append*/ into TableName Select /*+ parallel(t,4,1)*/ * from Tabl
    Bawer Journeyer
    Currently Being Moderated
    Paul  Horth wrote:
    No, he's not exaggerating. Your method is wrong for the reasons given.
    It is not hard to test it, I will post the test result next time even if I am wrong.

    Edited by: Bawer on 19.12.2012 14:20

    Ok guys you are right. I haven't tested but doc says:

    Direct-path INSERT generates both redo and undo for metadata changes, because these are needed for operation recovery. For data changes, undo and redo are generated as follows:

    Direct-path INSERT always bypasses undo generation for data changes.

    If the database is not in ARCHIVELOG or FORCE LOGGING mode, then no redo is generated for data changes, regardless of the logging setting of the table.
    ....
  • 12. Re: Insert /*+ append*/ into TableName Select /*+ parallel(t,4,1)*/ * from Tabl
    BluShadow Guru Moderator
    Currently Being Moderated
    Paul  Horth wrote:
    No, he's not exaggerating. Your method is wrong for the reasons given.

    The amount of UNDO used by insert is not that great and if you are inserting 100+ million rows you size your system/undo/redo etc. accordingly.

    He is not unique but BluShadow does know what he is talking about.

    Edited by: Paul Horth on Dec 19, 2012 12:59 PM
    I've changed my mind - he is unique of course but there are other DBAs with equivalent knowledge.
    A compliment whichever way... you're too kind. :)
  • 13. Re: Insert /*+ append*/ into TableName Select /*+ parallel(t,4,1)*/ * from Tabl
    Bawer Journeyer
    Currently Being Moderated
    Paul  Horth wrote:

    He is not unique but BluShadow does know what he is talking about.

    Edited by: Paul Horth on Dec 19, 2012 12:59 PM
    I've changed my mind - he is unique of course but there are other DBAs with equivalent knowledge.
    there are definetly other DBAs with much more knowledge.

    don't forget:
    the best book has not been written yet.
    because the best people have no time to write a book...
  • 14. Re: Insert /*+ append*/ into TableName Select /*+ parallel(t,4,1)*/ * from Tabl
    Bawer Journeyer
    Currently Being Moderated
    user8941550 wrote:

    If I remove Select /*+ parallel(t,4,1)*/ * from TableName , it gets very faster. I was looking for this answer that why is parallel making is slower here.

    Thanks..
    according to definition of Direct-Path-Insert, I assume, parallelism won't help to you (to get a faster insert) in this case. There are no commit until inserting finishs. It seems like a stream (continiously writing at the end of datafile), and you shouldn't interrupt it.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points