熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

SQL質量差導致邏輯讀比較多

2013-11-13 16:21:12  來源: Oracle 

  最近日常檢查查看awr報告發現由一個索引的邏輯讀很大

  Tablespace                      Subobject  Obj       Logical

  Owner         Name    Object Name            Name     Type         Reads  %Total

  

  BASECONF   TBS_DEFALU PK_USER_TO_CHANNEL              INDEX     

  VIPBILL    TBS_DEFALU BIL_COLLECT_BIT                 TABLE      

  BASECONF   DATA_    LAYER_CHANNEL_TO_NOD            TABLE       

  BASECONF   DATA_    PK_                           INDEX       

  LOGBILL    TBS_BAND_D IX_DAILYTABLE_ALL    P  INDEX       

  表結構如下

   Create table

  create table USER_TO_CHANNEL

  (

  USER_ID    NUMBER() not null

  CHANNEL_ID CHAR() not null

  constraint PK_USER_TO_CHANNEL primary key (USER_ID CHANNEL_ID)

  )

  organization index;

  這是一個索引組織表表的所有字段組成一個主鍵索引用索引組織表是沒有問題的

  查找top sql發現有如下sql操作USER_TO_CHANNEL

                                 garqxtutkp

  Module: JDBC Thin Client

  select ACHANNEL_ID BCHANNEL_NAME from USER_TO_CHANNEL APUB_CHANNEL B where A

  USER_ID= and ACHANNEL_ID=BCHANNEL_ID and BCHANNEL_TYPE=HTTP

  SQL> select count(*) from USER_TO_CHANNEL;

  COUNT(*)

  

  

  SQL> select count(*) from PUB_CHANNEL;

  COUNT(*)

  

  

  SQL>

  查看sql的執行計劃

  SQL> set autotrace traceonly;

  SQL> select ACHANNEL_ID BCHANNEL_NAME

      from USER_TO_CHANNEL A PUB_CHANNEL B

     where AUSER_ID =

       and ACHANNEL_ID = BCHANNEL_ID

       and BCHANNEL_TYPE = HTTP;

   rows selected

  Execution Plan

  

  

  | Id  | Operation          | Name               | Rows  | Bytes | Cost  |

  

  |   | SELECT STATEMENT   |                    |    |    |

  |   |  NESTED LOOPS      |                    |    |    |

  |   |   TABLE ACCESS FULL| PUB_CHANNEL        |  |   K|    |

  |   |   INDEX UNIQUE SCAN| PK_USER_TO_CHANNEL |     |     |     |

  

  Note

  

   PLAN_TABLE is old version

  Statistics

  

    recursive calls

    db block gets

    consistent gets

    physical reads

    redo size

    bytes sent via SQL*Net to client

    bytes received via SQL*Net from client

    SQL*Net roundtrips to/from client

    sorts (memory)

    sorts (disk)

    rows processed

  oracle默認采用NL進行全表掃描

  此時的邏輯讀=consistent gets+db block gets=

  在sql語句中加入hint(

  SQL> select /*+use_hash(ba)*/ ACHANNEL_ID BCHANNEL_NAME

      from USER_TO_CHANNEL A PUB_CHANNEL B

     where AUSER_ID =

       and ACHANNEL_ID = BCHANNEL_ID

       and BCHANNEL_TYPE = HTTP;

   rows selected

  Execution Plan

  

  

  | Id  | Operation          | Name               | Rows  | Bytes | Cost  |

  

  |   | SELECT STATEMENT   |                    |    |    |

  |   |  HASH JOIN         |                    |    |    |

  |   |   INDEX RANGE SCAN | PK_USER_TO_CHANNEL |    |   |     |

  |   |   TABLE ACCESS FULL| PUB_CHANNEL        |  |   K|    |

  

  Note

  

   PLAN_TABLE is old version

  Statistics

  

    recursive calls

    db block gets

    consistent gets

    physical reads

    redo size

    bytes sent via SQL*Net to client

    bytes received via SQL*Net from client

    SQL*Net roundtrips to/from client

    sorts (memory)

    sorts (disk)

    rows processed

  在sql語句中加入hint此時的邏輯讀有明顯下降

  此時的邏輯讀=consistent gets+db block gets=

  在sql語句中加入hint(

  SQL> select /*+ use_nl(ba) leading(a) */ ACHANNEL_ID BCHANNEL_NAME

        from USER_TO_CHANNEL A PUB_CHANNEL B

       where AUSER_ID =

         and ACHANNEL_ID = BCHANNEL_ID

         and BCHANNEL_TYPE = HTTP;

   rows selected

  Execution Plan

  

  

  

  | Id  | Operation                    | Name               | Rows  | Bytes | Cost

  |

  

  

  |   | SELECT STATEMENT             |                    |    |   

   |

  |   |  NESTED LOOPS                |                    |    |   

   |

  |   |   INDEX RANGE SCAN           | PK_USER_TO_CHANNEL |    |   |

   |

  |   |   TABLE ACCESS BY INDEX ROWID| PUB_CHANNEL        |     |    |

   |

  |   |    INDEX UNIQUE SCAN         | PK_CHANNEL         |     |       |

   |

  

  

  Note

  

   PLAN_TABLE is old version

  Statistics

  

    recursive calls

    db block gets

    consistent gets

    physical reads

    redo size

    bytes sent via SQL*Net to client

    bytes received via SQL*Net from client

    SQL*Net roundtrips to/from client

    sorts (memory)

    sorts (disk)

    rows processed

  SQL>

  結論還是由於sql的質量比較差導致的邏輯讀比較多

  此時的邏輯讀=consistent gets+db block gets=

  感慨原因雖然找到了在一個線上系統查找該sql很困難總之在一個線上系統做點調整真難


From:http://tw.wingwit.com/Article/program/Oracle/201311/18189.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.