High number of execution while using bind variables.

From: Nisha Mohan <NishaMohan_A_at_infosys.com>
Date: Thu, 19 Jul 2012 12:26:56 +0530
Message-ID: <3F2FEF77A7095A4D8D354F174B3535630DA249743A_at_BLRKECMBX07.ad.infosys.com>

Hi,
The below query is going for 79000 executions when bind variables are used. Select COUNT ( InID) From InD PPR BO95_PPR where IND.PPR = PPR. PPRName AND ( ( ( ( ( ( AGD = :1 ) AND ( AGUD is null ) ) AND ( MW is null ) ) AND ( ( ( Status = :2 ) OR ( Status = :3 ) ) ) ) AND BD = :4 ) )

Bind variable values are as follows

:1 = 10
:2 = Down
:3 = Run
:4 = 10

------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT              |                      |     1 |    54 |    74   (3)| 00:00:01 |

|   1 |  SORT AGGREGATE               |                      |     1 |    54 |            |          |

|   2 |   NESTED LOOPS                |                      | 79543 |  4194K|    74   (3)| 00:00:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| PPR     |     1 |    15 |     1   (0)| 00:00:01 |

|*  4 |     INDEX RANGE SCAN          | IDX_PPR_BD     |     1 |       |     1   (0)| 00:00:01 |


|   5 |    INLIST ITERATOR            |                      |       |       |            |          |

|*  6 |     INDEX RANGE SCAN          | IDX_InD_ASABMP | 79543 |  3029K|    73   (3)| 00:00:01 |
------------------------------------------------------------------------------------------------------

When I replace the bind variables with hard coded values, the row returned is 29000 only.

Why is it different?

Thanks and Regards,
Nisha Mohan.A

• CAUTION - Disclaimer ***************** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS******** End of Disclaimer ********INFOSYS***

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jul 19 2012 - 01:56:56 CDT