We help IT Professionals succeed at work.

Oracle SQL Tuning

pms_reddy1 used Ask the Experts™
How can you tell if a SQL statement needs tuning?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012

this may sound like a joke but I'm serious

if people complain about it.  

if nobody complains, then any effort you spend to try to improve it is simply time and money spent to improve something while providing no benefit to your business.
Most Valuable Expert 2011
Top Expert 2012

on the flipside,  you can try to be proactive and look for the queries that are the most resource intensive.  

Which queries consume the most temp, the most rollback, the most cpu, the most memory, the most io?

Those aren't necessarily "bad" queries, but if you can make them more efficient that alleviates pressure on the server and makes everything else run faster.

again though, tuning has a financial cost.  If you spend $1000 in time making something faster and your efforts only produce $100 of benefit to the company then the tuning was a bad idea.
David VanZandtOracle Database Administrator III
Rule of thumb #1:  when the user(s) complain
Rule of thumb #2:  when your service level agreements (SLA) are at risk (which can quickly lead to ROT #! above)
Rule of thumb #3:  "if it ain't broke don't fix it"

Seriously, I would usually start with an EXPLAIN PLAN on a long-running statement, and there are numerous ways to rank statement wait times to find potential problems.  Depending upon the database version and tools available, I would have you get familiar with the automated workload repository (AWR) utility, which can greatly help you diagnose potential problems.  TOAD, SQL*Developer, etc., all have reporting capabilities for this as well.

One caveat:  changing statements already in production requires risk.  Please do not tweak code that someone else is relying upon.


It's not a joke,
Infact if you consider the database is very new(I mean at initial stage) user may not be having much knowledge on performance of the appication,In this case I think developers may have to take some proactive measures.
Thanks for your 2nd time comments.


Excellent dvz.
Most Valuable Expert 2011
Top Expert 2012
during development of a new system, of course your users won't have much input and some upfront tuning will be needed before going live.

Use autotrace and pl/sql profiler to measure actual execution times and statistics.
As mentioned above.  Look at the biggest resource consumers first.

Tune resource consumption rather than time.

If you tune the consumption, the time reductions will come on their own

but, as mentioned before,  even this preliminary tuning still has a cost associated with it.
Tuning is an easy expense-trap to fall into.

Everything can always be made at least a little better; but eventually the effort and cost to do so becomes prohibitive compared to the benefit gained.

So, when looking at resource consumers, make sure you're taking into account context of the execution.  If you can improve a nightly process that takes 4 hours down to 1 minute that seems pretty good.  But if those 4 hours aren't really impacting anybody then maybe that shouldn't be a high priority.    If you can make a 20 second process complete in 18 seconds but it runs all the time during the day and affects everybody by competing for memory,cpu, etc , that might be the better candidate for first pass tuning.