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: 4082
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: 57612
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: 57612
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 message
Michel Cadot
Messages: 57612
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.
Previous Topic: Please help me merge query is taking more time
Next Topic: Performance issue
Goto Forum:
  


Current Time: Sat Apr 19 08:09:43 CDT 2014

Total time taken to generate the page: 0.05232 seconds