FAQ
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

Search Discussions

  • Boyle, Christopher at Jun 25, 2008 at 6:30 pm
    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
    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

    This email has been scanned by the MessageLabs Email Security System.
    For more information please visit http://www.messagelabs.com/email

    NOTICE OF CONFIDENTIALITY: Information included in and/or attached to this electronic mail transmission may be confidential. This electronic mail transmission is intended for the addressee(s) only. Any unauthorized disclosure, reproduction, or distribution of, and/or any unauthorized action taken in reliance on the information in this electronic mail is prohibited. If you believe that you have received this electronic mail transmission in error, please notify the sender by reply transmission, or contact helpdesk_at_multiplan.com, and delete the message without copying or disclosing it.

    This email has been scanned by the MessageLabs Email Security System.
    For more information please visit http://www.messagelabs.com/email
  • William Robertson at Jun 25, 2008 at 10:20 pm
    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
    *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
  • Sandra Becker at Jun 25, 2008 at 11:22 pm
    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> 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 ] *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

    **
    --
    http://www.freelists.org/webpage/oracle-l
  • William Robertson at Jun 26, 2008 at 6:13 am
    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
    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

    *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
  • Sandra Becker at Jun 26, 2008 at 2:24 pm
    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_at_williamrobertson.net> 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_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 ] *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

    **
    --
    http://www.freelists.org/webpage/oracle-l
  • Sandra Becker at Jun 26, 2008 at 2:31 pm
    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
    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_at_williamrobertson.net> 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_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 ] *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

    **
    --
    http://www.freelists.org/webpage/oracle-l
  • Nigel.cl.thomas_at_googlemail.com at Jun 26, 2008 at 3:01 pm

    2008/6/26 Sandra Becker :

    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.
    That could be your problem. N'string' uses the national language char set
    and collation - so a simple index on the column might not be used; are you
    seeing an implicit cast of your column to NCHAR or NVARCHAR?

    See SQL Ref Guide: Literals
    <http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements003.htm#i42617>

    Regards Nigel

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 25, '08 at 5:30p
activeJun 26, '08 at 3:01p
posts8
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase