Re: trace SQL

Papadomichelakis John wrote:
>
> How can I get some information of the execution plan
> of a SQL statement?
>

  You can use explain plan:

  You must create a output table. It can be created by running utlxplan.sql beforehand. The table created in this case is PLAN_TABLE.

  For example,

  EXPLAIN PLAN

	SET STATEMENT_ID = 'Raise in Chicago' 
	INTO output 
	FOR UPDATE emp 
		SET sal = sal * 1.10 
		WHERE deptno =  (SELECT deptno 
					FROM dept
					WHERE loc = 'CHICAGO'

  You can use the following query to examine the result:

  SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options, object_name, position

	FROM output 
	START WITH id = 0 AND statement_id = 'Raise in Chicago'
 	CONNECT BY PRIOR id = parent_id AND 
	statement_id = 'Raise in Chicago'   

  Alternatively, you can use ALTER SESSION SET SQL_TRACE=TRUE to trace the statements for a duration. In that case, you use TKPROF to format the result.

---
Name   : Lun Wing San (Certified Oracle Database Administrator)

Title  : Oracle Database Administrator and System Administrator of QRC
Phone  : (852)27885841

This posting represents the personal opinions of the author. It is not the
official opinion or policy of the author's employer. Warranty expired when you
opened this article and I will not be responsible for its contents or use.