索引既可以隐式调用，也可以显式调用。索引的优化对数据库的性能至关重要。在Oracle中开发一个对帐函数时，碰到了奇怪的现象，同样的语句放在函数中，与放在主程序中执行，性能差距数百倍，后来用性能工具分析才发现原来是某条select语句的索引引用不同，在函数中强制指定索引也无法生效。但同样的语句，区别只在于一个放在了函数中，Oracle在解释时为什么会厚此薄彼？似乎牵涉到一些深层的东西，暂时无法深究下去。
这条select语句比较普通，目标表是一张partion by list的分区表：
SELECT TRIM(t.host_sys_id),TRIM(t.tran_code)
INTO v_f_mbcode,v_netacct_data.v_srv_tancode
FROM tranrechis t
WHERE t.srv_trandate = v_srvdate
AND t.srv_seq = v_srvseq;
在plsql developer的查询分析器中执行非常快。
SELECT TRIM(t.host_sys_id), TRIM(t.tran_code)
-- INTO v_mbcode, nars.v_srv_tancode
FROM t_channel_tranrechis t
WHERE t.srv_trandate = '20120812'
AND t.srv_seq = '00525488'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 4 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY GLOBAL INDEX ROWID T_CHANNEL_TRANRECHIS PARTITION: ROW LOCATION ROW LOCATION (cr=4 pr=0 pw=0 time=120 us cost=3 size=25 card=1)
1 1 1 INDEX UNIQUE SCAN T_CHANNEL_TRANRECHIS_IDX1 (cr=3 pr=0 pw=0 time=81 us cost=2 size=0 card=1)(object id 93786)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
********************************************************************************
将同样的语句放入一个函数中，执行此函数，仍然非常快（对151条数据循环）。
SELECT TRIM(T.HOST_SYS_ID), TRIM(T.TRAN_CODE)
FROM
T_CHANNEL_TRANRECHIS T WHERE T.SRV_TRANDATE = :B2 AND T.SRV_SEQ = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 151 0.01 0.02 0 0 0 0
Fetch 151 0.00 0.00 0 604 0 151
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 304 0.02 0.02 0 604 0 151
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84 (recursive depth: 1)
Number of plan statistics captured: 2
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY GLOBAL INDEX ROWID T_CHANNEL_TRANRECHIS PARTITION: ROW LOCATION ROW LOCATION (cr=4 pr=0 pw=0 time=49 us cost=3 size=25 card=1)
1 1 1 INDEX UNIQUE SCAN T_CHANNEL_TRANRECHIS_IDX1 (cr=3 pr=0 pw=0 time=35 us cost=2 size=0 card=1)(object id 93786)
********************************************************************************
然而当把这条语句放入一个函数中，只执行1次，并且这个函数被另外一个函数重复调用151次时，却慢的出奇：
SQL ID: fr841k70ytha8 Plan Hash: 835389376
SELECT TRIM(T.HOST_SYS_ID), TRIM(T.TRAN_CODE)
FROM
T_CHANNEL_TRANRECHIS T WHERE T.SRV_TRANDATE = :B2 AND T.SRV_SEQ = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 151 0.01 0.01 0 0 0 0
Fetch 151 14.26 14.26 0 663041 604 151
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 303 14.28 14.27 0 663041 604 151
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 84 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 PARTITION LIST ALL PARTITION: 1 12 (cr=4391 pr=0 pw=0 time=94409 us cost=1340 size=550 card=22)
1 1 1 TABLE ACCESS FULL T_CHANNEL_TRANRECHIS PARTITION: 1 12 (cr=4391 pr=0 pw=0 time=94387 us cost=1340 size=550 card=22)
********************************************************************************
为解决此问题，对其进行10046跟踪，结果发现两种形式的plsql 函数代码调用解析并不一致。对于第三种极慢的情况，用hints指定索引，或者加上分区key条件，效果都不理想。指定索引后，索引可生效，速度有所提高，例如：
********************************************************************************
SQL ID: bcwwpywas8sg7 Plan Hash: 3768379849
SELECT /*+index(t,t_channel_tranrechis_idx1)*/ TRIM(T.HOST_SYS_ID),
TRIM(T.TRAN_CODE)
FROM
T_CHANNEL_TRANRECHIS T WHERE T.MONTH = SUBSTR(:B2 , 5, 2) AND T.SRV_TRANDATE
= :B2 AND T.SRV_SEQ = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 151 0.02 0.01 0 0 0 0
Fetch 151 9.44 9.44 0 149188 0 151
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 303 9.46 9.46 0 149188 0 151
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 84 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY GLOBAL INDEX ROWID T_CHANNEL_TRANRECHIS PARTITION: ROW LOCATION ROW LOCATION (cr=988 pr=0 pw=0 time=63602 us cost=1012 size=28 card=1)
1 1 1 INDEX FULL SCAN T_CHANNEL_TRANRECHIS_IDX1 (cr=987 pr=0 pw=0 time=63089 us cost=993 size=0 card=22)(object id 93786)
********************************************************************************
但是提高并不明显，执行速度仍远远低于另外一个函数中直接使用的情况。最后，将被函数调用的函数的代码移入调用者函数，即不再函数调函数，而是跟第二种方式调用一样，这时执行速度才变成与第二种调用方式速度一样快。 问题解决，但又等于没有解决。究竟是什么原因影响了oracle对函数中select语句的解析呢？
原创文章，作者：苏葳，如需转载，请注明出处：https://www.swmemo.com/402.html
