[20151203]一條sql語句的優化.txt

lfree發表於2015-12-03

[20151203]一條sql語句的優化.txt

--很久沒有看生產系統的awr報表,昨天下午看了一下,我非常討厭看生產系統的awr報表,看著開發寫的sql語句有時候心情會非常不好。

--實際上拿生產系統的sql語句寫blog非常困難,別人沒有你的環境(即使表述出來,也要浪費很長的篇幅),寫出來的東西大部分僅僅自
--己讀得懂。

1.環境:

SYSTEM@192.168.99.105:1521/dbcn> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

sql_id=18rkuzabjzj2f

SELECT CP_YZMX.XMMC
  FROM EMR_DJMX, emr_djfl, CP_YZMX
WHERE     EMR_DJMX.DJID = emr_djfl.Djid
       AND emr_djfl.DJID = CP_YZMX.DJID
       AND CP_YZMX.xmdm = EMR_DJMX.XMID
       AND CP_YZMX.XMDM = :1;

sql_id=7dys4vnppxu76

SELECT EMR_DJMX.SGLB, EMR_DJMX.YBLX, EMR_DJFL.ZXKS
  FROM EMR_DJMX, emr_djfl, CP_YZMX
WHERE     EMR_DJMX.DJID = emr_djfl.Djid
       AND emr_djfl.DJID = CP_YZMX.DJID
       AND CP_YZMX.xmdm = EMR_DJMX.XMID
       AND CP_YZMX.XMDM = :1;

--執行計劃如下:
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5a4qbg8wx7zz2, child number 0
-------------------------------------
select /* test 18rkuzabjzj2f */ /*+ gather_plan_statistics */
CP_YZMX.XMMC FROM EMR_DJMX , emr_djfl , CP_YZMX WHERE EMR_DJMX.DJID
=emr_djfl.Djid AND emr_djfl.DJID =CP_YZMX.DJID AND CP_YZMX.xmdm
=EMR_DJMX.XMID AND CP_YZMX.XMDM =:N1
Plan hash value: 2599026261
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |        |       |  8487 (100)|          |   4163 |00:00:00.22 |   32194 |       |       |          |
|*  1 |  HASH JOIN                    |                 |      1 |      3 |    99 |  8487   (1)| 00:01:42 |   4163 |00:00:00.22 |   32194 |  3886K|  3886K|  286K (0)|
|   2 |   JOIN FILTER CREATE          | :BF0000         |      1 |      1 |     9 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMR_DJMX        |      1 |      1 |     9 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|*  4 |     INDEX RANGE SCAN          | I_EMR_DJMX_XMID |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |  1025K|  1025K|          |
|   5 |   JOIN FILTER USE             | :BF0000         |      1 |     98 |  2352 |  8485   (1)| 00:01:42 |   4163 |00:00:00.22 |   32191 |       |       |          |
|*  6 |    TABLE ACCESS STORAGE FULL  | CP_YZMX         |      1 |     98 |  2352 |  8485   (1)| 00:01:42 |   4163 |00:00:00.22 |   32191 |  1025K|  1025K|          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$F04857F1
   3 - SEL$F04857F1 / EMR_DJMX@SEL$1
   4 - SEL$F04857F1 / EMR_DJMX@SEL$1
   6 - SEL$F04857F1 / CP_YZMX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 525
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("EMR_DJMX"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID")
   4 - access("EMR_DJMX"."XMID"=:N1)
   6 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."DJID","CP_YZMX"."XMDM")))
       filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."DJID","CP_YZMX"."XMDM")))
42 rows selected.

--僅僅顯示查詢的欄位不同。做一點小小的說明:EMR_DJMX, emr_djfl屬於應用的字典表都很小。僅僅CP_YZMX表很大,目前250M。

-- CP_YZMX.XMDM 漢語拼音轉義過來就是 專案程式碼。
-- CP_YZMX.XMMC 表示的就是專案名稱。
-- CP_YZMX 表示 CP表示臨床路徑 YZMX表示 醫囑明細

