一次sql的order by优化

同事发过来sql,说加order by与不加order by 差距很大,不加的返回数据时间为0.156,加后速度为3s
### 1 #sql语句

点击(此处)折叠或打开

1. select (select count(1)
   
2.           from V_AS_201512040908286162 v
   
3.          where v.FENCEID = t.dbid) datanum,
   
4.        t.*,
   
5.        s.username
   
6.   from bds_geofence_tmp t
   
7.   left join uic_bds_staff s
   
8.     on t.staff_id = s.id
   
9.  where t.status != '0'
   
10.    and t.group_id = 102
    
11.  order by t.create_time desc;

### 2 #数据量及定义信息
##2.1 
V_AS_201512040908286162 视图DDL
create or replace view v_as_201512040908286162 as
select * from as_201512040908286162@dblink223 
union all 
select * from as_201512040908286162@dblink38;
视图数据量：
select count(1) from V_AS_201512040908286162;
13
##2.2
bds_geofence_tmp 表数据量
select count(1) from bds_geofence_tmp;
5138
select count(1) from uic_bds_staff;
33
### 3 # 执行计划信息
##3.1 没有order by的执行计划

点击(此处)折叠或打开

1. SQL> set autotrace trace
   
2. SQL> set timing on
   
3. SQL> select (select count(1)
   
4.   2 from V_AS_201512040908286162 v
   
5.          where v.FENCEID = t.dbid) datanum,
   
6.        t.*,
   
7.        s.username
   
8.   from bds_geofence_tmp t
   
9.   left join uic_bds_staff s
   
10.     on t.staff_id = s.id
    
11.  where t.status != '0'
    
12.    and t.group_id = 102;
    
13. 
    
14. 5009 rows selected.
    
15. Elapsed: 00:00:03.48
    
16. 
    
17. Execution Plan
    
18. ----------------------------------------------------------
    
19. Plan hash value: 1530652091
    
20. 
    
21. -----------------------------------------------------------------------------------------------------------------
    
22. | Id | Operation     | Name            | Rows    | Bytes | Cost (%CPU)| Time    | Inst     |IN-OUT|
    
23. -----------------------------------------------------------------------------------------------------------------
    
24. | 0 | SELECT STATEMENT |             | 5009 | 3967K| 37 (3)| 00:00:01 |     |    |
    
25. | 1 | SORT AGGREGATE |             | 1 | 13 |     |        |     |    |
    
26. | 2 | VIEW         | V_AS_201512040908286162 | 5 | 65 | 5 (0)| 00:00:01 |     |    |
    
27. | 3 | UNION-ALL     |             |    |    |     |        |     |    |
    
28. | 4 | REMOTE     | AS_201512040908286162    | 4 | 52 | 3 (0)| 00:00:01 | DBLIN~ | R->S |
    
29. | 5 | REMOTE     | AS_201512040908286162    | 1 | 13 | 2 (0)| 00:00:01 | DBLIN~ | R->S |
    
30. |* 6 | HASH JOIN RIGHT OUTER|             | 5009 | 3967K| 37 (3)| 00:00:01 |     |    |
    
31. | 7 | TABLE ACCESS FULL | UIC_BDS_STAFF        | 31 | 1457 | 18 (0)| 00:00:01 |     |    |
    
32. |* 8 | TABLE ACCESS FULL | BDS_GEOFENCE_TMP    | 5009 | 3737K| 18 (0)| 00:00:01 |     |    |
    
33. -----------------------------------------------------------------------------------------------------------------
    
34. 
    
35. Predicate Information (identified by operation id):
    
36. ---------------------------------------------------
    
37. 
    
38.    6 - access("T"."STAFF_ID"="S"."ID"(+))
    
39.    8 - filter("T"."GROUP_ID"=102 AND "T"."STATUS"<>'0')
    
40. 
    
41. Remote SQL Information (identified by operation id):
    
42. ----------------------------------------------------
    
43. 
    
44.    4 - SELECT "FENCEID" FROM "AS_201512040908286162" "AS_201512040908286162" WHERE "FENCEID"=:1
    
45.        (accessing 'DBLINK223' )
    
46. 
    
47.    5 - SELECT "FENCEID" FROM "AS_201512040908286162" "AS_201512040908286162" WHERE "FENCEID"=:1
    
48.        (accessing 'DBLINK38' )
    
49. 
    
50. 
    
51. Note
    
52. -----
    
53.    - dynamic sampling used for this statement (level=2)
    
54. 
    
55. 
    
56. Statistics
    
57. ----------------------------------------------------------
    
58.     102 recursive calls
    
59.      4 db block gets
    
60.     677 consistent gets
    
61.      77 physical reads
    
62.      0 redo size
    
63.  123179 bytes sent via SQL*Net to client
    
64.    4183 bytes received via SQL*Net from client
    
65.     335 SQL*Net roundtrips to/from client
    
66.      7 sorts (memory)
    
67.      0 sorts (disk)
    
68.    5009 rows processed

##3.2 有order by 的执行计划
测试环境,清除shared_pool 和 buffer_cache缓存
alter system flush shared_pool;
alter system flush buffer_cache;

点击(此处)折叠或打开

1. SQL> select (select count(1)
   
2.          from V_AS_201512040908286162 v
   
3.          where v.FENCEID = t.dbid) datanum,
   
4.        t.*,
   
5.        s.username
   
6.   from bds_geofence_tmp t
   
7.   left join uic_bds_staff s
   
8.     on t.staff_id = s.id
   
9.  where t.status != '0'
   
10.    and t.group_id = 102
    
11.  order by t.create_time desc;
    
12. 
    
13. 5009 rows selected.
    
14. Elapsed: 00:00:03.44
    
15. 
    
16. Execution Plan
    
17. ----------------------------------------------------------
    
18. Plan hash value: 2586892804
    
19. 
    
20. --------------------------------------------------------------------------------------------------------------------------
    
21. | Id | Operation     | Name             | Rows | Bytes |TempSpc| Cost (%CPU)| Time     | Inst |IN-OUT|
    
22. --------------------------------------------------------------------------------------------------------------------------
    
23. | 0 | SELECT STATEMENT |             | 5009 | 3967K|     | 894 (1)| 00:00:11 |     |     |
    
24. | 1 | SORT AGGREGATE |             | 1 | 13 |     |     |      |     |     |
    
25. | 2 | VIEW         | V_AS_201512040908286162 | 5 | 65 |     | 5 (0)| 00:00:01 |     |     |
    
26. | 3 | UNION-ALL     |             |     |     |     |     |      |     |     |
    
27. | 4 | REMOTE     | AS_201512040908286162     | 4 | 52 |     | 3 (0)| 00:00:01 | DBLIN~ | R->S |
    
28. | 5 | REMOTE     | AS_201512040908286162     | 1 | 13 |     | 2 (0)| 00:00:01 | DBLIN~ | R->S |
    
29. | 6 | SORT ORDER BY     |             | 5009 | 3967K| 4464K| 894 (1)| 00:00:11 |     |     |
    
30. |* 7 | HASH JOIN RIGHT OUTER|             | 5009 | 3967K|     | 37 (3)| 00:00:01 |     |     |
    
31. | 8 | TABLE ACCESS FULL | UIC_BDS_STAFF         | 31 | 1457 |     | 18 (0)| 00:00:01 |     |     |
    
32. |* 9 | TABLE ACCESS FULL | BDS_GEOFENCE_TMP     | 5009 | 3737K|     | 18 (0)| 00:00:01 |     |     |
    
33. --------------------------------------------------------------------------------------------------------------------------
    
34. 
    
35. Predicate Information (identified by operation id):
    
36. ---------------------------------------------------
    
37. 
    
38.    7 - access("T"."STAFF_ID"="S"."ID"(+))
    
39.    9 - filter("T"."GROUP_ID"=102 AND "T"."STATUS"<>'0')
    
40. 
    
41. Remote SQL Information (identified by operation id):
    
