11GR2 : sql consuming lot of CPU
Hi ,
We have the following sql being run as part of bigger program. It is executing 1100+ times an hour . The sql itself executes in less than a second. However the cost is very high.
SQL
SELECT HP.PARTY_ID FROM HZ_PARTIES HP WHERE HP.PARTY_TYPE = 'PERSON' and
2 LOWER (HP.EMAIL_ADDRESS) = lower('emailaddress');
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17982 | 561K| 23134 (3)|
| 1 | TABLE ACCESS FULL| HZ_PARTIES | 17982 | 561K| 23134 (3)|
---------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
83455 consistent gets
83319 physical reads
0 redo size
336 bytes sent via SQL*Net to client
509 bytes received via SQL*Net from client