--很明顯第1條語句會出現出一串一樣的名稱,它輸出的全部做這個專案程式碼的專案名稱。第2條也是類似的情況。
--其實更讓我吃驚的是EMR_DJMX表裡面竟然沒有相關的xmmc(專案名稱)的欄位。

--這樣的查詢語句隨著表CP_YZMX表變大,查詢會越來越慢。

2.優化:
--也許有人會講像第1條語句建立CP_YZMX.XMDM的索引不就ok了嗎?當然加上CP_YZMX.XMMC建立的符合索引更佳(實際上僅僅建立
--CP_YZMX.XMDM索引根本沒用,因為這個欄位選擇性很差,我看統計僅僅28xx個不同的值),仔細想想好像是快的,但是隨著CP_YZMX記錄的
--增加,索引掃描的範圍會越來越大。

--如果真正優化我覺得應該寫成:

SELECT CP_YZMX.XMMC
  FROM EMR_DJMX, emr_djfl, CP_YZMX
WHERE     EMR_DJMX.DJID = emr_djfl.Djid
       AND emr_djfl.DJID = CP_YZMX.DJID
       AND CP_YZMX.xmdm = EMR_DJMX.XMID
       AND CP_YZMX.XMDM = :1 and rownum=1;

--當然還給建立符合索引 CP_YZMX.XMDM +CP_YZMX.XMMC. 或者CP_YZMX.XMDM。
--我明顯感覺開發資料結構存在問題,不知道為什麼字典表裡面沒有xmmc(專案名稱)欄位,也許還有其它字典儲存這個資訊。


3.接著看第2條語句:
--先看執行計劃:

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fqd21c2q20vvf, child number 0
-------------------------------------
select /* test 7dys4vnppxu76 */ /*+ gather_plan_statistics */
EMR_DJMX.SGLB , EMR_DJMX.YBLX , EMR_DJFL.ZXKS FROM EMR_DJMX , emr_djfl
, CP_YZMX WHERE EMR_DJMX.DJID =emr_djfl.Djid AND emr_djfl.DJID
=CP_YZMX.DJID AND CP_YZMX.xmdm =EMR_DJMX.XMID AND CP_YZMX.XMDM =:N1
Plan hash value: 1980913021
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |      1 |        |       |  8488 (100)|          |      0 |00:00:00.01 |       2 |       |       |          |
|*  1 |  HASH JOIN                      |                 |      1 |      2 |    62 |  8488   (1)| 00:01:42 |      0 |00:00:00.01 |       2 |  2345K|  2345K|37888  (0)|
|   2 |   JOIN FILTER CREATE            | :BF0000         |      1 |      1 |    24 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|   3 |    NESTED LOOPS                 |                 |      1 |      1 |    24 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|   4 |     NESTED LOOPS                |                 |      1 |      1 |    24 |     3   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| EMR_DJMX        |      1 |      1 |    16 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |       |       |          |
|*  6 |       INDEX RANGE SCAN          | I_EMR_DJMX_XMID |      1 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       2 |  1025K|  1025K|          |
|*  7 |      INDEX UNIQUE SCAN          | PK_EMR_DJFL     |      0 |      1 |       |     0   (0)|          |      0 |00:00:00.01 |       0 |  1025K|  1025K|          |
|   8 |     TABLE ACCESS BY INDEX ROWID | EMR_DJFL        |      0 |      1 |     8 |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |       |       |          |
|   9 |   JOIN FILTER USE               | :BF0000         |      0 |     98 |   686 |  8485   (1)| 00:01:42 |      0 |00:00:00.01 |       0 |       |       |          |
|* 10 |    TABLE ACCESS STORAGE FULL    | CP_YZMX         |      0 |     98 |   686 |  8485   (1)| 00:01:42 |      0 |00:00:00.01 |       0 |  1025K|  1025K|          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   5 - SEL$1 / EMR_DJMX@SEL$1
   6 - SEL$1 / EMR_DJMX@SEL$1
   7 - SEL$1 / EMR_DJFL@SEL$1
   8 - SEL$1 / EMR_DJFL@SEL$1
  10 - SEL$1 / CP_YZMX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 24024
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("EMR_DJFL"."DJID"="CP_YZMX"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID")
   6 - access("EMR_DJMX"."XMID"=:N1)
   7 - access("EMR_DJMX"."DJID"="EMR_DJFL"."DJID")
  10 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."DJID","CP_YZMX"."XMDM")))
       filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."DJID","CP_YZMX"."XMDM")))

--我這裡查詢結果是0,這也是這句話隱藏很深的原因,大部分情況下都是沒有結果(邏輯讀很小),因為這個表記錄的是臨床路徑的醫囑明細,許多項
--目不是臨床路徑的專案(看來dba熟悉業務也很重要),如果換成前面的525,再看看執行計劃。

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fqd21c2q20vvf, child number 0
-------------------------------------
select /* test 7dys4vnppxu76 */ /*+ gather_plan_statistics */
EMR_DJMX.SGLB , EMR_DJMX.YBLX , EMR_DJFL.ZXKS FROM EMR_DJMX , emr_djfl
, CP_YZMX WHERE EMR_DJMX.DJID =emr_djfl.Djid AND emr_djfl.DJID
=CP_YZMX.DJID AND CP_YZMX.xmdm =EMR_DJMX.XMID AND CP_YZMX.XMDM =:N1
Plan hash value: 1980913021
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |      1 |        |       |  8488 (100)|          |   4163 |00:00:00.19 |   32196 |       |       |          |
|*  1 |  HASH JOIN                      |                 |      1 |      2 |    62 |  8488   (1)| 00:01:42 |   4163 |00:00:00.19 |   32196 |  2981K|  2981K|  265K (0)|
|   2 |   JOIN FILTER CREATE            | :BF0000         |      1 |      1 |    24 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |       |       |          |
|   3 |    NESTED LOOPS                 |                 |      1 |      1 |    24 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |       |       |          |
|   4 |     NESTED LOOPS                |                 |      1 |      1 |    24 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |       |       |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| EMR_DJMX        |      1 |      1 |    16 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|*  6 |       INDEX RANGE SCAN          | I_EMR_DJMX_XMID |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |  1025K|  1025K|          |
|*  7 |      INDEX UNIQUE SCAN          | PK_EMR_DJFL     |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |  1025K|  1025K|          |
|   8 |     TABLE ACCESS BY INDEX ROWID | EMR_DJFL        |      1 |      1 |     8 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |       |       |          |
|   9 |   JOIN FILTER USE               | :BF0000         |      1 |     98 |   686 |  8485   (1)| 00:01:42 |   4163 |00:00:00.19 |   32191 |       |       |          |
|* 10 |    TABLE ACCESS STORAGE FULL    | CP_YZMX         |      1 |     98 |   686 |  8485   (1)| 00:01:42 |   4163 |00:00:00.19 |   32191 |  1025K|  1025K|          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--查詢返回4163條,全部是一樣的內容。

--最先想到後面加入rownum=1的條件,執行計劃如下:


PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5nrjjgv8rw3zf, child number 0
-------------------------------------
select /* test 7dys4vnppxu76 */ /*+ gather_plan_statistics */
EMR_DJMX.SGLB , EMR_DJMX.YBLX , EMR_DJFL.ZXKS FROM EMR_DJMX , emr_djfl
, CP_YZMX WHERE EMR_DJMX.DJID =emr_djfl.Djid AND emr_djfl.DJID
=CP_YZMX.DJID AND CP_YZMX.xmdm =EMR_DJMX.XMID AND CP_YZMX.XMDM =:N1 and
rownum=1
Plan hash value: 2932374829
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                 |      1 |        |       |  3204 (100)|          |      1 |00:00:00.19 |   32179 |       |       |          |
|*  1 |  COUNT STOPKEY                           |                 |      1 |        |       |            |          |      1 |00:00:00.19 |   32179 |       |       |          |
|   2 |   MERGE JOIN                             |                 |      1 |      3 |   159 |  3204   (1)| 00:00:39 |      1 |00:00:00.19 |   32179 |       |       |          |
|   3 |    MERGE JOIN                            |                 |      1 |     41 |   902 |  3201   (1)| 00:00:39 |      1 |00:00:00.19 |   32176 |       |       |          |
|   4 |     SORT JOIN                            |                 |      1 |     37 |   259 |  3197   (1)| 00:00:39 |      1 |00:00:00.19 |   32170 |   142K|   142K|  126K (0)|
|*  5 |      TABLE ACCESS STORAGE FULL FIRST ROWS| CP_YZMX         |      1 |     37 |   259 |  3197   (1)| 00:00:39 |   4163 |00:00:00.19 |   32170 |  1025K|  1025K|          |
|*  6 |     SORT JOIN                            |                 |      1 |     40 |   320 |     4  (25)| 00:00:01 |      1 |00:00:00.01 |       6 | 73728 | 73728 |          |
|   7 |      TABLE ACCESS STORAGE FULL FIRST ROWS| EMR_DJFL        |      1 |     40 |   320 |     3   (0)| 00:00:01 |     40 |00:00:00.01 |       6 |  1025K|  1025K|          |
|*  8 |    SORT JOIN                             |                 |      1 |      1 |    16 |     3  (34)| 00:00:01 |      1 |00:00:00.01 |       3 | 73728 | 73728 |          |
|   9 |     TABLE ACCESS BY INDEX ROWID          | EMR_DJMX        |      1 |      1 |    16 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|* 10 |      INDEX RANGE SCAN                    | I_EMR_DJMX_XMID |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |  1025K|  1025K|          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   5 - SEL$1 / CP_YZMX@SEL$1
   7 - SEL$1 / EMR_DJFL@SEL$1
   9 - SEL$1 / EMR_DJMX@SEL$1
  10 - SEL$1 / EMR_DJMX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 525
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)
   5 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1))
       filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1))
   6 - access("EMR_DJFL"."DJID"="CP_YZMX"."DJID")
       filter("EMR_DJFL"."DJID"="CP_YZMX"."DJID")
   8 - access("EMR_DJMX"."DJID"="EMR_DJFL"."DJID" AND "CP_YZMX"."XMDM"="EMR_DJMX"."XMID")
       filter(("CP_YZMX"."XMDM"="EMR_DJMX"."XMID" AND "EMR_DJMX"."DJID"="EMR_DJFL"."DJID"))
  10 - access("EMR_DJMX"."XMID"=:N1)

--說明:我們的系統使用硬體是exadata,第1次看到Operation =TABLE ACCESS STORAGE FULL FIRST ROWS,邏輯讀並不小32170,32170*8/1024=251M.
--改變連線順序,加入提示:/*+ leading  (EMR_DJMX , emr_djfl , CP_YZMX ) */

Plan hash value: 2276510683
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                 |      1 |        |       |  6912 (100)|          |      1 |00:00:00.02 |    3727 |       |       |          |
|*  1 |  COUNT STOPKEY                          |                 |      1 |        |       |            |          |      1 |00:00:00.02 |    3727 |       |       |          |
|*  2 |   HASH JOIN                             |                 |      1 |      2 |    62 |  6912   (1)| 00:01:23 |      1 |00:00:00.02 |    3727 |  2981K|  2981K|  282K (0)|
|   3 |    JOIN FILTER CREATE                   | :BF0000         |      1 |      1 |    24 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |       |       |          |
|   4 |     NESTED LOOPS                        |                 |      1 |      1 |    24 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |       |       |          |
|   5 |      NESTED LOOPS                       |                 |      1 |      1 |    24 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |       |       |          |
|   6 |       TABLE ACCESS BY INDEX ROWID       | EMR_DJMX        |      1 |      1 |    16 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|*  7 |        INDEX RANGE SCAN                 | I_EMR_DJMX_XMID |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |  1025K|  1025K|          |
|*  8 |       INDEX UNIQUE SCAN                 | PK_EMR_DJFL     |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |  1025K|  1025K|          |
|   9 |      TABLE ACCESS BY INDEX ROWID        | EMR_DJFL        |      1 |      1 |     8 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |       |       |          |
|  10 |    JOIN FILTER USE                      | :BF0000         |      1 |     80 |   560 |  6909   (1)| 00:01:23 |      1 |00:00:00.02 |    3722 |       |       |          |
|* 11 |     TABLE ACCESS STORAGE FULL FIRST ROWS| CP_YZMX         |      1 |     80 |   560 |  6909   (1)| 00:01:23 |      1 |00:00:00.02 |    3722 |  1025K|  1025K|          |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--邏輯讀減少不少。實際上注意看CP_YZMX邏輯讀3722,也就是邏輯讀讀到3772時,找到滿足條件的記錄,還可以發現一個奇怪的現象:
--前面的 TABLE ACCESS STORAGE FULL FIRST ROWS| CP_YZMX  cost= 3197,而後面的cost=6909,當然主要差異在 E-Rows 不同,前面37,
--而後者80。

