Saud Saud - 1 month ago 4x
SQL Question

sql oracle how to know which part of the query takes long time

I have a large script that I'm using to get information about Employees, the script consists of lots of lookups and joins between several tables, I'm wondering, is there anyway that I can know which part of the script or which lookup takes more time and make the script very slow so I can only tune it instead of trying to tune the whole script until I find out which one causing me the trouble.

I'm using oracle 11g.

Thanks in advance.


Use EXPLAIN PLAN to find out the execution workflow, and where is the most consuming parts in your query. For example, if you are using SQL Developer, you can run the following:

explain plan for
<your query>

Then run:


It will give you the results in a table format, something like this: enter image description here

You can look at the time and the cost, and the operation types. For example, whenever you see 'Full table scan' that is a red sign.