Re: what is the meaning of statistic_name='space_used' in v$segment_statistics?

From: Baziel de Leeuw <baziel_at_baziel.com>
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                          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_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-l
Received on Wed Oct 06 2010 - 09:04:53 CDT

Original text of this message