SQL from the hell

OS: AIX 5300-09
Oracle: 11.2.0.2

During  some performance research on one of the database I discovered SQL from hell.

USERNAME               SID     SERIAL SQL_ID        EVENT                                  P1         P2         P3 SECONDS_IN_WAIT --------------- ---------- ---------- ------------- ------------------------------ ---------- ---------- ---------- ------------------------------------------- SCALASIGNAL           1728      31921 cd1wv1fyuhbn0 db file sequential read               440     209309          1            1808  SQL> select SQL_FULLTEXT from v$sqlarea where sql_id='cd1wv1fyuhbn0';  /* 1049 */ SELECT /*+ first_rows */ DISTINCT IKV_ID, IKV_ID, WAARDE, DATAANVTV, TS_MUT, HIS_TS_END, 'IKT', null FROM (SELECT /*+ ordered use_nl(tr ikt tab ikv ikp iko ikpt ivah ivph ikvh ikgh srg srgt)  */ ikt.ikv_id ,ikt.dataanvtv ,ikt.ts_mut ,NVL(ikt.ts_ovr, to_timestamp('99991231','yyyymmdd')) his_ts_end , s.sect || ';' || s.risgrp || ';' || s.dataanv waarde FROM   np_sector_risicogroep s       ,np_inkomstenverhouding i       ,la_ikv_transactie ikt       ,la_tijdvakaangifte_ontvangst ta       ,np_aangiftetijdvak a       ,np_administratieve_eenheid e       ,np_loonaangifte_bericht b WHERE  ikt.ikv_id = i.ikv_id_core AND    ikt.ikv_type <> 'I' AND    i.ikv_id = s.ikv_id AND    s.cdverw = 3 AND    i.atv_id = a.atv_id AND    a.aeh_id = e.aeh_id AND    a.atv_type = ta.tva_type AND    b.ber_id = e.ber_id AND    b.beridbd_ref = ta.beridbd AND    b.berdlnr_ref = ta.berdlnr AND    ikt.tvao_id = ta.tvao_id AND    ikt.dataanvtv = TO_NUMBER(REPLACE(a.dataanvtv, '-'))) WHERE 1=1  AND TS_MUT > to_timestamp('2011-07-31 13:31:49','yyyy-mm-ddhh24:mi:ss')     AND DATAANVTV BETWEEN 20070101 AND 99991230 ORDER BY null, 1,2;

Let`s have a look for an explain plan.

SQL> select * from TABLE(dbms_xplan.display_cursor('cd1wv1fyuhbn0', NULL, NULL);

WTF! It looks like never ending SQL.


First let`s remove these 2 hints in the SQL: /*+ first_rows */ and
/*+ ordered use_nl(tr ikt tab ikv ikp iko ikpt ivah ivph ikvh ikgh srg srgt)  */

SQL> alter session set current_schema=SCALASIGNAL; SQL> explain plan for     /* 1049 */     SELECT DISTINCT IKV_ID, IKV_ID, WAARDE, DATAANVTV, TS_MUT, HIS_TS_END, 'IKT', null     FROM (SELECT     ikt.ikv_id     ,ikt.dataanvtv     ,ikt.ts_mut     ,NVL(ikt.ts_ovr, to_timestamp('99991231','yyyymmdd')) his_ts_end     , s.sect || ';' || s.risgrp || ';' || s.dataanv waarde    FROM   np_sector_risicogroep s    ,np_inkomstenverhouding i    ,la_ikv_transactie ikt    ,la_tijdvakaangifte_ontvangst ta     ,np_aangiftetijdvak a     ,np_administratieve_eenheid e     ,np_loonaangifte_bericht b     WHERE  ikt.ikv_id = i.ikv_id_core     AND    ikt.ikv_type <> 'I'     AND    i.ikv_id = s.ikv_id     AND    s.cdverw = 3     AND    i.atv_id = a.atv_id     AND    a.aeh_id = e.aeh_id    AND    a.atv_type = ta.tva_type    AND    b.ber_id = e.ber_id    AND    b.beridbd_ref = ta.beridbd    AND    b.berdlnr_ref = ta.berdlnr    AND    ikt.tvao_id = ta.tvao_id    AND    ikt.dataanvtv = TO_NUMBER(REPLACE(a.dataanvtv, '-')))    WHERE 1=1  AND TS_MUT > to_timestamp('2011-07-31 13:31:49','yyyy-mm-dd hh24:mi:ss')      AND DATAANVTV BETWEEN 20070101 AND 99991230 ORDER BY null, 1,2;   Explained. sys@pppls> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('SYS.PLAN_TABLE',NULL,'ALL'))

 

What a difference. Of course never rely on time in explain plan because Oracle optimizer cannot estimate the real execution time.

It is better to look at Cost (%CPU).

Lesson learn: don`t use hints in the SQL when it is not necessary.