RE: High number of execution while using bind variables.
Date: Thu, 19 Jul 2012 20:05:03 +0000
Message-ID: <BD475CE0B3EE894DA0CAB36CE2F7DEB4CE82A52F_at_CWYIGMBCRP02.Corp.Acxiom.net>
Mohan,
You're pretty much correct. The "Rows" column refers to how many rows the optimizer thinks it'll return for that step. With bind variables the optimizer has no idea what values are passed, but when you use a literal the optimizer can check existing stats/histograms and give more accurate data.
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring_at_acxiom.com TEL 630.944.4762 MBL 630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA WWW.ACXIOM.COM -----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nisha Mohan Sent: Thursday, July 19, 2012 3:58 AM
To: Niall Litchfield
Cc: oracle-l_at_freelists.org
Subject: RE: High number of execution while using bind variables.
What does 'Rows' imply in explain plan??Does it mean that 79543 rows are being fetched by that query using index?
Thanks and Regards,
Nisha Mohan.A
From: Niall Litchfield [mailto:niall.litchfield_at_gmail.com]
Sent: Thursday, July 19, 2012 12:39 PM
To: Nisha Mohan
Cc: oracle-l_at_freelists.org
Subject: Re: High number of execution while using bind variables.
What do you mean by 'executions'? Normally that would mean how many times the calling program ran the statement - but that's nothing to do with the bind vars and everything to do with the calling code.
On Jul 19, 2012 7:59 AM, "Nisha Mohan" <NishaMohan_A_at_infosys.com<mailto:NishaMohan_A_at_infosys.com>> wrote:
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 -- http://www.freelists.org/webpage/oracle-l *************************************************************************** The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank You. **************************************************************************** -- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 19 2012 - 15:05:03 CDT