Active Session History: updating the X$ASH, part 01

Part 02
Part 03

I'm a proud member of OraPub community. Some days ago I was speaking with Kaley Crum, another member of the OraPub community.

The context was ASH (Active Session History).

He told me that at some time, Oracle does an update of X$ASH in order to update the values of the TIME_WAITED column.

I didn't trust him because my conviction was that Oracle makes just an insert in this X$.

Well, I was wrong. Kaley right. Thanks to him to learn me something new.

On this argument, what is TIME_WAITED column, and some trouble with it. Kayle wrote a post himself.

Also, and I recommend it to you, a couple of post by Craig Shallahamer. You can find them here and here. And not forget the one wrote by Alberto Dell'Era.

And don't miss the pdf of Graham Wood, slide 35, where he shows the fallacies of the TIME_WAITED column in the V$ACTIVE_SESSION_HISTORY view. This series of posts are based on this slide nr 35.

What I want to do is try to explayn myself the problem, but here, in Part 01, I want just write the test I made.


1) Prepare the enviroment

create table my_active_session_history as select * from v$active_session_history where 1=2;
 
alter table my_active_session_history add (inserting_time timestamp);


2) Run the following anonymous PL/SQL


3) Run following statement, to report the result

col INSERTING_TIME for a30
col SAMPLE_TIME for a30
set lines 120
select 
    inserting_time
  , sample_id
  , sample_time
  , session_id
  , session_serial#
  , event
  , session_state
  , time_waited
from
    my_active_session_history
order by 
    session_id
  , sample_id
  , inserting_time;

You can see the result of this last statement, here.

Part 02
Part 03