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>
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
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.