问题描述：
类似语句如下：
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
4 - filter("RAW_ADD_TIME">=SYSDATE@!-1000 AND "RAW_ADD_TIME"
5 - access("ACCOUNT_NO"='20120810000000006777')
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，说明了有大量重复的行，这有利于数据分布判断
