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:
I rewrote this to;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
Using autotrace to look at the stats, things definitely seem to have improved.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
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 ?




Reply With Quote