Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

optimize oracle query

Posted on 2012-12-29
7
Medium Priority
?
453 Views
Last Modified: 2012-12-31
Hello,

How can I optimize the query insert into tr1234566_tmp_calc VALUES
         (:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14,:p15,:p
         16,:p17)  :
   Recommendation 1: SQL Tuning
   Estimated benefit is .28 active sessions, 10.88% of total activity.
   -------------------------------------------------------------------

         insert into tr1234566_tmp_calc VALUES
         (:p1,:p2,:p3,:p4,:p5,:p6,:p7,:p8,:p9,:p10,:p11,:p12,:p13,:p14,:p15,:p
         16,:p17)
   Rationale
      The SQL spent 98% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 99% for SQL
      execution, 1% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "f79cfu8cpyg1b" was executed 464908 times and
      had an average elapsed time of 0.0063 seconds.

Thanks

bibi
0
Comment
Question by:bibi92
7 Comments
 
LVL 14

Expert Comment

by:ajexpert
ID: 38729641
Are you purely going by recommendations OR you are really experiencing performance issues with these insert statements?
0
 

Author Comment

by:bibi92
ID: 38729822
by recommendation.
0
 
LVL 23

Expert Comment

by:David
ID: 38730090
Off the top, inserts are commonly optimized with:
add the hint +APPEND so that writes at made at the high-water mark
parallelize the database writers
disable indexes for the load, and rebuild them after the commit
update the table statistics for the cost-based optimizer
find a compromise on how oftern you need to commit vs load on your redo log buffers
with a half-million rows, you are presumably scheduling this to a minimal-impact window (after hours)

As always, your actual results will vary.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 38730197
Usually, inserts like this don't need any special tuning.  But, what is the time period for these half a million rows to be inserted?  What else is going on in the database at the time?  Can you combine these inserts to be multiple row inserts?  If yes, it may make sense to try the "append" hint.  But, there are some disadvantages to this approach.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 38730205
I suggest just don't follow recommendation unless you experience performance issue.  As suggested by other experts, Inserts do not need special tuning unless millions of records are being inserted.
0
 
LVL 35

Accepted Solution

by:
johnsone earned 1500 total points
ID: 38732408
I take this from you original post:

had an average elapsed time of 0.0063 seconds.

What do you expect to improve?

This is a bound statement and the plan is being repeatedly reused.  I don't see any problem with the statement at all.  It is showing up on a report because it exceeds a threshold, but the reason that it exceeds a threshold is that it has been run so many times.
0
 

Author Closing Comment

by:bibi92
ID: 38733100
Thanks regards bibi
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup
Suggested Courses

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question