2 Replies Latest reply: Aug 24, 2009 2:08 PM by damorgan RSS

    Comparison B/W Backup Table & Online Redefined Table of Original Table

    530356
      Hi!!!

      I created one backup of a table using "Create table Bkp_table as select * from Orig_table". This is making few changes in Storage parameters.

      Later, Online Redefined table using DBMS_REDEFINITION package just enabling "compress for all operations" and no changes made in Storage Parameters of INTERIM table

      Finally, After online Redef is finished, again the storage structure remains the same as expected ( as defined in Interim Table which is same as original table in my case).

      Q1. Any clue, why is storage parameters getting changed while creating backup table using CTAS* ??

      Q2. Are DML operations on original table and Backup table supposed to have similar perf. in this case for comparison with Online Redefined Table. OR, it is suggested to change the backup table structure similar to that of the original table and then check DML's on Backup table and Online redefined table for real performance comparison.

      Q3. Is it suggested to analyze ( analyze table .... compute statistics ) compressed table again just after online redef compression activity? If analysing and computing statictics will have any significant impact on DML perf. of this compressed table?


      E.g-  Considering DDL of one table from 11g EBSO Database

      ***Original Table Str.***

      CREATE TABLE "PO"."RCV_RECEIVING_SUB_LEDGER"
      ( "LAST_UPDATE_DATE" DATE NOT NULL ENABLE,
      "LAST_UPDATED_BY" NUMBER NOT NULL ENABLE,
      ..............
      ..............
      "PA_ADDITION_FLAG" VARCHAR2(1) DEFAULT 'N'
      ) PCTFREE 10 PCTUSED 70 INITRANS 10 MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 16384 NEXT 1024000 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER
      _POOL DEFAULT)
      TABLESPACE "POD"


      ***Str. of Backup Table created using CTAS* ***

      CREATE TABLE "PO"."RCV_RECEIVING_SUB_LEDGER_NOAC"
      ( "LAST_UPDATE_DATE" DATE NOT NULL ENABLE,
      "LAST_UPDATED_BY" NUMBER NOT NULL ENABLE,
      ..............
      ..............
      "PA_ADDITION_FLAG" VARCHAR2(1)
      ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 40960 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "POD"


      ***Interim Table created for Online Redef** [ Objective - To advance compress existing data in the table using Online Redef method ]*

      CREATE TABLE "PO"."INT_RCV_RECEIVING_SUB_LEDGER"
      ( "LAST_UPDATE_DATE" DATE NOT NULL ENABLE,
      "LAST_UPDATED_BY" NUMBER NOT NULL ENABLE,
      ..............
      ..............
      "PA_ADDITION_FLAG" VARCHAR2(1) DEFAULT 'N'
      ) PCTFREE 10 PCTUSED 70 INITRANS 10 MAXTRANS 255 COMPRESS FOR ALL OPERATIONS
      STORAGE(INITIAL 16384 NEXT 1024000 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
      TABLESPACE "POD"

      ***Final Str. of compressed table after online redef completion***

      CREATE TABLE "PO"."RCV_RECEIVING_SUB_LEDGER"
      ( "LAST_UPDATE_DATE" DATE NOT NULL ENABLE,
      "LAST_UPDATED_BY" NUMBER NOT NULL ENABLE,
      ..............
      ..............
      "PA_ADDITION_FLAG" VARCHAR2(1) DEFAULT 'N'
      ) PCTFREE 10 PCTUSED 70 INITRANS 10 MAXTRANS 255 COMPRESS FOR ALL OPERATIONS
      LOGGING
      STORAGE(INITIAL 16384 NEXT 1024000 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL DEFAULT)
      TABLESPACE "POD"

      Would highly appreciate quick response on this.

      Thanks & Regards,
      Smita

      Edited by: user527353 on Aug 21, 2009 7:40 PM
        • 1. Re: Comparison B/W Backup Table & Online Redefined Table of Original Table
          340744
          Hi Smita,

          to Q1:
          creating a table without supplying storage parameters always creates it with the default storage attributes,
          no matter if creating normally with column list or if creating with CTAS.
          For example pctfree is per default 10 on a normal table per default 0 on a compressed table.
          With online table redefinition in fact also at some step you have to create the interim table.
          And it is the same for it. If using grid control for the online redefinition it will create it with the original
          storage parameters.

          to Q2:
          i would say it depends on the storage parameters, if the same, you can expect same performance, if
          not they might differ, and of course compressed vs. not compressed, DML on compressed tables
          is slower

          to Q3:
          nowadays you use "analyze table .... compute statistics" only if you are interested in chain count,
          as this column in dba_tables is not filled by dbms_stats.
          But for statistics computation you should use dbms_stats.
          Statistics are important for queries for generating good execution plans, they normally do not
          affect DML performance as long as the DML statement does not contain a more complex query on the table.

          HTH Mathias
          • 2. Re: Comparison B/W Backup Table & Online Redefined Table of Original Table
            damorgan
            One quick correction on your answer to Q2. Compression does not always make DML slower. There are times when compression speeds things up. Lets look at the physics.

            If the choke point on a server is CPU ... if you are running at or near capacity ... and storage and network layers are under-utilized ... it is true that DML will be slower.

            But, as if very often the case, the CPU is sitting around waiting for some work to show up due to the disk access and file transfer speeds (often far too few HBA cards) then DML will be faster as few bytes need to be read and moved.