一次SQL分析
問題描述：
類似語句如下：
with t as
(select trans_code, trans_amount, direction, settle_channel_api
from acctrans_account_log
where account_no = '20130606000000589266' and raw_add_time>= sysdate-1000 and raw_add_time
t2 as
(select nvl(sum(decode(t.direction, 'DEBIT_DIRECTION', TRANS_AMOUNT)), 0) trans_out,
nvl(sum(decode(t.direction, 'CREDIT_DIRECTION', TRANS_AMOUNT)), 0) trans_in
from t
where t.trans_code like '3%'
or t.trans_code like '7%'
or t.trans_code like '2%'
or t.trans_code like '5%1'
--or t.trans_code in ('900003', '900013', '900006', '900016')
),
t7 as
(select sum(trans_out) trans_out, sum(trans_in) trans_in
from (select nvl(sum(decode(t.direction,
'CREDIT_DIRECTION',
-TRANS_AMOUNT)),
0) trans_out,
nvl(sum(decode(t.direction, 'DEBIT_DIRECTION', -TRANS_AMOUNT)),
0) trans_in
from t
where t.trans_code in ('900003', '900013', '900006', '900016')
union all
select * from t2)),
t3 as
(select nvl(sum(decode(direction,
'CREDIT_DIRECTION',
trans_amount,
-trans_amount)),
0) deposit
from t
where t.trans_code like '4%'
or t.trans_code in ('900001', '900002', '900011', '900012')),
t4 as
(select nvl(sum(decode(direction,
'CREDIT_DIRECTION',
-trans_amount,
trans_amount)),
0) deposit_back
from t
where t.trans_code like '5%'and t.trans_code not like '5%1'
or t.trans_code in ('900007', '900017')),
t5 as
(select nvl(sum(decode(direction,
'CREDIT_DIRECTION',
-trans_amount,
trans_amount)),
0) WITHDRAW
from t
where t.trans_code like '6%'
or t.trans_code in ('900004', '900014', '900005', '900015')),
t6 as
(select nvl(sum(decode(direction,
'CREDIT_DIRECTION',
trans_amount,
-trans_amount)),
0) WITHDRAW_back
from t
where t.trans_code like '8%'
or t.trans_code in ('900008', '900018'))
select * from t3, t4, t5, t6, t7;
這個語句的執行計劃中發現
select trans_code, trans_amount, direction, settle_channel_api
from acctrans_account_log
where account_no = '20130606000000589266' and raw_add_time>= sysdate-1000 and raw_add_time
在不改變執行計劃的情況下，使用COUNT(*)進行替換，注意COUNT(*)可能改變執行計劃，
select count(*)
from acctrans_account_log
where account_no = '20130606000000589266' and raw_add_time>= sysdate-1000 and raw_add_time
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 2wdwnwqas1r8n, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from
accountant.acctrans_account_log where account_no =
'20120810000000006777' and raw_add_time>= sysdate-1000 and
raw_add_time
Plan hash value: 2145504150
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:02.51 | 77003 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:02.51 | 77003 |
|* 2 | FILTER | | 1 | | 1666K|00:00:02.88 | 77003 |
| 3 | PARTITION HASH SINGLE | | 1 | 389 | 1666K|00:00:02.53 | 77003 |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| ACCTRANS_ACCOUNT_LOG | 1 | 389 | 1666K|00:00:0
|* 5 | INDEX RANGE SCAN | IDX_L_ACCNT_NO_HASHN | 1 | 21 | 1935K|00:00:00.57 | 8
----------------------------------------------------------------------------------------------------
很明顯的統計資料估計的基數389不對，而估計的行來自於行數*選擇率，這裡應該使用全分割槽掃描，因為大約本分割槽中80%的行都符合條件
'20120810000000006777'。繼而檢視其行的統計資料發現沒有問題，那麼出問題的就可能是選擇率出現了問題。
對於這樣一個表資料極不均勻的表，需要使用到直方圖。
同時使用STA進行建議檢視大致如下：
1、 - 考慮接受推薦的 SQL 概要檔案。
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_1995',
task_owner => 'SYS', replace => TRUE);
2- Index Finding (see explain plans section below)
--------------------------------------------------
透過建立一個或多個索引可以改進此語句的執行計劃。
- 考慮執行訪問指導以改進物理方案設計或者建立推薦的索引。如果選擇建立推薦的索引, 請考慮刪除索引
"ACCOUNTANT"."IDX_L_ACCNT_NO_HASHN", 因為它是推薦的索引的字首。
create index ACCOUNTANT.IDX$$_07CB0001 on
ACCOUNTANT.ACCTRANS_ACCOUNT_LOG("ACCOUNT_NO","RAW_ADD_TIME");
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 2145504150
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 103K (1)| 00:20:44 | | |
| 1 | SORT AGGREGATE | | 1 | 32 | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | PARTITION HASH SINGLE | | 1555K| 47M| 103K (1)| 00:20:44 | 197 | 197 |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| ACCTRANS_ACCOUNT_LOG | 1555K| 47M| 103K (1)| 00:20:44 | 197 | 197 |
|* 5 | INDEX RANGE SCAN | IDX_L_ACCNT_NO_HASHN | 85875 | | 8539 (1)| 00:01:43 | 197 | 197 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SYSDATE@!-1000
2- Using SQL Profile
--------------------
Plan hash value: 3113369923
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 10685 | | |
| 1 | SORT AGGREGATE | | 1 | 32 | | | |
| 2 | PARTITION HASH SINGLE| | 1555K| 47M| 10685 | 197 | 197 |
|* 3 | TABLE ACCESS FULL | ACCTRANS_ACCOUNT_LOG | 1555K| 47M| 10685 | 197 | 197 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ACCOUNT_NO"='20120810000000006777' AND "RAW_ADD_TIME">=SYSDATE@!-1000
AND "RAW_ADD_TIME"
可以看到ORACLE給出了2個建議
1、接受PROFILE，固定執行計劃
2、增加索引
這裡也能看出為什麼說STA非常有用，不管你使不使用它的方法都是一個強有力的輔助工具。
我們不考慮接受他的建議，增加索引對大表來說可能影響效能，這個分割槽表大約有20G，接受PROFILE是萬不得已的手段。
而轉為分析為什麼不使用全分割槽掃描。
繼而考慮使用10053進行分析，為什麼不走全分割槽掃描
擷取重點如下：
Column (#2): ACCOUNT_NO( Part#: 196
AvgLen: 21 NDV: 4301 Nulls: 0 Density: 0.000233
這裡給出了他的選擇率0.000233，沒有考慮到直翻圖，對於不均勻分佈的資料需要依靠直翻圖進行統計，最後進行重新收集直翻圖資訊。語句按照的全分割槽掃描執行了。
execute dbms_stats.gather_table_stats(ownname => 'ACCOUNTANT',tabname => 'acctrans_account_log',method_opt => 'FOR COLUMNS ACCOUNT_NO SIZE AUTO',degree => '2',estimate_percent => 50);
Plan hash value: 1651882679
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.09 | 72792 | 72782 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.09 | 72792 | 72782 |
|* 2 | FILTER | | 1 | | 1667K|00:00:01.53 | 72792 | 72782 |
| 3 | PARTITION HASH SINGLE| | 1 | 1612K| 1667K|00:00:01.17 | 72792 | 72782
|* 4 | TABLE ACCESS FULL | ACCTRANS_ACCOUNT_LOG | 1 | 1612K| 1667K|00:00:00.83 | 727
----------------------------------------------------------------------------------------------------
如此執行計劃正確。
10053 給出如下：
Column (#2): ACCOUNT_NO( Part#: 196
AvgLen: 21 NDV: 3525 Nulls: 0 Density: 0.000018
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 17
實際上我們也能看到經過壓縮後高度均勻直翻圖的桶為17，說明了有大量重複的行，這有利於資料分佈判斷
類似語句如下：
with t as
(select trans_code, trans_amount, direction, settle_channel_api
from acctrans_account_log
where account_no = '20130606000000589266' and raw_add_time>= sysdate-1000 and raw_add_time
t2 as
(select nvl(sum(decode(t.direction, 'DEBIT_DIRECTION', TRANS_AMOUNT)), 0) trans_out,
nvl(sum(decode(t.direction, 'CREDIT_DIRECTION', TRANS_AMOUNT)), 0) trans_in
from t
where t.trans_code like '3%'
or t.trans_code like '7%'
or t.trans_code like '2%'
or t.trans_code like '5%1'
--or t.trans_code in ('900003', '900013', '900006', '900016')
),
t7 as
(select sum(trans_out) trans_out, sum(trans_in) trans_in
from (select nvl(sum(decode(t.direction,
'CREDIT_DIRECTION',
-TRANS_AMOUNT)),
0) trans_out,
nvl(sum(decode(t.direction, 'DEBIT_DIRECTION', -TRANS_AMOUNT)),
0) trans_in
from t
where t.trans_code in ('900003', '900013', '900006', '900016')
union all
select * from t2)),
t3 as
(select nvl(sum(decode(direction,
'CREDIT_DIRECTION',
trans_amount,
-trans_amount)),
0) deposit
from t
where t.trans_code like '4%'
or t.trans_code in ('900001', '900002', '900011', '900012')),
t4 as
(select nvl(sum(decode(direction,
'CREDIT_DIRECTION',
-trans_amount,
trans_amount)),
0) deposit_back
from t
where t.trans_code like '5%'and t.trans_code not like '5%1'
or t.trans_code in ('900007', '900017')),
t5 as
(select nvl(sum(decode(direction,
'CREDIT_DIRECTION',
-trans_amount,
trans_amount)),
0) WITHDRAW
from t
where t.trans_code like '6%'
or t.trans_code in ('900004', '900014', '900005', '900015')),
t6 as
(select nvl(sum(decode(direction,
'CREDIT_DIRECTION',
trans_amount,
-trans_amount)),
0) WITHDRAW_back
from t
where t.trans_code like '8%'
or t.trans_code in ('900008', '900018'))
select * from t3, t4, t5, t6, t7;
這個語句的執行計劃中發現
select trans_code, trans_amount, direction, settle_channel_api
from acctrans_account_log
where account_no = '20130606000000589266' and raw_add_time>= sysdate-1000 and raw_add_time
在不改變執行計劃的情況下，使用COUNT(*)進行替換，注意COUNT(*)可能改變執行計劃，
select count(*)
from acctrans_account_log
where account_no = '20130606000000589266' and raw_add_time>= sysdate-1000 and raw_add_time
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 2wdwnwqas1r8n, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from
accountant.acctrans_account_log where account_no =
'20120810000000006777' and raw_add_time>= sysdate-1000 and
raw_add_time
Plan hash value: 2145504150
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:02.51 | 77003 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:02.51 | 77003 |
|* 2 | FILTER | | 1 | | 1666K|00:00:02.88 | 77003 |
| 3 | PARTITION HASH SINGLE | | 1 | 389 | 1666K|00:00:02.53 | 77003 |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| ACCTRANS_ACCOUNT_LOG | 1 | 389 | 1666K|00:00:0
|* 5 | INDEX RANGE SCAN | IDX_L_ACCNT_NO_HASHN | 1 | 21 | 1935K|00:00:00.57 | 8
----------------------------------------------------------------------------------------------------
很明顯的統計資料估計的基數389不對，而估計的行來自於行數*選擇率，這裡應該使用全分割槽掃描，因為大約本分割槽中80%的行都符合條件
'20120810000000006777'。繼而檢視其行的統計資料發現沒有問題，那麼出問題的就可能是選擇率出現了問題。
對於這樣一個表資料極不均勻的表，需要使用到直方圖。
同時使用STA進行建議檢視大致如下：
1、 - 考慮接受推薦的 SQL 概要檔案。
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_1995',
task_owner => 'SYS', replace => TRUE);
2- Index Finding (see explain plans section below)
--------------------------------------------------
透過建立一個或多個索引可以改進此語句的執行計劃。
- 考慮執行訪問指導以改進物理方案設計或者建立推薦的索引。如果選擇建立推薦的索引, 請考慮刪除索引
"ACCOUNTANT"."IDX_L_ACCNT_NO_HASHN", 因為它是推薦的索引的字首。
create index ACCOUNTANT.IDX$$_07CB0001 on
ACCOUNTANT.ACCTRANS_ACCOUNT_LOG("ACCOUNT_NO","RAW_ADD_TIME");
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 2145504150
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 103K (1)| 00:20:44 | | |
| 1 | SORT AGGREGATE | | 1 | 32 | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | PARTITION HASH SINGLE | | 1555K| 47M| 103K (1)| 00:20:44 | 197 | 197 |
|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| ACCTRANS_ACCOUNT_LOG | 1555K| 47M| 103K (1)| 00:20:44 | 197 | 197 |
|* 5 | INDEX RANGE SCAN | IDX_L_ACCNT_NO_HASHN | 85875 | | 8539 (1)| 00:01:43 | 197 | 197 |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SYSDATE@!-1000
2- Using SQL Profile
--------------------
Plan hash value: 3113369923
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 10685 | | |
| 1 | SORT AGGREGATE | | 1 | 32 | | | |
| 2 | PARTITION HASH SINGLE| | 1555K| 47M| 10685 | 197 | 197 |
|* 3 | TABLE ACCESS FULL | ACCTRANS_ACCOUNT_LOG | 1555K| 47M| 10685 | 197 | 197 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ACCOUNT_NO"='20120810000000006777' AND "RAW_ADD_TIME">=SYSDATE@!-1000
AND "RAW_ADD_TIME"
可以看到ORACLE給出了2個建議
1、接受PROFILE，固定執行計劃
2、增加索引
這裡也能看出為什麼說STA非常有用，不管你使不使用它的方法都是一個強有力的輔助工具。
我們不考慮接受他的建議，增加索引對大表來說可能影響效能，這個分割槽表大約有20G，接受PROFILE是萬不得已的手段。
而轉為分析為什麼不使用全分割槽掃描。
繼而考慮使用10053進行分析，為什麼不走全分割槽掃描
擷取重點如下：
Column (#2): ACCOUNT_NO( Part#: 196
AvgLen: 21 NDV: 4301 Nulls: 0 Density: 0.000233
這裡給出了他的選擇率0.000233，沒有考慮到直翻圖，對於不均勻分佈的資料需要依靠直翻圖進行統計，最後進行重新收集直翻圖資訊。語句按照的全分割槽掃描執行了。
execute dbms_stats.gather_table_stats(ownname => 'ACCOUNTANT',tabname => 'acctrans_account_log',method_opt => 'FOR COLUMNS ACCOUNT_NO SIZE AUTO',degree => '2',estimate_percent => 50);
Plan hash value: 1651882679
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:01.09 | 72792 | 72782 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:01.09 | 72792 | 72782 |
|* 2 | FILTER | | 1 | | 1667K|00:00:01.53 | 72792 | 72782 |
| 3 | PARTITION HASH SINGLE| | 1 | 1612K| 1667K|00:00:01.17 | 72792 | 72782
|* 4 | TABLE ACCESS FULL | ACCTRANS_ACCOUNT_LOG | 1 | 1612K| 1667K|00:00:00.83 | 727
----------------------------------------------------------------------------------------------------
如此執行計劃正確。
10053 給出如下：
Column (#2): ACCOUNT_NO( Part#: 196
AvgLen: 21 NDV: 3525 Nulls: 0 Density: 0.000018
Histogram: HtBal #Bkts: 254 UncompBkts: 254 EndPtVals: 17
實際上我們也能看到經過壓縮後高度均勻直翻圖的桶為17，說明了有大量重複的行，這有利於資料分佈判斷
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7728585/viewspace-1324061/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL Server一次SQL調優案例SQLServer
- 記一次sql優化SQL優化
- 記一次SQL Server刪除SQL調優SQLServer
- 一次sql優化小記SQL優化
- 效能分析SQLSQL
- 一次sql server2012 AwaysON只讀節點嚴重阻塞分析SQLServer
- 一次捕獲SQL調優資訊的指令碼 sql9.sqlSQL指令碼
- 一次捕獲SQL調優資訊的指令碼 sql10.sqlSQL指令碼
- 一次ODA當機分析
- SQLServer的一次堵塞分析SQLServer
- 記一次SQL調優過程SQL
- 一次awr報告分析（密碼錯誤引發sql執行時間過長）密碼SQL
- MySQL SQL效能分析MySql
- 一次線上OOM問題分析OOM
- 一次RAC監聽停止分析
- 好文分享 | 記一次Oracle12c資料庫SQL短暫緩慢問題分析Oracle資料庫SQL
- 小白的第一次sql實戰SQL
- 一次SQL Performance Analyzer的使用過程SQLORM
- 一次sql執行效率的討論SQL
- 一次sql語句優化的反思SQL優化
- 原始碼解析丨一次慢SQL排查原始碼SQL
- 對一次 GC日誌的分析GC
- 記一次NAS故障分析（ZFS NFS）NFS
- 一次VPN隧道建立異常分析
- SQL查詢效能分析SQL
- awr的top sql分析SQL
- 20【線上日誌分析】之記錄一次Spark Streaming+Spark SQL的資料傾斜SparkSQL
- mysql一次執行多個SQL檔案MySql
- MyBatis 一次執行多條SQL語句MyBatisSQL
- 記一次SQL隱碼攻擊實戰SQL
- 涉及子查詢sql的一次優化SQL優化
- 記一次 Node.js 原始碼分析Node.js原始碼
- 一次DG故障診斷過程分析
- 一次library cache lock 問題分析
- 一次資料庫響應慢分析資料庫
- 一次ORACLE字元轉換分析過程Oracle字元
- 一次 MySQL 線上死鎖分析實戰MySql
- 記一次dump檔案分析歷程