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!
Course Of The Month
4 days, 22 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
optimize oracle query
Posted on 2012-12-29
Hello,
How can I optimize the query insert into tr1234566_tmp_calc VALUES
(:p1,:p2,:p3,:p4,:p5,:p6,:
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,:
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
How can I optimize the query insert into tr1234566_tmp_calc VALUES
(:p1,:p2,:p3,:p4,:p5,:p6,:
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,:
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
- +2
7 Comments
Are you purely going by recommendations OR you are really experiencing performance issues with these insert statements?
Active 2 days ago
Off the top, inserts are commonly optimized with:
As always, your actual results will vary.
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.
Active today
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.
Active today
I take this from you original post:
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.
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.
Featured Post
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
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Import and exporting Oracle Data with encrypted columns | 4 | 70 | |
| return value in based on value passed | 6 | 49 | |
| running myfile.SQL from command line SQLPLUS 12c does not exit. | 7 | 54 | |
| ORA-01843: not a valid month - use NVL | 14 | 19 |
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…
This post first appeared at Oracleinaction (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself).
I will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA. It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses
Course of the Month4 days, 22 hours left to enroll
738 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.