Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Explain Plan not the same through ODBC

Re: Explain Plan not the same through ODBC

From: Rumpi Gravenstein <rgravens_at_gmail.com>
Date: Tue, 20 Feb 2007 17:09:40 -0500
Message-ID: <9c9b9dc90702201409n1180b062tbe07517a7f4d203a@mail.gmail.com>


How are you determining the ODBC and SQL*PLUS explain plans?

On 2/20/07, Luc Demanche <lucdemanche_at_gmail.com> wrote:
>
> 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
>

-- 
Rumpi Gravenstein

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 20 2007 - 16:09:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US