--Unix

Memory: 2617704K (860220K) real, 2774560K (942392K) virtual, 38440908K free Page# 1/19

CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
2 ? 20219 oracle 234 20 6390M 5384K run 31:02 100.13 99.96 oracletest1
--
select * from v$session where paddr in (select addr from v$process where spid = &Pid)--20219
--
select * from v$session_wait where sid = 50
50 383 db file sequential read file# 119 0000000000000077 block# 35698 0000000000008B72 blocks 1 0000000000000001 -1 9 WAITED KNOWN TIME
50 39582 latch free address 1.3835058074286E19 C00000046CB758D8 number 98 0000000000000062 tries 0 00 -1 1 WAITED KNOWN TIME XXXXXXXXXXXXXXX U
select * from v$latchname where latch# = 98;--P2
98 cache buffers chains
select * from v$event_name where name = 'latch free'
4 latch free address number tries [file#,block#,tries]
-------------------------------------------------------------------------------------------------
通过v$sqltext关联之后发现如下语句
--------------------------------------------------------------------------------------------------
select a.Approve_Serial_Nbr,
a.Approve_Type,
a.approve_flag,
a.acct_id,
a.Approve_charge,
a.Approve_Staff_Id,
a.Approve_Site_Id,
a.Approve_User_Name,
a.Approve_reason,
to_char(a.approve_date, 'mm/dd hh24:mi'),
a.Used_flag,
b.acc_nbr,
b.approve_value,
b.acct_month,
b.acct_item_type_id
from bill_cc.approve_serial_t a, bill_cc.approve_item_t b
where a.approve_serial_nbr = b.approve_serial_nbr
and a.Partition_id_region between 110101 and 110199
and b.Partition_id_region between 110101 and 110199
and a.Approve_Staff_Id = 'sys110101'
and to_char(a.Approve_Date, 'yyyy/mm/dd') between '2007/05/01' and
'2007/05/30'
and a.Approve_Type = 'DERATE'
and a.Approve_Type = b.Approve_Type
order by a.approve_serial_nbr,
a.ACCT_ID,
b.acct_month,
b.acct_item_type_id
---------------------------------------------------------------------------------
1
2 --------------------------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
4 --------------------------------------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | 1 | 336 | 11 | | |
6 | 1 | SORT ORDER BY | | 1 | 336 | 11 | | |
7 | 2 | TABLE ACCESS BY LOCAL INDEX ROWID | APPROVE_ITEM_T | 1 | 82 | 1 | | |
8 | 3 | NESTED LOOPS | | 1 | 336 | 2 | | |
9 | 4 | PARTITION RANGE ALL | | | | | 1 | 2 |
10 | 5 | TABLE ACCESS BY LOCAL INDEX ROWID| APPROVE_SERIAL_T | 1 | 254 | 1 | 1 | 2 |
11 | 6 | INDEX RANGE SCAN | I1_APPROVE_SERIAL_T | 1 | | 3 | 1 | 2 |
12 | 7 | PARTITION RANGE ALL | | | | | 1 | 2 |
13 | 8 | INDEX RANGE SCAN | I1_APPROVE_ITEM_T | 1 | | 2 | 1 | 2 |
14 --------------------------------------------------------------------------------------------------------------
15
16 Note: cpu costing is off, PLAN_TABLE' is old version

--------------------------------------------------------
检查各表数据
select count(*) from bill_cc.approve_serial_t --1353163
select count(*) from bill_cc.approve_item_t --1306518
--一般来说当2个大表进行连接涉及数据量较大时hash_jion比nested loops更为合适!
如果两个表都走索引的话后果很可能是恶梦。

------------------------------------
------------------------------------
下面强制执行全表扫描模式
------------------------------------
select //用时17s
/*+ full(a) full(b) */ *
from bill_cc.approve_serial_t a, bill_cc.approve_item_t b
where a.approve_serial_nbr = b.approve_serial_nbr
and a.Partition_id_region between 110101 and 110199
and b.Partition_id_region between 110101 and 110199
and a.Approve_Staff_Id = 'sys110101'
and to_char(a.Approve_Date, 'yyyy/mm/dd') between '2007/05/01' and
'2007/05/30'
and a.Approve_Type = 'DERATE'
and a.Approve_Type = b.Approve_Type
order by a.approve_serial_nbr,
a.ACCT_ID,
b.acct_month,
b.acct_item_type_id

1
2 -------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
4 -------------------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | 1 | 734 | 14 | | |
6 | 1 | SORT ORDER BY | | 1 | 734 | 14 | | |
7 | 2 | HASH JOIN | | 1 | 734 | 5 | | |
8 | 3 | PARTITION RANGE ALL| | | | | 1 | 2 |
9 | 4 | TABLE ACCESS FULL | APPROVE_SERIAL_T | 1 | 544 | 2 | 1 | 2 |
10 | 5 | PARTITION RANGE ALL| | | | | 1 | 2 |
11 | 6 | TABLE ACCESS FULL | APPROVE_ITEM_T | 1 | 190 | 2 | 1 | 2 |
12 -------------------------------------------------------------------------------------------
13
14 Note: cpu costing is off, PLAN_TABLE' is old version

--------------------
察看原来的索引如下:
create index BILL_CC.I1_APPROVE_ITEM_T on BILL_CC.APPROVE_ITEM_T (APPROVE_TYPE, ACCT_ID, APPROVE_SERIAL_NBR);
create index BILL_CC.I1_APPROVE_SERIAL_T on BILL_CC.APPROVE_SERIAL_T (APPROVE_TYPE, ACCT_ID, APPROVE_SERIAL_NBR);

分析索引的创建应该是不合理的
drop index BILL_CC.I1_APPROVE_ITEM_T;
drop index BILL_CC.I1_APPROVE_SERIAL_T;

create index BILL_CC.I1_APPROVE_ITEM_T on
bill_cc.approve_serial_t(Approve_Staff_Id,Approve_Type);

--考虑acct_id被经常用
create index BILL_CC.I2_APPROVE_ITEM_T on
bill_cc.approve_serial_t(acct_id);

create index BILL_CC.I1_APPROVE_SERIAL_T on
bill_cc.approve_serial_t(ACCT_ID);

--最后确认一下其他情形是否有对原来索引有所依赖?
--最终计划如下
--------------------------------------------------------
1
2 -----------------------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
4 -----------------------------------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | 1 | 336 | 1623 | | |
6 | 1 | SORT ORDER BY | | 1 | 336 | 1623 | | |
7 | 2 | HASH JOIN | | 1 | 336 | 1614 | | |
8 | 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| APPROVE_SERIAL_T | 1 | 254 | 2 | ROWID | ROW L |
9 | 4 | INDEX RANGE SCAN | I1_APPROVE_ITEM_T | 43 | | 1 | | |
10 | 5 | PARTITION RANGE ALL | | | | | 1 | 2 |
11 | 6 | TABLE ACCESS FULL | APPROVE_ITEM_T | 34 | 2788 | 1611 | 1 | 2 |
12 -----------------------------------------------------------------------------------------------------------
13
14 Note: cpu costing is off, PLAN_TABLE' is old version

--进一步考虑前台查询组合繁多,approve_type类型不断变化,所以这种
create index BILL_CC.I1_APPROVE_ITEM_T on
bill_cc.approve_serial_t(Approve_Staff_Id,Approve_Type);
是不能通用的，为什么没走skip index还是值得考虑的？

应该更改为:[ysdb]
drop index BILL_CC.I1_APPROVE_ITEM_T;
drop index BILL_CC.I1_APPROVE_SERIAL_T;
--
create index BILL_CC.I1_APPROVE_ITEM_T on
bill_cc.approve_serial_t(approve_Type);
--
acct_id索引的建立方式不变.
create index BILL_CC.I2_APPROVE_ITEM_T on
bill_cc.I2_APPROVE_ITEM_T(acct_id);

create index BILL_CC.I1_APPROVE_SERIAL_T on
bill_cc.approve_serial_t(ACCT_ID);

1
2 -----------------------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
4 -----------------------------------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | 1 | 336 | 1623 | | |
6 | 1 | SORT ORDER BY | | 1 | 336 | 1623 | | |
7 | 2 | HASH JOIN | | 1 | 336 | 1614 | | |
8 | 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| APPROVE_SERIAL_T | 1 | 254 | 2 | ROWID | ROW L |
9 | 4 | INDEX RANGE SCAN | I1_APPROVE_ITEM_T | 10831 | | 1 | | |
10 | 5 | PARTITION RANGE ALL | | | | | 1 | 2 |
11 | 6 | TABLE ACCESS FULL | APPROVE_ITEM_T | 34 | 2788 | 1611 | 1 | 2 |
12 -----------------------------------------------------------------------------------------------------------
13
14 Note: cpu costing is off, PLAN_TABLE' is old version

补充总结:根据v$session_wait可以看到等待事件是db file sequential read，通常来说索引读是好的,
会很快，但是如果过多的索引读就令人畏惧的。