2008.07.01 sql優化一例

昨天下午 客戶報某個頁面長期操作沒完成

檢查資料庫日誌 發現ora-01555錯誤  捕獲的sql如下:

Select b.Id, b.credit_amount
  From (Select * From tb_test_bonus Where role_bonus_id = 121) a
 Right Join (Select * From tb_test Where role_id = 6) b On a.client_id = b.Id
 Where a.Id Is Null

最先想到的是對錶進行分析 因為這兩個表在開始上生產時才分析過一次

現在資料量也有變化 用dbms_stat包分析後

SQL> set autot on
SQL> Select b.Id, b.credit_amount
  2    From (Select * From tb_test_bonus Where role_bonus_id = 121) a
  3   Right Join (Select * From tb_test Where role_id = 6) b On a.client_id =
b.Id
  4   Where a.Id Is Null;

        ID CREDIT_AMOUNT
---------- -------------
     77349             0

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=186 Card=235 Bytes=8
          460)

   1    0   FILTER
   2    1     NESTED LOOPS (OUTER)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TB_test' (Cost=20
          Card=207 Bytes=4554)

   4    3         INDEX (RANGE SCAN) OF 'FKINDEX1_21' (NON-UNIQUE) (Co
          st=1 Card=207)

   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'TB_test_BONUS' (Co
          st=186 Card=1 Bytes=14)

   6    5         BITMAP CONVERSION (TO ROWIDS)
   7    6           BITMAP AND
   8    7             BITMAP CONVERSION (FROM ROWIDS)
   9    8               INDEX (RANGE SCAN) OF 'FKINDEX2_28' (NON-UNIQU
          E)

  10    7             BITMAP CONVERSION (FROM ROWIDS)
  11   10               INDEX (RANGE SCAN) OF 'FKINDEX1_23' (NON-UNIQU
          E) (Cost=28 Card=3)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    1580879  consistent gets
          0  physical reads
          0  redo size
        358  bytes sent via SQL*Net to client
        495  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修改為

SQL> set timing on
SQL> Select Id,credit_amount From tb_test a Where role_id=6 And Not Exists
  2  ( Select 1 From tb_test_bonus b Where b.role_bonus_id=121 And a.Id=b.clie
nt_id);

        ID CREDIT_AMOUNT
---------- -------------
     77349             0

Elapsed: 00:00:01.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=70 Card=10 Bytes=220
          )

   1    0   FILTER
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TB_test' (Cost=20 Ca
          rd=10 Bytes=220)

   3    2       INDEX (RANGE SCAN) OF 'FKINDEX1_21' (NON-UNIQUE) (Cost
          =1 Card=207)

   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'TB_test_BONUS' (Cost
          =5 Card=1 Bytes=9)

   5    4       INDEX (RANGE SCAN) OF 'FKINDEX2_28' (NON-UNIQUE) (Cost
          =1 Card=3)

 

 

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

SQL>

時間1秒左右就出來   呵呵 起到優化效果

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7199859/viewspace-368737/，如需轉載，請註明出處，否則將追究法律責任。