Home » RDBMS Server » Performance Tuning » DBA_HIST_SEG_STAT view (11.2.0.3)
DBA_HIST_SEG_STAT view [message #604923] Mon, 06 January 2014 03:22 Go to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Hi Experts,

I executed below query and I'm confused with the output

select * from (SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE ,
    t.NAME "Tablespace", s.growth/(1024*1024) "Growth in MB",
    (SELECT sum(bytes)/(1024*1024*1024)
    FROM dba_segments
    WHERE segment_name=o.object_name) "Total Size(GB)"
FROM DBA_OBJECTS o,
    ( SELECT TS#,OBJ#,
        SUM(SPACE_USED_DELTA) growth
   FROM DBA_HIST_SEG_STAT
    GROUP BY TS#,OBJ#
    HAVING SUM(SPACE_USED_DELTA) > 0
    ORDER BY 2 DESC ) s,
    v$tablespace t
WHERE s.OBJ# = o.OBJECT_ID
AND s.TS#=t.TS# and o.owner='XXXXXX' and o.object_type='TABLE'
ORDER BY 6 DESC) where rownum<6;


OUTPUT

OWNER	OBJECT_NAME	SUBOBJECT_NAME	OBJECT_TYPE	Tablespace    Growth in MB	Total Size(GB)
XXXXXX	YYYY1		                    TABLE	USERS	        2,405.97	  0.02
XXXXXX	YYYY2		                    TABLE	USERS	        597.20	          0.01
XXXXXX	YYYY3		                    TABLE	USERS	        365.79	          0.32
XXXXXX	YYYY4		                    TABLE	USERS	        360.48	          0.05
XXXXXX	YYYY5		                    TABLE	USERS	        343.07	          0.06


Consider object "YYYY1" having growth of 2405 MB but its size is 0.02 GB (0.02*1024=20.48 MB)
How is that possible?
same goes for all the other objects, Please let me know what am I missing ?
Re: DBA_HIST_SEG_STAT view [message #604926 is a reply to message #604923] Mon, 06 January 2014 03:41 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I think you copied that script from here:
http://oraworklog.wordpress.com/category/database-monitoring/
If so, you should ask the author to explain it.
Re: DBA_HIST_SEG_STAT view [message #604929 is a reply to message #604926] Mon, 06 January 2014 04:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition, the sentence "No extra licensing cost as this feature is available automatically." is wrong.
To be allowed to query the DBA_HIST% views you must purchase the Diagnostics pack (or something like that).

Re: DBA_HIST_SEG_STAT view [message #604933 is a reply to message #604929] Mon, 06 January 2014 04:22 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
huh!! I'm not insane Cool , I thought i did some mathematical blunder!! Laughing

Well thanks guyies for the reply!!

Michel,

I'm unable to follow your comment, As per my understanding either we are having the license or it doesn't require a licence to query DBA_HIST%, because otherwise we would have got an error ,I'm not sure about data but sure we can query it.

Please correct me if I'm wrong.
Re: DBA_HIST_SEG_STAT view [message #604934 is a reply to message #604933] Mon, 06 January 2014 04:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The fact you (physically) can query the views does not mean you are allowed to do it.
You (legally) can query these views ONLY if you purchase the pack.
Oracle retrieves and stores the data even if you didn't purchase the pack.

It is like a paying car park. There are places even if no one is using them it and you can use them only if you pay for your place (even if there are some not used places).

Re: DBA_HIST_SEG_STAT view [message #605047 is a reply to message #604934] Tue, 07 January 2014 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From MOS note 1490798.1

Quote:
The Automatic Workload Repository (AWR) is a feature that allows database information to be recorded for multiple purposes including detection and elimination of performance issues. By default AWR data collection is enabled collecting various information on database activity. Many database features such as Automatic Segment Advisor and Undo Advisor need information captured by the AWR. Use of these features, which implicitly access some AWR views, does NOT require any Pack licenses. HOWEVER, direct access to AWR views and reports is NOT permitted without the Diagnostic Pack license.

In order to produce reports, access AWR views or use the diagnostic information from any part of the Automatic Workload Repository the Diagnostic Pack License is required. This includes the production of AWR reports, ADDM reports and ASH reports even if these are requested by product support or other agencies. It is the user responsibility to comply with license requirements and not violate license agreements.
Re: DBA_HIST_SEG_STAT view [message #616719 is a reply to message #604929] Thu, 19 June 2014 20:49 Go to previous messageGo to next message
rmadabhushanam
Messages: 1
Registered: June 2014
Location: Nashua,NH
Junior Member
Hi Michel,

Well. It might be hard to believe that some things in Oracle actually free.. but it is...

Please refer to Oracle Licensing documentation at http://docs.oracle.com/cd/E11882_01/license.112/e47877/options.htm#DBLIC168.

Quote:
All data dictionary views beginning with the prefix DBA_HIST_ are part of this pack, along with their underlying tables.The only exception are the views: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE, DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ, and DBA_HIST_UNDOSTAT. They can be used without the Oracle Diagnostics Pack license.


Same question was also discussed in many otn forums.. one of those links is here..
https://community.oracle.com/thread/2361757?start=0&tstart=0

HTH..

Thanks,
Ravi.M
Re: DBA_HIST_SEG_STAT view [message #616724 is a reply to message #616719] Fri, 20 June 2014 00:50 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Things change with the time, thanks for this information.

Previous Topic: SQL performance tunning :- DBMS_STATS procedure errored out
Next Topic: Calculating schema statistics during a huge 10 hour batch job
Goto Forum:
  


Current Time: Thu Mar 28 09:03:56 CDT 2024