Arun Sankar wrote:
alter table <Table_name> nologging; will reduse the redo operations, you can try this.
NOLOGGINGclause also specifies that subsequent direct loads using SQL*Loader and direct load
INSERToperations are not logged. Subsequent DML statements (
DELETE, and conventional path insert) are unaffected by the
NOLOGGINGattribute of the table and generate redo." (Emphasis mine)
two small additions:
- Tom Kyte provided a helpful matrix for logging operations on AskTom many years ago: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5280714813869
- maybe that's not too closely related to the actual question but: in 12.1 you can optimize the undo creation for temporary tables by setting the parameter TEMP_UNDO_ENABLED to true - http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn10326.htm#REFRN10326
regarding the redo generation for (and the use of) GTTs Tom Kyte writes - https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4817636200346963925:
the facts surrounding a global temporary table are:
a) they generate LESS redo - in most cases *significantly* less redo (no indexes) and if you can use a session based global temporary table - they can generate almost NO UNDO using insert /*+ append */
b) they do not require an expensive delete operation
c) they require less work on the part of the developer since they clean themselves out.
d) to use them with updates and deletes is what I would call "atypical" - not the normal use. most of the use is 1) insert into them, 2) query them, 3) commit and clear them out.
when you start updating and deleting - you start generating gobs of undo as that cannot be done using direct path and those two things generate the most undo possible. A delete has to record the entire row in the undo, and update has to record as much of the row that was modified.
So maybe a GTT could be useful - but I think the OP has not mentioned enough details for us to answer this question.
Marin has made very good points about update and delete effects on undo generation and on a normal table undo generation generates redo.
With the database set to noarchivelog mode then as all since the last backup are lost anyway you still want to remember that data in a GTT would be lost on shutdown even one taken to make a cold backup. Even if the data is not critical I doubt that the customer will want to discard it every time it is time to take a backup. Then you have the problem that GTT data is not intended to be shared between different sessions.
IMHO -- Mark D Powell --
How big is the table?
How often are log switches?
What hardware/OS/storage are you on?
How much concurrency is there with this dml?
Are the plans for the dml's appropriate?
What are the wait statistics?
Presumably these are all simple questions because they've already been examined. And yet, if you don't have undo how can you get read consistent dml or do a rollback statement? The questions sound like you are making some unwarranted assumptions about basic concepts. Using noarchivelog mode already gives you much more performance because you don't have to copy redo, redo helps you recover automatically from instance crashes.
We can reduce the size of redo generation, but can not make it zero.
We have seen that the following techniques can be used to reduce the amount of redo generated:
Eliminating redundant indexes
Reducing the number of columns updated
Eliminating redundant SELECT FOR UPDATE statements
Reducing the number of rows processed
Eliminating COMMIT statements
Increasing the batch size
Using Global Temporary Tables
Using External Tables