DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 19

Thread: Stats better but SQL is slower

Threaded View

  1. #1
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152

    Stats better but SQL is slower

    Running Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production.

    I had some SQL for detecting duplicates, which I rewrote to avoid having to do a self-join. The old SQL is:

    Code:
    SELECT /*+ USE_HASH(t d) */ 
             t.BUSINESS_DATE, t.SCHEDULE_ID,
            RANK() OVER 
            (PARTITION BY NVL(t.DEAL_ID, '~NVL'),
            NVL(t.LEG_TYPE_CD, '~NVL') 
    ORDER BY t.SRC_SYS_FEED_ROW_ID DESC, 
                 t.SCHEDULE_ID  DESC) ERROR_BIT_
    FROM OPD_SCHEDULE t, 
            (SELECT /*+ USE_HASH(t d) */ BUSINESS_DATE, 
                       SRC_SYS_FEED_ID,
                       NVL(t.DEAL_ID, '~NVL') AS DEAL_ID,
                       NVL(t.LEG_TYPE_CD, '~NVL') AS LEG_TYPE_CD
              FROM SCHEDULE t
             WHERE BUSINESS_DATE = :p_business_date 
                and SRC_SYS_FEED_ID = :p_src_sys_feed_id
             GROUP BY BUSINESS_DATE, SRC_SYS_FEED_ID, 
                           DEAL_ID, LEG_TYPE_CD HAVING COUNT(*) > 1) d
    WHERE t.BUSINESS_DATE = :p_business_date 
        and t.SRC_SYS_FEED_ID = :p_src_sys_feed_id 
        and BITAND(t.ROW_ERROR_CD, 3) = 0 
        and NVL(t.DEAL_ID, '~NVL') = d.DEAL_ID 
        and NVL(t.LEG_TYPE_CD, '~NVL') = d.LEG_TYPE_CD
    I rewrote this to;

    Code:
    SELECT t.rowid,  RANK() OVER (PARTITION BY DEAL_ID
    ,LEG_TYPE_CD
     ORDER BY t.SRC_SYS_FEED_ROW_ID DESC, t.SCHEDULE_ID  DESC) AS ERROR_BIT_ID
    FROM
    (SELECT ROWID, SRC_SYS_FEED_ROW_ID, SCHEDULE_ID,
    NVL(t.DEAL_ID, '~NVL') AS DEAL_ID,
    NVL(t.LEG_TYPE_CD, '~NVL') AS LEG_TYPE_CD,
    COUNT(1) OVER (PARTITION BY NVL(t.DEAL_ID, '~NVL')
    ,NVL(t.LEG_TYPE_CD, '~NVL')
     ) DUPS FROM SCHEDULE t WHERE t.BUSINESS_DATE = :p_business_date
    and t.SRC_SYS_FEED_ID = :p_src_sys_feed_id
    and BITAND(t.ROW_ERROR_CD, 3) = 0  ) t
     WHERE DUPS > 1
    Using autotrace to look at the stats, things definitely seem to have improved.

    Old:
    0 recursive calls
    29 db block gets
    43550 consistent gets
    58023 physical reads
    0 redo size
    210 bytes sent via SQL*Net to client
    234 bytes received via SQL*Net from client
    1 SQL*Net roundtrips to/from client
    1 sorts (memory)
    1 sorts (disk)
    0 rows processed

    New:
    0 recursive calls
    27 db block gets
    21775 consistent gets
    32073 physical reads
    0 redo size
    170 bytes sent via SQL*Net to client
    234 bytes received via SQL*Net from client
    1 SQL*Net roundtrips to/from client
    1 sorts (memory)
    1 sorts (disk)
    0 rows processed


    And yet...the old is actually quicker than the new one ! Admittedly, it's only a few seconds but it's a fairly consistent result across numerous runs. This is very annoying because I do think the new SQL is better and want to use it, but it's hard to justify when it seems to be slowing things down. Can anyone suggest an explanation why it is slower when all the stats point the other way ?
    Last edited by tamilselvan; 12-06-2005 at 12:36 PM.

Posting Permissions

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


Click Here to Expand Forum to Full Width