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

數據庫遷移中的Web翻頁優化實例

2013-11-13 22:08:37  來源: Oracle 

  最近忙著把公司的數據庫從mysql遷移到oracle期間作了很多工作來優化oracle平台的性能不過這裡面最大的性能調整還是來自sql下面舉一個web翻頁sql調整的例子
  
  環境
  Linux version custom (root@web) (gcc version (Red Hat Linux )) # SMP Thu Jun :: CST
  Mem: 
  Swap:
  CPU:兩個超線程的Intel(R) Xeon(TM) CPU GHz
  
  優化前語句在mysql裡面查詢秒左右出來轉移到oracle後進行在不調整索引和語句的情況下執行時間大概是調整後執行時間小於
  
  翻頁語句
  SELECT * FROM (SELECT T* rownum as linenum FROM (
  SELECT /*+ index(a ind_old)*/
  acategory FROM auction_auctions a WHERE acategory = AND aclosed= AND ends > sysdate AND (aapprove_status>=) ORDER BY aends) T WHERE rownum < ) WHERE linenum >=
  
  被查詢的表auction_auctions(產品表)
  表結構
  
  Code: [Copy to clipboard]
  
  SQL> desc auction_auctions;
  Name                   Null?  Type
  
  ID                    NOT NULL VARCHAR()
  USERNAME                      VARCHAR()
  TITLE                       CLOB
  GMT_MODIFIED               NOT NULL DATE
  STARTS                  NOT NULL DATE
  DESCRIPTION                    CLOB
  PICT_URL                      CLOB
  CATEGORY                 NOT NULL VARCHAR()
  MINIMUM_BID                    NUMBER
  RESERVE_PRICE                   NUMBER
  BUY_NOW                      NUMBER
  AUCTION_TYPE                    CHAR()
  DURATION                      VARCHAR()
  INCREMENTNUM               NOT NULL NUMBER
  CITY                        VARCHAR()
  PROV                        VARCHAR()
  LOCATION                      VARCHAR()
  LOCATION_ZIP                    VARCHAR()
  SHIPPING                      CHAR()
  PAYMENT                      CLOB
  INTERNATIONAL                   CHAR()
  ENDS                   NOT NULL DATE
  CURRENT_BID                    NUMBER
  CLOSED                       CHAR()
  PHOTO_UPLOADED                   CHAR()
  QUANTITY                      NUMBER()
  STORY                       CLOB
  HAVE_INVOICE               NOT NULL NUMBER()
  HAVE_GUARANTEE              NOT NULL NUMBER()
  STUFF_STATUS               NOT NULL NUMBER()
  APPROVE_STATUS              NOT NULL NUMBER()
  OLD_STARTS                NOT NULL DATE
  ZOO                        VARCHAR()
  PROMOTED_STATUS              NOT NULL NUMBER()
  REPOST_TYPE                    CHAR()
  REPOST_TIMES               NOT NULL NUMBER()
  SECURE_TRADE_AGREE            NOT NULL NUMBER()
  SECURE_TRADE_TRANSACTION_FEE            VARCHAR()
  SECURE_TRADE_ORDINARY_POST_FEE           NUMBER
  SECURE_TRADE_FAST_POST_FEE             NUMBER
  
  表記錄數及大小
  SQL> select count(*) from auction_auctions;
  
  COUNT(*)
  
  
  
  SQL> select segment_namebytesblocks from user_segments where segment_name =AUCTION_AUCTIONS;
  
  SEGMENT_NAME     BYTES   BLOCKS
  AUCTION_AUCTIONS     
  
  表上原有的索引
  create index ind_old on auction_auctions(closedapprove_statuscategoryends) tablespace tbsindex compress ;
  
  SQL> select segment_namebytesblocks from user_segments where segment_name = IND_OLD;
  
  SEGMENT_NAME      BYTES   BLOCKS
  IND_OLD             
  
  表和索引都已經分析過我們來看一下sql執行的費用
  SQL> set autotrace trace;
  SQL> SELECT * FROM (SELECT T* rownum as linenum FROM (SELECT a* FROM auction_auctions a WHERE acategory like % AND aclosed= AND ends > sysdate AND (aapprove_status>=) ORDER BY aends) T WHERE rownum <) WHERE linenum >= ;
   rows selected
  
  Execution Plan
  
     SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Byt
  es=)
  
      VIEW (Cost= Card= Bytes=)
       COUNT (STOPKEY)
        VIEW (Cost= Card= Bytes=)
         TABLE ACCESS (BY INDEX ROWID) OF AUCTION_AUCTIONS
  (Cost= Card= Bytes=)
  
          INDEX (RANGE SCAN) OF IND_OLD (NONUNIQUE) (Cost
  = Card=)
  
  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語句通過索引范圍掃描找到最裡面的結果集然後通過兩個view操作最後得出數據其中 consistent gets physical reads
  
  我們來看一下這個索引建的到底合不合理先看下各個查尋列的distinct值
  select count(distinct ends) from auction_auctions;
  
  COUNT(DISTINCTENDS)
  
  
  
  SQL> select count(distinct category) from auction_auctions;
  
  COUNT(DISTINCTCATEGORY)
  
  
  
  SQL> select count(distinct closed) from auction_auctions;
  
  COUNT(DISTINCTCLOSED)
  
  
  SQL> select count(distinct approve_status) from auction_auctions;
  
  COUNT(DISTINCTAPPROVE_STATUS)
  
  
  
  頁索引裡列平均存儲長度
  SQL> select avg(vsize(ends)) from auction_auctions;
  
  AVG(VSIZE(ENDS))
  
  
  
  SQL> select avg(vsize(closed)) from auction_auctions;
  
  AVG(VSIZE(CLOSED))
  
  
  
  SQL> select avg(vsize(category)) from auction_auctions;
  
  AVG(VSIZE(CATEGORY))
  
  
  
  SQL> select avg(vsize(approve_status)) from auction_auctions;
  
  AVG(VSIZE(APPROVE_STATUS))
  
  
  
  我們來估算一下各種組合索引的大小可以看到closedapprove_statuscategory都是相對較低集勢的列(重復值較多)下面我們來大概計算下各種頁索引需要的空間
  
  column        distinct num    column len
  ends                
  category                
  closed                  
  approve_status              
  
  index: (endsclosedcategoryapprove_status) compress
  en
From:http://tw.wingwit.com/Article/program/Oracle/201311/18323.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.