Performance Tuning

Question

How can i check the Query running from long time & steps of tuning the query? (Oracle)

Answer 1

1. Run explain plan for select .... to see what Oracle is doing with your query.
2. Post your query here so that we can look at it and help you out.
3. Check out the Oracle Performance Tuning FAQ for some tricks-of-the-trade, if you will.

Answer 2

You can capture the query by selecting from v$sql or v$sqltext. If you are not familiar with it, look up 'Explain Plan' in the Oracle documentation. There should be plenty on it in the performance tuning guide.

Have a look at Quest Software's Toad for a third party tool that helps in this area too.

K

Answer 3

Unfortunately your question is not expressed clearly. The other answers have already tackled the issue of tuning a known bad query, but another interpretation is that you want to monitor your database to find poorly performing queries.

If you don't have Enterprise Edition with the Diagnostics pack - and not many of us do - your best bet is to run statspack snapshots on a reqular basis. This will give you a lot of information about your system, including which queries take a long time to complete and which queries consume a lot of your system's resources. You can find out more about statspack here.