Re: what is the meaning of statistic_name='space_used' in v$segment_statistics?
Date: Wed, 6 Oct 2010 16:04:53 +0200
Message-ID: <AANLkTi=O6NBQfu3VQHDZmZZTthavo1F0CYLT57EKvg2A_at_mail.gmail.com>
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 9437184638064
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_at_gmail.com> 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_at_baziel.com>
>
> 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
>>
>>
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 06 2010 - 09:04:53 CDT