How do I tune SQL queries and PL/SQL? Any rules of thumb? Please also tell me any tools available for tuning and best tools among them.
Tuning SQL and PL/SQL begins with an understanding of how Oracle processes SQL and PL/SQL. The "Oracle9i Database concepts" manual provides an overview in "Part V data access: SQL, PL/SQL and Java." You should also review the "Oracle9i Database performance tuning guide and reference."
Most of the decisions about SQL processing can and should be left to Oracle's Cost-Based Optimizer. Make sure that you have accurate statistics on your tables and indexes so that the optimizer has the information it needs to do its job.
When you are ready to tackle the tuning of individual SQL statements, you will want to use the EXPLAIN PLAN command to see the optimizer plan for a given query, the DBMS_SUPPORT package to trace Oracle sessions, and the TKPROF utility to format trace information. The Oracle documentation contains information on all of these.