Thursday, February 14, 2013

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>

No comments: