Solved

Export/Import and Alter Table Nologging, turn logging off

Posted on 2001-06-19
9
11,706 Views
Last Modified: 2010-08-05
If I alter table x nologging, and then import table data into table x will the import generate redo?

The Oracle8i doc says this is just for direct path loads, but Oracle Enterprise Mangager (in schema manager) says it applies to every db operation.

My goal is to improve the speed of a huge import.

Is there any way to turn logging off?
0
Comment
Question by:pbybee
9 Comments
 

Expert Comment

by:akg72
Comment Utility
It is not for all operations. In fact I have tested this stuff with one of the table. What I did was kept the table in nologging mode and start to insert records then did a recovery on that table. recovery was successful. So this is true that in case of insert nologging is effective only incase of serail and parallel direct load inserts.
0
 

Expert Comment

by:neval
Comment Utility
It is supposed to be just for this operation, importing this table. You should definately use direct path load and if the database is solely depend of these ipmorts and maybe just queries change the database to noarchivlog mod.

If possible sort the data that you import then import it.

Sincerely,
Neval Ozturk  
0
 

Author Comment

by:pbybee
Comment Utility
"It is supposed to be just for this operation, importing this table."  I don't understand, and import is exactly
what I'm trying to do, a huge import with over 3,500 tables
using SQL Loader and Sorting is not an option.

Also, if I change the data base mode to noarchivelog mode redo is still generated, it's just that redos are not archived.
0
 

Author Comment

by:pbybee
Comment Utility
"It is supposed to be just for this operation, importing this table."  I don't understand, and import is exactly
what I'm trying to do, a huge import with over 3,500 tables
using SQL Loader and Sorting is not an option.

Also, if I change the data base mode to noarchivelog mode redo is still generated, it's just that redos are not archived.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:pbybee
Comment Utility
"It is supposed to be just for this operation, importing this table."  I don't understand, and import is exactly
what I'm trying to do, a huge import with over 3,500 tables
using SQL Loader and Sorting is not an option.

Also, if I change the data base mode to noarchivelog mode redo is still generated, it's just that redos are not archived.
0
 
LVL 2

Expert Comment

by:ddandekar
Comment Utility
Hi,
Another option is change all concerned tablespace attributes to nologging instead of doing it individually for each of 3500 tables.
0
 

Accepted Solution

by:
davidgornshtein earned 75 total points
Comment Utility

Hi,
  Your imp will however generate redo, regardless
  of Logging/Nologging mode of your table,
  but if you will perform some DML operation with
  hint of /*+ APPEND /, Oracle will generate
  redo for space (TS) operations only, I mean redo
  on DML operations on sys.FET$ and sys.UET$.
 
  All other dml operations on this table such
  as imp and all DMLs without append hint
  will generate regular quantity of redo and
  will not perform faster.
 

  There is example for you:

 SQL> SELECT s.sid, substr(n.name,1,50) Name, s.value
  2  FROM v$mystat s, v$statname n
  3  WHERE n.statistic# = s.statistic#
  4  AND name LIKE '%redo%'
  5  ;

       SID NAME                         VALUE
---------- ------------------ ----------
       184 redo synch writes                0
       184 redo synch time                  0
       184 redo entries                     0
       184 redo size                        0
       184 redo buffer allocation retries   0
       184 redo wastage                     0
       184 redo writer latching time        0
       184 redo writes                      0

SQL> create table test1 as select * from dba_objects;

Table created.

SQL> SELECT s.sid, substr(n.name,1,50) Name, s.value
  2  FROM v$mystat s, v$statname n
  3  WHERE n.statistic# = s.statistic#
  4  AND name LIKE '%redo%'
  5  ;

       SID NAME                         VALUE
---------- ------------------ ----------
       184 redo synch writes                1
       184 redo synch time                  7
       184 redo entries                   533
       184 redo size                    79372

SQL> create table test2 as select * from dba_objects where rownum < 2;

Table created.

SQL> SELECT s.sid, substr(n.name,1,50) Name, s.value
  2  FROM v$mystat s, v$statname n
  3  WHERE n.statistic# = s.statistic#
  4  AND name LIKE '%redo%';

       SID NAME                         VALUE
---------- ------------------ ----------
       184 redo synch writes                2
       184 redo synch time                  9
       184 redo entries                   615
       184 redo size                    98888

SQL> insert into test2 select * from dba_objects;

24530 rows created.

SQL> commit;

Commit complete.

SQL> SELECT s.sid, substr(n.name,1,50) Name, s.value
  2  FROM v$mystat s, v$statname n
  3  WHERE n.statistic# = s.statistic#
  4  AND name LIKE '%redo%'
  5  ;

       SID NAME                         VALUE
---------- ------------------ ----------
       184 redo synch writes                3
       184 redo synch time                 17
       184 redo entries                  2908
       184 redo size                  3024048

SQL> alter table test2 nologging;

Table altered.

SQL>  insert into test2 select * from dba_objects;

24530 rows created.

SQL> commit;

Commit complete.

SQL> SELECT s.sid, substr(n.name,1,50) Name, s.value
  2  FROM v$mystat s, v$statname n
  3  WHERE n.statistic# = s.statistic#
  4  AND name LIKE '%redo%';

       SID NAME                         VALUE
---------- ------------------ ----------
       184 redo synch writes                5
       184 redo synch time                 18
       184 redo entries                  5172
       184 redo size                  5947940

SQL> insert /*+ APPEND */ into test2 select * from dba_objects;

24530 rows created.

SQL> commit;

Commit complete.

SQL> SELECT s.sid, substr(n.name,1,50) Name, s.value
  2  FROM v$mystat s, v$statname n
  3  WHERE n.statistic# = s.statistic#
  4  AND name LIKE '%redo%'
  5  ;

       SID NAME                         VALUE
---------- ------------------ ----------
       184 redo synch writes                6
       184 redo synch time                 19
       184 redo entries                  5628
       184 redo size                  6008416
       184 redo buffer allocation retries   3
 
  If you wish to check imp operation, use as in my example,
  but find your imp sid and then

SQL> SELECT s.sid, substr(n.name,1,50) Name, s.value
  2  FROM v$sesstat s, v$statname n
  3  WHERE n.statistic# = s.statistic#
  4  AND name LIKE '%redo%' and s.sid = your sid ...

Best regards.
0
 

Expert Comment

by:davidgornshtein
Comment Utility

 The only one addition to my answer:
  If you wish to perform faster import, you
  unfortunately have no way to turn off redo,
  but you can compile single task import
  (if you are using unix) by
  $ORACLE_HOME/lib> make -f ins_rdbms.mk singletask
  and then you can use "impst" instead of "imp"
  it will give you about 10% of performance improvement.
  use large buffer, it will increase performance as well.
  something like:  impst .... buffer=1000000

  You can however, import it to you test database,
  create dblink and then copy you table
  by
    insert /*+ APPEND */ into local_table
    select *
    from table@dblink
  then it work faster on prodution environment,
  if you have fast network.

Best Regards.

David Gornshtein
Oracle Certified Professional
Oracle & MS Sql Server senior DBA
davidg@extent.com
Extent Technologies Ltd.
972-3-7530635
 

 
0
 

Author Comment

by:pbybee
Comment Utility
Good answer.

I wish we could control the behavior of the Redo Logs
for huge conversions like this one.

Oh well...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now