42. ----------------------------------------------------
    
43. 
    
44.    4 - SELECT "FENCEID" FROM "AS_201512040908286162" "AS_201512040908286162" WHERE "FENCEID"=:1 (accessing
    
45.        'DBLINK223' )
    
46. 
    
47.    5 - SELECT "FENCEID" FROM "AS_201512040908286162" "AS_201512040908286162" WHERE "FENCEID"=:1 (accessing
    
48.        'DBLINK38' )
    
49. 
    
50. Note
    
51. -----
    
52.    - dynamic sampling used for this statement (level=2)
    
53. 
    
54. 
    
55. Statistics
    
56. ----------------------------------------------------------
    
57.     102 recursive calls
    
58.      4 db block gets
    
59.     346 consistent gets
    
60.      77 physical reads
    
61.      0 redo size
    
62.  123180 bytes sent via SQL*Net to client
    
63.    4183 bytes received via SQL*Net from client
    
64.     335 SQL*Net roundtrips to/from client
    
65.      8 sorts (memory)
    
66.      0 sorts (disk)
    
67.    5009 rows processed

##3.3 将我们关心的结果进行整理对比如下：
结果1 : 无order by
5009 rows selected.
Elapsed: 00:00:03.48

102  recursive calls
 4  db block gets
677  consistent gets
77  physical reads
 0  redo size
 123179  bytes sent via SQL*Net to client
   4183  bytes received via SQL*Net from client
335  SQL*Net roundtrips to/from client
 7  sorts (memory)
 0  sorts (disk)
   5009  rows processed

结果2 : 有order by
5009 rows selected.
Elapsed: 00:00:03.44

102  recursive calls
 4  db block gets
346  consistent gets
77  physical reads
 0  redo size
 123180  bytes sent via SQL*Net to client
   4183  bytes received via SQL*Net from client
335  SQL*Net roundtrips to/from client
 8  sorts (memory)
 0  sorts (disk)
 5009  rows processed

从结果上进行初步分析,观察整理的结果,不难发现其实两个查询消耗的时间是差不多的,相对来说结果1的consistent gets多些,从查询时间来分析两者相差无几,询问得知,文章开始的时间是怎么得来的,
得到的回答是使用pl/sql developer得到的结果时间,于是考虑到可能是pl/sql developer的时间并不一定是实际的执行时间,当行多时,会优先返回一页的数据,但使用order by时必须等所有数据查完后
进行排序再来返回,这样一来,造成数据查询的时间差异;
从总数据量及返回数据量以及查询时间,觉得3秒来处理5000多行数据有些慢,排除完order by 加与不加的问题后,于是我们来解决另外
一个问题：这个完成的语句为什么会这么慢,从语句来看,其中有个标量子查询select count(1) from V_AS_201512040908286162 v where v.FENCEID = t.dbid 而且这个标量子查询所查的视图使用到
dblink对,标量子查询+dblink,我们第一步来看看是不是这个问题,标量子查询的运行次数会依照主查询的distinct条件来进行,观察主查询与标量子查询的连接键dbid,这个字段是个主键,意思为这个标量子查询
要执行5000多次,当使用dblink,没有使用driving_site的hint时,默认是会将remote的数据传到本地进行处理

### 4 # 设置10046跟踪查看
765行 ：=====================
PARSING IN CURSOR #139715891019864 len=293 dep=0 uid=103 oct=3 lid=103 tim=1449200078990904 hv=2867587325 ad='b8f12208' sqlid='f2pt4wypfru7x'
select (select count(1)
          from V_AS_201512040908286162 v
         where v.FENCEID = t.dbid) datanum,
       t.*,
       s.username
  from bds_geofence_tmp t
  left join uic_bds_staff s
    on t.staff_id = s.id
 where t.status != '0'
   and t.group_id = 102
 order by t.create_time desc
