开发丢来一个sql,说跑10多分钟都没有出结果
sql及执行计划如下:
SQL> set lines 200 pagesize 999
SQL> explain plan for select distinct u.id, c.real_name,u.user_name,c.phone,c.region,c.email,pv.name
2 from orders o , orderitem oi , users u ,customer c,product_version pv
3 where o.user_id = u.id
4 and u.id = c.user_id
5 and o.id = oi.order_id
6 and oi.product_id = pv.product_id
7 and oi.version_id = pv.version_id
8 and oi.product_id in (
9 28801
10 ,28800
11 ,28693
12 ,28542
13 ,28508
14 ,28766
15 ,28722
16 ,28750
17 ,28708
18 ,28762
19 ,28765
20 ,28702
21 ,28767
22 ,28718
23 ,28754
24 ,28705
25 ,28764
26 ,28721
27 ,28751
28 ,28709
29 ,28761
30 ,28719
31 ,28482
32 ,28861
33 ,28860
34 ,28480
35 ,28481
36 ,28859
37 ,28463
38 )
39 --and oi.version_id =1
40 and o.ordertime >= to_date('20130918', 'yyyymmdd')
41 and o.ordertime < to_date('20131012','yyyymmdd')
42 and o.orderstatusid = 1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3641462162
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 480 | 13 (8)| 00:00:01 | | |
| 1 | HASH UNIQUE | | 1 | 480 | 13 (8)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 1 | 480 | 12 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 382 | 10 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 1 | 129 | 9 (0)| 00:00:01 | | |
| 5 | NESTED LOOPS | | 1 | 90 | 6 (0)| 00:00:01 | | |
| 6 | REMOTE | ORDERS | 1 | 48 | 4 (0)| 00:00:01 | NEW_N~ | R->S |
| 7 | REMOTE | USERS | 1 | 42 | 2 (0)| 00:00:01 | NEW_T~ | R->S |
| 8 | REMOTE | ORDERITEM | 1 | 39 | 3 (0)| 00:00:01 | NEW_N~ | R->S |
| 9 | REMOTE | PRODUCT_VERSION | 1 | 253 | 1 (0)| 00:00:01 | NEW_N~ | R->S |
| 10 | REMOTE | CUSTOMER | 1 | 98 | 2 (0)| 00:00:01 | NEW_N~ | R->S |
-----------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
6 - SELECT "ID","USER_ID","ORDERSTATUSID","ORDERTIME" FROM "ORDERS" "O" WHERE
"ORDERTIME">=TO_DATE(' 2013-09-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "ORDERSTATUSID"=1
AND "ORDERTIME"<TO_DATE(' 2013-10-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss') (accessing
'NEW_NEWCOURSE' )
7 - SELECT "ID","USER_NAME" FROM "USERS" "U" WHERE :1="ID" (accessing 'NEW_TO_CORE' )
8 - SELECT "ORDER_ID","PRODUCT_ID","VERSION_ID" FROM "ORDERITEM" "OI" WHERE
("PRODUCT_ID"=28463 OR "PRODUCT_ID"=28480 OR "PRODUCT_ID"=28481 OR "PRODUCT_ID"=28482 OR
"PRODUCT_ID"=28508 OR "PRODUCT_ID"=28542 OR "PRODUCT_ID"=28693 OR "PRODUCT_ID"=28702 OR
"PRODUCT_ID"=28705 OR "PRODUCT_ID"=28708 OR "PRODUCT_ID"=28709 OR "PRODUCT_ID"=28718 OR
"PRODUCT_ID"=28719 OR "PRODUCT_ID"=28721 OR "PRODUCT_ID"=28722 OR "PRODUCT_ID"=28750 OR
"PRODUCT_ID"=28751 OR "PRODUCT_ID"=28754 OR "PRODUCT_ID"=28761 OR "PRODUCT_ID"=28762 OR
"PRODUCT_ID"=28764 OR "PRODUCT_ID"=28765 OR "PRODUCT_ID"=28766 OR "PRODUCT_ID"=28767 OR
"PRODUCT_ID"=28800 OR "PRODUCT_ID"=28801 OR "PRODUCT_ID"=28859 OR "PRODUCT_ID"=28860 OR
"PRODUCT_ID"=28861) AND :1="ORDER_ID" (accessing 'NEW_NEWCOURSE' )
9 - SELECT "PRODUCT_ID","VERSION_ID","NAME" FROM "PRODUCT_VERSION" "PV" WHERE
("PRODUCT_ID"=28463 OR "PRODUCT_ID"=28480 OR "PRODUCT_ID"=28481 OR "PRODUCT_ID"=28482 OR
"PRODUCT_ID"=28508 OR "PRODUCT_ID"=28542 OR "PRODUCT_ID"=28693 OR "PRODUCT_ID"=28702 OR
"PRODUCT_ID"=28705 OR "PRODUCT_ID"=28708 OR "PRODUCT_ID"=28709 OR "PRODUCT_ID"=28718 OR
"PRODUCT_ID"=28719 OR "PRODUCT_ID"=28721 OR "PRODUCT_ID"=28722 OR "PRODUCT_ID"=28750 OR
"PRODUCT_ID"=28751 OR "PRODUCT_ID"=28754 OR "PRODUCT_ID"=28761 OR "PRODUCT_ID"=28762 OR
"PRODUCT_ID"=28764 OR "PRODUCT_ID"=28765 OR "PRODUCT_ID"=28766 OR "PRODUCT_ID"=28767 OR
"PRODUCT_ID"=28800 OR "PRODUCT_ID"=28801 OR "PRODUCT_ID"=28859 OR "PRODUCT_ID"=28860 OR
"PRODUCT_ID"=28861) AND :1="PRODUCT_ID" AND :2="VERSION_ID" (accessing 'NEW_NEWCOURSE' )
10 - SELECT "USER_ID","REAL_NAME","EMAIL","PHONE","REGION" FROM "CUSTOMER" "C" WHERE
:1="USER_ID" (accessing 'NEW_NEWCOURSE' )
SQL>
很明显看得出来,所有的表都是通过dblink访问的远程的库里的数据,而驱动站点选择了本地库
问了这位朋友得知,查询后返回的数据也就30条左右
要优化这个sql很简单,把驱动站点放到远程数据库上,过滤完数据在返回结果集
sql及执行计划如下:
SQL> set lines 200 pagesize 999
SQL> explain plan for select distinct u.id, c.real_name,u.user_name,c.phone,c.region,c.email,pv.name
2 from orders o , orderitem oi , users u ,customer c,product_version pv
3 where o.user_id = u.id
4 and u.id = c.user_id
5 and o.id = oi.order_id
6 and oi.product_id = pv.product_id
7 and oi.version_id = pv.version_id
8 and oi.product_id in (
9 28801
10 ,28800
11 ,28693
12 ,28542
13 ,28508
14 ,28766
15 ,28722
16 ,28750
17 ,28708
18 ,28762
19 ,28765
20 ,28702
21 ,28767
22 ,28718
23 ,28754
24 ,28705
25 ,28764
26 ,28721
27 ,28751
28 ,28709
29 ,28761
30 ,28719
31 ,28482
32 ,28861
33 ,28860
34 ,28480
35 ,28481
36 ,28859
37 ,28463
38 )
39 --and oi.version_id =1
40 and o.ordertime >= to_date('20130918', 'yyyymmdd')
41 and o.ordertime < to_date('20131012','yyyymmdd')
42 and o.orderstatusid = 1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3641462162
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 480 | 13 (8)| 00:00:01 | | |
| 1 | HASH UNIQUE | | 1 | 480 | 13 (8)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 1 | 480 | 12 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 1 | 382 | 10 (0)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 1 | 129 | 9 (0)| 00:00:01 | | |
| 5 | NESTED LOOPS | | 1 | 90 | 6 (0)| 00:00:01 | | |
| 6 | REMOTE | ORDERS | 1 | 48 | 4 (0)| 00:00:01 | NEW_N~ | R->S |
| 7 | REMOTE | USERS | 1 | 42 | 2 (0)| 00:00:01 | NEW_T~ | R->S |
| 8 | REMOTE | ORDERITEM | 1 | 39 | 3 (0)| 00:00:01 | NEW_N~ | R->S |
| 9 | REMOTE | PRODUCT_VERSION | 1 | 253 | 1 (0)| 00:00:01 | NEW_N~ | R->S |
| 10 | REMOTE | CUSTOMER | 1 | 98 | 2 (0)| 00:00:01 | NEW_N~ | R->S |
-----------------------------------------------------------------------------------------------------
Remote SQL Information (identified by operation id):
----------------------------------------------------
6 - SELECT "ID","USER_ID","ORDERSTATUSID","ORDERTIME" FROM "ORDERS" "O" WHERE
"ORDERTIME">=TO_DATE(' 2013-09-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "ORDERSTATUSID"=1
AND "ORDERTIME"<TO_DATE(' 2013-10-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss') (accessing
'NEW_NEWCOURSE' )
7 - SELECT "ID","USER_NAME" FROM "USERS" "U" WHERE :1="ID" (accessing 'NEW_TO_CORE' )
8 - SELECT "ORDER_ID","PRODUCT_ID","VERSION_ID" FROM "ORDERITEM" "OI" WHERE
("PRODUCT_ID"=28463 OR "PRODUCT_ID"=28480 OR "PRODUCT_ID"=28481 OR "PRODUCT_ID"=28482 OR
"PRODUCT_ID"=28508 OR "PRODUCT_ID"=28542 OR "PRODUCT_ID"=28693 OR "PRODUCT_ID"=28702 OR
"PRODUCT_ID"=28705 OR "PRODUCT_ID"=28708 OR "PRODUCT_ID"=28709 OR "PRODUCT_ID"=28718 OR
"PRODUCT_ID"=28719 OR "PRODUCT_ID"=28721 OR "PRODUCT_ID"=28722 OR "PRODUCT_ID"=28750 OR
"PRODUCT_ID"=28751 OR "PRODUCT_ID"=28754 OR "PRODUCT_ID"=28761 OR "PRODUCT_ID"=28762 OR
"PRODUCT_ID"=28764 OR "PRODUCT_ID"=28765 OR "PRODUCT_ID"=28766 OR "PRODUCT_ID"=28767 OR
"PRODUCT_ID"=28800 OR "PRODUCT_ID"=28801 OR "PRODUCT_ID"=28859 OR "PRODUCT_ID"=28860 OR
"PRODUCT_ID"=28861) AND :1="ORDER_ID" (accessing 'NEW_NEWCOURSE' )
9 - SELECT "PRODUCT_ID","VERSION_ID","NAME" FROM "PRODUCT_VERSION" "PV" WHERE
("PRODUCT_ID"=28463 OR "PRODUCT_ID"=28480 OR "PRODUCT_ID"=28481 OR "PRODUCT_ID"=28482 OR
"PRODUCT_ID"=28508 OR "PRODUCT_ID"=28542 OR "PRODUCT_ID"=28693 OR "PRODUCT_ID"=28702 OR
"PRODUCT_ID"=28705 OR "PRODUCT_ID"=28708 OR "PRODUCT_ID"=28709 OR "PRODUCT_ID"=28718 OR
"PRODUCT_ID"=28719 OR "PRODUCT_ID"=28721 OR "PRODUCT_ID"=28722 OR "PRODUCT_ID"=28750 OR
"PRODUCT_ID"=28751 OR "PRODUCT_ID"=28754 OR "PRODUCT_ID"=28761 OR "PRODUCT_ID"=28762 OR
"PRODUCT_ID"=28764 OR "PRODUCT_ID"=28765 OR "PRODUCT_ID"=28766 OR "PRODUCT_ID"=28767 OR
"PRODUCT_ID"=28800 OR "PRODUCT_ID"=28801 OR "PRODUCT_ID"=28859 OR "PRODUCT_ID"=28860 OR
"PRODUCT_ID"=28861) AND :1="PRODUCT_ID" AND :2="VERSION_ID" (accessing 'NEW_NEWCOURSE' )
10 - SELECT "USER_ID","REAL_NAME","EMAIL","PHONE","REGION" FROM "CUSTOMER" "C" WHERE
:1="USER_ID" (accessing 'NEW_NEWCOURSE' )
SQL>
很明显看得出来,所有的表都是通过dblink访问的远程的库里的数据,而驱动站点选择了本地库
问了这位朋友得知,查询后返回的数据也就30条左右
要优化这个sql很简单,把驱动站点放到远程数据库上,过滤完数据在返回结果集
最后这个sql能在2秒内执行完成