What the heck is the SQL Execution ID – SQL_EXEC_ID?

Ok, I think it’s time to write another blog entry. I’ve been traveling and dealing with jetlag from 10-hour time difference, then traveling some more, spoken at conferences, drank beer, had fun, then traveled some more, trained customers, hacked some Exadatas and now I’m back home.

Anyway, do you know what is the SQL_EXEC_ID in V$SESSION and ASH views?

Oh yeah, it’s the “SQL Execution ID” just like the documentation says … all clear. Um … is it? I’d like to know more about it – what does it actually stand for?! Is it session level, instance level or a RAC-global counter? And why does it start from 16 million, not 1?

 

SQL> SELECT sql_exec_id FROM v$session WHERE sid = USERENV('SID');

SQL_EXEC_ID
-----------
   16777216

 

This number 16777216 looks strangely familiar – indeed, it’s 2^24.

When I run the same query again (incrementing the SQL_EXEC_ID counter for the same SQL), I see the counter going up by 1:

 

SQL> SELECT sql_exec_id FROM v$session WHERE sid = USERENV('SID');

SQL_EXEC_ID
-----------
   16777217

SQL> SELECT sql_exec_id FROM v$session WHERE sid = USERENV('SID');

SQL_EXEC_ID
-----------
   16777218

SQL> SELECT sql_exec_id FROM v$session WHERE sid = USERENV('SID');

SQL_EXEC_ID
-----------
   16777219

SQL> SELECT sql_exec_id FROM v$session WHERE sid = USERENV('SID');

SQL_EXEC_ID
-----------
   16777220

 

Further executions of the same query keep incrementing this counter, one by one – even if I run this same SQL from another session in the same instance. So, this SQL_EXEC_ID is not a session-scope value for each SQL_ID, it’s at least instance-wide. It looks like the counting starts from 2^24 (the bit representing 2^24 is set) and ignoring that bit for now, the counting works normally, one by one, starting from zero.

 

Note that changing even a single character in the SQL text (see the extra space in the end before the semi-colon) causes the SQL_ID to change and a different SQL_EXEC_ID counter to be reported (which starts from “zero” again). A separate SQL_EXEC_ID counter is maintained in shared pool for each SQL_ID:

SQL> SELECT sql_exec_id FROM v$session WHERE sid = USERENV('SID') ;

SQL_EXEC_ID
-----------
   16777216

 

So, obviously, when I have just restarted my instance and still see 16777216 as the starting SQL_EXEC_ID for any SQL I execute, it must mean that the full SQL_EXEC_ID value contains something else than just the execution number of this SQL_ID. Whenever I see such familiar values (like powers of 2), then I like to look into the values in hex format to see whether some higher order bits are used for some special purpose. Let’s run a new SQL statement:

 

SQL> SELECT sql_exec_id, TO_CHAR(sql_exec_id,'XXXXXXXX') hex FROM v$session WHERE sid = USERENV('SID') ;

SQL_EXEC_ID HEX
----------- ---------
   16777216   1000000

SQL> SELECT sql_exec_id, TO_CHAR(sql_exec_id,'XXXXXXXX') hex FROM v$session WHERE sid = USERENV('SID') ;

SQL_EXEC_ID HEX
----------- ---------
   16777217   1000001

SQL> SELECT sql_exec_id, TO_CHAR(sql_exec_id,'XXXXXXXX') hex FROM v$session WHERE sid = USERENV('SID') ;

SQL_EXEC_ID HEX
----------- ---------
   16777218   1000002

 

Indeed, it looks like the 25th bit (2^24) is always pre-set to 1, while the least significant 24 bits represent how many times this SQL ID has been executed in an instance (I have tested this with a loop – the 24 least significant bits do get used fully for representing the SQL ID’s execution count in the instance and once it reaches 0xFFFFFF – or 0x1FFFFFF with that pre-set 25th bit, it wraps to 0x1000000 – the 25th bit still remaining set!). So the SQL_EXEC_ID can reliably only track 2^24 – 1 SQL executions in an instance and then the counter wraps to beginning. This is why you should include SQL_EXEC_START (date datatype with 1 sec precision) column in your performance monitoring queries as well, to distinguish between SQL executions with a colliding SQL_EXEC_ID. As long as you’re executing your SQL statement less than 16.7 million times per second per instance, this should be fine :-)

 

Anyway, so what’s the magic 25th bit then? Well, in RAC it would be very hard to somehow coordinate the incrementing of a single counter globally (that’s why you want to keep your sequences cached in RAC), I figure that there are different counters for the same SQL ID in different RAC instances. Let’s check – I will log in to another RAC node (node 2) and run this:

 

SQL> SELECT sql_exec_id, TO_CHAR(sql_exec_id,'XXXXXXXX') hex FROM v$session WHERE sid = USERENV('SID') ;

SQL_EXEC_ID HEX
----------- ---------
   33554433   2000001

SQL> SELECT sql_exec_id, TO_CHAR(sql_exec_id,'XXXXXXXX') hex FROM v$session WHERE sid = USERENV('SID') ;

SQL_EXEC_ID HEX
----------- ---------
   33554434   2000002

SQL> SELECT sql_exec_id, TO_CHAR(sql_exec_id,'XXXXXXXX') hex FROM v$session WHERE sid = USERENV('SID') ;

SQL_EXEC_ID HEX
----------- ---------
   33554435   2000003

 

Whoa – the SQL Execution ID in the 2nd instance starts from 33 Million! And when you convert the value to hex, you’ll see that now the 26th bit is set – showing that this SQL was executed in instance #2!

So, it very much looks like that while the 24 least significant bits are used for the SQL execution ID counter, the more significant bits are used for showing which instance_id ran that SQL. Assuming that 32 bits are used for the whole SQL_EXEC_ID value, then up to 8 higher order bits could be used for storing the instance_id – supporting up to 256-node RAC clusters. This is very useful when analyzing past ASH data as you can aggregate data (count min/max exec ID difference to get the execution counts in a time range) either in each separate instance or globally – by stripping out the instance_id part from the value.

I haven’t tested the instance_id part with 256-node RAC clusters (as Santa Claus is cutting back due to poor economy), but at least on an 8-node full rack Exadata all 8 instance_ids were reported properly. Note that for serial queries, the SQL_EXEC_ID shows you the instance_id of the instance where the session is logged on to, but for inter-instance parallel query, you will see the instance_id of the query coordinator for all PX slaves, regardless of in which instances they run. Here’s a little script from a 8-node Exadata cluster to show it. I’ll leave it up to you to fully figure it out what, how and why it’s doing, but basically what it shows is that the SQL_EXEC_ID consists of the query coordinator’s instance_id value and the execution number for a SQL_ID in the instance where the query coordinator session was logged in:

 

SQL> SELECT qc_instance_id, MIN(TO_CHAR(sql_exec_id,'XXXXXXXX'))
  2  , MAX(TO_CHAR(sql_exec_id,'XXXXXXXX'))
  3* FROM gv$active_session_history GROUP BY qc_instance_id order by 1
SQL> /

QC_INSTANCE_ID MIN(TO_CH MAX(TO_CH
-------------- --------- ---------
             1   1000000   100540F
             2   2000000   20009BF
             3   3000000   300541E
             4   4000000   40000DD
             5   5000000   50C5035
             6   6000000   600018C
             7   7000000   700023D
             8   8000000   8000755
                 1000000   803DF3B

9 rows selected.

 

That’s all for today – more cool stuff is coming, I promise :-)

 

And oh, next week I’ll start another run of my Advanced Oracle Troubleshooting seminar, so check it out! ;-)

This entry was posted in Oracle and tagged , , , . Bookmark the permalink.

