We help IT Professionals succeed at work.

Table growth - Mismatch b\w dba_objects and dba_hist_seg_Stat

sivi_3883
sivi_3883 asked
on
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
Comment
Watch Question

SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Sound like an outer join issue.

Not where I can test this but try the old outer join syntax:

dba_hist_seg_Stat.obj#(+) = dba_objects.object_id

I always confuse the '+' side so you might need to reverse it.

Author

Commented:
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?
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.

Author

Commented:
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:(
SILVER EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.
The DBA_HIST* tables are a set of history tables that contain data for the AWR and related reports.  They are not designed to contain data about every object in the database, they would only contain data if those objects grew or had other activity that was noticeable, probably at a certain threshold.  You would have to talk with MOS to get exactly what criteria they would need to meet to show up on the DBA_HIST tables, but suffice to say that DBA_HIST tables will not have information about every object in the database.  If you need information about every segment in the database, you should lookat DBA_SEGMENTS, not DBA_HIST_SEGMENT_STATS