ITPub博客

首页 > Linux操作系统 > Linux操作系统 > HASHKEYS和SIZE参数对HASH SORT CLUSTER表的影响

HASHKEYS和SIZE参数对HASH SORT CLUSTER表的影响

原创 Linux操作系统 作者:yangtingkun 时间:2009-07-30 23:39:38 0 删除 编辑

对于HASH CLUSTER表而言,HASHKEYSSIZE是最重要的存储参数设置,而对于HASH SORT CLUSTER表也是一样的。

 

 

对于一个正常设置参数的HASH SORT CLUSTER表:

SQL> CREATE CLUSTER C_HASH_SORT
  2  (ID NUMBER, CREATED DATE SORT)
  3  HASHKEYS 100000 SIZE 1125;

Cluster created.

SQL> CREATE TABLE T_HASH_SORT
  2  (ID NUMBER,
  3  OWNER VARCHAR2(30),
  4  OBJECT_NAME VARCHAR2(30),
  5  OBJECT_TYPE VARCHAR2(30),
  6  CREATED DATE SORT)
  7  CLUSTER C_HASH_SORT (ID, CREATED);

Table created.

SQL> SET TIMING ON
SQL> INSERT INTO T_HASH_SORT
  2  SELECT *
  3  FROM
  4  (
  5  SELECT MOD(ROWNUM, 100000) ID,
  6  A.OWNER,
  7  OBJECT_NAME,
  8  OBJECT_TYPE,
  9  A.CREATED
 10  FROM DBA_OBJECTS A, DBA_DB_LINKS
 11  )
 12  ORDER BY ID, CREATED;

2477265 rows created.

Elapsed: 00:00:35.32
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.00
SQL> SET AUTOT ON
SQL> SELECT *
  2  FROM
  3  (
  4     SELECT ROWNUM RN, A.*
  5     FROM
  6     (
  7             SELECT ID, OWNER, OBJECT_TYPE, CREATED
  8             FROM T_HASH_SORT
  9             WHERE ID = 11232
 10             ORDER BY CREATED
 11     ) A
 12     WHERE ROWNUM <= 20
 13  )
 14  WHERE RN > 10;

        RN         ID OWNER                OBJECT_TYPE                    CREATED
---------- ---------- -------------------- ------------------------------ --------------
        11      11232 SYS                  JAVA CLASS                     11-6
-08
        12      11232 SYS                  JAVA CLASS                     11-6
-08
        13      11232 PUBLIC               SYNONYM                        11-6
-08
        14      11232 PUBLIC               SYNONYM                        11-6
-08
        15      11232 CTXSYS               TABLE                          11-6
-08
        16      11232 ORDSYS               PROCEDURE                      11-6
-08
        17      11232 MDSYS                TYPE                           11-6
-08
        18      11232 SYS                  JAVA CLASS                     11-6
-08
        19      11232 SYSMAN               TRIGGER                        11-6
-08
        20      11232 JIANGSU15            INDEX                          12-6
-08

10 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 156907859

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    20 |  1380 |     5 (100)| 00:00:01 |
|*  1 |  VIEW                |             |    20 |  1380 |     5 (100)| 00:00:01 |
|*  2 |   COUNT STOPKEY      |             |       |       |            |          |
|   3 |    VIEW              |             |  2293 |   125K|     5 (100)| 00:00:01 |
|*  4 |     TABLE ACCESS HASH| T_HASH_SORT |  2293 |   125K|            |          |
------------------------------------------------------------------------------------

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

   1 - filter("RN">10)
   2 - filter(ROWNUM<=20)
   4 - access("ID"=11232)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
       1087  bytes sent via SQL*Net to client
        491  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

如果参数设置合理,无论是批量加载数据,还是查询获取数据性能都会相对高效。需要注意的是,HASHKEYS计算的结果并不包含SORT列,而是CLUSTER中除去SORT列以外的列的键值数,而SIZE对应的是每个HASHKEYS的所有记录的和。

在上面的例子中,ID列中不相同的键值的列数就是HASHKEYS的值,这里是100000。而每个ID对应将近25条记录,每条记录的平均长度是45,因此得到SIZE1125

而如果认为HASH SORT CLUSTER中的所有列的不同值来计算HASHKEYS的大小,就会错误的设置存储参数:

SQL> DROP CLUSTER C_HASH_SORT INCLUDING TABLES;

Cluster dropped.

