Thanks for the question, Orna.

Asked: December 03, 2000 - 3:36 am UTC

Last updated: May 28, 2004 - 8:12 am UTC

Version: 8.1.5

Viewed 1000+ times

You Asked

Hi Tom,

After reading your answers on the subject I still have some unclear points I would appreciate if you can clarify :

1. Can a direct insert or parallel inserts be used in insert statements other then : INSERT AS SELECT ...?

2. As I understand the above methods do not log undo and redo information . if so - what is the advantage to further use the NOLOGGING option ?

3. Can you comment on the desired work flow when working with the above methods in production environment ? e.g . always perform a cald backup after using these insert methods .
Do you have standard recommandations ?

4. When tracing the CPU and elapsed times on DIRECT and PARALLEL inserts - I've noticed a considerable amount of unexplainable ( to me ) 'elapsed' time , while the 'CPU' time was very short , and the 'disk' was also negligable - can you comment on that ?

Thank you very much for your time
Orna

and Tom said...

1) no, you need to use insert /*+ append */ into t select .... to do a direct path insert. You would not ever want to do it for a "VALUES" statement anyway as each row would go above the HWM and never reuse existing free space (eg: each row would go into its own block). It does not use existing space. It is suitable only for adding "large" amounts of data to an existing table.

2) see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:485221567528 <code>

The advantage of nologging is that when performing the operations that can make use of it such as direct path loads, create table as select, create index and so on -- significantly less redo is generated for the operation, potentially speeding it up.

3) perform a backup, we don't care if it is hot or cold. If you do not backup and have a failure, you will have to redo the unrecoverable operation such as an index build yourself after recovering the rest of the data.

4) you were blocked waiting for something. Anytime the CPU is significantly smaller then the elapsed -- you were waiting for something during that time. Another user might have had the resource, you might have been waiting for IO, etc. You would use the v$ tables or utilities such as utle/bstat or statspack or Oracle Enterprise Manager (OEM) performance packs to diagnose what these wait events might have been.

Rating

(10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

does nologging really works..?

A reader, February 06, 2002 - 7:43 am UTC

Hi

I was trying to speed up a DWH loading process using direct insert and nologging mode however from stats I see no difference between logging and nologging


SQL> alter table t logging;
Table altered.

Elapsed: 00:00:00.05
SQL>  insert /*+ APPEND */ into t select * from bscs_own.bscs_sogsh nologging;
893681 rows created.

Elapsed: 00:03:23.23

Statistics
----------------------------------------------------------
        140  recursive calls
       1402  db block gets
      14108  consistent gets
      14092  physical reads
      42536  redo size
       1171  bytes sent via SQL*Net to client
        778  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
     893681  rows processed

SQL> insert /*+ APPEND */ into t select * from bscs_own.bscs_sogsh;

893681 rows created.

Elapsed: 00:02:39.63

Statistics
----------------------------------------------------------
          2  recursive calls
       1401  db block gets
      14094  consistent gets
      14091  physical reads
      42536  redo size
       1171  bytes sent via SQL*Net to client
        767  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
     893681  rows processed

----- here it´s faster without nologging clause I wonder why?-----


SQL> alter table t nologging;
Table altered.

Elapsed: 00:00:00.08

SQL> insert /*+ APPEND */ into t select * from bscs_own.bscs_sogsh;

893681 rows created.

Elapsed: 00:03:05.01

Statistics
----------------------------------------------------------
        140  recursive calls
       1402  db block gets
      14108  consistent gets
      14090  physical reads
      42536  redo size
       1171  bytes sent via SQL*Net to client
        767  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
     893681  rows processed

All insert generate same redo.... am I missing something here?

February 06, 2002 - 8:24 am UTC

I'll guess that you are in noarchivelog mode and you are on a system where other stuff is going on.

In noarchive log mode the append hint is all you need to bypass redo generation. In archive log mode, it makes a difference:

ops$tkyte@ORA8I.WORLD> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

ops$tkyte@ORA8I.WORLD> create table t as select * from all_objects where 1=0;
Table created.

ops$tkyte@ORA8I.WORLD> set autotrace on statistics

ops$tkyte@ORA8I.WORLD> alter table t logging;
Table altered.

ops$tkyte@ORA8I.WORLD> insert /*+ append */ into t select * from all_objects;
34755 rows created.

Statistics
----------------------------------------------------------
903 recursive calls
120 db block gets
472331 consistent gets
3392 physical reads
3871128 redo size
991 bytes sent via SQL*Net to client
827 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
17 sorts (memory)
0 sorts (disk)
34755 rows processed

ops$tkyte@ORA8I.WORLD> commit;
Commit complete.

ops$tkyte@ORA8I.WORLD> ops$tkyte@ORA8I.WORLD> alter table t nologging;
Table altered.

ops$tkyte@ORA8I.WORLD> insert /*+ append */ into t select * from all_objects;
34755 rows created.

Statistics
----------------------------------------------------------
335 recursive calls
123 db block gets
472242 consistent gets
1704 physical reads
10504 redo size
994 bytes sent via SQL*Net to client
827 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
34755 rows processed

<b>so, it makes quite a bit of difference. as for the difference in response times:

3:23
2:39
3:05

I'll write that off to "there was other stuff being done on the system at the same time". You had to wait for IO (lots in the first run, least in the second and a moderate amount in the third). The system was busy servicing other people.

Use sql_trace and tkprof to prove this -- if you see a disparity between cpu and elapsed -- you are seeing "waits" for something. If you have my book -- see chapter 10 and it'll show you how to see what these waits were (in your case, I would guess IO)</b>

Do you need alter session enable parallel dml with append hint

Rob, April 22, 2002 - 8:50 pm UTC

Tom

In order to get parallel writes with an
insert select stmt. is it necessary to use both the
append hint and alter session enable parallel dml.
Will either of those two cause a direct path insert
w/o the other?

April 22, 2002 - 9:18 pm UTC

read:

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c21dlins.htm#2507 <code>

the append hint is optional.

New Extend

kiro, April 23, 2002 - 4:23 am UTC

Hi Tom
Please see example below.

REM tablespace user1 local managed uniform extend 20k
REM Oracle version 9.0.1 on Win2000

SQL> drop table test_dir;

Table dropped.

SQL> create table test_dir
  2   (id number,
  3   status varchar2(20),
  4   cd_text varchar2(20))
  5  tablespace user1
  6  storage(pctincrease 0);

Table created.

SQL> 
SQL> drop table test_dir1;
drop table test_dir1
           *
ERROR at line 1:
ORA-00942: table or view does not exist 


SQL> create table test_dir1
  2   (id number,
  3   status varchar2(20),
  4   cd_text varchar2(20))
  5  tablespace user1
  6  storage(pctincrease 0);

Table created.

SQL> declare
  2  i number;
  3  begin
  4  for i in 1..1000 loop
  5  insert into test_dir1 values(i,'status','text');
  6  end loop;
  7  end;
  8  
  9  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> 
SQL> set pagesize 100;
SQL> set linesize 100;
SQL> set serverout on;
SQL> declare
  2  Cursor RunCur is
  3  select TABLE_NAME from all_tables
  4   where OWNER ='DIRECT_LOAD'
  5   and PCT_FREE IS NOT NULL;
  6  
  7  TableToExplore VARCHAR2(30);
  8  begin
  9   open RunCur ;
 10   LOOP
 11    FETCH RunCur INTO TableToExplore ;
 12    EXIT WHEN RunCur%NOTFOUND ;
 13    show_space_my(Upper(TableToExplore),'DIRECT_LOAD','TABLE');
 14  dbms_output.put_line('*************************************** END OF THIS OBJECT *****************************');
 15   END LOOP;
 16  end;
 17  /
Object Name *********** TEST_DIR                                                    
Object Type *********** TABLE                                                       
Free Blocks.............................0                        
Total Blocks............................5                         
% Free in Segment.......................0                             
Total Space In MB........................02                                
Unused Blocks...........................4                          
Unused Space In Mb.......................02                                 
Last Used Ext FileId....................7                                 
Last Used Ext BlockId...................17                                
Last Used Block.........................1                             
Partition Name..........................NO PARTITIONS                                               
*************************************** END OF THIS OBJECT 
Object Name *********** TEST_DIR1                                                   
Object Type *********** TABLE                                                       
Free Blocks.............................4                        
Total Blocks............................10                        
% Free in Segment.......................40                            
Total Space In MB........................04                                
Unused Blocks...........................0                          
Unused Space In Mb......................0                                   
Last Used Ext FileId....................7                                 
Last Used Ext BlockId...................27                                
Last Used Block.........................5                             
Partition Name..........................NO PARTITIONS                                               
*************************************** END OF THIS OBJECT 

PL/SQL procedure successfully completed.

SQL> execute  TunePack.SegmetTableInfo ;

OBJECT NAME-------------------TEST_DIR
OBJECT TYPE-------------------TABLE
Number Of Extents-------------1
Next Size Of Extent In MB-----.02
Max Extent--------------------2147483645
PCT Increase In %-------------0
Size In MB--------------------.02
 
************************** END OF THIS OBJECTS  
OBJECT NAME-------------------TEST_DIR1
OBJECT TYPE-------------------TABLE
Number Of Extents-------------2
Next Size Of Extent In MB-----.02
Max Extent--------------------2147483645
PCT Increase In %-------------0
Size In MB--------------------.04
 
************************** END OF THIS OBJECTS

PL/SQL procedure successfully completed.

SQL> execute  TunePack.ExtentTableInfo ;

OBJECT NAME-------------------TEST_DIR
DB File Name Belongs to-------C:\BABADB\DATAFILE\USER1.ORA
Extend Id No------------------0
Size In MB--------------------.02
 ************************** END OF THIS OBJECTS 
 
OBJECT NAME-------------------TEST_DIR1
DB File Name Belongs to-------C:\BABADB\DATAFILE\USER1.ORA
Extend Id No------------------0
Size In MB--------------------.02
OBJECT NAME-------------------TEST_DIR1
DB File Name Belongs to-------C:\BABADB\DATAFILE\USER1.ORA
Extend Id No------------------1
Size In MB--------------------.02
 
************************** END OF THIS OBJECTS 
PL/SQL procedure successfully completed.

------------
I try to Use  DIRECT PATH Insert
AS you said totally new extend will be taked DESPITE of free or unused extends in table, but I receive other kind
of result. Am i wrong in testing?
-----------

SQL> insert /*+ APPEND */ into test_dir select * from test_dir1 where id between 1 and 4;

4 rows created.

SQL> commit;

Commit complete.

SQL> declare
  2  Cursor RunCur is
  3  select TABLE_NAME from all_tables
  4   where OWNER ='DIRECT_LOAD'
  5   and PCT_FREE IS NOT NULL;
  6  
  7  TableToExplore VARCHAR2(30);
  8  begin
  9   open RunCur ;
 10   LOOP
 11    FETCH RunCur INTO TableToExplore ;
 12    EXIT WHEN RunCur%NOTFOUND ;
 13    show_space_my(Upper(TableToExplore),'DIRECT_LOAD','TABLE');
 14  dbms_output.put_line('*************************************** END OF THIS OBJECT *****************************');
 15   END LOOP;
 16  end;
 17  /
Object Name *********** TEST_DIR                                                    
Object Type *********** TABLE                                                       
Free Blocks.............................0                        
Total Blocks............................5                         
% Free in Segment.......................0                             
Total Space In MB........................02                                
Unused Blocks...........................3                          
Unused Space In Mb.......................01                                 
Last Used Ext FileId....................7                                 
Last Used Ext BlockId...................17                                
Last Used Block.........................2                             
Partition Name..........................NO PARTITIONS                                               
*************************************** END OF THIS OBJECT 
Object Name *********** TEST_DIR1                                                   
Object Type *********** TABLE                                                       
Free Blocks.............................4                        
Total Blocks............................10                        
% Free in Segment.......................40                            
Total Space In MB........................04                                
Unused Blocks...........................0                          
Unused Space In Mb......................0                                   
Last Used Ext FileId....................7                                 
Last Used Ext BlockId...................27                                
Last Used Block.........................5                             
Partition Name..........................NO PARTITIONS                                               
*************************************** END OF THIS OBJECT 

PL/SQL procedure successfully completed.

SQL> execute  TunePack.SegmetTableInfo ;
OBJECT NAME-------------------TEST_DIR
OBJECT TYPE-------------------TABLE
Number Of Extents-------------1
Next Size Of Extent In MB-----.02
Max Extent--------------------2147483645
PCT Increase In %-------------0
Size In MB--------------------.02
 
************************** END OF THIS OBJECTS *************************
 
OBJECT NAME-------------------TEST_DIR1
OBJECT TYPE-------------------TABLE
Number Of Extents-------------2
Next Size Of Extent In MB-----.02
Max Extent--------------------2147483645
PCT Increase In %-------------0
Size In MB--------------------.04
 
************************** END OF THIS OBJECTS *************************
 

PL/SQL procedure successfully completed.

SQL> execute  TunePack.ExtentTableInfo ;
OBJECT NAME-------------------TEST_DIR
DB File Name Belongs to-------C:\BABADB\DATAFILE\USER1.ORA
Extend Id No------------------0
Size In MB--------------------.02
 
************************** END OF THIS OBJECTS *************************
 
OBJECT NAME-------------------TEST_DIR1
DB File Name Belongs to-------C:\BABADB\DATAFILE\USER1.ORA
Extend Id No------------------0
Size In MB--------------------.02
OBJECT NAME-------------------TEST_DIR1
DB File Name Belongs to-------C:\BABADB\DATAFILE\USER1.ORA
Extend Id No------------------1
Size In MB--------------------.02
 
************************** END OF THIS OBJECTS *************************
PL/SQL procedure successfully completed.

SQL> spool off;

And we can see there is no new extend aallocated in table test_dir for direct path insert!
Am I wrong ? Please explain?

April 23, 2002 - 7:35 am UTC

the append hint writes above the HWM. sorry for the miscommunication above. It takes space above the HWM for the table, not necessarily a new extent.

I've corrected that.

I have a trouble

Adrian Usuy, September 30, 2002 - 12:24 pm UTC

Hi Tom, i was try to make the insert append like this:

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
SQL> insert /*+ append */ into dwbs_con_saldos_tmp
  2  select * from dwbs_con_saldos;
insert /*+ append */ into dwbs_con_saldos_tmp
                          *
ERROR at line 1:
ORA-00600: internal error code, arguments: [15201], [], [], [], [], [], [], []
SQL>

is some way to fix that?

Thanks

October 01, 2002 - 9:19 am UTC

ORA-00600 = contact support, all internal errors mean that.

Why slow?

A reader, January 24, 2003 - 4:32 pm UTC

Hi, TOm

I basically doing:
insert /*+ APPEND */ INTO t(employno, ename, sal, comm, deptno)
SELECT a.employno, a.ename, a.sal, a.comm, b.deptno
from emp a, dept b
where a.employno = b.employno

The SELECT stmt has more than 864518 records, the speed
of this insert transaction is VERY slow(already more than 1 hour, still running), could you tell me why? is it normal?
My DB is not in archive mode, so i don't think NOLOGGING
is an issue here.

THANKS

January 24, 2003 - 6:02 pm UTC

how many indexes are we talking about.

and if you just

set autotrace traceonly statistics
select.......

how long does that go

SELECT

A reader, January 26, 2003 - 10:43 pm UTC

Hi, TOm,
Thanks for your fast response.
Actually, the SELECT stmt itself is very fast.
But the insert /*+ APPEND */ ....... stmt, takes a long
long time, more than half day.

THanks

January 27, 2003 - 7:29 am UTC

a) did you consider comparing the plans to see if they are the same. insert /*+ append */ optimizes for "throughput" a select might be optimizing for first rows. if the plans are different, try hinting the select in the insert with first_rows and see if they become the same and try again

