AnySQL.net

    开发人员经常想用一个长长的In List来作为Where条件, 但这样的语句如果查执行计划不对的话, 很容易造成共享池(Shared Pool)内存问题, 下面是一个拥有40个值的In List的测试. 占用的内存如下所示:

SQL> SELECT HASH_VALUE,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM
  2  FROM V$SQL WHERE HASH_VALUE IN (1981294536 ,3511787793);

HASH_VALUE SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM
---------- ------------ -------------- -----------
1981294536        31836           1088        5336
3511787793       171534           1088      127936

    其中1981294536的计划中用的是INLIST ITERATOR方法, 3511787793语句用的是CONCATENATION方法. 源语句如下:

SQL> SELECT HASH_VALUE,SQL_TEXT FROM V$SQL
  2  WHERE HASH_VALUE IN (1981294536 ,3511787793);

HASH_VALUE SQL_TEXT
---------- ----------------------------------------
1981294536 SELECT /* INTEST1_TEST */ /*+ ALL_ROWS *
           /   OBJECT_ID, OBJECT_NAME FROM T_OBJECT
           S WHERE OBJECT_ID IN (   1,2,3,4,5,6,7,8
           ,9,10,11,12,13,14,15,16,17,18,19,20,   2
           1,22,23,24,25,26,27,28,29,30,31,32,33,34
           ,35,36,37,38,39,40)

3511787793 SELECT /* INTEST2_TEST */ /*+ USE_CONCAT
            */   OBJECT_ID, OBJECT_NAME FROM T_OBJE
           CTS WHERE OBJECT_ID IN (   1,2,3,4,5,6,7
           ,8,9,10,11,12,13,14,15,16,17,18,19,20,
            21,22,23,24,25,26,27,28,29,30,31,32,33,
           34,35,36,37,38,39,40)

    如果DBA发现了这样的情况, 应当建议开发人员减少In List的长度, 分为多次执行, 或者确保SQL语句的执行计划是用了INLIST ITERATOR方法.

    早上以为发现了一个10g的Bug, 大家看一下, 我重建过程P_DEMO后, 它的Last DDL Time没有变化:

SQL> SELECT OBJECT_NAME, LAST_DDL_TIME FROM USER_OBJECTS
  2  WHERE OBJECT_NAME='P_DEMO';

OBJECT_NAME          LAST_DDL_TIME
-------------------- -------------------
P_DEMO               2007-03-28 07:40:43

SQL> CREATE OR REPLACE procedure p_demo as
  2  begin
  3    null;
  4  end;
  5  /

Procedure created.

SQL> SELECT OBJECT_NAME, LAST_DDL_TIME FROM USER_OBJECTS
  2  WHERE OBJECT_NAME='P_DEMO';

OBJECT_NAME          LAST_DDL_TIME
-------------------- -------------------
P_DEMO               2007-03-28 07:40:43

    在修改OCIDIFF程序时, 发现不断地同步同一个Trigger, 以为是遇到Bug, 到9i上测了一把则是变化的, 后来我改了一下这个过程的代码, 再跑时, 发现Last DDL Time就变了:

SQL> CREATE OR REPLACE procedure p_demo as
  2  begin
  3    null;
  4    null;
  5  end;
  6  /

SQL> SELECT OBJECT_NAME, LAST_DDL_TIME FROM USER_OBJECTS
  2  WHERE OBJECT_NAME='P_DEMO';

OBJECT_NAME          LAST_DDL_TIME
-------------------- -------------------
P_DEMO               2007-03-28 16:38:03

    不知道这一点小变化, 有什么意义? 但重编译过程时, 则这个时间又是变化的.

    下面的测试是基于Kernel Internal Trigger的实体化视图日志(Primary Key+Sequence):

SQL> INSERT INTO T_OBJECTS SELECT * FROM ALL_OBJECTS WHERE ROWNUM < 10001;

9424 rows created.

Elapsed: 00:00:01.38

Statistics
----------------------------------------------------------
      11249  recursive calls
      41052  db block gets
      16575  consistent gets
         15  physical reads
    8266284  redo size
        674  bytes sent via SQL*Net to client
        606  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
       9424  rows processed

    下面的测试是基于Kernel Internal Trigger的实体化视图日志(Primary Key):

SQL> INSERT INTO T_OBJECTS SELECT * FROM ALL_OBJECTS WHERE ROWNUM < 10001;

9425 rows created.

Elapsed: 00:00:00.86

Statistics
----------------------------------------------------------
      10855  recursive calls
      40835  db block gets
      16383  consistent gets
          9  physical reads
    8181892  redo size
        679  bytes sent via SQL*Net to client
        606  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
       9425  rows processed

    下面的测试是基于自定义Trigger的实体化视图日志(Primary Key):

SQL> INSERT INTO T_OBJECTS SELECT * FROM ALL_OBJECTS WHERE ROWNUM < 10001;

9424 rows created.

Elapsed: 00:00:00.90

Statistics
----------------------------------------------------------
      10372  recursive calls
      40274  db block gets
      16144  consistent gets
          5  physical reads
    7997420  redo size
        678  bytes sent via SQL*Net to client
        606  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
       9424  rows processed

    下面这一段是用于生成实体第视图日志的自定义Trigger:

CREATE OR REPLACE TRIGGER TLOG$_T_OBJECTS
BEFORE INSERT OR UPDATE OR DELETE ON T_OBJECTS
FOR EACH ROW
BEGIN
  IF INSERTING THEN
     INSERT INTO MLOG_T_OBJECTS (OBJECT_ID, DMLTYPE$$) VALUES (:NEW.OBJECT_ID,'I');
  ELSIF UPDATING THEN
     IF (:OLD.OBJECT_ID = :NEW.OBJECT_ID) THEN
       INSERT INTO MLOG_T_OBJECTS (OBJECT_ID, DMLTYPE$$) VALUES (:NEW.OBJECT_ID,'U');
     ELSE
       INSERT INTO MLOG_T_OBJECTS (OBJECT_ID, DMLTYPE$$) VALUES (:NEW.OBJECT_ID,'D');
       INSERT INTO MLOG_T_OBJECTS (OBJECT_ID, DMLTYPE$$) VALUES (:NEW.OBJECT_ID,'I');
     END IF;
  ELSE
     INSERT INTO MLOG_T_OBJECTS (OBJECT_ID, DMLTYPE$$) VALUES (:NEW.OBJECT_ID,'D');
  END IF;
END;
/

    在10g的版本下, 居然看不出什么差别, 看来PL/SQL的执行效率的确是很高了.

    最近几个人都写Blog来关注Single Table Cluster, 我也来重复一下, 要说明的是可以在Hash表上加普通的索引, 以支持按范围的访问, 演示的角本如下:

CREATE CLUSTER HASH_C_DEMO (OBJECT_ID NUMBER)
SIZE 50 SINGLE TABLE HASHKEYS 1000;
CREATE TABLE T_C_DEMO (OBJECT_ID NUMBER, OBJECT_NAME VARCHAR2(30))
CLUSTER HASH_C_DEMO (OBJECT_ID);
CREATE INDEX IDX_T_C_DEMO ON T_C_DEMO (OBJECT_ID);
INSERT INTO T_C_DEMO SELECT OBJECT_ID, OBJECT_NAME FROM USER_OBJECTS;

    接下来测试一下等于查询的情况:

SQL> SELECT OBJECT_NAME FROM T_C_DEMO WHERE OBJECT_ID=621150;

OBJECT_NAME
------------------------------
P_DEMO

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (HASH) OF 'T_C_DEMO'

    接下来测试一下范围查询的情况:

SQL> SELECT OBJECT_NAME FROM T_C_DEMO WHERE OBJECT_ID BETWEEN 611851 AND 611853;

OBJECT_NAME
------------------------------
SYS_LOB0000609516C00004$$
T_IOT

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T_C_DEMO'
   2    1     INDEX (RANGE SCAN) OF 'IDX_T_C_DEMO' (NON-UNIQUE)

    关于Hash表SIZE参数和逻辑读的效率, 可以看玉面飞龙和汪海的文章, 我就不重复测了.

    Hash Cluster的表可以在没有索引的情况下, 获得对表的极快访问, 这种访问的逻辑读比维一性索引还有效. 在这儿有一张表T_OBJECTS, 其中的OBJECT_ID是主键, 大量的SQL语句都是根据OBJECT_ID去访问其他字段, 通过索引的情况下每次执行的逻辑读已经只有3了(Index Root->Leaf->Table), 但是就是这样一句简单的SQL的逻辑读占据了大半. 因此考虑到使用Single Hash Cluster表来进行调优. 应当如何来创建这个Cluster呢? 决定Cluster性能的主要有两个因素: SIZE和HASHKEYS.

    下面是我的思路, 首先分析一下现在的表, 获得比较准确的块数.

SQL> SELECT BLOCKS FROM USER_TABLES WHERE TABLE_NAME='T_OBJECTS';

    BLOCKS
----------
       118

    因为在这个情况下, 数据分布均匀, 因此我只要将HASHKEYS定义为块数就已经比较好了, 实际上为了更安全, 选择一个比较大的值, 如在这儿我选择144. 而SIZE就直接选择块的大小好了.

SQL> CREATE CLUSTER C_T_OBJECTS (OBJECT_ID NUMBER(38,0))
  2  SIZE 8192 SINGLE TABLE HASHKEYS 144;

Cluster created.

    接下来创建一个CT_OBJECTS的Cluster表, 并将T_OBJECTS表的记录填充进去. 在这个例子中, 我用下面代码去检查所有的行, 目标是要检测新的Cluster表的性能:

SQL> DECLARE
  2    TEMP NUMBER:=0;
  3  BEGIN
  4    FOR REC IN (SELECT OBJECT_ID FROM T_OBJECTS) LOOP
  5       SELECT DATA_OBJECT_ID INTO TEMP FROM CT_OBJECTS
  6              WHERE OBJECT_ID=REC.OBJECT_ID;
  7    END LOOP;
  8  END;
  9  /

    然后找到这个SQL语句的Hash Value, 从V$SQL视图中去检查一下执行的效率.

SQL> SELECT HASH_VALUE, BUFFER_GETS, EXECUTIONS
  2  FROM V$SQL WHERE HASH_VALUE=3267226907;

HASH_VALUE BUFFER_GETS EXECUTIONS
---------- ----------- ----------
3267226907        9464       9462

    可以看到这个SQL语句是很有效的, 这种技术可以推荐用在OLTP系统中.