相差數十倍的SQL效能分析(r11筆記第98天)

   今天處理開發同學提交的一個資料查詢需求，看起來是一個很常規的SQL,但是有一點不同的是，他們提供了兩份檔案，一份是一個id列表，大概有3000多個id值，另外一個份是個SQL檔案。

   之前也處理過幾十萬，上百萬id值的情況，使得我原來開發中對於變動的敏感性依舊存在，所以我採用了另外一種靈活的方式，即外部表,外部表是資料庫外的資料存在，在資料庫依舊可以讀取訪問。

CREATE TABLE  test_cn
      (cn    varchar2(50)
       )
    ORGANIZATION EXTERNAL
      (TYPE ORACLE_LOADER
      DEFAULT DIRECTORY ext_dir
      ACCESS PARAMETERS
        (
        RECORDS DELIMITED BY NEWLINE      
        )
      LOCATION ('data.txt')
     );   而在這個基礎上執行的SQL語句也很簡短。

select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn ) ;   這樣一來就達到了一種一勞永逸的效果，那就是後期如果開發同學繼續提供另外一個查詢，只要提供了id值，不管是多大，我都能輕鬆處理，不管是哪個業務的SQL我都能靈活套用。

   但是問題來了，上面的SQL語句執行的時候，速度讓我很不滿意，因為持續了近2分鐘。

select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn ) ;
Elapsed: 00:01:59.39為什麼很不滿意，是因為這個“表”中的主鍵是基於欄位uin的，竟然查詢速度這麼慢，實在不給面子。

INDEX_NAME                     COLUMN_NAME  INDEX_TYPE  UNIQUENES
------------------------------ ------------ ---------------------
PK_USER_CERTIFICATION_INFO1    UIN          NORMAL      UNIQUE對於這類問題我還是有不小的興趣，畢竟能夠順手最佳化最佳化也是不錯的體驗。我嘗試加了rownum，儘管這樣不夠嚴謹，但是輸出結果和時間還是和開始的差不多。

select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn ) and rownum<=4000 ;
Elapsed: 00:01:59.15可見Oracle最佳化器早就看穿了我的心思，我怎麼能夠耍點小聰明呢。

select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn where rownum<=3200) ;
Elapsed: 00:00:00.29這樣一個查詢就能夠達到非一般的速度。

這是為什麼呢。要想得到一些更為細緻的問題，那我們就開啟trace來診斷一下，怎麼診斷呢，一種比較自然的思路那就是10053事件。

10053事件診斷SQL

開啟10053事件的步驟如下：

ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
explain plan for select uin,regDate,regIP,cert_number from accstat.test_certification_info where uin in(select cn from test.test_cn ) ;
ALTER SESSION SET EVENTS '10053 trace name context off';其中能夠看到不少細節的資訊，我摘取出一小段來。

FPD: Considering simple filter push (pre rewrite) in query block SEL$1 (#0)
FPD:  Current where clause predicates "TEST_CERTIFICATION_INFO"."UIN"=ANY (SELECT "TEST_CN"."CN" FROM "TEST"."TEST_CN" "TEST_CN")
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "TEST_CERTIFICATION_INFO"."UIN"=ANY (SELECT "TEST_CN"."CN" FROM "TEST"."TEST_CN" "TEST_CN")
最後經過查詢轉換，得到的最終語句如下：

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TEST_CERTIFICATION_INFO"."UIN" "UIN",..... FROM "TEST"."TEST_CN" "TEST_CN", ( (SELECT "ACC00_TEST_CERTIFICATION_INFO"."UIN" ... "ACC35_TEST_CERTIFICATION_INFO")) "TEST_CERTIFICATION_INFO" WHERE "TEST_CERTIFICATION_INFO"."UIN"=TO_NUMBER("TEST_CN"."CN")
kkoqbc: optimizing query block SEL$2 (#14)   可能看到這裡就有些懵了，這個test_certification_info其實是個檢視，裡面包含有12個物化檢視。其實這個簡單的查詢就好比是12個物化檢視和一個外部表的關聯查詢。

   那麼為什麼子查詢使用了rownum之後，效率大大提升呢。這個可以從日誌中看出端倪，我們可以清楚的看到最佳化器預估的時候這個外部表的資料條數是179950，和現在的3000多條想去甚遠。

Table Stats::
  Table: TEST_CN  Alias: TEST_CN
    #Rows: 179950  #Blks:  462  AvgRowLen:  21.00  ChainCnt:  0.00
Access path analysis for TEST_CN那麼為什麼最佳化器認為是179950條資料呢，這個和統計資訊還是密切相關，儘管外部表不佔用資料檔案的儲存，但是依然還是有一個基本的統計資訊。

SQL> select num_rows from dba_tables where table_name='TEST_CN';
  NUM_ROWS
----------
    179950可能有很多同學說，那就收集統計資訊，應該能夠解決這個問題。SQL>  exec dbms_stats.gather_table_stats(ownname=>'TEST',TABNAME=>'TEST_CN');
PL/SQL procedure successfully completed.   然後再次嘗試，竟然還是很慢，檢視執行計劃發現裡面始終是走了全表掃描。

   這個問題的一種快速解決方式就是使用子查詢中的rownum來限定，如果查詢的資料缺失夠多，走全表也不失為一種合理的方法。