updates causing loads of redo?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: updates causing loads of redo?

  1. #1
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258

    updates causing loads of redo?

    We have an issue where the log switches every 4 minutes on a 'test' database, i.e. not doing much, just a few tests. I have checked the log miner and discovered that an unsupported procedure was doing 41,000+ updates every 4 minutes:
    SQL> @logmnr_results

    OPERATION SEG_OWNER SEG_NAME COUNT(*)
    ---------------------------------------- -------------------- UNSUPPORTED LIMARES ALLOCATION_HOLD_MAP 41024
    UNSUPPORTED SYS SEQ$ 2
    On checking the procedure ALLOCATION_HOLD_MAP it updates all 864 columns table to null every 5 seconds. Apparently this needs to be done this way, so we can ensure the data is refreshed for new data! Anyway, I am assuming that these 41000+ entries in the log file are related to these updates (updating 864 columns in the table, every 5 seconds within approx. 4 minutes. Here is the equation (60/5*4=45, 48*864=41,472).
    My question is, why is this update have 41000+ entries in the log file? Does each column updated represent one entry? Rgds. Sheryl

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Mnnnhhh... 864 columns table? do you mean 864 rows table perhaps?
    Well... a redo image is needed for each update, isn't it? That's the way redo works.
    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.

  3. #3
    Join Date
    Apr 2001
    Location
    Congleton
    Posts
    258
    No, its one update sql statement that updates 864 columns to NULL for 1 row, e.g.

    UPDATE LIMARES.ALLOCATION_HOLD_MAP
    SET
    T_1_0000 =NULL,
    T_1_0005 =NULL,
    T_1_0010 =NULL, etc etc.(864 columns later.....)
    WHERE RES_ID= :B1
    commit;
    This is why I am asking the question, I thought it would just have 1 entry in the redo log as it is 1 transation.
    Rgds. Sheryl

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