b) again -- how many indexes are we talking about there. how many rows are you inserting.

A reader, May 21, 2003 - 1:38 pm UTC

Hi Tom,

In our environment, I was able to consistently run INSERT without /*+ APPEND */ faster than the similar INSERT with the hint. Please see the example below. What could be the reason for this?

thanks

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> CREATE TABLE t (
  2  c1 int,
  3  c2 int
  4  );

Table created.

SQL> 
SQL> CREATE TABLE t2 AS
  2  SELECT * FROM t
  3  WHERE  1 = 0;

Table created.

SQL> 
SQL> 
SQL> BEGIN
  2     FOR i IN 1..100000 LOOP
  3        INSERT INTO t VALUES(i, i);
  4     END LOOP;
  5  
  6     COMMIT;
  7  
  8     FOR i IN 1..5 LOOP
  9        INSERT INTO t
 10        SELECT * FROM t;
 11     END LOOP;
 12  
 13     COMMIT;
 14  END;
 15  /

PL/SQL procedure successfully completed.

SQL> 
SQL> SELECT COUNT(*) FROM t;

  COUNT(*)
----------
   3200000

SQL> 
SQL> 
SQL> set timing on
SQL> 
SQL> INSERT INTO t2 SELECT * FROM t;

3200000 rows created.

