2008.07.01 sql优化一例

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

检查数据库日志 发现ora-01555错误  捕获的sql如下:

Select b.Id, b.credit_amountITPUB个人空间 w^J(w8rAML
@{7I0r
  From (Select * From tb_test_bonus Where role_bonus_id = 121) aITPUB个人空间9h8N.C%M/t3DL
 Right Join (Select * From tb_test Where role_id = 6) b On a.client_id = b.Id
:pc[B#^.~0 Where a.Id Is Null

最先想到的是对表进行分析 因为这两个表在开始上生产时才分析过一次

现在数据量也有变化 用dbms_stat包分析后

SQL> set autot onITPUB个人空间amc p}5CA1h~
SQL> Select b.Id, b.credit_amountITPUB个人空间 P)V4Z`-e b9N&m,t
  2    From (Select * From tb_test_bonus Where role_bonus_id = 121) aITPUB个人空间ig"biOJ
  3   Right Join (Select * From tb_test Where role_id = 6) b On a.client_id =ITPUB个人空间 V"j8Qb&Ub
b.IdITPUB个人空间9[)KwO.s
  4   Where a.Id Is Null;

        ID CREDIT_AMOUNT
/R&\.y a/icI\X0---------- -------------
`(? _^/d9i7N|@m M0     77349             0

ITPUB个人空间#J*?U)ow
Execution PlanITPUB个人空间3\U]iH(q({7SX
----------------------------------------------------------
Qs-W)RHO0   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=186 Card=235 Bytes=8
j9H?P
mj aA0          460)

   1    0   FILTER
X)\@!t9Ds%fY g0   2    1     NESTED LOOPS (OUTER)
5Q4^3p c6lX:k0   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TB_test' (Cost=20ITPUB个人空间W;qAy
`5Z
          Card=207 Bytes=4554)

   4    3         INDEX (RANGE SCAN) OF 'FKINDEX1_21' (NON-UNIQUE) (CoITPUB个人空间3h;RC\Q
          st=1 Card=207)

   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'TB_test_BONUS' (CoITPUB个人空间`Iy^.Xs
          st=186 Card=1 Bytes=14)

   6    5         BITMAP CONVERSION (TO ROWIDS)
N8Q9W:I(y4|0   7    6           BITMAP ANDITPUB个人空间.G0[0lQvf0{1S
   8    7             BITMAP CONVERSION (FROM ROWIDS)
?&OS)q8o:Wy0   9    8               INDEX (RANGE SCAN) OF 'FKINDEX2_28' (NON-UNIQU
(w:`"}Js8d*OId/?"M6_0          E)

  10    7             BITMAP CONVERSION (FROM ROWIDS)ITPUB个人空间qyn!wJx&x
  11   10               INDEX (RANGE SCAN) OF 'FKINDEX1_23' (NON-UNIQU
vb;EJ7PA'b#q6{z0          E) (Cost=28 Card=3)

Statistics
)}HRb:^9]p&U0----------------------------------------------------------
;So5gBj"wj.k0          0  recursive callsITPUB个人空间zS:WA
b0?3zX,H8C
          0  db block gets
;lzj;?6Oax8e0    1580879  consistent gets
y!B5v5Q&l*o|0          0  physical readsITPUB个人空间:w{S C9I#G@3V
          0  redo size
(m b3Rl.V[l Y:z0        358  bytes sent via SQL*Net to client
e[}'p+dq0W@0        495  bytes received via SQL*Net from clientITPUB个人空间#T+b)r+b3pO
          2  SQL*Net roundtrips to/from clientITPUB个人空间/PV|1eD3s5c.KTr
          0  sorts (memory)
jf:eU.}#l8?0          0  sorts (disk)ITPUB个人空间o7z9T1c,dT
          1  rows processed

SQL>

一致性读很高 导致很长时间才出来 问过开发人员知道逻辑后把sql修改为

SQL> set timing on
cL$m~*y}(BU6iR7\0SQL> Select Id,credit_amount From tb_test a Where role_id=6 And Not ExistsITPUB个人空间3u-?+m#Q:H
  2  ( Select 1 From tb_test_bonus b Where b.role_bonus_id=121 And a.Id=b.clie
*S;n
jq%i)F-Z0nt_id);

        ID CREDIT_AMOUNT
H8m$_'|S
T{0---------- -------------
c+ABHR$@0     77349             0

Elapsed: 00:00:01.02

Execution PlanITPUB个人空间0A3rd9hmpr
----------------------------------------------------------ITPUB个人空间S5EM1W.m @3~$u*R
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=70 Card=10 Bytes=220
`oz[Ch$W0          )

   1    0   FILTERITPUB个人空间To/u)L9Eku#[
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TB_test' (Cost=20 CaITPUB个人空间&|/s"b3?{+t_gx ~'j
          rd=10 Bytes=220)

   3    2       INDEX (RANGE SCAN) OF 'FKINDEX1_21' (NON-UNIQUE) (Cost
*u1yp%O5@H:u0          =1 Card=207)

   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'TB_test_BONUS' (CostITPUB个人空间%O:`.`3o0J
          =5 Card=1 Bytes=9)

   5    4       INDEX (RANGE SCAN) OF 'FKINDEX2_28' (NON-UNIQUE) (Cost
:PRB^K2LB4{0          =1 Card=3)

Statistics
i#r{$i[tN(~WM0----------------------------------------------------------ITPUB个人空间7] ]ST/H4i
          0  recursive callsITPUB个人空间n-K*v7?!Qpu B
          0  db block getsITPUB个人空间*hK!g pg
    132448  consistent gets
E#QF7mO0          0  physical readsITPUB个人空间8x,XX$~2{+ri
          0  redo sizeITPUB个人空间~ z?j W"AaWt
        358  bytes sent via SQL*Net to client
&QB MM0H{v&C0        495  bytes received via SQL*Net from client
2f6w*K?5H"w?&Jyhmer0          2  SQL*Net roundtrips to/from clientITPUB个人空间.Qh}0q c;^v!Z'utL
          0  sorts (memory)
!b^y} Les0          0  sorts (disk)ITPUB个人空间/z,k0O b2@dP-]*rm
          1  rows processed

SQL>

时间1秒左右就出来   呵呵 起到优化效果
`_ ~
NaH5V#Z0
Eq&KD8\[!@0