Re: High number of execution while using bind variables.
- From: Ryan January <rjjanuary@xxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 26 Jul 2012 10:24:36 -0500
Resent due to over-quoting.
Nisha,
I believe you may be confusing the actual number of rows returned with
cardinality estimates. The values you're seeing in the 'rows' column of
the explain plan are estimates based on table statistics, not the number
of actual values returned for that step of the plan. Conversely; Your
query results are showing actual results, not estimates. The two should
not be expected to return the exact same value.
To see this in action try viewing the plan as the following link
suggests by using the GATHER_PLAN_STATISTICS hint and viewing the
resultant plan through DBMS_XPLAN.DISPLAY_CURSOR.
(https://blogs.oracle.com/optimizer/entry/how_do_i_know_if)
If the estimates are consistently much different than actual rows on
operations containing that table, it could indicate an issue with your
statistics.
Also keep in mind that by replacing the bind with a literal value the
optimizer will see this as a different sql statement and will create a
separate plan accordingly.
Thanks,
Ryan
On 07/20/2012 01:07 AM, Nisha Mohan wrote:
> 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
---------------------------------------------------------------------------------------
This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email.
---------------------------------------------------------------------------------------
--
//www.freelists.org/webpage/oracle-l
Other related posts:
