The data in the OutOfLine LOB, being stored in a separate segment (which is of type 'LOBSEGMENT') is accessed via an Index (which is of type 'LOBINDEX').
It is easy to map a LOB Segment to the actual Table Column. The Segment Name contains the ObjectID and ColumnID. Alternatively, you can query USER_LOBs.
OutOfLine LOBs are read and written in CHUNK sizes. Typically the CHUNK size is one datablock, although it can be defined to be a large multiple of the datablock size. Therefore, if you store less than a datablock sized contents in a LOB, the I/O is still executed as a datablock. Thus, in an 8KB tablespace, the I/O is one 8KB datablock even if you store only a few tens of bytes or hundreds of bytes in a deliberately defined OutOfLine LOB.
Here is a simple demonstration of LOB Storage and I/O. I start with a table that has 2 OutOfLine LOBs and one Inline LOB (remember : data exceeding 4000 bytes in Inline LOB is automatically stored OutOfLine so Oracle precreates a LOBSEGMENT even for the Inline LOB).
SQL> drop table table_with_LOBs;
Table dropped.
SQL>
SQL> create table table_with_LOBs
2 (id_column number not null primary key,
3 ool_lob_1 clob,
4 ool_lob_2 clob,
5 inline_lob clob,
6 date_col date,
7 varchar_col varchar2(50))
8 lob (ool_lob_1)
9 store as (disable storage in row tablespace Uniform_64KB chunk 8192 nocache logging),
10 lob (ool_lob_2)
11 store as (disable storage in row tablespace Uniform_64KB chunk 8192 nocache logging),
12 lob (inline_lob)
13 store as (enable storage in row)
14 /
Table created.
SQL>
SQL>
SQL> insert into table_with_LOBs
2 select rownum, -- id_column
3 dbms_random.string('A',10), -- ool_lob_1 at 10 characters per row
4 dbms_random.string('A',1000), -- ool_lob_2 at 1000 characters per row
5 dbms_random.string('A',1500), -- inline_lob
6 sysdate,
7 dbms_random.string('A',20) -- varchar
8 from dual
9 connect by level < 1001
10 /
1000 rows created.
SQL>
SQL> -- measure the writes
SQL> -- notice how each LOB column had a write call (3 x 1000)
SQL> -- also the data written is less than the CHUNKSIZE so it is 'unaligned'
SQL> select n.name, s.value
2 from v$statname n, v$mystat s
3 where n.statistic#=s.statistic#
4 and n.name in ('lob reads','lob writes', 'lob writes unaligned')
5 /
NAME VALUE
---------------------------------------------------------------- ----------
lob reads 0
lob writes 3000
lob writes unaligned 3000
SQL> commit;
Commit complete.
SQL>
SQL> -- compute statisics
SQL> exec dbms_stats.gather_table_stats('','TABLE_WITH_LOBS',estimate_percent=>100,cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>
SQL> -- get column lengths
SQL> select column_name, data_type, data_length, avg_col_len
2 from user_tab_columns
3 where table_name = 'TABLE_WITH_LOBS'
4 order by column_id
5 /
COLUMN_NAME DATA_TYPE DATA_LENGTH AVG_COL_LEN
------------------------------ ---------- ----------- -----------
ID_COLUMN NUMBER 22 4
OOL_LOB_1 CLOB 476 87
OOL_LOB_2 CLOB 476 87
INLINE_LOB CLOB 4000 3103
DATE_COL DATE 7 8
VARCHAR_COL VARCHAR2 50 21
6 rows selected.
SQL>
SQL> -- get Table size
SQL> select blocks, num_rows, avg_row_len
2 from user_tables
3 where table_name = 'TABLE_WITH_LOBS'
4 /
BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- -----------
622 1000 3310
SQL> select blocks, bytes/1048576 Size_MB
2 from user_segments
3 where segment_type = 'TABLE'
4 and segment_name = 'TABLE_WITH_LOBS'
5 /
BLOCKS SIZE_MB
---------- ----------
640 5
SQL>
SQL> -- get LOBSegment sizes -- note how the OutOfLine LOBs are 1 8KB block per row
SQL> select l.column_name, l.segment_name, s.blocks, s.bytes/1048576 Size_MB
2 from user_lobs l, user_segments s
3 where l.segment_name = s.segment_name
4 and l.table_name = 'TABLE_WITH_LOBS'
5 and s.segment_type = 'LOBSEGMENT'
6 order by 1
7 /
COLUMN_NAME SEGMENT_NAME BLOCKS SIZE_MB
------------------------------ ------------------------------ ---------- ----------
INLINE_LOB SYS_LOB0000086489C00004$$ 8 .0625
OOL_LOB_1 SYS_LOB0000086489C00002$$ 1152 9
OOL_LOB_2 SYS_LOB0000086489C00003$$ 1152 9
SQL>
SQL> -- restart the database instance so that V$SEGMENT_STATISTICS are reset
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 201329052 bytes
Database Buffers 209715200 bytes
Redo Buffers 6103040 bytes
Database mounted.
Database opened.
SQL> -- now let's measure read I/O
SQL> -- create a new session so that session statistics are reset
SQL> connect hemant/hemant
Connected.
SQL>
SQL> -- read twelve rows and one lob column
SQL> select id_column, ool_lob_1
2 from table_with_lobs
3 where id_column between 10 and 21
4 order by id_column
5 /
ID_COLUMN OOL_LOB_1
---------- ------------
10 JHGJctoADd
11 RPZbdLYypm
12 OpOrrJiCzb
13 UPxpHLZhFa
14 OAqNgbIAWw
15 IhXYLHVZQF
16 LYlNaOVKDW
17 rQqbCWQdxM
18 YiicEavWeF
19 dAETtPpjyo
20 OczgVXLJqX
21 hdoJCSFCnQ
12 rows selected.
SQL> -- get the statistics
SQL> select n.name, s.value
2 from v$statname n, v$mystat s
3 where n.statistic#=s.statistic#
4 and n.name in ('lob reads','lob writes', 'lob writes unaligned')
5 /
NAME VALUE
---------------------------------------------------------------- ----------
lob reads 12
lob writes 0
lob writes unaligned 0
SQL> select object_name, statistic_name, value
2 from v$segment_statistics
3 where owner = 'HEMANT'
4 and tablespace_name = 'UNIFORM_64KB'
5 and (
6 object_name like 'SYS_LOB00000864%'
7 or
8 object_name like 'SYS_IL00000864%'
9 )
10 and value != 0
11 order by 1,2
12 /
OBJECT_NAME STATISTIC_NAME VALUE
------------------------------ ------------------------------ ----------
SYS_IL0000086489C00002$$ logical reads 48
SYS_IL0000086489C00002$$ physical read requests 2
SYS_IL0000086489C00002$$ physical reads 5
SYS_LOB0000086489C00002$$ physical read requests 12
SYS_LOB0000086489C00002$$ physical reads 12
SYS_LOB0000086489C00002$$ physical reads direct 12
6 rows selected.
SQL>
SQL> -- trace the same query
SQL> exec dbms_session.session_trace_enable(waits=>TRUE);
PL/SQL procedure successfully completed.
SQL> select id_column, ool_lob_1
2 from table_with_lobs
3 where id_column between 10 and 21
4 order by id_column
5 /
ID_COLUMN OOL_LOB_1
---------- ------------
10 JHGJctoADd
11 RPZbdLYypm
12 OpOrrJiCzb
13 UPxpHLZhFa
14 OAqNgbIAWw
15 IhXYLHVZQF
16 LYlNaOVKDW
17 rQqbCWQdxM
18 YiicEavWeF
19 dAETtPpjyo
20 OczgVXLJqX
21 hdoJCSFCnQ
12 rows selected.
SQL> exec dbms_session.session_trace_disable;
PL/SQL procedure successfully completed.
SQL>
Since the table has 3 LOBs, each LOB has a separate 'lob write' when INSERTing data. Because I insert less than 8KB, each write is 'unaligned'.
The OutOfLine LOB columns still take up 87Bytes in the Table Row.
The InLine LOB, even though I inserted 1500 characters takes up 3103 per entry.
The total storage in each row in the Table segment is 3,310 bytes -- which, fortunately, fits into the 8KB block for a table (so I do not have Row Chaining).
As each OutOfLineLOB is an 8KB write, even a 10character write to OOL_LOB_1 takes an 8KB block. That is why the disk space for the two OutOfLine LOBs far exceeds that for the table rows itself.
When retrieving a LOB columnm, for each row retrieved, a separate read I/O is made.
If I were to retrieve only the non-LOB columns from the table, even if I do a FullTableScan, the OutOfLine LOBs are not read ! That is the advantage of them being OutOfLine. They are not read if they are not required. (Contrast that with normal columns in a table -- if reading a row, the normal columns are in the same row piece in the table block, so they are read from disk but, if not required by the query are ignored after the I/O is executed).
The statistics (from V$MYSTAT, V$SESSION_STAT, V$SYSSTAT and V$SEGMENT_STATISTICS) do show the Physical Reads and the LOB reads ('lob reads' are included in 'physical reads' so you don't need to add the two statistics).
However, if you did an SQL trace, the LOB reads will not be apparent.
Here's a tkprof of the SQL trace :
select id_column, ool_lob_1
from table_with_lobs
where id_column between 10 and 21
order by id_column
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 13 0.00 0.00 0 26 0 12
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.00 0.00 0 26 0 12
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 184
Rows Row Source Operation
------- ---------------------------------------------------
12 TABLE ACCESS BY INDEX ROWID TABLE_WITH_LOBS (cr=26 pr=0 pw=0 time=0 us cost=9 size=1183 card=13)
12 INDEX RANGE SCAN SYS_C0016880 (cr=14 pr=0 pw=0 time=209 us cost=2 size=0 card=13)(object id 86496)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 14 0.00 0.00
SQL*Net message from client 14 0.03 0.04
********************************************************************************
tkprof only shows that the Table was read --- it does not indicate that the LOBSegment for column OOL_LOB_1, which is a physically separate segment, was actually read. (It also doesn't show the LOBIndex being read).
So, the next time you are dealing with queries that retrieve OutOfLine LOBs (in the WHERE or SELECT clause), remember that additional LOB reads will be occurring as Physical Reads and this will not be apparent in a tkprof of a SQL Trace.
.
.
.
3 comments:
AWR claims my hottest CLOB is using physical writes while all other CLOBS do direct physical writes and I'm trying to determine why all are not using direct writes?
usprd204:PREMIERADBA.PP3PRE>select object_name,statistic_name,value from v$segment_statistics where object_name in
2 ('SYS_LOB0000313289C00005$$','SYS_LOB0000086636C00004$$','SYS_LOB0000082592C00005$$')
3 and statistic# in (0,4,6,5,7) order by object_name,statistic_name;
OBJECT_NAME STATISTIC_NAME VALUE
------------------------------ ---------------------------------------------------------------- ----------
SYS_LOB0000082592C00005$$ logical reads 12518352
SYS_LOB0000082592C00005$$ physical reads 973029
SYS_LOB0000082592C00005$$ physical reads direct 0
SYS_LOB0000082592C00005$$ physical writes 8706107
SYS_LOB0000082592C00005$$ physical writes direct 0
SYS_LOB0000086636C00004$$ logical reads 316384
SYS_LOB0000086636C00004$$ physical reads 22290
SYS_LOB0000086636C00004$$ physical reads direct 26471
SYS_LOB0000086636C00004$$ physical writes 23483
SYS_LOB0000086636C00004$$ physical writes direct 26762
SYS_LOB0000313289C00005$$ logical reads 5856
SYS_LOB0000313289C00005$$ physical reads 11445
SYS_LOB0000313289C00005$$ physical reads direct 330138
SYS_LOB0000313289C00005$$ physical writes 337644
SYS_LOB0000313289C00005$$ physical writes direct 1154832
fredb,
Check your LOBs and see if they are set to CACHE or NOCACHE.
select SEGMENT_NAME, CACHE from USER_LOBS.
Post a Comment