This discussion is archived
2 Replies Latest reply: Aug 24, 2009 12:08 PM by damorgan RSS

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

530356 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points