Sundaramurthy Blog

Oracle Trace

ORACLE  SQL TRACE COMMAND

Sql for to identify the trace name

SET LINESIZE 120
COLUMN trace_file FORMAT A75
SELECT s.sid,
 s.serial#,
 ip.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||    
 '_ora_' || p.spid || '.trc' AS trace_file
FROM   v$session s,
 v$process p,
 v$parameter ip
WHERE  ip.name = 'user_dump_dest'
AND    s.paddr = p.addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

 SID    SERIAL# TRACE_FILE
---------- ---------- ---------------------------------------------------------------------------
 2173         99 /home/oracle/product/10.2.0/db/admin/TESTDB/udump/TESTDB1_ora_1085.trc
 2150       8667 /home/oracle/product/10.2.0/db/admin/TESTDB/udump/TESTDB1_ora_2222.trc
 2161         18 /home/oracle/product/10.2.0/db/admin/TESTDB/udump/TESTDB1_ora_12617.trc
 2144        166 /home/oracle/product/10.2.0/db/admin/TESTDB/udump/TESTDB1_ora_1119.trc
 2156         70 /home/oracle/product/10.2.0/db/admin/TESTDB/udump/TESTDB1_ora_13659.trc
 2177        237 /home/oracle/product/10.2.0/db/admin/TESTDB/udump/TESTDB1_ora_13691.trc

6 rows selected.

SQL> ALTER SESSION SET sql_trace=TRUE;
SQL> ALTER SESSION SET sql_trace=FALSE;

This package provides access to SQL ALTER SESSION and SET ROLE statements, and other session
information, from PL/SQL. You can use DBMS_SESSION to set preferences and security levels

SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);
SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);

SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>2173, serial#=>99, sql_trace=>TRUE);
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>2173, serial#=>99, sql_trace=>FALSE);

*  1 – Standard SQL_TRACE functionality.
*  4 – As level 1 plus tracing of bind variables.
*  8 – As level 1 plus wait events.
* 12 – As level 1 plus bind variables and wait events.

si binary_integer, — SID
se binary_integer, — Serial#
ev binary_integer, — Event code or number to set.
le binary_integer, — Usually level to trace
nm varchar2        — sets the Event Name null = “context forever”.

SQL> EXEC DBMS_SYSTEM.set_ev(si=>2173 , se=>99, ev=>10046, le=>8, nm=>’ ‘);
SQL> EXEC DBMS_SYSTEM.set_ev(si=>2173 , se=>99, ev=>10046, le=>0, nm=>’ ‘);

SQL> ALTER SESSION SET EVENTS ’10046 trace name context forever, level 8′;
SQL> ALTER SESSION SET EVENTS ’10046 trace name context off’;

DBMS_SUPPORT package to be loaded.
$ORACLE_HOME/rdbms/admin/dbmssupp.sql

grant execute on dbms_support to <user>

SQL> EXEC DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace;

SQL> EXEC DBMS_SUPPORT.start_trace_in_session(sid=>2173, serial=>99, waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_SUPPORT.stop_trace_in_session(sid=>2173, serial=>99);

oradebug utility

SQL> CONN sys/password AS SYSDBA;
SQL> ORADEBUG SETMYPID;
SQL> ORADEBUG SETOSPID  2173;              Debug session with the specified OS process.
SQL> ORADEBUG SETORAPID  842343;      Oracle process ID.

SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12;
SQL> ORADEBUG TRACEFILE_NAME;      — Display the current trace file.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;

ORACLE HANGANALYZE:

1. SQL> oradebug setmypid
2. SQL> oradebug setinst all
3. SQL> oradebug -g def hanganalyze 3
4.  Wait at least 3 minutes to give time to identify process state changes.
5. SQL> oradebug -g def hanganalyze 3

alter session set events ‘immediate trace name SYSTEMSTATE level 10′;

Forceing  Parallel Query runs in serial with the Parallel Execution Plan-  (oracle metalink note):

alter session set events ‘10384 trace name context forever , level 16384’;
<your parallel query >
alter session set events ‘10384 trace name context off’;

SQL> alter session set events ‘10384 trace name context forever , level 16384’;
SQL> select /*+  gather_plan_statistics  */ * from t1 a;
SQL> select * from TABLE(dbms_xplan.display_cursor( null, null, ‘ALL IOSTATS LAST’))
SQL> alter session set events ‘10384 trace name context off’;

If you trace a session with a the event 10046 and 10384 you have to do:

alter session set events ‘10384 trace name context forever , level 16384’;
alter session set events ‘10046 trace name context forever , level 8’;
<your parallel query >
alter session set events ‘10046 trace name context off’;
alter session set events ‘10384 trace name
context off’;
parallel process basic:

Parallel processing of DML statements must be absolutely enabled at the session level to take advantage of it.
to check parallel DML statement is enabled or disable at session level use this below sql command.

select inst_id,PDML_ENABLED from gv$session where sid = sys_context(‘userenv’,’sid’);

Setting parallel DML process in session level commands.

ALTER SESSION ENABLE PARALLEL DML
ALTER SESSION DISABLE PARALLEL DML
ALTER SESSION FORCE DML PARALLEL 8;

select inst_id,PDML_ENABLED from gv$session where sid = sys_context(‘userenv’,’sid’);

Parallel DDL statements are enabled by default. At the session level we can enabled or diabled by using following command.

ALTER SESSION ENABLE PARALLEL DDL
ALTER SESSION DISABLE PARALLEL DDL
ALTER SESSION FORCE DDL PARALLEL 8;

select inst_id,PDDL_STATUS from gv$session where sid = sys_context(‘userenv’,’sid’);

to check parllel query is enabled or disbaled or force at session level by using following query.

select inst_id,PQ_STATUS  from gv$session where sid = sys_context(‘userenv’,’sid’);

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: