AnySQL.net

    大量的排序操作会占用大量的CPU资源, 即便是内存中的排序. 通常我们可以用索引来 避免排, 但在分区表上, 就有些区别了. 下面是我做的一个测试例子, 你会知道在分区列上使用绑定变量 时会引起不必要的排序. 虽然在这个例子中用不用绑定变量都很明显地只访问一个分区, 但是Oracle还是 在用绑定变量时进行了不必要的排序. 因此Oracle有时是笨得可爱.

    请使用下面的角本来创建测试表:

CREATE TABLE TEST_SORT
(
  COL1 NUMBER,
  COL2 NUMBER,
  pkey number,
  col3 varchar2(30)
)
partition by range(pkey)
(
  partition p0 values less than (1),
  partition p1 values less than (2),
  partition p2 values less than (3)
) tablespace data02;

INSERT INTO TEST_SORT
  SELECT ROWNUM, MOD(ROWNUM,100), mod(rownum,3), OBJECT_NAME
    FROM DBA_OBJECTS WHERE ROWNUM < 5001;

commit;
CREATE INDEX IDX_TEST_SORT ON (COL1,COL2,PKEY) LOCAL;

    就前一篇中的例子, 来作一下10046的跟踪, 看看排序倒底占据了多少的CPU. 我一直是认为就算几条记录的排序也是有代价的, 至少它需要去准备一块排序区, 并运行排序的算法. 有时数据库系统中每秒种会有上千次的内存排序, 这时如果将排序的次数减少到一半, 机器的负荷会下降多少呢? 对于排序没有直接的印象, 不象对于逻辑读那么熟悉. 就等着一步一步研究吧?

    先来看一下分区列上用绑定变量(有排序)时的情况:

SELECT * FROM TEST_SORT
  WHERE COL2=:p_co1 AND PKEY=:p_key AND COL1 IN (810,510,210)
  ORDER BY COL1

Rows    Row Source Operation
-------  ---------------------------------------------------
      3  SORT ORDER BY (cr=9 r=0 w=0 time=390 us)
      3  PARTITION ... (cr=9 r=0 w=0 time=246 us)
      3    INLIST ITERATOR  (cr=9 r=0 w=0 time=240 us)
      3    TABLE ACCESS BY ... (cr=9 r=0 w=0 time=205 us)
      3      INDEX RANGE SCAN ... (cr=6 r=0 w=0 time=141 us)

    当使用9205以前版本的exp程序去9205及以上的数据库中去导出带LOB字段的表时, 会遇到一个错误, 错误信息为"EXP-00003 : 没找到段的存贮定义 .....", 事实上这是一个Oracle的Bug, 可以通过监时地更改视图"exu9tne"的定义来临时解决问题, 如下所示:

    在导出前, 连接到SYS用户, 运行以下SQL:

CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
UNION ALL
SELECT * FROM SYS.EXU9TNEB
/

    导出完成后, 运行以下命令来还原视图的定义, 下面贴的是Oracle 9用的, 10g的还是请访问Metalink来确定, 或者在运行前一个命令之前, 从USER_VIEWS中将原视图的定义查出来, 这样做也是DBA一个很好的习惯.

CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
/

    这是一个在Oracle 9i上的Bug, 问题发现于数据库从8i升级到9i时, 发现占用了大量的临时段. 与其说是Bug, 更不如说Oracle故意如此, 因为Oracle说这不是Bug, 而是其设计上如此. 我们在一个Session中做实验(9i)就可以了, 你可以想象一下如果你有几百个会话都在调用有临时LOB对象的角本, 而TEMP表空间的UNIFORM SIZE设得又比较大时, 总共需要多少临时空间?.

ASQL> declare
    2  a clob;
    3 begin
    4  a := 'abc';
    5 end;
    6 /

Procedure executed.

ASQL> ora sort

SID USERNAME BLOCKS TABLESPACE SEGTYPE 
--- -------- ------ ---------- --------
547 ANYSQL      128 TEMP01    LOB_DATA
 
1 rows returned.

    在Oracle 8i中试验一下:

    我们先来搞一个测试, 将其中的一个列设为UNUSED:

SQL> DESC T_FID
Name                    Null?    Type
----------------------- -------- -------
COL1                            NUMBER
COL2                            NUMBER
COL3                            NUMBER
SQL> ALTER TABLE T_FID SET UNUSED (COL3);
Table altered.
SQL> alter system checkpoint;
System altered.

    然后我们去查COL$表中的三列(COL#,SEGCOL#,NAME), 看看值是什么? 在这儿为了方便, 我就用AUL/MyDUL的DESC命令去看了

AUL> desc alex.t_fid
Storage(OBJ#=7641 OBJD=7641 TS=2 FILE=3 BLOCK=1529 CLUSTER=0)
No. SEQ Column Name                  Type
--- --- ----------------------------- ----------------
  1  1 COL1                          NUMBER
  2  2 COL2                          NUMBER
  0  3 SYS_C00004_05092817:31:11$    NUMBER

AUL> UNLOAD TABLE alex.t_fid;
2005-09-28 17:32:40
Unload OBJD=7641 FILE=3 BLOCK=1529 CLUSTER=0 ...
9,8,7
2005-09-28 17:32:40

    从这儿可以看到, SET UNUSED时将一个列的名字换一下, 此外还将这个列的显示顺序的值设为0, 原来为3. 虽然Oracle不提供反向操作,但实际上原来的数据还存在, 不提供的原因可能是因为不能保证not null和其他的约束吧.