RE: Problem with SQL coming from SQL Server into Oracle DB
- From: "Boyle, Christopher" <Christopher.Boyle@xxxxxxxxxxxxx>
- To: <sbecker6925@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 25 Jun 2008 14:30:53 -0400
At a guess, I would say your problem lies in this line
AND m.assigneduserid IN (:inCompanyid)
If there is more than one company id it is being treated as
AND m.assigneduserid IN ('company1, company2, company3') A single
value.
NOT as AND m.assigneduserid IN ('company1', 'company2', 'company3')
three values.
Search Ask Tom for variable in lists. There are multiple solutions
available on that site. After that is addressed then reexamine the
execution plan and tell us if anything changed.
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sandra Becker
Sent: Wednesday, June 25, 2008 1:31 PM
To: oracle-l
Subject: Problem with SQL coming from SQL Server into Oracle DB
Environment: IBM zSeries VM, SLES10, Oracle EE 10.2.0.3
We have several SQL queries that run slowly when run through SQL Server
Reporting Services 2005. Explain plans with the bind variable look
good. When it actually gets to the Oracle database, the exeuction plans
show the bind variable replaced with actual values and it's a really bad
plan. When we run the query from SQL*Plus command line, it runs well
and has a good execution plan regardless of how many entries exist in
the "IN" clause for the bind variable. Has anyone seen anthing like
this or have suggestions where we can look next to tune these queries?
Example code with bind variable:
SELECT
u.userlastname||', '||u.userfirstname AS username,
u.userid,
c.companyname,
COUNT(m.docid) as activecount,
sum(m.amountdue) as activedollars
FROM users u, companies c, mbinvoice m
WHERE u.companyid = c.Companyid
AND m.assigneduser_seq = u.user_seq
AND m.assigneduserid IN (:inCompanyid)
AND m.state IN ('unMapped', 'RequiresApproval')
AND m.isdeleted = '0'
AND m.issent = '1'
AND m.paymentduedate >= SYSDATE
GROUP BY u.userlastname, u.userfirstname, u.userid, c.companyname
Example code as it appears coming from SQL Server; when the following
code has only one entry in the IN clause, it runs quickly, more than one
is very slow.
SELECT
u.userlastname||', '||u.userfirstname AS username,
u.userid,
c.companyname,
COUNT(m.docid) as activecount,
sum(m.amountdue) as activedollars
FROM users u, companies c, mbinvoice m
WHERE u.companyid = c.Companyid
AND m.assigneduser_seq = u.user_seq
AND m.assigneduserid IN
(N'ABC000012421000',N'ABC000012421002',N'ABC000012421003')
AND m.state IN ('unMapped', 'RequiresApproval')
AND m.isdeleted = '0'
AND m.issent = '1'
AND m.paymentduedate >= SYSDATE
GROUP BY u.userlastname, u.userfirstname, u.userid, c.companyname
Any help would be greatly appreciated.
Sandy
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
NOTICE OF CONFIDENTIALITY: Information included in and/or attached to this
electronic mail transmission may be confidential. This electronic mail
transmission is intended for the addressee(s) only. Any unauthorized
disclosure, reproduction, or distribution of, and/or any unauthorized action
taken in reliance on the information in this electronic mail is prohibited. If
you believe that you have received this electronic mail transmission in error,
please notify the sender by reply transmission, or contact
helpdesk@xxxxxxxxxxxxx, and delete the message without copying or disclosing
it.
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
Other related posts:
