Preamble

You all surely know oradebug tool to activate SQL trace of another session or to generate the hanganalyze and systemstate reports to Oracle support when your database is hanging.

Tanel Poder is revealing an extension of this usage in his post The full power of Oracle’s diagnostic events, part 2: ORADEBUG DOC and 11g improvements.

Oradebug

All is starting from the official oradebug documentation:

SQL> oradebug doc
 
Internal Documentation
**********************
 
  EVENT                           Help ON events (syntax, event list, ...)
  COMPONENT       [<comp_name>]   List ALL components OR describe <comp_name>

You can obtain additional information using:

SQL> oradebug doc event
SQL> oradebug doc event name
SQL> oradebug doc event scope
SQL> oradebug doc event filter
SQL> oradebug doc event action

Deep investigation is required but one looks very interesting, trace one particular sql_id over time…

As an example I start by creating the below table with my Oracle account (YJAQUIER):

SQL> CREATE TABLE toto (val NUMBER, descr VARCHAR2(50));
 
TABLE created.
 
SQL> INSERT INTO toto VALUES (1,'One');
 
1 ROW created.
 
SQL> COMMIT;
 
COMMIT complete.

Then using something like below I find my SQL*Developer session:

SELECT *
FROM v$session v, v$process p
WHERE v.paddr=p.addr
AND v.username='YJAQUIER';

I then execute the below query:

SELECT /* Yannick */ * FROM toto WHERE val=1;

And find its associated sql_id using:

SELECT * FROM v$sql
WHERE sql_text LIKE 'select /* Yannick */%';

I can know activate tracing for this sql_id for my session:

SQL> oradebug setospid 30926
Oracle pid: 42, Unix process pid: 30926, image: oracle@server1
SQL> oradebug event sql_trace[SQL: sql_id=100035jk2ztcg]
Statement processed.

The trace file can be found in this directory:

/diagnostic_dest/diag/rdbms/dbname/instname/trace

You can also find its full name using v$process.tracefile column…

If you look at trace file you will see that tracing is taken only when I execute the monitored query. Any other statements are NOT monitored…

To stop tracing issue (you may need to reconnect to spid if you close your session):

SQL> oradebug event sql_trace off
Statement processed.

References

• How to Enable SQL_TRACE for Another Session or in MTS Using Oradebug [ID 1058210.6]
• Diagnosing Database Hanging Issues [ID 61552.1]
• ORADEBUG DOC

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts