RE: High number of execution while using bind variables.

Hi,
I am still confused. Pls help me understanding.
If I replace the bind variables with values as below ,it returns 27K records 
only
Select COUNT ( InID) From InD   PPR BO95_PPR where  IND.PPR = PPR. PPRName AND  
 ( ( ( ( ( (  AGD = 10 ) AND (  AGUD is null ) ) AND (  MW is null ) ) AND ( ( 
(  Status = 'DOWN' ) OR (  Status = 'RUN' ) ) ) ) AND  BD = 10 ) );


COUNT(InID)
-------------
       27030

But why is plan showing 79543?
I have done some RnD as below but more confused now.

SQL> select count(*) from IND;
  COUNT(*)
----------
   1703971

explain plan for
  2  select count(*) from IND;

Explained.
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------
Plan hash value: 507063835

-------------------------------------------------------------------------------------
| Id  | Operation        | Name                     | Rows  | Cost (%CPU)| Time 
    |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                          |     1 |  1180   (3)| 
00:00:15 |
|   1 |  SORT AGGREGATE  |                          |     1 |            |      
    |
|   2 |   INDEX FULL SCAN| IX_IND_NVL_ASS_GRP |  1703K|  1180   (3)| 00:00:15 |
-------------------------------------------------------------------------------------

9 rows selected.

So the count(*) and Rows in plan table is same.

SQL> select count(*) from InD where inid@000;

  COUNT(*)
----------
         1

QL> explain plan for
  2  select count(*) from InD where inid @000;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3834469162

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     6 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     6 |            |          |
|*  2 |   INDEX RANGE SCAN| PK72 |     1 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("INID "@000)

14 rows selected.

Now I will replace inid @000 with bind variable .

SQL> explain plan for select count(*) from IND  where INID= :1;
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3834469162

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     6 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     6 |            |          |
|*  2 |   INDEX RANGE SCAN| PK72 |     1 |     6 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("INID"=TO_NUMBER(:1))

So even after replacing INID@00 with INID=:1 ,the optimizer shows the Rows as 1 
in plan table. Why is the same not happening for my original query?The plan 
table also should show 27k instead of 79K.Please throw some light on this.

Thanks and Regards,
Nisha Mohan.A


-----Original Message-----
From: Herring Dave - dherri [mailto:Dave.Herring@xxxxxxxxxx]
Sent: Friday, July 20, 2012 1:35 AM
To: Nisha Mohan; Niall Litchfield
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: High number of execution while using bind variables.

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@xxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Nisha Mohan
Sent: Thursday, July 19, 2012 3:58 AM
To: Niall Litchfield
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx]
Sent: Thursday, July 19, 2012 12:39 PM
To: Nisha Mohan
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx<mailto:NishaMohan_A@xxxxxxxxxxx>> 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-l


Other related posts: