Oracle DBA Place

Using Autotrace SQL> set autotrace traceonly explain SQL> select ename from emp where sal > 500; Execution Plan ---------------------------------------------------------- Plan hash value: 2872589290 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 25 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------- Using DBMS_XPLAN Package SQL> explain plan for select ename from emp where sal > 500; Explained. SQL> select * from TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------ Plan hash value: 2872589290 ------------------------------------------------------------------------- |

Using the same technique I mentioned at " Tracing Commands " here is a code in a form of logging trigger, easier when you want to capture just a specific schema from the very first transaction. CREATE OR REPLACE TRIGGER SYS. LOGON_TRACE_CRYSTAL_TRG AFTER LOGON ON CRYSTAL. SCHEMA DECLARE cmd VARCHAR2(100); BEGIN cmd := 'ALTER SESSION SET max_dump_file_size = unlimited'; EXECUTE IMMEDIATE cmd; cmd := 'ALTER SESSION SET tracefile_identifier = ''10046'''; EXECUTE IMMEDIATE cmd; cmd := 'ALTER SESSION SET statistics_level = ALL'; EXECUTE IMMEDIATE cmd; cmd := 'ALTER SESSION SET events ''10046 trace name context forever, level 12'''; EXECUTE IMMEDIATE cmd; EXCEPTION WHEN OTHERS THEN --NULL; RAISE; END ; / optional: CREATE OR REPLACE TRIGGER  SYS. LOGOFF_TRACE_CRYSTAL_TRG BEFORE LOGOFF ON CRYSTAL. SCHEMA DECLARE cmd VARCHAR2(100); BEGIN cmd := 'ALTER SESSION SET EVENTS ''10046