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.
