Sunday, February 13, 2011

Interesting Observation regarding ASH in 10GR2

was looking thru some reports on a time period when we has Database Contention . I noticed something really Odd in the ASH reports
I have one index that was the top DB Object w.r.t to Db sequential file read. I wanted to move that index to  a different tablespace anyway so i rebuilt the table. What i noticed on the ASH report was very interesting
Prior to my Index Rebuild the ASH report

Top DB Objects

  • With respect to Application, Cluster, User I/O and buffer busy waits only.
Object ID % Activity Event % Event Object Name (Type) Tablespace
135617 13.76 db file sequential read 13.71 INDEX1 (INDEX) X_BPGEN_50M
378375 1.20 enq: TX - row lock contention 1.06 TABLE1 (TABLE) D_PAT_AL1
86047 1.16 db file sequential read 1.16 TABLE2 D_PAT1

Top DB Files

  • With respect to Cluster and User I/O events only.
File ID % Activity Event % Event File Name Tablespace
124 11.70 db file sequential read 11.54 +DATA02/xyz/datafile/x_bpgen_50m.691.727601609 X_BPGEN_50M
146 2.39 db file sequential read 2.18 +DATA02/xyz/datafile/x_bpgen_50m.550.727596551 X_BPGEN_50M
After the Index Rebuild the ASH Report for the same time period

Top DB Objects

  • With respect to Application, Cluster, User I/O and buffer busy waits only.
Object ID % Activity Event % Event Object Name (Type) Tablespace
135617 13.76 db file sequential read 13.71 INDEX1 (INDEX) X_MISC_AL
378375 1.20 enq: TX - row lock contention 1.06 TABlE1 (TABLE) D_PAT_AL1
86047 1.16 db file sequential read 1.16 TABl2 D_PAT1



Top DB Files

  • With respect to Cluster and User I/O events only.
File ID % Activity Event % Event File Name Tablespace
124 11.70 db file sequential read 11.54 +DATA02/xyzdatafile/x_bpgen_50m.691.727601609 X_BPGEN_50M
146 2.39 db file sequential read 2.18 +DATA02/xyzd/datafile/x_bpgen_50m.550.727596551 X_BPGEN_50M
While the Top DB Files remains the same the top DB Objects now has a different table space not because the table space was that at the time of the problem but because it seems oracle is performing a real time query to determine what the current table space this. this can be confusing and misleading since the top db files and the top db objects now no longer match.
Anyone seen this or have any explanation of why Oracle would be doing this.

Google Search