Re: what is the meaning of statistic_name='space_used' in v$segment_statistics?
- From: Baziel de Leeuw <baziel@xxxxxxxxxx>
- To: Gerwin Hendriksen <gerwin.hendriksen@xxxxxxxxx>
- Date: Wed, 6 Oct 2010 16:04:53 +0200
hi Gerwin,
long time no see!
Thanks for answering, but i don't know, that specific table isn't that big.
SQL> select bytes from dba_segments where owner='SYS'and
segment_name='WRI$_OPTSTAT_HISTGRM_HISTORY';
BYTES
------------------
31457280
and i doubt that that column is so small that 30mb makes it overflow.
Also: the values do not seem to be consistent with anything i can find space
related
e.g.
select a.owner, a.segment_name, sum(a.bytes) , sum(b.value)
from dba_segments a , v$segment_statistics b
where a.segment_name=b.object_name
and b.statistic_name='space used'
and a.owner='SYS'
group by a.owner, a.segment_name
order by 3,4,2;
gives values like
SYS I_ARGUMENT2
9437184 0
SYS I_H_OBJ#_COL# 9437184
23278
SYS WRH$_FILESTATXS_PK 9437184
273456
SYS WRH$_SGASTAT_U 9437184
273456
SYS WRI$_ADV_OBJECTS 9437184
393219
SYS WRH$_WAITSTAT_PK 9437184
638064
So for the same number of bytes in dba_segments you can have different
values of 'space used' in v$segment_statistics which i would not have
expected in a column overflow.
and also
SYS IDL_UB2$
34603008 0
SYS IDL_UB1$
201326592 0
SYS AUD$
226492416 0
SYS SOURCE$
285212672 0
SYS WRI$_ADV_TASKS 262144
-983
SYS WRH$_LOG 131072
-1065
SYS WRH$_LIBRARYCACHE 196608
-1147
SYS WRH$_SEG_STAT_OBJ 524288
-1966
There are small tables with negative values and large tables with a 'space
used' of 0.
What do you think? What could it be measuring?
Baziel
On Wed, Oct 6, 2010 at 3:23 PM, Gerwin Hendriksen <
gerwin.hendriksen@xxxxxxxxx> wrote:
> Hi Baziel,
>
> I think the problem here is a column overflow as sometimes happens with for
> example the statistic "redo size" in v$sesstat, when the number are getting
> really big, above 4294967296 (32 bit border), the number wents to negative.
>
> Regards,
>
> Gerwin
>
> 2010/10/6 Baziel de Leeuw <baziel@xxxxxxxxxx>
>
> Hi,
>>
>> What does the statistic_name='space used' column in v$segment_statistics
>> show?
>>
>> I get results i don't understand at all, e.g.
>>
>> SQL> select owner, object_name, statistic_name, value from
>> v$segment_statistics where owner='SYS' and
>> object_name='WRI$_OPTSTAT_HISTGRM_HISTORY' and statistic_name='space used';
>>
>> OWNER OBJECT_NAME STATISTIC_NAME
>> VALUE
>> ---------- ------------------------------ ---------------
>> ------------------
>> SYS WRI$_OPTSTAT_HISTGRM_HISTORY space used
>> -554110
>>
>> Yes, that is a negative value.
>>
>> I expected beforehand that space_used would be something like bytes in
>> memory or something like that.
>>
>> Does anyone know what it actually is?
>>
>> Baziel
>>
>>
>>
>
Other related posts:
