[20210418]CBC latch再討論3.txt
[20210418]CBC latch再討論3.txt
--//繼續連結http://blog.itpub.net/267265/viewspace-2768582/的測試，再增加一個測試：
1.環境：
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SYS@book> @ hide _db_hot_block_tracking
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------------------- ------------------------------------------ ------------- ------------- ------------ ----- ---------
_db_hot_block_tracking track hot blocks for hash latch contention TRUE FALSE FALSE TRUE IMMEDIATE
SYS@book> alter system set "_db_hot_block_tracking"=true scope=memory;
System altered.
SYS@book> alter system set "_db_hot_block_tracking"=true scope=both;
System altered.
SYS@book> select * from x$kslhot;
ADDR INDX INST_ID KSLHOT_ID KSLHOT_REF
---------------- ---------- ---------- ---------- ----------
0000000086199AA0 0 1 0 0
0000000086199AB0 1 1 0 0
0000000086199AC0 2 1 0 0
0000000086199AD0 3 1 0 0
0000000086199AE0 4 1 0 0
0000000086199AF0 5 1 0 0
0000000086199B00 6 1 0 0
0000000086199B10 7 1 0 0
0000000086199B20 8 1 0 0
0000000086199B30 9 1 0 0
10 rows selected.
--//視乎僅僅記錄10個塊地址。
2.建立測試環境：
SCOTT@book> create table job_times (sid number, time_ela number,method varchar2(20));
Table created.
SCOTT@book> create table t as select rownum id ,cast('test' as varchar2(10)) name from dual ;
Table created.
SCOTT@book> alter table t modify ( id not null );
Table altered.
--//分析表略。
SCOTT@book> select rowid from t;
ROWID
------------------
AAAWdqAAEAAAALbAAA
SCOTT@book> @ rowid AAAWdqAAEAAAALbAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
92010 4 731 0 0x10002DB 4,731 alter system dump datafile 4 block 731 ;
create index i_t_id on t(id);
--//create unique index i_t_id on t(id);
--//drop index i_t_id_name ;
$ cat m10.txt
set verify off
host sleep $(echo &&3/50 | bc -l )
variable vmethod varchar2(20);
exec :vmethod := '&&2';
insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,:vmethod) ;
commit ;
declare
v_id number;
v_d date;
begin
for i in 1 .. &&1 loop
--select /*+ full(t) &&3 */ count (name) into v_id from t ;
--select /*+ index(t) &&3 */ count (name) into v_id from t ;
--select /*+ ROWID(t) &&3 */ count (name) into v_id from t where rowid='AAAWdqAAEAAAALbAAA';
--select /*+ index(t) &&3 */ count (name) into v_id from t where id=1;
--select /*+ index_ffs(t) &&3 */ count (*) into v_id from t ;
--select /*+ index_ffs(t) &&3 */ count (name) into v_id from t ;
--select /*+ ROWID(t) &&3 */ count (name) into v_id from t where rowid between 'AAAWdqAAEAAAALbAAA' and 'AAAWdqAAEAAAALbAAB';
select /*+ index(t) &&3 */ count (id) into v_id from t ;
end loop;
end ;
/
update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method=:vmethod;
commit;
quit
--//這次測試不用回表，這樣cbc latch的爭用更加密集，理論講更加慢，看看測試結果。
SCOTT@book> select /*+ index(t) &&3 */ count (id) from t ;
COUNT(ID)
----------
1
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 5a28jmqxp3xvr, child number 1
-------------------------------------
select /*+ index(t) 11 */ count (id) from t
Plan hash value: 4021579484
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 (100)| | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 1 |
| 2 | INDEX FULL SCAN| I_T_ID | 1 | 1 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
COUNT(NAME)
-----------
1
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID d00rqa296sd3f, child number 0
-------------------------------------
select /*+ index(t) 11 */ count (name) from t
Plan hash value: 200203536
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 2 |
| 1 | SORT AGGREGATE | | 1 | 1 | 5 | | | 1 |00:00:00.01 | 2 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 5 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
| 3 | INDEX FULL SCAN | I_T_ID | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |
---------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T@SEL$1
3 - SEL$1 / T@SEL$1
--//select /*+ index(t) 11 */ count (id) from t; 邏輯讀1
--//select /*+ index(t) 11 */ count (name) from t; 邏輯讀2
--//這樣前者cbc latch增用更加密集，這樣理論執行時間要更長一些。
$ seq 150 | xargs -I{} -P 150 sqlplus -s -l scott/book @m10.txt 1e5 p1index_id=150 {} >/dev/null
--//注每次測試完成我都重啟資料庫。這樣檢視x$kslhot內容會清空。
3.測試方法在前面的測試已經說明，僅僅記錄測試結果:
SYS@book> select * from x$kslhot where KSLHOT_REF>0;
ADDR INDX INST_ID KSLHOT_ID KSLHOT_REF
---------------- ---------- ---------- ---------- ----------
0000000086199AA0 0 1 4195268 2
0000000086199AB0 1 1 16781643 557180
SYS@book> @ find_obj 4 4427
FILE_ID BLOCK_ID BLOCKS SEGMENT_TYPE OWNER SEGMENT_NAME PARTITION_NAME EXTENT_ID BYTES TABLESPACE_NAME RELATIVE_FNO SEGTSN SEGRFN SEGBID
---------- ---------- ---------- -------------------- ------ --------------- --------------- ---------- ---------- ------------------------------ ------------ ---------- ---------- ----------
4 4424 8 INDEX SCOTT I_T_ID 0 65536 USERS 4 4 4 4426
SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from scott.job_times group by method order by 3 ;
METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)
-------------------- ---------- ---------------------- -------------
p1rowid=150 150 2280 341990
p1uniindex=150 150 2382 357225
p1indexffs_name=150 150 2669 400324
p1indexffs=150 150 2698 404737
p1full=150 150 2769 415285
p1rowidbetween=150 150 4357 653557
p1index_fs=150 150 6748 1012159
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
p1not_uniindex=150 150 7046 1056924
p1index_id=150 150 7924 1188671
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
9 rows selected.
--//可以發現平均慢了接近12秒，而實際上這次邏輯讀更小，理論執行更快，而實際上反而更慢，cbc latch爭用更加厲害。
--//看看單獨1,2,24個使用者執行的情況。
$ seq 1 | xargs -I{} -P 1 sqlplus -s -l scott/book @m10.txt 1e5 p1index_id=1 {} >/dev/null
$ seq 2 | xargs -I{} -P 2 sqlplus -s -l scott/book @m10.txt 1e5 p1index_id=2 {} >/dev/null
$ seq 24 | xargs -I{} -P 24 sqlplus -s -l scott/book @m10.txt 1e5 p1index_id=24 {} >/dev/null
SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from scott.job_times group by method order by 3 ;
METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)
-------------------- ---------- ---------------------- -------------
p1index_id=1 1 273 273
p1index_id=2 2 287 574
p1index_id=24 24 1090 26161
p1rowid=150 150 2280 341990
p1uniindex=150 150 2382 357225
p1indexffs_name=150 150 2669 400324
p1indexffs=150 150 2698 404737
p1full=150 150 2769 415285
p1rowidbetween=150 150 4357 653557
p1index_fs=150 150 6748 1012159
p1not_uniindex=150 150 7046 1056924
p1index_id=150 150 7924 1188671
12 rows selected.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2768632/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210419]CBC latch再討論4.txt
- [20210413]CBC latch再討論2.txt
- [20211229]再論19c latch free等待事件分析.txt事件
- [20220413]shared pool latch與使用sga heap的疑問3.txt
- 討論
- [iOS Monkey 討論帖] 整套新的 fastmonkey 討論iOSAST
- [20210520]11g shared pool latch與library cache mutex的簡單探究3.txtMutex
- [Note] git清空.git目錄後，重新再git init & git remote的討論GitREM
- httprunner 大佬討論群HTTP
- [譯] 討論 JS ⚡：文件JS
- 當我們在討論CQRS時，我們在討論些神馬？
- 小組討論結果
- js中分號的討論JS
- 當我們在討論遊戲社群時，我們在討論什麼？遊戲
- 【討論】論 cursor 在測試中的使用
- 主題討論，第六組
- 討論專案合理分層
- 資料分析主題討論
- 外掛需求討論群 241266707
- 原始碼防洩密討論原始碼
- 有沒有一些大廠的高階架構技術討論討論架構
- 求一.NET算術演算法.歡迎朋友們都進來討論討論.演算法
- AES CBC 加密解密加密解密
- laravel 事件系統 問題討論Laravel事件
- Flutter 仿QQ討論組頭像Flutter
- PHP vs Node.js 深入討論PHPNode.js
- 對容器映象的思考和討論
- 全域性角度出發討論敏捷敏捷
- Go知識圖譜討論帖Go
- [20210418]開啟多個程式執行.txt
- [20210418]查詢v$檢視問題.txt
- Twitter：2019年Twitter遊戲討論度排行 日本為最熱衷討論遊戲國家遊戲
- mutex,latch,lock,enqueue hash chains latch基礎概念MutexENQAI
- 2149.令人討厭的手機號（再續）
- AES-CBC 模式加密模式加密
- 資料庫系統架構討論資料庫架構
- 為什麼需要討論“當代性”？
- 關於神經網路的討論神經網路