Solved

Export/Import and Alter Table Nologging, turn logging off

Posted on 2001-06-19
9
11,839 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 

Expert Comment

by:akg72
ID: 6207683
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
ID: 6207806
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
ID: 6207924
"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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:pbybee
ID: 6207925
"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
ID: 6207926
"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
ID: 6209252
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
ID: 6210234

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
ID: 6210293

 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
ID: 6210298
Good answer.

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

Oh well...
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

635 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