一個sql的優化
處理了一起sql優化
[code]
SELECT
*
FROM
(
SELECT
f.name AS variance_name,
a.*,
(a.last_count_order-a.count_order) AS rise_diff ,
b.name AS t_name,
b.parent_id AS t_parent_id,
b.forum_dir,
b.forum_id AS t_forum_id,
b.pub_url AS t_pub_url,
b.pub_dir ,
d.price AS area_price ,
NVL(e.min_price,0) AS min_price,
NVL(e.max_price,0) AS max_price
FROM
PDL_PRODUCT a,
PDL_PRODUCT_TYPE b ,
(
SELECT
*
FROM
PP_PRICE
WHERE
PRICE BETWEEN 501 AND 1000
AND AREA_ID = 100
)
d,
eml_price e ,
pdl_product_variance f
WHERE
a.status! =0
AND a.review_status<>4
AND a.order_type <2
AND a.concept IS NULL
AND a.type_id =b.id
AND b.status =1
AND b.type =3
AND b.parent_id =20811
AND a.id =d.product_id
AND e.product_id(+) = a.id
AND a.variance_id =f.id(+)
ORDER BY
a.last_count_order ASC
)
WHERE
rownum <= 300;
[/code]對應的執行計劃：
Plan hash value: 1304873736
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 300 | 624K| 6207 (1)| 00:01:15 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 613 | 1275K| 6207 (1)| 00:01:15 |
|* 3 | SORT ORDER BY STOPKEY | | 613 | 373K| 6207 (1)| 00:01:15 |
|* 4 | HASH JOIN OUTER | | 613 | 373K| 6206 (1)| 00:01:15 |
| 5 | NESTED LOOPS OUTER | | 613 | 366K| 6148 (1)| 00:01:14 |
|* 6 | HASH JOIN | | 613 | 352K| 6132 (1)| 00:01:14 |
|* 7 | INDEX FAST FULL SCAN | IDX_PP_PRIC_PRD | 14923 | 174K| 2060 (3)| 00:00:25 |
|* 8 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_BASE | 44 | 21252 | 33 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 9967 | 5616K| 4071 (1)| 00:00:49 |
|* 10 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_TYPE | 225 | 21150 | 96 (2)| 00:00:02 |
| 11 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 12 | BITMAP AND | | | | | |
| 13 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 14 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_I2 | | | 1 (0)| 00:00:01 |
| 15 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 16 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_I1 | | | 26 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_ID | 56 | | 2 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_VARIANCE | 1 | 23 | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PDL_PRODUCT_VARIOUS_U1 | 1 | | 0 (0)| 00:00:01 |
| 20 | TABLE ACCESS FULL | EML_PRICE | 88782 | 1040K| 57 (4)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
可以看到cost 花了6207 是一個比較差的計劃
可以看到大部分的效能都消耗在 hash join 上
修改後的執行計劃：
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 12786 | 167 (1)| 00:00:03 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 6 | 12786 | 167 (1)| 00:00:03 |
|* 3 | SORT ORDER BY STOPKEY | | 6 | 3678 | 167 (1)| 00:00:03 |
| 4 | NESTED LOOPS OUTER | | 6 | 3678 | 166 (0)| 00:00:02 |
| 5 | NESTED LOOPS OUTER | | 6 | 3606 | 164 (0)| 00:00:02 |
| 6 | NESTED LOOPS | | 6 | 3468 | 163 (0)| 00:00:02 |
| 7 | NESTED LOOPS | | 94 | 53204 | 46 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_TYPE | 2 | 166 | 11 (0)| 00:00:01 |
| 9 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 10 | BITMAP AND | | | | | |
| 11 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 12 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_I2 | 26 | | 1 (0)| 00:00:01 |
| 13 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 14 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_I1 | 26 | | 8 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_BASE | 44 | 21252 | 33 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_ID | 56 | | 2 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | PP_PRICE | 1 | 12 | 2 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PP_PRICE_PK | 1 | | 1 (0)| 00:00:01 |
| 19 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_VARIANCE | 1 | 23 | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PDL_PRODUCT_VARIOUS_U1 | 1 | | 0 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | EML_PRICE | 1 | 12 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | PK_EML_PRICE | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
執行cost 從6200 降到了167
這裡看到計劃從hash join 變為了neted loop
這裡還有一個點，當然針對目前這個sql 可能意義不太大，但是針對產品庫意義就很大了
那就是sql 程式碼裡大量的常量沒有適用繫結變數 ，導致目前sql版本很多。
對於表關聯，如果量表的資料量都比較大，hash join 不是一個好方案， 應該儘量安排走neted loop
而如果其中一個表的資料量比較小，hash join 是優於neted loop
這裡的計劃裡，都出現了bitmap convertion to rowid
這個計劃是oracle 希望儘可能的利用索引，少回表的一個策略，一般是兩個條件都存在索引，這種情況先，我們可以考慮建立組合索引。
bitmap convertion 一般認為不是很好的計劃，（這個我不能確定對錯，自己決定吧）
bitmap 這個可以考慮 引數 _b_tree_bitmap_plans=false
下面列出加了組合索引的執行計劃：
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 12786 | 169 (1)| 00:00:03 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 6 | 12786 | 169 (1)| 00:00:03 |
|* 3 | SORT ORDER BY STOPKEY | | 6 | 3678 | 169 (1)| 00:00:03 |
| 4 | NESTED LOOPS OUTER | | 6 | 3678 | 168 (0)| 00:00:03 |
| 5 | NESTED LOOPS OUTER | | 6 | 3606 | 166 (0)| 00:00:02 |
| 6 | NESTED LOOPS | | 6 | 3468 | 165 (0)| 00:00:02 |
| 7 | NESTED LOOPS | | 94 | 53204 | 48 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| PDL_PRODUCT_TYPE | 2 | 166 | 13 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IDX_PDL_PRD_TYP_STAT_PARID_TYP | 18 | | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| PDL_PRODUCT_BASE | 44 | 21252 | 33 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_ID | 56 | | 2 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | PP_PRICE | 1 | 12 | 2 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PP_PRICE_PK | 1 | | 1 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_VARIANCE | 1 | 23 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PDL_PRODUCT_VARIOUS_U1 | 1 | | 0 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | EML_PRICE | 1 | 12 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_EML_PRICE | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
[code]
SELECT
*
FROM
(
SELECT
f.name AS variance_name,
a.*,
(a.last_count_order-a.count_order) AS rise_diff ,
b.name AS t_name,
b.parent_id AS t_parent_id,
b.forum_dir,
b.forum_id AS t_forum_id,
b.pub_url AS t_pub_url,
b.pub_dir ,
d.price AS area_price ,
NVL(e.min_price,0) AS min_price,
NVL(e.max_price,0) AS max_price
FROM
PDL_PRODUCT a,
PDL_PRODUCT_TYPE b ,
(
SELECT
*
FROM
PP_PRICE
WHERE
PRICE BETWEEN 501 AND 1000
AND AREA_ID = 100
)
d,
eml_price e ,
pdl_product_variance f
WHERE
a.status! =0
AND a.review_status<>4
AND a.order_type <2
AND a.concept IS NULL
AND a.type_id =b.id
AND b.status =1
AND b.type =3
AND b.parent_id =20811
AND a.id =d.product_id
AND e.product_id(+) = a.id
AND a.variance_id =f.id(+)
ORDER BY
a.last_count_order ASC
)
WHERE
rownum <= 300;
[/code]對應的執行計劃：
Plan hash value: 1304873736
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 300 | 624K| 6207 (1)| 00:01:15 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 613 | 1275K| 6207 (1)| 00:01:15 |
|* 3 | SORT ORDER BY STOPKEY | | 613 | 373K| 6207 (1)| 00:01:15 |
|* 4 | HASH JOIN OUTER | | 613 | 373K| 6206 (1)| 00:01:15 |
| 5 | NESTED LOOPS OUTER | | 613 | 366K| 6148 (1)| 00:01:14 |
|* 6 | HASH JOIN | | 613 | 352K| 6132 (1)| 00:01:14 |
|* 7 | INDEX FAST FULL SCAN | IDX_PP_PRIC_PRD | 14923 | 174K| 2060 (3)| 00:00:25 |
|* 8 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_BASE | 44 | 21252 | 33 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 9967 | 5616K| 4071 (1)| 00:00:49 |
|* 10 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_TYPE | 225 | 21150 | 96 (2)| 00:00:02 |
| 11 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 12 | BITMAP AND | | | | | |
| 13 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 14 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_I2 | | | 1 (0)| 00:00:01 |
| 15 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 16 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_I1 | | | 26 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_ID | 56 | | 2 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_VARIANCE | 1 | 23 | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PDL_PRODUCT_VARIOUS_U1 | 1 | | 0 (0)| 00:00:01 |
| 20 | TABLE ACCESS FULL | EML_PRICE | 88782 | 1040K| 57 (4)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------
可以看到cost 花了6207 是一個比較差的計劃
可以看到大部分的效能都消耗在 hash join 上
修改後的執行計劃：
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 12786 | 167 (1)| 00:00:03 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 6 | 12786 | 167 (1)| 00:00:03 |
|* 3 | SORT ORDER BY STOPKEY | | 6 | 3678 | 167 (1)| 00:00:03 |
| 4 | NESTED LOOPS OUTER | | 6 | 3678 | 166 (0)| 00:00:02 |
| 5 | NESTED LOOPS OUTER | | 6 | 3606 | 164 (0)| 00:00:02 |
| 6 | NESTED LOOPS | | 6 | 3468 | 163 (0)| 00:00:02 |
| 7 | NESTED LOOPS | | 94 | 53204 | 46 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_TYPE | 2 | 166 | 11 (0)| 00:00:01 |
| 9 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 10 | BITMAP AND | | | | | |
| 11 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 12 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_I2 | 26 | | 1 (0)| 00:00:01 |
| 13 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 14 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_I1 | 26 | | 8 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_BASE | 44 | 21252 | 33 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_ID | 56 | | 2 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | PP_PRICE | 1 | 12 | 2 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PP_PRICE_PK | 1 | | 1 (0)| 00:00:01 |
| 19 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_VARIANCE | 1 | 23 | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PDL_PRODUCT_VARIOUS_U1 | 1 | | 0 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | EML_PRICE | 1 | 12 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | PK_EML_PRICE | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
執行cost 從6200 降到了167
這裡看到計劃從hash join 變為了neted loop
這裡還有一個點，當然針對目前這個sql 可能意義不太大，但是針對產品庫意義就很大了
那就是sql 程式碼裡大量的常量沒有適用繫結變數 ，導致目前sql版本很多。
對於表關聯，如果量表的資料量都比較大，hash join 不是一個好方案， 應該儘量安排走neted loop
而如果其中一個表的資料量比較小，hash join 是優於neted loop
這裡的計劃裡，都出現了bitmap convertion to rowid
這個計劃是oracle 希望儘可能的利用索引，少回表的一個策略，一般是兩個條件都存在索引，這種情況先，我們可以考慮建立組合索引。
bitmap convertion 一般認為不是很好的計劃，（這個我不能確定對錯，自己決定吧）
bitmap 這個可以考慮 引數 _b_tree_bitmap_plans=false
下面列出加了組合索引的執行計劃：
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 12786 | 169 (1)| 00:00:03 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 6 | 12786 | 169 (1)| 00:00:03 |
|* 3 | SORT ORDER BY STOPKEY | | 6 | 3678 | 169 (1)| 00:00:03 |
| 4 | NESTED LOOPS OUTER | | 6 | 3678 | 168 (0)| 00:00:03 |
| 5 | NESTED LOOPS OUTER | | 6 | 3606 | 166 (0)| 00:00:02 |
| 6 | NESTED LOOPS | | 6 | 3468 | 165 (0)| 00:00:02 |
| 7 | NESTED LOOPS | | 94 | 53204 | 48 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID| PDL_PRODUCT_TYPE | 2 | 166 | 13 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | IDX_PDL_PRD_TYP_STAT_PARID_TYP | 18 | | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID| PDL_PRODUCT_BASE | 44 | 21252 | 33 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | PDL_PRODUCT_TYPE_ID | 56 | | 2 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | PP_PRICE | 1 | 12 | 2 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PP_PRICE_PK | 1 | | 1 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | PDL_PRODUCT_VARIANCE | 1 | 23 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PDL_PRODUCT_VARIOUS_U1 | 1 | | 0 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | EML_PRICE | 1 | 12 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_EML_PRICE | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/133735/viewspace-703722/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 一個SQL優化SQL優化
- 一個SQL語句的優化SQL優化
- 記一個SQL優化案例SQL優化
- 一個SQL效能問題的優化探索SQL優化
- MYSQL 阿里的一個sql優化問題MySql阿里優化
- 優化同事發過來的一個sql優化SQL
- SQL優化（一）SQL優化
- [轉]轉一個關於優化sql的文章優化SQL
- SQL優化--用各種hints優化一條SQLSQL優化
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- 幫朋友優化個sql優化SQL
- 如何優化這個sql？優化SQL
- oracle優化一例之sql優化Oracle優化SQL
- 一條sql的優化過程SQL優化
- 一條sql語句的優化SQL優化
- SQL優化引出的問題（一）SQL優化
- MySQL幾個簡單SQL的優化MySql優化
- 【SQL Server 優化效能的幾個方面】SQLServer優化
- MySQL SQL優化案例(一)MySql優化
- oracle sql like優化（一）OracleSQL優化
- 8個SQL講解優化SQL優化
- 【SQL優化】SQL優化的10點注意事項SQL優化
- ORACLE SQL效能優化系列 (一)OracleSQL優化
- 記一次sql優化SQL優化
- PL/SQL優化一例SQL優化
- 一個效能優化的案例優化
- 下週開始做sql優化的一個內部培訓SQL優化
- 【SQL優化】SQL優化工具SQL優化
- mysql的sql優化MySql優化
- 優化SQL中的or優化SQL
- SQL Server優化之SQL語句優化SQLServer優化
- zt_如何用一個表的兩個以上索引訪問表_sql tuning_sql優化索引SQL優化
- 一條SQL語句的優化過程SQL優化
- 一次sql語句優化的反思SQL優化
- SQL優化SQL優化
- with as優化sql優化SQL
- 效能優化案例-SQL優化優化SQL
- MySQL之SQL優化詳解（一）MySql優化