EXPLAIN PLAN FOR TABLE...

for checking execution plan

conn scott/tiger

Nashim$ >@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlxplan.sql;

Table created.

Nashim$ >select * from plan_table;

no rows selected

Nashim$ >GRANT ALL ON PLAN_TABLE TO PUBLIC;

Grant succeeded.

Nashim$ >conn /as sysdba
Connected.

Nashim$>@C:\oracle\product\10.2.0\db_1\sqlplus\ADMIN\plustrce.sql;

Nashim$ >GRANT PLUSTRACE TO PUBLIC;

Nashim$ >set AUTOTRACE traceonly

Nashim$ >conn scott/tiger
Connected.
Nashim$ >select * from emp, dept
  2  where emp.deptno=dept.deptno
  3  /

14 rows selected.

Nashim$ >set AUTOTRACE traceonly
Nashim$ >select * from emp, dept
  2  where emp.deptno=dept.deptno
  3  /

14 rows selected.



Nashim$ >set AUTOTRACE off;
Nashim$ >/

 
14 rows selected.

Nashim$ >set AUTOTRACE traceonly;
Nashim$ >/

14 rows selected.



Nashim$ >

conn /as sysdba
SQL> alter session set sql_trace=true;
SQL> alter session set max_dump_file_size = unlimited;


where <spid> is the server process ID of the session for which the trace wasenabled.
On Windows, the following query may be used to retrieve your session’s trace file name:

SQL> select c.value || '\ORA' || to_char(a.spid,'fm00000') || '.trc'
2 from v$process a, v$session b, v$parameter c
3 where a.addr = b.paddr
4 and b.audsid = userenv('sessionid')
5 and c.name = 'user_dump_dest';


On Unix, this query can be used to retrieve the session’s trace file name:

SQL> select c.value || '/' || d.instance_name || '_ora_' ||
2 to_char(a.spid,'fm99999') || '.trc'
3 from v$process a, v$session b, v$parameter c, v$instance d
4 where a.addr = b.paddr
5 and b.audsid = userenv('sessionid')
6 and c.name = 'user_dump_dest';