日韩中文首页,希岛爱理一区二区三区av高清,国模雨婷捆绑高清在线

?? 通常情況下數(shù)據(jù)庫引擎每訪問一個數(shù)據(jù)塊將產(chǎn)生至少一個邏輯讀。而行預取與邏輯讀息息相關(guān)。行預取是指當客戶端從數(shù)據(jù)庫獲取數(shù)據(jù)時
可以采用單行也可以采用多行方式返回數(shù)據(jù)。當采用多行方式時，則會預取多條記錄存儲在客戶端內(nèi)存中以避免后續(xù)多次該數(shù)據(jù)的請求所致的
各種開銷(LIO，PIO，NET IO)。一般預取行數(shù)越大，則所產(chǎn)生的開銷越小，當達到臨界值時其變化不大。
一、演示

1、創(chuàng)建演示表

	scott@CNMMBO> select * from v$version where rownum<2;

	

	BANNER

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

	Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

	scott@CNMMBO> create table t as select * from dba_objects;   -->創(chuàng)建演示表

	

	scott@CNMMBO> analyze table t compute statistics;            -->更新統(tǒng)計信息

	

	scott@CNMMBO> exec show_space('T');

	Unformatted Blocks .....................               0

	FS1 Blocks (0-25) ......................               0

	FS2 Blocks (25-50) .....................               0

	FS3 Blocks (50-75) .....................               0

	FS4 Blocks (75-100).....................               0

	Full Blocks ............................             690

	Total Blocks............................             768    -->表段上的總塊數(shù)768

	Total Bytes.............................       6，291，456

	Total MBytes............................               6

	Unused Blocks...........................              58    -->未使用的塊數(shù)58

	Unused Bytes............................         475，136

	Last Used Ext FileId....................              11

	Last Used Ext BlockId...................          39，561

	Last Used Block.........................              70

	

	PL/SQL procedure successfully completed.



2、arraysize與consistent gets的關(guān)系

	scott@CNMMBO> show arraysize                 -->arraysize的大小此時為默認值15

	arraysize 15

	scott@CNMMBO> set arraysize 2                -->修改arraysize的值為2

	scott@CNMMBO> set autotrace traceonly stat;  -->啟用autotrace   

	scott@CNMMBO> select * from t;

	

	Statistics

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

	          1  recursive calls

	          0  db block gets

	      25390  consistent gets                -->此時的consistent gets為25390

	          0  physical reads

	          0  redo size

	    6596152  bytes sent via SQL*Net to client

	     275844  bytes received via SQL*Net from client

	      25034  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	      50066  rows processed

	

	scott@CNMMBO> set arraysize 15              -->修改arraysize的值為15

	scott@CNMMBO> select * from t;

	

	Statistics

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

	          0  recursive calls

	          0  db block gets

	       3992  consistent gets                -->此時的consistent gets為3992

	          0  physical reads

	          0  redo size

	    2625967  bytes sent via SQL*Net to client

	      37199  bytes received via SQL*Net from client

	       3339  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	      50066  rows processed



--從上面的演示可以看出由于設(shè)置了不同的array size值，導致在全表掃描(表上不存在索引)是產(chǎn)生了不同的consistent gets

--當arraysize值變大時，consistent gets的值會減少

--consistent gets 為3992從何而來?



	scott@CNMMBO> set autotrace off;

	scott@CNMMBO> select 50066/15+768-58 from dual;  -->number_of_rows/arraysize + total_blocks_read 

	

	50066/15+768-58

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

	     4047.73333

	     

--從上面的計算可以看出4047若高于3992，因此該consistent gets接近于計算得到的值



	scott@CNMMBO> set autotrace traceonly;

	scott@CNMMBO> select count(*) from t;           -->聚合計算

	

	Execution Plan

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

	Plan hash value: 2966233522

	

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

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

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

	|   0 | SELECT STATEMENT   |      |     1 |   158   (1)| 00:00:02 |

	|   1 |  SORT AGGREGATE    |      |     1 |            |          |

	|   2 |   TABLE ACCESS FULL| T    | 50066 |   158   (1)| 00:00:02 |

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

	

	Statistics

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

	          0  recursive calls

	          0  db block gets

	        695  consistent gets                -->此時的consistent gets為695

	          0  physical reads

	          0  redo size

	        517  bytes sent via SQL*Net to client

	        492  bytes received via SQL*Net from client

	          2  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	          1  rows processed

	

	--此時走的是全表掃描，為什么一致讀僅僅只有695?接近于表上的塊數(shù).

	--考慮表段上的塊數(shù)768-58=710

	--注意此時的聚合僅僅是一個SINGLE CALL,SQL引擎內(nèi)部使用行預取,每個塊上將產(chǎn)生一次邏輯讀，抽取其中的所有行	

	

	scott@CNMMBO> set autotrace off;

	scott@CNMMBO> SELECT num_rows，

	  2    blocks blks，

	  3    empty_blocks em_blks，

	  4    avg_space，

	  5    chain_cnt，

	  6    avg_row_len，

	  7    round(num_rows / blocks) AS avg_rows_per_block，

	  8    last_analyzed lst_anly，

	  9    stale_stats

	 10  FROM   dba_tab_statistics

	 11  WHERE  table_name = 'T'

	 12  AND owner = 'SCOTT';

	

	  NUM_ROWS       BLKS    EM_BLKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_ROWS_PER_BLOCK LST_ANLY  STA

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

	     50066        710         58        860          0          97                 71 12-JAN-12 NO

	

	scott@CNMMBO> select 71*710 from dual;

	

	    71*710

	----------            -->值50410與50066相接近

	     50410



3、arraysize與全表掃描	

	scott@CNMMBO> set autotrace traceonly stat;

	scott@CNMMBO> set arraysize 100       -->調(diào)整arraysize值為100

	scott@CNMMBO> select * from t;

	

	Statistics

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

	          0  recursive calls

	          0  db block gets

	       1185  consistent gets

	          0  physical reads

	          0  redo size

	    2106796  bytes sent via SQL*Net to client

	       5992  bytes received via SQL*Net from client

	        502  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	      50066  rows processed

	

	scott@CNMMBO> set arraysize 200       -->調(diào)整arraysize值為200  

	scott@CNMMBO> select * from t;

	

	Statistics

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

	          0  recursive calls

	          0  db block gets

	        940  consistent gets

	          0  physical reads

	          0  redo size

	    2061046  bytes sent via SQL*Net to client

	       3242  bytes received via SQL*Net from client

	        252  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	      50066  rows processed

	

	scott@CNMMBO> set arraysize 2000      -->調(diào)整arraysize值為2000,此時訪問方式為全表掃描

	scott@CNMMBO> select * from t;

	

	Statistics

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

	          0  recursive calls

	          0  db block gets

	        721  consistent gets

	          0  physical reads

	          0  redo size

	    2019871  bytes sent via SQL*Net to client

	        767  bytes received via SQL*Net from client

	         27  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	      50066  rows processed

	

	scott@CNMMBO> set arraysize 4000      -->調(diào)整arraysize值為4000,此時訪問方式為全表掃描

	scott@CNMMBO> select * from t;

	

	Statistics

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

	          0  recursive calls

	          0  db block gets

	        708  consistent gets

	          0  physical reads

	          0  redo size

	    2017492  bytes sent via SQL*Net to client

	        624  bytes received via SQL*Net from client

	         14  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	      50066  rows processed 

	

	scott@CNMMBO> set arraysize 20000     -->調(diào)整arraysize超出范圍

	SP2-0267: arraysize option 20000 out of range (1 through 5000)

	scott@CNMMBO> set arraysize 5000      -->調(diào)整arraysize到最大值5000,此時訪問方式為全表掃描

	scott@CNMMBO> select * from t;

	

	Statistics

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

	          0  recursive calls

	          0  db block gets

	        706  consistent gets

	          0  physical reads

	          0  redo size

	    2017126  bytes sent via SQL*Net to client

	        602  bytes received via SQL*Net from client

	         12  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	      50066  rows processed 

                

