[20190215]sqlplus set arraysize.txt

--//看連結,我感覺有點奇怪的地方,我一直認為全表掃描,如果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

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

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

| 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

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

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

| 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

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

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

| 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

[20190215]sqlplus set arraysize.txt

相關文章