Mohamed Houri’s Oracle Notes

December 10, 2017

Compress enable novalidate

Filed under: Oracle — hourim @ 7:47 pm

We knew here that we can’t DDL optimise a BASIC compressed table. So I uncompressed my table in a 12.2.0.1.0 and DDL optimise it as in the following:

SQL> select 
        pct_free
	,compression
	,compress_for
    from 
	 user_tables
    where 
	   table_name = 'T1';

  PCT_FREE COMPRESS COMPRESS_FOR
---------- -------- -------------
         0 DISABLED

SQL> alter table t1 add c_ddl number default 42 not null;

Table altered.

But I changed my mind and finally decided to revert back and drop this newly added column:

SQL> alter table t1 drop column c_ddl;
alter table t1 drop column c_ddl
                           *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

Damned database!!!

What’s going on here? My table is not compressed. Is it?

In fact I didn’t show you how I have uncompressed the t1 table:

SQL> alter table t1 nocompress;

Table altered.

Compressing or un-compressing a table without moving it will do nothing to its data. When the COMPRESSION column of a table is DISABLED this doesn’t necessarily mean that this table doesn’t contain compressed data . And this is probably the reason why the above bizarre error pops up out from nowhere.
In fact in order to have a significant meaning, the COMPRESSION column should normally always be considered with the PCT_FREE column (notice in passing that this is why I printed above the pct_free value):

SQL> select
         table_name
        ,compression
        ,pct_free
        ,compress_for
     from 
     user_tables
    where table_name = 'T1';

TABLE_NAME COMPRESS   PCT_FREE COMPRESS_FOR
---------- -------- ---------- --------------
T1         DISABLED          0

When PCT_FREE equals zero this is generally a hint that Oracle is considering BASIC compression since it thinks that the table is not considered for updates anymore. The definition given by Oracle to the COMPRESSION column goes in the same direction as well:

COMPRESSION	 	Indicates whether table compression 
                    is enabled (ENABLED) or not (DISABLED)

This is crystal clear: COMPRESSION referes to the status of the current compression. It doesn’t tell anything about the compression history of the table if any.

Compressing/uncompressing a table without moving it is nothing else than implementing or desimplementing compression but only for newly inserted rows. This is why I think that Oracle could have avoided the confusion by using the same command as when dealing with table constraints. Instead of this:

SQL> alter table t1 nocompress;

We would have this :

SQL> alter table t1 nocompress enable novalidate;

And of course the COMPRESSION column should have had a status indicating whether it is validated or not.

In the 10046 file of a compressed table with a move of its data and without a move we can see this respectively:

alter table t1 move compress basic

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          0          1           0
Execute      1      1.43       1.44          3       1865       2548     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.45       1.46          3       1865       2549     1000000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD AS SELECT  T1 (cr=1905 pr=1 pw=1382 time=1436345 us
   1000000    1000000    1000000   TABLE ACCESS FULL T1 (cr=1797 pr=0 pw=0 time=104378 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PGA memory operation                         1140        0.00          0.01
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                         3        0.00          0.00
  direct path write                               1        0.00          0.00
  reliable message                                4        0.00          0.00
  enq: RO - fast object reuse                     2        0.00          0.00
  enq: CR - block range reuse ckpt                2        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        5.93          5.93
********************************************************************************
alter table t1 nocompress


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          2           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.01          0          0          2           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106  

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Compression analysis                            8        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        6.65          6.65
********************************************************************************

Spot that whileOracle managed to rework a million rows of in the first case it didn’t touch any in the second case proving that nocompress/compress without a move doesn’t concern existing data.

Since it concerns only new data then an inserted row should be compressed/non-compressed. Let’s see:

SQL> create table t1 as select
      rownum n1
     ,mod(rownum,10) n2
     from dual
     connect by level <=1e6;

Table created.

SQL> @sizeBysegNameMB
Enter value for segment_name: t1
Enter value for owner: c##mhouri


SEGMENT_TYPE  TABLESPACE_NAME  SEGMENT_NAME PARTITION_NAME          MB
------------- ---------------- ------------ --------------- ----------
TABLE         USERS            T1                                   15
                                                            ----------
Total Segment Size                                                  15

Let’s compress this table without moving its data and get its new size:

SQL> alter table t1 compress;

Table altered.

SQL> @sizeBysegNameMB
Enter value for segment_name: t1
Enter value for owner: c##mhouri


SEGMENT_TYPE  TABLESPACE_NAME  SEGMENT_NAME PARTITION_NAME          MB
------------- ---------------- ------------ --------------- ----------
TABLE         USERS            T1                                   15
                                                            ----------
Total Segment Size                                                  15

As you can see the size of the table remains intact indicating again that compressing without moving does nothing to the existing data.

Let’s now direct path load a single row, dump the corresponding block and analyse the dump to see if compression has been activated or not:

SQL> select rowid, n1,n2 from t1 where n1=1;

ROWID                      N1         N2
------------------ ---------- ----------
AAATYNAAHAAAdpTAAA          1          1

SQL> insert /*+ append */ into t1 select 1,42 from dual;

1 row created.

SQL> select rowid, n1,n2 from t1 where n1=1;
select rowid, n1,n2 from t1 where n1=1
                         *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL> commit;

Commit complete.

SQL> select rowid, n1,n2 from t1 where n1=1;

ROWID                      N1         N2
------------------ ---------- ----------
AAATYNAAHAAAdpTAAA          1          1
AAATYNAAHAADOgcAAA          1         42

SQL> select
  2    dbms_rowid.rowid_relative_fno('AAATYNAAHAADOgcAAA') file_no
  3  , dbms_rowid.rowid_block_number('AAATYNAAHAADOgcAAA') block_no
  4  from dual;

   FILE_NO   BLOCK_NO
---------- ----------
         7     845852


SQL> alter session set tracefile_identifier='DumpingDirectLoadRowInsert';

Session altered.

SQL> alter system dump datafile 7 block 845852;

System altered.

Unfortunately I couldn’t spot anything interesting in the dump file which indicates that the inserted row has been compressed. I will probably come back to this article in a near future.

Advertisements

Leave a Comment »

No comments yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

You are commenting using your Google+ 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: