亚洲三页,亚洲福利秒拍一区二区,免费精品久久

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

1、創建演示表

	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;   -->創建演示表

	

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

	

	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    -->表段上的總塊數768

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

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

	Unused Blocks...........................              58    -->未使用的塊數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的關系

	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



--從上面的演示可以看出由于設置了不同的array size值，導致在全表掃描(表上不存在索引)是產生了不同的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?接近于表上的塊數.

	--考慮表段上的塊數768-58=710

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

	

	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       -->調整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       -->調整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      -->調整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      -->調整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     -->調整arraysize超出范圍

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

	scott@CNMMBO> set arraysize 5000      -->調整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時，表上的一條記錄將產生一次consistent gets  

	         

3、arraysize與索引掃描

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

	

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

	

	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

	          

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

	

	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       -->索引段總塊數120      

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

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

	Unused Blocks...........................               5       -->空閑塊數為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無關

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

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

綁定變量及其優缺點

dbms_xplan之display_cursor函數的使用

dbms_xplan之display函數的使用

執行計劃中各字段各模塊描述

使用 EXPLAIN PLAN 獲取SQL語句執行計劃

更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號聯系： 360901061

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

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

2元

5元

10元

20元

自定義

亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频