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.
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:
You can laso 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.
- 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
- Oracle Label Security (OLS) 12c installation and configuration - November 20, 2017
- Database Vault 12cR1 installation and configuration - October 23, 2017
- Data Redaction (DBMS_REDACT) with 12cR1 (220.127.116.11) - September 26, 2017
- Resolve ORA-01578 error with and without a backup - August 28, 2017
- ProxySQL high availability tutorial with MariaDB replication - July 24, 2017
- Orchestrator MySQL replication topology tool tutorial - June 26, 2017