RE: Explain Plan not the same through ODBC

What MDAC version are you running and what Oracle client version?  

If you are using a 10 client try the same thing with a 9206 client, I have seen a rounding issue in an early version of the 10 client with ODBC, It could have been the MDAC or even the VB code using it that was the culprit, but teh customer resolved it by changing back to a 9206.  

Worth a try to isolate your issue  

Cheers  

--
Peter McLarty
Database Administrator
Student System Upgrade Project
Central Queensland University


Email:  p.mclarty_at_cqu.edu.au
  


________________________________

From: Rumpi Gravenstein [mailto:rgravens_at_gmail.com] 
Sent: Wednesday, 21 February 2007 08:10 AM
To: lucdemanche_at_gmail.com
Cc: oracle-l; Brandon.Allen_at_oneneck.com; cdelisle_at_msds.com
Subject: Re: Explain Plan not the same through ODBC


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