昨天下午客户报某个页面长期操作没完成

检查数据库日志发现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秒左右就出来呵呵起到优化效果

2008.07.01 sql优化一例