今天一同事说，在where条件下>= 执行得很快，<=执行得很慢
语句如下：
select connector_id,
conn.sidea_port_id,
conn.sideb_port_id,
conn.rsc_spec_id as line_rsc_spec_id,
PG_TRP_FUNCTION.Fun_GetCodeByPortAndSpec(1,
conn.sidea_port_id,
conn.sidea_port_spec_id) as up_dev_code,
PG_TRP_FUNCTION.Fun_GetCodeByPortAndSpec(2,
conn.sidea_port_id,
conn.sidea_port_spec_id) as up_port_code,
cab.code as cable_code,
conn.code as line_code,
PG_TRP_FUNCTION.Fun_GetCodeByPortAndSpec(1,
conn.sideb_port_id,
conn.sideb_port_spec_id) as down_dev_code,
PG_TRP_FUNCTION.Fun_GetCodeByPortAndSpec(2,
conn.sideb_port_id,
conn.sideb_port_spec_id) as down_port_code,
PG_TRP_FUNCTION.Fun_GetDevIdByPortAndSpec(CONN.SIDEA_PORT_ID,
CONN.SIDEA_PORT_SPEC_ID) AS UP_DEVICE_ID,
PG_TRP_FUNCTION.Fun_GetDevIdByPortAndSpec(CONN.SIDEB_PORT_ID,
CONN.SIDEB_PORT_SPEC_ID) AS DOWN_DEVICE_ID,
PG_TRP_FUNCTION.Fun_GetDevSpecIdByPortAndSpec(conn.sidea_port_id,
conn.sidea_port_spec_id) as up_dev_rsc_spec_id,
PG_TRP_FUNCTION.Fun_GetDevSpecIdByPortAndSpec(conn.sideb_port_id,
conn.sideb_port_spec_id) as down_dev_rsc_spec_id
from connector conn, device cab, user_side_port usp
where conn.device_id = cab.device_id
and conn.sidea_port_id = usp.port_id
and conn.rsc_status_cd = 1
and conn.side_id in
(select side_id
from side
where node_id_a in
(select nd.node_id
from device dev, node nd
where dev.device_id = nd.device_id
and dev.rsc_spec_id in (10301001, 10301002, 10301003)
and dev.code like '79G/J0010101%'
and dev.area_id = 18 ))
and usp.code >= '0017'
and rownum < 20 ;
####################################
# and usp.code >= '0017'的执行计划：#
###################################
SELECT STATEMENT, GOAL = CHOOSE 1018 19 1881 1018
COUNT STOPKEY
NESTED LOOPS 1018 64 6336 1018
NESTED LOOPS 890 64 5504 890
NESTED LOOPS 762 64 4416 762
NESTED LOOPS 31 43 946 31
VIEW SYS VW_NSO_1 6 1 13
SORT UNIQUE 1 34
NESTED LOOPS 6 1 34 6
TABLE ACCESS BY INDEX ROWID RM DEVICE 4 1 24 4
INDEX RANGE SCAN RM UIDX_DEV_CODE 3 1 3
TABLE ACCESS BY INDEX ROWID RM NODE 2 1 10 2
INDEX UNIQUE SCAN RM UIDX_NODE_DEVICE 1 1 1
TABLE ACCESS BY INDEX ROWID RM SIDE 23 43 387 23
INDEX RANGE SCAN RM UIDX_SIDE_NODEA 2 43 2
TABLE ACCESS BY INDEX ROWID RM CONNECTOR 17 1 47 17
INDEX RANGE SCAN RM IDX_CONNECTOR_SIDE 2 33 2
TABLE ACCESS BY INDEX ROWID RM DEVICE 2 1 17 2
INDEX UNIQUE SCAN RM PK_DEVICE 1 1 1
TABLE ACCESS BY INDEX ROWID RM USER_SIDE_PORT 2 1 13 2
INDEX UNIQUE SCAN RM PK_USER_SIDE_PORT 1 1 1
####################################
# and usp.code <= '0017'的执行计划：#
###################################
SELECT STATEMENT, GOAL = CHOOSE 769 1 99 769
COUNT STOPKEY
NESTED LOOPS 769 1 99 769
HASH JOIN 767 1 82 767
TABLE ACCESS BY INDEX ROWID RM CONNECTOR 17 1 47 17
NESTED LOOPS 762 64 4416 762
NESTED LOOPS 31 43 946 31
VIEW SYS VW_NSO_1 6 1 13
SORT UNIQUE 1 34
NESTED LOOPS 6 1 34 6
TABLE ACCESS BY INDEX ROWID RM DEVICE 4 1 24 4
INDEX RANGE SCAN RM UIDX_DEV_CODE 3 1 3
TABLE ACCESS BY INDEX ROWID RM NODE 2 1 10 2
INDEX UNIQUE SCAN RM UIDX_NODE_DEVICE 1 1 1
TABLE ACCESS BY INDEX ROWID RM SIDE 23 43 387 23
INDEX RANGE SCAN RM UIDX_SIDE_NODEA 2 43 2
INDEX RANGE SCAN RM IDX_CONNECTOR_SIDE 2 33 2
TABLE ACCESS BY INDEX ROWID RM USER_SIDE_PORT 4 11149 144937 4
INDEX RANGE SCAN RM UIDX_USP_CODE 2 1 2
TABLE ACCESS BY INDEX ROWID RM DEVICE 2 1 17 2
INDEX UNIQUE SCAN RM PK_DEVICE 1 1 1
很明显>= 走的是嵌套， <= 走的HASH JOIN
加上暗示
/*+USE_NL(conn, cab, usp) */
-----------------------------------------------------------------
然后同事继续提：
小小修改一下where条件
and usp.code <= '0017'
and usp.code >= '0014'
当数值不同时，执行很快，当数值相同时，执行相当慢
继续看执行计划，这次是带/*+USE_NL(conn, cab, usp) */的执行计划
当执行：
and usp.code <= '0017'
and usp.code >= '0014'
执行计划：
SELECT STATEMENT, GOAL = CHOOSE 1018 19 1881 1018 SELE
COUNT STOPKEY COU
NESTED LOOPS 1018 64 6336 1018 NE
NESTED LOOPS 890 64 5504 890 N
NESTED LOOPS 762 64 4416 762
NESTED LOOPS 31 43 946 31
VIEW SYS VW_NSO_1 6 1 13
SORT UNIQUE 1 34
NESTED LOOPS 6 1 34 6
TABLE ACCESS BY INDEX ROWID RM DEVICE 4 1 24 4
INDEX RANGE SCAN RM UIDX_DEV_CODE 3 1 3
TABLE ACCESS BY INDEX ROWID RM NODE 2 1 10 2
INDEX UNIQUE SCAN RM UIDX_NODE_DEVICE 1 1 1
TABLE ACCESS BY INDEX ROWID RM SIDE 23 43 387 23
INDEX RANGE SCAN RM UIDX_SIDE_NODEA 2 43 2
TABLE ACCESS BY INDEX ROWID RM CONNECTOR 17 1 47 17
INDEX RANGE SCAN RM IDX_CONNECTOR_SIDE 2 33 2
TABLE ACCESS BY INDEX ROWID RM DEVICE 2 1 17 2
INDEX UNIQUE SCAN RM PK_DEVICE 1 1 1
TABLE ACCESS BY INDEX ROWID RM USER_SIDE_PORT 2 1 13 2 T
INDEX UNIQUE SCAN RM PK_USER_SIDE_PORT 1 1 1
当执行：
and usp.code <= '0017'
and usp.code >= '0017'
执行计划
SELECT STATEMENT, GOAL = CHOOSE 332 1 99 332
COUNT STOPKEY
NESTED LOOPS 332 1 99 332
NESTED LOOPS 330 1 82 330
NESTED LOOPS 202 64 4672 202
NESTED LOOPS 10 64 1664 10
VIEW SYS VW_NSO_1 6 1 13
SORT UNIQUE 1 34
NESTED LOOPS 6 1 34 6
TABLE ACCESS BY INDEX ROWID RM DEVICE 4 1 24 4
INDEX RANGE SCAN RM UIDX_DEV_CODE 3 1 3
TABLE ACCESS BY INDEX ROWID RM NODE 2 1 10 2
INDEX UNIQUE SCAN RM UIDX_NODE_DEVICE 1 1 1
TABLE ACCESS BY INDEX ROWID RM USER_SIDE_PORT 2 64 832 2
INDEX RANGE SCAN RM UIDX_USP_CODE 1 64 1
TABLE ACCESS BY INDEX ROWID RM CONNECTOR 3 1 47 3
INDEX RANGE SCAN RM IDX_CONNECTOR_PORTA 2 1 2
TABLE ACCESS BY INDEX ROWID RM SIDE 2 1 9 2
INDEX UNIQUE SCAN RM PK_SIDE 1 1 1
TABLE ACCESS BY INDEX ROWID RM DEVICE 2 1 17 2
INDEX UNIQUE SCAN RM PK_DEVICE 1 1 1
看到表连接的顺序不同
解决方法，使用/*+ORDERED*/
调优后的SQL语句：
select /*+USE_NL(conn, cab, usp) ORDERED*/ connector_id,
conn.sidea_port_id,
conn.sideb_port_id,
conn.rsc_spec_id as line_rsc_spec_id,
PG_TRP_FUNCTION.Fun_GetCodeByPortAndSpec(1,
conn.sidea_port_id,
conn.sidea_port_spec_id) as up_dev_code,
PG_TRP_FUNCTION.Fun_GetCodeByPortAndSpec(2,
conn.sidea_port_id,
conn.sidea_port_spec_id) as up_port_code,
cab.code as cable_code,
conn.code as line_code,
PG_TRP_FUNCTION.Fun_GetCodeByPortAndSpec(1,
conn.sideb_port_id,
conn.sideb_port_spec_id) as down_dev_code,
PG_TRP_FUNCTION.Fun_GetCodeByPortAndSpec(2,
conn.sideb_port_id,
conn.sideb_port_spec_id) as down_port_code,
PG_TRP_FUNCTION.Fun_GetDevIdByPortAndSpec(CONN.SIDEA_PORT_ID,
CONN.SIDEA_PORT_SPEC_ID) AS UP_DEVICE_ID,
PG_TRP_FUNCTION.Fun_GetDevIdByPortAndSpec(CONN.SIDEB_PORT_ID,
CONN.SIDEB_PORT_SPEC_ID) AS DOWN_DEVICE_ID,
PG_TRP_FUNCTION.Fun_GetDevSpecIdByPortAndSpec(conn.sidea_port_id,
conn.sidea_port_spec_id) as up_dev_rsc_spec_id,
PG_TRP_FUNCTION.Fun_GetDevSpecIdByPortAndSpec(conn.sideb_port_id,
conn.sideb_port_spec_id) as down_dev_rsc_spec_id
from connector conn, device cab, user_side_port usp
where conn.device_id = cab.device_id
and conn.sidea_port_id = usp.port_id
and conn.rsc_status_cd = 1
and conn.side_id in
(select side_id
from side
where node_id_a in
(select nd.node_id
from device dev, node nd
where dev.device_id = nd.device_id
and dev.rsc_spec_id in (10301001, 10301002, 10301003)
and dev.code like '79G/J0010101%'
and dev.area_id = 18 ))
and usp.code >= '0017'
and usp.code <= '0017'
and rownum < 20 ;
阅读(1647) | 评论(0) | 转发(0) |
