Home » RDBMS Server » Performance Tuning » How to make inserts faster (Oracle10g)
How to make inserts faster [message #317569] Fri, 02 May 2008 00:15 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
We have a few general suggetions to make a 'select' query faster (a few examples are by having an index, avoiding UNION preferring UNION ALL, using exists instead of IN..), similarly do we have a list of general suggestion or checklist to ensure that our INSERT are also run faster ? One point I could think of is dropping/disabling the index during insert and rebuild it later.
Please enlighten me.
Re: How to make inserts faster [message #317577 is a reply to message #317569] Fri, 02 May 2008 01:26 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
This is the code snippet. I currently don't have the Explain plan for this. I will try to get it.

              BEGIN
                  FORALL l_nm_loop IN l_nm_start_cnt..l_nm_end_cnt
                     INSERT INTO TVD_TL_PIP_AMOUNT (PIP_AMOUNT_ID, VALUATION_ITERATION_ID, REATTRBN_KEYDATE_ELIGIBLITY_ID,
                                                    SCALED_AMOUNT, SWITCHABLE_AMOUNT, HYB_SWITCHABLE_AMOUNT,
                                                    TOTAL_AMOUNT, MINIMUM_ADDITION_AMOUNT, FINAL_PIP_AMOUNT, MINIMUM_APPLIED_IND,
                                                    CREATE_DATE_TIME, CREATE_USER_ID,
                                                    LAST_UPDATE_DATE_TIME, UPDATE_USER_ID)
                                            VALUES (TVD_TL_PIP_AMOUNT_SEQ.NEXTVAL, l_nm_valuation_iteration_id, TO_NUMBER (g_a100_sm_reckey (l_nm_loop)),
                                                    g_a242_sm_scaled_pip (l_nm_loop), g_a242_sm_switchable_pip (l_nm_loop), g_a242_sm_hyb_npsw_pip (l_nm_loop),
                                                    g_a242_sm_total_pip (l_nm_loop), g_a242_sm_min_amt (l_nm_loop), g_a242_sm_final_pip (l_nm_loop), g_a100_sm_min_applied (l_nm_loop),
                                                    SYSDATE, g_vc_userid,
                                                    SYSDATE, g_vc_userid);
                  l_nm_recs_inserted := l_nm_recs_inserted + SQL%ROWCOUNT;
                  COMMIT;
 
                  l_nm_start_cnt := l_nm_end_cnt + 1;
                  l_nm_end_cnt := l_nm_end_cnt + l_nm_increment;
 
              EXCEPTION
                  WHEN OTHERS THEN
                      p_pip_exceptions_logging (l_vc_component_name, SQLERRM);
                      p_delete_pip_records (l_nm_valuation_iteration_id, l_nm_del_status);
                      l_nm_exit_status := 1;
              END;
          END LOOP;
 





Re: How to make inserts faster [message #317578 is a reply to message #317569] Fri, 02 May 2008 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have "insert values" it does not matter.
If you have "insert select", the select part is optimized in the same way than a classic "select". You can, if your hardware allow it:
a) use direct load (append hint)
b) use parallel
c) disable constraint
d) drop/disable index
a) and b) imply you have large number of rows
a) and b) imply you have to commit just after if you want to reuse the table -> can't be done in a transaction processing
a) implies you know how to handle the case of instance/media crash
b) implies you have multiple disks/cpu for this table otherwise it is likely you just waste cpu and time
c) implies you have to execute a fix batch if you can't reenable the constraints
d) implies you have to recreate/rebuild the indexes, so does it save you time? It depends on the ratio number of inserted rows/overall number of rows.
...

By the way, your "suggestions" on select are not quite true.
UNION must be used only if you don't want duplicates and UNION ALL if you don't care about them, there is no choice.
IN and EXISTS are faster each one, it depends on the query (if EXISTS were always faster, Oracle would suppress IN). Anyway, if your statistics are up to date, Oracle will (most often) internally rewrite your query to use the correct predicate.

Regards
Michel
Re: How to make inserts faster [message #317579 is a reply to message #317577] Fri, 02 May 2008 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My first comment in your PL/SLQ is: why not a single SQL?
Then get rid of the commit, it will:
- greatly slow down the performances
- need you to know how to handle in case of failure (program, instance, media)

Then get rid of this WHEN OTHERS block, only trap the errors you know how to handle and let the other raised.
Think about it, for instance, what happen to your logic if the error is "unable to extent segment"?

There is no need of an explain plan for an "insert values", there is just one line: "insert statement".

Regards
Michel

Re: How to make inserts faster [message #317580 is a reply to message #317578] Fri, 02 May 2008 01:39 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Thanks for the reply, Michael.
Re: How to make inserts faster [message #317596 is a reply to message #317579] Fri, 02 May 2008 02:27 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Can there a be a commit after every n records (say 10000) are inserted. Will that help ?
And if I remove WHEN OTHERS, there is a chance of lot of unhandled exceptions isn't it ?
Re: How to make inserts faster [message #317607 is a reply to message #317596] Fri, 02 May 2008 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can there a be a commit after every n records (say 10000) are inserted. Will that help ?

In performances, surely not, it is the opposite. Less commits, better performances. Just commit once, at the end.

Quote:
And if I remove WHEN OTHERS, there is a chance of lot of unhandled exceptions isn't it ?

If you don't know how to handle an error then it is in any case unhandled even if you trap it in "when others"; you only things you do in "when other" are to hide the actual error and when it raises and continue a process that should abort. Think about the "unable to extend" error.

Regards
Michel


Re: How to make inserts faster [message #317608 is a reply to message #317596] Fri, 02 May 2008 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at:
High Volume DML
High Volume Procedural Code

Regards
Michel

Re: How to make inserts faster [message #317629 is a reply to message #317607] Fri, 02 May 2008 04:10 Go to previous messageGo to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
thanks Michael. The values for the columns to be inserted are fetched from various arrays and hence the need to loop through the arrays.
Is it possible to do it in a single insert statement ? How do we do it ?
Re: How to make inserts faster [message #317858 is a reply to message #317629] Sun, 04 May 2008 06:45 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Also take a look at
PL/SQL Tuning article
SQL Tuning article

Ross Leishman
Previous Topic: SQL statement tuning
Next Topic: Where can I download this Book?
Goto Forum:
  


Current Time: Tue Dec 06 08:28:40 CST 2016

Total time taken to generate the page: 0.13526 seconds