Re: Problem with SQL coming from SQL Server into Oracle DB
- From: "Sandra Becker" <sbecker6925@xxxxxxxxx>
- To: william@xxxxxxxxxxxxxxxxxxxx
- Date: Wed, 25 Jun 2008 17:22:25 -0600
If the bind variable is replaced with 1, 2, 3, or even up to 20 values and
run from sqlplus or SQL Developer, if performs very well. If you submit the
same values through SQL Server it runs very poorly. No idea how evenly the
values are distributed. Tables were analyzed and we do the automatic stats
gathering available in 10g.
As far as one value running as quickly as 3, well, there are those among us
who think that doing an FTS on 23M rows and returning 30% of those rows
should come back in 3 seconds or less. That's another battle for another
day. Right now our concern is that the sql runs very well from sqlplus but
chokes when submitted through SQL Server. The other DBA and I believe it's
a problem on the SQL Server or the JSP side of things, but have been unable
to convince this particular developer that he needs to do more
investigation.
Sandy
On Wed, Jun 25, 2008 at 4:20 PM, William Robertson <
william@xxxxxxxxxxxxxxxxxxxx> wrote:
> 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@xxxxxxxxxxxxx [
> mailto:oracle-l-bounce@xxxxxxxxxxxxx <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
>
> **
>
>
Other related posts:
