[20210119]sqlplus 12c LOBPREFETCH.txt

--//被别人问的问题,sqlplus 12c开始增加参数LOBPREFETCH,我以前也测试过,反正越测试越乱.

https://blogs.oracle.com/opal/sqlplus-12201-adds-new-performance-features

SET LOBPREFETCH

This option is to improve access of smaller LOBs where LOB data is prefetched and cached. The benefit of this setting is
to reduce the number of network round trips to the server, allowing LOB data to be fetched in one round trip when LOB
data is within the LOBPREFETCH size defined.

Example:

SQL> set lobprefetch 2000
SQL> select * from lob_tab;

--//我的理解应该减少 network round trips.

1.环境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE TABLE t1 (id INT, a VARCHAR2(100), b CLOB, c CLOB);
INSERT INTO t1 SELECT rownum, dummy, dummy, dummy FROM dual CONNECT BY LEVEL <= 1000;
commit ;
--//分析略.

2.测试:
--//注意我的数据库是11g,我可以使用sqlplus的客户端是12c的版本.
SCOTT@78> show sqlpluscompatibility
sqlpluscompatibility 12.2.0

SCOTT@78> show lobprefetch
lobprefetch 0

SCOTT@78> show array
arraysize 200

SCOTT@78> set timing on
SCOTT@78> set autot traceonly
SCOTT@78> set feedback only

SCOTT@78> select id,a from t1;
1000 rows selected.
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
       9640  bytes sent via SQL*Net to client
        516  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed
--//如果出现recursive calls,再次执行取没有递归的结果.你可以发现现在SQL*Net roundtrips to/from client=6
--//前面设置arraysize=200,这样fetch 1,200,200,200,200,200,199. 共6次.

SCOTT@78> select id,a,b from t1;
1000 rows selected.
Elapsed: 00:00:05.71
Execution Plan
----------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1003  consistent gets
          0  physical reads
          0  redo size
     381307  bytes sent via SQL*Net to client
     242472  bytes received via SQL*Net from client
       2002  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed
--//SQL*Net roundtrips to/from client=2002.
--//而一旦显示有lob字段,每行一次fetch,这样变成了SQL*Net roundtrips to/from client=2002.逻辑读1003.

SCOTT@78> select id,a,b,c from t1;
1000 rows selected.
Elapsed: 00:00:11.07
Execution Plan
----------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1003  consistent gets
          0  physical reads
          0  redo size
     667358  bytes sent via SQL*Net to client
     473472  bytes received via SQL*Net from client
       3002  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed
--//增加一个lob字段显示,SQL*Net roundtrips to/from client=3002.增加1000.逻辑读1003.

3.如果修改lobprefetch呢?
SCOTT@78> set lobprefetch 32767

SCOTT@78> select id,a,b from t1;
1000 rows selected.
Elapsed: 00:00:05.96
Execution Plan
----------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2003  consistent gets
          0  physical reads
          0  redo size
     360342  bytes sent via SQL*Net to client
     224775  bytes received via SQL*Net from client
       2002  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

--//如果你对比前面consistent gets=2003反而增加了1000,而是执行时间并没有怎么改变对比前面.

SCOTT@78> select id,a,b,c from t1;
1000 rows selected.
Elapsed: 00:00:11.13
Execution Plan
----------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3003  consistent gets
          0  physical reads
          0  redo size
     625393  bytes sent via SQL*Net to client
     437804  bytes received via SQL*Net from client
       3002  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

--//如果你对比前面consistent gets=3003,又增加了1000,SQL*Net roundtrips to/from client=3002.并没有怎么变化.
--//执行时间并没有怎么改变对比前面.

4.我的测试看不出设置LOBPREFETCH的效果,而且逻辑读反而增加.执行时间上也没有什么变化.

5.我在想前面的测试lob是在块内,测试块外看看.

SCOTT@78> drop table t1 purge ;
Table dropped.

CREATE TABLE t1 (id INT, a VARCHAR2(100), b CLOB, c CLOB);
INSERT INTO t1 SELECT rownum, dummy, lpad('a',4000,'a') ,lpad('b',4000,'b') FROM dual CONNECT BY LEVEL <= 1000;
commit ;
--//分析略.

SCOTT@78> set lobprefetch 0
SCOTT@78> select id,a,b from t1;
1000 rows selected.
Elapsed: 00:00:08.44
Execution Plan
----------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2002  consistent gets
       1000  physical reads
          0  redo size
    8417307  bytes sent via SQL*Net to client
     244472  bytes received via SQL*Net from client
       2002  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SCOTT@78> select id,a,b,c from t1;
1000 rows selected.
Elapsed: 00:00:15.57
Execution Plan
----------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3002  consistent gets
       2000  physical reads
          0  redo size
   16739358  bytes sent via SQL*Net to client
     477472  bytes received via SQL*Net from client
       3002  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

--//因为lob在数据段外,逻辑读增加.

SCOTT@78> set lobprefetch 32767
SCOTT@78> select id,a,b from t1;
1000 rows selected.
Elapsed: 00:00:12.60
Execution Plan
----------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4002  consistent gets
       2000  physical reads
          0  redo size
   16422342  bytes sent via SQL*Net to client
     224775  bytes received via SQL*Net from client
       2002  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SCOTT@78> select id,a,b,c from t1;
1000 rows selected.
Elapsed: 00:00:21.51
Execution Plan
----------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       7002  consistent gets
       4000  physical reads
          0  redo size
   32749393  bytes sent via SQL*Net to client
     437804  bytes received via SQL*Net from client
       3002  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

--//设置lobprefetch 32767反而性能更差,我自己都不知道问题在哪里.而且物理读反而增加啊.