从 FROM V$SQL_PLAN_STATISTICS_ALL P 获得 01wa3qb3vd2qm 的执行计划 932283339
Global Stats
=========================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
=========================================================================================
| 895 | 150 | 744 | 0.00 | 2.13 | 1 | 22M | 217K | 4GB |
=========================================================================================
SQL Plan Monitoring Details (Plan Hash Value=932283339)
=================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
=================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 43 | +848 | 1 | 7 | | | | |
| 1 | VIEW | | 3 | 3408 | 43 | +848 | 1 | 7 | | | | |
| 2 | COUNT STOPKEY | | | | 43 | +848 | 1 | 7 | | | | |
| 3 | CONCATENATION | | | | 43 | +848 | 1 | 5 | | | | |
| 4 | FILTER | | | | 7 | +848 | 1 | 5 | | | | |
| 5 | FILTER | | | | 7 | +848 | 1 | 5 | | | | |
| 6 | NESTED LOOPS OUTER | | 1 | 945 | 7 | +848 | 1 | 5 | | | | |
| 7 | NESTED LOOPS OUTER | | 1 | 942 | 7 | +848 | 1 | 5 | | | | |
| 8 | NESTED LOOPS OUTER | | 1 | 939 | 7 | +848 | 1 | 5 | | | | |
| 9 | NESTED LOOPS OUTER | | 1 | 936 | 7 | +848 | 1 | 5 | | | | |
| 10 | NESTED LOOPS | | 1 | 935 | 885 | +6 | 1 | 5 | | | | |
| 11 | NESTED LOOPS OUTER | | 1 | 932 | 885 | +6 | 1 | 4M | | | 0.34 | Cpu (3) |
| 12 | PARTITION RANGE ALL | | 1 | 931 | 885 | +6 | 1 | 4M | | | | |
| 13 | TABLE ACCESS FULL | ACC_SITE_REC | 1 | 931 | 889 | +2 | 18 | 4M | 54970 | 3GB | 23.34 | gc cr multi block request (1) |
| | | | | | | | | | | | | Cpu (53) |
| | | | | | | | | | | | | db file scattered read (88) |
| | | | | | | | | | | | | db file sequential read (65) |
| 14 | TABLE ACCESS BY INDEX ROWID | CMS_ORG_DETAIL | 1 | 1 | 885 | +6 | 4M | 4M | | | 1.69 | Cpu (15) |
| 15 | INDEX UNIQUE SCAN | CMS_ORG_DETAIL_PK | 1 | | 885 | +6 | 4M | 4M | 2 | 16384 | 1.35 | Cpu (12) |
| 16 | TABLE ACCESS BY GLOBAL INDEX ROWID | ACC_SITE_SEND | 1 | 3 | 885 | +6 | 4M | 5 | 925 | 7MB | 1.80 | Cpu (11) |
| | | | | | | | | | | | | db file sequential read (5) |
| 17 | INDEX UNIQUE SCAN | ACC_SITE_SEND_PK | 1 | 2 | 890 | +1 | 4M | 4M | 161K | 1GB | 71.48 | gc cr grant 2-way (2) |
| | | | | | | | | | | | | Cpu (60) |
| | | | | | | | | | | | | db file sequential read (572) |
| 18 | TABLE ACCESS BY INDEX ROWID | CMS_ORG_DETAIL | 1 | 1 | 7 | +848 | 5 | 5 | | | | |
| 19 | INDEX UNIQUE SCAN | CMS_ORG_DETAIL_PK | 1 | | 7 | +848 | 5 | 5 | | | | |
| 20 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_SIGN | 1 | 3 | 1 | +848 | 5 | 1 | | | | |
| 21 | INDEX UNIQUE SCAN | OPT_SIGN_PK | 1 | 2 | 1 | +848 | 5 | 1 | 5 | 40960 | | |
| 22 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_BILL | 1 | 3 | 7 | +848 | 5 | 5 | 1 | 8192 | | |
| 23 | INDEX UNIQUE SCAN | OPT_BILL_PK | 1 | 2 | 7 | +848 | 5 | 5 | | | | |
| 24 | TABLE ACCESS BY GLOBAL INDEX ROWID | ACC_BILL | 1 | 3 | 7 | +848 | 5 | 5 | | | | |
| 25 | INDEX UNIQUE SCAN | ACC_BILL_PK | 1 | 2 | 7 | +848 | 5 | 5 | | | | |
| 26 | FILTER | | | | 1 | +890 | 1 | 2 | | | | |
| 27 | FILTER | | | | 1 | +890 | 1 | 2 | | | | |
| 28 | NESTED LOOPS OUTER | | 2 | 2463 | 1 | +890 | 1 | 2 | | | | |
| 29 | NESTED LOOPS OUTER | | 2 | 2457 | 1 | +890 | 1 | 2 | | | | |
| 30 | NESTED LOOPS OUTER | | 2 | 2451 | 1 | +890 | 1 | 2 | | | | |
| 31 | NESTED LOOPS OUTER | | 2 | 2445 | 1 | +890 | 1 | 2 | | | | |
| 32 | NESTED LOOPS OUTER | | 2 | 2444 | 1 | +890 | 1 | 2 | | | | |
| 33 | NESTED LOOPS | | 2 | 2442 | 1 | +890 | 1 | 2 | | | | |
| 34 | PARTITION RANGE ITERATOR | | 6 | 2424 | 1 | +890 | 1 | 3 | | | | |
| 35 | TABLE ACCESS BY LOCAL INDEX ROWID | ACC_SITE_SEND | 6 | 2424 | 1 | +890 | 1 | 3 | 15 | 120KB | | |
| 36 | INDEX RANGE SCAN | ACC_SITE_SEND_IDX1 | 223 | 2212 | 1 | +890 | 1 | 12736 | 1 | 8192 | | |
| 37 | TABLE ACCESS BY GLOBAL INDEX ROWID | ACC_SITE_REC | 1 | 3 | 1 | +890 | 3 | 2 | | | | |
| 38 | INDEX UNIQUE SCAN | ACC_SITE_REC_PK | 1 | 2 | 1 | +890 | 3 | 3 | 1 | 8192 | | |
| 39 | TABLE ACCESS BY INDEX ROWID | CMS_ORG_DETAIL | 1 | 1 | 1 | +890 | 2 | 2 | | | | |
| 40 | INDEX UNIQUE SCAN | CMS_ORG_DETAIL_PK | 1 | | 1 | +890 | 2 | 2 | | | | |
| 41 | TABLE ACCESS BY INDEX ROWID | CMS_ORG_DETAIL | 1 | 1 | 1 | +890 | 2 | 2 | | | | |
| 42 | INDEX UNIQUE SCAN | CMS_ORG_DETAIL_PK | 1 | | 1 | +890 | 2 | 2 | | | | |
| 43 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_SIGN | 1 | 3 | | | 2 | | | | | |
| 44 | INDEX UNIQUE SCAN | OPT_SIGN_PK | 1 | 2 | | | 2 | | | | | |
| 45 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_BILL | 1 | 3 | 1 | +890 | 2 | 2 | 1 | 8192 | | |
| 46 | INDEX UNIQUE SCAN | OPT_BILL_PK | 1 | 2 | 1 | +890 | 2 | 2 | 1 | 8192 | | |
| 47 | TABLE ACCESS BY GLOBAL INDEX ROWID | ACC_BILL | 1 | 3 | 1 | +890 | 2 | 2 | | | | |
| 48 | INDEX UNIQUE SCAN | ACC_BILL_PK | 1 | 2 | 1 | +890 | 2 | 2 | | | | |
=================================================================================================================================================================================================
然后把该语句在PLSQL DELEVOPER 下 和 SQLPLUS 下得到是与上面不一样的执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 3643155137
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2237 | 18 (0)| 00:00:01 | | |
|* 1 | VIEW | | 1 | 2237 | 18 (0)| 00:00:01 | | |
|* 2 | COUNT STOPKEY | | | | | | | |
|* 3 | FILTER | | | | | | | |
| 4 | NESTED LOOPS OUTER | | 1 | 591 | 18 (0)| 00:00:01 | | |
| 5 | NESTED LOOPS OUTER | | 1 | 579 | 17 (0)| 00:00:01 | | |
| 6 | NESTED LOOPS OUTER | | 1 | 567 | 16 (0)| 00:00:01 | | |
| 7 | NESTED LOOPS | | 1 | 326 | 13 (0)| 00:00:01 | | |
| 8 | NESTED LOOPS OUTER | | 1 | 214 | 10 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS OUTER | | 1 | 185 | 7 (0)| 00:00:01 | | |
| 10 | PARTITION RANGE ITERATOR | | 1 | 154 | 4 (0)| 00:00:01 | KEY | KEY |
|* 11 | TABLE ACCESS BY LOCAL INDEX ROWID| ACC_SITE_SEND | 1 | 154 | 4 (0)| 00:00:01 | KEY | KEY |
|* 12 | INDEX RANGE SCAN | ACC_SITE_SEND_IDX1 | 1 | | 3 (0)| 00:00:01 | KEY | KEY |
| 13 | TABLE ACCESS BY GLOBAL INDEX ROWID| OPT_SIGN | 1 | 31 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 14 | INDEX UNIQUE SCAN | OPT_SIGN_PK | 1 | | 2 (0)| 00:00:01 | | |
| 15 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_BILL | 1 | 29 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 16 | INDEX UNIQUE SCAN | OPT_BILL_PK | 1 | | 2 (0)| 00:00:01 | | |
| 17 | TABLE ACCESS BY GLOBAL INDEX ROWID | ACC_SITE_REC | 1 | 112 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 18 | INDEX UNIQUE SCAN | ACC_SITE_REC_PK | 1 | | 2 (0)| 00:00:01 | | |
| 19 | TABLE ACCESS BY GLOBAL INDEX ROWID | ACC_BILL | 1 | 241 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 20 | INDEX UNIQUE SCAN | ACC_BILL_PK | 1 | | 2 (0)| 00:00:01 | | |
| 21 | TABLE ACCESS BY INDEX ROWID | CMS_ORG_DETAIL | 1 | 12 | 1 (0)| 00:00:01 | | |
|* 22 | INDEX UNIQUE SCAN | CMS_ORG_DETAIL_PK | 1 | | 0 (0)| 00:00:01 | | |
| 23 | TABLE ACCESS BY INDEX ROWID | CMS_ORG_DETAIL | 1 | 12 | 1 (0)| 00:00:01 | | |
|* 24 | INDEX UNIQUE SCAN | CMS_ORG_DETAIL_PK | 1 | | 0 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------