观察索引组织表（IOT）和传统堆表性能差异

来源：互联网发布：中元节生日知乎编辑：程序博客网时间：2024/06/06 04:57

--配置实验环境EODA@PROD1> create table emp  2  as  3  select object_id empno,  4  object_name ename,  5  created      hiredate,  6  owner      job  7  from all_objects  8  /Table created.EODA@PROD1> EODA@PROD1> alter table emp add constraint emp_pk primary key(empno);Table altered.EODA@PROD1> EODA@PROD1> begin  2    dbms_stats.gather_table_stats( user, 'EMP', cascade=>true );  3  end;  4  /PL/SQL procedure successfully completed.EODA@PROD1> EODA@PROD1> create table heap_addresses  2  ( empno references emp(empno) on delete cascade,  3    addr_type varchar2(10),  4    street varchar2(20),  5    city varchar2(20),  6    state varchar2(2),  7    zip number,  8    primary key (empno,addr_type)  9  ) 10  /Table created.EODA@PROD1> EODA@PROD1> create table iot_addresses  2    ( empno   references emp(empno) on delete cascade,  3   addr_type varchar2(10),  4   street    varchar2(20),  5   city   varchar2(20),  6   state   varchar2(2),  7   zip   number,  8   primary key (empno,addr_type)  9    ) 10  ORGANIZATION INDEX 11  /Table created.EODA@PROD1> insert into heap_addresses select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123 from emp;72830 rows created.EODA@PROD1> insert into iot_addresses select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123 from emp;72830 rows created.EODA@PROD1> insert into heap_addresses select empno, 'HOME', '123 main street', 'Washington', 'DC', 20123 from emp;72830 rows created.EODA@PROD1> insert into iot_addresses select empno, 'HOME', '123 main street', 'Washington', 'DC', 20123   from emp;72830 rows created.EODA@PROD1> insert into heap_addresses select empno, 'PREV', '123 main street', 'Washington', 'DC', 20123 from emp;72830 rows created.EODA@PROD1> insert into iot_addresses select empno, 'PREV', '123 main street', 'Washington', 'DC', 20123 from emp;72830 rows created.EODA@PROD1> insert into heap_addresses select empno, 'SCHOOL', '123 main street', 'Washington', 'DC', 20123 from emp;72830 rows created.EODA@PROD1> insert into iot_addresses select empno, 'SCHOOL', '123 main street', 'Washington', 'DC', 20123 from emp;72830 rows created.EODA@PROD1> exec dbms_stats.gather_table_stats( user, 'HEAP_ADDRESSES' );PL/SQL procedure successfully completed.EODA@PROD1> exec dbms_stats.gather_table_stats( user, 'IOT_ADDRESSES' );PL/SQL procedure successfully completed.

--模拟运行set echo onexec dbms_monitor.session_trace_enable;begin      for x in ( select empno from emp )      loop          for y in ( select emp.ename, a.street, a.city, a.state, a.zip                       from emp, heap_addresses a                       where emp.empno = a.empno                       and emp.empno = x.empno )          loop             null;          end loop;      end loop;end;/begin      for x in ( select empno from emp )      loop          for y in ( select emp.ename, a.street, a.city, a.state, a.zip                       from emp, iot_addresses a                       where emp.empno = a.empno                       and emp.empno = x.empno )          loop             null;          end loop;      end loop;end;/exec dbms_monitor.session_trace_disable;

--tkprof查看结果SQL ID: 0rjynmp9ry1fv Plan Hash: 132509299SELECT EMP.ENAME, A.STREET, A.CITY, A.STATE, A.ZIPFROM EMP, HEAP_ADDRESSES A WHERE EMP.EMPNO = A.EMPNO AND EMP.EMPNO = :B1call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute  72830      0.67       0.82          0          0          0           0Fetch    72830      2.00       2.41          0     729742          0      291320------- ------  -------- ---------- ---------- ---------- ----------  ----------total   145661      2.68       3.23          0     729742          0      291320    --堆表进行了73W次逻辑I/OMisses in library cache during parse: 1Misses in library cache during execute: 1Optimizer mode: ALL_ROWSParsing user id: 97     (recursive depth: 1)Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max)  Row Source Operation********************************************************************************SQL ID: cu5b727auc25x Plan Hash: 2655033663SELECT EMP.ENAME, A.STREET, A.CITY, A.STATE, A.ZIPFROM EMP, IOT_ADDRESSES A WHERE EMP.EMPNO = A.EMPNO AND EMP.EMPNO = :B1call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute  72830      0.66       0.83          0          0          0           0Fetch    72830      1.41       1.71          0     441723          0      291320------- ------  -------- ---------- ---------- ---------- ----------  ----------total   145661      2.08       2.54          0     441723          0      291320   --IOT进行了44W次逻辑I/OMisses in library cache during parse: 1Misses in library cache during execute: 1Optimizer mode: ALL_ROWSParsing user id: 97     (recursive depth: 1)Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max)  Row Source Operation---------- ---------- ----------  ---------------------------------------------------         4          4          4  NESTED LOOPS  (cr=7 pr=0 pw=0 time=193 us cost=4 size=280 card=4)         1          1          1   TABLE ACCESS BY INDEX ROWID EMP (cr=3 pr=0 pw=0 time=20 us cost=2 size=30 card=1)         1          1          1    INDEX UNIQUE SCAN EMP_PK (cr=2 pr=0 pw=0 time=11 us cost=1 size=0 card=1)(object id 85793)         4          4          4   INDEX RANGE SCAN SYS_IOT_TOP_85796 (cr=4 pr=0 pw=0 time=170 us cost=2 size=160 card=4)(object id 85797)********************************************************************************

