SQL Performance (MOSC)

MOSC Banner

11GR2 : sql consuming lot of CPU

edited May 19, 2017 5:02AM in SQL Performance (MOSC) 10 commentsAnswered

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center