AnySQL.net

    当我们进行索引的联机维护(重命名, 改结构, 或删除)时, 会让现有的SQL走了错误的路径, 因为在现有的索引的HINT中一般都直接指明了索引的确切名字, 为了让SQL走新的索引, 你可能不得不去更改SQL中的HINT, 或用Outline来实现, 这样其实并不方便. 另外对于开发人员来说, 他们或许不知道那个索引更好, 但他们一般知道这个表上那个列的可选择性(Selectivity)好一些. 如果有方法能让Oracle的优化器选择某些列或以某些列前导的索引, 对DBA及开发人员都将十分有利. 一年多以前我就想让Oracle加一个INDEX_COL的HINT, 在用时我们只需要指定"index_col(表名, 列, ...)"就行了.

    为此我在Google讨论组上发了这个点子, 有人提示我Oracle已经有一个没有公开的HINT来实现这个功能了, 是Jonathan Lewis在10g版本中发现的, 对于这个用法, 我个人十分喜欢, 想不出大师是如何发现这个的? Oracle有内鬼和他相通?

    下面是这个HINT的一个用法, 将让Oracle选择以COL1或COL1+COL2列开头的索引(需要做实验验证一下), 但不会选择以COL2列开始的索引, 这一点要注意了, 不过不同的版本中, Oracle会有所更改. 不知道这个功能在Oracle 11g中有没有作出改进?

/*+ index(table_alias tablename(col1, col2)) */

    现在Oracle宣称11g Beta出来已经有不少天数了, 不知道有没有朋友得到Beta版本的, 有的话能帮我看一下这个HINT是不是已经公开了, 还是仍然是一个非公开的HINT, 还是加了一个INDEX_COL的HINT, 如果已经公开, 我们绝对会使用!

    几天前接到一个任务在现在的表上建一个索引, 发现这个表的现在有一个主键索引是建在(A,B)这两列上的, 而要建的索引是在(A,B,C)列上的, 主要目标是要让一个执行频率比较高的SQL不去访问表, 以减少逻辑读, 提升性能. 这时你会如何去考虑?

    实际在增加约束时是可以指定约束使用那个索引的, 这个主键可以使用新建的这个三个列的索引, 从儿可以删除掉原来的建在两个列上的索引, 以节约空间. 看如下的演示:

SQL> CREATE TABLE T_OBJECTS AS
  2  SELECT * FROM ALL_OBJECTS WHERE ROWNUM < 1000;

Table created.

SQL> CREATE INDEX T_OBJECTS_IDX
  2  ON T_OBJECTS (OBJECT_ID, OBJECT_NAME);

Index created.

SQL> ALTER TABLE T_OBJECTS ADD CONSTRAINT PK_T_OBJECTS
  2  PRIMARY KEY (OBJECT_ID) USING INDEX T_OBJECTS_IDX;

Table altered.

SQL> SELECT INDEX_NAME FROM USER_INDEXES
  2  WHERE TABLE_NAME='T_OBJECTS';

INDEX_NAME
------------------------------
T_OBJECTS_IDX

    在真实的环境中应用时, 需要考虑是否真的要这样做, 要看这个节约下来的存贮是否有意义, 因为索引加了一列变大了, 可能会使层次变高, 从而改变了一些现有的SQL的执行成本. 另外的坏处是如果约束被禁用了, 那么可能会有重复记录进入到表, 这是另外需要考虑的问题.

    在Pro*C中可以为SQL中的绑定变量传入一个数组, 进行批量处理, 减少User Call和Execute Count, 尤其是在客户端服务器的情况下, 这个性能提升十份明显. 在Java中也有类似的executeBatch调用, 最新版本的Perl中也可以用数组来绑定一个变量. Oracle在9i的PL/SQL中正式支持了批量操作, 下面是一个批量删除的例子:

DECLARE
  TYPE ARRROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
  tbrows ARRROWID;
  row    PLS_INTEGER;
  cursor delete_table is select row_id from testobj_rowid;
BEGIN
  open delete_table;
  loop
     fetch delete_table bulk collect into tbrows limit 250;
     FORALL row IN 1 .. tbrows.count()
       DELETE TEST.TESTOBJ WHERE rowid = tbrows(row);
     commit;
     -- dbms_lock.sleep(1);
     exit when delete_table%notfound;
  end loop;
  close delete_table;
END;
/

    致于批量的插入或更新角本, 照着这个改改应当很容易出来的. 写在这儿, 也是为了方便我自已抄写.

    从8i到9i再到10g, Oracle总是让更多的参数可以动态修改, 以减少停机的时间, 这是很有必要的. 通过比对9i和10g的参数列表, 我列出了下面一些认为比较常见的参数, 以供参考:

    和内存有关的参数:

sga_target
java_pool_size

    和SQL优化器(Optimizer)有关的参数:

cpu_count
optimizer_index_caching
optimizer_index_cost_adj
optimizer_mode
optimizer_secure_view_merging
star_transformation_enabled

    和并行执行(Parallel Execution)有关的:

parallel_max_servers
parallel_min_servers
parallel_threads_per_cpu

    其他参数:

resumable_timeout
create_stored_outlines
skip_unusable_indexes
sql_trace

    我个人觉得SQL_TRACE能在System级别修改是很危险的, 已经有DBA不小心在这个参数上范过错了, 其他的改进都很好.

    Oracle的AQ可用于消息处理, 可有一个或几个消息产生者将不同的消息插入到AQ表中, 然后由多个进程来获取并处理这些消息, 虽然这儿的解释很简单, 但要实现一个可靠具有事务功能的消息处理系统还是件不容易的事情. 在这儿我要讲的是当有多个消息处理进程时, Oracle是如何让他们能最快地并行地处理的?

    这个问题可以简化为, 在一个表中有很多的记录(消息), 然后有很多进程来将这个表中的记录取出来, 进行处理后, 并在这个表中删除已经处理过的记录, 如果只有一两个这样的进程时, 可能什么事情也看不出来, 但是当有很多同样的进程来并行地处理这些记录时, 就会遇到数据库中常会遇到的Enqueue等待.

    按照一般的处理方法可能会使用如下的代码:

declare
   cursor msg is select ... from message_table
       where rownum < n and .....;
begin
   open msg;
   loop
      fetch msg into ....;
      process_message;
      delete message_table where pk=msg.pk;
      exit when msg%notfound;
   end loop;
   close msg;
exception
   when others then
        rollback;
end;
/

    当有很多进程并行运行这段代码时, 就有可能会出现很严重的Enqueue Wait, 在Oracle AQ的Dequeue过程中,使用了Skip Locked这样的关键字, 以跳过别其他进程锁住的记录, 更改后的代码如下:

declare
   cursor msg is select ... from message_table
       where rownum < n and ..... for update skip locked;
begin
   open msg;
   loop
      fetch msg into ....;
      process_message;
      delete message_table where pk=msg.pk;
      exit when msg%notfound;
   end loop;
   close msg;
exception
   when others then
        rollback;
end;
/

    我是在研究AQ时顺便用SQL_TRACE去跟踪了一把才发现的, 一转眼已经是两年多以前的事了.