sql优化案例:改变表的写法使代价和逻辑读降下来

上一篇 / 下一篇 2008-04-10 16:47:54 / 个人分类：pl/sql

优化前:

SQL> set autotrace traceonlyITPUB个人空间4hqu| J\fH }&J2q
SQL> Select a.*,b.*ITPUB个人空间y(u8e5|o7q]z Z:F#k
2    From tb_bet_log a
.id-I H~j;U%v0 3    Full Outer Join tb_user b On a.client_id = b.id
#l6Rw*GhF/x0 4   Where (b.AFFILIATE_CODE Is Not Null)
s;n8E+ZOs!|1T!g6h0 5   AndITPUB个人空间];o jSMqA8U
6   ((a.created_date Between to_date('2008-04-01 12:00:00', 'yyyy-mm-dd hh24:m
Z]/@0Z#^2Wuc0i:ss')
.ys;W-bpY:B%\`6z7U0 7   And to_date('2008-04-02 12:00:00', 'yyyy-mm-dd hh24:mi:ss')ITPUB个人空间TT{-W2p
8   And a.game_code_id < 1000000)ITPUB个人空间&U.a3c*I;_!y
9   Or b.first_login_time between to_date('2008-04-01 12:00:00','yyyy-mm-dd hh
9]msd'P'xi5F'U024:mi:ss')ITPUB个人空间1~Z8n/k{bI
10   And to_date('2008-04-02 12:00:00', 'yyyy-mm-dd hh24:mi:ss')ITPUB个人空间S+Oo;d{u7_%n%}
11   Or b.first_deposit_time Between to_date('2008-04-01 12:00:00', 'yyyy-mm-ddITPUB个人空间ZQ,zi W^K1nic
hh24:mi:ss')ITPUB个人空间P:m$l&n~2oH*R
12   And to_date('2008-04-02 12:00:00', 'yyyy-mm-dd hh24:mi:ss'));

11 rows selected.

@fQ R [x]0Execution Plan
&M J"Y)Qk%WM]&oFG0----------------------------------------------------------ITPUB个人空间,I3{/y4@f3JNK3W#F
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=5799 Card=2659929 ByITPUB个人空间I-z2_p T$Te!dF
tes=2332757733)

   1    0   VIEW (Cost=5799Card=2659929 Bytes=2332757733)ITPUB个人空间,i@%H:~S~pWED
   2    1     UNION-ALL
DdU'R q!\0   3    2       FILTER
)}7e`9mQ zMr0   4    3         HASH JOIN (OUTER)
|h/a+CH,^7b%~4{e1w0   5    4           PARTITION RANGE (ALL)
8o-jN9a5A0   6    5            TABLE ACCESS (FULL) OF 'TB_GAME_BET_LOG' (Cost=1
-Q4W0@9z1Z|z0          438 Card=2659928 Bytes=252693160)

7 4 TABLE ACCESS (FULL) OF 'TB_user' (Cost=6 Card=37ITPUB个人空间5{(]0I-NR"B
36 Bytes=511832)

   8    2       NESTED LOOPS (ANTI) (Cost=16 Card=1 Bytes=141)
j8NXCI0   9    8         TABLE ACCESS (FULL) OF 'TB_user' (Cost=6 Card=1 ByITPUB个人空间6w8pRW|"vO8H8|
          tes=137)

10 8 INDEX (RANGE SCAN) OF 'IDX_GAME_BET_CLIENT_ID1' (NON
0^:Po#m:y#C[jVUv0 -UNIQUE) (Cost=10 Card=2659928 Bytes=10639712)

Statistics
8~;R7`E!}(s$O0----------------------------------------------------------
p,hO2V3L0          0 recursive callsITPUB个人空间x:s7r5yh&Lnmf
          0 db block gets
!u%d[1Oh'b*rY[0     37354 consistent getsITPUB个人空间.r3T$e s(Vm#K.w
      74871 physical reads
m(W2n+F3Hb(w H0          0 redo size
`F0p8WD^$b*@:b0       3972 bytes sent via SQL*Net to client
2u E4v n5V{y&U_0        495 bytes received via SQL*Net from clientITPUB个人空间_[v4n&]-Pl*{ qQq
          2 SQL*Net roundtrips to/from client
(q6?SD N!Jn0          0 sorts (memory)ITPUB个人空间UI%[,W IT vf
          0 sorts (disk)
]wC)^WC;Zd'~0         11 rows processed

SQL>

优化后

改成每个表的数据放成一个子查询,先查出数据,再关联

SQL> select a.*, b.*ITPUB个人空间W0H{h+]K-`6f
2    from (Select *ITPUB个人空间&}4NCmTQ
3            From tb_bet_logITPUB个人空间u.}p5yT8M
4           where created_date Between
tu%U^j%a6M0 5                 to_date('2008-04-01 12:00:00', 'yyyy-mm-dd hh24:mi:ss') AndITPUB个人空间tc @ Lk%RCz
6                 to_date('2008-04-02 12:00:00', 'yyyy-mm-dd hh24:mi:ss')
8M ])|$dKk3C|8~0 7             And game_code_id < 1000000) a
p_,vVyF0 8    Full Outer Join (select *ITPUB个人空间 J{1u\8@D
9                       from tb_userITPUB个人空间LxEO FiXGbWI\
10                      Where
O^5L(J Xj8JH9o0 11                          first_login_time betweenITPUB个人空间xSu2W7WPy}^;\%I
12                            to_date('2008-04-01 12:00:00',
l U-wn~b0 13                                    'yyyy-mm-dd hh24:mi:ss') And
!_;Lu0Ic6L NHs0 14                            to_date('2008-04-02 12:00:00',ITPUB个人空间$wxM^-|^
15                                    'yyyy-mm-dd hh24:mi:ss')
G|*i0gfhx0 16                         Or first_deposit_time Between
|*Hx$X8e:v#IY0 17                            to_date('2008-04-01 12:00:00',ITPUB个人空间&E&o O7Je6fT
18                                    'yyyy-mm-dd hh24:mi:ss') AndITPUB个人空间4KPB9C ? Z
19                            to_date('2008-04-02 12:00:00',ITPUB个人空间geeF(E1a9r-F
20                                    'yyyy-mm-dd hh24:mi:ss')) b On a.client_iITPUB个人空间+Fv1d Z ~J
d = b.id
?K@S BM/V4@^+zI5f2RC0 21           where b.AFFILIATE_CODE is not null;

11 rows selected.

? b&xF?7o"R(V0Execution PlanITPUB个人空间Ci3Aw\ ]7Y3OZ
----------------------------------------------------------ITPUB个人空间w-{tx!{(W.UTVj
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=11Card=2 Bytes=1674
i[Ov|VWh/P0 )

   1    0   VIEW (Cost=11 Card=2 Bytes=1674)ITPUB个人空间!ul|1jXR)@*Q
   2    1     UNION-ALLITPUB个人空间d)fU/U!TL9~
   3    2       FILTERITPUB个人空间TXl2?)k2S
   4    3         NESTED LOOPS (OUTER)
4H!G}(Yj9S"hE0   5    4           TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TB_GAME_BE
d$y,}e5s-@:] k7~0          T_LOG' (Cost=2 Card=1 Bytes=100)

6 5 INDEX (RANGE SCAN) OF 'IDX_GAME_BET_LOG_CREATED_
-m:tO4u)w F,L#_~0 DATE' (NON-UNIQUE) (Cost=1 Card=1)

   7    4           VIEW PUSHED PREDICATE (Cost=1 Card=1 Bytes=137)ITPUB个人空间7im#?4m l?Om
   8    7             TABLE ACCESS (BY INDEX ROWID) OF 'TB_user' (Co
GNg!_ J0          st=2 Card=1 Bytes=137)

9 8 INDEX (UNIQUE SCAN) OF 'PK_TB_CLIENT' (UNIQUE)ITPUB个人空间(y^l czHS5p
(Cost=1 Card=3736)

10    2       NESTED LOOPS (ANTI) (Cost=8 Card=1 Bytes=154)ITPUB个人空间Jb4Gv E1|+fQ#C5H
11   10         TABLE ACCESS (FULL) OF 'TB_CLIENT' (Cost=6 Card=1 By
^:N5C:Rn/c*X0          tes=137)

12 10 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TB_GAME_BET_ITPUB个人空间&A1Rh"| J3N:X4m]g Z
LOG' (Cost=2 Card=1 Bytes=17)

13 12 INDEX (RANGE SCAN) OF 'IDX_GAME_BET_LOG_CREATED_DAITPUB个人空间1h4\0WjC x
TE' (NON-UNIQUE) (Cost=1 Card=1)

Statistics
ip}:z5d0----------------------------------------------------------ITPUB个人空间7Hg%v5u$mp
          0 recursive callsITPUB个人空间Z.W.i-v0i
          0 db block getsITPUB个人空间QOX&d6B1Fb
        130 consistent gets
\X.JW-F Ms,tW5g)A!G0          0 physical reads
Zb'}$TIGlq0          0 redo sizeITPUB个人空间X?l W o wp;u-}
       3972 bytes sent via SQL*Net to client
b l7l+K+e$K0        495 bytes received via SQL*Net from clientITPUB个人空间NQ'mQT8a(f
          2 SQL*Net roundtrips to/from client
O]NJi_0\0          0 sorts (memory)
L3X!i;ExVNQ0          0 sorts (disk)ITPUB个人空间y(x lRe
         11 rows processed
%Y#Hjn-h0

从 37354 consistent gets 到 130 consistent gets
G6\0\J7W0是一个很大的提高了ITPUB个人空间-P4K9_ c4l!]
而且查询时间从原来的92秒到现在的0.109秒

心里高兴了一把呵呵

全部脚印 不留脚印留下脚印:

导入论坛引用链接收藏分享给好友推荐到圈子管理举报

TAG:

引用删除 paulyibinyi / 2008-04-21 16:16:39: 这个也是同样的,先从一个表中查出需要的50行结果,再关联
第一个没优化代价为74,第二个优化后代价为70,
但逻辑读都一样
SQL> set autot on
SQL> Select Roulette_Value, Id
  2  From (Select a.Roulette_Value As Roulette_Value, a.Id
  3                   From Tb_Roulette_Deal_Detail a
  4                   Inner Join (Select Id
  5                                                                   From Tb_
Game_Deal_Log
  6                                                                   Where St
atus = 1 And Table_Id Like '%oo%' And Game_Code_Id = 50000
  7                   And created_date > Add_Months(Sysdate, -1)
  8                                                                   Order By
Created_Date Desc) b On a.Game_Deal_Log_Id = b.Id
  9                   Where a.Roulette_Value Is Not Null And a.Status = 1
10                   Order By a.Id Desc)
11  Where Rownum < 51;

no rows selected

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=74 Card=50 Bytes=130
      0)

1 0 COUNT (STOPKEY)
2 1    VIEW (Cost=74 Card=823 Bytes=21398)
3 2    SORT (ORDER BY STOPKEY) (Cost=74 Card=823 Bytes=18929)
4 3       HASH JOIN (Cost=69 Card=823 Bytes=18929)
5 4          VIEW (Cost=6 Card=823 Bytes=4938)
6 5          PARTITION RANGE (ITERATOR)
7 6             TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TB_GAM
      E_DEAL_LOG' (Cost=6 Card=823 Bytes=20575)

8 7                INDEX (RANGE SCAN DESCENDING) OF 'IDX_GAME_D
      EAL_LOG_CREATD_DATE1' (NON-UNIQUE) (Cost=56 Card=16863)

9 4          TABLE ACCESS (FULL) OF 'TB_ROULETTE_DEAL_DETAIL' (
      Cost=60 Card=301153 Bytes=5119601)

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

SQL> Select a.roulette_value As roulette_value, a.id
  2          From tb_roulette_deal_detail a
  3          Inner Join (
  4          select id from
  5          (
  6          Select id
  7                      From tb_game_deal_log
  8                      Where status = 1
  9                      and table_id like '%BB%'
10                      And game_code_id = 50000
11                      And created_date > Add_Months(Sysdate, -1)
12                      Order By created_date Desc
13          ) Where Rownum < 51
14                      ) b On a.game_deal_log_id = b.id
15          Where a.roulette_value Is Not Null
16          And a.status = 1
17          Order By a.id Desc;

no rows selected

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=70 Card=50 Bytes=115
      0)

1 0 SORT (ORDER BY) (Cost=70 Card=50 Bytes=1150)
2 1    HASH JOIN (Cost=68 Card=50 Bytes=1150)
3 2    VIEW (Cost=6 Card=50 Bytes=300)
4 3       COUNT (STOPKEY)
5 4          VIEW (Cost=6 Card=823 Bytes=10699)
6 5          PARTITION RANGE (ITERATOR)
7 6             TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TB_GAM
      E_DEAL_LOG' (Cost=6 Card=823 Bytes=20575)

8 7                INDEX (RANGE SCAN DESCENDING) OF 'IDX_GAME_D
      EAL_LOG_CREATD_DATE1' (NON-UNIQUE) (Cost=56 Card=16863)

9 2    TABLE ACCESS (FULL) OF 'TB_ROULETTE_DEAL_DETAIL' (Cost
      =60 Card=301153 Bytes=5119601)

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

SQL>