[20170104]一條sql優化.txt
[20170104]一條sql優化.txt
--生產系統不明原因重啟,看了1下,順便看了前後的awr報表,發現一條語句,其實問題沒什麼,只不過這種現象在開發很普遍,做一點點記錄.
1.環境:
xxxxxx> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
$ cat gf3wvnjzfnysy.sql
SELECT DISTINCT dept_dict.dept_name, staff_vs_group.group_code
FROM dept_dict, staff_dict, staff_vs_group
WHERE (staff_dict.emp_no = staff_vs_group.emp_no)
AND (staff_vs_group.group_code = dept_dict.dept_code)
AND (staff_vs_group.group_class = '門診醫生');
xxxxxx> alter session set statistics_level=all;
Session altered.
xxxxxx> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 07drst9ks3xjm, child number 0
-------------------------------------
SELECT DISTINCT dept_dict.dept_name, staff_vs_group.group_code FROM dept_dict, staff_dict, staff_vs_group WHERE (staff_dict.emp_no =
staff_vs_group.emp_no) AND (staff_vs_group.group_code = dept_dict.dept_code) AND (staff_vs_group.group_class = '門診醫生')
Plan hash value: 3073008191
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | HASH UNIQUE | | 1 | 781 | 35145 | 6 (34)| 00:00:01 | 63 |00:00:00.01 | 941 | 795K| 795K| 1187K (0)|
| 2 | NESTED LOOPS | | 1 | 781 | 35145 | 5 (20)| 00:00:01 | 915 |00:00:00.01 | 941 | | | |
|* 3 | HASH JOIN | | 1 | 781 | 31240 | 5 (20)| 00:00:01 | 929 |00:00:00.01 | 10 | 877K| 877K| 1105K (0)|
| 4 | INDEX FAST FULL SCAN| I_DEPT_DICT_D_NAME_CODE_IN_C | 1 | 425 | 8500 | 2 (0)| 00:00:01 | 432 |00:00:00.01 | 7 | | | |
|* 5 | INDEX RANGE SCAN | PK_STAFF_VS_GROUP | 1 | 781 | 15620 | 2 (0)| 00:00:01 | 943 |00:00:00.01 | 3 | | | |
|* 6 | INDEX UNIQUE SCAN | PK_STAFF_DICT | 929 | 1 | 5 | 0 (0)| | 915 |00:00:00.01 | 931 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / DEPT_DICT@SEL$1
5 - SEL$1 / STAFF_VS_GROUP@SEL$1
6 - SEL$1 / STAFF_DICT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("STAFF_VS_GROUP"."GROUP_CODE"="DEPT_DICT"."DEPT_CODE")
5 - access("STAFF_VS_GROUP"."GROUP_CLASS"='門診醫生')
6 - access("STAFF_DICT"."EMP_NO"="STAFF_VS_GROUP"."EMP_NO")
--//邏輯讀941,問題主要出在第2步執行的NESTED LOOPS,導致邏輯讀上升.優化的問題是減少邏輯讀,減少響應時間.
--//加入提示use_hash(STAFF_DICT).
$ cat gf3wvnjzfnysy.sql
SELECT /*+ use_hash(STAFF_DICT) */ DISTINCT dept_dict.dept_name, staff_vs_group.group_code
FROM dept_dict, staff_dict, staff_vs_group
WHERE (staff_dict.emp_no = staff_vs_group.emp_no)
AND (staff_vs_group.group_code = dept_dict.dept_code)
AND (staff_vs_group.group_class = '門診醫生');
xxxxxx> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID c4mbqwykjp1n0, child number 0
-------------------------------------
SELECT /*+ use_hash(STAFF_DICT) */ DISTINCT dept_dict.dept_name, staff_vs_group.group_code FROM dept_dict, staff_dict, staff_vs_group WHERE
(staff_dict.emp_no = staff_vs_group.emp_no) AND (staff_vs_group.group_code = dept_dict.dept_code) AND (staff_vs_group.group_class =
'門診醫生')
Plan hash value: 1733666958
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | HASH UNIQUE | | 1 | 781 | 35145 | 9 (23)| 00:00:01 | 63 |00:00:00.01 | 23 | 795K| 795K| 1186K (0)|
|* 2 | HASH JOIN | | 1 | 781 | 35145 | 8 (13)| 00:00:01 | 915 |00:00:00.01 | 23 | 833K| 833K| 1139K (0)|
|* 3 | HASH JOIN | | 1 | 781 | 31240 | 5 (20)| 00:00:01 | 929 |00:00:00.01 | 10 | 877K| 877K| 1182K (0)|
| 4 | INDEX FAST FULL SCAN| I_DEPT_DICT_D_NAME_CODE_IN_C | 1 | 425 | 8500 | 2 (0)| 00:00:01 | 432 |00:00:00.01 | 7 | | | |
|* 5 | INDEX RANGE SCAN | PK_STAFF_VS_GROUP | 1 | 781 | 15620 | 2 (0)| 00:00:01 | 943 |00:00:00.01 | 3 | | | |
| 6 | INDEX FAST FULL SCAN | PK_STAFF_DICT | 1 | 3530 | 17650 | 3 (0)| 00:00:01 | 3544 |00:00:00.01 | 13 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
4 - SEL$1 / DEPT_DICT@SEL$1
5 - SEL$1 / STAFF_VS_GROUP@SEL$1
6 - SEL$1 / STAFF_DICT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STAFF_DICT"."EMP_NO"="STAFF_VS_GROUP"."EMP_NO")
3 - access("STAFF_VS_GROUP"."GROUP_CODE"="DEPT_DICT"."DEPT_CODE")
5 - access("STAFF_VS_GROUP"."GROUP_CLASS"='門診醫生')
--//邏輯讀下降到23,視乎優化完成.實際上如果你仔細看上面的sql,我個人現在對dinstinct很敏感,查詢的欄位是dept_dict.dept_name,
--//staff_vs_group.group_code. 而欄位staff_vs_group.group_code與這個欄位dept_dict.dept_code是一樣的,很明顯查詢僅僅是1個
--//表dept_dict,經典的使用extists例子.參考連線:http://www.itpub.net/thread-2048496-1-1.html
--//可以想像開發如下寫sql語句,把需要的表列出來,寫成連線需要的條件,然後寫出需要顯示的欄位,重複加一個distinct,ok解決問題.
--//正確的應該這樣寫.
$ cat gf3wvnjzfnysy.sql4
SELECT dept_dict.dept_name, dept_dict.dept_code
FROM dept_dict
WHERE EXISTS (
SELECT /*+ use_hash111(staff_vs_group staff_dict) */1
FROM staff_vs_group , staff_dict
WHERE staff_vs_group.group_code = dept_dict.dept_code
AND staff_vs_group.group_class = '門診醫生'
AND staff_dict.emp_no = staff_vs_group.emp_no
);
xxxxxx> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4mummndvnvj6m, child number 0
-------------------------------------
SELECT dept_dict.dept_name, dept_dict.dept_code FROM dept_dict WHERE EXISTS ( SELECT /*+ use_hash111(staff_vs_group staff_dict) */1
FROM staff_vs_group , staff_dict WHERE staff_vs_group.group_code = dept_dict.dept_code AND staff_vs_group.group_class = '門診醫生'
AND staff_dict.emp_no = staff_vs_group.emp_no )
Plan hash value: 1139953391
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN SEMI | | 1 | 2 | 52 | 5 (20)| 00:00:01 | 63 |00:00:00.01 | 956 | 877K| 877K| 1111K (0)|
| 2 | INDEX FAST FULL SCAN| I_DEPT_DICT_D_NAME_CODE_IN_C | 1 | 425 | 8500 | 2 (0)| 00:00:01 | 432 |00:00:00.01 | 7 | | | |
| 3 | VIEW | VW_SQ_1 | 1 | 781 | 4686 | 2 (0)| 00:00:01 | 929 |00:00:00.01 | 949 | | | |
| 4 | NESTED LOOPS | | 1 | 781 | 19525 | 2 (0)| 00:00:01 | 929 |00:00:00.01 | 949 | | | |
|* 5 | INDEX RANGE SCAN | PK_STAFF_VS_GROUP | 1 | 781 | 15620 | 2 (0)| 00:00:01 | 943 |00:00:00.01 | 4 | | | |
|* 6 | INDEX UNIQUE SCAN | PK_STAFF_DICT | 943 | 1 | 5 | 0 (0)| | 929 |00:00:00.01 | 945 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$C772B8D1
2 - SEL$C772B8D1 / DEPT_DICT@SEL$1
3 - SEL$683B0107 / VW_SQ_1@SEL$7511BFD2
4 - SEL$683B0107
5 - SEL$683B0107 / STAFF_VS_GROUP@SEL$2
6 - SEL$683B0107 / STAFF_DICT@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("GROUP_CODE"="DEPT_DICT"."DEPT_CODE")
5 - access("STAFF_VS_GROUP"."GROUP_CLASS"='門診醫生')
6 - access("STAFF_DICT"."EMP_NO"="STAFF_VS_GROUP"."EMP_NO")
--//邏輯讀956,有點高.加入提示/*+ use_hash(staff_vs_group staff_dict) */
$ cat gf3wvnjzfnysy.sql4
SELECT dept_dict.dept_name, dept_dict.dept_code
FROM dept_dict
WHERE EXISTS (
SELECT /*+ use_hash(staff_vs_group staff_dict) */1
FROM staff_vs_group , staff_dict
WHERE staff_vs_group.group_code = dept_dict.dept_code
AND staff_vs_group.group_class = '門診醫生'
AND staff_dict.emp_no = staff_vs_group.emp_no
);
xxxxxx> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9jqqu3djrdgvg, child number 0
-------------------------------------
SELECT dept_dict.dept_name, dept_dict.dept_code FROM dept_dict WHERE EXISTS ( SELECT /*+ use_hash(staff_vs_group staff_dict) */1 FROM
staff_vs_group , staff_dict WHERE staff_vs_group.group_code = dept_dict.dept_code AND staff_vs_group.group_class = '門診醫生'
AND staff_dict.emp_no = staff_vs_group.emp_no )
Plan hash value: 335906791
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN SEMI | | 1 | 2 | 52 | 8 (13)| 00:00:01 | 63 |00:00:00.01 | 24 | 877K| 877K| 1166K (0)|
| 2 | INDEX FAST FULL SCAN | I_DEPT_DICT_D_NAME_CODE_IN_C | 1 | 425 | 8500 | 2 (0)| 00:00:01 | 432 |00:00:00.01 | 7 | | | |
| 3 | VIEW | VW_SQ_1 | 1 | 781 | 4686 | 6 (17)| 00:00:01 | 929 |00:00:00.01 | 17 | | | |
|* 4 | HASH JOIN | | 1 | 781 | 19525 | 6 (17)| 00:00:01 | 929 |00:00:00.01 | 17 | 972K| 972K| 1207K (0)|
|* 5 | INDEX RANGE SCAN | PK_STAFF_VS_GROUP | 1 | 781 | 15620 | 2 (0)| 00:00:01 | 943 |00:00:00.01 | 3 | | | |
| 6 | INDEX FAST FULL SCAN| PK_STAFF_DICT | 1 | 3530 | 17650 | 3 (0)| 00:00:01 | 3544 |00:00:00.01 | 14 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$C772B8D1
2 - SEL$C772B8D1 / DEPT_DICT@SEL$1
3 - SEL$683B0107 / VW_SQ_1@SEL$7511BFD2
4 - SEL$683B0107
5 - SEL$683B0107 / STAFF_VS_GROUP@SEL$2
6 - SEL$683B0107 / STAFF_DICT@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("GROUP_CODE"="DEPT_DICT"."DEPT_CODE")
4 - access("STAFF_DICT"."EMP_NO"="STAFF_VS_GROUP"."EMP_NO")
5 - access("STAFF_VS_GROUP"."GROUP_CLASS"='門診醫生')
--//邏輯讀24.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2131844/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL優化--用各種hints優化一條SQLSQL優化
- [20181114]一條sql語句的優化.txtSQL優化
- [20131025]一條sql語句的優化.txtSQL優化
- [20151203]一條sql語句的優化.txtSQL優化
- [20150715]一條sql語句的優化.txtSQL優化
- [20120319]一條sql語句的優化.txtSQL優化
- [20130319]一條sql語句的優化.txtSQL優化
- 一條sql的優化過程SQL優化
- 一條sql語句的優化SQL優化
- SQL優化34條SQL優化
- 從一條問題SQL優化看SQL TransformationSQL優化ORM
- [20151209]一條sql語句的優化(續).txtSQL優化
- 一條SQL語句的優化過程SQL優化
- SQL優化經驗總結34條(一)SQL優化
- [20140210]一條sql語句的優化(11g).txtSQL優化
- 一條sql語句優化不出來, 哭了一鼻子SQL優化
- 通過新增條件優化SQL優化SQL
- 一條大sql的調優SQL
- SQL優化（一）SQL優化
- SQL 優化經驗總結34條SQL優化
- SQL語句優化－－十條經驗SQL優化
- SQL優化經驗總結34條SQL優化
- 一個SQL優化SQL優化
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- 對一條基於分割槽的簡單SQL的優化SQL優化
- Oracle SQL效能優化的40條軍規OracleSQL優化
- 詳解SQL效能優化十條經驗SQL優化
- 34條簡單的SQL優化準則SQL優化
- SQL優化經驗總結34條(二)SQL優化
- oracle優化一例之sql優化Oracle優化SQL
- 通過_optimizer_rownum_pred_based_fkr優化一條sql語句優化SQL
- 一條簡單的SQL語句優化-新年新氣象SQL優化
- MySQL SQL優化案例(一)MySql優化
- 一個sql的優化SQL優化
- oracle sql like優化（一）OracleSQL優化
- ORACLE SQL過濾條件是IS NULL or !=的優化OracleSQLNull優化
- 高手詳解SQL效能優化十條經驗SQL優化
- 總結出10條SQL語句優化精髓SQL優化