reader-friendly ASH sql_exec_id

bitand(ash.sql_exec_id,(power(2,24)-1))exid
is a reader-friendly expression for execution id, instead of
   16777216
   16777217
 etc.

[oracle@stormking cdb12102 ash]$ cat test.sql
variable sql_id varchar2(13)
begin
        for i in 1..10
        loop
                for rec_col in (
                        -- a long-runing query
                        select * from dba_tab_columns
                )
                loop
                        null;
                end loop;
        end loop;
        select s.prev_sql_id into :sql_id
        from v$session s
        where s.sid = sys_context('userenv','sid');
end;
/

set linesize 32767
set trimspool on
column exid format 9999
column session_state format a12
column event format a50
column sql_text format a100

select sql_text
from v$sqlarea
where sql_id = :sql_id;

select
ash.sample_id, ash.sql_id,
bitand(ash.sql_exec_id,(power(2,24)-1))exid,
ash.sql_exec_start,
ash.session_state, ash.event
from v$active_session_history ash
where ash.sql_id = :sql_id
order by ash.sample_id
;

quit

[oracle@stormking cdb12102 ash]$ sysdba @ test.sql

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 30 20:09:28 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


PL/SQL procedure successfully completed.


SQL_TEXT
----------------------------------------------------------------------------------------------------
SELECT * FROM DBA_TAB_COLUMNS


 SAMPLE_ID SQL_ID         EXID SQL_EXEC_START SESSION_STAT EVENT
---------- ------------- ----- -------------- ------------ --------------------------------------------------
  51803476 3wygahc5pagsy                      WAITING      db file sequential read
  51803477 3wygahc5pagsy                      WAITING      db file sequential read
  51803478 3wygahc5pagsy                      WAITING      db file sequential read
  51803479 3wygahc5pagsy     0 20170530 20:09 ON CPU
  51803480 3wygahc5pagsy     0 20170530 20:09 WAITING      db file sequential read
  51803481 3wygahc5pagsy     0 20170530 20:09 WAITING      db file sequential read
  51803482 3wygahc5pagsy     0 20170530 20:09 ON CPU
  51803483 3wygahc5pagsy     0 20170530 20:09 WAITING      db file sequential read
  51803484 3wygahc5pagsy     0 20170530 20:09 WAITING      db file sequential read
  51803485 3wygahc5pagsy     0 20170530 20:09 WAITING      db file sequential read
  51803486 3wygahc5pagsy     0 20170530 20:09 ON CPU
  51803487 3wygahc5pagsy     0 20170530 20:09 ON CPU
  51803488 3wygahc5pagsy     1 20170530 20:09 ON CPU
  51803489 3wygahc5pagsy     1 20170530 20:09 ON CPU
  51803490 3wygahc5pagsy     2 20170530 20:09 ON CPU
  51803491 3wygahc5pagsy     2 20170530 20:09 ON CPU
  51803492 3wygahc5pagsy     3 20170530 20:09 ON CPU
  51803493 3wygahc5pagsy     3 20170530 20:09 ON CPU
  51803494 3wygahc5pagsy     3 20170530 20:09 ON CPU
  51803495 3wygahc5pagsy     4 20170530 20:09 ON CPU
  51803496 3wygahc5pagsy     4 20170530 20:09 ON CPU
  51803497 3wygahc5pagsy     4 20170530 20:09 ON CPU
  51803498 3wygahc5pagsy     5 20170530 20:09 ON CPU
  51803499 3wygahc5pagsy     5 20170530 20:09 ON CPU
  51803500 3wygahc5pagsy     5 20170530 20:09 ON CPU
  51803501 3wygahc5pagsy     6 20170530 20:09 ON CPU
  51803502 3wygahc5pagsy     6 20170530 20:09 ON CPU
  51803503 3wygahc5pagsy     6 20170530 20:09 ON CPU
  51803504 3wygahc5pagsy     7 20170530 20:09 ON CPU
  51803505 3wygahc5pagsy     7 20170530 20:09 ON CPU
  51803506 3wygahc5pagsy     7 20170530 20:09 ON CPU
  51803507 3wygahc5pagsy     8 20170530 20:09 ON CPU
  51803508 3wygahc5pagsy     8 20170530 20:09 ON CPU
  51803509 3wygahc5pagsy     9 20170530 20:10 ON CPU
  51803510 3wygahc5pagsy     9 20170530 20:10 ON CPU
  51803511 3wygahc5pagsy     9 20170530 20:10 ON CPU

36 rows selected.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

Notice that the values under exid are small numbers, and easy to read.

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.