Interpreting v$segment

Interpreting v$segment_statistics

Updated May 18, 2018

Hello all ;'

I am trying to check Information about segment level statistics.
but I cannot interpret statistics # , and it's value.
Can anyone help me to understand this?

SYS>select STATISTIC_NAME , STATISTIC# , VALUE from  v$segment_statistics
  2  WHERE owner = '***'  AND object_name='*****';

STATISTIC_NAME         STATISTIC#      VALUE
------- ----------  ----------

logical reads 0 939312

buffer busy waits            1             0
gc buffer busy               2             0
db block changes             3        360544
physical reads               4          1359
physical writes              5          4923
physical reads direct        6             0
physical writes direct       7             0
gc cr blocks received        9             0
gc current blocks received  10             0
ITL waits                   11             0
row lock waits              12             0
space used                  14      11615747
space allocated             15      13631488
segment scans               17             2

Thanks

DB version is : 10.2.0.1
OEL 4.4

Comments

10 Jul 2013 11:10 am Sushma
The statistic# is just an arbitrary number assigned to each statistic.
The value is dependent on the statistic:
For reads/writes/waits they are actual values, for space, they are bytes.

10 Jul 2013 11:11 am amit butola
Hello everyone ;

I am learning tuning area (just started). My lead asked me to interpret following things. when i have queried i got following results ..already i raised 1st question here. I am very quiet young in oracle. Please discuss in detail .

QUES 1

SQL>select STATISTIC_NAME , STATISTIC# , VALUE from v$segment_statistics
2 WHERE owner = 'SAM' AND object_name='TAB1';
STATISTIC_NAME STATISTIC# VALUE
----------------- ------------ ---------- ----------
logical reads 0 2273008
buffer busy waits 1 0
gc buffer busy 2 0
db block changes 3 2198160
physical reads 4 1662
physical writes 5 5581
physical reads direct 6 0
physical writes direct 7 0
gc cr blocks received 9 0
gc current blocks received 10 0
ITL waits 11 0
row lock waits 12 2
space used 14 -8118083
space allocated 15 0
segment scans 17 1
15 rows selected.

QUES 2

I dont understand 2nd question. what kind of indexes here. please provide some explanation about this.

SQL> select segment_name , object_type , total_physical_reads
2 from (select owner||'.'||object_name as segment_name , object_type,
3 value as total_physical_reads
4 from v$segment_statistics
5 where statistic_name in ('physical reads')
6 order by total_physical_reads desc)
7 where rownum <=10;
SEGMENT_NAME OBJECT_TYPE TOTAL_PHYSICAL_READS
------------------------------- ------------ --------------------
SYS.SMON_SCN_TIME TABLE 50
SYS.I_HH_OBJ#_INTCOL# INDEX 25
SYS.KOTTD$ TABLE 24
SYS.KOTAD$ TABLE 15
SYS.WRI$_ADV_PARAMETERS TABLE 11
SYS.SYS_C00650 INDEX 10

SYS.WRI$_ADV_PARAMETERS_PK INDEX 10

SYS.KOTTB$ TABLE 8

SYSMAN.MGMT_SYSTEM_PERFORMANCE_LOG TABLE 8

SYSMAN.MGMT_STRING_METRIC_HISTORY TABLE 8
10 rows selected.

thanks ..

10 Jul 2013 11:12 am amit butola
Thanks for your reply.

I don't understand this question. what kind of indexes here. please provide some explanation about this.

SQL> select segment_name , object_type , total_physical_reads
2 from (select owner||'.'||object_name as segment_name , object_type,
3 value as total_physical_reads
4 from v$segment_statistics
5 where statistic_name in ('physical reads')
6 order by total_physical_reads desc)
7 where rownum <=10;
SEGMENT_NAME OBJECT_TYPE TOTAL_PHYSICAL_READS