CTWR Consuming Lots Of Cpu

OS: Red Hat Enterprise Linux AS release 4 (Nahant Update 5) 
Oracle: 11.2.0.2

I received an alert that CPU utilization is high on one of the server.


First look at top command.

oracle@asrwed003(/home/oracle)$ ps auxw | grep 4149 | grep -v grep oracle    4149 84.9  0.2 2525648 18540 ?     Rs   Aug16 3366:47 ora_ctwr_emrep

CTWR process is the block change tracking one.
Let`s search on the Metalink.
Bingo.

Bug 10170431 - CTWR consuming lots of CPU cycles with block tracking (Doc ID 10170431.8)

CTWR Consuming Lots Of Cpu Cycles With Block Tracking (Doc ID 1310304.1)

There are solution to apply the patch but it requires a downtime.
I use a workaround: disable block change tracking.

Let`s check the session on the database using SQL*Plus.

SQL> select * from v$block_change_tracking; STATUS FILENAME  BYTES -------------------------------------------------------------------------------------------------------------- ENABLED /opt/oracle/admin/emrep/bct/blockchange.track   11599872 SQL> alter database disable block change tracking; Database altered.

After that CPU utilization came back to normal.

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.