Elapsed: 00:00:42.12
SQL> 
SQL> ROLLBACK;

Rollback complete.

Elapsed: 00:00:08.82
SQL> 
SQL> INSERT /*+ APPEND */
  2  INTO t2 SELECT * FROM t;

3200000 rows created.

Elapsed: 00:00:46.96
SQL> 
SQL> ROLLBACK;

Rollback complete.

Elapsed: 00:00:00.10
SQL>

May 21, 2003 - 3:07 pm UTC

I see no real difference here.  in the grand scheme of things 42 and 46 aren't different to me, not wall clock timewise...

look at the amount of redo generated, look at the work performed.  it is a 2 small column table.  Not much of anything happening there.

try a real example, use the tool to speed up something "big" and "slow".


in any case, not reproducing by me here:

ops$tkyte@ORA920> create table t ( c1 int, c2 int );

Table created.

ops$tkyte@ORA920> create table t2 as select * from t;

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> BEGIN
  2     FOR i IN 1..100000 LOOP
  3        INSERT INTO t VALUES(i, i);
  4     END LOOP;
  5
  6     COMMIT;
  7
  8     FOR i IN 1..5 LOOP
  9        INSERT INTO t
 10        SELECT * FROM t;
 11     END LOOP;
 12
 13     COMMIT;
 14  END;
 15  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> set timing on
