optimize oracle query


How can I optimize the query insert into tr1234566_tmp_calc VALUES
         16,:p17)  :
   Recommendation 1: SQL Tuning
   Estimated benefit is .28 active sessions, 10.88% of total activity.

         insert into tr1234566_tmp_calc VALUES
      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.
      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
      SQL statement with SQL_ID "f79cfu8cpyg1b" was executed 464908 times and
      had an average elapsed time of 0.0063 seconds.


Who is Participating?
johnsoneConnect With a Mentor Senior Oracle DBACommented:
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.
Are you purely going by recommendations OR you are really experiencing performance issues with these insert statements?
bibi92Author Commented:
by recommendation.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

DavidSenior Oracle Database AdministratorCommented:
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.
Mark GeerlingsDatabase AdministratorCommented:
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.
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.
bibi92Author Commented:
Thanks regards bibi
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.