Arraysize 對consistent get的影響
logical read 或 consistent get 是指讀取block 的塊*次,對block每次訪問被計數一次
建立測試表,生成統計資訊
[oracle@oracle10g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 3 02:53:38 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn test/test
Connected.
SQL> create table t
2 nologging
3 as
4 select *
5 from all_objects;
Table created.
SQL> create index t_ind on t(owner,object_type,object_name) nologging;
Index created.
SQL> begin
2 dbms_stats.gather_table_stats(
3 ownname =>test,
4 tabname =>'T',
5 estimate_percent=>dbms_stats.auto_sample_size,
6 cascade=>true);
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select count(*) from t;
COUNT(*)
----------
70611
SQL>
使用show_space儲存過程查詢block情況
SQL> set serveroutput on
SQL> exec show_space('T_IND','TEST','INDEX');
Total Blocks............................512
Total Bytes.............................4194304
Unused Blocks...........................1
Unused Bytes............................8192
Last Used Ext FileId....................4
Last Used Ext BlockId...................119433
Last Used Block.........................127
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............1
25% -- 50% free space bytes.............8192
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................494
Total bytes.............................4046848
PL/SQL procedure successfully completed.
每個block 大概70611/512=138行記錄
arraysize to 15
SQL> set autotrace traceonly explain statistics;
SQL> show arraysize;
arraysize 15
SQL> select owner,object_type,object_name from t order by owner,object_type,object_name;
70611 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1106009184
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 70611 | 2758K| 497 (1)| 00:00:06 |
| 1 | INDEX FULL SCAN | T_IND | 70611 | 2758K| 497 (1)| 00:00:06 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5167 consistent gets
0 physical reads
0 redo size
2666019 bytes sent via SQL*Net to client
52196 bytes received via SQL*Net from client
4709 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
70611 rows processed
Arraysize 設定為2 consistent gets為35548
SQL> set arraysize 2;
SQL> select owner,object_type,object_name from t order by owner,object_type,object_name;
70611 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1106009184
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 70611 | 2758K| 497 (1)| 00:00:06 |
| 1 | INDEX FULL SCAN | T_IND | 70611 | 2758K| 497 (1)| 00:00:06 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
35548 consistent gets
0 physical reads
0 redo size
6551934 bytes sent via SQL*Net to client
388774 bytes received via SQL*Net from client
35307 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
70611 rows processed
Arraysize 設定為2 consistent gets 509
SQL> set arraysize 5000;
SQL> select owner,object_type,object_name from t order by owner,object_type,object_name;
70611 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1106009184
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 70611 | 2758K| 497 (1)| 00:00:06 |
| 1 | INDEX FULL SCAN | T_IND | 70611 | 2758K| 497 (1)| 00:00:06 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
509 consistent gets
0 physical reads
0 redo size
2070008 bytes sent via SQL*Net to client
573 bytes received via SQL*Net from client
16 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
70611 rows processed
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/15747463/viewspace-1159245/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 測試SQLPLUS的ARRAYSIZE對效能的影響SQL
- Arraysize的設定以及對查詢效能的影響
- arraysize/TDU/SDU的設定對sql執行代價的影響SQL
- db block get和consistent read getBloC
- 【Oracle】-【COMMIT對索引的影響】-從trace看COMMIT對索引的影響OracleMIT索引
- shrink 操作對索引的影響索引
- Update操作對索引的影響索引
- unusable index對DML/QUERY的影響Index
- mysql event對主從的影響MySql
- 新增欄位對SQL的影響SQL
- 語言對思維的影響
- “影響者研究”系列(一):社會網路化時代影響者對營銷創新的影響
- 絕對定位對margin外邊距的影響
- shrink 與rebuild對索引高度的影響對比Rebuild索引
- 遊戲暗示對於遊戲玩家的影響遊戲
- INDEX建立方式對SQL的影響IndexSQL
- 關於OPcache對Swoole影響的理解opcache
- 物聯網對企業的影響
- Web-Scale IT:對企業的影響Web
- 音樂對程式設計的影響程式設計
- JAVA 異常對於效能的影響Java
- 表型別對AUTO_INCREMENT的影響型別REM
- 對我影響最大的圖靈書圖靈
- 關於drop操作對role的影響
- Stripe Size大小對讀寫的影響
- append HINT 的對事務的影響APP
- Cirium:資料揭示新冠肺炎對中國航空業的影響及對全球航空旅遊增長的影響
- 淺談疫情對消費金融的影響
- cluster factor對執行計劃的影響
- JVM 引數調整對 sortx 的影響JVM
- 任正非談人工智慧對全球的影響人工智慧
- namespace對axis解析xml請求的影響namespaceXML
- margin為負值對佈局的影響
- python:super()對多繼承的影響Python繼承
- DB2 HADR對效能的影響DB2
- 終端環境對go程式的影響?Go
- 哪些特徵對我的機器學習模型影響最大?特徵機器學習模型
- Sailthru:Facebook醜聞對人們的影響AI