最近用户经常反馈和任务单相关的查询界面非常慢。并且都是很精确的查询。
正常来说,选择性好的查询(直接输入任务单的唯一编号了)应该速度很快的。
但是,为何这个查询慢(超过30秒)?不合理的现象,而且还是标准功能:
跟踪了这个查询对应的SQL,发现是这样子的:
SELECT wip_entity_name, wip_entity_id
FROM wip_entities
WHERE (UPPER(WIP_ENTITY_NAME) LIKE :1
AND (WIP_ENTITY_NAME LIKE :2
OR WIP_ENTITY_NAME LIKE :3
OR WIP_ENTITY_NAME LIKE :4
OR WIP_ENTITY_NAME LIKE :5))
AND (organization_id = :6)
ORDER BY wip_entity_name
然后看绑定变量,是这样子的:
Binds*
=================================================
| Name | Position | Type | Value |
=================================================
| :1 | 1 | VARCHAR2(32) | SL170221390% |
| :2 | 2 | VARCHAR2(32) | sl% |
| :3 | 3 | VARCHAR2(32) | sL% |
| :4 | 4 | VARCHAR2(32) | Sl% |
| :5 | 5 | VARCHAR2(32) | SL% |
| :6 | *6 | NUMBER | 189 |
=================================================
再看执行计划:
从SQL以及对应绑定变量,还有执行计划,可以看出:
这个准确的查询,被Oracle自动添加了一个Upper函数,从而无法用到选择性最好的关键字过滤,导致查询缓慢。而且会随着数据量的增加,这个查询会越来越慢的。
真的很奇怪,查询的时候,明明我输入了SL170221390,在LOV过滤的时候应该直接用:
WIP_ENTITY_NAME LIKE :1
就是:WIP_ENTITY_NAME LIKE ‘SL170221390%’就可以查询到结果了啊,这样子也是最有效率的。为什么偏偏要加一个UPPER函数呢?
经过不断的测试,加上本人对EBS系统的处理逻辑的理解,我确认了应该是这个原因导致:
原来是Oracle的LOV长值列表的自动处理逻辑导致:长值列表 的关键字查询 不区分大小写!
也就是说,LOV:Let the user filter records before dislaying them(Fliter Before Display)设置为Yes的查询,在做Fliter的时候,关键字自动做了不区分大小写的效果!
正是因为Oracle要达到这个效果,所以才自动套一个转大写的函数来做查询。
举个简单的例子:
在Lov里面,查询WORK_REQUEST_FNAME段:erp%
如果不区分大小写,则LOV查询的时候,自动转换为:
AND (UPPER(WORK_REQUEST_FNAME) LIKE ‘ERP%’
AND (WORK_REQUEST_FNAME LIKE ‘er%’
OR WORK_REQUEST_FNAME LIKE ‘eR%’
OR WORK_REQUEST_FNAME LIKE ‘Er%’
OR WORK_REQUEST_FNAME LIKE ‘ER%’))
这样子,所有包括ERP%关键字(不区分大小写)的数据都会搜索出来。
对不区分大小写的查询的处理探索:
--根据结果,猜测其处理的规律:
首先系统看查询的关键字是否有包括字母。
如果完全没包括字母,就是查询的关键字是纯数字,则默认是最有效率的查询:EMPLOYEE_NUMBER LIKE :1
只要是包括字母,则默认当前字段是自动添加函数:UPPER(EMPLOYEE_NUMBER) LIKE :1 (注意这里的:1是自动大写了,例如2144wsB92 会自动变为:2144WSB92%)
然后,自动添加前2个字符的各种不同的组合来做查询!
例如:
wsb214492:4种可能性 ws WS Ws wS
w214492:2种可能性 W2 w2
214492www:1种可能性 21
不得不说,这个效果还是很不错的。因为很多时候用户查询的时候确实可能大小写混用,导致查询没结果。
不过这个处理逻辑的副作用就是用不到该段的索引!最郁闷的是,目前没发现哪里可以设定!(如果哪个兄台知道这个的设定的麻烦留言一下,十分感谢!)
所以,针对这个问题,目前的解决办法只好是:在长值列表的主要搜索字段添加一个UPPER的函数索引。这样子就算LOV自动帮我们加一个UPPER函数,也可以用到对应段的索引了。
CREATE UNIQUE INDEX WIP.WIP_ENTITIES_U3 ON WIP.WIP_ENTITIES
(UPPER(WIP_ENTITY_NAME), ORGANIZATION_ID)
NOLOGGING
TABLESPACE APPS_TS_TX_IDX;
添加之后的查询执行计划是:
SQL_ID dxcuk5cb89qk5, child number 0
-------------------------------------
select wip_entity_name, wip_entity_id from wip_entities where (
UPPER(WIP_ENTITY_NAME) LIKE :1 AND (WIP_ENTITY_NAME LIKE :2 OR
WIP_ENTITY_NAME LIKE :3 OR WIP_ENTITY_NAME LIKE :4 OR WIP_ENTITY_NAME
LIKE :5)) AND ( organization_id = :6 ) order by wip_entity_name
Plan hash value: 269543263
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 13 (100)| |
| 1 | SORT ORDER BY | | 1 | 145 | 13 (8)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| WIP_ENTITIES | 1 | 145 | 12 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | WIP_ENTITIES_U3 | 20 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
至此,问题得到解决!
其实我觉得最好的解决办法应该是有一个这样子的设定:LOV过滤不区分大小写。默认为Yes,如果设置为No,则就是区分大小写的查询处理,没必要添加什么UPPER函数!
备注:
Form表单的栏位有一个属性:Case Insensitive Query,默认为No(Flase),其实就是这个意思。如果设置为Yes,则您按F11做栏位的查询的时候,会自动添加不区分大小写的查询逻辑。有兴趣的兄台可以试试!