Chinaunix首页 | 论坛 | 博客
  • 博客访问: 12220
  • 博文数量: 10
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 110
  • 用 户 组: 普通用户
  • 注册时间: 2014-12-23 15:56
文章分类

全部博文(10)

文章存档

2014年(10)

我的朋友

分类: Oracle

2014-12-24 10:00:37


  1. --聚合因子试验准备:


  2. --分别建两张有序和无序的表

  3. CREATE TABLE T_COLOCATED ( ID NUMBER, COL2 VARCHAR2(100) );
  4. BEGIN
  5.         FOR I IN 1 .. 100000
  6.         LOOP
  7.             INSERT INTO T_COLOCATED(ID,COL2)
  8.             VALUES (I, RPAD(DBMS_RANDOM.RANDOM,95,'*') );
  9.         END LOOP;
  10. END;
  11. /

  12. ALTER TABLE T_COLOCATED ADD CONSTRAINT PK_T_COLOCATED PRIMARY KEY(ID);

  13. CREATE TABLE T_DISORGANIZED
  14.      AS
  15.     SELECT ID,COL2
  16.     FROM T_COLOCATED
  17.     ORDER BY COL2;

  18. ALTER TABLE T_DISORGANIZED ADD CONSTRAINT PK_T_DISORG PRIMARY KEY (ID);


  19. --分别分析两张表的聚合因子层度

  20.                                                          
  21. SELECT INDEX_NAME,
  22.               BLEVEL,
  23.               LEAF_BLOCKS,
  24.               NUM_ROWS,
  25.               DISTINCT_KEYS,
  26.               CLUSTERING_FACTOR
  27.          FROM USER_IND_STATISTICS
  28.         WHERE TABLE_NAME IN( 'T_COLOCATED','T_DISORGANIZED');


  29. INDEX_NAME                         BLEVEL LEAF_BLOCKS   NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
    ------------------------------ ---------- ----------- ---------- ------------- -----------------
    PK_T_COLOCATED                          1         208     100000        100000              1469
    PK_T_DISORG                             1         208     100000        100000             99935
  30. --首先观察有序表的查询性能




  31. 执行并比较性能差异
  32. SQL> select /*+index(t)*/ * from t_colocated t where id>=20000 and id<=40000;

    已选择20001行。

    已用时间:  00: 00: 02.73

    执行计划
    ----------------------------------------------------------
    Plan hash value: 4204525375

    ----------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                | 22727 |  1442K|   417   (0)| 00:00:06 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T_COLOCATED    | 22727 |  1442K|   417   (0)| 00:00:06 |
    |*  2 |   INDEX RANGE SCAN          | PK_T_COLOCATED | 22727 |       |    56   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       2 - access("ID">=20000 AND "ID"<=40000)

    Note
    -----
       - dynamic sampling used for this statement (level=2)


    统计信息
    ----------------------------------------------------------
              7  recursive calls
              0  db block gets
           3058  consistent gets
             37  physical reads
              0  redo size
        2368473  bytes sent via SQL*Net to client
          15182  bytes received via SQL*Net from client
           1335  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
          20001  rows processed


  33. 再观察无序表的查询性能
  34. select /*+index(t)*/ * from t_disorganized t where id>=20000 and id<=40000;

  35. 已用时间:  00: 00: 11.51

    执行计划
    ----------------------------------------------------------
    Plan hash value: 603192320

    ----------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                | 24048 |  1526K| 20069   (1)| 00:04:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T_DISORGANIZED | 24048 |  1526K| 20069   (1)| 00:04:01 |
    |*  2 |   INDEX RANGE SCAN          | PK_T_DISORG    | 24048 |       |    46   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       2 - access("ID">=20000 AND "ID"<=40000)

    Note
    -----
       - dynamic sampling used for this statement (level=2)


    统计信息
    ----------------------------------------------------------
            246  recursive calls
              0  db block gets
          21491  consistent gets
             43  physical reads
              0  redo size
        2368473  bytes sent via SQL*Net to client
          15182  bytes received via SQL*Net from client
           1335  SQL*Net roundtrips to/from client
              6  sorts (memory)
              0  sorts (disk)
          20001  rows processed


  36. --Oracle文档对聚簇因子的解释

  37. Indicates the amount of order of the rows in the table based on the values of the index.
  38. If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend to point to rows in the same data blocks.
  39. If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks.

阅读(314) | 评论(0) | 转发(0) |
0

上一篇:Oracle SQL Trace简介

下一篇:没有了

给主人留下些什么吧!~~