SQLTRACE-PLAN

TUNING SQL Statements: Using TK_PROF, EXPLAIN PLAN.

If you have a system that is performing badly, a good way to identify problem SQL statements is to trace a typical user session and then use TkProf to format the output using the sort functions on the tkprof command line.

Explain plan is a representation of the access path that is taken when a query is executed within Oracle.

1. SET TIME ON;

2. ALTER SESSION SET SQL_TRACE = ‘TRUE’;

3. Then run the required program unit

4. Sql_trace file will be created in User_dump_dest (dir)

5. Since this trace_file is in the binary format, run tkprof

6. $ Tkprof [options]

7. $ Tkprof [EXPLAIN=user/password]

TKPROF allows you to analyze a trace file to determine where time is being spent and what query plans are being used on SQL statements. Tkprof is an executable that 'parses' Oracle trace files to produce more readable output. Remember that all the information in TkProf is available from the base trace file.

8.Elapsed time/num of rows * 1000 --> 'X' Mille Second.

Explain plan To discover the execution plan for a select statement. The explain plan statement is most often used from sql* plus. Before that you must create the to hold the resultsFind INDEXES is being used: By using EXPLAIN PLAN. Output is put into PLAN_TABLE.

Sql trace: To diagnosing performance problems on running systems.To knowNo of times the sql statement executed. Total CPU and elapsed time used by the statementTotal no of physical reads trigger by the statement
Total no of records processed by the statementTotal no of the logical reads trigger by the statement
Hints:They can be placed into your Sql statements to force the optimizers to utilize a particular execution path for absolute best performance.

/*+ ALL_ROWS */

/*+ FIRST_ROWS */

/*+ CHOOSE */

/*+ HASH_SJ */

When using subquery after EXISTSàIt improves the response time.