END OF STMT
779行 : PARSE #139715891019864:c=44002,e=435129,p=76,cr=223,cu=1,mis=1,r=0,dep=0,og=1,plh=2586892804,tim=1449200078990903
EXEC #139715891019864:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2586892804,tim=1449200078991020
WAIT #139715891019864: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=1658974 tim=1449200078991084
WAIT #139715891019864: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=1658974 tim=1449200078991463
WAIT #139715891019864: nam='SQL*Net message from dblink' ela= 36810 driver id=1413697536 #bytes=1 p3=0 obj#=1658974 tim=1449200079028298
WAIT #139715891019864: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=1658974 tim=1449200079028531
WAIT #139715891019864: nam='SQL*Net message from dblink' ela= 4643 driver id=1413697536 #bytes=1 p3=0 obj#=1658974 tim=1449200079033200
WAIT #139715891019864: nam='SQL*Net message to dblink' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=1658974 tim=1449200079033356
WAIT #139715891019864: nam='SQL*Net message from dblink' ela= 111 driver id=1413697536 #bytes=1 p3=0 obj#=1658974 tim=1449200079033488
......
41738行 : FETCH #139715891019864:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=2586892804,tim=1449200083230355
WAIT #139715891019864: nam='SQL*Net message from client' ela= 1957 driver id=1650815232 #bytes=1 p3=0 obj#=1658974 tim=1449200083232328
WAIT #139715891019864: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=1658974 tim=1449200083232361
.....
41859行 ： WAIT #139715891019864: nam='SQL*Net message from client' ela= 1936 driver id=1650815232 #bytes=1 p3=0 obj#=1658974 tim=1449200083313162
WAIT #139715891019864: nam='SQL*Net message to client' ela= 0 driver id=1650815232 #bytes=1 p3=0 obj#=1658974 tim=1449200083313193
FETCH #139715891019864:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=13,dep=0,og=1,plh=2586892804,tim=1449200083313258
41862行 ： STAT #139715891019864 id=1 cnt=5009 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=0 pr=0 pw=0 time=3561550 us)'
STAT #139715891019864 id=2 cnt=10 pid=1 pos=1 obj=1658915 op='VIEW  V_AS_201512040908286162 (cr=0 pr=0 pw=0 time=3548344 us cost=5 size=65 card=5)'
STAT #139715891019864 id=3 cnt=10 pid=2 pos=1 obj=0 op='UNION-ALL  (cr=0 pr=0 pw=0 time=3544663 us)'
STAT #139715891019864 id=4 cnt=10 pid=3 pos=1 obj=0 op='REMOTE  AS_201512040908286162 (cr=0 pr=0 pw=0 time=1403003 us cost=3 size=52 card=4)'
STAT #139715891019864 id=5 cnt=0 pid=3 pos=2 obj=0 op='REMOTE  AS_201512040908286162 (cr=0 pr=0 pw=0 time=2126388 us cost=2 size=13 card=1)'
STAT #139715891019864 id=6 cnt=5009 pid=0 pos=2 obj=0 op='SORT ORDER BY (cr=129 pr=0 pw=0 time=3593215 us cost=894 size=4062299 card=5009)'
STAT #139715891019864 id=7 cnt=5009 pid=6 pos=1 obj=0 op='HASH JOIN RIGHT OUTER (cr=129 pr=0 pw=0 time=12792 us cost=37 size=4062299 card=5009)'
STAT #139715891019864 id=8 cnt=31 pid=7 pos=1 obj=1471601 op='TABLE ACCESS FULL UIC_BDS_STAFF (cr=68 pr=0 pw=0 time=113 us cost=18 size=1457 card=31)'
STAT #139715891019864 id=9 cnt=5009 pid=7 pos=2 obj=1658974 op='TABLE ACCESS FULL BDS_GEOFENCE_TMP (cr=61 pr=0 pw=0 time=4521 us cost=18 size=3826876 card=5009)'

### 5 # 分析
通过对trace文件的观察,行数标记为手动添加,4万行左右的都与dblink通讯有关,我们先去讨论表的连接方式,是不是该走索引等等,就现有的情况来讲,排序是在内存中进行的,没有使用的临时表空间,
通过改写语句来解决这个标量子查询来提高查询速度,order by速度慢有时候不一定是sort_area_size不足导致

