Posts

Showing posts from September, 2010

Methods for viewing SQL Execution Plans

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
-------------------------------------------------------------------------
| Id | Operation | Nam…

Loggon Trigger for Tracing

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 trace name context off''';