散列聚簇表浅析

来源：互联网发布：六宫格切图软件编辑：程序博客网时间：2024/05/24 06:06
--散列聚簇表浅析--结构分析EODA@PROD1> create cluster hash_cluster  2    ( hash_key number )  3   hashkeys 1000  4   size 8192  5   tablespace mssm  6  /Cluster created.EODA@PROD1> EODA@PROD1> exec show_space( 'HASH_CLUSTER', user, 'CLUSTER' )Free Blocks.............................       0Total Blocks............................   1,024  --一共分配了1024个块，应该是1009个（因为需要是质数），但实际分配会高一些Total Bytes.............................       8,388,608Total MBytes............................       8Unused Blocks...........................      14  --14个块未被使用Unused Bytes............................ 114,688Last Used Ext FileId....................      10Last Used Ext BlockId...................   1,024Last Used Block.........................     114PL/SQL procedure successfully completed.--对于一个散列聚簇，表一开始就很大，需要花更长的时间去创建，因为oracle必须要初始化各个块。--对于一般的表来说这个动作通常在数据增加到表时才发生。--性能区别EODA@PROD1> drop cluster hash_cluster;Cluster dropped.EODA@PROD1> EODA@PROD1> create cluster hash_cluster  2    ( hash_key number )  3   hashkeys 75000  4   size 150  5  /Cluster created.EODA@PROD1> EODA@PROD1> create table t_hashed  --创建散列聚簇表  2    cluster hash_cluster(object_id)  3    as  4    select *  5    from all_objects  6  /Table created.EODA@PROD1> EODA@PROD1> alter table t_hashed add constraint  2    t_hashed_pk primary key(object_id)  3  /Table altered.EODA@PROD1> EODA@PROD1> begin  2   dbms_stats.gather_table_stats( user, 'T_HASHED' );  3  end;  4  /PL/SQL procedure successfully completed.EODA@PROD1> EODA@PROD1> create table t_heap  --创建普通堆表  2  as  3  select *  4  from t_hashed  5  /Table created.EODA@PROD1> EODA@PROD1> alter table t_heap add constraint  2    t_heap_pk primary key(object_id)  3  /Table altered.EODA@PROD1> EODA@PROD1> begin  2  dbms_stats.gather_table_stats( user, 'T_HEAP' );    3  end;  4  /PL/SQL procedure successfully completed.EODA@PROD1> EODA@PROD1> create or replace package state_pkg  2  as  3    type array is table of t_hashed.object_id%type;  4    g_data array;  5  end;  6  /Package created.EODA@PROD1> EODA@PROD1> begin          --定义并生成随机数据  2    select object_id bulk collect into state_pkg.g_data  3        from t_hashed  4       order by dbms_random.random;  5  end;  6  /PL/SQL procedure successfully completed.EODA@PROD1> EODA@PROD1> exec runStats_pkg.rs_start;PL/SQL procedure successfully completed.EODA@PROD1> declare             --分别填入数据  2   l_rec t_hashed%rowtype;  3  begin  4   for i in 1 .. state_pkg.g_data.count  5   loop  6       select * into l_rec from t_hashed  7       where object_id = state_pkg.g_data(i);  8   end loop;  9  end; 10  /PL/SQL procedure successfully completed.EODA@PROD1> exec runStats_pkg.rs_middle;PL/SQL procedure successfully completed.EODA@PROD1> declare  2   l_rec t_heap%rowtype;  3  begin  4   for i in 1 .. state_pkg.g_data.count  5   loop  6       select * into l_rec from t_heap  7       where object_id = state_pkg.g_data(i);  8   end loop;  9  end; 10  /PL/SQL procedure successfully completed.EODA@PROD1> exec runStats_pkg.rs_stop(1000);Run1 ran in 133 cpu hsecsRun2 ran in 136 cpu hsecsrun 1 ran in 97.79% of the timeName  Run1  Run2  DiffLATCH.simulator hash latch 4,387 8,923 4,536STAT...file io wait time 8,377     0-8,377STAT...cell physical IO interc16,384     0       -16,384STAT...physical read total byt16,384     0       -16,384STAT...physical read bytes16,384     0       -16,384LATCH.cache buffers chains       146,193       218,90572,712STAT...Cached Commit SCN refer72,831     0       -72,831STAT...table fetch by rowid    2572,89072,865STAT...cluster key scan block72,880     1       -72,879STAT...cluster key scans72,880     1       -72,879STAT...rows fetched via callba     072,88072,880STAT...index fetch by key     072,88172,881STAT...no work - consistent re72,942    11       -72,931STAT...consistent gets from ca72,950    19       -72,931STAT...session logical reads73,041       218,711       145,670STAT...consistent gets from ca72,983       218,662       145,679STAT...consistent gets72,983       218,662       145,679STAT...buffer is not pinned co72,967       218,662       145,695STAT...consistent gets - exami    31       218,642       218,611STAT...logical read bytes from   598,351,872 1,791,680,512 1,193,328,640Run1 latches total versus runs -- difference and pctRun1   Run2      Diff Pct224,713   301,509     76,79674.53%PL/SQL procedure successfully completed.--虽然使用的时间几乎一样，但是散列簇表在缓冲区缓存链闩大幅减少。这说明在一个读密集型环境中，散列实现应该具有更好的扩展性。--因为他需要的串行化资源更少，下面tkprof更能说明EODA@PROD1> pauseEODA@PROD1> EODA@PROD1> exec dbms_monitor.session_trace_enable;PL/SQL procedure successfully completed.EODA@PROD1> declare  2   l_rec t_hashed%rowtype;  3  begin  4   for i in 1 .. state_pkg.g_data.count  5   loop  6       select * into l_rec from t_hashed  7       where object_id = state_pkg.g_data(i);  8   end loop;  9  end; 10  /PL/SQL procedure successfully completed.EODA@PROD1> declare  2   l_rec t_heap%rowtype;  3  begin  4   for i in 1 .. state_pkg.g_data.count  5   loop  6       select * into l_rec from t_heap  7       where object_id = state_pkg.g_data(i);  8   end loop;  9  end; 10  /PL/SQL procedure successfully completed.EODA@PROD1> exec dbms_monitor.session_trace_disable;PL/SQL procedure successfully completed.--查看trace文件SQL ID: 6wyqvnr7mkbrv Plan Hash: 1450564094SELECT *FROM T_HASHED WHERE OBJECT_ID = :B1call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute  72880      0.44       1.07          0          2          0           0Fetch    72880      0.48       0.66          0      72880          0       72880------- ------  -------- ---------- ---------- ---------- ----------  ----------total   145761      0.92       1.73          0      72882          0       72880Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 97     (recursive depth: 1)Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max)  Row Source Operation---------- ---------- ----------  ---------------------------------------------------         1          1          1  TABLE ACCESS HASH T_HASHED (cr=1 pr=0 pw=0 time=16 us)********************************************************************************SQL ID: b6syrq5gcw169 Plan Hash: 2815550882SELECT *FROM T_HEAP WHERE OBJECT_ID = :B1call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute  72880      0.44       1.01          0          0          0           0Fetch    72880      0.53       0.79          0     218640          0       72880------- ------  -------- ---------- ---------- ---------- ----------  ----------total   145761      0.97       1.80          0     218640          0       72880Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 97     (recursive depth: 1)Number of plan statistics captured: 1Rows (1st) Rows (avg) Rows (max)  Row Source Operation---------- ---------- ----------  ---------------------------------------------------         1          1          1  TABLE ACCESS BY INDEX ROWID T_HEAP (cr=3 pr=0 pw=0 time=59 us cost=2 size=97 card=1)         1          1          1   INDEX UNIQUE SCAN T_HEAP_PK (cr=2 pr=0 pw=0 time=50 us cost=1 size=0 card=1)(object id 85907)********************************************************************************--T_HASHED只需要直接读取，T_HEAP需要先通过索引再访问行，可以看到实现需要的I/O达到了三倍。/* 总结如果I/O资源有限，且所执行的查询要根据键做大量读操作，此时散列聚簇就能提高性能。如果CPU资源有限，再采用散列聚簇可能会降低性能，因为他需要更多CPU来执行散列。如果所使用的CPU时间中存在很多缓冲区缓存链闩自旋，那么则会显著减低CPU需求 */
--参考来源《Oracle编程艺术深入理解数据库体系结构（第三版）》
0 0