Avatar of bibi92
bibi92
Flag for France asked on

optimize oracle query

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
Oracle Database

Avatar of undefined
Last Comment
bibi92

8/22/2022 - Mon
ajexpert

Are you purely going by recommendations OR you are really experiencing performance issues with these insert statements?
bibi92

ASKER
by recommendation.
David VanZandt

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Mark Geerlings

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.
ajexpert

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.
ASKER CERTIFIED SOLUTION
johnsone

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
bibi92

ASKER
Thanks regards bibi
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.