[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2768632/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261102]光标跟踪事件9.txt
- [20261102]光标跟踪事件8.txt
- [20261102]为什么出现光标出现TOP_LEVEL_RPI_CURSOR不共享.txt
- [20261102]改变pdb名字.txt
- [20261018]光标跟踪事件3.txt
- [20261016]光标跟踪事件2.txt
- [20261015]模拟kksfbc child completion等待事件2.txt
- [20261013]光标跟踪事件.txt
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
