The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

mercoledì, dicembre 20, 2017

Active Session History: Updating the X$ASH, part 03

Part 01
Part 02

Here I try to show you what happens and why Oracle updates the V$ASH view. Please read the pdf by Graham Wood, for more details.

What I analyze is the session "session_id=1732" with "session_serial#=31065" saw in the previous post.

This is what happens

Click for enlarge

The session 1732 (serial# 31065) is sampled 3 times: at sample_id 50243482, 50243483, 50243484. In the last sample_id, there is a change of state of the session. In fact, querying the V$ACTIVE_SESSION_HISTORY, you see

Click for enlarge

But what happens behind the lines is something like this. Pay attention that my conclusions are based on my experiment

Click for enlarge

(1) SAMPLE_ID 50243482/SAMPLE_TIME 15-DEC-17 02:50:40.054

At the 15/12/2017 14:50:40.838429, one row is inserted in X$ASH with SAMPLE_TIME, 14:50:40.054

(2) SAMPLE_ID 50243484/SAMPLE_TIME 15-DEC-17 02:50:42.064

At the 15/12/2017 14:50:42.016313, one row is inserted in X$ASH with SAMPLE_TIME, 14:50:41.064

(3) SAMPLE_ID 50243484/SAMPLE_TIME 15-DEC-17 02:50:41.064

At the 15/12/2017 14:50:43.192604, one row is inserted in X$ASH with SAMPLE_TIME, 14:50:42.064

Note that, at this time, the session 1732 is waiting, but Oracle doesn't know yet how match time the session will wait

(4) SAMPLE_ID 50243484/SAMPLE_TIME 15-DEC-17 02:50:41.064 (the same of the previous one)

At the 15/12/2017 14:50:44.372309 (sample_id 50243485), the last row is updated in X$ASH with SAMPLE_TIME, 14:50:42.064

At sample_id 50243485, the session 1732 finish it works and Oracle know how much time the session waited. So he can update the state of the wait on X$ASH

Here I left the previous row, just to show what happening.

(5) The final result

What you see finally, is a consistent state of the session

1 commento:

Kaley ha detto...

Excellent series of posts! Well done friend!! =]