RE: Explain Plan not the same through ODBC
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 20 Feb 2007 17:09:42 -0500
Try executing a trace on each session using DBMS_MONITOR.SESSION_TRACE_ENABLE.
Make sure that the cursor is closed before you stop tracing or log off of
Oracle; that will cause the actual query plans to be written to the trace
files. Compare those to make sure that they're really different.
Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel@xxxxxxxxxxxxxxxxx
www.credit-suisse.com
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Luc Demanche
Sent: Tuesday, February 20, 2007 4:06 PM
To: oracle-l; Brandon.Allen@xxxxxxxxxxx
Cc: cdelisle@xxxxxxxx
Subject: Re: Explain Plan not the same through ODBC
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@xxxxxxxxx <mailto:lucdemanche@xxxxxxxxx>
> 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
BM_110e0e056508d1b7_planStep4
4 NESTED LOOPS [OUTER]
BM_110e0e056508d1b7_planStep1
1 PREPROD_MSDS_SYST.DFS TABLE ACCESS [FULL]
BM_110e0e056508d1b7_planStep3
3 PREPROD_MSDS_SYST.G4D TABLE ACCESS [BY INDEX ROWID]
BM_110e0e056508d1b7_planStep2
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
==============================================================================
Please access the attached hyperlink for an important electronic communications
disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================
Other related posts:
