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

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

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

Original text of this message

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