[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反而效能更差,我自己都不知道問題在哪裡.而且物理讀反而增加啊.