title: How to reliably get the SQL_ID of a query
body: <p>I know this might seem a simple question - for which you might think existing answers exist. However ...</p>
  
  <p>Understand that I want it be reasonable in performance, so it allows to be logged for every single query executed - or at least the big ones - without much overhead.</p>
  
  <p>My first idea was this query:</p>
  
  <pre><code>select sid,serial#,prev_sql_id from v$session where audsid=userenv('sessionid');
  </code></pre>
  
  <p>My idea was if I run this right after my target query, I will capture the correct <code>sql_id</code> through prev_sql_id.</p>
  
  <p>However ... I was not ... I was getting a different SQL ... apparently in between my target SELECT statement and the query for <code>prev_sql_id</code>, something else ran. In my case Auditing is enabled, and I was capturing the insert into the <code>SYS.AUD$</code> table. No good.</p>
  
  <p>As my main purpose for this attempt was to capture the execution plan for the query (as it was executed and captured by the shared pool), I thought that instead I can simply run this query:</p>
  
  <pre><code>SELECT *
    FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
  </code></pre>
  
  <p>Documentation states that with <code>NULL</code> <code>SQL_ID</code> as parameter, it will run the explain plan on the most recent query ran. I was hoping that this would take care of earlier issues. However ... I got the plan for the exact same insert into the <code>SYS.AUD$</code> table.</p>
  
  <p>You might say, ok, then just simply put a comment in your query that allows to easily capture the <code>SQL_ID</code>, like in following:</p>
  
  <pre><code>SELECT /* SQL: 1234-12' */ FROM DUAL;
  </code></pre>
  
  <p>Then I can try to find the SQL_ID as follows:</p>
  
  <pre><code>SELECT * FROM V$SQLAREA WHERE sql_text like '%SQL: 1234-12%';
  </code></pre>
  
  <p>That will give me several possible candidates, of which the <code>V$SQLAREA</code> query itself is also included. The problem here is that I will need to randomize every query ran, which would cause me to always have a hard-parse.</p>
  
  <p>I have tried other solutions where I go through history, but that comes at a much bigger cost. I have tried to search other solutions. They all seem to lag in some way.</p>
  
  <p>Related Articles:</p>
  
  <ul>
  <li><a href="https://blogs.oracle.com/optimizer/entry/displaying_and_reading_the_execution_plans_for_a_sql_statement" rel="nofollow noreferrer">Blog: Displaying and Reading the exception plans for a SQL Statement</a></li>
  <li><a href="https://stackoverflow.com/questions/11414216/oracle-queries-executed-by-a-session">Oracle queries executed by a session</a></li>
  </ul>