[20210419]CBC latch再讨论4.txt

来源：这里教程网时间：2026-03-03 16:37:18 作者：

[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争用的情况增加很缓慢。不展开分析。