前2天在我的blog中写到,oracle优化方法之一----使用hints对sql进行优化,刚好昨天开发的同事写了一个报表的sql语句,逻辑也不算复杂,但是执行时间却很久,足足花了差不多2分钟,由于需要紧急发布到正式环境,发给我,让我这里进行优化一下,其中用到了我上一篇文章中提到的hints,以及一些其他的小技巧,也算经验吧,详细记录如下:
优化之前的sql:
SELECT Y.*,
X.order_count torder_count,
X.order_amount tbox_count,
trunc(Y.order_count / X.order_count, 4) bili
FROM (SELECT Y.express_name,
Y.express_id,
sum(Y.box_count) box_count,
sum(Y.order_count) order_count,
sum(Y.order_amount) order_amount,
sum(Y.send_order_count) send_order_count,
sum(Y.wait_order_count) wait_order_count,
sum(Y.again_order_count) again_order_count,
sum(Y.return_order_count) return_order_count,
sum(Y.received_order_count) received_order_count,
sum(Y.getself_order_count) getself_order_count,
sum(Y.contact_order_count) contact_order_count
FROM (select ei.express_name,
ei.express_id,
sum(pi.box_count) box_count,
count(oi.order_id) order_count,
CASE oi.ORDER_STATUS
when 'ORDER_STATUS_SEND' then
count(oi.order_id)
else
0
end send_order_count,
CASE op.process_status
when 'ORDER_PROBLEM_STATUS_WAIT' then
count(oi.order_id)
else
0
end wait_order_count,
CASE op.process_status
when 'ORDER_PROBLEM_STATUS_AGAIN' then
count(oi.order_id)
else
0
end again_order_count,
CASE op.process_status
when 'ORDER_PROBLEM_STATUS_RETURN' then
count(oi.order_id)
else
0
end return_order_count,
CASE op.process_status
when 'ORDER_PROBLEM_STATUS_RECEIVED_MK' then
count(oi.order_id)
else
0
end received_order_count,
CASE op.process_status
when 'ORDER_PROBLEM_STATUS_GETSELF' then
count(oi.order_id)
else
0
end getself_order_count,
CASE op.process_status
when 'ORDER_PROBLEM_STATUS_CONTACT' then
count(oi.order_id)
else
0
end contact_order_count,
sum(oi.amount) order_amount
from m_order_info oi,
m_order_problem op,
m_send_page_info pi,
m_express_info ei,
m_order_send_page sp
where oi.order_id = sp.order_id
AND sp.send_page_id = pi.send_page_id(+)
AND oi.order_id = op.order_id(+)
and pi.express_id = ei.express_id
and oi.send_date + ei.feedback_day >=
to_date('2012-01-06', 'yyyy-mm-dd')
and oi.send_date + ei.feedback_day <=
to_date('2012-01-06 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and pi.cancel_flag = 0
and oi.order_status not in
('ORDER_STATUS_SIGNED', 'ORDER_STATUS_RETURN_GOODS')
and oi.send_date
Statistics
----------------------------------------------------------
29 recursive calls
0 db block gets
1058742 consistent gets
0 physical reads
0 redo size
2960 bytes sent via SQL*Net to client
3742 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
23 rows processed
Statistics
----------------------------------------------------------
29 recursive calls
0 db block gets
202832 consistent gets
0 physical reads
0 redo size
2963 bytes sent via SQL*Net to client
3573 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
23 rows processed
group by ei.express_id,
ei.express_name,
oi.ORDER_STATUS,
op.process_status
order by ei.express_id) Y
group by Y.express_id, Y.express_name
order by Y.express_id) Y
LEFT JOIN (select ei.express_name,
ei.express_id,
sum(pi.box_count) box_count,
count(oi.order_id) order_count,
sum(oi.amount) order_amount
from m_order_info oi,
m_send_page_info pi,
m_express_info ei,
m_order_send_page sp
where oi.order_id = sp.order_id
AND sp.send_page_id = pi.send_page_id
and pi.express_id = ei.express_id
and pi.cancel_flag = 0
and oi.send_date >= to_date('2012-01-06', 'yyyy-mm-dd')
and oi.send_date <=
to_date('2012-01-06 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
group by ei.express_id, ei.express_name
order by ei.express_id) X ON X.express_id = Y.express_id;
其具体执行计划及相关统计信息如下:
23 rows selected.
Elapsed: 00:01:51.91
Execution Plan
----------------------------------------------------------
Plan hash value: 3884932126
----------------------------------------------------------
Plan hash value: 3884932126
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 67 | 14003 | 21970 (2)| 00:04:24 |
|* 1 | HASH JOIN RIGHT OUTER | | 67 | 14003 | 21970 (2)| 00:04:24 |
| 2 | VIEW | | 4 | 156 | 881 (4)| 00:00:11 |
| 3 | SORT GROUP BY | | 4 | 252 | 881 (4)| 00:00:11 |
| 4 | NESTED LOOPS | | 4 | 252 | 880 (4)| 00:00:11 |
| 5 | NESTED LOOPS | | 4 | 192 | 876 (4)| 00:00:11 |
|* 6 | HASH JOIN | | 4 | 124 | 868 (4)| 00:00:11 |
| 7 | TABLE ACCESS BY INDEX ROWID | M_ORDER_INFO | 4 | 72 | 7 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | M_ORDER_INFO_IDX5 | 4 | | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | M_ORDER_SEND_PAGE | 746K| 9481K| 852 (3)| 00:00:11 |
|* 10 | TABLE ACCESS BY INDEX ROWID | M_SEND_PAGE_INFO | 1 | 17 | 2 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_M_SEND_PAGE_INFO | 1 | | 1 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | M_EXPRESS_INFO | 1 | 15 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_M_EXPRESS_INFO | 1 | | 0 (0)| 00:00:01 |
| 14 | VIEW | | 67 | 11390 | 21088 (2)| 00:04:14 |
| 15 | SORT GROUP BY | | 67 | 9715 | 21088 (2)| 00:04:14 |
| 16 | VIEW | | 67 | 9715 | 21088 (2)| 00:04:14 |
| 17 | HASH GROUP BY | | 67 | 8241 | 21088 (2)| 00:04:14 |
| 18 | NESTED LOOPS OUTER | | 67 | 8241 | 21087 (2)| 00:04:14 |
|* 19 | HASH JOIN | | 67 | 5829 | 21078 (2)| 00:04:13 |
|* 20 | HASH JOIN | | 9690 | 662K| 9554 (1)| 00:01:55 |
|* 21 | TABLE ACCESS BY INDEX ROWID| M_ORDER_INFO | 67 | 2613 | 60 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 9690 | 539K| 8692 (1)| 00:01:45 |
| 23 | TABLE ACCESS FULL | M_EXPRESS_INFO | 144 | 2592 | 6 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | M_ORDER_INFO_IDX5 | 29 | | 32 (0)| 00:00:01 |
| 25 | TABLE ACCESS FULL | M_ORDER_SEND_PAGE | 746K| 9481K| 852 (3)| 00:00:11 |
|* 26 | TABLE ACCESS FULL | M_SEND_PAGE_INFO | 1267K| 20M| 11509 (2)| 00:02:19 |
| 27 | TABLE ACCESS BY INDEX ROWID | M_ORDER_PROBLEM | 1 | 36 | 1 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | PK_M_ORDER_PROBLEM | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 67 | 14003 | 21970 (2)| 00:04:24 |
|* 1 | HASH JOIN RIGHT OUTER | | 67 | 14003 | 21970 (2)| 00:04:24 |
| 2 | VIEW | | 4 | 156 | 881 (4)| 00:00:11 |
| 3 | SORT GROUP BY | | 4 | 252 | 881 (4)| 00:00:11 |
| 4 | NESTED LOOPS | | 4 | 252 | 880 (4)| 00:00:11 |
| 5 | NESTED LOOPS | | 4 | 192 | 876 (4)| 00:00:11 |
|* 6 | HASH JOIN | | 4 | 124 | 868 (4)| 00:00:11 |
| 7 | TABLE ACCESS BY INDEX ROWID | M_ORDER_INFO | 4 | 72 | 7 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | M_ORDER_INFO_IDX5 | 4 | | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | M_ORDER_SEND_PAGE | 746K| 9481K| 852 (3)| 00:00:11 |
|* 10 | TABLE ACCESS BY INDEX ROWID | M_SEND_PAGE_INFO | 1 | 17 | 2 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_M_SEND_PAGE_INFO | 1 | | 1 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | M_EXPRESS_INFO | 1 | 15 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_M_EXPRESS_INFO | 1 | | 0 (0)| 00:00:01 |
| 14 | VIEW | | 67 | 11390 | 21088 (2)| 00:04:14 |
| 15 | SORT GROUP BY | | 67 | 9715 | 21088 (2)| 00:04:14 |
| 16 | VIEW | | 67 | 9715 | 21088 (2)| 00:04:14 |
| 17 | HASH GROUP BY | | 67 | 8241 | 21088 (2)| 00:04:14 |
| 18 | NESTED LOOPS OUTER | | 67 | 8241 | 21087 (2)| 00:04:14 |
|* 19 | HASH JOIN | | 67 | 5829 | 21078 (2)| 00:04:13 |
|* 20 | HASH JOIN | | 9690 | 662K| 9554 (1)| 00:01:55 |
|* 21 | TABLE ACCESS BY INDEX ROWID| M_ORDER_INFO | 67 | 2613 | 60 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 9690 | 539K| 8692 (1)| 00:01:45 |
| 23 | TABLE ACCESS FULL | M_EXPRESS_INFO | 144 | 2592 | 6 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | M_ORDER_INFO_IDX5 | 29 | | 32 (0)| 00:00:01 |
| 25 | TABLE ACCESS FULL | M_ORDER_SEND_PAGE | 746K| 9481K| 852 (3)| 00:00:11 |
|* 26 | TABLE ACCESS FULL | M_SEND_PAGE_INFO | 1267K| 20M| 11509 (2)| 00:02:19 |
| 27 | TABLE ACCESS BY INDEX ROWID | M_ORDER_PROBLEM | 1 | 36 | 1 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | PK_M_ORDER_PROBLEM | 1 | | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
1 - access("X"."EXPRESS_ID"(+)="Y"."EXPRESS_ID")
6 - access("OI"."ORDER_ID"="SP"."ORDER_ID")
8 - access("OI"."SEND_DATE">=TO_DATE(' 2012-01-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"OI"."SEND_DATE"<=TO_DATE(' 2012-01-06 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
10 - filter("PI"."CANCEL_FLAG"=0)
11 - access("SP"."SEND_PAGE_ID"="PI"."SEND_PAGE_ID")
13 - access("PI"."EXPRESS_ID"="EI"."EXPRESS_ID")
19 - access("SP"."SEND_PAGE_ID"="PI"."SEND_PAGE_ID" AND "PI"."EXPRESS_ID"="EI"."EXPRESS_ID")
20 - access("OI"."ORDER_ID"="SP"."ORDER_ID")
21 - filter("OI"."ORDER_STATUS"<>'ORDER_STATUS_SIGNED' AND
"OI"."ORDER_STATUS"<>'ORDER_STATUS_RETURN_GOODS')
24 - access("OI"."SEND_DATE"< SYSDATE@!-INTERNAL_FUNCTION("EI"."FEEDBACK_DAY"))
filter(INTERNAL_FUNCTION("OI"."SEND_DATE")+INTERNAL_FUNCTION("EI"."FEEDBACK_DAY")>=TO_DATE('
2012-01-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
INTERNAL_FUNCTION("OI"."SEND_DATE")+INTERNAL_FUNCTION("EI"."FEEDBACK_DAY")<=TO_DATE(' 2012-01-06
23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
26 - filter("PI"."CANCEL_FLAG"=0)
28 - access("OI"."ORDER_ID"="OP"."ORDER_ID"(+))
6 - access("OI"."ORDER_ID"="SP"."ORDER_ID")
8 - access("OI"."SEND_DATE">=TO_DATE(' 2012-01-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"OI"."SEND_DATE"<=TO_DATE(' 2012-01-06 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
10 - filter("PI"."CANCEL_FLAG"=0)
11 - access("SP"."SEND_PAGE_ID"="PI"."SEND_PAGE_ID")
13 - access("PI"."EXPRESS_ID"="EI"."EXPRESS_ID")
19 - access("SP"."SEND_PAGE_ID"="PI"."SEND_PAGE_ID" AND "PI"."EXPRESS_ID"="EI"."EXPRESS_ID")
20 - access("OI"."ORDER_ID"="SP"."ORDER_ID")
21 - filter("OI"."ORDER_STATUS"<>'ORDER_STATUS_SIGNED' AND
"OI"."ORDER_STATUS"<>'ORDER_STATUS_RETURN_GOODS')
24 - access("OI"."SEND_DATE"< SYSDATE@!-INTERNAL_FUNCTION("EI"."FEEDBACK_DAY"))
filter(INTERNAL_FUNCTION("OI"."SEND_DATE")+INTERNAL_FUNCTION("EI"."FEEDBACK_DAY")>=TO_DATE('
2012-01-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
INTERNAL_FUNCTION("OI"."SEND_DATE")+INTERNAL_FUNCTION("EI"."FEEDBACK_DAY")<=TO_DATE(' 2012-01-06
23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
26 - filter("PI"."CANCEL_FLAG"=0)
28 - access("OI"."ORDER_ID"="OP"."ORDER_ID"(+))
Statistics
----------------------------------------------------------
29 recursive calls
0 db block gets
1058742 consistent gets
0 physical reads
0 redo size
2960 bytes sent via SQL*Net to client
3742 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
23 rows processed
这条sql执行总花费时长111秒左右,其中一致读很高,达到了
1058742
,从执行计划看,执行成本和io并不高,几张大表都走索引了,这也应该是oracle所期望的,较好的平衡了cpu使用和io负载,但是速度却不理想,尤其是在一个实时性要求很高的系统中,最快的速度响应需求才是第一位的;
优化之后的sql:
SELECT Y.*,
X.order_count torder_count,
X.order_amount tbox_count,
trunc(Y.order_count / X.order_count, 4) bili
FROM (SELECT Y.express_name,
Y.express_id,
sum(Y.box_count) box_count,
sum(Y.order_count) order_count,
sum(Y.order_amount) order_amount,
sum(Y.send_order_count) send_order_count,
sum(Y.wait_order_count) wait_order_count,
sum(Y.again_order_count) again_order_count,
sum(Y.return_order_count) return_order_count,
sum(Y.received_order_count) received_order_count,
sum(Y.getself_order_count) getself_order_count,
sum(Y.contact_order_count) contact_order_count
FROM (select
/*+use_hash(oi,op,pi,ei,sp) first_rows*/
ei.express_name,
ei.express_id,
sum(pi.box_count) box_count,
count(oi.order_id) order_count,
CASE oi.ORDER_STATUS
when 'ORDER_STATUS_SEND' then
count(oi.order_id)
else
0
end send_order_count,
CASE op.process_status
when 'ORDER_PROBLEM_STATUS_WAIT' then
count(oi.order_id)
else
0
end wait_order_count,
CASE op.process_status
when 'ORDER_PROBLEM_STATUS_AGAIN' then
count(oi.order_id)
else
0
end again_order_count,
CASE op.process_status
when 'ORDER_PROBLEM_STATUS_RETURN' then
count(oi.order_id)
else
0
end return_order_count,
CASE op.process_status
when 'ORDER_PROBLEM_STATUS_RECEIVED_MK' then
count(oi.order_id)
else
0
end received_order_count,
CASE op.process_status
when 'ORDER_PROBLEM_STATUS_GETSELF' then
count(oi.order_id)
else
0
end getself_order_count,
CASE op.process_status
when 'ORDER_PROBLEM_STATUS_CONTACT' then
count(oi.order_id)
else
0
end contact_order_count,
sum(oi.amount) order_amount
from m_order_info oi,
m_order_problem op,
m_send_page_info pi,
m_express_info ei,
m_order_send_page sp
where oi.order_id = sp.order_id
AND sp.send_page_id = pi.send_page_id(+)
AND oi.order_id = op.order_id(+)
and pi.express_id = ei.express_id
and pi.cancel_flag = 0
and oi.order_status not in
('ORDER_STATUS_SIGNED', 'ORDER_STATUS_RETURN_GOODS')
and oi.send_date
and oi.send_date >=
to_date('2012-01-06', 'yyyy-mm-dd') - ei.feedback_day
and oi.send_date <=
to_date('2012-01-06 23:59:59', 'yyyy-mm-dd hh24:mi:ss') -
ei.feedback_day
group by ei.express_id,
ei.express_name,
oi.ORDER_STATUS,
op.process_status
order by ei.express_id) Y
group by Y.express_id, Y.express_name
order by Y.express_id) Y
LEFT JOIN (select
/*+use_hash(oi,pi,ei,sp) first_rows*/
ei.express_name,
ei.express_id,
sum(pi.box_count) box_count,
count(oi.order_id) order_count,
sum(oi.amount) order_amount
from m_order_info oi,
m_send_page_info pi,
m_express_info ei,
m_order_send_page sp
where oi.order_id = sp.order_id
AND sp.send_page_id = pi.send_page_id
and pi.express_id = ei.express_id
and pi.cancel_flag = 0
and oi.send_date >= to_date('2012-01-06', 'yyyy-mm-dd')
and oi.send_date <=
to_date('2012-01-06 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
group by ei.express_id, ei.express_name
order by ei.express_id) X ON X.express_id = Y.express_id;
其具体执行计划及相关统计信息如下:
23 rows selected.
Elapsed: 00:00:03.44
Execution Plan
----------------------------------------------------------
Plan hash value: 1236569700
----------------------------------------------------------
Plan hash value: 1236569700
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 67 | 14003 | | 60235 (2)| 00:12:03 |
|* 1 | HASH JOIN RIGHT OUTER | | 67 | 14003 | | 60235 (2)| 00:12:03 |
| 2 | VIEW | | 4 | 156 | | 12400 (2)| 00:02:29 |
| 3 | SORT GROUP BY | | 4 | 252 | | 12400 (2)| 00:02:29 |
|* 4 | HASH JOIN | | 4 | 252 | | 12399 (2)| 00:02:29 |
|* 5 | HASH JOIN | | 4 | 192 | | 12393 (2)| 00:02:29 |
|* 6 | HASH JOIN | | 4 | 124 | | 868 (4)| 00:00:11 |
| 7 | TABLE ACCESS BY INDEX ROWID| M_ORDER_INFO | 4 | 72 | | 7 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | M_ORDER_INFO_IDX5 | 4 | | | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | M_ORDER_SEND_PAGE | 746K| 9481K| | 852 (3)| 00:00:11 |
|* 10 | TABLE ACCESS FULL | M_SEND_PAGE_INFO | 1267K| 20M| | 11509 (2)| 00:02:19 |
| 11 | TABLE ACCESS FULL | M_EXPRESS_INFO | 144 | 2160 | | 6 (0)| 00:00:01 |
| 12 | VIEW | | 67 | 11390 | | 47834 (2)| 00:09:35 |
| 13 | SORT GROUP BY | | 67 | 9715 | | 47834 (2)| 00:09:35 |
| 14 | VIEW | | 67 | 9715 | | 47834 (2)| 00:09:35 |
| 15 | HASH GROUP BY | | 67 | 8241 | | 47834 (2)| 00:09:35 |
|* 16 | HASH JOIN OUTER | | 67 | 8241 | | 47833 (2)| 00:09:34 |
|* 17 | HASH JOIN | | 67 | 5829 | | 46652 (2)| 00:09:20 |
| 18 | TABLE ACCESS FULL | M_EXPRESS_INFO | 144 | 2592 | | 6 (0)| 00:00:01 |
|* 19 | HASH JOIN | | 538K| 35M| 26M| 46639 (2)| 00:09:20 |
|* 20 | TABLE ACCESS FULL | M_ORDER_INFO | 538K| 20M| | 28799 (2)| 00:05:46 |
|* 21 | HASH JOIN | | 746K| 21M| 17M| 15029 (2)| 00:03:01 |
| 22 | TABLE ACCESS FULL | M_ORDER_SEND_PAGE | 746K| 9481K| | 852 (3)| 00:00:11 |
|* 23 | TABLE ACCESS FULL | M_SEND_PAGE_INFO | 1267K| 20M| | 11509 (2)| 00:02:19 |
| 24 | TABLE ACCESS FULL | M_ORDER_PROBLEM | 193K| 6812K| | 1178 (1)| 00:00:15 |
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 67 | 14003 | | 60235 (2)| 00:12:03 |
|* 1 | HASH JOIN RIGHT OUTER | | 67 | 14003 | | 60235 (2)| 00:12:03 |
| 2 | VIEW | | 4 | 156 | | 12400 (2)| 00:02:29 |
| 3 | SORT GROUP BY | | 4 | 252 | | 12400 (2)| 00:02:29 |
|* 4 | HASH JOIN | | 4 | 252 | | 12399 (2)| 00:02:29 |
|* 5 | HASH JOIN | | 4 | 192 | | 12393 (2)| 00:02:29 |
|* 6 | HASH JOIN | | 4 | 124 | | 868 (4)| 00:00:11 |
| 7 | TABLE ACCESS BY INDEX ROWID| M_ORDER_INFO | 4 | 72 | | 7 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | M_ORDER_INFO_IDX5 | 4 | | | 3 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | M_ORDER_SEND_PAGE | 746K| 9481K| | 852 (3)| 00:00:11 |
|* 10 | TABLE ACCESS FULL | M_SEND_PAGE_INFO | 1267K| 20M| | 11509 (2)| 00:02:19 |
| 11 | TABLE ACCESS FULL | M_EXPRESS_INFO | 144 | 2160 | | 6 (0)| 00:00:01 |
| 12 | VIEW | | 67 | 11390 | | 47834 (2)| 00:09:35 |
| 13 | SORT GROUP BY | | 67 | 9715 | | 47834 (2)| 00:09:35 |
| 14 | VIEW | | 67 | 9715 | | 47834 (2)| 00:09:35 |
| 15 | HASH GROUP BY | | 67 | 8241 | | 47834 (2)| 00:09:35 |
|* 16 | HASH JOIN OUTER | | 67 | 8241 | | 47833 (2)| 00:09:34 |
|* 17 | HASH JOIN | | 67 | 5829 | | 46652 (2)| 00:09:20 |
| 18 | TABLE ACCESS FULL | M_EXPRESS_INFO | 144 | 2592 | | 6 (0)| 00:00:01 |
|* 19 | HASH JOIN | | 538K| 35M| 26M| 46639 (2)| 00:09:20 |
|* 20 | TABLE ACCESS FULL | M_ORDER_INFO | 538K| 20M| | 28799 (2)| 00:05:46 |
|* 21 | HASH JOIN | | 746K| 21M| 17M| 15029 (2)| 00:03:01 |
| 22 | TABLE ACCESS FULL | M_ORDER_SEND_PAGE | 746K| 9481K| | 852 (3)| 00:00:11 |
|* 23 | TABLE ACCESS FULL | M_SEND_PAGE_INFO | 1267K| 20M| | 11509 (2)| 00:02:19 |
| 24 | TABLE ACCESS FULL | M_ORDER_PROBLEM | 193K| 6812K| | 1178 (1)| 00:00:15 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
1 - access("X"."EXPRESS_ID"(+)="Y"."EXPRESS_ID")
4 - access("PI"."EXPRESS_ID"="EI"."EXPRESS_ID")
5 - access("SP"."SEND_PAGE_ID"="PI"."SEND_PAGE_ID")
6 - access("OI"."ORDER_ID"="SP"."ORDER_ID")
8 - access("OI"."SEND_DATE">=TO_DATE(' 2012-01-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"OI"."SEND_DATE"<=TO_DATE(' 2012-01-06 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
10 - filter("PI"."CANCEL_FLAG"=0)
16 - access("OI"."ORDER_ID"="OP"."ORDER_ID"(+))
17 - access("PI"."EXPRESS_ID"="EI"."EXPRESS_ID")
filter("OI"."SEND_DATE"< SYSDATE@!-INTERNAL_FUNCTION("EI"."FEEDBACK_DAY") AND
"OI"."SEND_DATE">=TO_DATE(' 2012-01-06 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')-INTERNAL_FUNCTION("EI"."FEEDBACK_DAY") AND "OI"."SEND_DATE"<=TO_DATE(' 2012-01-06
23:59:59', 'syyyy-mm-dd hh24:mi:ss')-INTERNAL_FUNCTION("EI"."FEEDBACK_DAY"))
19 - access("OI"."ORDER_ID"="SP"."ORDER_ID")
20 - filter("OI"."ORDER_STATUS"<>'ORDER_STATUS_SIGNED' AND
"OI"."ORDER_STATUS"<>'ORDER_STATUS_RETURN_GOODS')
21 - access("SP"."SEND_PAGE_ID"="PI"."SEND_PAGE_ID")
23 - filter("PI"."CANCEL_FLAG"=0)
4 - access("PI"."EXPRESS_ID"="EI"."EXPRESS_ID")
5 - access("SP"."SEND_PAGE_ID"="PI"."SEND_PAGE_ID")
6 - access("OI"."ORDER_ID"="SP"."ORDER_ID")
8 - access("OI"."SEND_DATE">=TO_DATE(' 2012-01-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"OI"."SEND_DATE"<=TO_DATE(' 2012-01-06 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
10 - filter("PI"."CANCEL_FLAG"=0)
16 - access("OI"."ORDER_ID"="OP"."ORDER_ID"(+))
17 - access("PI"."EXPRESS_ID"="EI"."EXPRESS_ID")
filter("OI"."SEND_DATE"< SYSDATE@!-INTERNAL_FUNCTION("EI"."FEEDBACK_DAY") AND
"OI"."SEND_DATE">=TO_DATE(' 2012-01-06 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')-INTERNAL_FUNCTION("EI"."FEEDBACK_DAY") AND "OI"."SEND_DATE"<=TO_DATE(' 2012-01-06
23:59:59', 'syyyy-mm-dd hh24:mi:ss')-INTERNAL_FUNCTION("EI"."FEEDBACK_DAY"))
19 - access("OI"."ORDER_ID"="SP"."ORDER_ID")
20 - filter("OI"."ORDER_STATUS"<>'ORDER_STATUS_SIGNED' AND
"OI"."ORDER_STATUS"<>'ORDER_STATUS_RETURN_GOODS')
21 - access("SP"."SEND_PAGE_ID"="PI"."SEND_PAGE_ID")
23 - filter("PI"."CANCEL_FLAG"=0)
Statistics
----------------------------------------------------------
29 recursive calls
0 db block gets
202832 consistent gets
0 physical reads
0 redo size
2963 bytes sent via SQL*Net to client
3573 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
23 rows processed
这条sql语句,通过使用hints和改变sql语句中的一些条件的顺序(多表之间强制使用hash连接,多了很多全表扫描,而原来的sql语句,使用嵌套循环,较好的平衡了io负载和cpu使用成本,同时使用了first_rows提示以提高响应速度,而where条件中的一些语句作了调整之后,过滤掉了大部分无关的数据),执行只花费了3秒多,一致读降低了差不多5倍,其响应速度得到了明显的提升,但是显著的问题也出来了,cpu使用成本涨了2倍,io负载也上去了,所以,具体的优化措施,得根据相应的业务来权衡,同时还要结合具体的服务器资源等综合考虑。
以上都是我的一些心得体会,有不对的地方,望各位博友能批评指正,以互相学习。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25618347/viewspace-714654/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25618347/viewspace-714654/