Re: Explain Plan not the same through ODBC

Hi Allan,

We are connecting to the same Oracle user, so it's the same database, same schema, same statistics, same triggers, etc. The only difference is for one, we are using SQLPlus, for the other, it's through ODBC.

Thanks
Luc

On 2/20/07, Luc Demanche <lucdemanche_at_gmail.com> wrote:
>
> Hi DBAs,
>
> We are testing our application (Fox Pro using ODBC) on a brang new server
> with Oracle10g (10.2).
> We having some performance problems regarding some SQL statement.
>
> The problem is: With the same SQL, one executing in SQLPlus and the same
> SQL executing through ODBC, we getting 2 different explain plan.
>
> With SQLPlus we have the proper one
>
> ---------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows | Bytes | Cost
> (%CPU)| Time |
> ---------------------------------------------------------------------------------------
>
> | 0 | SELECT STATEMENT | | 1 | 1251 | 5
> (0)| 00:00:01 |
> | 1 | NESTED LOOPS OUTER | | 1 | 1251 | 5
> (0)| 00:00:01 |
> | 2 | TABLE ACCESS BY INDEX ROWID| DFS | 1 | 234 | 3
> (0)| 00:00:01 |
> |* 3 | INDEX UNIQUE SCAN | DFS_PK | 1 | | 2
> (0)| 00:00:01 |
> | 4 | TABLE ACCESS BY INDEX ROWID| G4D | 1 | 1017 | 2
> (0)| 00:00:01 |
> |* 5 | INDEX UNIQUE SCAN | G4D_PK | 1 | | 1
> (0)| 00:00:01 |
>
> ---------------------------------------------------------------------------------------
>
>
> But the one through we have this one:
>
> 5 SELECT STATEMENT
> 4 NESTED LOOPS [OUTER]
> 1 PREPROD_MSDS_SYST.DFS TABLE ACCESS [FULL]
> 3 PREPROD_MSDS_SYST.G4D TABLE ACCESS [BY INDEX ROWID]
> 2 PREPROD_MSDS_SYST.G4D_PK INDEX [UNIQUE SCAN]
>
> Why do we have 2 different explain plan for the same SQL ?
>
> Thanks
> Luc
>
> --
> Luc Demanche
> Oracle DBA
> (514) 867-9977
>

-- 
Luc Demanche
Oracle DBA
(514) 867-9977

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