ERROR: Emon failed to start

A short note on this error which popup in alertlog. This was due to session were waiting for

select inst_id,sid, serial#, event from gv$session where upper(event) like upper('%wait for EMON to spawn%');

   INST_ID        SID    SERIAL# EVENT
---------- ---------- ---------- ----------------------------------------------------------------
         2       1428        259 wait for EMON to spawn
         1       1388         32 wait for EMON to spawn

And as per metalink note 1361166.1 there are numerous entries on system table sys.reg$. Also, that document refer to some oracle bugs also. ID 782050.1

SQL> select SUBSCRIPTION_NAME ,count(*) from reg$ group by SUBSCRIPTION_NAME;

SUBSCRIPTION_NAME
--------------------------------------------------------------------------------   COUNT(*)
----------
"SYS"."ALERT_QUE":"HAE_SUB"
         3

"SYS"."SRVQUEUE":"TAFTSM"
      8992

Removing the excessive entries from sys.reg$ will resolve the EMON start-up issue

SQL> delete from sys.reg$ where location_name like 'net8%' and
	location_name not in (select location_name from sys.loc$) and subscription_name like '%TAFTSM%';

8987 rows deleted.

SQL> commit;

Commit complete.

SQL> select SUBSCRIPTION_NAME ,count(*) from reg$ group by SUBSCRIPTION_NAME;

SUBSCRIPTION_NAME
--------------------------------------------------------------------------------
  COUNT(*)
----------
"SYS"."ALERT_QUE":"HAE_SUB"
         3

"SYS"."SRVQUEUE":"TAFTSM"
         5

Adding Column In 11g With Default Values

Few days back friend ask me in 10g or previous version adding column in existing table with default values with not null constraint takes longer time while in 11g it takes few seconds.

Well, Answer to this is to test. Starting with 10.2.0.5 then 11g

10.2.0.5
=============

create table test as select rownum col1, 'XX' col2 from dual connect by level <= 20000;

Table created.

Elapsed: 00:00:00.09
alter table test add (col3 varchar2(10) default 'YYYY' not null);

Table altered.

Elapsed: 00:00:01.31

11.2.0.3
=============

create table test as select rownum col1, 'XX' col2 from dual connect by level <= 20000;

Table created.

Elapsed: 00:00:00.14
alter table test add (col3 varchar2(10) default 'YYYY' not null);

Table altered.

Elapsed: 00:00:00.01

Well, it’s a good amount of difference. Reason behind this is before 11g when you add column with default values and not null constraint it has to update all the columns in that tables which are null.

Starting from 11g this has changed. In 11g oracle keeps the information in the dictionary table ecol$. So, when you add column there are just few data dictonary operations. And when you select from that table it find that information from that table to populate values for existing data. But, when you insert new record it insert it with that values as default. It keeps track of that. Also, while doing this operation it takes “ROW EXCLUSIVE MODE NOWAIT” Lock.

Here is the example.

drop table test purge;
create table test pctfree 99 as select rownum col1, 'XX' col2 from dual connect by level <= 10;

tl: 9 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 0a
col  1: [ 2]  58 58
tab 0, row 3, @0x1f5c
tl: 9 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 0b
col  1: [ 2]  58 58

select * from test;

      COL1 CO COL3
---------- -- ----------
         1 XX YYYY
         2 XX YYYY
         3 XX YYYY
         4 XX YYYY
         5 XX YYYY
         6 XX YYYY
         7 XX YYYY
         8 XX YYYY
         9 XX YYYY
        10 XX YYYY

select utl_raw.cast_to_varchar2(dbms_lob.substr(BINARYDEFVAL)) from sys.ecol$;

UTL_RAW
---------
YYYY

Now, when you insert a row.

insert into test values(11,'XX','AAAA');

tl: 14 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 0c
col  1: [ 2]  58 58
col  2: [ 4]  41 41 41 41

Also, do disable this feature you can set _add_col_optim_enabled to false and you are back to 10g like behaviour.