33 Responses to What the heck is the SQL Execution ID – SQL_EXEC_ID?

  1. Apex says:

    > As long as you’re executing your SQL statement less than 24 million times per second, this should be fine :-)

    Hm… not 24 million times per sec, but (2^24 – 1) times per sec, isn’t it? Or have I missed something?

    Anyway, welcome home and thank you for a nice note:)

  2. Tanel Poder says:

    @Apex
    Oh, yes you’re correct, it’s 2^24, I wanted to write ~16 million times there …

    And to nitpick on myself, actually it should be 2^24 not 2^24 – 1 I think :)

  3. goryszewskig says:

    Thanks Tanel, but I really miss old time row cache/shared pool hacking :) and messing around.
    Regards
    GregG

  4. Tanel Poder says:

    @goryszewskig

    You mean online sessions? Well my AOT class will start the next week ;-) But I plan to do more hacking sessions too!

  5. Nice post – I wondered why the starting number was 16777216 on most systems but 33554432 on some others. (it seems I usually log in to node 1 on most rack systems)

    I’ve been meaning to do a post on SQL Monitor but just can’t seem to find the time.

  6. Tanel Poder says:

    @Kerry Osborne

    “just can’t seem to find the time”

    Tell me about it ;-)

  7. zfriese says:

    @Tanel Poder
    It is 2^24, of course, since you start at 0. Start at 1, and then you lose one…

  8. Tanel Poder says:

    @zfriese
    Yep! But i was too lazy to go back and edit it – got to leave something interesting to figure out for the readers’ brains too! :)

  9. @atulgg says:

    Hello,
    Btw get sql_exec_id incremented by 1 if i run statement 1 & 2 one after other,
    meaning sql_exec_id does not start with 16777216 for statement 2

    1)SELECT sql_exec_id FROM v$session WHERE sid = USERENV(‘SID’);
    2)SELECT sql_exec_id FROM v$session WHERE sid = USERENV(‘SID’) ;

    However below two statements give same sql_exec_id(16777216) , notice the space after SELECT in 2nd statement.
    1)SELECT sql_exec_id FROM v$session WHERE sid = USERENV(‘SID’);
    2)SELECT sql_exec_id FROM v$session WHERE sid = USERENV(‘SID’);

    Version Details :
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

    This is bit interesting to me !
    Br
    Atul

  10. @atulgg says:

    Seems above post has some firmatting issues as space after SELECT did’t appear so reposting and trying my luck

    1)SELECT sql_exec_id FROM v$session WHERE sid = USERENV(‘SID’);
    2)SELECT sql_exec_id FROM v$session WHERE sid = USERENV(‘SID’);

  11. Tanel Poder says:

    @@atulgg

    So, post me the full test case with output to actually show what’s wrong. I don’t want to waste time on guessing… If you use some 3rd party tool, then first try to reproduce this in sqlplus, god knows what these other tools do to the sql text (like trimming spaces) before sending it to the server…

  12. Tanel Poder says:

    Btw you should be able to use the CODE or SQL tags in square brackets to post code examples. Like this:

    SELECT * FROM dual
  13. @atulgg says:

    you are right i was doing it with third party tool :(, in sqlplus it works ok.

  14. Ganesh says:

    Tanel,

    ” but basically what it shows is that the SQL_EXEC_ID consists of the query coordinator’s instance_id value and the execution number for a SQL_ID in the instance where the query coordinator session was logged in:”

    I think it uses the instance if of teh QC to keep track of where the PX query origniated from.
    While the Instance id of the query coordinator is used to keep track of where the QC session resides, I would think it is using the execution count based on the instance on which the PX slave(s) is running and not the execution number from the instance where the QC is running. If that was the case, the max(xxx) value you posted will all be the same. Is it not?

  15. Paul Janda says:

    Very interesting. I wonder what it’s used for and why it was exposed to v$ views. I’ve troubleshot lots of problems involving spinning processes, unexpected loops, bad sql plans, etc. In those scenarios, usually more than one statement is involved. If I understand correctly, I would think that counter would turnover constantly. I’m curious and puzzled by it. Clearly, there is/was a reason for it’s creation.

    I’m behind on my reading. I hope I’m not past the relevance date :)

  16. Tanel Poder says:

    @Ganesh
    Ganesh, nope, as there were many different queries executed in the whole cluster, then these exec IDs don’t have to correspond to each other (assuming that you were talking about a case where QC runs a SQL which the gets executed using PX across multiple nodes – in this case, for that query only, the sql_exec_id is the same for all PX slaves, regardless of on which instance the slaves happen to run). So if I picked only one SQL statement executed with PX I would see what you mentioned, but in the above case there were many different SQLs executed over time.

    If you don’t agree, you’re welcome to post a test case (with output) which proves me wrong ;-)

  17. Tanel Poder says:

    @Paul Janda
    Paul, this was added to ASH in 11g as in 10g it wasn’t possible to determine from ASH data only whether a query was so active because it took so long time to execute or it was just executed so frequently. I don’t know whether any of the GUI tools actually used it or not … The SQL Monitoring report does show only the sql_exec_id of the monitored SQL…

    It wraps to 0 when you hit 2^24 – 1, but that should be fine as the sql_exec_id should be used with the sql_exec_start column (date), so your old executions with similar sql_exec_ids would have different start date…

  18. I recently found another use for sql_exec_id. It’s also included in v$open_cursor and not null there for the currently executing statements. I used it in a BEFORE DDL trigger to get more information about the triggering statement (via v$sqlarea).

  19. Sandeep says:

    Hi Tanel,

    First of all I would like to thank you for sharing your knowledge. I will be more thankful to you if you can give some time to write a post about SADDR,PADDR columns in V$SESSION.

    Thanks

  20. srivenu kadiyala says:

    Tanel,
    I think this definition is a little more explanatory.
    http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3052.htm

    SQL_EXEC_ID – “Execution identifier. Together, the three columns SQL_ID, SQL_EXEC_START, and SQL_EXEC_ID represent the execution key. The execution key is used to uniquely identify one execution of the SQL statement.”

    thanks

  21. Coskan says:

    Tanel,

    Is this possible or a bug where my sql_exec_id reset without database restart. I can also say this is a sql which runs everyday for mview refresh but somehow sql_exec_id is not increasing.

    SQL> select startup_time from v$instance;
    
    STARTUP_TIME
    -------------------
    25/02/2012 18:02:02
    
    SQL> select sql_exec_id,max(sample_time) from dba_hist_active_sess_history where sql_id='8a44sdv64km4p' group by sql_exec_id order by 2 asc;
    
    SQL_EXEC_ID MAX(SAMPLE_TIME)
    ----------- ---------------------------------------------------------------------------
       16777226 25-MAR-12 22.06.33.777
       16777227 25-MAR-12 23.21.29.834
       16777228 26-MAR-12 00.32.35.585
       16777220 08-APR-12 15.03.32.236
       16777221 08-APR-12 15.49.35.926
       16777222 08-APR-12 16.43.30.211
       16777223 08-APR-12 17.54.25.740
       16777224 08-APR-12 19.01.51.133
       16777225 08-APR-12 20.38.59.016
       16777217 08-APR-12 23.29.02.680
       16777218 09-APR-12 00.45.28.840
       16777219 09-APR-12 01.49.13.982
       16777216 03-MAY-12 20.54.15.490
    
    • Coskan says:

      By the way this is single instance database

      • Tanel Poder says:

        IIRC the SQL_EXEC_ID info was kept in the parent cursor … or was it child … forgotten already (got to do more testing) so maybe some invalidation is causing that information to be thrown out as well occasionally and after next load the ID starts from scratch.

        But I think the docs also state that you should use two other columns (including the sql_exec_start or whatever it was called :) to get a completely unique value. So, the sql_exec_id is not guaranteed to be unique over long periods of time.

  22. Gautham says:

    Hi Tanel,

    How is “sql_Exec_id” different from “executions” column in v$sql view?

  23. rajesh says:

    Have noticed sometimes in the ash history that sql_exec_id is same for diffferent sql_exec_start values for the same sql_id. The documentation also says that the combination of sql_id, sql_exec_start and sql_exec_id is the unique key. So I believe sql_id and sql_exec_id alone is not the counter.

    • Tanel Poder says:

      I explained in the article when this happens – when the 24-bit counter wraps back to zero. So as long as you don’t execute the same SQL more than 16.7 million of times per second (in an instance), combining with sql_exec_start timestamp gives you still an unique “SQL execution ID”. So, sql_exec_id alone IS the counter (something which gets incremented one by one when executing), but you need to combine it with sql_exec_start to deal with “counter wraps”

      • Yousuf says:

        Hi Tanel,

        Just one request.. Need some more information about sequence has to be “noorder” specailly in RAC env..

        Sequence with order cluase will create contention & hence need to use “Noorder” cluase for sequences in RAC. (Does SEQ$ table will create any problem?)

        Please request to shed some light on this..

        Thanks,

        Yousuf

        • Tanel Poder says:

          Yousuf, when wanting guaranteed order of sequence values across multiple RAC nodes, there needs to be a single “authority” of next sequence value across the whole cluster, managed by only one of the nodes (serialized using the SQ enqueue), so all other nodes will end up having to talk to it every time they need the next value. Caching doesn’t help here either as if you cache 100 values (let’s say 501-600) in node A and another 100 in node B (601-700), then these ranges are already out of order when sessions go and fetch next values in different instances (could be for example: 501, 601, 602, 603, 502, etc…)

          So on RAC, you need CACHE + NOORDER for performance. If guaranteed ordering is a must, you need to use NOORDER (or some home-grown sequence value table in your app). To reduce global enqueue contention, you could try to arrange the workloads so that all users of that sequence are running all in the same instance…

  24. Wojciech says:

    Tanel,

    have you ever though about how to get histograms for particular sqlid response time just with sql_exec_(start|id) from ASH? It is pretty easy to get average response time from dba_hist_sqlstat but sometimes is may hide the truth when ie. some of the executions are much longer then usual.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>