程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL優化一例

SQL優化一例

編輯:關於SqlServer

       今天閒的 看awr,發現一條SQL 每次執行40多秒,語句如下
    ?

    1 SELECT a.bill_class AS pol_code , b.bill_name AS pol_name , a.bill_no AS card_no , '網站' AS buy_path , a.rev_clerk_code AS agent_code , a.rev_clerk_type AS agent_type , to_char(a.regist_date, 'yyyy-mm-dd') AS recip_date , to_char(a.chk_date, 'yyyy-mm-dd') AS pay_date , to_char(a.invalid_date, 'yyyy-mm-dd') AS invalid_date , CASE WHEN chk_stat = '0' THEN '未核銷' WHEN chk_stat = '1' AND autochkflag = '1' THEN '已自動核銷' WHEN chk_stat = '1' AND autochkflag = '0' THEN '已人工核銷' WHEN chk_stat = '2' THEN '丟失' WHEN chk_stat = '3' THEN '作廢' WHEN chk_stat = '4' THEN '回繳核銷' WHEN chk_stat = '5' THEN '已銷毀' WHEN chk_stat = 'A' THEN '待核銷' END AS recip_stat , rev_branch_no , b.bill_literal_price AS prem FROM shcvms.bill_grant_check a , shcvms.bill_class b WHERE a.regist_date IS NOT NULL AND a.bill_class = b.bill_class AND a.bill_class IN ( '1001', '1093', '1096', '1097', '1098', '1099', '1100', '1302', '1303', '1305', '1306', '1910', '1911', '1912', '1913', '1914', '1915', '1916', '1917' , '1918', '1919', '1922', '1923', '1924', '1925', '1926', '1927', '1928', '1929', '1930', '1931', '1935', '1936', '1937', '1938', '1939', '1940', '1941', '1942', '1943', '1944', '1945', '5232', '5233', '5234', '5252', '5255', '5258', '5265', '5260', '5276', '5277', '5278', '5285', '5290', '5301', '5304', '5309', '5311', '5329', '5330', '5359' , '5376', '5507', '5512', '5514', '5516', '5122', '5126', '5130', '5132', '5141', '5142', '5156', '5157', '5161', '5162', '5163', '5164', '5165', '5166', '5167', '5168', '5558', '5564', '5507' )

      看這種SQL,一種方法,是通過PL/SQL DEV直接格式化下,第二種方法,放到txt文件中,搜索 FROM,直接把前面的全刪了(非標量子查詢時)。

      SELECT ……

      FROM shcvms.bill_grant_check a , shcvms.bill_class b

      WHERE a.regist_date IS NOT NULL AND

      a.bill_class = b.bill_class

      AND a.bill_class IN ( ……);

      這裡也沒有綁定變量,所以explain plan for的執行計劃是沒有誤差的

      當前執行計劃如下

    ?

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2990887684   --------------------------------------------------------------------------------------- | Id  | Operation      | Name         | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |              |  6452K|   553M|   151K  (5)| 00:30:19 | |*  1 |  HASH JOIN     |              |  6452K|   553M|   151K  (5)| 00:30:19 | |*  2 |   TABLE ACCESS FULL| BILL_CLASS       |   206 |  5150 |     3   (0)| 00:00:01 | |*  3 |   TABLE ACCESS FULL| BILL_GRANT_CHECK |  7321K|   453M|   151K  (5)| 00:30:19 | ---------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - access("A"."BILL_CLASS"="B"."BILL_CLASS")    2 - filter("B"."BILL_CLASS"='1001' OR "B"."BILL_CLASS"='1093' OR           "B"."BILL_CLASS"='1096' OR "B"."BILL_CLASS"='1097' OR "B"."BILL_CLASS"='1098'           OR "B"."BILL_CLASS"='1099' OR "B"."BILL_CLASS"='1100' OR           "B"."BILL_CLASS"='1302' OR "B"."BILL_CLASS"='1303' OR "B"."BILL_CLASS"='1 。 。 。 。

      這個看起來很簡單的,7321k=7.3M 7.3M/453M =1.6% 這個結果集,完全可以走索引,而且這裡使用的in,並且裡面用的是常量,完全可以走 INList ITERATOR

      我創建一個虛索引,看一下執行計劃,如果效果確實不錯,再提個申請,真實創建這個索引。

    ?

    1 2 3 4 5 6 7 8 SQL> create index billgrant_class on shcvms.bill_grant_check(bill_class) nosegment;   Index created.   SQL> alter session set "_use_nosegment_indexes"=true;   Session altered.

      然後使用explain plan for 來解析執行計劃

    ?

    1 2 3 4 5 explain plan for SELECT /*+ index(a billgrant_class ) */ N多個列  FROM shcvms.bill_grant_check a , shcvms.bill_class b WHERE a.regist_date IS NOT NULL AND a.bill_class = b.bill_class AND a.bill_class IN (......N多常量 )

      我們看看執行計劃

    ?

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 2116188717   -------------------------------------------------------------------------------------------------- | Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT          |          |  6452K|   553M|   207   (1)| 00:00:03 | |   1 |  MERGE JOIN           |          |  6452K|   553M|   207   (1)| 00:00:03 | |   2 |   INLIST ITERATOR         |          |   |   |        |      | |*  3 |    TABLE ACCESS BY INDEX ROWID| BILL_GRANT_CHECK |  7321K|   453M|   203   (1)| 00:00:03 | |*  4 |     INDEX RANGE SCAN          | BILLGRANT_CLASS  |  7321K|   |    16   (0)| 00:00:01 | |*  5 |   SORT JOIN           |          |   206 |  5150 |     4  (25)| 00:00:01 | |*  6 |    TABLE ACCESS FULL          | BILL_CLASS   |   206 |  5150 |     3   (0)| 00:00:01 | --------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      3 - filter("A"."REGIST_DATE" IS NOT NULL)    4 - access("A"."BILL_CLASS"='1001' OR "A"."BILL_CLASS"='1093' OR           "A"."BILL_CLASS"='1096' OR "A"."BILL_CLASS"='1097' OR "A"."BILL_CLASS"='1098' OR           "A"."BILL_CLASS"='1099' OR "A"."BILL_CLASS"='1100' OR "A"."BILL_CLASS"='1302' OR

      看Cost。從之前的151k 降到了現在的207,速度提升為原來的1/1000。 在統計信息正確的情況下 cost是具有參考價值的(不正確的話就別看cost了)

      現在可以提申請了。真實創建這個索引。

      一個簡單的例子完事,(雖然這麼簡單的,不想往這貼,但是我blog中“Oracle優化之SQL 優化”分支太空了,寫點東西充實一下它)

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved