带有自定义函数的sql优化

author：skate
time：2009/05/14

---

 

一. 第一个sql
 
 Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
     60,414,728          195      309,819.1   28.1   619.83  11991.55  905212684

 

优化前：

 

SELECT
TOM.ORDER_ID
  FROM TB_ORDER_MATCH TOM
  LEFT JOIN TB_ORDER TOD ON TOD.ID = TOM.ORDER_ID
 WHERE TOD.WARE_ID = 32135
   AND TOM.GAME_NO = 245
   AND TOD.BUY_QUOMODO = 1
   AND TOD.STATE = 0
   AND TOD.IS_ADVANCED = 'Y'
 ORDER BY TOM.ORDER_ID

 

优化后：

 SELECT /*+ index(tod,PIDX_ORDER_WARE_ID)*/
TOM.ORDER_ID
  FROM TB_ORDER_MATCH TOM
  LEFT JOIN TB_ORDER TOD ON TOD.ID = TOM.ORDER_ID
 WHERE TOD.WARE_ID = 32135
   AND TOM.GAME_NO = 245
   AND TOD.BUY_QUOMODO = 1
   AND TOD.STATE = 0
   AND TOD.IS_ADVANCED = 'Y'
 ORDER BY TOM.ORDER_ID
          
这个sql特点是：执行的次数不是很少，每个sql执行非常耗逻辑读（309,819.1）  

 

优化前后对比：

优化前执行时间：1.6秒左右
优化后执行时间：0.001秒

 

请把以上这个sql放到相应位置          
          

          

2.第二，三个sql 

 

如下的优化过程已经优化的生产库

 

优化前后对比：

优化前执行时间：1.6秒左右

优化后执行时间：0.001秒

 

速度提高近1600倍

           CPU      Elapsd
  Buffer Gets    Executions  Gets per Exec  %Total Time (s)  Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
     35,382,746           20    1,769,137.3   16.4   132.72    132.41  390687896

 

 SELECT
        w.ID AS ware_id,
        l.ID as lottery_id,
        l.lottery_name AS lottery_name,
        w.issue AS ware_issue,
        (f_recall_earliest_gametime(w.ID) + f_recall_advanced_by_gametime()) AS start_time
 FROM tb_lottery l,tb_ware w

 WHERE  l.ID = w.lottery_id AND l.sort_name <> 2 AND l.id <> 57 AND l.allow_advanced = 'Y'
 AND w.state IN (1,-1)
 AND NOT EXISTS
     (
       SELECT * FROM tb_recall_task rt
       WHERE rt.recall_type = 32 AND rt.ware_id = w.ID AND rt.game_no = 0 AND rt.is_done = 'Y'
     )
 AND (f_recall_earliest_gametime(w.ID) + f_recall_advanced_by_gametime()) <= to_date('2009-06-03 00:00:00','yyyy-mm-dd hh24:mi:ss')
ORDER BY  ware_id

 

这个语句的特点是虽然执行次数很少，仅有20次，但是他每次执行却消耗很多资源，读取buffer：1,769,137.3  其实超过100000的就说明这个sql的性能已经很差了

 

这也sql在优化前执行速度：7.83秒

 

状态：
TB_WARE: 14191
TB_LOTTERY; 58
TB_RECALL_TASK; 249276

 

这个sql返回很少的行，本sql返回8行记录

分析从这语句中执行计划中可以看到，执行计划很好，但就为什么还慢呢，我于是采用试探法

 

1.把语句简化为：

 

 SELECT
        w.ID AS ware_id,
        l.ID as lottery_id,
        l.lottery_name AS lottery_name,
        w.issue AS ware_issue,
        (f_recall_earliest_gametime(w.ID) + f_recall_advanced_by_gametime()) AS start_time
 FROM tb_lottery l,tb_ware w

 WHERE  l.ID = w.lottery_id AND l.sort_name <> 2 AND l.id <> 57 AND l.allow_advanced = 'Y'
 AND w.state IN (1,-1)
 /*AND NOT EXISTS
     (
       SELECT * FROM tb_recall_task rt
       WHERE rt.recall_type = 32 AND rt.ware_id = w.ID AND rt.game_no = 0 AND rt.is_done = 'Y'
     )*/
 AND (f_recall_earliest_gametime(w.ID) + f_recall_advanced_by_gametime()) <= to_date('2009-06-03 00:00:00','yyyy-mm-dd hh24:mi:ss')
