2

I know this might seem a simple question - for which you might think existing answers exist. However ...

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.

My first idea was this query:

select sid,serial#,prev_sql_id from v$session where audsid=userenv('sessionid');

My idea was if I run this right after my target query, I will capture the correct sql_id through prev_sql_id.

However ... I was not ... I was getting a different SQL ... apparently in between my target SELECT statement and the query for prev_sql_id, something else ran. In my case Auditing is enabled, and I was capturing the insert into the SYS.AUD$ table. No good.

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:

SELECT *
  FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

Documentation states that with NULL SQL_ID 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 SYS.AUD$ table.

You might say, ok, then just simply put a comment in your query that allows to easily capture the SQL_ID, like in following:

SELECT /* SQL: 1234-12' */ FROM DUAL;

Then I can try to find the SQL_ID as follows:

SELECT * FROM V$SQLAREA WHERE sql_text like '%SQL: 1234-12%';

That will give me several possible candidates, of which the V$SQLAREA 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.

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.

Related Articles:

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
YoYo
  • 9,157
  • 8
  • 57
  • 74
  • Thanks YoYo. Can you include your oracle version, and if in the 12c era, whether adaptive plans are enabled? I ask because you mentioned your primary purpose was to capture the execution plan for the query, and the runtime influence of adaptive plans can change the available approaches for evaluating xplans. Also, are you planning to do this in a production environment, or temporarily in development? I'm interested in whether turning off auditing while doing this evaluation is an available option for you. – alexgibbs Mar 15 '17 at 02:57
  • I never heard about a concept like development or production environment? So yeah - I have one production environment and that's it - no turning off auditing. Actually it is more than one such DB, we went to 12c for most, but not all.. The main need is to track performance for reports developed as part of a Java Web application. Not sure yet about 'adaptive plans', because I am not familiar yet with the term. – YoYo Mar 15 '17 at 03:37
  • about 'adaptive plans' - maybe we can focus on getting the sql_id, maybe that simplifies the question. Getting the execution plan might be just only one of the purposes. – YoYo Mar 15 '17 at 03:43
  • 2
    @YoYo The edition is probably more important than the version. If you have Enterprise Edition and have licensed the Diagnostics pack, there are multiple ways to get this information using AWR (Active Workload Repository). – Jon Heller Mar 15 '17 at 04:03
  • Enterprise Edition, and I would have all licensed features, definitely AWR. As a matter of fact - I am already using AWR and OEM for some of my ad hoc evaluating of query performance. – YoYo Mar 15 '17 at 04:05
  • @JonHeller has a good point. If SQL_IDs are a target, things like DBA_HIST_SQLTEXT may be a resource if you haven't already used it, and DBA_HIST_SQLSTAT has more info. YoYo Have you explored sql hist at all? Also if a single java application is being tracked, have you tried setting trace on its session(s) or client_id to watch what it is executing? – alexgibbs Mar 15 '17 at 04:46
  • @YoYo Do you just need a query like `select sql_id, count(*) sample_count from gv$active_session_history where sql_id is not null group by sql_id order by count(*) desc;`? If you're trying to get every single SQL statement for performance tuning, that's a common trap a lot of people fall into. All you need is Oracle's once-per-second sampling. Yes, it misses a lot of queries. But if a query never shows up in the sample data than it's not important and doesn't need to be tuned. – Jon Heller Mar 15 '17 at 05:45
  • 1
    Take a look at https://stackoverflow.com/questions/4376329/jdbc-oracle-fetch-explain-plan-for-query/49927499#49927499 for sample code to find sql_id by recently executed sql text (full or partial). – Vadzim May 11 '18 at 15:22
  • @alexgibbs DB sessions in java web applications are managed in connection pools. Changes on the session bleeds outside the context where you intend it to happen. – YoYo May 28 '18 at 02:13

1 Answers1

7

You could use new SQL*Plus option:

SET FEEDBACK ON SQL_ID;

SQL_ID returns the sql_id for the SQL or PL/SQL statements that are executed. The sql_id will be assigned to the predefined variable _SQL_ID. You can use this predefined variable to debug the SQL statement that was executed. The variable can be used like any other predefined variable, such as _USER and _DATE.

SQL> SET FEEDBACK ON SQL_ID
SQL> SELECT * FROM DUAL;  

D 
- 
X  

1 row selected.  
SQL_ID: a5ks9fhw2v9s1 

--
SQL> SELECT sql_text FROM v$sql WHERE sql_id = '&_sql_id';  

SQL_TEXT 
----------------------------------------------------- 
SELECT * FROM DUAL  

1 row selected.
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275