[20210419]CBC latch再讨论4.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 reset "_db_hot_block_tracking";
System altered.
--//重启数据库略。
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
3.看看单独1,2, .. 50个用户平行的情况执行的情况。
$ seq 50 | xargs -IQ echo "seq Q | xargs -I{} -P Q sqlplus -s -l scott/book @m10.txt 1e5 p1index_id=Q {} >/dev/null "
$ seq 50 | xargs -IQ echo "seq Q | xargs -I{} -P Q sqlplus -s -l scott/book @m10.txt 1e5 p1index_id=Q {} >/dev/null " > bb.sh
$ . ./bb.sh
SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA),min(time_ela),max(time_ela) from scott.job_times group by method order by 2 ;
METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA) MIN(TIME_ELA) MAX(TIME_ELA)
-------------------- ---------- ---------------------- ------------- ------------- -------------
p1index_id=1 1 264 264 264 264
p1index_id=2 2 275 549 246 303
p1index_id=3 3 283 850 270 305
p1index_id=4 4 297 1187 285 313
p1index_id=5 5 299 1497 292 307
p1index_id=6 6 305 1829 303 306
p1index_id=7 7 306 2145 301 311
p1index_id=8 8 316 2524 300 352
p1index_id=9 9 330 2968 324 339
p1index_id=10 10 335 3351 325 347
p1index_id=11 11 359 3950 330 404
p1index_id=12 12 374 4487 345 442
p1index_id=13 13 400 5200 369 465
p1index_id=14 14 412 5765 344 477
p1index_id=15 15 453 6802 408 515
p1index_id=16 16 486 7769 419 544
p1index_id=17 17 511 8679 399 573
p1index_id=18 18 564 10153 464 628
p1index_id=19 19 612 11637 493 675
p1index_id=20 20 668 13350 523 735
p1index_id=21 21 720 15120 600 794
p1index_id=22 22 787 17320 700 841
p1index_id=23 23 897 20635 832 952
p1index_id=24 24 1018 24438 940 1065
p1index_id=25 25 1523 38082 1484 1541
p1index_id=26 26 1492 38784 1456 1511
p1index_id=27 27 1468 39638 1427 1492
p1index_id=28 28 1566 43853 1506 1589
p1index_id=29 29 1600 46413 1556 1625
p1index_id=30 30 1623 48680 1554 1648
p1index_id=31 31 1655 51303 1594 1681
p1index_id=32 32 1695 54235 1634 1726
p1index_id=33 33 1780 58746 1661 1812
p1index_id=34 34 1817 61787 1700 1853
p1index_id=35 35 1870 65436 1784 1905
p1index_id=36 36 1890 68033 1784 1924
p1index_id=37 37 1998 73940 1913 2035
p1index_id=38 38 1999 75951 1893 2042
p1index_id=39 39 2045 79739 1942 2082
p1index_id=40 40 2159 86346 2013 2196
p1index_id=41 41 2198 90121 2057 2247
p1index_id=42 42 2233 93771 2094 2285
p1index_id=43 43 2312 99414 2146 2357
p1index_id=44 44 2376 104550 2294 2415
p1index_id=45 45 2436 109600 2277 2495
p1index_id=46 46 2481 114117 2335 2540
p1index_id=47 47 2461 115689 2269 2515
p1index_id=48 48 2585 124079 2376 2646
p1index_id=49 49 2592 126986 2463 2661
p1index_id=50 50 2682 134087 2508 2749
50 rows selected.
--//并行25时间稍微长一点，估计正好遇到整点的awr生成的情况。
--//另外从测试可以看出从24到25之间存在一个大的跳跃。我测试环境cpu_count=24.
4.测试使用select /*+ index(t) &&3 */ count (name) into v_id from t where id=1;的情况，使用唯一索引。
SCOTT@book> rename job_times to job_times_old1;
Table renamed.
SCOTT@book> create table job_times (sid number, time_ela number,method varchar2(20));
Table created.
$ seq 50 | xargs -IQ echo "seq Q | xargs -I{} -P Q sqlplus -s -l scott/book @m10.txt 1e5 p1unique_index=Q {} >/dev/null " > cc.sh
--//修改m10.txt 略。
SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA),min(time_ela),max(time_ela) from scott.job_times group by method order by 2 ;
METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA) MIN(TIME_ELA) MAX(TIME_ELA)
-------------------- -------- ---------------------- ------------- ------------- -------------
p1unique_index=1 1 297 297 297 297
p1unique_index=2 2 287 573 280 293
p1unique_index=3 3 284 852 270 306
p1unique_index=4 4 297 1187 292 301
p1unique_index=5 5 279 1395 273 287
p1unique_index=6 6 286 1715 277 295
p1unique_index=7 7 282 1971 277 286
p1unique_index=8 8 276 2210 270 280
p1unique_index=9 9 276 2485 270 282
p1unique_index=10 10 278 2777 271 285
p1unique_index=11 11 297 3268 271 370
p1unique_index=12 12 286 3430 266 320
p1unique_index=13 13 283 3683 266 352
p1unique_index=14 14 311 4354 265 368
p1unique_index=15 15 313 4689 273 369
p1unique_index=16 16 316 5052 265 367
p1unique_index=17 17 320 5439 265 364
p1unique_index=18 18 331 5957 263 380
p1unique_index=19 19 344 6528 268 389
p1unique_index=20 20 355 7092 273 395
p1unique_index=21 21 362 7594 268 393
p1unique_index=22 22 376 8280 264 404
p1unique_index=23 23 389 8944 267 407
p1unique_index=24 24 394 9464 380 413
p1unique_index=25 25 412 10311 329 455
p1unique_index=26 26 424 11018 391 454
p1unique_index=27 27 437 11804 401 487
p1unique_index=28 28 445 12457 398 493
p1unique_index=29 29 468 13584 407 524
p1unique_index=30 30 474 14228 398 535
p1unique_index=31 31 485 15026 401 536
p1unique_index=32 32 495 15829 400 551
p1unique_index=33 33 519 17115 418 568
p1unique_index=34 34 536 18217 461 580
p1unique_index=35 35 538 18840 408 596
p1unique_index=36 36 560 20143 412 615
p1unique_index=37 37 578 21386 475 626
p1unique_index=38 38 584 22186 490 657
p1unique_index=39 39 603 23518 407 665
p1unique_index=40 40 619 24760 433 677
p1unique_index=41 41 629 25804 546 675
p1unique_index=42 42 642 26950 504 701
p1unique_index=43 43 654 28115 516 705
p1unique_index=44 44 686 30177 630 727
p1unique_index=45 45 683 30755 578 746
p1unique_index=46 46 699 32155 599 752
p1unique_index=47 47 716 33673 606 778
p1unique_index=48 48 733 35192 657 793
p1unique_index=49 49 753 36913 614 798
p1unique_index=50 50 766 38317 661 814
50 rows selected.
--//不存在cbc latch争用的情况增加很缓慢。不展开分析。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2768637/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [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
