生產環境sql語句調優實戰第五篇
今天在生產環境中發現一條sql語句儘管走了主鍵索引，但是查詢還是很慢。
sql語句類似下面的形式：
SELECT /*+ index (bl1_cyc_payer_pop BL1_CYC_PAYER_POP_PK) */
T_TAX.BA_NO,
T_TAX.TOTAL_TAX_AMT,
T_TAX.TAX_RELATION,
T_TAX_ITEM.TAX_ITEM_SEQ_NO,
T_TAX_ITEM.TAX_SEQ_NO,
T_TAX_ITEM.TAX_AUTHORITY,
T_TAX_ITEM.TAX_TYPE,
T_TAX_ITEM.TAX_RATE,
T_TAX_ITEM.TAX_AMOUNT,
T_TAX_ITEM.TAXABLE_AMOUNT,
.......
FROM T_TAX, T_TAX_ITEM, T_DOCUMENT, T_CYC_PAYER_POP --這幾張都是大表，少則500萬左右，多則1000多萬。
WHERE T_TAX.TAX_ITEM_PERIOD_KEY = T_TAX_ITEM.PERIOD_KEY
AND T_TAX.CUSTOMER_KEY = T_CYC_PAYER_POP.CUSTOMER_KEY
AND T_TAX.BA_NO = T_CYC_PAYER_POP.BA_NO
AND T_TAX.CYCLE_SEQ_NO = T_CYC_PAYER_POP.CYCLE_SEQ_NO
AND T_TAX.CYCLE_SEQ_RUN = T_CYC_PAYER_POP.CYCLE_SEQ_RUN
AND T_TAX.PERIOD_KEY = T_CYC_PAYER_POP.PERIOD_KEY
AND T_TAX_ITEM.CUSTOMER_KEY = T_CYC_PAYER_POP.CUSTOMER_KEY
AND T_TAX_ITEM.TAX_SEQ_NO = T_TAX.TAX_SEQ_NO
AND T_DOCUMENT.PERIOD_KEY = T_CYC_PAYER_POP.PERIOD_KEY
AND T_DOCUMENT.CUSTOMER_KEY = T_CYC_PAYER_POP.CUSTOMER_KEY
AND T_DOCUMENT.BA_NO = T_CYC_PAYER_POP.BA_NO
AND T_DOCUMENT.CYCLE_SEQ_NO = T_CYC_PAYER_POP.CYCLE_SEQ_NO
AND T_DOCUMENT.CYCLE_SEQ_RUN = T_CYC_PAYER_POP.CYCLE_SEQ_RUN
AND T_DOCUMENT.DOC_PRODUCE_IND IN ('Y', 'E')
AND T_CYC_PAYER_POP.CUSTOMER_KEY = 78
AND T_CYC_PAYER_POP.PERIOD_KEY = 55
AND T_CYC_PAYER_POP.QA_GROUP = 3
AND T_CYC_PAYER_POP.CYCLE_SEQ_NO = 2925
檢視該sql的執行計劃
發現有嚴重的io問題，瓶頸就在於使用的primary key對應的index
這個問題很值得深究，完全可以使用如下的方式來驗證。我嘗試使用pk的Hint，另外不加任何hint，看錶查詢的時候會不會使用index
-->使用hint強制走主鍵查詢
Plan hash value: 3105767292
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2501 (1)| 00:00:31 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 12 | 2501 (1)| 00:00:31 | 171 | 171 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T_CYC_PAYER_POP | 1 | 12 | 2501 (1)| 00:00:31 | 171 | 171 |
|* 3 | INDEX FULL SCAN | T_CYC_PAYER_POP_PK | 541 | | 2444 (1)| 00:00:30 | 171 | 171 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T_CYC_PAYER_POP"."QA_GROUP"=3)
3 - access("T_CYC_PAYER_POP"."CYCLE_SEQ_NO"=2925 AND "T_CYC_PAYER_POP"."PERIOD_KEY"=55 AND
"T_CYC_PAYER_POP"."CUSTOMER_KEY"=78)
filter("T_CYC_PAYER_POP"."CUSTOMER_KEY"=78 AND "T_CYC_PAYER_POP"."CYCLE_SEQ_NO"=2925 AND
"T_CYC_PAYER_POP"."PERIOD_KEY"=55)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
31395 consistent gets
0 physical reads
0 redo size
910 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
-->來看看不使用hint之後，發生了什麼
select T_CYC_PAYER_POP.CUSTOMER_KEY,
T_CYC_PAYER_POP.PERIOD_KEY,
T_CYC_PAYER_POP.QA_GROUP ,
T_CYC_PAYER_POP.CYCLE_SEQ_NO
from T_CYC_PAYER_POP where T_CYC_PAYER_POP.CUSTOMER_KEY = 78
AND T_CYC_PAYER_POP.PERIOD_KEY = 55
AND T_CYC_PAYER_POP.QA_GROUP = 3
AND T_CYC_PAYER_POP.CYCLE_SEQ_NO = 2925
/
Execution Plan
----------------------------------------------------------
Plan hash value: 23637115
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 12 | 2 (0)| 00:00:01 | 171 | 171 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T_CYC_PAYER_POP | 1 | 12 | 2 (0)| 00:00:01 | 171 | 171 |
|* 3 | INDEX RANGE SCAN | T_CYC_PAYER_POP_5IX | 535 | | 1 (0)| 00:00:01 | 171 | 171 |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T_CYC_PAYER_POP"."CUSTOMER_KEY"=78 AND "T_CYC_PAYER_POP"."PERIOD_KEY"=55)
3 - access("T_CYC_PAYER_POP"."QA_GROUP"=3 AND "T_CYC_PAYER_POP"."CYCLE_SEQ_NO"=2925)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
951 consistent gets
644 physical reads
80 redo size
910 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
啟用了index range scan,而且從執行計劃和統計資訊來看，明顯要比全索引掃描效率高得多。
可以看到使用index range scan之後，先查詢了索引列的資訊，然後無法走索引過濾了其他的條件。根據目前的資料情況，這個效率要比全索引效率還高的多。
以下是做了hint的改動之後，統計資訊的情況，可以看到明顯的改善。對於這個Hint的細節需要和客戶做更多的確認，畢竟對於調優不能越調越差，穩定和高效才是關鍵。
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1178 consistent gets
756 physical reads
0 redo size
3229 bytes sent via SQL*Net to client
553 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
58 rows processed
sql語句類似下面的形式：
SELECT /*+ index (bl1_cyc_payer_pop BL1_CYC_PAYER_POP_PK) */
T_TAX.BA_NO,
T_TAX.TOTAL_TAX_AMT,
T_TAX.TAX_RELATION,
T_TAX_ITEM.TAX_ITEM_SEQ_NO,
T_TAX_ITEM.TAX_SEQ_NO,
T_TAX_ITEM.TAX_AUTHORITY,
T_TAX_ITEM.TAX_TYPE,
T_TAX_ITEM.TAX_RATE,
T_TAX_ITEM.TAX_AMOUNT,
T_TAX_ITEM.TAXABLE_AMOUNT,
.......
FROM T_TAX, T_TAX_ITEM, T_DOCUMENT, T_CYC_PAYER_POP --這幾張都是大表，少則500萬左右，多則1000多萬。
WHERE T_TAX.TAX_ITEM_PERIOD_KEY = T_TAX_ITEM.PERIOD_KEY
AND T_TAX.CUSTOMER_KEY = T_CYC_PAYER_POP.CUSTOMER_KEY
AND T_TAX.BA_NO = T_CYC_PAYER_POP.BA_NO
AND T_TAX.CYCLE_SEQ_NO = T_CYC_PAYER_POP.CYCLE_SEQ_NO
AND T_TAX.CYCLE_SEQ_RUN = T_CYC_PAYER_POP.CYCLE_SEQ_RUN
AND T_TAX.PERIOD_KEY = T_CYC_PAYER_POP.PERIOD_KEY
AND T_TAX_ITEM.CUSTOMER_KEY = T_CYC_PAYER_POP.CUSTOMER_KEY
AND T_TAX_ITEM.TAX_SEQ_NO = T_TAX.TAX_SEQ_NO
AND T_DOCUMENT.PERIOD_KEY = T_CYC_PAYER_POP.PERIOD_KEY
AND T_DOCUMENT.CUSTOMER_KEY = T_CYC_PAYER_POP.CUSTOMER_KEY
AND T_DOCUMENT.BA_NO = T_CYC_PAYER_POP.BA_NO
AND T_DOCUMENT.CYCLE_SEQ_NO = T_CYC_PAYER_POP.CYCLE_SEQ_NO
AND T_DOCUMENT.CYCLE_SEQ_RUN = T_CYC_PAYER_POP.CYCLE_SEQ_RUN
AND T_DOCUMENT.DOC_PRODUCE_IND IN ('Y', 'E')
AND T_CYC_PAYER_POP.CUSTOMER_KEY = 78
AND T_CYC_PAYER_POP.PERIOD_KEY = 55
AND T_CYC_PAYER_POP.QA_GROUP = 3
AND T_CYC_PAYER_POP.CYCLE_SEQ_NO = 2925
檢視該sql的執行計劃
發現有嚴重的io問題，瓶頸就在於使用的primary key對應的index
| Id | Operation | Name |
Estimated Rows |
Cost |
Active Period (56s) |
Execs | Rows | Memory | Temp | IO Requests | CPU Activity | Wait Activity | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
. |
0 | SELECT STATEMENT |
. |
. |
. |
|
1 | 67 |
. |
. |
|
. |
. |
||||||||||
|
. |
1 | . NESTED LOOPS |
. |
. |
. |
|
1 | 67 |
. |
. |
|
. |
. |
||||||||||
|
. |
2 | .. NESTED LOOPS |
. |
1 | 2447 |
|
1 | 67 |
. |
. |
|
. |
. |
||||||||||
|
. |
3 | ... NESTED LOOPS |
. |
1 | 2446 |
|
1 | 67 |
. |
. |
|
. |
. |
||||||||||
|
. |
4 | .... NESTED LOOPS |
. |
1 | 2445 |
|
1 | 9 |
. |
. |
|
. |
. |
||||||||||
|
. |
5 | .....PARTITION RANGE SINGLE |
. |
1 | 2444 |
|
1 | 9 |
. |
. |
|
. |
. |
||||||||||
|
. |
6 | ...... TABLE ACCESS BY LOCAL INDEX ROWID | CYC_PAYER_POP | 1 | 2444 |
|
1 | 9 |
. |
. |
|
. |
. |
||||||||||
| -> | 7 | .......INDEX FULL SCAN | CYC_PAYER_POP_PK | 1 | 2444 |
|
1 | 793 |
. |
. |
|
|
|
||||||||||
這個問題很值得深究，完全可以使用如下的方式來驗證。我嘗試使用pk的Hint，另外不加任何hint，看錶查詢的時候會不會使用index
-->使用hint強制走主鍵查詢
Plan hash value: 3105767292
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2501 (1)| 00:00:31 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 12 | 2501 (1)| 00:00:31 | 171 | 171 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T_CYC_PAYER_POP | 1 | 12 | 2501 (1)| 00:00:31 | 171 | 171 |
|* 3 | INDEX FULL SCAN | T_CYC_PAYER_POP_PK | 541 | | 2444 (1)| 00:00:30 | 171 | 171 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T_CYC_PAYER_POP"."QA_GROUP"=3)
3 - access("T_CYC_PAYER_POP"."CYCLE_SEQ_NO"=2925 AND "T_CYC_PAYER_POP"."PERIOD_KEY"=55 AND
"T_CYC_PAYER_POP"."CUSTOMER_KEY"=78)
filter("T_CYC_PAYER_POP"."CUSTOMER_KEY"=78 AND "T_CYC_PAYER_POP"."CYCLE_SEQ_NO"=2925 AND
"T_CYC_PAYER_POP"."PERIOD_KEY"=55)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
31395 consistent gets
0 physical reads
0 redo size
910 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
-->來看看不使用hint之後，發生了什麼
select T_CYC_PAYER_POP.CUSTOMER_KEY,
T_CYC_PAYER_POP.PERIOD_KEY,
T_CYC_PAYER_POP.QA_GROUP ,
T_CYC_PAYER_POP.CYCLE_SEQ_NO
from T_CYC_PAYER_POP where T_CYC_PAYER_POP.CUSTOMER_KEY = 78
AND T_CYC_PAYER_POP.PERIOD_KEY = 55
AND T_CYC_PAYER_POP.QA_GROUP = 3
AND T_CYC_PAYER_POP.CYCLE_SEQ_NO = 2925
/
Execution Plan
----------------------------------------------------------
Plan hash value: 23637115
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 12 | 2 (0)| 00:00:01 | 171 | 171 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T_CYC_PAYER_POP | 1 | 12 | 2 (0)| 00:00:01 | 171 | 171 |
|* 3 | INDEX RANGE SCAN | T_CYC_PAYER_POP_5IX | 535 | | 1 (0)| 00:00:01 | 171 | 171 |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T_CYC_PAYER_POP"."CUSTOMER_KEY"=78 AND "T_CYC_PAYER_POP"."PERIOD_KEY"=55)
3 - access("T_CYC_PAYER_POP"."QA_GROUP"=3 AND "T_CYC_PAYER_POP"."CYCLE_SEQ_NO"=2925)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
951 consistent gets
644 physical reads
80 redo size
910 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
啟用了index range scan,而且從執行計劃和統計資訊來看，明顯要比全索引掃描效率高得多。
可以看到使用index range scan之後，先查詢了索引列的資訊，然後無法走索引過濾了其他的條件。根據目前的資料情況，這個效率要比全索引效率還高的多。
以下是做了hint的改動之後，統計資訊的情況，可以看到明顯的改善。對於這個Hint的細節需要和客戶做更多的確認，畢竟對於調優不能越調越差，穩定和高效才是關鍵。
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1178 consistent gets
756 physical reads
0 redo size
3229 bytes sent via SQL*Net to client
553 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
58 rows processed
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8494287/viewspace-1347085/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 生產環境sql語句調優實戰第二篇SQL
- 生產環境sql語句調優實戰第三篇SQL
- 生產環境sql語句調優實戰第四篇SQL
- 生產環境sql語句調優實戰第六篇SQL
- 生產環境sql語句調優實戰第八篇SQL
- 生產環境sql語句調優實戰第九篇SQL
- 生產環境sql語句調優實戰第七篇SQL
- 生產環境sql語句調優實戰第十篇SQL
- 生產環境大型sql語句調優實戰第一篇（一）SQL
- 生產環境大型sql語句調優實戰第一篇（二）SQL
- kafka生產環境規劃-kafka 商業環境實戰Kafka
- 使用hint來調優sql語句SQL
- 實戰生產環境vCenter HA配置（VCSA6.5）
- kafka生產者Producer引數設定及引數調優建議-kafka 商業環境實戰Kafka
- Eureka：生產環境優化總結。優化
- 生產系統pl/sql調優案例SQL
- MySQL調優篇 | SQL調優實戰（5）MySql
- oracle 對比sql語句執行環境OracleSQL
- SQL 語句調優_減少或者避免笛卡爾乘積的發生SQL
- 生產sql調優之統計資訊分析SQL
- 透過使用hint unnest調優sql語句SQL
- 通過使用hint unnest調優sql語句SQL
- 生產SQL語句突然變慢問題定位SQL
- 一條sql語句的建議調優分析SQL
- redmine生產環境搭建
- Django生產環境搭建Django
- SQL語句優化SQL優化
- 使用USE_HASH Hint調優一個SQL語句SQL
- 使用SQL調整顧問進行語句優化SQL優化
- HBase 核心元件協調及RegionServer JVM引數調優-OLAP商業環境實戰元件ServerJVM
- Vue 移動端專案生產環境的優化Vue優化
- Java生產環境效能監控與調優—基於JDK命令列工具的監控JavaJDK命令列
- MYSQL SQL語句優化MySql優化
- sql語句效能優化SQL優化
- SQL語句的優化SQL優化
- 求助：SQL語句優化SQL優化
- SQL Server優化之SQL語句優化SQLServer優化
- soar-PHP - SQL 語句優化器和重寫器的 PHP 擴充套件包、 方便框架中 SQL 語句調優PHPSQL優化套件框架