DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Can We Ignore RBS ?

  1. #1
    Join Date
    Oct 2001
    Location
    Hyderabad, India
    Posts
    15

    Question

    Hi,

    I'm working on Data porting from one database to another. Version of both the databases are same. We have created some predefined scripts which deletes,inserts & updates data to new database as selecting from older one.

    We worked a lot on query optimizations and minimising network traffics which improved speed/performance to a better extents.

    Now still we need to improve speed as we have to port one more database very soon which contains very heavy data. So I need more tips on improving performance while data porting.

    can creating tables in nologging mode will help ?
    can we ignore RBS so the internal workload will minimise?
    Is there any other aspect to look at?

    Thanks in advance.

    - Milind

  2. #2
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Sure nologging can be very much of a help.

    But I don't think you can ignore the RBS.
    Make small rbs for small transactions,
    and big rbs for big transactions.

    Other aspect to look at are:
    Storage definition if more updates then
    PCTFREE 15+ and PCTUSED 80, PCTINCREASE 0
    if less updates more inserts then
    PCTFREE 5 and PCTUSED 90, PCTINCREASE 0.

    increase data buffer cache, sort_area_size

    Indexes of the older db must at least be rebuild.

    That's all I can think for now.


  3. #3
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    one last minor thing : create indexes after having imported data, it should be faster

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Originally posted by skmilind
    can creating tables in nologging mode will help ?
    Definitely yes, but it's a bit tricky.

    The target table must be set to nologging (ALTER TABLE NOLOGGING), it should have large extents (MB) and the worst thing: you must drop all indexes on it and then create them again (you can do that in nologging mode).

    NOLOGGING only works for direct-load inserts (INSERT /*+ APPEND */ INTO ... SELECT ...) and for SQL*Loader.

    I'd recommend to test nologging on small part of data for each table and watch redo ...

    If you need more help just ask me, I spent the whole sunday with similar task :-)). But in the end it generated 200K of redo for 2,2 mil. rows. Total time: 30 min.

    HTH,
    Ales


    [Edited by ales on 10-22-2001 at 09:32 AM]

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Originally posted by ales
    you must drop all indexes on it and then create them again
    Stupid me, of course you do NOT have to drop indexes on populated tables, but they are logged and I haven't found any way how to avoid that. So if you want to minimize redo size you should drop them and after loading data recreate them with the nologging clause.

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