昨天下午 客户报某个页面长期操作没完成
检查数据库日志 发现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秒左右就出来 呵呵 起到优化效果