一次分析的全过程,和大家交流! 我们的程序员抱怨一段执行很慢的程序，我把代码执行分析执行计划，后果如下： 这是最初的执行效果及执行计划 SQL> SELECT "SP_TRANS"."TRANS_NO", 2 "SP_TRANS_SUB"."ITEM_CODE", 3 "SP_ITEM"."ITEM_NAME", 4 "SP_ITEM"."CHART_ID", 5 "SP_ITEM"."SPECIFICATION", 6 "SP_TRANS_SUB"."COUNTRY", 7 "SP_TRANS_SUB"."QTY", 8 "SP_TRANS_SUB"."PRICE", 9 "SP_TRANS"."VENDOR_CODE", 10 "SP_TRANS"."PAY_MODE", 11 NVL("SP_TRANS_SUB"."PAY_QTY",0), 12 0 as PAY_THIS 13 FROM "SP_ITEM", 14 "SP_TRANS_SUB", 15 "SP_TRANS" 16 WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and 17 ( "SP_ITEM"."ITEM_CODE" = "SP_TRANS_SUB"."ITEM_CODE" ) and 18 ( ( "SP_TRANS"."VENDOR_CODE" = '20011021023') ) 19 / 8 rows selected. Elapsed: 00: 00: 00.51 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 NESTED LOOPS 3 2 TABLE ACCESS (FULL) OF 'SP_TRANS' 4 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB' 5 4 INDEX (RANGE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE) 6 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' 7 6 INDEX (UNIQUE SCAN) OF 'PK_SP_ITEM' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 4 db block gets 323 consistent gets 0 physical reads 0 redo size 1809 bytes sent via SQL*Net to client 425 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 8 rows processed[@more@]