ops$tkyte@ORA920> insert into t2 select * from t;

3200000 rows created.

Elapsed: 00:00:32.81
ops$tkyte@ORA920> commit;

Commit complete.

Elapsed: 00:00:00.08
ops$tkyte@ORA920> truncate table t2;

Table truncated.

Elapsed: 00:00:03.50
ops$tkyte@ORA920> insert /*+ APPEND */ into t2 select * from t;

3200000 rows created.

Elapsed: 00:00:05.43
ops$tkyte@ORA920> commit;

Commit complete.


<b>and again</b>

ops$tkyte@ORA920> drop table t2;

Table dropped.

Elapsed: 00:00:00.29
ops$tkyte@ORA920> create table t2 as select * from t where 1=0;

Table created.

Elapsed: 00:00:00.09
ops$tkyte@ORA920>
ops$tkyte@ORA920> set timing on
ops$tkyte@ORA920> insert into t2 select * from t;

3200000 rows created.

Elapsed: 00:00:14.27
ops$tkyte@ORA920> rollback;

Rollback complete.

Elapsed: 00:00:18.07
ops$tkyte@ORA920> insert /*+ APPEND */ into t2 select * from t;

3200000 rows created.

Elapsed: 00:00:08.57
ops$tkyte@ORA920> rollback;

Rollback complete.
Elapsed: 00:00:00.02

<b>notice already the wide flucuations in times..  and again....</b>


ops$tkyte@ORA920> set timing on
ops$tkyte@ORA920> insert into t2 select * from t;

3200000 rows created.

Elapsed: 00:00:15.57
ops$tkyte@ORA920> rollback;

Rollback complete.

Elapsed: 00:00:19.89
ops$tkyte@ORA920> insert /*+ APPEND */ into t2 select * from t;

3200000 rows created.

Elapsed: 00:00:08.09
ops$tkyte@ORA920> rollback;

Rollback complete.

Elapsed: 00:00:00.06
ops$tkyte@ORA920>

how to verify....

Neeti, July 09, 2003 - 3:10 pm UTC

insert /*+ APPEND PARALLEL (a,4) */
into a
nologging
select /*+ PARALLEL (b,4) */
from b

How to verify that inserts are running in parallel,
I se only select running in parallel...?

Thanks,
Neeti

July 09, 2003 - 7:21 pm UTC

well, first, that nologging is not_meaningful -- not anymore then:

ops$tkyte@ORA920> insert /*+ APPEND PARALLEL (a,4) */
  2  into a<b>
  3  I_beg_of_you_to_do_nologging</b>
  4  select /*+ PARALLEL (b,4) */ *
  5  from b;

1 row created.



would be.  nologging is just an alias for A there.  You want to disable logging on table A via the alter table command.

see

http://docs.oracle.com/docs/cd/B10501_01/server.920/a96524/c21dlins.htm#10629

for the exact requirements of a parallel direct path insert.

Why Slow?

Janet A, January 20, 2004 - 4:03 pm UTC

I had the same problem, my SELECT runs for 10 minutes and the INSERT takes hours and still does not insert anything. I have Append and Parallel hints already. When I used first_rows, it inserted in 15 minutes! Thanks!

Parellel Direct Load

Naina, May 28, 2004 - 2:15 am UTC

The book says, an table that's modified using parellel direct insert cannot be queried or modified again within the same transaction.
I could not understand why it is so ?

May 28, 2004 - 8:12 am UTC

because you bypass UNDO generation, you write directly to the table. Our consistent read mechanism cannot "work" in such an environment.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

does nologging really works..?

Do you need alter session enable parallel dml with append hint

New Extend

I have a trouble

Why slow?

SELECT

how to verify....

Why Slow?

Parellel Direct Load

More to Explore

Hints

Direct insert and parallel insert

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

does nologging really works..?

Do you need alter session enable parallel dml with append hint

New Extend

I have a trouble

Why slow?

SELECT

how to verify....

Why Slow?

Parellel Direct Load

More to Explore

Hints