|
今天调优一条SQL语句，由于SQL比较复杂，用autotrace很难一眼看出哪里出了问题，直接上10046。 SELECT AB.*
FROM (SELECT A.*, rownum RN
FROM (SELECT *
from (SELECT DISTINCT (D.DEVICE_ID), F.FUNCTION_LOCATION_ID
from GG_device D,
GG_CLASSIFY_CARD C,
GG_function_location F,
GG_fl_device L,
GG_tech_object_node n,
(SELECT N.TECH_OBJECT_ID
FROM GG_TECH_OBJECT_NODE N
WHERE N.NODE_TYPE = 2
START WITH N.TECH_OBJECT_ID = 15773325
CONNECT BY PRIOR
N.TECH_OBJECT_NODE_ID = N.PARENT_ID) TAB
where F.FUNCTION_LOCATION_ID = L.FUNCTION_LOCATION_ID
and L.Device_Id = d.device_id
and d.classify_id = c.classify_id
AND EXISTS
(SELECT 1
FROM GG_TECH_OBJECT_NODE N
WHERE N.TECH_OBJECT_ID = D.DEVICE_ID
AND N.NODE_TYPE = 2)
AND D.CURRENT_STATUS = 0
AND D.IS_SHARE_DEVICE = 1
AND TAB.TECH_OBJECT_ID = D.DEVICE_ID
and n.tech_object_id = f.function_location_id
AND F.SITE_ID = 1021
AND C.ALIAS_NAME IN ('A176')
union all
SELECT DISTINCT (D.DEVICE_ID), F.FUNCTION_LOCATION_ID
FROM GG_DEVICE D,
GG_CLASSIFY_CARD C,
GG_FUNCTION_LOCATION F,
GG_tech_object_node n,
(SELECT N.TECH_OBJECT_ID
FROM GG_TECH_OBJECT_NODE N
WHERE N.NODE_TYPE = 2
START WITH N.TECH_OBJECT_ID = 15773325
CONNECT BY PRIOR
N.TECH_OBJECT_NODE_ID = N.PARENT_ID) TAB
WHERE D.CLASSIFY_ID = C.CLASSIFY_ID
AND F.FUNCTION_LOCATION_ID(+) =
D.FUNCTION_LOCATION_ID
and n.tech_object_id = f.function_location_id
AND EXISTS
(SELECT 1
FROM GG_TECH_OBJECT_NODE N
WHERE N.TECH_OBJECT_ID = D.DEVICE_ID
AND N.NODE_TYPE = 2)
AND D.CURRENT_STATUS = 0
AND D.IS_SHARE_DEVICE = 0
AND TAB.TECH_OBJECT_ID = D.DEVICE_ID
AND F.SITE_ID = 1021
AND C.ALIAS_NAME IN ('A176'))) A
WHERE ROWNUM <= 25) AB
WHERE AB.RN > 0;
已用时间: 00: 00: 05.56
----------------------------------------------------------
Plan hash value: 1124467031
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 67248 | 28951 (1)| 00:05:48 |
|* 1 | VIEW | | 18 | 67248 | 28951 (1)| 00:05:48 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 18 | 67014 | 28951 (1)| 00:05:48 |
|* 4 | SORT ORDER BY STOPKEY | | 18 | 67014 | 28951 (1)| 00:05:48 |
| 5 | VIEW | | 18 | 67014 | 28950 (1)| 00:05:48 |
| 6 | UNION-ALL | | | | | |
| 7 | HASH UNIQUE | | 6 | 2064 | 15146 (1)| 00:03:02 |
|* 8 | HASH JOIN | | 6 | 2064 | 15145 (1)| 00:03:02 |
| 9 | TABLE ACCESS BY INDEX ROWID | GG_TECH_OBJECT_NODE | 1 | 102 | 3 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 6 | 2022 | 5842 (1)| 00:01:11 |
| 11 | NESTED LOOPS SEMI | | 5 | 1175 | 5827 (1)| 00:01:10 |
| 12 | NESTED LOOPS | | 5 | 1125 | 5817 (1)| 00:01:10 |
|* 13 | HASH JOIN | | 172 | 32508 | 5645 (1)| 00:01:08 |
|* 14 | HASH JOIN | | 77 | 13629 | 5601 (1)| 00:01:08 |
|* 15 | TABLE ACCESS FULL | GG_CLASSIFY_CARD | 1 | 93 | 16 (0)| 00:00:01 |
|* 16 | TABLE ACCESS FULL | GG_DEVICE | 22527 | 1847K| 5584 (1)| 00:01:08 |
| 17 | TABLE ACCESS FULL | GG_FL_DEVICE | 74829 | 876K| 43 (3)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID| GG_FUNCTION_LOCATION | 1 | 36 | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PK_GG_FUNCTION_LOCATION | 1 | | 0 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | IDX_TECH_NODE_ID | 482K| 4712K| 2 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | IDX_TECH_OBJECT_ID | 1 | | 2 (0)| 00:00:01 |
| 22 | VIEW | | 1762K| 11M| 9291 (1)| 00:01:52 |
|* 23 | FILTER | | | | | |
|* 24 | CONNECT BY WITH FILTERING | | | | | |
| 25 | TABLE ACCESS BY INDEX |