Re: High number of execution while using bind variables.

 

Maybe the other 50K executions used a different value somewhere.

Are you sure all 79K executions used the same bind values?


----------------------------------------------------------------------------

Nisha Mohan[1]
19 July 2012 07:56 
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
is29000 only.

Why is it different?

Thanks and Regards,
Nisha Mohan.A






--- Links ---
   1 mailto:NishaMohan_A@xxxxxxxxxxx

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

Other related posts: