Database Reference
In-Depth Information
SELECT OUT_ROW FROM T WHERE ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 18240 0.23 0.24 0 0 0 0
Fetch 18240 1.95 1.67 18240 72960 0 18240
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 36481 2.18 1.91 18240 72960 0 18240
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path read 18240 0.00 0.14
The retrieval of the
IN_ROW
column was significantly faster and consumed far fewer resources. We can see that it
used 54,720 logical I/Os (query mode gets), whereas the
OUT_ROW
column used significantly more logical I/Os. At first
it is not clear where these extra logical I/Os are coming from, but if you remember how LOBs are stored, it will become
obvious. These are the I/Os against the LOBINDEX segment in order to find the pieces of the LOB. Those extra logical
I/Os are all against this LOBINDEX.
Additionally, you can see that the retrieval of 18,240 rows with out of row storage incurred 18,240 physical I/Os
and resulted in 18,240 I/O waits for
direct path read
. These were the reads of the noncached LOB data. We might be
able to reduce them in this case by enabling caching on the LOB data, but then we'd have to ensure we had sufficient
additional buffer cache to be used for this. Also, if there were some really large LOBs in there, we might not really want
this data to be cached.
This in row/out of row storage will affect modifications as well as reads. If we were to update the first 100 rows
with short strings, and insert 100 new rows with short strings and use the same techniques to monitor performance as
follows:
EODA@ORA12CR1> create sequence s start with 100000;
Sequence created.
EODA@ORA12CR1> declare
2 l_cnt number;
3 l_data varchar2(32765);
4 begin
5 dbms_monitor.session_trace_enable;
6 for i in 1 .. 100
7 loop
8 update t set in_row =
to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') where id = i;
9 update t set out_row =
to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') where id = i;
10 insert into t (id, in_row) values ( s.nextval, 'Hello World' );
11 insert into t (id,out_row) values ( s.nextval, 'Hello World' );
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
