Tracing in oracle

Enable session tracing:

EXEC DBMS_MONITOR.session_trace_enable;

Check default trace file name:

SELECT value
FROM   v$diag_info
WHERE  name = 'Default Trace File';

In v$diag_info we can check various diagnostic information's.

Execute required query that you want to trace.

Ex: WITH query1 AS (
  SELECT /*+ MATERIALIZE */ * FROM t1
)
SELECT * FROM query1;
--To check materialize hint in trace file.

Disable tracing:

EXEC DBMS_MONITOR.session_trace_disable;

If you don't have permissions to execute this procedure, you may connect by sys and give required permissions.

D:\Oracle\12.1.0.2>sqlplus / as sysdba

SQL> grant execute on DBMS_MONITOR to SCOTT;

Grant succeeded.

SQL>


Other related tracing levels etc.

http://www.orafaq.com/node/2891