--再仔細看顯示欄位根本沒有CP_YZMX表的欄位,查詢修改如下:

SELECT EMR_DJMX.SGLB, EMR_DJMX.YBLX, EMR_DJFL.ZXKS
  FROM EMR_DJMX, emr_djfl
WHERE     EMR_DJMX.DJID = emr_djfl.Djid
       AND EXISTS
              (SELECT 1
                 FROM CP_YZMX
                WHERE     CP_YZMX.XMDM = :1
                      AND CP_YZMX.xmdm = EMR_DJMX.XMID
                      AND emr_djfl.DJID = CP_YZMX.DJID);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8112tdu600w31, child number 0
-------------------------------------
SELECT EMR_DJMX.SGLB, EMR_DJMX.YBLX, EMR_DJFL.ZXKS   FROM EMR_DJMX,
emr_djfl  WHERE     EMR_DJMX.DJID = emr_djfl.Djid        AND EXISTS
          (SELECT 1                  FROM CP_YZMX                 WHERE
    CP_YZMX.XMDM = :N1                       AND CP_YZMX.xmdm =
EMR_DJMX.XMID                       AND emr_djfl.DJID = CP_YZMX.DJID)
Plan hash value: 130981308
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |      1 |        |       |  8488 (100)|          |      1 |00:00:00.03 |    3727 |       |       |          |
|*  1 |  HASH JOIN SEMI                 |                 |      1 |      1 |    31 |  8488   (1)| 00:01:42 |      1 |00:00:00.03 |    3727 |  2981K|  2981K|  282K (0)|
|   2 |   JOIN FILTER CREATE            | :BF0000         |      1 |      1 |    24 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |       |       |          |
|   3 |    NESTED LOOPS                 |                 |      1 |      1 |    24 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |       |       |          |
|   4 |     NESTED LOOPS                |                 |      1 |      1 |    24 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |       |       |          |
|   5 |      TABLE ACCESS BY INDEX ROWID| EMR_DJMX        |      1 |      1 |    16 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |       |       |          |
|*  6 |       INDEX RANGE SCAN          | I_EMR_DJMX_XMID |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |  1025K|  1025K|          |
|*  7 |      INDEX UNIQUE SCAN          | PK_EMR_DJFL     |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |  1025K|  1025K|          |
|   8 |     TABLE ACCESS BY INDEX ROWID | EMR_DJFL        |      1 |      1 |     8 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |       |       |          |
|   9 |   JOIN FILTER USE               | :BF0000         |      1 |     98 |   686 |  8485   (1)| 00:01:42 |      1 |00:00:00.03 |    3722 |       |       |          |
|* 10 |    TABLE ACCESS STORAGE FULL    | CP_YZMX         |      1 |     98 |   686 |  8485   (1)| 00:01:42 |      1 |00:00:00.03 |    3722 |  1025K|  1025K|          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$5DA710D3
   5 - SEL$5DA710D3 / EMR_DJMX@SEL$1
   6 - SEL$5DA710D3 / EMR_DJMX@SEL$1
   7 - SEL$5DA710D3 / EMR_DJFL@SEL$1
   8 - SEL$5DA710D3 / EMR_DJFL@SEL$1
  10 - SEL$5DA710D3 / CP_YZMX@SEL$2
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 525
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("CP_YZMX"."XMDM"="EMR_DJMX"."XMID" AND "EMR_DJFL"."DJID"="CP_YZMX"."DJID")
   6 - access("EMR_DJMX"."XMID"=:N1)
   7 - access("EMR_DJMX"."DJID"="EMR_DJFL"."DJID")
  10 - storage(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."XMDM","CP_YZMX"."DJID")))
       filter(("CP_YZMX"."DJID" IS NOT NULL AND "CP_YZMX"."XMDM"=:N1 AND SYS_OP_BLOOM_FILTER(:BF0000,"CP_YZMX"."XMDM","CP_YZMX"."DJID")))
