Re: Explain Plan not the same through ODBC
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: lucdemanche@xxxxxxxxx
- Date: Tue, 20 Feb 2007 14:56:06 -0700
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
______________________________________________________________________
Other related posts:
