I used this query to debug some updating issues on a table:
select ss.DATAOBJ#, sn.END_INTERVAL_TIME, ss.DB_BLOCK_CHANGES_DELTA, ss.PHYSICAL_WRITES_DELTA from DBA_HIST_SEG_STAT ss, DBA_HIST_SNAPSHOT sn, DBA_HIST_SEG_STAT_OBJ so where so.OWNER='MYUSER' and so.OBJECT_NAME='PLAN_TABLE' and so.OBJECT_TYPE='TABLE' and ss.OBJ#=so.OBJ# and ss.DATAOBJ#=so.DATAOBJ# and ss.snap_id=sn.snap_id order by ss.snap_id,ss.DATAOBJ#;
This is just an example of a PLAN_TABLE in my schema. I changed the schema name for security. Here is edited output:
DATAOBJ# END_INTERVAL_TIME DB_BLOCK_CHANGES_DELTA PHYSICAL_WRITES_DELTA ---------- ------------------------- ---------------------- --------------------- 2956377 17-APR-13 11.00.37.126 AM 32 11 2956387 17-APR-13 11.00.37.126 AM 16 0 2956389 17-APR-13 12.00.55.941 PM 16 8 2956397 17-APR-13 12.00.55.941 PM 3296 35 2956395 17-APR-13 02.00.31.239 PM 0 0
This could be useful to see when a given table was modified.
– Bobby
P.S. I think the DATAOBJ# changes when I truncate the table. I usually truncate my PLAN_TABLE before doing a plan to make sure I don’t get an old plan by accident.
