[20220124]index split level.txt

[20220124]index split level.txt --//https://jonathanlewis.wordpress.com/2022/01/09/oeri-6051/,重复测试: 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 2.建立2K的表空间: SYS@book> alter system set db_2k_cache_size=16m scope=spfile; System altered. --//重启数据库. CREATE TABLESPACE tea2k DATAFILE   '/mnt/ramdisk/book/tea2k01.dbf' SIZE 100M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 2K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON / 3.测试脚本: rem Script:  silly_index_3a.sql rem Author:  Jonathan Lewis rem Dated:   Mar 2004 / Jan 2022 rem Purpose: Build an index with a large blevel rem Notes: rem Uses 2K block size for tablespace holding the index rem Estimated run-time (for me) with m_blevel = 23 - ca. 1 hour define m_blevel = 5 define m_rows = power(2,&m_blevel) drop table t1 purge; create table t1 (v1 varchar2(8)); create index t1_i1 on t1(substrb(lpad(v1,1469,'0'),1,1469)) tablespace tea2k ; -- execute snap_my_stats.start_snap prompt  =================================================== prompt  Inserting &m_rows (short) rows in reverse order prompt  =================================================== begin    for i in reverse 1..&m_rows loop            insert into t1 values (i); --         commit;    end loop; end; / -- execute snap_my_stats.end_snap prompt  ================ prompt  Validating index prompt  ================ validate index t1_i1; select         lf_rows, height, height-1 blevel, lf_blks, br_blks from         index_stats ; column  object_id new_value m_object_id select  object_id from    user_objects where   object_name = 'T1_I1' / alter session set events 'immediate trace name treedump level &m_object_id'; insert into t1(v1) values('0'); SCOTT@book> @ silly_index_3a.sql Table dropped. Table created. Index created. =================================================== Inserting power(2,5) (short) rows in reverse order =================================================== PL/SQL procedure successfully completed. ================ Validating index ================ Index analyzed.    LF_ROWS     HEIGHT     BLEVEL    LF_BLKS    BR_BLKS ---------- ---------- ---------- ---------- ----------         32          6          5         32         31  OBJECT_ID ----------     331001 Session altered. 1 row created. SCOTT@book> @ ttt tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_27138.trc ----- begin tree dump branch: 0x1c00204 29360644 (0: nrow: 2, level: 5)    branch: 0x1c0023b 29360699 (-1: nrow: 2, level: 4)       branch: 0x1c00205 29360645 (-1: nrow: 2, level: 3)          branch: 0x1c0021d 29360669 (-1: nrow: 2, level: 2)             branch: 0x1c00218 29360664 (-1: nrow: 2, level: 1)                leaf: 0x1c00215 29360661 (-1: nrow: 1 rrow: 1)                leaf: 0x1c00259 29360729 (0: nrow: 1 rrow: 1)             branch: 0x1c00258 29360728 (0: nrow: 2, level: 1)                leaf: 0x1c00257 29360727 (-1: nrow: 1 rrow: 1)                leaf: 0x1c00256 29360726 (0: nrow: 1 rrow: 1)          branch: 0x1c00255 29360725 (0: nrow: 2, level: 2)             branch: 0x1c00228 29360680 (-1: nrow: 2, level: 1)                leaf: 0x1c00227 29360679 (-1: nrow: 1 rrow: 1)                leaf: 0x1c00226 29360678 (0: nrow: 1 rrow: 1)             branch: 0x1c00225 29360677 (0: nrow: 2, level: 1)                leaf: 0x1c00224 29360676 (-1: nrow: 1 rrow: 1)                leaf: 0x1c00223 29360675 (0: nrow: 1 rrow: 1)       branch: 0x1c00222 29360674 (0: nrow: 2, level: 3)          branch: 0x1c0022f 29360687 (-1: nrow: 2, level: 2)             branch: 0x1c0022e 29360686 (-1: nrow: 2, level: 1)                leaf: 0x1c0022d 29360685 (-1: nrow: 1 rrow: 1)                leaf: 0x1c0022c 29360684 (0: nrow: 1 rrow: 1)             branch: 0x1c0022b 29360683 (0: nrow: 2, level: 1)                leaf: 0x1c0022a 29360682 (-1: nrow: 1 rrow: 1)                leaf: 0x1c00234 29360692 (0: nrow: 1 rrow: 1)          branch: 0x1c00233 29360691 (0: nrow: 2, level: 2)             branch: 0x1c00232 29360690 (-1: nrow: 2, level: 1)                leaf: 0x1c00231 29360689 (-1: nrow: 1 rrow: 1)                leaf: 0x1c00230 29360688 (0: nrow: 1 rrow: 1)             branch: 0x1c0023f 29360703 (0: nrow: 2, level: 1)                leaf: 0x1c0023e 29360702 (-1: nrow: 1 rrow: 1)                leaf: 0x1c0023d 29360701 (0: nrow: 1 rrow: 1)    branch: 0x1c0023c 29360700 (0: nrow: 2, level: 4)       branch: 0x1c0023a 29360698 (-1: nrow: 2, level: 3)          branch: 0x1c00239 29360697 (-1: nrow: 2, level: 2)             branch: 0x1c00238 29360696 (-1: nrow: 2, level: 1)                leaf: 0x1c00237 29360695 (-1: nrow: 1 rrow: 1)                leaf: 0x1c00236 29360694 (0: nrow: 1 rrow: 1)             branch: 0x1c00229 29360681 (0: nrow: 2, level: 1)                leaf: 0x1c00235 29360693 (-1: nrow: 1 rrow: 1)                leaf: 0x1c0020e 29360654 (0: nrow: 1 rrow: 1)          branch: 0x1c0020d 29360653 (0: nrow: 2, level: 2)             branch: 0x1c0020c 29360652 (-1: nrow: 2, level: 1)                leaf: 0x1c0020b 29360651 (-1: nrow: 1 rrow: 1)                leaf: 0x1c0020a 29360650 (0: nrow: 1 rrow: 1)             branch: 0x1c00209 29360649 (0: nrow: 2, level: 1)                leaf: 0x1c00208 29360648 (-1: nrow: 1 rrow: 1)                leaf: 0x1c00207 29360647 (0: nrow: 1 rrow: 1)       branch: 0x1c00206 29360646 (0: nrow: 2, level: 3)          branch: 0x1c0020f 29360655 (-1: nrow: 2, level: 2)             branch: 0x1c00214 29360660 (-1: nrow: 2, level: 1)                leaf: 0x1c00213 29360659 (-1: nrow: 1 rrow: 1)                leaf: 0x1c00212 29360658 (0: nrow: 1 rrow: 1)             branch: 0x1c00211 29360657 (0: nrow: 2, level: 1)                leaf: 0x1c00210 29360656 (-1: nrow: 1 rrow: 1)                leaf: 0x1c0021f 29360671 (0: nrow: 1 rrow: 1)          branch: 0x1c0021e 29360670 (0: nrow: 2, level: 2)             branch: 0x1c0021c 29360668 (-1: nrow: 2, level: 1)                leaf: 0x1c0021b 29360667 (-1: nrow: 1 rrow: 1)                leaf: 0x1c0021a 29360666 (0: nrow: 1 rrow: 1)             branch: 0x1c00219 29360665 (0: nrow: 2, level: 1)                leaf: 0x1c00217 29360663 (-1: nrow: 1 rrow: 1)                leaf: 0x1c00216 29360662 (0: nrow: 1 rrow: 1) ----- end tree dump --//作者利用2K数据块,保存键值1469长度字符串,这样每块仅仅1个键值,快速提高level水平. --//2^23 = 8388608条,8388608*2/1024/1024 = 16G ,要达到23level测试时间有点长. CREATE TABLESPACE TEA2Kx DATAFILE   '/u01/tea2kx01.dbf' SIZE 100M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED LOGGING ONLINE EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 2K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON; --//修改脚本m_blevel =23,表空间指向tea2kx.重复测试.明天看看. SCOTT@book> @ silly_index_3a.sql Table dropped. Table created. Index created. =================================================== Inserting power(2,23) (short) rows in reverse order =================================================== begin * ERROR at line 1: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1' ORA-06512: at line 3 ================ Validating index ================ Index analyzed.    LF_ROWS     HEIGHT     BLEVEL    LF_BLKS    BR_BLKS ---------- ---------- ---------- ---------- ----------          0         20         19     317024     317030  OBJECT_ID ----------     331003 Session altered. 1 row created. SCOTT@book> commit ; Commit complete. --//放弃,磁盘空间不足.