• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 528
  • Last Modified:

SQL performance tuning

What are the key steps to address the slow running SQL query in Oracle 11gr2.
Where to begin with , what should be the typical steps to find the root cause?
1 Solution
Steve WalesSenior Database AdministratorCommented:
One of the first things you could look at is the execution plan

See the Oracle Documentation for how to gather that information:


Then there's some other reference sites:


For formatting the output in a nice, understandable way, there's DBMS_XPLAN


Gathering your plan data is the first step.

Interpreting the data that you get is a whole other animal, but start with the above.
slightwv (䄆 Netminder) Commented:
First make sure your statistics are up to date and generate an explain plan.

Look for obvious things like full table scans that you don't think should be there.

If this is a large query with a lot of sub selects, generate explain plans for them individually (break it down into smaller pieces).

If none of that helps, you can trace the session and use tkprof to look at the execution in much greater detail.

Then you can look at the possibility of things like hot blocks in the tablespace.  Possibly look at ways to redesign things.  Possible move some tables to a different tablespace with a different block size, move them to different disks, etc...
taazAuthor Commented:
Thanks for the quick reply.
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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now