Re: High number of execution while using bind variables.

From: Ryan January <rjjanuary_at_multiservice.com>
Date: Thu, 26 Jul 2012 10:24:36 -0500
Message-ID: <50116134.5060009_at_multiservice.com>

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_at_000;
>
> COUNT(*)
> ----------
> 1
>
> QL> explain plan for
> 2 select count(*) from InD where inid _at_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 "_at_000)
>
> 14 rows selected.
>
> Now I will replace inid _at_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_at_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.

---

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

Received on Thu Jul 26 2012 - 10:24:36 CDT