優化同事發過來的一個sql

SQL>set autotrace traceonly

SQL> Select distinct Id, Role_Id, Login
  2    From tb_test a,(Select Client_Id
  3            From Tb_test_Bet_Log y,
  4             (Select Distinct a.test_Deal_Log_Id
  5                    From Tb_test_Bet_Log a,
  6                     (Select Id
  7                            From tb_test
  8                           Where Exists (Select Id
  9                                    From (Select Id
 10                                            From Tb_Admin_Role
 11                                          Connect By Prior Id = Parent_Id
 12                                           Start With Id = 1) x
 13                                   Where Role_Id = x.Id)
 14                             And Lower(Login) = 'rtest5') b
 15                   Where a.Client_Id=b.id) b
 16               Where y.test_Deal_Log_Id=b.test_Deal_Log_Id
 17                 ) b
 18   Where a.Id=b.client_id
 19         ;

189 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2492 Card=4247 Bytes
          =237832)

   1    0   SORT (UNIQUE) (Cost=2492 Card=4247 Bytes=237832)
   2    1     HASH JOIN (Cost=2450 Card=4247 Bytes=237832)
   3    2       TABLE ACCESS (FULL) OF 'tb_test' (Cost=6 Card=3667 B
          ytes=62339)

   4    2       TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TB_test_BET_L
          OG' (Cost=10 Card=36 Bytes=324)

   5    4         NESTED LOOPS (Cost=2442 Card=4247 Bytes=165633)
   6    5           HASH JOIN (Cost=1262 Card=118 Bytes=3540)
   7    6             HASH JOIN (Cost=9 Card=1 Bytes=21)
   8    7               VIEW (Cost=2 Card=20 Bytes=80)
   9    8                 CONNECT BY (WITH FILTERING)
  10    9                   NESTED LOOPS
  11   10                     INDEX (UNIQUE SCAN) OF 'PK_TB_ADMIN_ROLE
          ' (UNIQUE) (Cost=1 Card=1 Bytes=4)

  12   10                     TABLE ACCESS (BY USER ROWID) OF 'TB_ADMI
          N_ROLE'

  13    9                   NESTED LOOPS
  14   13                     BUFFER (SORT)
  15   14                       CONNECT BY PUMP
  16   13                     INDEX (RANGE SCAN) OF 'INX_ADMIN_ROLE_P'
           (NON-UNIQUE) (Cost=2 Card=20 Bytes=160)

  17    7               TABLE ACCESS (FULL) OF 'tb_test' (Cost=6 Car
          d=37 Bytes=629)

  18    6             PARTITION RANGE (ALL)
  19   18               TABLE ACCESS (FULL) OF 'TB_test_BET_LOG' (Cost
          =1246 Card=2316228 Bytes=20846052)

  20    5           INDEX (RANGE SCAN) OF 'IDX_test_DEAL_LOG1' (NON-UN
          IQUE) (Cost=2 Card=36)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      39295  consistent gets
      33639  physical reads
          0  redo size
       4667  bytes sent via SQL*Net to client
        628  bytes received via SQL*Net from client
         14  SQL*Net roundtrips to/from client
         16  sorts (memory)
          0  sorts (disk)
        189  rows processed

通過紅色部分可以看出邏輯讀和物理讀比較大,並且還有兩個表全表掃描,

tb_test這個表是小表,全表掃描,但tb_test_bet_log是大表幾千萬資料,全表

掃描那就不合適了,這個表也沒加上任何限制條件,根據業務相結合,可以加上時間限制條件

這個表是分割槽表,而且是按時間分割槽索引的,所以加上時間限制條件後

看下面優化後的語句

SQL> Select distinct Id, Role_Id, Login
  2    From tb_test a,(Select Client_Id
  3            From Tb_test_Bet_Log y,
  4             (Select Distinct a.test_Deal_Log_Id,created_date
  5                    From Tb_test_Bet_Log a,   (Select Id
  6                            From tb_test
  7                           Where Exists (Select Id
  8                                    From (Select Id
  9                                            From Tb_Admin_Role
 10                                          Connect By Prior Id = Parent_Id
 11                                           Start With Id = 1) x
 12                                   Where Role_Id = x.Id)
 13                             And Lower(Login) = 'rtest5') b
 14                   Where a.Client_Id=b.id
 15                   and a.created_date between
 16                  to_date('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
 17                 and to_date('2007-01-03 23:59:59','yyyy-mm-dd hh24:mi:ss'))
b
 18               Where y.test_Deal_Log_Id=b.test_Deal_Log_Id
 19                 ) b
 20   Where a.Id=b.client_id;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=36 Card=6 Bytes=384)
   1    0   SORT (UNIQUE) (Cost=36 Card=6 Bytes=384)
   2    1     NESTED LOOPS (Cost=34 Card=6 Bytes=384)
   3    2       NESTED LOOPS (Cost=28 Card=6 Bytes=282)
   4    3         NESTED LOOPS (Cost=18 Card=1 Bytes=38)
   5    4           HASH JOIN (Cost=9 Card=1 Bytes=21)
   6    5             VIEW (Cost=2 Card=20 Bytes=80)
   7    6               FILTER
   8    7                 CONNECT BY (WITH FILTERING)
   9    8                   NESTED LOOPS
  10    9                     INDEX (UNIQUE SCAN) OF 'PK_TB_ADMIN_ROLE
          ' (UNIQUE) (Cost=1 Card=1 Bytes=4)

  11    9                     TABLE ACCESS (BY USER ROWID) OF 'TB_ADMI
          N_ROLE'

  12    8                   NESTED LOOPS
  13   12                     BUFFER (SORT)
  14   13                       CONNECT BY PUMP
  15   12                     INDEX (RANGE SCAN) OF 'INX_ADMIN_ROLE_P'
           (NON-UNIQUE) (Cost=2 Card=20 Bytes=160)

  16    5             TABLE ACCESS (FULL) OF 'tb_test' (Cost=6 Card=
          37 Bytes=629)

  17    4           TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'TB_test_BE
          T_LOG' (Cost=9 Card=2 Bytes=34)

  18   17             INDEX (RANGE SCAN) OF 'IDX_test_BET_LOG_CREATED_
          DATE' (NON-UNIQUE) (Cost=1 Card=82)

  19    3         TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'TB_test_BET
          _LOG' (Cost=10 Card=36 Bytes=324)

  20   19           INDEX (RANGE SCAN) OF 'IDX_test_DEAL_LOG1' (NON-UN
          IQUE) (Cost=2 Card=36)

  21    2       TABLE ACCESS (BY INDEX ROWID) OF 'tb_test' (Cost=1 C
          ard=1 Bytes=17)

  22   21         INDEX (UNIQUE SCAN) OF 'PK_tb_test' (UNIQUE)

 

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

SQL>
用上索引,邏輯讀和物理讀都降下來了

所以在優化過程中,有效的降低邏輯讀和物理讀那是最直接的方法而且要和業務相結合

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