官术网_书友最值得收藏!

Chapter 2. NOLOGGING Operations

You could be asking yourself why I want to talk about NOLOGGING operations in a backup and recovery book. The answer is simple: NOLOGGING operations will affect the recoverability of a database and due to this, it is very important for a DBA to understand this concept. It is the most visited topic on my blog www.oraclenz.org.

Many DBAs and developers use NOLOGGING operations when doing bulk inserts and massive deletion of data to reduce redo generation but many do not know that these statements will always generate redo, in other words, UPDATE or DELETE will always be logged. Later in this chapter, we will see some techniques that will reduce redo generation for massive updates and deletes.

In this chapter, we will cover the following topics:

  • LOGGING versus NOLOGGING
  • Disabling redo generation
  • NOLOGGING operations
  • How to reduce redo generation
  • Redo log wait events
  • Practice with some interesting scripts

NOLOGGING operations do not generate redo records in the redo log files (only a notification that a NOLOGGING operation was made is registered, but not the changes). Consequently, such operations are a very helpful option to reduce the amount of redo to be generated in a transaction, which might make the transaction run faster and also reduce any unnecessary stress on the database.

You need to understand that NOLOGGING operations are direct path—they bypass the buffer cache. If you direct path load say 100 MB of data and that all fits in the buffer cache—a conventional path load might be much faster than a non-logged direct path load (you don't want the blocks to be written to disk and the redo streamed to disk in the background by the LGWR process).

On the other hand, if you are loading gigabytes of data, more than what can be buffered in the cache, then you might benefit from direct path writes since you'd be waiting for the DBWR process to empty the cache.

However, a problem arises due to misconceptions in the use of these operations. In particular, many people forget that NOLOGGING operations will affect the recoverability of the database.

Also it's incredible how many questions I receive regarding this topic when speaking at conferences or when connected in forums and technical chats. Some of the main questions I hear all the time are:

  • Does creating a table with the NOLOGGING option mean there is no generation of redo ever, or just that the initial creation operation has no redo generation, but does that DML down the road generate redo?
  • How and when can the NOLOGGING option be employed?

All these questions and many more will be answered as this chapter develops.

You need to remember that redo generation is a crucial part of the Oracle recovery mechanism. NOLOGGING operations only affect the recovery from a media failure perspective (due that you then will need to recover from a backup and apply all the available archive logs in the recover process), but will not affect a database in case of an instance failure. On the other hand, excessive generation of redo is the result of an excessive workload of update, insert, and DML operations in the database.

Tip

A very important rule with respect to data is to never put yourself into an unrecoverable situation. The importance of this guideline cannot be stressed enough, but it does not mean that you can never use time saving or performance enhancing options.

主站蜘蛛池模板: 长沙市| 梁平县| 漳浦县| 浦北县| 济宁市| 依兰县| 梁河县| 望江县| 呼伦贝尔市| 龙江县| 兴仁县| 淮南市| 合作市| 鄱阳县| 黑龙江省| 循化| 渝中区| 钦州市| 宁夏| 方城县| 朝阳市| 类乌齐县| 海伦市| 寿宁县| 温宿县| 贵港市| 仁布县| 斗六市| 呼伦贝尔市| 黑河市| 铜山县| 南阳市| 西峡县| 灵宝市| 邯郸市| 新疆| 亚东县| 礼泉县| 万源市| 周至县| 攀枝花市|