How can i check the Query running from long time & steps of tuning the query? (Oracle)
- Run
explain plan for select ....
to see what Oracle is doing with your query. - Post your query here so that we can look at it and help you out.
- Check out the Oracle Performance Tuning FAQ for some tricks-of-the-trade, if you will.
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
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.