Database Reference
In-Depth Information
6 lob (in_row) store as ( enable storage in row )
7 lob (out_row) store as ( disable storage in row )
8 /
Table created.
Into this table we'll insert some string data, all of which is less than 4,000 bytes in length:
EODA@ORA12CR1> insert into t
2 select rownum,
3 owner || ' ' || object_name || ' ' || object_type || ' ' || status,
4 owner || ' ' || object_name || ' ' || object_type || ' ' || status
5 from all_objects
6 /
72085 rows created.
EODA@ORA12CR1> commit;
Commit complete.
Now, if we try to read out each row and, using the
DBMS_MONITOR
package, do this with
SQL_TRACE
enabled, we'll
be able to see the performance upon data retrieval of each:
EODA@ORA12CR1> declare
2 l_cnt number;
3 l_data varchar2(32765);
4 begin
5 select count(*)
6 into l_cnt
7 from t;
8
9 dbms_monitor.session_trace_enable;
10 for i in 1 .. l_cnt
11 loop
12 select in_row into l_data from t where id = i;
13 select out_row into l_data from t where id = i;
14 end loop;
15 end;
16 /
PL/SQL procedure successfully completed.
When we review the
TKPROF
report for this small simulation, the results are rather obvious:
SELECT IN_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.25 0 0 0 0
Fetch 18240 0.22 0.27 0 54720 0 18240
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 36481 0.46 0.53 0 54720 0 18240
********************************************************************************