Run1 ran in 233 cpu hsecs  --heap tabelRun2 ran in 206 cpu hsecs  --IOT tablerun 1 ran in 113.11% of the timeName  Run1  Run2  DiffSTAT...Requests to/from client     5     4    -1STAT...free buffer requested    10    11     1STAT...hot buffers moved to he     1     0    -1STAT...redo entries    14    15     1STAT...rows fetched via callba72,83172,830    -1STAT...index fetch by key72,83172,830    -1STAT...HSC Heap Segment Block    14    15     1STAT...Heap Segment Array Inse    14    15     1STAT...parse time elapsed     2     1    -1STAT...parse count (hard)     5     4    -1LATCH.post/wait queue     1     2     1LATCH.call allocation     1     2     1LATCH.session allocation     4     3    -1LATCH.session switching      2     1    -1LATCH.session idle bit    15    14    -1LATCH.FOB s.o list latch     1     0    -1LATCH.ksv allocation latch     1     0    -1LATCH.KMG MMAN ready and start     0     1     1LATCH.cache buffers lru chain    12    11    -1LATCH.dml lock allocation     1     0    -1LATCH.list of block allocation     0     1     1LATCH.undo global data     5     6     1LATCH.Change Notification Hash     0     1     1LATCH.sort extent pool     1     0    -1LATCH.deferred cleanup latch     1     0    -1LATCH.resmgr:active threads     1     0    -1LATCH.resmgr:schema config    10     9    -1LATCH.cp sga latch     1     0    -1LATCH.ncodef allocation latch     1     0    -1LATCH.ASM network state latch     1     0    -1LATCH.ASM db client latch     0     2     2STAT...calls to get snapshot s72,83972,837    -2LATCH.redo writing     4     6     2LATCH.redo allocation     5     3    -2LATCH.In memory undo latch     3     1    -2LATCH.job_queue_processes para     2     0    -2LATCH.object queue header oper    26    23    -3STAT...cleanout - number of kt     0     3     3STAT...session cursor cache hi 7,298 7,301     3STAT...bytes received via SQL* 1,513 1,510    -3STAT...parse count (total)     7     4    -3STAT...recursive calls73,60673,603    -3STAT...active txn count during     0     3     3STAT...calls to kcmgcs    28    31     3LATCH.session state list latch     3     0    -3STAT...session cursor cache co     6     2    -4LATCH.space background task la     0     4     4LATCH.resmgr:resource group CP    47    43    -4STAT...db block changes     75    71    -4STAT...consistent changes    60    55    -5STAT...enqueue releases     12     7    -5LATCH.active service list     8     2    -6LATCH.shared pool simulator    14     8    -6STAT...enqueue requests     13     7    -6STAT...db block gets from cach    58    49    -9STAT...db block gets    58    49    -9LATCH.messages    14    26    12LATCH.channel operations paren     1    15    14LATCH.checkpoint queue latch     9    25    16STAT...undo change vector size 3,368 3,392    24STAT...CPU used when call star   232   207   -25STAT...CPU used by this sessio   233   206   -27STAT...DB time   236   208   -28STAT...recursive cpu usage   203   175   -28STAT...Elapsed Time   237   207   -30LATCH.JS queue state obj latch    36     0   -36LATCH.row cache objects    310   271   -39LATCH.shared pool73,15173,100   -51LATCH.SQL memory manager worka     3    70    67LATCH.enqueue hash chains    98    13   -85STAT...redo size 4,480 4,576    96STAT...workarea memory allocat   -64    59   123LATCH.simulator hash latch31,75723,967-7,790STAT...session uga memory     065,51265,512STAT...session uga memory max       123,452     0      -123,452STAT...buffer is pinned count       218,490     0      -218,490STAT...consistent gets from ca       730,649       442,631      -288,018STAT...consistent gets from ca       366,49678,478      -288,018STAT...consistent gets       730,649       442,631      -288,018STAT...no work - consistent re       366,48278,463      -288,019STAT...session logical reads       730,707       442,680      -288,027STAT...table fetch by rowid       364,16172,840      -291,321STAT...buffer is not pinned co       655,492       291,340      -364,152STAT...session pga memory max       458,75265,536      -393,216STAT...session pga memory       458,75265,536      -393,216LATCH.cache buffers chains     1,097,377       521,329      -576,048STAT...logical read bytes from 5,985,951,744 3,626,434,560-2,359,517,184  Run1 latches total versus runs -- difference and pct   --IOT存在显著下降Run1   Run2      Diff Pct1,203,086    619,118     -583,968 194.32%

IOT提高缓冲区缓存效率因为在给定查询中需要的块更少，而且减少了缓冲区缓存访问所以改善了可扩展性。

0 0