Oracle PL/SQL | Tools For Query Tunning

For any PL/SQL developer, there is needs analyse his queries befor it is put to use. Oracle provides follwoing tools, to the same:
1. DBMS_PROFILER
2. Statspack
3. SQL*Trace /tkprof
4. Explian Plan
DBMS Profiler
Details can be found in following link: http://www.oracle-base.com/articles/9i/DBMS_PROFILER.php
Statpacks
Details can be found in follwoing link: http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/statspac.htm
Explain plan
in SQL*Plus you have to type:
explain plan for ;select * from table(dbms_xplan.display);
When you get error messages or a message complaining about an old version of plan_table, make sure you run the script utlxplan.sql.
The output you get here basically shows you what the cost based optimizer expects. It gives you an idea on why the cost based optimizer chooses an access path.
SQL*Trace/tkprof
For this you have to type in SQL*Plus:- alter session set sql_trace true;
disconnect (this step is important, because it ensures all cursors get closed, and "row source operation" is generated) identify your trace file in the server directory as specified in the parameter user_dump_dest on your operating system: tkprof a.txt sys=no sort=prsela exeela fchela
The file a.txt will now give you valuable information on what has actually happened. No predictions but the truth.