Explain plan for
select acct_id,
acc_nbr,
serv_name,
service_type,
region_id_serv,
acct_month,
charge_meter,
charge_trunk,
charge_data,
charge_other,
late_fee,
bad_charge,
ture_charge,
charge_project,
staff_name,
bank_name,
cust_level
from (select acct_id,
acc_nbr,
serv_name,
service_type,
region_id_serv,
acct_month,
charge_meter,
charge_trunk,
charge_data,
charge_other,
late_fee,
bad_charge,
ture_charge,
charge_project,
staff_name,
bank_name,
cust_level
from (select acct_id,
acc_nbr,
serv_name,
service_type,
region_id_serv,
acct_month,
sum(charge_meter / 100) charge_meter,
sum(charge_trunk / 100) charge_trunk,
sum(charge_data / 100) charge_data,
sum(charge_other / 100) charge_other,
sum(late_fee / 100) late_fee,
sum(bad_charge / 100) bad_charge,
sum(charge_meter + charge_trunk + charge_data + charge_other + late_fee) /
100 ture_charge,
decode(charge_flag,
'0',
'普通欠费',
'1',
'托收欠费',
'2',
'坏帐欠费',
'9',
'收回欠费',
'其它欠费') charge_project,
staff_name,
bank_name,
cust_level from acct_item_day_sta_t
where 1 = 1
and (region_id_acct like '110504%')
and charge_flag <> 9
and payment_method = 1 and
billing_cycle_id between 400000000 and 400000329
and cust_level in ('0', '1', '2', '3', '4') and billing_type_id in (1,0)
group by acct_id,
acc_nbr,
acct_month,
serv_name,
acct_id,
charge_flag,
service_type,
region_id_serv,
staff_name,
bank_name,
cust_level
union all
select acct_id,
'小计' acc_nbr,
'' serv_name,
'' service_type,
0 region_id_serv,
acct_month,
sum(charge_meter / 100) charge_meter,
sum(charge_trunk / 100) charge_trunk,
sum(charge_data / 100) charge_data,
sum(charge_other / 100) charge_other,
sum(late_fee / 100) late_fee,
sum(bad_charge / 100) bad_charge,
sum(charge_meter + charge_trunk +
charge_data + charge_other + late_fee) /
100ture_charge,
decode(charge_flag,
'0',
'普通欠费',
'1',
'托收欠费',
'2',
'坏帐欠费',
'9',
'收回欠费',
'其它欠费') charge_project,
'' satff_name,
'' bank_name,
0 cust_level
from acct_item_day_sta_t where 1 = 1 and
(region_id_acct like '110504%') and
charge_flag <> 9 and payment_method = 1 and
billing_cycle_id between 400000000 and
400000329 and
cust_level in ('0', '1', '2', '3', '4') and
billing_type_id in(1, 0)
group by acct_id,
acct_month,
charge_flag,
cust_level
)
order by acct_id, acc_nbr)
Union all
select count(distinct acct_id),
'总计' acc_nbr,
'' serv_name,
'' service_type,
0 region_id_serv,
'' acct_month,
sum(charge_meter / 100) charge_meter,
sum(charge_trunk / 100) charge_trunk,
sum(charge_data / 100) charge_data,
sum(charge_other / 100) charge_other,
sum(late_fee / 100) late_fee,
sum(bad_charge / 100) bad_charge,
sum(charge_meter + charge_trunk + charge_data + charge_other + late_fee) /
100 ture_charge,
'' charge_project,
'' satff_name,
'' bank_name,
0 cust_level
from acct_item_day_sta_t
where 1 = 1
and (region_id_acct like '110504%')
and charge_flag <> 9
and payment_method = 1
and billing_cycle_id between 400000000 and 400000329
and cust_level in ('0', '1', '2', '3', '4')
and billing_type_id in (1, 0)

--------------------------------------------------
create index BILL.I1_ACCT_ITEM_DAY_STA_T on BILL.ACCT_ITEM_DAY_STA_T (SERV_ID);
create index BILL.I2_ACCT_ITEM_DAY_STA_T on BILL.ACCT_ITEM_DAY_STA_T (ACCT_ID);
create index BILL.I3_ACCT_ITEM_DAY_STA_T on BILL.ACCT_ITEM_DAY_STA_T (BILLING_CYCLE_ID, CHARGE_FLAG, BILLING_TYPE_ID, PAYMENT_METHOD);
--------------------------------------------------

2 ---------------------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes | Cost |
4 ---------------------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | | | |
6 | 1 | UNION-ALL | | | | |
7 | 2 | VIEW | | | | |
8 | 3 | SORT ORDER BY | | | | |
9 | 4 | VIEW | | | | |
10 | 5 | UNION-ALL | | | | |
11 | 6 | SORT GROUP BY | | | | |
12 | 7 | TABLE ACCESS BY INDEX ROWID| ACCT_ITEM_DAY_STA_T | | | |
13 | 8 | INDEX RANGE SCAN | I3_ACCT_ITEM_DAY_STA_T | | | |
14 | 9 | SORT GROUP BY | | | | |
15 | 10 | TABLE ACCESS BY INDEX ROWID| ACCT_ITEM_DAY_STA_T | | | |
16 | 11 | INDEX RANGE SCAN | I3_ACCT_ITEM_DAY_STA_T | | | |
17 | 12 | SORT GROUP BY | | | | |
18 | 13 | TABLE ACCESS BY INDEX ROWID | ACCT_ITEM_DAY_STA_T | | | |
19 | 14 | INDEX RANGE SCAN | I3_ACCT_ITEM_DAY_STA_T | | | |
20 ---------------------------------------------------------------------------------------------
21
22 Note: rule based optimization, PLAN_TABLE' is old version
--------------------------------------------------------------------------
select * from v$session_wait where sid =59;
59 6157 db file sequential read file# 890 000000000000037A block# 249017 000000000003CCB9 blocks 1 0000000000000001 0 0 WAITING
--------------------------------------------------------------------------
执行时间:--612s

-----------------------------------
drop index BILL.I3_ACCT_ITEM_DAY_STA_T
-----------------------------------
1
2 --------------------------------------------------------------------------------
3 | Id | Operation | Name | Rows | Bytes | Cost |
4 --------------------------------------------------------------------------------
5 | 0 | SELECT STATEMENT | | | | |
6 | 1 | UNION-ALL | | | | |
7 | 2 | VIEW | | | | |
8 | 3 | SORT ORDER BY | | | | |
9 | 4 | VIEW | | | | |
10 | 5 | UNION-ALL | | | | |
11 | 6 | SORT GROUP BY | | | | |
12 | 7 | TABLE ACCESS FULL| ACCT_ITEM_DAY_STA_T | | | |
13 | 8 | SORT GROUP BY | | | | |
14 | 9 | TABLE ACCESS FULL| ACCT_ITEM_DAY_STA_T | | | |
15 | 10 | SORT GROUP BY | | | | |
16 | 11 | TABLE ACCESS FULL | ACCT_ITEM_DAY_STA_T | | | |
17 --------------------------------------------------------------------------------
18
19 Note: rule based optimization, PLAN_TABLE' is old version

select * from v$session_wait where sid =59;
1 59 4131 db file scattered read file# 1058 0000000000000422 block# 108473 000000000001A7B9 blocks 16 0000000000000010 0 0 WAITING
执行时间:--270s

[@more@]