Large Objects (LOBs) are designed to hold large amounts of data. They are suitable for semi-structured data (like XML documents) and unstructured data (like MP3 files). There are two basic types: external LOBs, which are stored outside the database, and internal LOBs that are stored inside database tablespaces. Each LOB has a locator and a value. The LOB locator is a reference to where the LOB value is physically stored. LOB columns in the database can’t be manipulated with standard DML statements, you need to use an Oracle supplied package called DBMS_LOB.
Moving Data to LOBs
The recommended procedure is as follows:
- INSERT an empty LOB, RETURNING the LOB locator.
- Move data into the LOB using this locator.
- COMMIT, which releases the ROW locks and makes the LOB data persistent.
LOBs statistics
Session statistics are accessible through the V$MYSTAT, V$SESSTAT, and V$SYSSTAT dynamic performance views. And three session-level statistics specific to LOBs are available to users:
- LOB reads.
- LOB writes.
- LOB writes unaligned.
Note: A single LOB API read/writemay correspond to multiple physical/logical disk block reads/writes.
Example code
The following example demonstrates how LOB session statistics are updated as the user performs read and write operations on LOBs.
First create a database user and grant him all necessary privileges:
SQL> CONNECT / AS SYSDBA;
Connected.
SQL> SET ECHO ON;
SQL> CREATE USER TEST IDENTIFIED BY TEST1234
2 QUOTA UNLIMITED ON USERS;
User created.
SQL> GRANT CREATE TABLE TO TEST;
Grant succeeded.
SQL> GRANT CREATE SESSION TO TEST;
Grant succeeded.
SQL> GRANT CREATE VIEW TO TEST;
Grant succeeded.
SQL> GRANT SELECT_CATALOG_ROLE TO TEST;
Grant succeeded.
SQL> GRANT SELECT ON sys.v_$mystat TO TEST;
Grant succeeded.
SQL> GRANT SELECT ON sys.v_$statname TO TEST;
Grant succeeded.Now create a simplified view for statistics queries:
SQL> CONNECT TEST/TEST1234;
Connected.
SQL> SET ECHO ON;
SQL> CREATE VIEW v_lobstats
2 AS
3 SELECT SUBSTR(n.name,1,20) name,
4 m.value
5 FROM v$mystat m,
6 v$statname n
7 WHERE m.statistic# = n.statistic#
8 AND n.name LIKE 'lob%';
View created.Let’s create a table with a CLOB type column. For our porpuses:
SQL> CREATE TABLE t_test (i NUMBER, c CLOB)
2 lob(c) STORE AS (DISABLE STORAGE IN ROW);
Table created.And now populate the table with some data. This should result in unaligned writes for each row populated.
SQL> SELECT * FROM v_lobstats;
NAME VALUE
-------------------- ----------
lob reads 0
lob writes 0
lob writes unaligned 0
SQL> INSERT INTO t_test VALUES (1, 'a');
1 row created.
SQL> INSERT INTO t_test VALUES (2, rpad('a',4000,'a'));
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM v_lobstats;
NAME VALUE
-------------------- ----------
lob reads 0
lob writes 2
lob writes unaligned 2Next step is getting the lob length, this operation will not read lob data so reads and writes statistics remain unchanged:
SQL> SELECT LENGTH(c) FROM t_test;
LENGTH(C)
----------
1
4000
SQL> SELECT * FROM v_lobstats;
NAME VALUE
-------------------- ----------
lob reads 0
lob writes 2
lob writes unaligned 2Now we will read the lobs one for each lob in the table:
SQL> SELECT * FROM t_test;
I C
---------- ------------------------------------------------------------
1 a
2 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
SQL> SELECT * FROM v_lobstats;
NAME VALUE
-------------------- ----------
lob reads 2
lob writes 2
lob writes unaligned 2Now is a good time to perform more complex operations such as manipulating lobs through temporary lobs.he use of complex operators like “substr()” results in the implicit creation and use of temporary lobs.These operations on temporary lobs also update lob statistics.
SQL> SELECT substr(c, length(c), 1) FROM t_test;
SUBSTR(C,LENGTH(C),1)
-----------------------------------------------------------------------------
a
a
SQL> SELECT substr(c, 1, 1) FROM t_test;
SUBSTR(C,1,1)
-----------------------------------------------------------------------------
a
a
SQL> SELECT * FROM v_lobstats;
NAME VALUE
-------------------- ----------
lob reads 6
lob writes 6
lob writes unaligned 6Let’s see what happens now performing some aligned overwrites. Only lob write statistics are updated because both the byte offset of the write, and the size of the buffer being written are aligned on the lob chunksize.
SQL> DECLARE
2 loc CLOB;
3 buf LONG;
4 chunk NUMBER;
5 BEGIN
6 SELECT c INTO loc FROM t_test WHERE i = 1
7 FOR UPDATE;
8
9 chunk := DBMS_LOB.GETCHUNKSIZE(loc);
10 buf := rpad('b', chunk, 'b');
11
12 -- aligned buffer length and offset
13 DBMS_LOB.WRITE(loc, chunk, 1, buf);
14 DBMS_LOB.WRITE(loc, chunk, 1+chunk, buf);
15 COMMIT;
16 END;
17 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM v_lobstats;
NAME VALUE
-------------------- ----------
lob reads 6
lob writes 8
lob writes unaligned 6And now let’s see what happens performing some unaligned overwrites. In this case both lob write and lob unaligned write statistics are updated because either one or both of the write byte offset and buffer size are unaligned with the lob’s chunksize.
SQL> DECLARE
2 loc CLOB;
3 buf LONG;
4 BEGIN
5 SELECT c INTO loc FROM t_test WHERE i = 1
6 FOR UPDATE;
7
8 buf := rpad('b', DBMS_LOB.GETCHUNKSIZE(loc), 'b');
9
10 -- unaligned buffer length
11 DBMS_LOB.WRITE(loc, DBMS_LOB.GETCHUNKSIZE(loc)-1, 1, buf);
12
13 -- unaligned start offset
14 DBMS_LOB.WRITE(loc, DBMS_LOB.GETCHUNKSIZE(loc), 2, buf);
15
16 -- unaligned buffer length and start offset
17 DBMS_LOB.WRITE(loc, DBMS_LOB.GETCHUNKSIZE(loc)-1, 2, buf);
18
19 COMMIT;
20 END;
21 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM v_lobstats;
NAME VALUE
-------------------- ----------
lob reads 6
lob writes 11
lob writes unaligned 9This example code enables you to see how the lob statistics change for the specific operation you are testing.
TIP: Session statistics are aggregated across operations to all LOBs accessed in a session so you can reconnect to the database for each demonstration to clear the V$MYSTAT.
Link to script that contains the examples in this post.