[20210119]sqlplus 12c LOBPREFETCH.txt

[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反而性能更差,我自己都不知道问题在哪里.而且物理读反而增加啊.