[20190215]sqlplus set arraysize.txt

来源：这里教程网时间：2026-03-03 13:00:40 作者：

[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