[20190215]sqlplus set arraysize.txt

[20190215]sqlplus set arraysize.txt

https://www.josip-pojatina.com/en/impact-of-parameter-arraysize-on-performance/

--//看链接,我感觉有点奇怪的地方,我一直认为全表扫描,如果arraysize很大的情况下,逻辑读不会出现很大变化.

--//而对方设置arraysize=1000,5000,还是存在很大差异,不知道我以前的理解那里存在问题.先重复作者的测试看看.

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

2.测试:

SCOTT@book> set timing on

SCOTT@book> set autot traceonly

SCOTT@book> set arraysize 1000

SCOTT@book> select * from sh.sales;

918843 rows selected.

Elapsed: 00:00:06.03

Execution Plan

----------------------------------------------------------

Plan hash value: 1550251865

---------------------------------------------------------------------------------------------

| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |       |   918K|    25M|   525   (2)| 00:00:07 |       |       |

|   1 |  PARTITION RANGE ALL|       |   918K|    25M|   525   (2)| 00:00:07 |     1 |    28 |

|   2 |   TABLE ACCESS FULL | SALES |   918K|    25M|   525   (2)| 00:00:07 |     1 |    28 |

---------------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       2550  consistent gets

       1619  physical reads

          0  redo size

   25877676  bytes sent via SQL*Net to client

      10617  bytes received via SQL*Net from client

        920  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

     918843  rows processed

SCOTT@book> set arraysize 5000

SCOTT@book> select * from sh.sales;

918843 rows selected.

Elapsed: 00:00:05.31

Execution Plan

----------------------------------------------------------

Plan hash value: 1550251865

---------------------------------------------------------------------------------------------

| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |       |   918K|    25M|   525   (2)| 00:00:07 |       |       |

|   1 |  PARTITION RANGE ALL|       |   918K|    25M|   525   (2)| 00:00:07 |     1 |    28 |

|   2 |   TABLE ACCESS FULL | SALES |   918K|    25M|   525   (2)| 00:00:07 |     1 |    28 |

---------------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       1818  consistent gets

       1619  physical reads

          0  redo size

   25743171  bytes sent via SQL*Net to client

       2532  bytes received via SQL*Net from client

        185  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

     918843  rows processed

--//也画一个表格:

array size   elapsed    consistent gets    SQL*Net trips

      1000      06.03   2550               920

      5000      05.31   1818               185

--//存在少量差异,但是有点出乎意料,consistent gets还是存在一些不同.实际上ayyaysize越大越接近如下语句的逻辑读.

--//执行select /*+ full(a) */ count(*) from sh.sales a;逻辑读.

SCOTT@book> select /*+ full(a) */ count(*) from sh.sales a;

Elapsed: 00:00:00.03

Execution Plan

----------------------------------------------------------

Plan hash value: 3519235612

--------------------------------------------------------------------------------------

| Id  | Operation            | Name  | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       |     1 |   522   (1)| 00:00:07 |       |       |

|   1 |  SORT AGGREGATE      |       |     1 |            |          |       |       |

|   2 |   PARTITION RANGE ALL|       |   918K|   522   (1)| 00:00:07 |     1 |    28 |

|   3 |    TABLE ACCESS FULL | SALES |   918K|   522   (1)| 00:00:07 |     1 |    28 |

--------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       1635  consistent gets

       1619  physical reads

          0  redo size

        528  bytes sent via SQL*Net to client

        519  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

--//实际上可以这样简单估算两者的差别.

918843/1000 = 918.84300000000000000000

918843/5000  = 183.76860000000000000000

918-183 = 735

--//也就是arraysize= 1000,逻辑读比arraysize=5000多735次.验证看看是否正确.

--//2550 - 1818 = 732,非常接近. 

--//我个人的观点设置很大没有任何意义,毕竟oltp系统很少大量读取数据.设置200-400之间就足够,我的测试环境都设置200.

--//实际上对方选择的表sh.sales非常特殊,平均记录行长很短仅仅29字节.而记录相对很多达到918843条.这样平均1块有250条记录.

SCOTT@book> @ tab_stat sh sales

TABLE:SALES

TABLE PARTITION/SH/SALES

CARD:918843  BLKS:1907  AVGLEN:29  SAMPLE:918843  ANALYZED:2013/08/24 12:09:49

----------------------------------------------------------------------------------------------------------------------

COL:PROD_ID                         TYP:NUMBER      VALS:          72  DENS:       0  NULLS:           0  HIST:  72

COL:CUST_ID                         TYP:NUMBER      VALS:       7,059  DENS:   .0001  NULLS:           0  HIST:   1

COL:TIME_ID                         TYP:DATE        VALS:       1,460  DENS:   .0007  NULLS:           0  HIST:   1

COL:CHANNEL_ID                      TYP:NUMBER      VALS:           4  DENS:     .25  NULLS:           0  HIST:   1

COL:PROMO_ID                        TYP:NUMBER      VALS:           4  DENS:     .25  NULLS:           0  HIST:   1

COL:QUANTITY_SOLD                   TYP:NUMBER      VALS:           1  DENS:       1  NULLS:           0  HIST:   1

COL:AMOUNT_SOLD                     TYP:NUMBER      VALS:       3,586  DENS:   .0003  NULLS:           0  HIST:   1

-----------------------------------------------------------------------------------------------------------------------

INAME:SALES_TIME_BIX                  ITYP:BITMAP                LBLKS:       57  KEYS:       1,460  CLUSTR:1460

..ROWS:       1,460 ANALYZED:2013/08/24 12:09:53

....POS:  1 COL:TIME_ID

-----------------------------------------------------------------------------------------------------------------------

INAME:SALES_PROMO_BIX                 ITYP:BITMAP                LBLKS:       30  KEYS:           4  CLUSTR:54

..ROWS:          54 ANALYZED:2013/08/24 12:09:54

....POS:  1 COL:PROMO_ID

-----------------------------------------------------------------------------------------------------------------------

INAME:SALES_CHANNEL_BIX               ITYP:BITMAP                LBLKS:       47  KEYS:           4  CLUSTR:92

..ROWS:          92 ANALYZED:2013/08/24 12:09:54

....POS:  1 COL:CHANNEL_ID

-----------------------------------------------------------------------------------------------------------------------

INAME:SALES_CUST_BIX                  ITYP:BITMAP                LBLKS:      452  KEYS:       7,059  CLUSTR:35808

..ROWS:      35,808 ANALYZED:2013/08/24 12:09:52

....POS:  1 COL:CUST_ID

-----------------------------------------------------------------------------------------------------------------------

INAME:SALES_PROD_BIX                  ITYP:BITMAP                LBLKS:       32  KEYS:          72  CLUSTR:1074

..ROWS:       1,074 ANALYZED:2013/08/24 12:09:50

....POS:  1 COL:PROD_ID

Done.

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.38

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2636120/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2636120/