Repolulating the tables with multi-million rows for reduced redo logs
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Repolulating the tables with multi-million rows for reduced redo logs

  1. #1
    Join Date
    Jul 2003
    Posts
    135

    Repolulating the tables with multi-million rows for reduced redo logs

    Environment - Oracle 11g on linux

    I need to drop and recreate 10 kind-of temp tables (with some of the tables having multi-million rows) by application call.

    Below script (takes 10 minutes) is what I am doing.

    Questions:
    1. Is there a better performing/handling way.
    2. I am avoiding dropping and recreating the below tables as it invalidates some views and also want to use no-logging.
    3. Is TRUNCATE TABLE REUSE STORAGE good option here
    4. Should I drop and recreate indexes.
    5. There is separate locally managed tablespace holding these 10 tables. Is it better to do nologging at tablespace level or on individual tables level - purpose is to reduce red log creation for the process

    Please advice with answers to above questions. Thanks
    --------------------------------------------------------

    ALTER TABLESPACE tbs_10_tables NOLOGGING;

    alter table T1 nologging;
    alter table T2 nologging;
    ...
    ...
    alter table T10 nologging;


    --the script below will run everytime


    ---1

    TRUNCATE TABLE T1 REUSE STORAGE;
    drop index IDX_T1_1
    drop index IDX_T1_2
    drop index IDX_T1_3

    ---2

    TRUNCATE TABLE T2 REUSE STORAGE;
    drop index IDX_T2_1
    drop index IDX_T2_2
    drop index IDX_T2_3

    ...
    ...

    ---10

    TRUNCATE TABLE T10 REUSE STORAGE;
    drop index IDX_T10_1
    drop index IDX_T10_2
    drop index IDX_T10_3


    ----------- re-polulation of data

    Insert into T1 Select * from T1v;
    create index IDX_T1_1 on T1 (col1);
    create index IDX_T1_2 on T1 (col2);
    create index IDX_T1_3 on T1 (col3);

    Insert into T2 Select * from T2v;
    create index IDX_T2_1 on T2 (col1);
    create index IDX_T2_2 on T2 (col2);
    create index IDX_T2_3 on T2 (col3);

    ...
    ...


    Insert into T10 Select * from T10v;
    create index IDX_T10_1 on T10 (col1);
    create index IDX_T10_2 on T10 (col2);
    create index IDX_T10_3 on T10 (col3);

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    1. Is there a better performing/handling way.
    can improve little bit. you are truncating tables first then dropping indexes this need I/O on indexes also. first drop indexes then truncate.

    2. I am avoiding dropping and recreating the below tables as it invalidates some views and also want to use no-logging.
    Oracle says ...
    When an existing tablespace logging attribute is changed by an ALTER TABLESPACE statement, all tables, indexes, and partitions created after the statement will have the new default logging attribute (which you can still subsequently override). The logging attribute of existing objects is not changed.
    so, better use object level nologging option.

    3. Is TRUNCATE TABLE REUSE STORAGE good option here
    If you know that you are going to load almost same amount of data, the answer is yes.

    4. Should I drop and recreate indexes.
    as you are dealing with multi-million rows, yes you need to. drop index -> truncate -> load -> recreate index.

    create the indexes with nologging option if they are not in nologging TS.


    5. There is separate locally managed tablespace holding these 10 tables. Is it better to do nologging at tablespace level or on individual tables level - purpose is to reduce red log creation for the process
    please refer answer 2.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Jul 2003
    Posts
    135
    Thanks vnktummala.

    I was doing the table population earlier by dropping and recreating the table as 'create table myTable as select * from' - that was generating lot of redo.
    Then I changed the table and index to nologging and populated it as
    INSERT /*+ APPEND */ into myTable Select * from...

    I was expecting reduced redo but it actually went up. See numbers below.
    Why did redo went up??

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

    select value OLD_VALUE from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';

    46772

    create table myTable as Select * from myTable_v;
    Create index myTable_IX1 on myTable (col1);
    Create index myTable_IX2 on myTable (col2);


    select value OLD_VALUE from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';

    460488


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


    alter table myTable nologging;

    drop index myTable_IX1;
    drop index myTable_IX2;
    TRUNCATE TABLE myTable REUSE STORAGE;

    select value OLD_VALUE from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';

    520446

    INSERT /*+ APPEND */ into myTable Select * from myTable;
    commit;
    Create index myTable_IX1 on myTable (col1) NOLOGGING;
    Create index myTable_IX2 on myTable (col2) NOLOGGING;

    select value OLD_VALUE from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';

    1002608
    Last edited by daljitsb; 10-05-2011 at 11:48 AM.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Two questions...

    1- Are you positive the ONLY process running on the database was this one? if any other process was running that will affect redo size.

    2- As far as I can see table was created from two different sources - myTable_v on the first run and PCPA_PLAN_PTEVENT on the second run.

    Also... if you are planning to do insert APPEND do not use REUSE STORAGE during truncation, APPEND hint causes Oracle to insert above high watermark.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jul 2003
    Posts
    135
    Thanks PAVB.

    Yes, there is nothing else running.

    2 table sources were typo. I edited it now.

    Don't we need APPEND to realize the affect of nologging.

    Are you saying Reuse Storage is not benefiting the performance here. With Truncate the HWM should come down and append will insert data from lowered HWM but will not have to expand the TS storage as it fills multi-million rows.

  6. #6
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Quote Originally Posted by daljitsb View Post
    Thanks PAVB.

    Don't we need APPEND to realize the affect of nologging.

    Are you saying Reuse Storage is not benefiting the performance here. With Truncate the HWM should come down and append will insert data from lowered HWM but will not have to expand the TS storage as it fills multi-million rows.
    APPEND use direct path and appends the data after the HWM.

    Truncate with reuse storage would not bring down HWM. So not recommended to use when you are using APPEND hint.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  7. #7
    Join Date
    Jul 2003
    Posts
    135
    Did not get any performance gains or even reduced logging by switching to nologging mode of table and indexes.
    Why. Am I missing a step or parameter.

    The process (covering 10 tables) is actually now taking more time to complete earlier was 14 minutes, now 16 minutes

    --logging mode

    drop table a
    create table a as select * from a_view
    create indexes
    set grants

    select value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';
    before run and after run 'value' = 10254896


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

    --nologging mode

    truncate table a
    alter table a nologging;
    insert /*+ APPEND */ into table a as select * from a_view
    create indexes parallel 2 NOLOGGING

    select value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';
    before run and after run 'value' = 11657796

  8. #8
    Join Date
    Jul 2003
    Posts
    135
    Any suggestions

  9. #9
    Join Date
    Jul 2006
    Posts
    195
    Quote Originally Posted by daljitsb View Post
    Any suggestions
    interesting query, what exactly does this measure, redo for a specific session?

    select value from v$mystat, v$statname where v$mystat.statistic# = v$statname.statistic# and v$statname.name = 'redo size';

  10. #10
    Join Date
    Jul 2003
    Posts
    135
    Yes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width