Problem with SQL coming from SQL Server into Oracle DB
- From: "Sandra Becker" <sbecker6925@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 25 Jun 2008 11:30:46 -0600
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
Other related posts:
