Mohamed Houri’s Oracle Notes

March 20, 2018

DDL optimisation is not working in 12cR2: really?

Filed under: Oracle — hourim @ 7:32 pm

If you want to know what DDL optimisation is then you can read this article I wrote a couple of years ago.

As the title already suggests, I have been surprised during the preparation of a database upgrade script from 11gR2 to 12cR2 to see the following alter table taking 5 hours instead of what should have been an instantaneous operation:

SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


SQL> alter table t1 add
  	(col1 number  default 0 null
       ,col2 number default 0 null
       ,col3 number default 0 null
       ,col4 number default 0 null
       ,col5 number default 0 null
       ,col6 number default 0 null
       ,col7 number default 0 null
      )
      /
Table altered.

Elapsed: 04:51:24.48

Oracle should have used the DDL optimisation technique which has been extended to nullable columns in 12cR1.

So what the heck is this? From where am I going to start troubleshooting this issue?

The first thing that came to my mind was to check the value of the hidden parameter, _add_col_optim_enabled, driven this feature. It might be possible that the DBA has un-set its default value during the database upgrade:

SQL> select
         n.ksppinm
        ,c.ksppstvl
        ,n.ksppdesc
       from
        sys.x$ksppi n
        ,sys.x$ksppcv c
       where n.indx=c.indx
       and n.ksppinm = '_add_col_optim_enabled';

KSPPINM                KSPPSTVL   KSPPDESC
---------------------- ---------- -----------------------------------
_add_col_optim_enabled TRUE       Allows new add column optimization

The DDL optimisation parameter is correctly set. If this parameter has been changed to FALSE the DDL optimisation feature would have, naturally, been cancelled as the following demonstrates:

create table t as select rownum n1 from dual connect by level <=10;

-- the following alter table will use the DDL optimisation
-- as the predicate of the execution plan confirms

alter table t add col1 number default 0 null;

select count(1) from t where col1=42;

select * from table(dbms_xplan.display_cursor);					   

---------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |
|*  2 |   TABLE ACCESS FULL| T    |     1 |     3 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00002$",0)),NULL
             ,NVL("COL1",0),'0',NVL("COL1",0),'1',"COL1")=42)

-- change the hidden parameter driving the DDL optimisation
alter session set "_add_col_optim_enabled"=false;

-- and add a new equivalent column
alter table t add col2 number default 0 null;

select count(1) from t where col2=42;

select * from table(dbms_xplan.display_cursor);	

---------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |
|*  2 |   TABLE ACCESS FULL| T    |     1 |     3 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 – filter("COL2"=42) -- DDL optimisation didn't occur.

After having ruled out the hidden parameter hypothesis, I embarked on a bibliography research checking the Oracle official documentation to see whether the DDL optimisation has actually been withdrawn in 12cR2 for nullable added columns having a default value or not. It is useless to say that this effort has been pointless.

Nowadays when all else fails Twitter might be there for a quick help:

Unfortunately not everyone is lucky. No answer 🙂

There was, nevertheless, something in the overnight SQL batch log that should have given me a hint about what was happeing under the hood:

SQL> alter table t1 add (colX numeric default 0 not null);

Table altered.

Elapsed: 00:500:00.06

While adding a nullable column with default value was taking an eternity, adding a not null column with default value to the same table was instantaneous. This database is reacting exactly as if I were running under an 11gR2 version. But I’ve completely neglected this option and continued exploring other possibilities until I decided to close my laptop and leave for the week end.

On Monday of the next week I shared this issue with one of my friends who suggested me to check the compatible parameter. No sooner said than done:

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
compatible                           string      11.2.0.4.0

Now that I have this value in front of my eyes, bringing together the pieces of the puzzle becomes easy. I provisionned a new fresh copy of Production database, upgraded it to 12.2.0.1.0, checked the compatible parameter:

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
compatible                           string      12.2.0.1

and launched again the same alter table which obviously completed in less a second:

SQL> alter table t1 add
  	(col1 number  default 0 null
       ,col2 number default 0 null
       ,col3 number default 0 null
       ,col4 number default 0 null
       ,col5 number default 0 null
       ,col6 number default 0 null
       ,col7 number default 0 null
      )
      /
Table altered.

Elapsed: 00:00:00.10

Bottom Line

It doesn’t matter what Oracle version you are running, the only features that you will be allowed to use are those enabled by the Oracle version you will set in the compatible parameter. Thus don’t be surprised to see new features not kicking in if you set the compatible mode to a prior release value where these intended features were not implemented yet.

Advertisements

1 Comment »

  1. Very interesting. Thank you

    Comment by Saad — March 24, 2018 @ 7:09 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: