一次分析的全過程,和大家交流

一次分析的全過程,和大家交流! 我們的程式設計師抱怨一段執行很慢的程式，我把程式碼執行分析執行計劃，後果如下： 這是最初的執行效果及執行計劃 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@]