Re: Explain Plan not the same through ODBC

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Tue, 20 Feb 2007 14:56:06 -0700
Message-Id: <20070220215503.03E5C607CAF@turing.freelists.org>

What are the predicates for IDs 3 and 5 on the first plan?

Check if ODBC does some predicate data type mangling that SQLPlus does not. If Oracle has to do some implicit type conversion for the predicate on the DFS table that would preclude the use of the index.

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

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

---

This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email

---

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

Received on Tue Feb 20 2007 - 15:56:06 CST