Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    2,296

    Unanswered: Speed of Query or COST?

    I am trying to tune a bunch of code for our internet application.

    Obviously speed is the most important since all anyone sees is how fast the screen pops up.

    Regarding queries, I am tuning one now that one way runs faster but the Explain_Plan COST is REALLY high. The other way I wrote it takes longer but has less COST.


    SO, WTF?!

    I plan on turning SQL_TRACE on and just doing a TKPROF on the buggers, but why would it come back faster but have a MUCH HIGHER cost than the slower one??

    Crazy.

    BTW - they both use the same indexes essentially.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    here is the Explain plan for both.

    the top one takes longer, the bottom is twice as fast, but look at the cost.

    PHP Code:
    Operation    Object Name    Rows    Bytes    Cost
                    
    SELECT STATEMENT Optimizer Mode
    =CHOOSE        26         1044
      SORT GROUP BY NOSORT        26    468    992
        FILTER                    
          FILTER                    
            TABLE ACCESS BY INDEX ROWID    METER_INTERVAL    26    468    992
              INDEX RANGE SCAN    MTRINT_ORG_ID_IX    7 K         25
          FILTER                    
            NESTED LOOPS        1    50    1
              NESTED LOOPS        1    36    1
                INDEX RANGE SCAN    ACC_DEV_ACC_DEV_PK    1    21    1
                INDEX RANGE SCAN    ACCOUNT_ACT_IND2_IX    18    270     
              INDEX RANGE SCAN    GTWY_PRMS_ACT_IND_IDX    31    434     
          NESTED LOOPS        1    42    1
            INDEX RANGE SCAN    ACCOUNT_CRTD_DT_IX    36    756    1
            INDEX UNIQUE SCAN    ACC_DEV_ACC_DEV_PK    1    21     


                    
    Operation    Object Name    Rows    Bytes    Cost
                    
    SELECT STATEMENT Optimizer Mode
    =CHOOSE        510         2012
      SORT GROUP BY NOSORT        510    8 K    992
        FILTER                    
          FILTER                    
            TABLE ACCESS BY INDEX ROWID    METER_INTERVAL    520    9 K    992
              INDEX RANGE SCAN    MTRINT_ORG_ID_IX    7 K         25
          TABLE ACCESS BY INDEX ROWID    TEST_VIEW    1    16    2
            INDEX RANGE SCAN    TEMP_TEST    1         1 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    To be honest a total 'expected' cost of 1000 versus 2000 is nothing. Most instances, even laptop based with tiny pools and db buffers wouldn't notice the difference.

    You and I have discussed at length why a query with a lower expected cost can be slower than a query with a higher expected cost.

    The main points are:

    1: It's an expected cost, not a true cost. Use post query analysis to confirm what the actual cost is. ISTR sending some example queries which exaggerated how the expected cost could be wildly inaccurate against actual cost.

    2: I can pay a taxi 10 pounds from one tube station to another or pay 2 pounds for a single ticket on the underground. Sometimes the cheaper tube is quicker than the taxi, sometimes it is slower. Again, you have to use post query analysis to find out why. Cost <> speed. Oracle itself gives no metric for cost. Sometimes the more expensive taxi is quicker, sometimes it is not.

    You haven't included the actual SQL to generate those plans. Does one perform an order by on the whole result, whereas the other doesn't? This can give the impression of a slower result when in fact it is just the initial response which is quicker/slower.

    I believe I already sent you some example queries which highlited that point?

    Maybe someone else can/should explain it better than I can.

    Bill

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    yeah, I understand.

    thanks again.

    I just get frustrated a little sometimes.
    All people want is for the pages to load fast.

    How I do it is my problem I guess.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Ah, you're concerned with response time rather than cost or speed. I suppose going back to my analogy regarding the taxi and the tube. The taxi might be there straight away whereas the tube might not arrive for a couple of minutes. The end result is that the final cost and speed of getting there aren't related to the initial responsiveness.

    On the response times, they too have many factors. You can do a little to speed up initial response, there is also some client side trickery you can do to help. It very much depends on what client you're using?

    Sorry I didn't mean to give the impression that it was your problem, I just thought we were going to cover old ground.

    Cheers
    Bill

  6. #6
    Join Date
    Aug 2003
    Location
    Canada
    Posts
    3

    Cool For your reference

    It seems to me your application is a RULE-BASED not a COST-BASED, therefore, the "COST" should not reflect the real COST calculation since wrong statistics is used.

    It's also very obvious the bottom one is faster than the upper one, because the bottom one has more shallow levels and less nested than the upper one.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    What I am attempting to do is evaluate many queries to determine what could be used as views where they do checks on the data.

    This is turning out to be a great performance enhancement once all the code is re-written
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    The RBO won't give you any cost values whatsoever, only the CBO will. The whole purpose of the RBO is to follow it's rule rankings - there are no other factors (including cost or statistics).

    The shallowness or not of the plan has no bearing on the true cost of the query. A sort merge or hash join twenty levels deep may or may not have a higher cost (or faster/slower response) than what appears to be a similar sort merge or hash join at the top level. The perceived 'complexity' of the plan has no bearing on planned cost, actual cost or responsiveness.

    planned cost <> performance <> response.

    Jayson, good luck with the views. When you select from a view Oracle will merge your main query with the view query to re-plan it's access paths - ie it will generate a single query to optimise. There are both pitfalls and advantages to this route.

    Generally, the better the normalisation of your tables, the more consistent the access paths (plans) will be but not necessarily the performance. This is where global hints can be useful. Also don't discount histograms, even in their simplest form they are very powerful.

    Hth
    Bill

Posting Permissions

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