9 Replies Latest reply: Apr 25, 2014 1:31 PM by Jocelyn Simard RSS

    12c Row Archival space utilization

    Jocelyn Simard

      Hi all,

       

      Question 1:

      Have someone noticed that RAW hidden column (see column named SYS_NC00004$ below) created when one enable row archival?

      Any explanation about what it is used for?

       

      I haven't seen any documentation on that one. I'm mostly concerned about its eventual space utilization...

      My test show it to be zero initially but... that doesn't prove it will never grow.

      I'm also surprised that the ORA_ARCHIVE_STATE is defined as VARCHAR2(4000) - just strange...

       

      Question 2:

       

      How do you know if a column utilizes space?

       

      If you look at DBA_TAB_COLUMNS for that ORA_ARCHIVE_STATE, you'll notice that VIRTUAL_COLUMN = 'NO', but it seems that the default value is actually not stored in the table (easy to prove: alter table xxx row archival is alway instantaneous regardless of the number of rows).

       

      VSIZE and DUMP are useless for that, I think.

       

      Do I really need to dump the block and analyze the output?

       

      Thanks to all!

       

      SQL> drop table tmp_objects purge

      Table dropped.

      SQL> create table tmp_objects as select owner, object_name, created from dba_objects

      Table created.

      SQL> set linesize 100

      SQL> column column_name format a30

      SQL> column data_type format a15

      SQL> column data_default format a15

      SQL> select column_id, column_name, data_type, data_length, data_default, virtual_column from user_tab_cols where table_name='TMP_OBJECTS' order by 1

       

      COLUMN_ID COLUMN_NAME                    DATA_TYPE       DATA_LENGTH DATA_DEFAULT    VIRTUAL_COLUMN

      ---------- ------------------------------ --------------- ----------- --------------- --------------

               1 OWNER                          VARCHAR2                128                 NO           

               2 OBJECT_NAME                    VARCHAR2                128                 NO           

               3 CREATED                        DATE                      7                 NO           

       

      3 rows selected.

      SQL> alter table tmp_objects row archival

      Table altered.

      SQL> select column_id, column_name, data_type, data_length, data_default, virtual_column from user_tab_cols where table_name='TMP_OBJECTS' order by 1

       

      COLUMN_ID COLUMN_NAME                    DATA_TYPE       DATA_LENGTH DATA_DEFAULT    VIRTUAL_COLUMN

      ---------- ------------------------------ --------------- ----------- --------------- --------------

               1 OWNER                          VARCHAR2                128                 NO           

               2 OBJECT_NAME                    VARCHAR2                128                 NO           

               3 CREATED                        DATE                      7                 NO           

                 SYS_NC00004$                   RAW                     126                 NO           

                 ORA_ARCHIVE_STATE              VARCHAR2               4000 0               NO           

       

      5 rows selected.

      SQL> select sum(nvl(vsize(SYS_NC00004$),0)) from tmp_objects

       

      SUM(NVL(VSIZE(SYS_NC00004$),0))

      -------------------------------

                                    0

      1 row selected.

      SQL> select sum(nvl(vsize(ORA_ARCHIVE_STATE),0)) from tmp_objects

       

      SUM(NVL(VSIZE(ORA_ARCHIVE_STATE),0))

      ------------------------------------

                                     91518

      1 row selected.

        • 1. Re: 12c Row Archival space utilization
          Mike Kutz

          I can't help you on question 1.

           

          As far as question 2 goes:

          It looks like you're seeing the effects of an 11g "fast add column" feature.

          A command like:

          ALTER TABLE add ( new_column varchar2(50) not null default 'something')
          

          will only modify the metadata for the table, not the data blocks.

           

          Oracle 11g Database New Features - Enhanced ADD COLULMN functionality

           

          MK

          • 2. Re: 12c Row Archival space utilization
            Jocelyn Simard

            Thank you Mike for pointing that out.

             

            I should have mentioned that ROW ARCHIVAL is a 12.1.0.1 feature.

            The ORA_ARCHIVE_STATE column is NULL allowed.

            Oracle extended that functionality to optional column as well.

             

            From 12c documentation: ALTER TABLE

             

            If you specify the DEFAULT clause for a column, then the default value is stored as metadata but the column itself is not populated with data. However, subsequent queries that specify the new column are rewritten so that the default value is returned in the result set.

             

            So, that answer question 2. Thanks!

             

            Any thoughts on that mysterious SYS_NC00004$ column appearing with ORA_ARCHIVE_STATE?

             

            Regards,

            Jocelyn

            • 3. Re: 12c Row Archival space utilization
              rp0428

              Have someone noticed that RAW hidden column (see column named SYS_NC00004$ below) created when one enable row archival?

              Any explanation about what it is used for?

              . . .

              Any thoughts on that mysterious SYS_NC00004$ column appearing with ORA_ARCHIVE_STATE?

              Yes - it is the column that maintains the value of 'ORA_ARCHIVE_STATE'.

               

              It was likely defined as VARCHAR2(4000) so that you, the user, could set a value that large if you so choose.

               

              See the example 5-9 'Using In-Database Archiving' in the VLDB and Partitioning doc

              http://docs.oracle.com/cd/E16655_01/server.121/e17613/part_lifecycle.htm#CHDDAIDE

              To manage In-Database Archiving for a table, you must enable ROW ARCHIVAL for the table and manipulate the ORA_ARCHIVE_STATE hidden column of the table. Optionally, you specify either ACTIVE or ALL for the ROW ARCHIVAL VISIBILITY session parameter.

               

              For example, you can use the SQL statements similar to those in Example 5-9 to hide or show rows in a table. The purpose is to display only active data in most situations, but to maintain all data in case it is needed in specific situations.
              . . .

              /* Insert a value into ORA_ARCHIVE_STATE to set inactive */

              UPDATE employees_indbarch SET ORA_ARCHIVE_STATE = '20' WHERE employee_id = 252;

              . . . 

              • 4. Re: 12c Row Archival space utilization
                Jocelyn Simard

                Thank you for your response,

                 

                 

                I know about the hidden column ORA_ARCHIVE_STATE and that's ok if it's VARCHAR2(4000)...

                 

                My question is mostly regarding the other hidden column (named SYS_NC00004$ above) that appear at the same time as ORA_ARCHIVE_STATE. This column is declare as RAW(126) and I could not find any documentation for this.

                 

                 

                So, I create the table with 3 columns initially, then altered the table to ROW ARCHIVAL, then 2 new hidden columns are there:

                 

                 

                COLUMN_ID COLUMN_NAME                    DATA_TYPE       DATA_LENGTH DATA_DEFAULT    VIRTUAL_COLUMN

                ---------- ------------------------------ --------------- ----------- --------------- --------------

                (3 first columns as expected...)

                           SYS_NC00004$                   RAW                    126                NO          

                           ORA_ARCHIVE_STATE              VARCHAR2               4000 0               NO         

                 

                 

                Thanks again!

                • 5. Re: 12c Row Archival space utilization
                  rp0428
                  My question is mostly regarding the other hidden column (named SYS_NC00004$ above) that appear at the same time as ORA_ARCHIVE_STATE. This column is declare as RAW(126) and I could not find any documentation for this.

                  The only prior use of that column has been with functional indexes to store optimizer statistics as noted in Richard Foote's blog

                  https://richardfoote.wordpress.com/category/function-based-indexes/

                   

                  and also shown in one of Jonathan Lewis's articles

                  http://jonathanlewis.wordpress.com/2013/11/04/outline-bug/

                   

                  For the functional index use case the column is VIRTUAL

                   

                  Your use case may have discovered a bug. The reason I suspect a bug is because that column is NOT always created. LImited testing shows that the SYS_NC00004$ column is ONLY created if you use to ALTER statement to enable row archiving on an existing (even if empty) table.

                   

                  That column is NOT created if you create the table with archiving. Also a CTAS using ROW ARCHIVAL actually fails.

                  -- succeeds with NO SYS_NC00004$ column
                  create table tmp_objects1 (owner varchar2(128), object_name varchar2(128), created date) row archival

                   

                  -- succeeds but creates a SYS_NC00004$ column that is NOT virtual

                  create table tmp_objects2 (owner varchar2(128), object_name varchar2(128), created date)

                  alter table tmp_objects2 row archival

                   

                  -- fails with 'ORA-01722: invalid number'
                  create table tmp_objects3 row archival as select owner, object_name, created from all_objects

                  I discovered that by first testing with that example 5-9 from the doc link I provided earlier. I noticed that it did NOT create that column.

                   

                  If you have a support contract you could search MOS to see if a bug has been reported for that and, if not, open a TAR and see what Oracle says.

                   

                  I can't think of a reason to add that column when using ALTER but not when creating the table as archive-enabled to begin with. I haven't tested but if it is NOT a hidden column you should find that it DOES consume space.

                   

                  Weird.

                   

                  I suggest you leave the thread open for a while to see what other responses you get. I posted a companion thread with BUG in the subject to try to get more input and testing by others.

                  https://community.oracle.com/message/12397331#12397331

                  • 6. Re: 12c Row Archival space utilization
                    Jocelyn Simard

                    Thank you rp0428!

                     

                    I'll leave this thread open then...

                     

                    Just a precision:

                    - That SYS_NC* column is HIDDEN and NOT virtual... so my concern is to eventually leak storage with that

                    - I wish one could tell that a column is using storage or just metadata (but that's another story - enhancement request?)

                     

                    Regards,

                    Jocelyn Simard

                    • 7. Re: 12c Row Archival space utilization
                      Mike Kutz

                      - I wish one could tell that a column is using storage or just metadata (but that's another story - enhancement request?)

                      why? If you want to know how much disk space is being used, look at dbms_space.free_space and Tom Kyte's show_space code.

                       

                      Besides, the answer to this question becomes very cloudy as soon as you introduce table level compression.

                      A data value could be stored once within the data block and 'reused' for all rows that are contained within that data block.

                      In that situation, the answer to your question is: neither

                       

                      MK

                      • 8. Re: 12c Row Archival space utilization
                        rp0428
                        - I wish one could tell that a column is using storage or just metadata (but that's another story - enhancement request?)

                        If it isn't 'virtual' then it is using storage. The amount of storage used depends on: 1) whether it is null 2) if null whether is is in one of the trailing null columns, 3) the datatype (e.g. CHAR is fixed length, VARCHAR2 is variable length) and 4) the actual value

                         

                        You can conduct some tests that insert rows and then see if those columns have data, get the length of those columns and/or see how many used blocks get allocated.

                        • 9. Re: 12c Row Archival space utilization
                          Jocelyn Simard

                          "If it isn't 'virtual' then it is using storage."

                           

                          Not necessarily... Fast-column add is using metadata initially until you update the data.

                           

                          From what's been said above:

                          - The ORA_ARCHIVE_STATE column is not virtual.

                          - It is apparently fast-added (see Mark answer above)

                          - It is not using space until we start updating the data

                           

                          We could have a non virtual column that is metadata only, using storage or anything in between...