Query Is Slow...

First of all, you have to know why it is slow. What is the real cause of your problem. If the reason why is not known, suggesting to rewrite the query, or hinting the query, suggesting parallellization et cetera is not very productive. Once in a while you may get lucky. But even then you have to realize that if your problem seems "solved", but you don't know why, nothing guarantees you that the problem won't come back tomorrow. So the first step should always be to investigate the root cause. The tools at your disposal are,

 among more: -

 dbms_profiler -

explain plan -
SQL*Trace / tkprof - s

tatspack Use dbms_profiler


 if you want to know where time is being spent in PL/SQL code. Statspack is a must if you are a dba and want to know what is going on in your entire database. For a single query or a small process, explain plan and SQL*Trace and tkprof are your tools. explain plan in SQL*Plus you have to type: explain plan for Your Query; 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. By comparing the output from explain plan with the output from tkprof, you are able to identify the possible problem areas. I am fully aware that this text is only a tiny fraction of what can be done, and that other people may choose different tools and actions, but the above gives you a very reasonable start at solving your performance problem. Below is the sequence of statments we should follow while writing a sql query,
example:

 1. filter column shold come first,

 2. Thn join conditions,

3 etc How to use explain plan:

 SQL> explain plan for select * from scott.emp;
Explained.

 SQL> select plan_table_output from table(dbms_xplan.display);


 PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------

Plan hash value:
3956160932 --------------------------------------------------------------------------

Id Operation Name Rows Bytes
Cost (%CPU) Time -------------------------------------------------------------------------- 0

SELECT STATEMENT 14 518 3 (0) 00:00:01 1 TABLE ACCESS FULL EMP 14 518 3 (0) 00:00:01 -------------------------------------------------------------------------- 8 rows selected.



SQL> select * from V$version; BANNER --------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 -

Production PL/SQL Release 10.2.0.4.0 -

 Production CORE 11.1.0.6.0

Production TNS for Linux: Version 10.2.0.4.0 -

 Production NLSRTL Version 10.2.0.4.0 - Production

 Most import is to read the explain plan in the tkprof.
and finally LIKE operater should be used as 'Word%' to give a match first and to improve Query performance.