执行计划如下:
SQL_ID 1fjad7hzrw2r2, child number 1
-------------------------------------
SELECT DISTINCT ia_bill.cbillid, ia_bill.vbillcode, ia_bill.pk_corp FROM ia_bill, ia_bill_b
WHERE ia_bill.cbillid = ia_bill_b.cbillid and ia_bill_b.csourcebillid = :1 and rtrim(
ia_bill_b.csourcebilltypecode,' ') = :2 and ia_bill.cbilltypecode = :3 and ia_bill.dr = 0
and ia_bill_b.dr = 0
Plan hash value: 2004987261
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | HASH UNIQUE | | 1 | 108 | 5 (20)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID | IA_BILL_B | 1 | 48 | 2 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 108 | 4 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID| IA_BILL | 1 | 60 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_IA_BILL_TYPE | 1 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I_IA_BILL_B_BILLID | 6 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (VARCHAR2(30), CSID=852): '1006A2100000000PW4PR'
2 - :2 (VARCHAR2(30), CSID=852): '4O'
3 - :3 (VARCHAR2(30), CSID=852): 'I8'
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("IA_BILL_B"."CSOURCEBILLID"=:1 AND RTRIM("IA_BILL_B"."CSOURCEBILLTYPECODE",'
')=:2 AND "IA_BILL_B"."DR"=0))
4 - filter("IA_BILL"."DR"=0)
5 - access("IA_BILL"."CBILLTYPECODE"=:3)
6 - access("IA_BILL"."CBILLID"="IA_BILL_B"."CBILLID")
1. Bind peeking
因为这里oracle选择了CBILLTYPECODE字段上的索引,而这个字段的索引选择性很差,只是
因为第一次生成执行的输入的cbilltypecode='I8',由于bind peeking的机制,oracle在第一次评估的时候就是根据I8的值来生成执行计划,cbilltypecode='I8'的值只有28行,所以第一次
执行的时候选择的执行计划对于这个值来说是正确的,而之后在执行这个sql的时候由于重用了这个执行计划,当cbilltypecode为不同的值时返回的行数很多,所以第一次生成的执行计划对后面的值不是最优的.
2. 一个节点快一个节点慢的原因 :
Rac环境下不同的节点不会重用别的节点的执行计划,快的节点选择的是CSOURCEBILLID(选择性很高的字段) 上的索引,然后驱动ia_bill做nest loop
3. 解决方法:
禁用bind_peeking (_optim_peek_user_binds=false):修改隐含参数不建议使用
B.删除cbilltypecode字段上的直方图信息
dbms_stats.set_column_stats(user,'IA_BILL','CBILLTYPECODE',distcnt=>12);
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8984272/viewspace-619904/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8984272/viewspace-619904/