[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.