Elapsed: 00:00:00.11
SQL> SELECT COUNT(*)
  2  FROM 
  3  (
  4  SELECT DISTINCT ID, CREATED
  5  FROM
  6  (
  7  SELECT MOD(ROWNUM, 100000) ID,
  8  A.OWNER,
  9  OBJECT_NAME,
 10  OBJECT_TYPE,
 11  A.CREATED
 12  FROM DBA_OBJECTS A, DBA_DB_LINKS
 13  )
 14  );

  COUNT(*)
----------
   2477059

Elapsed: 00:00:03.54

SQL> CREATE CLUSTER C_HASH_SORT
  2  (ID NUMBER, CREATED DATE SORT)
  3  HASHKEYS 2477059 SIZE 70;

Cluster created.

Elapsed: 00:00:01.77
SQL> CREATE TABLE T_HASH_SORT
  2  (ID NUMBER,
  3  OWNER VARCHAR2(30),
  4  OBJECT_NAME VARCHAR2(30),
  5  OBJECT_TYPE VARCHAR2(30),
  6  CREATED DATE SORT)
  7  CLUSTER C_HASH_SORT (ID, CREATED);

Table created.

Elapsed: 00:00:00.01
SQL> INSERT INTO T_HASH_SORT
  2  SELECT *
  3  FROM
  4  (
  5  SELECT MOD(ROWNUM, 100000) ID,
  6  A.OWNER,
  7  OBJECT_NAME,
  8  OBJECT_TYPE,
  9  A.CREATED
 10  FROM DBA_OBJECTS A, DBA_DB_LINKS
 11  )
 12  ORDER BY ID, CREATED;

2477265 rows created.

Elapsed: 00:00:41.71
SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.00
SQL> SET AUTOT ON
SQL> SELECT *
  2  FROM
  3  (
  4     SELECT ROWNUM RN, A.*
  5     FROM
  6     (
  7             SELECT ID, OWNER, OBJECT_TYPE, CREATED
  8             FROM T_NORMAL
  9             WHERE ID = 11232
 10             ORDER BY CREATED
 11     ) A
 12     WHERE ROWNUM <= 20
 13  )
 14  WHERE RN > 10;

        RN         ID OWNER                OBJECT_TYPE                    CREATED
---------- ---------- -------------------- ------------------------------ --------------
        11      11232 SYS                  JAVA CLASS                     11-6
-08
        12      11232 SYS                  JAVA CLASS                     11-6
-08
        13      11232 PUBLIC               SYNONYM                        11-6
-08
        14      11232 SYS                  JAVA CLASS                     11-6
-08
        15      11232 CTXSYS               TABLE                          11-6
-08
        16      11232 ORDSYS               JAVA RESOURCE                  11-6
-08
        17      11232 MDSYS                PACKAGE BODY                   11-6
-08
        18      11232 PUBLIC               SYNONYM                        11-6
-08
        19      11232 PUBLIC               SYNONYM                        11-6
-08
        20      11232 JIANGSU15            INDEX                          12-6
-08

10 rows selected.

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1590327436

------------------------------------------------------------------------------------------
| Id | Operation                      | Name         |Rows |Bytes| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |              |   20| 1380|     4   (0)| 00:00:01 |
|* 1 |  VIEW                          |              |   20| 1380|     4   (0)| 00:00:01 |
|* 2 |   COUNT STOPKEY                |              |     |     |            |          |
|  3 |    VIEW                        |              |   21| 1176|     4   (0)| 00:00:01 |
|  4 |     TABLE ACCESS BY INDEX ROWID| T_NORMAL     |   21|  609|     4   (0)| 00:00:01 |
|* 5 |      INDEX RANGE SCAN          | IND_T_NORMAL |   25|     |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - filter("RN">10)
   2 - filter(ROWNUM<=20)
   5 - access("ID"=11232)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
       1099  bytes sent via SQL*Net to client
        491  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

可以看到,如果错误的设置HASHKEYSSIZE参数,无论是批量插入所虚时间,还是查询语句所需的IO数,都要超过正常设置的值。

而且设置太大的HASHKEYS,会导致Oracle预分配大量的空间,造成空间的浪费;而设置HASHKEYS太小,就可能出现HASH冲突,从而影响性能。因此对于HASH类型的CLUSTER,设置HASHKEYSSIZE参数,应该经过仔细的设计和计算。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-610912/,如需转载,请注明出处,否则将追究法律责任。

下一篇: ORA-7445(kdodpm)错误
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10405780