Oracle statistic records are missing

[Former Member]

Hi,

Even though, "DBA_HIST_SYSTEM_EVENT" table is storing "enq: TX - row

lock contention" event records, I can't see any records

on "DBA_HIST_ACTIVE_SESS_HISTORY" or "GV$ACTIVE_SESSION_HISTORY"

tables, releated by "enq: TX - row lock contention" event, in

questioned time period. As a sample scenerio, following sql statement

returns, some records;

SELECT

TO_CHAR(END_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS')

END_INTERVAL_TIME,

TOTAL_WAITS,

TIME_WAITED_MICRO,

ROUND(DECODE(TOTAL_WAITS, 0, 0, TIME_WAITED_MICRO /

TOTAL_WAITS / 1000), 2) AVG_WAIT_MS

FROM

( SELECT

HSS.END_INTERVAL_TIME,

HSE.EVENT_NAME,

HSE.TOTAL_WAITS - LAG(HSE.TOTAL_WAITS, 1) OVER

(ORDER BY HSS.SNAP_ID) TOTAL_WAITS,

HSE.TIME_WAITED_MICRO - LAG(HSE.TIME_WAITED_MICRO, 1) OVER

(ORDER BY HSS.SNAP_ID) TIME_WAITED_MICRO

FROM

DBA_HIST_SYSTEM_EVENT HSE, DBA_HIST_SNAPSHOT HSS

WHERE

HSE.SNAP_ID = HSS.SNAP_ID AND

HSE.EVENT_NAME = 'enq: TX - row lock contention'

ORDER BY

HSS.SNAP_ID DESC

)

WHERE

TOTAL_WAITS >= 0

ORDER BY END_INTERVAL_TIME;

END_INTERVAL_TIME TOTAL_WAITS TIME_WAITED_MICRO AVG_WAIT_MS

-

---

-

---

-

---

-

---

2009-05-31 10:00:36 0 0 0

2009-05-31 11:00:24 0 0 0

2009-05-31 12:00:12 0 0 0

2009-05-31 13:01:03 0 0 0

2009-05-31 14:00:54 0 0 0

2009-05-31 15:00:45 0 0 0

2009-05-31 16:00:33 0 0 0

2009-05-31 17:00:24 0 0 0

2009-05-31 18:00:12 0 0 0

2009-05-31 19:00:03 0 0 0

2009-05-31 20:00:57 0 0 0

END_INTERVAL_TIME TOTAL_WAITS TIME_WAITED_MICRO AVG_WAIT_MS

-

---

-

---

-

---

-

---

2009-05-31 21:00:48 0 0 0

2009-06-01 05:00:05 1 2676 2.68

2009-06-01 06:00:53 1 149 .15

2009-06-01 07:00:44 1 113 .11

2009-06-01 08:00:32 1 353 .35

When I execute a query, on "DBA_HIST_ACTIVE_SESS_HISTORY"

or "GV$ACTIVE_SESSION_HISTORY" tables between "2009-06-01 06:00:53"

and "2009-06-01 08:00:32", it returns no records found. I expect that 3

rows should be returned. Am I right?

All in all, the question is why all the statistical records are not

corresponding among those tables? Anyone has a suggestion?

[fidel_vales · Accepted solution]

Hi,

a quick idea is the different kind of information that the tables(views) contain.

first you have V$system_event.

this view store ALL events that happen since the DB started.

The AWR takes an snapshot every hour of this table that is stored in some WR* tables that you "see" consulting the view DBA_HIST_SYSTEM_EVENT.

that means that you see ALL events

the behavior of ASH (active session history) is quite different.

The oracle process MMNL (Manageability Monitor light) takes a snapshot of the v$session every second and stores the ACTIVE sessions. This means that the ASH does nt shows ALL that happens, but only SAMPLES of what happen.

The data from the ASH is flushed to disk every hour (or before if the buffer is flushed), but only 1 sample of every 10 is stored (1 sample every 10 seconds)

this means that it is possible to find events in V$system_event and not in v$active_session_history.

Note also that the history is only keep for 7 days (by default)

More information in the oracle [documentation|http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/autostat.htm#i35568], as usual.