Re: Problem with SQL coming from SQL Server into Oracle DB

Surely nobody would expect Oracle to treat one variable as three if it turns out to contain two commas.

What is the execution plan? Are the assigneduserid values unevenly distributed? Are the tables analyzed? How? etc etc.

-----Original message-----
From: Boyle, Christopher
Date: 25/6/08 19:30
>
>
>
> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] *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 <http://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
>
>
> //
>

--
http://www.freelists.org/webpage/oracle-l