daily pastebin goal
75%
SHARE
TWEET

reader-friendly ASH sql_exec_id

ExaGridDba May 30th, 2017 60 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. bitand(ash.sql_exec_id,(power(2,24)-1))exid
  2. is a reader-friendly expression for execution id, instead of
  3.    16777216
  4.    16777217
  5.  etc.
  6.  
  7. [oracle@stormking cdb12102 ash]$ cat test.sql
  8. variable sql_id varchar2(13)
  9. begin
  10.         for i in 1..10
  11.         loop
  12.                 for rec_col in (
  13.                         -- a long-runing query
  14.                         select * from dba_tab_columns
  15.                 )
  16.                 loop
  17.                         null;
  18.                 end loop;
  19.         end loop;
  20.         select s.prev_sql_id into :sql_id
  21.         from v$session s
  22.         where s.sid = sys_context('userenv','sid');
  23. end;
  24. /
  25.  
  26. set linesize 32767
  27. set trimspool on
  28. column exid format 9999
  29. column session_state format a12
  30. column event format a50
  31. column sql_text format a100
  32.  
  33. select sql_text
  34. from v$sqlarea
  35. where sql_id = :sql_id;
  36.  
  37. select
  38. ash.sample_id, ash.sql_id,
  39. bitand(ash.sql_exec_id,(power(2,24)-1))exid,
  40. ash.sql_exec_start,
  41. ash.session_state, ash.event
  42. from v$active_session_history ash
  43. where ash.sql_id = :sql_id
  44. order by ash.sample_id
  45. ;
  46.  
  47. quit
  48.  
  49. [oracle@stormking cdb12102 ash]$ sysdba @ test.sql
  50.  
  51. SQL*Plus: Release 12.1.0.2.0 Production on Tue May 30 20:09:28 2017
  52.  
  53. Copyright (c) 1982, 2014, Oracle.  All rights reserved.
  54.  
  55.  
  56. Connected to:
  57. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  58. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
  59. and Real Application Testing options
  60.  
  61.  
  62. PL/SQL procedure successfully completed.
  63.  
  64.  
  65. SQL_TEXT
  66. ----------------------------------------------------------------------------------------------------
  67. SELECT * FROM DBA_TAB_COLUMNS
  68.  
  69.  
  70.  SAMPLE_ID SQL_ID         EXID SQL_EXEC_START SESSION_STAT EVENT
  71. ---------- ------------- ----- -------------- ------------ --------------------------------------------------
  72.   51803476 3wygahc5pagsy                      WAITING      db file sequential read
  73.   51803477 3wygahc5pagsy                      WAITING      db file sequential read
  74.   51803478 3wygahc5pagsy                      WAITING      db file sequential read
  75.   51803479 3wygahc5pagsy     0 20170530 20:09 ON CPU
  76.   51803480 3wygahc5pagsy     0 20170530 20:09 WAITING      db file sequential read
  77.   51803481 3wygahc5pagsy     0 20170530 20:09 WAITING      db file sequential read
  78.   51803482 3wygahc5pagsy     0 20170530 20:09 ON CPU
  79.   51803483 3wygahc5pagsy     0 20170530 20:09 WAITING      db file sequential read
  80.   51803484 3wygahc5pagsy     0 20170530 20:09 WAITING      db file sequential read
  81.   51803485 3wygahc5pagsy     0 20170530 20:09 WAITING      db file sequential read
  82.   51803486 3wygahc5pagsy     0 20170530 20:09 ON CPU
  83.   51803487 3wygahc5pagsy     0 20170530 20:09 ON CPU
  84.   51803488 3wygahc5pagsy     1 20170530 20:09 ON CPU
  85.   51803489 3wygahc5pagsy     1 20170530 20:09 ON CPU
  86.   51803490 3wygahc5pagsy     2 20170530 20:09 ON CPU
  87.   51803491 3wygahc5pagsy     2 20170530 20:09 ON CPU
  88.   51803492 3wygahc5pagsy     3 20170530 20:09 ON CPU
  89.   51803493 3wygahc5pagsy     3 20170530 20:09 ON CPU
  90.   51803494 3wygahc5pagsy     3 20170530 20:09 ON CPU
  91.   51803495 3wygahc5pagsy     4 20170530 20:09 ON CPU
  92.   51803496 3wygahc5pagsy     4 20170530 20:09 ON CPU
  93.   51803497 3wygahc5pagsy     4 20170530 20:09 ON CPU
  94.   51803498 3wygahc5pagsy     5 20170530 20:09 ON CPU
  95.   51803499 3wygahc5pagsy     5 20170530 20:09 ON CPU
  96.   51803500 3wygahc5pagsy     5 20170530 20:09 ON CPU
  97.   51803501 3wygahc5pagsy     6 20170530 20:09 ON CPU
  98.   51803502 3wygahc5pagsy     6 20170530 20:09 ON CPU
  99.   51803503 3wygahc5pagsy     6 20170530 20:09 ON CPU
  100.   51803504 3wygahc5pagsy     7 20170530 20:09 ON CPU
  101.   51803505 3wygahc5pagsy     7 20170530 20:09 ON CPU
  102.   51803506 3wygahc5pagsy     7 20170530 20:09 ON CPU
  103.   51803507 3wygahc5pagsy     8 20170530 20:09 ON CPU
  104.   51803508 3wygahc5pagsy     8 20170530 20:09 ON CPU
  105.   51803509 3wygahc5pagsy     9 20170530 20:10 ON CPU
  106.   51803510 3wygahc5pagsy     9 20170530 20:10 ON CPU
  107.   51803511 3wygahc5pagsy     9 20170530 20:10 ON CPU
  108.  
  109. 36 rows selected.
  110.  
  111. Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  112. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
  113. and Real Application Testing options
  114.  
  115. Notice that the values under exid are small numbers, and easy to read.
  116.  
  117. Notice that the data is read into the buffer cache on the first execution, and that as a result, subsequent executions have a faster response time than the first execution.
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top