This discussion is archived
4 Replies Latest reply: Feb 13, 2013 4:02 PM by rp0428 RSS

Should we use LOGGING or NOLOGGING for table, lob segment, and indexes?

liaison1 Newbie
Currently Being Moderated
We have some DML performance issue on cf contention over the tables that also include LOB segments. In this case, should we define LOGGING on tables, lob segments, and/or INDEXES?

Based on the metalink note < Performance Degradation as a Result of 'enq: CF - contention' [ID 1072417.1]> It looks we need to turn on logging for at least table and lob segment. What about the indexes?

Thanks!
  • 1. Re: Should we use LOGGING or NOLOGGING for table, lob segment, and indexes?
    rp0428 Guru
    Currently Being Moderated
    >
    We have some DML performance issue on cf contention over the tables that also include LOB segments. In this case, should we define LOGGING on tables, lob segments, and/or INDEXES?

    Based on the metalink note < Performance Degradation as a Result of 'enq: CF - contention' [ID 1072417.1]> It looks we need to turn on logging for at least table and lob segment. What about the indexes?
    >
    What about them? What about the tables?

    Since LOGGING is the default you don't need to 'define LOGGING on tables, lob segments, and/or INDEXES' or 'turn on logging' unless someone has turned it off for some reason.

    You should talk to that 'someone' and find out why they turned it off to begin with.

    NOLOGGING and most other modifiable settings should only be changed from their default values if you have some reason for doing so. Without knowing WHY you have changed this LOGGING setting to NOLOGGING we have no way of knowing whether you should change it back or not.

    So lets start from the beginning. You have an Oracle database. It uses LOGGING by default. Someone in your organization chose to change that setting to NOLOGGING for some 'unspecified' set of objects in your database.

    Determine why that was done. If you can't find a valid reason then change it back to the default of LOGGING.

    Post any information about why you are using the settings you are currently using. Only then can we try to help with any issue or problem you are having.
  • 2. Re: Should we use LOGGING or NOLOGGING for table, lob segment, and indexes?
    ursusca Explorer
    Currently Being Moderated
    How did you find out about DML performance issue? Could you please post some more information? AWR, wait events, etc.
  • 3. Re: Should we use LOGGING or NOLOGGING for table, lob segment, and indexes?
    liaison1 Newbie
    Currently Being Moderated
    These tables that have nologging are likely from the application team. Yes, we need to turn on the logging from nologging for tables and lob segments. What about the indexes?

    Thanks,
  • 4. Re: Should we use LOGGING or NOLOGGING for table, lob segment, and indexes?
    rp0428 Guru
    Currently Being Moderated
    >
    These tables that have nologging are likely from the application team. Yes, we need to turn on the logging from nologging for tables and lob segments. What about the indexes?
    >
    Indexes only get modified when the underlying table is modified. When you need recovery you don't want to do things that can interfere with Oracle's ability to perform its normal recovery. For indexes there will never be loss of data that can't be recovered by rebuilding the index.

    But use of NOLOGGING means that NO RECOVERY is possible. For production objects you should ALWAYS use LOGGING. And even for those use cases where use of NOLOGGING is appropriate for a table (loading a large amount of data into a staging table) the indexes are typically dropped (or at least disabled) before the load and then rebuilt afterward. When they are rebuilt NOLOGGING is used during the rebuild. Normal index operations will be logged anyway so for these 'offline' staging tables the setting for the indexes doesn't really matter. Still, as a rule of thumb you only use NOLOGGING during the specific load (for a table) or rebuild (for an index) and then you would ALTER the setting to LOGGING again.

    This is from Tom Kyte in his AskTom blog from over 10 years ago and it still applies today.
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5280714813869
    >
    NO NO NO -- it does not make sense to leave objects in NOLOGGING mode in a production
    instance!!!! it should be used CAREFULLY, and only in close coordination with the guys
    responsible for doing backups -- every non-logged operation performed makes media
    recovery for that segment IMPOSSIBLE until you back it up.
    >
    Use of NOLOGGING is a special-case operation. It is mainly used in Datawarehouse (OLAP systems) data processing during truncate-and-load operations on staging tables. Those are background or even offline operations and the tables are NOT accessible by end users; they are work tables used to prepare the data that will be merged to the production tables.

    1. TRUNCATE a table
    2. load the table with data
    3. process the data in the table

    In those operations the table load is seldom backed up and rarely needs recovery. So use of NOLOGGING enhances the performance of the data load and the data can be recovered, if necessary, from the source it was loaded from to begin with.

    Use of NOLOGGING is rarely, if ever, used for OLTP systems since that data needs to be recovered.

Legend

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