同一条SQL语句在单机下执行速只要1秒，而在RAC环境下要3-4秒，做了跟踪发现在SQL硬解析CPU要花3-4秒
RAC下己做了应用隔离
SQL语句：select a.*, b.goodsname,
b.goodstype,
b.goodsno from mpcs_nm_mrp_exec_need a,mpcs_mep_pln_innord_v b
where a.innordid = b.innordid(+) and a.mrpexecid=6585;
已用时间: 00: 00: 04.10执行计划
----------------------------------------------------------
Plan hash value: 3689069394----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74 | 49506 | 64 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 74 | 49506 | 64 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | MPCS_NM_MRP_EXEC_NEED | 12 | 1668 | 4 (0)| 00:0
|* 3 | INDEX RANGE SCAN | MPCS_NM_MRP_EXEC_NEED_IDX1 | 12 | | 1 (0)| 00:00:01 |
| 4 | VIEW PUSHED PREDICATE | MPCS_MEP_PLN_INNORD_V | 1 | 530 | 5 (0)| 00:00:01
| 5 | NESTED LOOPS OUTER | | 6 | 1398 | 5 (0)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 216 | 3 (0)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 1 | 204 | 3 (0)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 1 | 132 | 2 (0)| 00:00:01 |
| 9 | NESTED LOOPS OUTER | | 1 | 127 | 2 (0)| 00:00:01 |
| 10 | NESTED LOOPS OUTER | | 1 | 122 | 2 (0)| 00:00:01 |
| 11 | NESTED LOOPS OUTER | | 1 | 117 | 2 (0)| 00:00:01 |
| 12 | NESTED LOOPS OUTER | | 1 | 113 | 2 (0)| 00:00:01 |
| 13 | NESTED LOOPS OUTER | | 1 | 100 | 2 (0)| 00:00:01 |
| 14 | NESTED LOOPS OUTER | | 1 | 95 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID| MPCS_MEP_PLN_INNORD | 1 | 82 | 2 (0)| 00:0
|* 16 | INDEX UNIQUE SCAN | MPCS_MEP_PLN_INNORD_PK | 1 | | 1 (0)| 00:00:01
|* 17 | INDEX UNIQUE SCAN | MPCS_MB_PRODLINE_DEF_PK | 1 | 13 | 0 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PUB_COMPANY_PK | 3201 | 16005 | 0 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | MPCS_MB_PROD_TRACE_DOC_PK | 1 | 13 | 0 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PUB_CUSTOMER_PK | 311 | 1244 | 0 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | PUB_GOODSDETAIL_PK | 105K| 513K| 0 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | EMPLOYEEID_PK | 3547 | 17735 | 0 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | EMPLOYEEID_PK | 3547 | 17735 | 0 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | PUB_GOODS | 103K| 7265K| 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | PUB_GOODS_PK | 1 | | 0 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | MPCS_CHK_DOC_STATUS_IDX1 | 1 | 12 | 0 (0)| 00:00:01 |
| 27 | VIEW PUSHED PREDICATE | | 1 | 17 | 2 (0)| 00:00:01 |
| 28 | NESTED LOOPS OUTER | | 7 | 168 | 2 (0)| 00:00:01 |
| 29 | NESTED LOOPS | | 7 | 133 | 2 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | BMS_BOM_PK | 1 | 10 | 1 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | BMS_PR_BOM_DTL_IDX2 | 7 | 63 | 1 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | PUB_GOODS_PK | 1 | 5 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 3 - access("A"."MRPEXECID"=6585)
16 - access("A"."INNORDID"="A"."INNORDID")
17 - access("A"."PRODLINEID"="I"."PRODLINEID"(+))
18 - access("A"."DEPTID"="H"."COMPANYID"(+))
19 - access("A"."TRACEID"="G"."TRACEID"(+))
20 - access("A"."CUSTOMID"="F"."CUSTOMID"(+))
21 - access("A"."GOODSDTLID"="E"."GOODSDTLID"(+))
22 - access("A"."INPUTMANID"="D"."EMPLOYEEID"(+))
23 - access("A"."APPRMANID"="C"."EMPLOYEEID"(+))
25 - access("A"."GOODSID"="B"."GOODSID"(+))
26 - access("CHK"."SOURCEID"(+)="A"."INNORDID" AND "CHK"."SOURCETABLE"(+)=24 AND "CHK"."CHKTYPEID"
filter("CHK"."SOURCEID"(+)="A"."INNORDID")
30 - access("J"."BOMID"="A"."BOMID")
31 - access("K"."BOMID"="A"."BOMID")
32 - access("K"."MGOODSID"="L"."GOODSID"(+))
统计信息
----------------------------------------------------------
298 recursive calls
0 db block gets
40 consistent gets
0 physical reads
0 redo size
1041 bytes sent via SQL*Net to client
230 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed单机的执行计划和路径跟RAC下的一模一样
RAC下己做了应用隔离
SQL语句：select a.*, b.goodsname,
b.goodstype,
b.goodsno from mpcs_nm_mrp_exec_need a,mpcs_mep_pln_innord_v b
where a.innordid = b.innordid(+) and a.mrpexecid=6585;
已用时间: 00: 00: 04.10执行计划
----------------------------------------------------------
Plan hash value: 3689069394----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74 | 49506 | 64 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 74 | 49506 | 64 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | MPCS_NM_MRP_EXEC_NEED | 12 | 1668 | 4 (0)| 00:0
|* 3 | INDEX RANGE SCAN | MPCS_NM_MRP_EXEC_NEED_IDX1 | 12 | | 1 (0)| 00:00:01 |
| 4 | VIEW PUSHED PREDICATE | MPCS_MEP_PLN_INNORD_V | 1 | 530 | 5 (0)| 00:00:01
| 5 | NESTED LOOPS OUTER | | 6 | 1398 | 5 (0)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 216 | 3 (0)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 1 | 204 | 3 (0)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 1 | 132 | 2 (0)| 00:00:01 |
| 9 | NESTED LOOPS OUTER | | 1 | 127 | 2 (0)| 00:00:01 |
| 10 | NESTED LOOPS OUTER | | 1 | 122 | 2 (0)| 00:00:01 |
| 11 | NESTED LOOPS OUTER | | 1 | 117 | 2 (0)| 00:00:01 |
| 12 | NESTED LOOPS OUTER | | 1 | 113 | 2 (0)| 00:00:01 |
| 13 | NESTED LOOPS OUTER | | 1 | 100 | 2 (0)| 00:00:01 |
| 14 | NESTED LOOPS OUTER | | 1 | 95 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID| MPCS_MEP_PLN_INNORD | 1 | 82 | 2 (0)| 00:0
|* 16 | INDEX UNIQUE SCAN | MPCS_MEP_PLN_INNORD_PK | 1 | | 1 (0)| 00:00:01
|* 17 | INDEX UNIQUE SCAN | MPCS_MB_PRODLINE_DEF_PK | 1 | 13 | 0 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PUB_COMPANY_PK | 3201 | 16005 | 0 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | MPCS_MB_PROD_TRACE_DOC_PK | 1 | 13 | 0 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PUB_CUSTOMER_PK | 311 | 1244 | 0 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | PUB_GOODSDETAIL_PK | 105K| 513K| 0 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | EMPLOYEEID_PK | 3547 | 17735 | 0 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | EMPLOYEEID_PK | 3547 | 17735 | 0 (0)| 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | PUB_GOODS | 103K| 7265K| 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | PUB_GOODS_PK | 1 | | 0 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | MPCS_CHK_DOC_STATUS_IDX1 | 1 | 12 | 0 (0)| 00:00:01 |
| 27 | VIEW PUSHED PREDICATE | | 1 | 17 | 2 (0)| 00:00:01 |
| 28 | NESTED LOOPS OUTER | | 7 | 168 | 2 (0)| 00:00:01 |
| 29 | NESTED LOOPS | | 7 | 133 | 2 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | BMS_BOM_PK | 1 | 10 | 1 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | BMS_PR_BOM_DTL_IDX2 | 7 | 63 | 1 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | PUB_GOODS_PK | 1 | 5 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 3 - access("A"."MRPEXECID"=6585)
16 - access("A"."INNORDID"="A"."INNORDID")
17 - access("A"."PRODLINEID"="I"."PRODLINEID"(+))
18 - access("A"."DEPTID"="H"."COMPANYID"(+))
19 - access("A"."TRACEID"="G"."TRACEID"(+))
20 - access("A"."CUSTOMID"="F"."CUSTOMID"(+))
21 - access("A"."GOODSDTLID"="E"."GOODSDTLID"(+))
22 - access("A"."INPUTMANID"="D"."EMPLOYEEID"(+))
23 - access("A"."APPRMANID"="C"."EMPLOYEEID"(+))
25 - access("A"."GOODSID"="B"."GOODSID"(+))
26 - access("CHK"."SOURCEID"(+)="A"."INNORDID" AND "CHK"."SOURCETABLE"(+)=24 AND "CHK"."CHKTYPEID"
filter("CHK"."SOURCEID"(+)="A"."INNORDID")
30 - access("J"."BOMID"="A"."BOMID")
31 - access("K"."BOMID"="A"."BOMID")
32 - access("K"."MGOODSID"="L"."GOODSID"(+))
统计信息
----------------------------------------------------------
298 recursive calls
0 db block gets
40 consistent gets
0 physical reads
0 redo size
1041 bytes sent via SQL*Net to client
230 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed单机的执行计划和路径跟RAC下的一模一样
b.goodstype,
b.goodsno from mpcs_nm_mrp_exec_need a,mpcs_mep_pln_innord_v b
where a.innordid = b.innordid(+) and a.mrpexecid=6588
已用时间: 00: 00: 02.37执行计划
----------------------------------------------------------
Plan hash value: 2377382721-------------------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN OUTER | |
| 2 | SORT JOIN | |
| 3 | TABLE ACCESS BY INDEX ROWID | MPCS_NM_MRP_EXEC_NEED |
|* 4 | INDEX RANGE SCAN | MPCS_NM_MRP_EXEC_NEED_IDX1 |
|* 5 | SORT JOIN | |
| 6 | VIEW | MPCS_MEP_PLN_INNORD_V |
| 7 | MERGE JOIN OUTER | |
| 8 | SORT JOIN | |
| 9 | NESTED LOOPS OUTER | |
| 10 | NESTED LOOPS OUTER | |
| 11 | NESTED LOOPS OUTER | |
| 12 | NESTED LOOPS OUTER | |
| 13 | NESTED LOOPS OUTER | |
| 14 | NESTED LOOPS OUTER | |
| 15 | NESTED LOOPS OUTER | |
| 16 | NESTED LOOPS OUTER | |
| 17 | NESTED LOOPS OUTER | |
| 18 | TABLE ACCESS FULL | MPCS_MEP_PLN_INNORD |
|* 19 | INDEX UNIQUE SCAN | MPCS_MB_PRODLINE_DEF_PK |
|* 20 | INDEX UNIQUE SCAN | PUB_COMPANY_PK |
|* 21 | INDEX UNIQUE SCAN | MPCS_MB_PROD_TRACE_DOC_PK |
|* 22 | INDEX UNIQUE SCAN | PUB_CUSTOMER_PK |
|* 23 | INDEX UNIQUE SCAN | PUB_GOODSDETAIL_PK |
|* 24 | INDEX UNIQUE SCAN | EMPLOYEEID_PK |
|* 25 | INDEX UNIQUE SCAN | EMPLOYEEID_PK |
| 26 | TABLE ACCESS BY INDEX ROWID| PUB_GOODS |
|* 27 | INDEX UNIQUE SCAN | PUB_GOODS_PK |
|* 28 | INDEX UNIQUE SCAN | MPCS_CHK_DOC_STATUS_IDX1 |
|* 29 | SORT JOIN | |
| 30 | VIEW | |
| 31 | NESTED LOOPS | |
| 32 | NESTED LOOPS OUTER | |
| 33 | TABLE ACCESS FULL | BMS_PR_BOM_DTL |
|* 34 | INDEX UNIQUE SCAN | PUB_GOODS_PK |
|* 35 | INDEX UNIQUE SCAN | BMS_BOM_PK |
-------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 4 - access("A"."MRPEXECID"=6588)
5 - access("A"."INNORDID"="B"."INNORDID"(+))
filter("A"."INNORDID"="B"."INNORDID"(+))
19 - access("A"."PRODLINEID"="I"."PRODLINEID"(+))
20 - access("A"."DEPTID"="H"."COMPANYID"(+))
21 - access("A"."TRACEID"="G"."TRACEID"(+))
22 - access("A"."CUSTOMID"="F"."CUSTOMID"(+))
23 - access("A"."GOODSDTLID"="E"."GOODSDTLID"(+))
24 - access("A"."INPUTMANID"="D"."EMPLOYEEID"(+))
25 - access("A"."APPRMANID"="C"."EMPLOYEEID"(+))
27 - access("A"."GOODSID"="B"."GOODSID"(+))
28 - access("CHK"."SOURCEID"(+)="A"."INNORDID" AND
"CHK"."SOURCETABLE"(+)=24 AND "CHK"."CHKTYPEID"(+)=427)
29 - access("A"."BOMID"="M"."BOMID"(+))
filter("A"."BOMID"="M"."BOMID"(+))
34 - access("K"."MGOODSID"="L"."GOODSID"(+))
35 - access("J"."BOMID"="K"."BOMID")Note
-----
- rule based optimizer used (consider using cbo)
统计信息
----------------------------------------------------------
8 recursive calls
0 db block gets
72237 consistent gets
1 physical reads
0 redo size
1692 bytes sent via SQL*Net to client
237 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
4 rows processed
速度变快了一倍。但在单机环境下同样用Rule规则，只要用0.03秒
上述发现 recursive calls 高 导致CPU硬解析耗时很大，麻烦指点一下
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 7.94 7.78 0 0 0 0
Execute 5 0.03 0.04 0 4 41 33
Fetch 7 0.05 0.02 1 48 0 60
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 17 8.02 7.85 1 52 41 93Misses in library cache during parse: 2Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 17 0.00 0.00
SQL*Net message from client 16 17.06 17.34
library cache lock 27 0.00 0.02
gc cr grant 2-way 1 0.00 0.00
db file sequential read 1 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 69 0.00 0.01 0 0 0 0
Execute 102 0.06 0.09 0 9 76 34
Fetch 100 0.13 0.13 2 241 35 108
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 271 0.19 0.23 2 250 111 142Misses in library cache during parse: 1