### 6 # 改写sql语句为

点击(此处)折叠或打开

1. SQL> select t.*,v.datanum from bds_geofence_tmp t,
   
2.      (select fenceid,count(1) as datanum from V_AS_201512040908286162 group by fenceid) v,uic_bds_staff s
   
3. where t.dbid=v.fenceid(+)
   
4. and t.staff_id = s.id(+)
   
5. and t.group_id = 102
   
6. and t.status != '0'
   
7. order by t.create_time desc;
   
8. 
   
9. 5009 rows selected.
   
10. Elapsed: 00:00:00.20
    
11. 
    
12. Execution Plan
    
13. ----------------------------------------------------------
    
14. Plan hash value: 1125746558
    
15. 
    
16. --------------------------------------------------------------------------------------------------------------------------
    
17. | Id | Operation     | Name             | Rows | Bytes |TempSpc| Cost (%CPU)| Time     | Inst |IN-OUT|
    
18. --------------------------------------------------------------------------------------------------------------------------
    
19. | 0 | SELECT STATEMENT |             | 5009 | 3864K|     | 860 (1)| 00:00:11 |     |     |
    
20. | 1 | SORT ORDER BY     |             | 5009 | 3864K| 4016K| 860 (1)| 00:00:11 |     |     |
    
21. |* 2 | HASH JOIN RIGHT OUTER|             | 5009 | 3864K|     | 25 (8)| 00:00:01 |     |     |
    
22. | 3 | VIEW      |             | 491 | 12766 |     | 6 (17)| 00:00:01 |     |     |
    
23. | 4 | HASH GROUP BY |             | 491 | 6383 |     | 6 (17)| 00:00:01 |     |     |
    
24. | 5 | VIEW     | V_AS_201512040908286162 | 491 | 6383 |     | 5 (0)| 00:00:01 |     |     |
    
25. | 6 | UNION-ALL |             |     |     |     |     |      |     |     |
    
26. | 7 | REMOTE     | AS_201512040908286162     | 409 | 5317 |     | 3 (0)| 00:00:01 | DBLIN~ | R->S |
    
27. | 8 | REMOTE     | AS_201512040908286162     | 82 | 1066 |     | 2 (0)| 00:00:01 | DBLIN~ | R->S |
    
28. |* 9 | TABLE ACCESS FULL | BDS_GEOFENCE_TMP     | 5009 | 3737K|     | 18 (0)| 00:00:01 |     |     |
    
29. --------------------------------------------------------------------------------------------------------------------------
    
30. 
    
31. Predicate Information (identified by operation id):
    
32. ---------------------------------------------------
    
33. 
    
34.    2 - access("T"."DBID"="V"."FENCEID"(+))
    
35.    9 - filter("T"."GROUP_ID"=102 AND "T"."STATUS"<>'0')
    
36. 
    
37. Remote SQL Information (identified by operation id):
    
38. ----------------------------------------------------
    
39. 
    
40.    7 - SELECT "FENCEID" FROM "AS_201512040908286162" "AS_201512040908286162" (accessing 'DBLINK223' )
    
41. 
    
42.    8 - SELECT "FENCEID" FROM "AS_201512040908286162" "AS_201512040908286162" (accessing 'DBLINK38' )
    
43. 
    
44. 
    
45. Note
    
46. -----
    
47.    - dynamic sampling used for this statement (level=2)
    
48. 
    
49. 
    
50. Statistics
    
51. ----------------------------------------------------------
    
52.      99 recursive calls
    
53.      0 db block gets
    
54.     210 consistent gets
    
55.      75 physical reads
    
56.      0 redo size
    
57.  123090 bytes sent via SQL*Net to client
    
58.    4183 bytes received via SQL*Net from client
    
59.     335 SQL*Net roundtrips to/from client
    
60.      8 sorts (memory)
    
61.      0 sorts (disk)
    
62.        5009 rows processed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28507395/viewspace-1853389/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28507395/viewspace-1853389/