--從上面觀察可以看出當arraysize不但增大的情形下，其邏輯讀相應會減少，當arraysize達到一定值之后邏輯讀減少的幅度變化不大

      

	scott@CNMMBO> set autotrace traceonly; 

	scott@CNMMBO> show arraysize 

	arraysize 1

	scott@CNMMBO> select * from emp;

	

	Execution Plan

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

	Plan hash value: 3956160932

	

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

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

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

	|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |

	|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |

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

	

	Statistics

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

	          0  recursive calls

	          0  db block gets

	         14  consistent gets

	          0  physical reads

	          0  redo size

	       2633  bytes sent via SQL*Net to client

	        558  bytes received via SQL*Net from client

	          8  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	         14  rows processed	    

--從上面的測試可以看出在全表掃描時，當arraysize為1時，表上的一條記錄將產(chǎn)生一次consistent gets  

	         

3、arraysize與索引掃描

	scott@CNMMBO> alter table t add constraint t_pk primary key(object_id);  -->為表添加索引

	

	scott@CNMMBO> analyze table t compute statistics;                        -->更新統(tǒng)計信息

	

	scott@CNMMBO> set autotrace traceonly; 

	scott@CNMMBO> select count(*) from t;

	

	Execution Plan

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

	Plan hash value: 454320086

	

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

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

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

	|   0 | SELECT STATEMENT      |      |     1 |    24   (0)| 00:00:01 |

	|   1 |  SORT AGGREGATE       |      |     1 |            |          |

	|   2 |   INDEX FAST FULL SCAN| T_PK | 50066 |    24   (0)| 00:00:01 |

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

	

	Statistics

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

	          1  recursive calls

	          0  db block gets

	        111  consistent gets

	          0  physical reads

	          0  redo size

	        517  bytes sent via SQL*Net to client

	        492  bytes received via SQL*Net from client

	          2  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	          1  rows processed

	          

	--從上面的執(zhí)行計劃和buffers可以看出此時走了索引全掃描,且consistent gets的大小為111.即優(yōu)化器基于索引統(tǒng)計得到總行數(shù),而不是基于表.

	

	scott@CNMMBO> exec show_space('T_PK'，'SCOTT'，'INDEX');         -->查看索引段的使用情況

	Unformatted Blocks .....................               0

	FS1 Blocks (0-25) ......................               0

	FS2 Blocks (25-50) .....................               1

	FS3 Blocks (50-75) .....................               0

	FS4 Blocks (75-100).....................               0

	Full Blocks ............................             104

	Total Blocks............................             120       -->索引段總塊數(shù)120      

	Total Bytes.............................         983，040

	Total MBytes............................               0

	Unused Blocks...........................               5       -->空閑塊數(shù)為5

	Unused Bytes............................          40，960

	Last Used Ext FileId....................              11

	Last Used Ext BlockId...................          39，745

	Last Used Block.........................               3

	

	PL/SQL procedure successfully completed.



	scott@CNMMBO> show arraysize 

	arraysize 5000	

	scott@CNMMBO> select count(*) from t;

	

	Statistics

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

	          0  recursive calls

	          0  db block gets

	        111  consistent gets

	          0  physical reads

	          0  redo size

	        517  bytes sent via SQL*Net to client

	        492  bytes received via SQL*Net from client

	          2  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	          1  rows processed

	

	scott@CNMMBO> set arraysize 2000

	scott@CNMMBO> select count(*) from t;

	

	Statistics

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

	          0  recursive calls

	          0  db block gets

	        111  consistent gets

	          0  physical reads

	          0  redo size

	        517  bytes sent via SQL*Net to client

	        492  bytes received via SQL*Net from client

	          2  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	          1  rows processed

	

	scott@CNMMBO> set arraysize 15

	scott@CNMMBO> select count(*) from t;

	

	Statistics

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

	          0  recursive calls

	          0  db block gets

	        111  consistent gets

	          0  physical reads

	          0  redo size

	        517  bytes sent via SQL*Net to client

	        492  bytes received via SQL*Net from client

	          2  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	          1  rows processed

	

	scott@CNMMBO> set arraysize 1

	scott@CNMMBO> select count(*) from t;

	

	Statistics

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

	          0  recursive calls

	          0  db block gets

	        111  consistent gets

	          0  physical reads

	          0  redo size

	        517  bytes sent via SQL*Net to client

	        492  bytes received via SQL*Net from client

	          2  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	          1  rows processed

	          

