Re: High number of execution while using bind variables.
From: William Robertson <william_at_williamrobertson.net>
Date: Thu, 19 Jul 2012 08:18:38 +0100
Message-ID: <5007B4CE.5070501_at_williamrobertson.net>
| 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 |
| 5 | INLIST ITERATOR | | | | | |
|* 6 | INDEX RANGE SCAN | IDX_InD_ASABMP | 79543 | 3029K| 73 (3)| 00:00:01 |
Date: Thu, 19 Jul 2012 08:18:38 +0100
Message-ID: <5007B4CE.5070501_at_williamrobertson.net>
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_at_infosys.com
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 19 2012 - 02:18:38 CDT