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

What is different about the two environments and the execution plans? Different session parameters, v$ses_optimizer_env?

I still don't quite understand the "in (:single_bind_var)" construction or the bit about /"When it actually gets to the Oracle database, the execution plans show the bind variable replaced with actual values"/ (I've never seen that) so I suspect the version via SQL Server is somehow a different query to the one tested in SQL*Plus. How are you getting the execution plan? e.g.

select * from
table(dbms_xplan.display_cursor(/sql_id,child_number/,'typical +peeked_binds'));

using sql_id and child number from v$session?

-----Original message-----
From: Sandra Becker
Date: 26/6/08 00:22
> 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_at_williamrobertson.net <mailto:william_at_williamrobertson.net>>
> 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_at_freelists.org
>> <mailto: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