[20111229]理解consistent gets*相關資訊[補充]
1.補充一點資料:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> create table t1 as select rownum id ,'test' name from dual connect by level<=100;
SQL> alter table t1 add constraint pk_t1 PRIMARY KEY(id) USING INDEX(CREATE INDEX i_t1_id ON t1(id,name)) ENABLE ;
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'T1', estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 1');
SQL> select index_name from user_indexes where table_name='T1';
INDEX_NAME
------------------------------
I_T1_ID
2.
SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 51
consistent gets from cache 51
consistent gets - examination 5
consistent gets direct 0
SQL> select id,name from t1 where id=60 ;
ID NAME
---------- ----
60 test
SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 53(+2)
consistent gets from cache 53(+2)
consistent gets - examination 5(+0)
consistent gets direct 0
--失望!oracle 看來還是不夠聰明,執行計劃依舊是索引範圍掃描。看來要想實現一個邏輯讀,只能使用IOT表。
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9r3x00whhy46y, child number 0
-------------------------------------
select id,name from t1 where id=60
Plan hash value: 4406210
---------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------
|* 1 | INDEX RANGE SCAN| I_T1_ID | 1 |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=60)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
23 rows selected.
3.在11Gr2下重複測試:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 37
consistent gets from cache 37
consistent gets from cache (fastpath) 33
consistent gets - examinatio 4
consistent gets direct 0
SQL> select id,name from t1 where id=60 ;
ID NAME
---------- ----
60 test
SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 39(+2)
consistent gets from cache 39(+2)
consistent gets from cache (fastpath) 34(+1)
consistent gets - examination 4(+0)
consistent gets direct 0
--與10g一樣,不過11G增加一個統計資訊consistent gets from cache (fastpath).並且有1個讀,以後研究看看。
補充: 如果執行這樣呢?select id,name from t1 where id=60 and rownum<=1;
SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 37
consistent gets from cache 37
consistent gets from cache (fastpath) 33
consistent gets - examination 4
consistent gets direct 0
15:09:28 SQL> select id,name from t1 where id=60 and rownum<=1;
ID NAME
---------- ----
60 test
15:09:54 SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 38
consistent gets from cache 38
consistent gets from cache (fastpath) 33
consistent gets - examination 4
consistent gets direct 0
邏輯讀僅僅為1,不過一般情況下不會這樣寫。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-714117/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- db block gets 與 consistent read getsBloC
- python之執行緒相關操作(補充)Python執行緒
- C語言關於指標,gets()和gets_s()函式的理解C語言指標函式
- 原碼反碼補碼的相關理解
- Elasticsearch 學習總結 - 相關配置補充說明Elasticsearch
- [20190329]探究sql語句相關mutexes補充2.txtSQLMutex
- GlusterFS分散式儲存叢集部署記錄-相關補充分散式
- 有關元件的補充~~~~~~~元件
- [20230303]生成相關備庫的awr報表(補充說明).txt
- 『現學現忘』Docker相關概念 — 3、IaaS、SaaS、PaaS服務模式補充Docker模式
- macw資訊 macOS Catalina 10.15.6補充更新已釋出Mac
- 課時7.其它知識補充(理解)
- 新能源汽車智慧充電樁管理方案:充電停車APP/小程式的相關資訊APP
- JS 相關寬高理解JS
- 獲取app版本號相關資訊APP
- 使用Python獲取ECS相關資訊Python
- 資訊熵相關知識總結熵
- Linux檢視相關係統資訊Linux
- 【SCRIPT】Oracle統計資訊相關SQLOracleSQL
- 關於轉儲Oracle索引資訊的相關命令Oracle索引
- Mysql 顯示錶的相關資訊 --命令MySql
- GreatSQL統計資訊相關知識點SQL
- Oracle 統計資訊相關命令彙總Oracle
- Git——關於Git的一些補充(1)Git
- 關於Quick.logger的一點點補充UI
- Linux根據程式號,檢視相關資訊Linux
- 互資訊與相關性的影像配準
- [20191206]確定sys.file$相關資訊.txt
- 音訊相關知識音訊
- 系統日誌及資料庫相關資訊收集資料庫
- JVM補充篇JVM
- linux命令補充Linux
- 聯通性補充
- 理解socket.io(一)---相關的APIAPI
- JVM相關 - 深入理解 System.gc()JVMGC
- 前端效能優化gzip初探(補充gzip壓縮使用演算法brotli壓縮的相關介紹)前端優化演算法
- MySQL索引統計資訊更新相關的引數MySql索引
- 檢視錶和索引碎片情況相關資訊索引
- PHP獲取上級(來路)URL相關資訊PHP