50 rows selected.

--實際上如果不用CP_YZMX表,查詢可以修改如下:

SELECT EMR_DJMX.SGLB, EMR_DJMX.YBLX, EMR_DJFL.ZXKS FROM EMR_DJMX, emr_djfl WHERE     EMR_DJMX.DJID = emr_djfl.Djid and
EMR_DJMX.XMID=:N1;

SYSTEM@192.168.99.105:1521/dbcn> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4sjt49mk99kk3, child number 0
-------------------------------------
SELECT EMR_DJMX.SGLB, EMR_DJMX.YBLX, EMR_DJFL.ZXKS FROM EMR_DJMX,
emr_djfl WHERE     EMR_DJMX.DJID = emr_djfl.Djid and EMR_DJMX.XMID=:N1
Plan hash value: 582457811
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                 |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |       6 |       |       |          |
|   1 |  NESTED LOOPS                 |                 |      1 |      1 |    24 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       6 |       |       |          |
|   2 |   NESTED LOOPS                |                 |      1 |      1 |    24 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMR_DJMX        |      1 |      1 |    16 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |       |       |          |
|*  4 |     INDEX RANGE SCAN          | I_EMR_DJMX_XMID |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |  1025K|  1025K|          |
|*  5 |    INDEX UNIQUE SCAN          | PK_EMR_DJFL     |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |  1025K|  1025K|          |
|   6 |   TABLE ACCESS BY INDEX ROWID | EMR_DJFL        |      1 |      1 |     8 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / EMR_DJMX@SEL$1
   4 - SEL$1 / EMR_DJMX@SEL$1
   5 - SEL$1 / EMR_DJFL@SEL$1
   6 - SEL$1 / EMR_DJFL@SEL$1
Peeked Binds (identified by position):
--------------------------------------
   1 - (NUMBER): 525
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("EMR_DJMX"."XMID"=:N1)
   5 - access("EMR_DJMX"."DJID"="EMR_DJFL"."DJID")

--僅僅6個邏輯讀。
--當然要把邏輯讀進一步降低,還要建立CP_YZMX.XMDM的垃圾索引。當然我的優化建立在修改結果集的情況。

總結:
--1.每次看生產系統的awr報表心情總是不好,一些有問題的語句awr看不到,像第2條,如果僅僅關注邏輯讀很難發現第2條語句存在問題。因為大部分查詢返回0行。
--2.資料結構存在問題,也許還有1個表儲存專案名稱。
--3.開發寫sql語句到底想一下沒有,看著這樣開發團隊,真令人失望。

--4.在完成後才發現第1個還可以這樣寫:

SELECT CP_YZMX.XMMC
  FROM CP_YZMX
WHERE     CP_YZMX.XMDM = :1 and rownum=1
       AND EXISTS
              (SELECT 1
                 FROM EMR_DJMX, emr_djfl
                WHERE     EMR_DJMX.DJID = emr_djfl.Djid
                      AND emr_djfl.DJID = CP_YZMX.DJID
                      AND CP_YZMX.xmdm = EMR_DJMX.XMID);

--甚至後面的exists都不要,真不知道開發要表達的意思,只能說明開發寫sql思路混亂。

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

相關文章