asked on

Table growth - Mismatch b\w dba_objects and dba_hist_seg_Stat

Hello All,

I am trying to track the data growth for specific objects(object names with '%2MP%') in ORACLE 11G database(Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production) for the last 60 days using the below sql:

select * from (select to_char(end_interval_time, 'MM/DD/YY') mydate, segment_name,SEGMENT_TYPE, sum(space_used_delta) / 1024 / 1024 "Space used (MB)", avg(c.bytes) / 1024 / 1024 "Total Object Size (MB)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where begin_interval_time > trunc(sysdate) - 60
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.segment_name like '%2MP%'
and C.owner = 'DITMGR'
group by to_char(end_interval_time, 'MM/DD/YY'), segment_name, SEGMENT_TYPE)
order by segment_name,SEGMENT_TYPE, to_date(mydate, 'MM/DD/YY');

Issue:

Some objects(which are present in DB) are missing from my sql output. I thought those objects were not analyzed recently but statistics were gathered for the whole database yesterday night. Join condition between dba_hist_seg_Stat.obj# = dba_objects.object_id is failing for many objects. Please let me know what am I missing here?

Regards
Siva

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sivi_3883

ASKER

Thanks for your response slightwv.

Sorry. I didnt explain my question in the right way.
Why all objects are not present in dba_hist_seg_Stat? Is there any procedure I need to run so that oracle inbuilt views like dba_hist_seg_Stat get refreshed?

I expected all objects would be present in dba_hist_seg_Stat as long as the data size in those objects are being modified.
Moreover, I am also seeing output with 0 MB as the delta space used from my sql

SEGMENT_NAME SEGMENT_TYPE Space used (MB) MYDATE Total Object Size (MB)
LIEN_MATCH_FROM_LPS_2MP TABLE 0 10/28/11 1,530
LIEN_MATCH_FROM_LPS_2MP TABLE 0 10/30/11 1,530

Does it mean that dba_hist_seg_Stat tracks objects even if size is not changed?

slightwv (䄆 Netminder)

I would have to refer to the docs on that view and what is in it.

On mobile right now an cannot look it up. Hopefully another Expert will be along later that knows that view.

slightwv (䄆 Netminder)

From the docs: "This view captures the top segments based on a set of criteria"

http://download.oracle.com/docs/cd/E11882_01/server.112/e25513/statviews_4032.htm#REFRN23436

It doesn't sound like this view is supposed to have ALL objects.

sivi_3883

ASKER

Exactly. It depends upon a set of criteria.
My understanding was that if the data size/volume changes for an object and stats are gathered , it would appear on dba_hist_seg_Stat.
I thought the missing objects have no size/volume changes recently(still surprising because many missing tables from dba_hist_seg_Stat are loaded with new data on a daily basis and the data volume for those tables didnt change for the last 60 days? really?).
Also I am able to see objects in dba_hist_seg_Stat whose SPACE_USED_DELTA is 0MB. So even if the data volume/size does not change they still appear on dba_hist_seg_Stat.

I am still figuring out what is the criteria for the objects to come in dba_hist_seg_Stat:(

slightwv (䄆 Netminder)

Sorry but I'm not familiar with that view.

You can click the 'Request Attention' link above and see if a moderator can locate some additional Experts.

You might also want to contact Oracle Support.

ASKER CERTIFIED SOLUTION

johnsmith1962

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial