Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2006
    Posts
    47

    Unanswered: where clause 5x slowdown for 1 extra field? wtf

    All,

    I'm not all that well versed on the intricaies of oracles optimization engine, but the behavior I'm seeing strikes me as a little odd.

    Here's the basic info:

    Products
    prodID: NUMBER
    prodName VARCHAR2(100)
    prodTitle VARCHAR2(100)
    prodUsage VARCHAR2(100)
    ProdPlacement VARCHAR2(100)
    prodStatus CHAR(1)

    At issue seems to be the prodStatus field. My first query was:

    SELECT COUNT(prodID) FROM Products WHERE prodTitle is null

    That was returning in ~270ms. That makes sense, i even think its pretty fast.

    Someone then brought up the fact that only when prodStatus='A' (active) should we be returning info about it, so....

    SELECT COUNT(prodID) FROM Products WHERE prodTitle is null AND status='A'

    Simple enough, I even thought it might save a few milliseconds. To my surpise, my execution time was now averaging 1.3 seconds. more than 4x slower than a less precise query.

    So my question is this:

    1.) Is there a way to speed this up?

    2.)Sometimes additional where clauses speed my queries up, other time they slow them down (as in this case). It seems to always depend on the field being referenced. If checking status, it seems to slow down, but if I were to add prodID as one of the where clause, it seems to speed up a little. Am I going crazy?


    Thanks in advance!

    --james

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Please check and post the execution plan.
    That is the best reference to analyze problems like that.

    Details for the EXPLAIN PLAN command can be found here:http://download-west.oracle.com/docs...plan.htm#17522

    Or use the SET AUTOTRACE option in SQL*Plus.
    http://download-west.oracle.com/docs...13.htm#1012209

  3. #3
    Join Date
    Mar 2006
    Posts
    47
    heh, good god man!

    Alrighty, I'm reading the info you linked to. Not the easiest documentation I've ever read I'll let you know when I get it figured out.


    --james

    Quote Originally Posted by shammat
    Please check and post the execution plan.
    That is the best reference to analyze problems like that.

    Details for the EXPLAIN PLAN command can be found here:http://download-west.oracle.com/docs...plan.htm#17522

    Or use the SET AUTOTRACE option in SQL*Plus.
    http://download-west.oracle.com/docs...13.htm#1012209

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by jholder
    Not the easiest documentation I've ever real
    That is true, however complex tools require complex descriptions
    Start with AUTOTRACE it's a bit easier to use than EXPLAIN PLAN

  5. #5
    Join Date
    Mar 2006
    Posts
    47

    more info

    Ok,
    I'm still looking into understanding all the trace/autotrace/explain plan stuff. But here is some info I noticed. (as you mentioned, from autotrace using Oracle SQL Developer)

    SELECT COUNT(prodID) FROM Products WHERE prodTitle is null
    consistent gets: 1718

    SELECT COUNT(prodID) FROM Products WHERE prodTitle is null AND status='A'
    consistent gets: 70292


    From the numbers alone, I'm guessing that is where my slowdown is coming from.

    We check the status in almost every query we use. So it seems to me if I could get a speedup on that part of the where clause (indexing it?materialized view?), it would have a very nice trickle down effect.

    I'm still trying to find some tutorials/pages that talk about how to tune, once you have the data. If you know of any, please shoot them my way.

    Thanks again!

    --james

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Post the execution plan that is printed by the AUTOTRACE option and post all index definitions that you have on the table. We might be able to help you then

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Check to see if you have an index on status.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •