Re: Problem with SQL coming from SQL Server into Oracle DB
- From: "Sandra Becker" <sbecker6925@xxxxxxxxx>
- To: william@xxxxxxxxxxxxxxxxxxxx
- Date: Thu, 26 Jun 2008 08:31:30 -0600
Something I forgot to mention earlier is that SSRS always puts an "N" in
front of each value in the "IN" clause and we have to strip that out before
we run it from sqlplus.
Sandy
On Thu, Jun 26, 2008 at 8:24 AM, Sandra Becker <sbecker6925@xxxxxxxxx>
wrote:
> SQL Server rewrites the query and puts in the values for the bind variable
> BEFORE submitting it to the Oracle database. We pull the execution plan
> from v$sqlplan.
>
> Sandy
>
> On Thu, Jun 26, 2008 at 12:13 AM, William Robertson <
> william@xxxxxxxxxxxxxxxxxxxx> wrote:
>
>> 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@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:
