[20111229]Consistent gets from cache (fastpath).txt
[20111229]Consistent gets from cache (fastpath).txt
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 * from v$statname where NAME like 'consistent gets%';
STATISTIC# NAME CLASS STAT_ID
---------- ---------------------------------------------------------------- ---------- ----------
67 consistent gets 8 4162191256
68 consistent gets from cache 8 2839918855
69 consistent gets from cache (fastpath) 8 2173751438
70 consistent gets - examination 8 1701530557
71 consistent gets direct 8 420374750
如果對比10g可以發現，11G增加一個consistent gets from cache (fastpath)統計，google發現如下連結:
http://afatkulin.blogspot.com/2009/01/consistent-gets-from-cache-fastpath.html
1.重複測試:
create table t1 nologging as select rownum id ,a.* from all_objects a where rownum <=10000;
create table t2 nologging as select * from t1;
create index idx_t1_id on t1(id);
exec dbms_stats.gather_table_stats(user,'T1');
exec dbms_stats.gather_table_stats(user,'T2');
2.在11GR2下執行如下：
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
SQL> select/*+use_nl(t2 t1)*/ count(*) from t1,t2 where t1.id = t2.id;
COUNT(*)
----------
10000
10:26:20 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 324(+287)
consistent gets from cache 324(+287)
consistent gets from cache (fastpath) 298(+265)
consistent gets - examination 5(+1)
consistent gets direct 0
執行計劃如下:
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 518mw211s3mmv, child number 0
-------------------------------------
select/*+use_nl(t2 t1)*/ count(*) from t1,t2 where t1.id = t2.id
Plan hash value: 3211355954
--------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | NESTED LOOPS | | 10000 |
| 3 | TABLE ACCESS FULL| T2 | 10000 |
|* 4 | INDEX RANGE SCAN | IDX_T1_ID | 1 |
--------------------------------------------------
3.在10G下執行:
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> 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/*+use_nl(t2 t1)*/ count(*) from t1,t2 where t1.id = t2.id;
COUNT(*)
----------
10000
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 10208(+10157)
consistent gets from cache 10208(+10157)
consistent gets - examination 6(+1)
consistent gets direct 0
--可以發現邏輯讀在10g下增加許多，在11G下僅僅287個邏輯讀。11g下邏輯讀明顯減少。
4.對比執行計劃：
11G:
SQL> select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id = t2.id;
COUNT(*)
----------
10000
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 877z7w0x4d3rx, child number 0
-------------------------------------
select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id = t2.id
Plan hash value: 3211355954
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 287 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 287 |
| 2 | NESTED LOOPS | | 1 | 10000 | 10000 |00:00:00.03 | 287 |
| 3 | TABLE ACCESS FULL| T2 | 1 | 10000 | 10000 |00:00:00.01 | 139 |
|* 4 | INDEX RANGE SCAN | IDX_T1_ID | 10000 | 1 | 10000 |00:00:00.02 | 148 |
-------------------------------------------------------------------------------------------
10G:
SQL> select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id = t2.id;
COUNT(*)
----------
10000
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST OUTLINE PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 877z7w0x4d3rx, child number 0
-------------------------------------
select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id =t2.id
Plan hash value: 3211355954
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.09 | 10157 |
| 2 | NESTED LOOPS | | 1 | 10000 | 10000 |00:00:00.12 | 10157 |
| 3 | TABLE ACCESS FULL| T2 | 1 | 10000 | 10000 |00:00:00.01 | 135 |
|* 4 | INDEX RANGE SCAN | IDX_T1_ID | 10000 | 1 | 10000 |00:00:00.07 | 10022 |
-------------------------------------------------------------------------------------------
--可以發現執行計劃一樣的,差別主要在掃描IDX_T1_ID索引上的buffers，可以知道11G改進了一些東西。因為T2插入是按照的id號的順序插入的，在掃描t1的IDX_T1_ID索引時id=1,id=2.....應該在同一索引資料塊上。
試想一下，如果T2表插入的資料不是有序的，邏輯讀沒有這麼少了。
5.建立表T3,打亂id的順序。重複以上的測試：
11G:
SQL> create table t3 nologging as select * from t1 order by dbms_random.random;
SQL> exec dbms_stats.gather_table_stats(user,'T3');
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
SQL> select/*+use_nl(t3 t1) gather_plan_statistics */ count(*) from t1,t3 where t1.id = t3.id;
COUNT(*)
----------
10000
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 10198
consistent gets from cache 10198
consistent gets from cache (fastpath) 10193
consistent gets - examination 5
consistent gets direct 0
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID cwdqmh7ryywx8, child number 0
-------------------------------------
select/*+use_nl(t3 t1) gather_plan_statistics */ count(*) from t1,t3 where t1.id = t3.id
Plan hash value: 1864576943
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.04 | 10161 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.04 | 10161 |
| 2 | NESTED LOOPS | | 1 | 10000 | 10000 |00:00:00.05 | 10161 |
| 3 | TABLE ACCESS FULL| T3 | 1 | 10794 | 10000 |00:00:00.01 | 139 |
|* 4 | INDEX RANGE SCAN | IDX_T1_ID | 10000 | 1 | 10000 |00:00:00.04 | 10022 |
-------------------------------------------------------------------------------------------
10g下：
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/*+use_nl(t3 t1) gather_plan_statistics */ count(*) from t1,t3 where t1.id = t3.id;
COUNT(*)
----------
10000
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 10208
consistent gets from cach 10208
consistent gets - examinaton 6
consistent gets direct 0
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 * from v$statname where NAME like 'consistent gets%';
STATISTIC# NAME CLASS STAT_ID
---------- ---------------------------------------------------------------- ---------- ----------
67 consistent gets 8 4162191256
68 consistent gets from cache 8 2839918855
69 consistent gets from cache (fastpath) 8 2173751438
70 consistent gets - examination 8 1701530557
71 consistent gets direct 8 420374750
如果對比10g可以發現，11G增加一個consistent gets from cache (fastpath)統計，google發現如下連結:
http://afatkulin.blogspot.com/2009/01/consistent-gets-from-cache-fastpath.html
1.重複測試:
create table t1 nologging as select rownum id ,a.* from all_objects a where rownum <=10000;
create table t2 nologging as select * from t1;
create index idx_t1_id on t1(id);
exec dbms_stats.gather_table_stats(user,'T1');
exec dbms_stats.gather_table_stats(user,'T2');
2.在11GR2下執行如下：
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
SQL> select/*+use_nl(t2 t1)*/ count(*) from t1,t2 where t1.id = t2.id;
COUNT(*)
----------
10000
10:26:20 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 324(+287)
consistent gets from cache 324(+287)
consistent gets from cache (fastpath) 298(+265)
consistent gets - examination 5(+1)
consistent gets direct 0
執行計劃如下:
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 518mw211s3mmv, child number 0
-------------------------------------
select/*+use_nl(t2 t1)*/ count(*) from t1,t2 where t1.id = t2.id
Plan hash value: 3211355954
--------------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | NESTED LOOPS | | 10000 |
| 3 | TABLE ACCESS FULL| T2 | 10000 |
|* 4 | INDEX RANGE SCAN | IDX_T1_ID | 1 |
--------------------------------------------------
3.在10G下執行:
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> 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/*+use_nl(t2 t1)*/ count(*) from t1,t2 where t1.id = t2.id;
COUNT(*)
----------
10000
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 10208(+10157)
consistent gets from cache 10208(+10157)
consistent gets - examination 6(+1)
consistent gets direct 0
--可以發現邏輯讀在10g下增加許多，在11G下僅僅287個邏輯讀。11g下邏輯讀明顯減少。
4.對比執行計劃：
11G:
SQL> select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id = t2.id;
COUNT(*)
----------
10000
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 877z7w0x4d3rx, child number 0
-------------------------------------
select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id = t2.id
Plan hash value: 3211355954
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 287 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.03 | 287 |
| 2 | NESTED LOOPS | | 1 | 10000 | 10000 |00:00:00.03 | 287 |
| 3 | TABLE ACCESS FULL| T2 | 1 | 10000 | 10000 |00:00:00.01 | 139 |
|* 4 | INDEX RANGE SCAN | IDX_T1_ID | 10000 | 1 | 10000 |00:00:00.02 | 148 |
-------------------------------------------------------------------------------------------
10G:
SQL> select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id = t2.id;
COUNT(*)
----------
10000
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST OUTLINE PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 877z7w0x4d3rx, child number 0
-------------------------------------
select/*+use_nl(t2 t1) gather_plan_statistics */ count(*) from t1,t2 where t1.id =t2.id
Plan hash value: 3211355954
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.09 | 10157 |
| 2 | NESTED LOOPS | | 1 | 10000 | 10000 |00:00:00.12 | 10157 |
| 3 | TABLE ACCESS FULL| T2 | 1 | 10000 | 10000 |00:00:00.01 | 135 |
|* 4 | INDEX RANGE SCAN | IDX_T1_ID | 10000 | 1 | 10000 |00:00:00.07 | 10022 |
-------------------------------------------------------------------------------------------
--可以發現執行計劃一樣的,差別主要在掃描IDX_T1_ID索引上的buffers，可以知道11G改進了一些東西。因為T2插入是按照的id號的順序插入的，在掃描t1的IDX_T1_ID索引時id=1,id=2.....應該在同一索引資料塊上。
試想一下，如果T2表插入的資料不是有序的，邏輯讀沒有這麼少了。
5.建立表T3,打亂id的順序。重複以上的測試：
11G:
SQL> create table t3 nologging as select * from t1 order by dbms_random.random;
SQL> exec dbms_stats.gather_table_stats(user,'T3');
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
SQL> select/*+use_nl(t3 t1) gather_plan_statistics */ count(*) from t1,t3 where t1.id = t3.id;
COUNT(*)
----------
10000
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 10198
consistent gets from cache 10198
consistent gets from cache (fastpath) 10193
consistent gets - examination 5
consistent gets direct 0
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID cwdqmh7ryywx8, child number 0
-------------------------------------
select/*+use_nl(t3 t1) gather_plan_statistics */ count(*) from t1,t3 where t1.id = t3.id
Plan hash value: 1864576943
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.04 | 10161 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.04 | 10161 |
| 2 | NESTED LOOPS | | 1 | 10000 | 10000 |00:00:00.05 | 10161 |
| 3 | TABLE ACCESS FULL| T3 | 1 | 10794 | 10000 |00:00:00.01 | 139 |
|* 4 | INDEX RANGE SCAN | IDX_T1_ID | 10000 | 1 | 10000 |00:00:00.04 | 10022 |
-------------------------------------------------------------------------------------------
10g下：
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/*+use_nl(t3 t1) gather_plan_statistics */ count(*) from t1,t3 where t1.id = t3.id;
COUNT(*)
----------
10000
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 10208
consistent gets from cach 10208
consistent gets - examinaton 6
consistent gets direct 0
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-714135/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20111229]理解consistent gets*相關資訊[補充]
- consistent gets
- oracle buffer gets=db block gets+consistent getsOracleBloC
- DB Bocks gets & Consistent gets 區別
- db block gets 與 consistent read getsBloC
- DB Block Gets、Consistent Gets、Physical ReadsBloC
- recursive calls， db block gets，consistent gets，physical ReadsBloC
- recursive calls ,db block gets , consistent gets的含義BloC
- DB Bocks gets，Consistent gets And Physical reads 轉載
- consistent gets 到底指的是什麼?
- 對'Consistent Gets',''Physical Reads'和'DB Block Gets'的理解BloC
- consistent gets、db block gets的簡單精闢的理解BloC
- 淺談consistent gets的計算
- Consistent Gets,Physical Reads和DB Block Gets的解釋（轉）BloC
- （轉）關於 db block gets,consistent gets,physical reads的概念BloC
- 對'Consistent Gets',''Physical Reads'和'DB Block Gets'的理解和解釋BloC
- 【Oracle-記憶體管理】－DB Blocks gets,Consistent gets And Physical readsOracle記憶體BloC
- consistent gets暴漲的一種情況
- sqlplus中arrayseize引數以及consistent getsSQL
- oracle實驗記錄 （oracle consistent gets 計算）Oracle
- Consistent Gets（就是logical read）+ DB Block Gets = 所謂的Logical ReadsBloC
- 關於統計中Consistent Gets，Physical Reads和DB Block Gets的意義BloC
- 關於執行計劃裡recursive calls，db block gets和consistent gets引數的解釋BloC
- [20111228]理解consistent gets*相關資訊
- [20111229]date time timestamp? ansi liternals.txt
- 行欲取導致sqlplus中trace的consistent gets遠大於實際塊數SQL
- HTTP快取——304與200 from cacheHTTP快取
- [20190416]檢視shared latch gets的變化.txt
- SQL*Net more data from dblink引起library cache pinSQL
- [20170727]library cache: mutex X.txtMutex
- Export Parameter : ConsistentExport
- [20240824]跟蹤library cache lock library cache pin使用gdb.txt
- [20190402]Library Cache mutex.txtMutex
- [20240920]跟蹤library cache lock library cache pin使用gdb.txt
- 在 docker-in-docker 環境中使用 cache-from 提升編譯速度Docker編譯
- gets函式的漏洞函式
- [20210507]分析library cache轉儲.txt
- [20141219]result cache與view.txtView