Tom,
I have recently taken over support of a poorly written application that is suffering performance problems (bet you've heard that before!) - One of the conditions that I have identified is excessive full table scans and or index range scans that in many cases are being caused by implicit data type conversions which results in not using the indexes or not using all of the indexes columns...
Your article on
http://www.oracle.com/technetwork/issue-archive/2014/14-sep/o54asktom-2279184.html covers the topic of Reduced Access Paths in much detail and discusses steps to be taken during development to expose these issues.
The fix is straight forward - correct the bind variable type or modify the query to explicity perform the required conversion. My problem is that this type of bug is spread throughout the application.
So my question is this - is there a programmatic way to identify queries where Oracle has modified the SQL to address type mismatches so I can direct the development teams and focus their efforts on the most costly queries.
For example:
Table T_ZEV_FD_STAT is indexed on PROC_DT, FD_TSYM_CD and ACTY_TYPE_CD
SELECT stat_ts,lup_dt,lup_user_id
from fvcmdcntr.T_ZEV_FD_STAT
WHERE proc_dt = '25-oct-2017'
AND fd_tsym_cd = 1510 -- NOTE: fd_tsym_cd is defined as varchar2(10) and bind variable is numeric
AND acty_type_cd = 1;
Plan:
Plan hash value: 3144449764
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 8 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 46 | 8 (0)| 00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T_ZEV_FD_STAT | 1 | 46 | 8 (0)| 00:00:01 | KEY | KEY |
|* 3 | INDEX RANGE SCAN | PKT_ZEV_FD_STAT | 1 | | 8 (0)| 00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PROC_DT"=TO_TIMESTAMP('25-oct-2017') AND "ACTY_TYPE_CD"=1)
filter("ACTY_TYPE_CD"=1 AND TO_NUMBER("FD_TSYM_CD")=1510)
As you can see the optimizer is wrapping the reference to FD_TSYM_CD in TO_NUMBER()
If I modify the query to:
SELECT stat_ts,lup_dt,lup_user_id
from fvcmdcntr.T_ZEV_FD_STAT
WHERE proc_dt = '25-oct-2017'
AND fd_tsym_cd = '1510' -- NOTE: Passing string vs. Number
AND acty_type_cd = 1;
The plan (an performance) is much better
Plan hash value: 3144449764
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 46 | 1 (0)| 00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| T_ZEV_FD_STAT | 1 | 46 | 1 (0)| 00:00:01 | KEY | KEY |
|* 3 | INDEX RANGE SCAN | PKT_ZEV_FD_STAT | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PROC_DT"=TO_TIMESTAMP('25-oct-2017') AND "FD_TSYM_CD"='1510' AND "ACTY_TYPE_CD"=1)
filter("ACTY_TYPE_CD"=1)
Thanks.
Richard.
I would do some polling on v$sql_plan. For example
SQL> create table t as select d.*, to_char(object_id) charobj from dba_objects d;
Table created.
SQL> create index ix1 on t ( charobj ) ;
Index created.
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> declare
2 x int;
3 begin
4 for i in 1 .. 20 loop
5 execute immediate 'select count('||i||') from t where charobj = :1' into x using i;
6 execute immediate 'select count('||i||'+1) from t where charobj = :1' into x using to_char(i);
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
So I've got 40 queries there, half of which the datatype is OK, the other half will have the dreaded "to_number" implicitly put around the charobj column. We can see the SQL's "look" ok:
SQL> select sql_id, sql_text from v$sql where sql_text like 'select count%charobj%';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------
g2ffss60h01sd select count(14) from t where charobj = :1
1kzpzz32z424s select count(1+1) from t where charobj = :1
1jvj02ydwha1b select count(3+1) from t where charobj = :1
339350hnghhbz select count(7+1) from t where charobj = :1
9xbr5mqk5skty select count(10) from t where charobj = :1
bvfyqtwh6nmg1 select count(15) from t where charobj = :1
2k87s2fsrhnjp select count(1) from t where charobj = :1
1sgm0hxrd4qmt select count(11) from t where charobj = :1
5f0h85j8t8qy3 select count(8+1) from t where charobj = :1
0z8p4phyf111v select count(6) from t where charobj = :1
73c6c6dzwx5bg select count(9+1) from t where charobj = :1
16vw9ba58961b select count(12+1) from t where charobj = :1
dtpzuxwtq9a5t select count(4) from t where charobj = :1
9u0n62tu3jat9 select count(14+1) from t where charobj = :1
64rnvb3kxxb42 select count(2+1) from t where charobj = :1
b0gnw284s5ja2 select count(15+1) from t where charobj = :1
4kjgrtdpadmjj select count(3) from t where charobj = :1
d8xmda8padn4f select count(5+1) from t where charobj = :1
gscruwu2kdp7j select count(16+1) from t where charobj = :1
6gzym9afsppcc select count(17+1) from t where charobj = :1
bd58qwn95xq1w select count(20+1) from t where charobj = :1
1gb80skua9v0b select count(12) from t where charobj = :1
2947f8ywbu373 select count(7) from t where charobj = :1
gugzh2nbxf5th select count(19) from t where charobj = :1
38at550xcfat6 select count(16) from t where charobj = :1
2gw77660qucuw select count(9) from t where charobj = :1
a0uwxwfj6kj71 select count(2) from t where charobj = :1
7nbrc3uj3uma7 select count(11+1) from t where charobj = :1
7s43mps72avpx select count(20) from t where charobj = :1
0kttjn899z03n select count(17) from t where charobj = :1
bgfvffu5g71k3 select count(5) from t where charobj = :1
0r5qurspx7638 select count(8) from t where charobj = :1
82wr45fnqr7a6 select count(6+1) from t where charobj = :1
cuac94204vfr3 select count(13+1) from t where charobj = :1
8fz6jrq877hsj select count(4+1) from t where charobj = :1
dzzd8wwrp7n44 select count(19+1) from t where charobj = :1
8kkzk3asp7nf1 select count(10+1) from t where charobj = :1
a2283hfdfrnp1 select count(13) from t where charobj = :1
5aj5a28uxmvvu select count(18+1) from t where charobj = :1
b09456gnpbwg0 select count(18) from t where charobj = :1
but of course that hasn't revealed the issue with the datatype. But if I look at V$SQL_PLAN, I'll see:
SQL> select sql_id, filter_predicates
2 from v$sql_plan
3 where sql_id in ( select sql_id from v$sql where sql_text like 'select count%charobj%' )
4 and filter_predicates is not null
5 order by 1,2;
SQL_ID FILTER_PREDICATES
------------- --------------------------------------------------
0kttjn899z03n TO_NUMBER("CHAROBJ")=:1
0r5qurspx7638 TO_NUMBER("CHAROBJ")=:1
0z8p4phyf111v TO_NUMBER("CHAROBJ")=:1
1gb80skua9v0b TO_NUMBER("CHAROBJ")=:1
1sgm0hxrd4qmt TO_NUMBER("CHAROBJ")=:1
2947f8ywbu373 TO_NUMBER("CHAROBJ")=:1
2gw77660qucuw TO_NUMBER("CHAROBJ")=:1
2k87s2fsrhnjp TO_NUMBER("CHAROBJ")=:1
38at550xcfat6 TO_NUMBER("CHAROBJ")=:1
4kjgrtdpadmjj TO_NUMBER("CHAROBJ")=:1
7s43mps72avpx TO_NUMBER("CHAROBJ")=:1
9xbr5mqk5skty TO_NUMBER("CHAROBJ")=:1
a0uwxwfj6kj71 TO_NUMBER("CHAROBJ")=:1
a2283hfdfrnp1 TO_NUMBER("CHAROBJ")=:1
b09456gnpbwg0 TO_NUMBER("CHAROBJ")=:1
bgfvffu5g71k3 TO_NUMBER("CHAROBJ")=:1
bvfyqtwh6nmg1 TO_NUMBER("CHAROBJ")=:1
dtpzuxwtq9a5t TO_NUMBER("CHAROBJ")=:1
g2ffss60h01sd TO_NUMBER("CHAROBJ")=:1
gugzh2nbxf5th TO_NUMBER("CHAROBJ")=:1
20 rows selected.
I can see that for 20 of them things were not as nice as we thought.
So I'd go looking for entries in FILTER_PREDICATES that suggest a conversion is taking place.
Typically look for
TO_NUMBER("
and
INTERNAL_FUNCTION
(which is often used for dates)