ORDER BY  ware_id;

 

但sql的执行性能几乎没有变化，

 

2.猜想可能是因为那两个函数的原因

 

f_recall_earliest_gametime(w.ID)
f_recall_advanced_by_gametime()

sql进一步简化：

 

 SELECT
        w.ID AS ware_id,
        l.ID as lottery_id,
        l.lottery_name AS lottery_name,
        w.issue AS ware_issue,
       -- (f_recall_earliest_gametime(w.ID) + f_recall_advanced_by_gametime()) AS start_time
 FROM tb_lottery l,tb_ware w

 WHERE  l.ID = w.lottery_id AND l.sort_name <> 2 AND l.id <> 57 AND l.allow_advanced = 'Y'
 AND w.state IN (1,-1)
 /*AND NOT EXISTS
     (
       SELECT * FROM tb_recall_task rt
       WHERE rt.recall_type = 32 AND rt.ware_id = w.ID AND rt.game_no = 0 AND rt.is_done = 'Y'
     )*/
-- AND (f_recall_earliest_gametime(w.ID) + f_recall_advanced_by_gametime()) <= to_date('2009-06-03 00:00:00','yyyy-mm-dd hh24:mi:ss')
ORDER BY  ware_id;

 

这次在查询速度就发生了根本的变化，从7.83秒降低到 0.032秒，速度提高近244倍

 

尽然已经知道问题的原因，那就查看那两个函数

 

f_recall_earliest_gametime(w.ID)
f_recall_advanced_by_gametime()

以下两个函数内容都很简单，其中f_recall_advanced_by_gametime()就是返回常数值，这个实在是没办法优化了

f_recall_earliest_gametime(w.ID)中就一个语句，如下：

 

 

SELECT
MIN(G.GAME_TIME)
  FROM TB_GAME G
 INNER JOIN TB_MATCH M ON M.GAME_ID = G.ID
 WHERE M.WARE_ID = 20934

 

 观察这个语句的执行计划，发现这个sql占buffer ：45331 ，对这个sql优化

tb_game  44640行
tb_match 146764行

 

 

这个sql，返回很少的记录行，但却占有那么多的逻辑读，一般情况是没走正确的索引或走全表扫描

 

 

SQL> SELECT
  2  MIN(G.GAME_TIME)
  3    FROM TB_GAME G
  4   INNER JOIN TB_MATCH M ON M.GAME_ID = G.ID
  5   WHERE M.WARE_ID = 20934;

1 row selected.

Elapsed: 00:00:00.84

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=18 Card=1 Bytes=22)
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=18 Card=43 Bytes=946)
   3    2       TABLE ACCESS (FULL) OF 'TB_GAME' (Cost=18 Card=44640 B
          ytes=580320)

   4    2       INDEX (UNIQUE SCAN) OF 'AK_TB_MATCHA' (UNIQUE)

 

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

SQL>

 

调整之后的sql如下：

 

SELECT /*+ use_nl(g) index(g,PK_TB_GAME)*/
MIN(G.GAME_TIME)
  FROM TB_GAME G
 INNER JOIN TB_MATCH M ON M.GAME_ID = G.ID
 WHERE M.WARE_ID = 20934

 观察其执行计划如下：

 

 SQL> SELECT /*+ use_nl(g) index(g,PK_TB_GAME)*/
  2  MIN(G.GAME_TIME)
  3    FROM TB_GAME G
  4   INNER JOIN TB_MATCH M ON M.GAME_ID = G.ID
  5   WHERE M.WARE_ID = 20934;

1 row selected.

Elapsed: 00:00:00.60

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=45 Card=1 Bytes=22)
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=45 Card=43 Bytes=946)
   3    2       INDEX (RANGE SCAN) OF 'AK_TB_MATCHA' (UNIQUE) (Cost=2
          Card=43 Bytes=387)

   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'TB_GAME' (Cost=1 Car
          d=1 Bytes=13)

   5    4         INDEX (UNIQUE SCAN) OF 'PK_TB_GAME' (UNIQUE)

 

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

 

这个两个函数优化完了，再回到开始的那个sql，在执行时，发现其速度已经从7.83秒降到0.032秒

 

优化完毕，这两个sql就是我前两天抓取的，高逻辑读的前三个sql

 

 

 

 -----end---