A Simple Test of V$SEGSTAT_NAME and V$SEGMENT_STATISTICS

This was tested on Oracle 11.2.0.2.7. V$SEGSTAT_NAME shows the names of various statistics which Oracle records. The SAMPLED column shows whether the figures are obtained by sampling or not: 

SQL> l

  1* select * from v$segstat_name

SQL> /

 

STATISTIC# NAME                           SAMPLED

---------- ------------------------------ -------

         0 logical reads                  YES

         1 buffer busy waits              NO

         2 gc buffer busy                 NO

         3 db block changes               YES

         4 physical reads                 NO

         5 physical writes                NO

         6 physical read requests         NO

         7 physical write requests        NO

         8 physical reads direct          NO

         9 physical writes direct         NO

        11 optimized physical reads       NO

        12 gc cr blocks received          NO

        13 gc current blocks received     NO

        14 ITL waits                      NO

        15 row lock waits                 NO

        17 space used                     NO

        18 space allocated                NO

        20 segment scans                  NO

 

18 rows selected.

 

SQL>

 

The statistics themselves are recorded in V$SEGMENT_STATISTICS:

 

SQL> desc v$segment_statistics

Name                       Null?    Type

-------------------------- -------- ------------------

OWNER                               VARCHAR2(30)

OBJECT_NAME                         VARCHAR2(30)

SUBOBJECT_NAME                      VARCHAR2(30)

TABLESPACE_NAME                     VARCHAR2(30)

TS#                                 NUMBER

OBJ#                                NUMBER

DATAOBJ#                            NUMBER

OBJECT_TYPE                         VARCHAR2(18)

STATISTIC_NAME                      VARCHAR2(64)

STATISTIC#                          NUMBER

VALUE                               NUMBER

 

SQL>

 

I thought the row lock waits statistic looked interesting so I decided to test it. First, in the red session below, I created a table, inserted a row into it, committed it then updated it:

 

SQL> conn andrew/reid

Connected.

SQL> create table lock_test

  2  (col1 varchar2(10))

  3  /

 

Table created.

 

SQL> insert into lock_test values ('ANDREW')

  2  /

 

1 row created.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL> update lock_test

  2  set col1 = 'BRIAN'

  3  /

 

1 row updated.

 

SQL>

 

Then, in a separate session, shown in blue, I checked if there had been any row lock waits on the table:

 

SQL> conn / as sysdba

Connected.

SQL> select value from v$segment_statistics

  2  where owner = 'ANDREW'

  3  and object_name = 'LOCK_TEST'

  4  and statistic_name = 'row lock waits'

  5  /

 

     VALUE

----------

         0

 

SQL>

 

I started a third session, in green, viewed the contents of the LOCK_TEST table then tried to update it. As you might expect, this session had to wait for the red session above:

 

SQL> conn andrew/reid

Connected.

SQL> select col1 from lock_test

  2  /

 

COL1

----------

ANDREW

 

SQL> update lock_test

  2  set col1 = 'COLIN'

  3  /

 

I returned to the blue session and verified that the row lock waits figure for the table had increased by 1:

 

SQL> l

  1  select value from v$segment_statistics

  2  where owner = 'ANDREW'

  3  and object_name = 'LOCK_TEST'

  4* and statistic_name = 'row lock waits'

SQL> /

 

     VALUE

----------

         1

 

SQL>