DBA_HIST_SEG_STAT example

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.

 

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply