11.3 LOB Access Statistics

Three session-level statistics specific to LOBs are available to users: LOB reads, LOB writes, and LOB writes unaligned.

Session statistics are accessible through the V$MYSTAT, V$SESSTAT, and V$SYSSTAT dynamic performance views. To query these views, the user must be granted the privileges SELECT_CATALOG_ROLE, SELECT ON SYS.V_$MYSTAT view, and SELECT ON SYS.V_$STATNAME view.

LOB reads is defined as the number of LOB API read operations performed in the session/system. A single LOB API read may correspond to multiple physical/logical disk block reads.

LOB writes is defined as the number of LOB API write operations performed in the session/system. A single LOB API write may correspond to multiple physical/logical disk block writes.

LOB writes unaligned is defined as the number of LOB API write operations whose start offset or buffer size is not aligned to the LOB block boundary. Writes aligned to block boundaries are the most efficient write operations. The usable LOB block size of a LOB is available through the LOB API (for example, using PL/SQL, by DBMS_LOB.GETCHUNKSIZE()).

It is important to note that session statistics are aggregated across operations to all LOBs accessed in a session; the statistics are not separated or categorized by objects (that is, table, column, segment, object numbers, and so on). Oracle recommends that you reconnect to the database for each demonstration to clear the V$MYSTAT. This enables you to see how the lob statistics change for the specific operation you are testing, without the potentially obscuring effect of past LOB operations within the same session.

See also:

Oracle Database Reference, appendix E, "Statistics Descriptions"

This example demonstrates how LOB session statistics are updated as the user performs read or write operations on LOBs.

rem
rem Set up the user
rem
 
CONNECT / AS SYSDBA;
SET ECHO ON; 
GRANT SELECT_CATALOG_ROLE TO pm;
GRANT SELECT ON sys.v_$mystat TO pm;
GRANT SELECT ON sys.v_$statname TO pm;
 
rem
rem Create a simplified view for statistics queries
rem
 
CONNECT pm/pm;
SET ECHO ON;
 
DROP VIEW mylobstats;
CREATE VIEW mylobstats
AS
SELECT  SUBSTR(n.name,1,20) name,
        m.value             value
FROM    v$mystat    m,
        v$statname  n
WHERE   m.statistic# = n.statistic#
    AND n.name LIKE 'lob%';
 
rem
rem Create a test table
rem
 
DROP TABLE t;
CREATE TABLE t (i NUMBER, c CLOB)
    lob(c) STORE AS (DISABLE STORAGE IN ROW);
 
rem
rem Populate some data
rem
rem This should result in unaligned writes, one for
rem each row/lob populated.
rem

CONNECT pm/pm
SELECT * FROM mylobstats;
INSERT INTO t VALUES (1, 'a');
INSERT INTO t VALUES (2, rpad('a',4000,'a'));
COMMIT;
SELECT * FROM mylobstats;
 
rem
rem Get the lob length
rem
rem Computing lob length does not read lob data, no change
rem in read/write stats.
rem
 
CONNECT pm/pm;
SELECT * FROM mylobstats;
SELECT LENGTH(c) FROM t;
SELECT * FROM mylobstats;
 
rem
rem Read the lobs
rem
rem Lob reads are performed, one for each lob in the table.
rem
 
CONNECT pm/pm;
SELECT * FROM mylobstats;
SELECT * FROM t;
SELECT * FROM mylobstats;
 
rem
rem Read and manipulate the lobs (through temporary lobs)
rem
rem The use of complex operators like "substr()" results in
rem the implicit creation and use of temporary lobs. operations
rem on temporary lobs also update lob statistics.
rem
 
CONNECT pm/pm;
SELECT * FROM mylobstats;
SELECT substr(c, length(c), 1) FROM t;
SELECT substr(c, 1, 1) FROM t;
SELECT * FROM mylobstats;
 
rem
rem Perform some aligned overwrites
rem
rem Only lob write statistics are updated because both the
rem byte offset of the write, and the size of the buffer
rem being written are aligned on the lob block size.
rem
 
CONNECT pm/pm;
SELECT * FROM mylobstats;
DECLARE
    loc     CLOB;
    buf     LONG;
    chunk   NUMBER;
BEGIN
    SELECT c INTO loc FROM t WHERE i = 1
        FOR UPDATE;
 
    chunk := DBMS_LOB.GETCHUNKSIZE(loc);
    chunk = chunk * floor(32767/chunk); /* integer multiple of chunk */
    buf   := rpad('b', chunk, 'b');
 
    -- aligned buffer length and offset
    DBMS_LOB.WRITE(loc, chunk, 1, buf);
    DBMS_LOB.WRITE(loc, chunk, 1+chunk, buf);
    COMMIT;
END;
/
SELECT * FROM mylobstats;
 
rem
rem Perform some unaligned overwrites
rem 
rem Both lob write and lob unaligned write statistics are
rem updated because either one or both of the write byte offset
rem and buffer size are unaligned with the lob's chunksize.
rem 
 
CONNECT pm/pm;
SELECT * FROM mylobstats;
DECLARE
    loc CLOB;
    buf LONG;
BEGIN
    SELECT c INTO loc FROM t WHERE i = 1
        FOR UPDATE;
 
    buf := rpad('b', DBMS_LOB.GETCHUNKSIZE(loc), 'b');
 
    -- unaligned buffer length
    DBMS_LOB.WRITE(loc, DBMS_LOB.GETCHUNKSIZE(loc)-1, 1, buf);
 
    -- unaligned start offset
    DBMS_LOB.WRITE(loc, DBMS_LOB.GETCHUNKSIZE(loc), 2, buf);
 
    -- unaligned buffer length and start offset
    DBMS_LOB.WRITE(loc, DBMS_LOB.GETCHUNKSIZE(loc)-1, 2, buf);
 
    COMMIT;
END;
/
SELECT * FROM mylobstats;
DROP TABLE t;
DROP VIEW mylobstats;
 
CONNECT / AS SYSDBA
REVOKE SELECT_CATALOG_ROLE FROM pm;
REVOKE SELECT ON sys.v_$mystat FROM pm;
REVOKE SELECT ON sys.v_$statname FROM pm;
 
QUIT;