AnySQL.net

    前面遇到一个排序的问题后, 昨天在数据库中做一了下调优, 将数据库的内存排序的次数从每秒的550次减少到330次左右, 并临控数据库的负荷, 很失望地发现对于降底数据库的负荷并没有什么作用, 甚至于还变高了一些, 由于没有发现明显的效果, 因此马上取消了所有的调优操作, 返回到原来的状态.

    今天早上一来, 就做了以下测试, 首先是不需要排序时的情况:

ASQL> @TEST.SQL
ASQL> BEGIN /* Test no sorting */
    2  FOR I IN 1..10000 LOOP
    3      FOR REC IN (SELECT * FROM TEST_SORT WHERE COL2=10 AND PKEY=0
    4        AND COL1 IN (810,510,210) ORDER BY COL1) LOOP
    5        NULL;
    6      END LOOP;
    7  END LOOP;
    8 END;
    9 /

Procedure executed.
Execute time: 00:00:05.479

Statistics
---------------------------------------------------
        445  CPU used by this session
        120K  consistent gets
        10K  execute count
          1  sorts (memory)

ASQL> /

Procedure executed.
Execute time: 00:00:05.438

Statistics
---------------------------------------------------
        442  CPU used by this session
        120K  consistent gets
        10K  execute count
          1  sorts (memory)

    接下来是需要排序时的情况:

ASQL> DECLARE /* Test with sorting */
    2  P_KEY NUMBER := 0;
    3 BEGIN
    4  FOR I IN 1..10000 LOOP
    5      FOR REC IN (SELECT * FROM TEST_SORT WHERE COL2=10 AND PKEY=P_KEY
    6          AND COL1 IN (810,510,210) ORDER BY COL1) LOOP
    7        NULL;
    8      END LOOP;
    9  END LOOP;
  10 END;
  11 /

Procedure executed.
Execute time: 00:00:06.370

Statistics
---------------------------------------------------
        484  CPU used by this session
        90K  consistent gets
        10K  execute count
        10K  sorts (memory)

ASQL> /

Procedure executed.
Execute time: 00:00:05.739

Statistics
---------------------------------------------------
        463  CPU used by this session
        90K  consistent gets
        10K  execute count
        10K  sorts (memory)

    可以看到有和没有排序时的CPU消耗相差不多, 虽然最后结果是有排序时的CPU多一点, 但没有排序时的逻辑读却多了30K(在这儿我用Array Size 200了). 看来Oracle中逻辑读的成本不底, 和Trace中的40%左右的CPU时间增长很不成比例啊. 看来还是没有搞明白.