分类： Oracle
2009-07-23 16:01:04
错误的执行计划：
SELECT /*+no_merge(sa) ORDERED use_nl(sa, conn, cab, usp) INDEX_JOIN(O)*/
ROWNUM num, conn.connector_id, conn.code, conn.rsc_status_cd,
conn.rsc_spec_id, conn.line_ability, conn.sidea_port_id,
conn.sidea_port_spec_id, conn.sideb_port_id,
conn.sideb_port_spec_id, sa.device_id
FROM (SELECT /*+ORDERED*/
s.side_id, sd.device_id
FROM device dev INNER JOIN node nd
ON nd.device_id = dev.device_id
INNER JOIN side s ON s.node_id_a = nd.node_id
INNER JOIN side_device sd ON sd.side_id = s.side_id
WHERE dev.code = 'MDF-0315'
AND dev.rsc_spec_id IN (10301001, 10301002, 10301003)
AND dev.area_id = 127) sa,
connector conn,
user_side_port usp
WHERE conn.side_id = sa.side_id
AND conn.device_id = sa.device_id
AND conn.sidea_port_id = usp.port_id
AND conn.sidea_port_id IS NOT NULL
AND conn.sideb_port_id IS NOT NULL
AND usp.code >= '009-05-00012'
AND usp.code <= '009-05-00099'
AND ROWNUM <= 100;
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 36 (0)|
| 1 | COUNT STOPKEY | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | USER_SIDE_PORT | 1 | 15 | 1 (0)|
| 3 | NESTED LOOPS | | 1 | 95 | 36 (0)|
| 4 | NESTED LOOPS | | 1 | 80 | 35 (0)|
| 5 | VIEW | | 1 | 12 | 32 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID | SIDE_DEVICE | 1 | 12 | 3 (0)|
| 7 | NESTED LOOPS | | 1 | 70 | 32 (0)|
| 8 | NESTED LOOPS | | 1 | 58 | 29 (0)|
| 9 | NESTED LOOPS | | 1 | 46 | 6 (0)|
| 10 | TABLE ACCESS BY INDEX ROWID| DEVICE | 1 | 34 | 4 (0)|
| 11 | INDEX RANGE SCAN | UIDX_DEV_CODE | 1 | | 3 (0)|
| 12 | TABLE ACCESS BY INDEX ROWID| NODE | 1 | 12 | 2 (0)|
| 13 | INDEX UNIQUE SCAN | UIDX_NODE_DEVICE | 1 | | 1 (0)|
| 14 | TABLE ACCESS BY INDEX ROWID | SIDE | 1 | 12 | 23 (0)|
| 15 | INDEX RANGE SCAN | UIDX_SIDE_NODEA | 24 | | 2 (0)|
| 16 | INDEX RANGE SCAN | IDX_SIDE_DEVICE_SIDE | 1 | | 2 (0)|
| 17 | TABLE ACCESS BY INDEX ROWID | CONNECTOR | 1 | 68 | 3 (0)|
| 18 | INDEX RANGE SCAN | IDX_CONNECTOR_DEVICE_SIDE | 1 | | 2 (0)|
| 19 | INDEX RANGE SCAN | UIDX_USP_CODE | 1 | | 1 (0)|
-------------------------------------------------------------------------------------------------------
跑的性能：
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SELECT /*+no_merge(sa) ORDERED use_nl(sa, conn, cab, usp) INDEX_JOIN(O)*/
ROWNUM num, conn.connector_id, conn.code, conn.rsc_status_cd,
conn.rsc_spec_id, conn.line_ability, conn.sidea_port_id,
conn.sidea_port_spec_id, conn.sideb_port_id,
conn.sideb_port_spec_id, sa.device_id
FROM (SELECT /*+ORDERED*/
s.side_id, sd.device_id
FROM device dev INNER JOIN node nd
ON nd.device_id = dev.device_id
INNER JOIN side s ON s.node_id_a = nd.node_id
INNER JOIN side_device sd ON sd.side_id = s.side_id
WHERE dev.code = 'MDF-0315'
AND dev.rsc_spec_id IN (10301001, 10301002, 10301003)
AND dev.area_id = 127) sa,
connector conn,
user_side_port usp
WHERE conn.side_id = sa.side_id
AND conn.device_id = sa.device_id
AND conn.sidea_port_id = usp.port_id
AND conn.sidea_port_id IS NOT NULL
AND conn.sideb_port_id IS NOT NULL
AND usp.code >= '009-05-00012'
AND usp.code <= '009-05-00099'
AND ROWNUM <= 100
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 1163.75 1145.34 54 234017447 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 1163.76 1145.36 54 234017447 0 0
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 70
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY (cr=0 pr=0 pw=0 time=22 us)
0 TABLE ACCESS BY INDEX ROWID USER_SIDE_PORT (cr=0 pr=0 pw=0 time=19 us)
229444027 NESTED LOOPS (cr=1439466 pr=54 pw=0 time=230349716 us)
12387 NESTED LOOPS (cr=2575 pr=54 pw=0 time=136469 us)
29 VIEW (cr=101 pr=0 pw=0 time=1659 us)
29 TABLE ACCESS BY INDEX ROWID SIDE_DEVICE (cr=101 pr=0 pw=0 time=1545 us)
47 NESTED LOOPS (cr=61 pr=0 pw=0 time=7138 us)
18 NESTED LOOPS (cr=23 pr=0 pw=0 time=313 us)
1 NESTED LOOPS (cr=8 pr=0 pw=0 time=105 us)
1 TABLE ACCESS BY INDEX ROWID DEVICE (cr=4 pr=0 pw=0 time=60 us)
1 INDEX RANGE SCAN UIDX_DEV_CODE (cr=3 pr=0 pw=0 time=51 us)(object id 92467)
1 TABLE ACCESS BY INDEX ROWID NODE (cr=4 pr=0 pw=0 time=44 us)
1 INDEX UNIQUE SCAN UIDX_NODE_DEVICE (cr=3 pr=0 pw=0 time=27 us)(object id 92474)
18 TABLE ACCESS BY INDEX ROWID SIDE (cr=15 pr=0 pw=0 time=205 us)
18 INDEX RANGE SCAN UIDX_SIDE_NODEA (cr=3 pr=0 pw=0 time=24 us)(object id 92496)
29 INDEX RANGE SCAN IDX_SIDE_DEVICE_SIDE (cr=38 pr=0 pw=0 time=363 us)(object id 92498)
12387 TABLE ACCESS BY INDEX ROWID CONNECTOR (cr=2474 pr=54 pw=0 time=132713 us)
12387 INDEX RANGE SCAN IDX_CONNECTOR_DEVICE_SIDE (cr=126 pr=7 pw=0 time=41528 us)(object id 95150)
229431640 INDEX RANGE SCAN UIDX_USP_CODE (cr=1436891 pr=0 pw=0 time=931034 us)(object id 95133)
正确的执行计划：
SELECT /*+no_merge(sa) USE_NL(sa, conn, usp) ORDERED NO_INDEX(usp UIDX_USP_CODE)*/
ROWNUM num, conn.connector_id, conn.code, conn.rsc_status_cd,
conn.rsc_spec_id, conn.line_ability, conn.sidea_port_id,
conn.sidea_port_spec_id, conn.sideb_port_id,
conn.sideb_port_spec_id, sa.device_id, usp.code
FROM (SELECT /*+ORDERED*/
s.side_id, sd.device_id
FROM device dev INNER JOIN node nd
ON nd.device_id = dev.device_id
INNER JOIN side s ON s.node_id_a = nd.node_id
INNER JOIN side_device sd ON sd.side_id = s.side_id
WHERE dev.code = 'MDF-0315'
AND dev.rsc_spec_id IN (10301001, 10301002, 10301003)
AND dev.area_id = 127) sa,
connector conn,
user_side_port usp
WHERE conn.side_id = sa.side_id
AND conn.device_id = sa.device_id
AND conn.sidea_port_id = usp.port_id
AND conn.sidea_port_id IS NOT NULL
AND conn.sideb_port_id IS NOT NULL
AND usp.code >= '001-01-00001'
AND usp.code <= '079-01-00001'
AND ROWNUM <= 100
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 95 | 38 (0)|
| 1 | COUNT STOPKEY | | | | |
| 2 | NESTED LOOPS | | 1 | 95 | 38 (0)|
| 3 | NESTED LOOPS | | 1 | 80 | 36 (0)|
| 4 | VIEW | | 1 | 12 | 32 (0)|
| 5 | TABLE ACCESS BY INDEX ROWID | SIDE_DEVICE | 1 | 12 | 3 (0)|
| 6 | NESTED LOOPS | | 1 | 70 | 32 (0)|
| 7 | NESTED LOOPS | | 1 | 58 | 29 (0)|
| 8 | NESTED LOOPS | | 1 | 46 | 6 (0)|
| 9 | TABLE ACCESS BY INDEX ROWID| DEVICE | 1 | 34 | 4 (0)|
| 10 | INDEX RANGE SCAN | UIDX_DEV_CODE | 1 | | 3 (0)|
| 11 | TABLE ACCESS BY INDEX ROWID| NODE | 1 | 12 | 2 (0)|
| 12 | INDEX UNIQUE SCAN | UIDX_NODE_DEVICE | 1 | | 1 (0)|
| 13 | TABLE ACCESS BY INDEX ROWID | SIDE | 1 | 12 | 23 (0)|
| 14 | INDEX RANGE SCAN | UIDX_SIDE_NODEA | 24 | | 2 (0)|
| 15 | INDEX RANGE SCAN | IDX_SIDE_DEVICE_SIDE | 1 | | 2 (0)|
| 16 | TABLE ACCESS BY INDEX ROWID | CONNECTOR | 1 | 68 | 4 (0)|
| 17 | INDEX RANGE SCAN | IDX_CONNECTOR_DEVICE_SIDE | 1 | | 3 (0)|
| 18 | TABLE ACCESS BY INDEX ROWID | USER_SIDE_PORT | 1 | 15 | 2 (0)|
| 19 | INDEX UNIQUE SCAN | PK_USER_SIDE_PORT | 1 | | 1 (0)|
------------------------------------------------------------------------------------------------------
跑的性能：
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SELECT /*+no_merge(sa) USE_NL(sa, conn, usp) ORDERED NO_INDEX(usp UIDX_USP_CODE)*/
ROWNUM num, conn.connector_id, conn.code, conn.rsc_status_cd,
conn.rsc_spec_id, conn.line_ability, conn.sidea_port_id,
conn.sidea_port_spec_id, conn.sideb_port_id,
conn.sideb_port_spec_id, sa.device_id, usp.code
FROM (SELECT /*+ORDERED*/
s.side_id, sd.device_id
FROM device dev INNER JOIN node nd
ON nd.device_id = dev.device_id
INNER JOIN side s ON s.node_id_a = nd.node_id
INNER JOIN side_device sd ON sd.side_id = s.side_id
WHERE dev.code = 'MDF-0315'
AND dev.rsc_spec_id IN (10301001, 10301002, 10301003)
AND dev.area_id = 127) sa,
connector conn,
user_side_port usp
WHERE conn.side_id = sa.side_id
AND conn.device_id = sa.device_id
AND conn.sidea_port_id = usp.port_id
AND conn.sidea_port_id IS NOT NULL
AND conn.sideb_port_id IS NOT NULL
AND usp.code >= '001-01-00001'
AND usp.code <= '079-01-00001'
AND ROWNUM <= 100
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.01 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 16 0.00 0.00 0 786 0 200
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 0.00 0.01 0 786 0 200
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 70
Rows Row Source Operation
------- ---------------------------------------------------
100 COUNT STOPKEY (cr=393 pr=0 pw=0 time=221 us)
100 NESTED LOOPS (cr=393 pr=0 pw=0 time=215 us)
100 NESTED LOOPS (cr=83 pr=0 pw=0 time=2863 us)
1 VIEW (cr=16 pr=0 pw=0 time=146 us)
1 TABLE ACCESS BY INDEX ROWID SIDE_DEVICE (cr=16 pr=0 pw=0 time=145 us)
2 NESTED LOOPS (cr=15 pr=0 pw=0 time=131 us)
1 NESTED LOOPS (cr=12 pr=0 pw=0 time=109 us)
1 NESTED LOOPS (cr=8 pr=0 pw=0 time=75 us)
1 TABLE ACCESS BY INDEX ROWID DEVICE (cr=4 pr=0 pw=0 time=41 us)
1 INDEX RANGE SCAN UIDX_DEV_CODE (cr=3 pr=0 pw=0 time=34 us)(object id 92467)
1 TABLE ACCESS BY INDEX ROWID NODE (cr=4 pr=0 pw=0 time=32 us)
1 INDEX UNIQUE SCAN UIDX_NODE_DEVICE (cr=3 pr=0 pw=0 time=23 us)(object id 92474)
1 TABLE ACCESS BY INDEX ROWID SIDE (cr=4 pr=0 pw=0 time=32 us)
1 INDEX RANGE SCAN UIDX_SIDE_NODEA (cr=3 pr=0 pw=0 time=23 us)(object id 92496)
1 INDEX RANGE SCAN IDX_SIDE_DEVICE_SIDE (cr=3 pr=0 pw=0 time=19 us)(object id 92498)
100 TABLE ACCESS BY INDEX ROWID CONNECTOR (cr=67 pr=0 pw=0 time=2715 us)
100 INDEX RANGE SCAN IDX_CONNECTOR_DEVICE_SIDE (cr=11 pr=0 pw=0 time=31 us)(object id 95150)
100 TABLE ACCESS BY INDEX ROWID USER_SIDE_PORT (cr=310 pr=0 pw=0 time=1629 us)
100 INDEX UNIQUE SCAN PK_USER_SIDE_PORT (cr=208 pr=0 pw=0 time=873 us)(object id 66468)
********************************************************************************
SELECT /*+no_merge(sa) ORDERED use_nl(sa, conn, cab, usp) INDEX_JOIN(O)*/ SELECT /*+no_merge(sa) USE_NL(sa, conn, usp) ORDERED NO_INDEX(usp UIDX_USP_CODE)*/
ROWNUM num, ROWNUM num,
conn.connector_id, conn.connector_id,
conn.code, conn.code,
conn.rsc_status_cd, conn.rsc_status_cd,
conn.rsc_spec_id, conn.rsc_spec_id,
conn.line_ability, conn.line_ability,
conn.sidea_port_id, conn.sidea_port_id,
conn.sidea_port_spec_id, conn.sidea_port_spec_id,
conn.sideb_port_id, conn.sideb_port_id,
conn.sideb_port_spec_id, conn.sideb_port_spec_id,
sa.device_id sa.device_id,
FROM (SELECT /*+ORDERED*/ usp.code
s.side_id, sd.device_id FROM (SELECT /*+ORDERED*/
FROM device dev s.side_id, sd.device_id
INNER JOIN node nd ON nd.device_id = dev.device_id FROM device dev
INNER JOIN side s ON s.node_id_a = nd.node_id INNER JOIN node nd ON nd.device_id = dev.device_id
INNER JOIN side_device sd ON sd.side_id = s.side_id INNER JOIN side s ON s.node_id_a = nd.node_id
WHERE dev.code = 'MDF-0315' INNER JOIN side_device sd ON sd.side_id = s.side_id
AND dev.rsc_spec_id IN (10301001, 10301002, 10301003) WHERE dev.code = 'MDF-0315'
AND dev.area_id = 127) sa, AND dev.rsc_spec_id IN (10301001, 10301002, 10301003)
connector conn, AND dev.area_id = 127) sa,
user_side_port usp connector conn,
WHERE conn.side_id = sa.side_id user_side_port usp
AND conn.device_id = sa.device_id WHERE conn.side_id = sa.side_id
AND conn.sidea_port_id = usp.port_id AND conn.device_id = sa.device_id
AND conn.sidea_port_id IS NOT NULL AND conn.sidea_port_id = usp.port_id
AND conn.sideb_port_id IS NOT NULL AND conn.sidea_port_id IS NOT NULL
AND usp.code >= '009-05-00012' AND conn.sideb_port_id IS NOT NULL
AND usp.code <= '009-05-00099' AND usp.code >= '001-01-00001'
AND ROWNUM <= 100; AND usp.code <= '079-01-00001'
AND ROWNUM <= 100
-------------------------------------------------------------------------- -------------------------------------------------------------------------
| Id | Operation | Name | | Id | Operation | Name |
-------------------------------------------------------------------------- -------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 0 | SELECT STATEMENT | |
| 1 | COUNT STOPKEY | | | 1 | COUNT STOPKEY | |
| 2 | TABLE ACCESS BY INDEX ROWID | USER_SIDE_PORT | | 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | | | 3 | NESTED LOOPS | |
| 4 | NESTED LOOPS | | | 4 | VIEW | |
| 5 | VIEW | | | 5 | TABLE ACCESS BY INDEX ROWID | SIDE_DEVICE |
| 6 | TABLE ACCESS BY INDEX ROWID | SIDE_DEVICE | | 6 | NESTED LOOPS | |
| 7 | NESTED LOOPS | | | 7 | NESTED LOOPS | |
| 8 | NESTED LOOPS | | | 8 | NESTED LOOPS | |
| 9 | NESTED LOOPS | | | 9 | TABLE ACCESS BY INDEX ROWID| DEVICE |
| 10 | TABLE ACCESS BY INDEX ROWID| DEVICE | | 10 | INDEX RANGE SCAN | UIDX_DEV_CODE |
| 11 | INDEX RANGE SCAN | UIDX_DEV_CODE | | 11 | TABLE ACCESS BY INDEX ROWID| NODE |
| 12 | TABLE ACCESS BY INDEX ROWID| NODE | | 12 | INDEX UNIQUE SCAN | UIDX_NODE_DEVICE |
| 13 | INDEX UNIQUE SCAN | UIDX_NODE_DEVICE | | 13 | TABLE ACCESS BY INDEX ROWID | SIDE |
| 14 | TABLE ACCESS BY INDEX ROWID | SIDE | | 14 | INDEX RANGE SCAN | UIDX_SIDE_NODEA |
| 15 | INDEX RANGE SCAN | UIDX_SIDE_NODEA | | 15 | INDEX RANGE SCAN | IDX_SIDE_DEVICE_SIDE |
| 16 | INDEX RANGE SCAN | IDX_SIDE_DEVICE_SIDE | | 16 | TABLE ACCESS BY INDEX ROWID | CONNECTOR |
| 17 | TABLE ACCESS BY INDEX ROWID | CONNECTOR | | 17 | INDEX RANGE SCAN | IDX_CONNECTOR_DEVICE_SIDE |
| 18 | INDEX RANGE SCAN | IDX_CONNECTOR_DEVICE_SIDE | | 18 | TABLE ACCESS BY INDEX ROWID | USER_SIDE_PORT |
| 19 | INDEX RANGE SCAN | UIDX_USP_CODE | | 19 | INDEX UNIQUE SCAN | PK_USER_SIDE_PORT |
-------------------------------------------------------------------------- -------------------------------------------------------------------------
主要引起的原因是，统计信息发生了变化，导致SQL的执行计划走错了索引!
能够查出这个原因，还是从原来的文章中找到了原因!