--從上面的測試可知，走索引掃描之后的聚合與arraysize無關(guān)

二、分析
? 假定使用select * from t發(fā)布查詢，此時表上每個數(shù)據(jù)塊為16行，且arraysize 的設(shè)置為15(缺省值)，
? 則
??? 第一次fetch，讀第一塊15行，此時產(chǎn)生第1次consistent gets
??? 第二次fetch，讀第一塊1行，此時產(chǎn)生第2次consistent gets，讀第二塊14行，此時產(chǎn)生第3次consistent gets
??? 第三次fetch，讀第二塊2行，此時產(chǎn)生第4次consistent gets，讀第三塊13行，此時產(chǎn)生第5次consistent gets
???? 依此內(nèi)推
? 假定此時表上每個數(shù)據(jù)塊為10行，即數(shù)據(jù)塊上的行數(shù)小于arraysize的情形(10<15)
???? 第一次fetch，讀第一塊10行，此時產(chǎn)生第1次consistent gets
?????????? 讀第二塊5行，此時產(chǎn)生第2次consistent gets
???? 第二次fetch，讀第二塊5行，此時產(chǎn)生第3次consistent gets
?????????? 讀第三塊10行，此時產(chǎn)生第4次consistent gets
???? 第三次fetch，讀第四塊10行，此時產(chǎn)生第5次consistent gets
?????????? 讀第五塊5行，此時產(chǎn)生第6次consistent gets
??? 依此內(nèi)推
???
? 當arraysize設(shè)置為n(15)時，則Oracle從buffer一次fetch n(15)行，然后將數(shù)據(jù)返回給客戶端.接下來Oracle會再次從buffer中fetch第二次
? ，返回下一個n(15)行?
? 由上可知，在fetch期間可能會產(chǎn)生多次consistent gets。
?
? 當設(shè)置了較大的arraysize，從演示中可以看出SQL*Net的sent與received也隨之減少。即請求時往返的次數(shù)明顯降低。??
三、總結(jié)
? 1、arraysize參數(shù)用于控制返回給客戶端預取的行數(shù)，缺省值為15
? 2、邏輯讀為consistent gets + db block gets，為簡化描述，下面直接使用邏輯讀
? 3、當行預取值設(shè)定為1時，對于全表掃描，每返回一個行需要產(chǎn)生一個邏輯讀，如果設(shè)定的行預取值大于單塊存儲的行數(shù)，則邏輯讀接近于塊數(shù)
? 4、邏輯讀的值隨arraysize的增大而減小，當達到臨界值(即略大于表上單塊存儲的行束)之后其變化趨勢不大
? 5、SQL*Net的sent與received的值會隨著arraysize的增大而減小
? 6、邏輯讀通常會多于一次，見第二點的分析
? 7、對于聚合運算的情形(無索引)，SQL引擎內(nèi)部使用行預取。即每次SQL引擎訪問一個塊的時候，抽取其中的所有行。其邏輯讀接近于塊數(shù)
? 8、在使用索引的情形下，consistent gets與arraysize無關(guān)
四、更多參考?

父游標、子游標及共享游標

綁定變量及其優(yōu)缺點

dbms_xplan之display_cursor函數(shù)的使用

dbms_xplan之display函數(shù)的使用

執(zhí)行計劃中各字段各模塊描述

使用 EXPLAIN PLAN 獲取SQL語句執(zhí)行計劃

更多文章、技術(shù)交流、商務合作、聯(lián)系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號聯(lián)系： 360901061

您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對您有幫助就好】元

2元

5元

10元

20元

自定義

日韩久久久精品,亚洲精品久久久久久久久久久,亚洲欧美一区二区三区国产精品 ,一区二区福利