Explain Plan not the same through ODBC

From: Luc Demanche <lucdemanche_at_gmail.com>
Date: Tue, 20 Feb 2007 15:38:23 -0500
Message-ID: <4b3687720702201238u7f467ee1o366a2cd426f122b5@mail.gmail.com>

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<file:///C:/oracle/product/10.2.0/client_1/sysman/report/portable_2074983756/index.html#step5> SELECT
STATEMENT
  4<file:///C:/oracle/product/10.2.0/client_1/sysman/report/portable_2074983756/index.html#step4> NESTED
LOOPS [OUTER]
  1<file:///C:/oracle/product/10.2.0/client_1/sysman/report/portable_2074983756/index.html#step1> PREPROD_MSDS_SYST.DFS
TABLE ACCESS [FULL]
  3<file:///C:/oracle/product/10.2.0/client_1/sysman/report/portable_2074983756/index.html#step3> PREPROD_MSDS_SYST.G4D
TABLE ACCESS [BY INDEX ROWID]
  2<file:///C:/oracle/product/10.2.0/client_1/sysman/report/portable_2074983756/index.html#step2> 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

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

Received on Tue Feb 20 2007 - 14:38:23 CST