any relation between nologging and direstload /*+append*/
P, May 30, 2002 - 4:11 pm UTC
Tom,
here is the responce I got from one of the DBA's.
------------
Yes, I had noticed that you were blowing up the rollback segment. Unfortunately, you are laboring under a number of misconceptions as to how Oracle actually works. No logging is unnecessary because this database is not in archivelog mode. Technically you get a small performance benefit by not running the changes through the online redo logs. However, you are not havening performance problems. The append hint does NOT perform direct path loads. Direct path is only available thought SQL* Loader. Append prevents the database from checking the free space list when inserting new rows. This would provide a performance benefit if you were loading into a table that you had previously deleted rows from. If you are starting with an empty table it buys you nothing.
---------------------
My question is
1)Is it true that if database is not in archivelog mode then one cannot use NOLOGGING and /*+append*/ hint to minimise redo and undo log generation?
2) is the last statement of above reply is correct?
"If you are starting with an empty table it buys you nothing."
that is /*+append*/ will not work if you start with an empty table
May 30, 2002 - 4:55 pm UTC
sigh, time for a new dba.
<b>They are the ones laboring under the misconception that they know how Oracle actually works.</b>
Setting the nologging option in this case (noarchivelog) is not necessary they are correct. The insert /*+ append */ won't generate log for the table modifications. In archivelog mode, the table would (should) be in nologging mode.
So, in a noarchive log mode database, you don't need to set nologging on the table to bypass redo generation with insert /*+ append */. This is easy to see and demonstrate:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects where 1=0;
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace on statistics
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects;
23014 rows created.
Statistics
----------------------------------------------------------
...
5932 redo size
...
23014 rows processed
ops$tkyte@ORA817DEV.US.ORACLE.COM> rollback;
Rollback complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t select * from all_objects;
23014 rows created.
Statistics
----------------------------------------------------------
...
2575040 redo size
...
23014 rows processed
ops$tkyte@ORA817DEV.US.ORACLE.COM> rollback;
Rollback complete.
<b>I don't know about you, but if I don't have to wait for 2.5meg of redo to be a) generated and b) written (yes, you have to wait for it to write) -- I don't want to.
Consider the savings on a really big load.
"small performance benefit" -- i think not. Ask them "ok, so what about when the logs switch and we start checkpointing? and when the logs wrap and we have to wait for the checkpoints to complete? Can't I avoid that by bypassing redo as well as getting the TREMENDOUS savings from not writing it in the first place???"</b>
As for the comment:
"The append hint does NOT perform direct path loads. Direct path is only available thought SQL* Loader."
Please give them the link above -- (to the server concepts manual, which they OBVIOUSLY need to read)
<quote>
Introduction to Direct-Load INSERT
Direct-load INSERT enhances performance during insert operations <b>by formatting and writing data directly into Oracle datafiles, without using the buffer cache. This functionality is similar to that of the Direct Loader utility (SQL*Loader). </b>
</quote>
Hmmmm.......
The comment about an empty table vs a previously loaded table is a bunch of HOOEY as well. <b>total and utter hooey</b>. They no not what they speak of.
1) FALSE. Test case above was performed in a noarchive log mode database. Serious savings - SERIOUS (and looky at that - the table was *empty*)
2) FALSE.
<b>just read the contents of the link, have them read it. It is very clearly documented -- just waiting to be read!</b>
Ram Gopal Das, May 30, 2002 - 5:25 pm UTC
Tom, Please clarify this once again for me. An empty table has the HWM at 0 blocks. So whether we use /*+APPEND*/ or not the insert will start using the blocks above HWM. So what you are saying is that, in this case (ie of an empty table) the fuction of the append hint would be only to invoke the Direct Mode insert (ie bypassibg the DB buffer)? Right? If yes then I think Oracle Documentation has not clearly explained it - what I mean is that the doc gives an impression that /*+APPEND*/ means using blocks above HWM. So I think many people tend to believe that it is unnecessary to use append hint on an empty table.
This is a very subtle distiction. Thanks for pointing out.
Rgds,
Ram
May 30, 2002 - 8:24 pm UTC
The advantage is:
a) no redo need be generated
b) write directly to blocks, bypass buffer cache
c) no freelist inspection for each and every block allocated (and you'll be doing lots of those)
It is very useful to use APPEND on any table when you are bulk loading into it LOTS OF ROWS. It's faster, it's cheaper, it's better -- just make sure that you clear it with the smart DBA so they can schedule a backup (when done unrecoverable -- nologging -- it needs to be backed up shortly thereafter)
noarchivelog and /*+append*/
P, May 30, 2002 - 6:50 pm UTC
tom,
you said
"Setting the nologging option in this case (noarchivelog) is not necessary they
are correct. The insert /*+ append */ won't generate log for the table modifications. "
could you please explain what is the technical reason it doesnot generate redo/undo log when one uses /*+append*/ in noarchivelog mode. link would be just fine
May 30, 2002 - 8:26 pm UTC
The technical reason is that is writes directly to the database files -- no logging needed, we bypass the buffer cache -- we don't need the log for instance recovery. Since you cannot do MEDIA recovery in noarchive log mode, we don't need the log for that either.
Hence, in noarchivelog mode, we don't need the log.
In archivelog mode, we assume you want to be able to do media recovery. Hence, by default, we log the changes so that you can. If you specify NOLOGGING, we won't.
That is why.
A reader, May 30, 2002 - 10:50 pm UTC
What did the DBA say?
AB, May 31, 2002 - 3:46 am UTC
P from LA
---------
I hope you showed this to your DBA - I for one would be very interested to know how he responded to the public trashing of his so-called knowledge... :)
AB
A reader, May 31, 2002 - 1:15 pm UTC
"It is very useful to use APPEND on any table when you are bulk loading into it LOTS OF ROWS. It's faster, it's cheaper, it's better "
--Is there a way I can use APPEND in exp/imp? I know we can use DIRECT mode during exports. Is there anything equivalent for imports? If not why? Thanks.
May 31, 2002 - 1:45 pm UTC
No, there is not. The reason -- import does single row array inserts and INSERT /*+ APPEND */ works only on inserts with SELECTS
insert ... values -- no append.
insert ... select -- append is possible.
snap shot too old error
P, May 31, 2002 - 2:23 pm UTC
tom,
here is the PL/SQL job I am using. I know I can do that using /*+APPEND*/ but I just want to know the reason for this error
I am commiting after 100 rows event I tried commit after 10 rows, still it gives snapshot too old error
---------
SQL> ed
Wrote file afiedt.buf
1 DECLARE
2 n_rowcount NUMBER(10) ;
3 dt_load_date date;
4 CURSOR c1 IS
5 SELECT
6 A.LAST_UPDATE col1,
7 B.mel_seq col2,
8 A.CM_ID col3,
9 C.mel_seq col4,
10 A.LK_ID col5
11 FROM
12 ECDB.ECDB_SETROUTE_LINKS A,
13 NCDBMEL.MASTER_EQUIPMENT_LIST B,
14 NCDBMEL.MASTER_EQUIPMENT_LIST C
15 where
16 a.cm_id = b.equipment_id and
17 a.lk_id = c.equipment_id
18 ;
19 BEGIN
20 dt_load_date := SYSDATE;
21 n_rowcount :=0;
22 FOR c1_rec IN c1 LOOP
23 INSERT INTO NCDBMEL.EROUTE_LINKS
24 (
25 EROUTE_LINKS_SEQ,
26 CREATE_USER,
27 CREATE_DATE,
28 MODIFIED_USER,
29 MODIFIED_DATE,
30 MEL_SEQ,
31 EQUIPMENT_ID,
32 LINK_MEL_SEQ,
33 LINK_EQUIPMENT_ID
34 )
35 VALUES
36 (NCDBMEL.MAIN_SEQ.NEXTVAL,
37 'LOAD DATA',
38 dt_load_date ,
39 null,
40 c1_rec.col1,
41 c1_rec.col2,
42 c1_rec.col3,
43 c1_rec.col4,
44 c1_rec.col5
45 ) ;
46 n_rowcount := n_rowcount + 1;
47 IF n_rowcount =100 THEN
48 COMMIT WORK;
49 n_rowcount :=0;
50 END IF;
51 END LOOP;
52 COMMIT WORK;
53* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 4 with name "RB3" too
small
ORA-06512: at line 22
SQL>
May 31, 2002 - 3:03 pm UTC
it is DUE TO THE COMMIT.
get rid of the commit and see what happens.
THIS SHOULD be a single statement -- period, plain and simple, no if and or buts about it.
snapshot too old
P, May 31, 2002 - 2:31 pm UTC
Tom,
I also want to mention that no one other than me is using those tables. Infact we have very very few people logged on that DB.
thank you for resourceful site and fantastic book
P
snapshot too old
P, May 31, 2002 - 3:31 pm UTC
tom,
so you are saying there is no way we can do this without increasing our RS?(which is 30 meg).
P
May 31, 2002 - 3:40 pm UTC
30meg is pretty darn small.
But, I betcha you might find that a single insert into as select would do it -- have you even tried it?
snapshot too old
P, May 31, 2002 - 3:46 pm UTC
tom,
yes i did with /*+APPEND*/ hint and without also - doesnt work.
it blows up no matter what I do.
single statement or batchjob doesnt matter
and I know 30 meg is small but we dont need more its not a prd system, for that matter its not even a dev system and with very very few users.
oh btw we are using sequent as our OS(i know i know its old old but hey we have it).
P
May 31, 2002 - 4:06 pm UTC
Well, you can either spend lots of time beating yourself about the head trying to make this work with a ton of code or -- you can just increase the size of your RBS to be more appropriate.
Just add a big rbs from temp or somewhere, use the dreaded "set transaction use rolback" statement -- do the transaction and drop the newly added rbs.
snapshot too old
P, May 31, 2002 - 4:19 pm UTC
well how big is big?
I have so many tables to migrate.
some have millions of rows and some with few hundred
May 31, 2002 - 4:26 pm UTC
when you say "migrate" what do you mean? Is NCDBMEL.EROUTE_LINKS an empty table that you are filling? how many indexes on it -- you'll have to give me more info.
If you are doing a one time migration -- drop the indexes, alter the table nologging, do the index /*+ append*/ as select, rebuild the indexes in parallel with nologging. No rollback at all. No redo either.
failed to extend rollback segment
P, May 31, 2002 - 5:41 pm UTC
here it is tom
I am running following sql (as you said single SQL) with /*+APPEND*/ linek and our DB is in noarchivelog.
if I understand it correctly then it should not generate any rollback segment - no indexes on destination table (only table thats it).
-------------
SQL> INSERT /*+APPEND */ INTO NCDBMEL.MEL_REFERENCE
2 (
3 MEL_REFERENCE_SEQ,
4 MEL_SEQ,
5 CREATE_USER,
6 CREATE_DATE,
7 MODIFIED_USER,
8 MODIFIED_DATE,
9 EQUIPMENT_ID,
10 REF_TYPE_CODE,
11 REFERENCE,
12 REF_SHEET,
13 REF_MODULE,
14 REF_COORD,
15 DOCUMENT_TYPE,
16 ENGDWGS_SEQ
17 )
18 (
19 SELECT
20 NCDBMEL.MAIN_SEQ.NEXTVAL,
21 B.MEL_SEQ,
22 'LOAD DATA',
23 SYSDATE,
24 NULL,
25 A.LAST_UPDATE,
26 A.SETROUTE_KEY,
27 NULL,
28 A.SETROUTE_REFERENCE,
29 A.REF_SHEET,
30 'EC',
31 NULL,
32 'DRAWING',
33 NULL
34 FROM
35 ECDB.ECDB_SETROUTE_REFERENCE A,
36 NCDBMEL.MASTER_EQUIPMENT_LIST B
37 WHERE
38 A.SETROUTE_KEY = B.EQUIPMENT_ID AND
39 B.CATEGORY_CODE IN ('EROUTE CABLE','EROUTE RACEWAY','EROUTE FROMTO')
40 );
NCDBMEL.MAIN_SEQ.NEXTVAL,
*
ERROR at line 20:
ORA-00604: error occurred at recursive SQL level 1
ORA-01562: failed to extend rollback segment number 4
ORA-01628: max # extents (20) reached for rollback segment RB3
May 31, 2002 - 9:00 pm UTC
You must have missed an index somewhere....
consider:
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;
Table dropped.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects where 1=0;
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects;
23027 rows created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;
USED_UBLK
----------
1
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(object_name);
Index created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t select * from all_objects;
23028 rows created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;
USED_UBLK
----------
116
ops$tkyte@ORA817DEV.US.ORACLE.COM>
<b>see the difference. I'm thinking - you have an index....
</b>
RBS full
P, May 31, 2002 - 5:59 pm UTC
also tom we have more than 2 million rows to migrate for this table
May 31, 2002 - 9:05 pm UTC
Well, here we go -- noarchivelog mode test database:
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;
Table dropped.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select * from big_table where 1=0;
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t select * from big_table;
1101008 rows created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction;
USED_UBLK
----------
1
Hmmm (big_table is really CTAS from all_objects with lots of insert intos -- it is "fat")
Took 1 block of RBS stuff.
Even if 2 million rows DOUBLED THAT - it would fit in 30meg I think (2 blocks)
You are doing something wrong.
maybe:
a) there is an index
b) there is an index
I think -- maybe -- there is an index.......
nologging
P, June 03, 2002 - 12:34 pm UTC
tom
I checked there are ZERO indexex on EROUTE_TERMINATION table and I get this -
I am using /*+append*/
THERE ARE NO INDEXES ON ANY DESTINATION TABLES
--------------------
SQL> ed
Wrote file afiedt.buf
1 INSERT /*+APPEND */ INTO NCDBMEL.EROUTE_TERMINATION
2 (
3 EROUTE_TERMINATION_SEQ,
4 CREATE_USER,
5 CREATE_DATE,
6 MODIFIED_USER,
7 MODIFIED_DATE,
8 MEL_SEQ,
9 EQUIPMENT_ID,
10 TERMINATION_MEL_SEQ,
11 TERMINATION_EQUIPMENT_ID,
12 WIRE,
13 BLOCK,
14 POINT,
15 VENDOR,
16 CORJ,
17 NKC_SEQ
18 )
19 (
20 SELECT
21 ncdbmel.main_seq.nextval,
22 'LOAD DATA',
23 SYSDATE,
24 NULL,
25 NULL,
26 B.mel_seq,
27 A.CJ_ID,
28 C.mel_seq,
29 A.E_ID,
30 A.WIRE,
31 A.BLOCK,
32 A.POINT,
33 A.VENDOR,
34 A.CORJ,
35 A.NKC_SEQ
36 FROM
37 ECDB.ECDB_SETROUTE_TERM A,
38 NCDBMEL.MASTER_EQUIPMENT_LIST B,
39 NCDBMEL.MASTER_EQUIPMENT_LIST C
40 WHERE
41 A.CJ_ID = B.EQUIPMENT_ID
42 AND A.E_ID = C.EQUIPMENT_ID
43* )
SQL> /
ncdbmel.main_seq.nextval,
*
ERROR at line 21:
ORA-00604: error occurred at recursive SQL level 1
ORA-01562: failed to extend rollback segment number 2
ORA-01628: max # extents (20) reached for rollback segment RB1
------------------------
June 03, 2002 - 3:50 pm UTC
Look for anything, anything at all, with regards to the table. Triggers for example. I quite simply cannot reproduce. Make sure to look when logged in as a DBA and do the looking in the DBA_ views (as I can create an index on your table, I can create a trigger on your table -- need not be in the NCDBMEL schema.
There is something else out there -- there is a reason, you'll just need to dig a little deeper. I've done this on archivelog/noarchivelog and the results are the same.
Look for a trigger this time (also look for the index but make sure to look in the DBA_ views)
A reader, June 03, 2002 - 5:08 pm UTC
Tom,
One of the comment in follow up is
"if I understand it correctly then it should not generate any rollback segment -
no indexes on destination table (only table thats it)."
I am confused here.
Direct load shold not generate REDO but it Should geneate
ROLLBACK information.
Am i right?
June 03, 2002 - 5:42 pm UTC
Wrong, it writes above the HWM for the table.
In noarchivelog mode databases -- append means NO UNDO, NO REDO (for the TABLE)
In archivelog mode databases -- append means NO UNDO and
- if table is logging -- redo will be generated
- if table is nologging
- if version is 9iR1 and before -- NO REDO
- if version if 9iR2 and after
- if database permits unloggged operations NO REDO
- else redo will be generated
(for the TABLE)
read the link I convienently provided in the answer -- details abound there in the documentation!
failed to extend RBS
P, June 03, 2002 - 5:37 pm UTC
ok here it is
DB is noarchivelog
only one user
I droped and ecreated this table so no indexes, no triggers only table
----------------------
SQL> DROP TABLE EROUTE_TERMINATION;
Table dropped.
SQL> CREATE TABLE NCDBMEL.EROUTE_TERMINATION
2 ( EROUTE_TERMINATION_SEQ NUMBER(20) NOT NULL,
3 CREATE_USER VARCHAR2(10),
4 CREATE_DATE DATE,
5 MODIFIED_USER VARCHAR2(10),
6 MODIFIED_DATE DATE,
7 MEL_SEQ NUMBER(20),
8 EQUIPMENT_ID VARCHAR2(30),
9 TERMINATION_MEL_SEQ NUMBER(20),
10 TERMINATION_EQUIPMENT_ID VARCHAR2(30),
11 WIRE VARCHAR2(8),
12 BLOCK VARCHAR2(4),
13 POINT VARCHAR2(4),
14 VENDOR VARCHAR2(1),
15 CORJ VARCHAR2(1),
16 NKC_SEQ NUMBER(3,0)
17 ) ;
Table created.
SQL> ed
Wrote file afiedt.buf
1 INSERT /*+APPEND */ INTO NCDBMEL.EROUTE_TERMINATION
2 (
3 EROUTE_TERMINATION_SEQ,
4 CREATE_USER,
5 CREATE_DATE,
6 MODIFIED_USER,
7 MODIFIED_DATE,
8 MEL_SEQ,
9 EQUIPMENT_ID,
10 TERMINATION_MEL_SEQ,
11 TERMINATION_EQUIPMENT_ID,
12 WIRE,
13 BLOCK,
14 POINT,
15 VENDOR,
16 CORJ,
17 NKC_SEQ
18 )
19 (
20 SELECT
21 ncdbmel.main_seq.nextval,
22 'LOAD DATA',
23 SYSDATE,
24 NULL,
25 NULL,
26 B.mel_seq,
27 A.CJ_ID,
28 C.mel_seq,
29 A.E_ID,
30 A.WIRE,
31 A.BLOCK,
32 A.POINT,
33 A.VENDOR,
34 A.CORJ,
35 A.NKC_SEQ
36 FROM
37 ECDB.ECDB_SETROUTE_TERM A,
38 NCDBMEL.MASTER_EQUIPMENT_LIST B,
39 NCDBMEL.MASTER_EQUIPMENT_LIST C
40 WHERE
41 A.CJ_ID = B.EQUIPMENT_ID
42 AND A.E_ID = C.EQUIPMENT_ID
43* )
SQL> /
ncdbmel.main_seq.nextval,
*
ERROR at line 21:
ORA-00604: error occurred at recursive SQL level 1
ORA-01562: failed to extend rollback segment number 4
ORA-01628: max # extents (20) reached for rollback segment RB3
June 03, 2002 - 5:51 pm UTC
email me at tkyte@oracle.com
Talk to your DBA
A reader, June 04, 2002 - 9:05 am UTC
I think P from LA should talk to his/her DBA. Even though the DBA may not be clear with some concepts, I am sure the DBA can still help. Please let Tom answer some questions that more people are interested in. Thanks a lot!
Seq.nextval
A reader, June 04, 2002 - 9:32 am UTC
Tom,
I believe the problem is with the sequence generation. if we remove seq.nextval and use a simple "rownum" then I did not get the error. what is the reason?
Thanks,
June 04, 2002 - 9:43 am UTC
I saw the sequence myself. I still could not reproduce the issue:
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop sequence t_seq;
Sequence dropped.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create sequence t_seq;
Sequence created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t as select * from all_objects where 1=0;
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t
2 select OWNER, OBJECT_NAME, SUBOBJECT_NAME, <b>t_seq.nextval,</b>
3 DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME,
4 TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY
5 from (select * from all_objects
6 union all
7 select * from all_objects
8 union all
9 select * from all_objects )
10 /
69135 rows created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select used_ublk from v$transaction
2 /
USED_UBLK
----------
1
ops$tkyte@ORA817DEV.US.ORACLE.COM>
nologging and backup
Pushparaj Arulappan, June 04, 2002 - 1:12 pm UTC
Tom,
Our Database is currently running in ARCHIVELOG mode.
Every Saturday we refresh 3 static tables with millions of
rows. Around 10 million rows for each table. We basically copy data from the other database into our production database. For example, we use the following command to do this.
COPY TO scott/tiger@D100 INSERT emp USING SELECT * FROM emp@D200 WHERE deptno between 1 and 10;
These tables are currently created with logging mode.
SELECT logging FROM dba_tables WHERE table_name='EMP';
LOGGING
-------
YES
After refereshing these three tables the amount of archive log generation is around 30 GB. All these three tables have a unique index.
The loss of data in these three tables is not a concern because we can bring the data from the other database any time. And these three tables purely a read only table.
To save the archiving space and even speed up this, I am planning to use the Direct-load INSERT.
INSERT /*+ APPEND */ INTO emp
SELECT * FROM emp@D200 WHERE deptno between 1 and 10;
My main concern is: Question1:
1) After I load these tables with direct-load insert, should I have to take the backup of the entire database (cold backup) or just taking the export of these three tables alone is enough ?
Assume 05/01/2002 is my last full database backup and I have refereshed these three tables using Direct-Load insert method on 05/08/2002 , 05/15/2002 and 05/22/02 and have only taken the export of these three tables and on 05/27/02 the database crashes due to some media failures.
Can I restore the database from 05/01/2002 backup and recover the database using the archive logs untill 05/27/02 for the rest of the tables in the database without any problem.
Question2:
Since redo for the indexes can not be avoided, I want to drop the indexes and recreate them after the load. Is there any other good way ? ( I can do a QUERY on the other database to see if the rows are unique before I insert into my tables )
Question3:
Can I leave the tables in LOGGING mode and create the INDEX in NOLOGGING mode. Is it advisable ?
Question4:
Would you reccommend to alter these three tables permenantly into NOLOGGING mode ? If I alter the tables into a NOLOGGING mode, do I need to take a FULL DATABASE BACKUP after that?
Question5:
What is advantage of creating these three tables with NOLOGGING over LOGGING mode.
Please give me any other advise that you think is good for my situation.
Thanks
Pushparaj
June 05, 2002 - 6:15 am UTC
I would consider doing this:
a) the tables in question are in NOLOGGING mode all of the time
b) use the direct path insert to populate them -- do not use regular inserts (like COPY). Do not have any indexes
(that way, when loaded, they will not generate undo or redo)
c) create NON-UNIQUE indexes on the table (the columns you normally use in a primary key). When you add the indexes -- do it in PARALLEL with NOLOGGING
d) add the primary key with "exceptions into" -- you will capture duplicate rows for cleaning up. The primary key will "kidnap" the non-unique index and use it.
e) after doing these operations -- simply backup (hot backup) the affected tablespaces (the tablespaces with the table / index data ). Add that to your backup set.
That is it.
How big are the rollback segment extents?
Mike, June 04, 2002 - 2:16 pm UTC
The error says it's failing at 20 extents. How big is your extent size for the rollback segment?
mr p
A reader, June 04, 2002 - 5:34 pm UTC
mr p how can you have 20 as maxextents of RBS
ORA-01628: max # extents (20) reached for rollback segment RB3
LOL
The direct load INSERT /*+ APPEND */ does not work on one db
Sean Bu, August 26, 2002 - 10:04 am UTC
Tom,
The direct load of "Insert /*+ append */ .." does not work on one 8.1.7.3 database (which generated more redo than no-direct load) , but works on another 8.1.7.3 database on the same HP server. The both are on the same oracle home. I could not figure the reasons of the differentail.
I have traced on the both sessions on the two databases. If you need them , I can send your the raw files. By looking into the trace outputs of the both, except the difference order of the statements shown in the trace files (the test procedure were same for the two db), I have not enabled to justify the reasons direct load not working on the RESUMIX database.
The test table t was in LMT on the both databases.
[oracle@ne:RESUMIX_8.1.7_64]$ sqlplus toolman
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Aug 26 09:47:56 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.3.0 - 64bit Production
SQL> drop table t;
Table dropped.
SQL> create table t as select * from dba_objects where 0=1;
Table created.
SQL> set autotrace on stat
SQL> insert into t select * from dba_objects;
4534 rows created.
Statistics
----------------------------------------------------------
7 recursive calls
121 db block gets
2846 consistent gets
0 physical reads
430600 redo size
646 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4534 rows processed
SQL> rollback;
Rollback complete.
SQL> insert /*+ append */ into t select * from dba_objects;
4534 rows created.
Statistics
----------------------------------------------------------
2 recursive calls
17 db block gets
2786 consistent gets
0 physical reads
459848 redo size
634 bytes sent via SQL*Net to client
482 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
4534 rows processed
SQL>exit
On the DM database, IT WORKS.
[oracle@ne:dm_8.1.7_64]$ sqlplus toolman
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Aug 26 09:45:10 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.3.0 - 64bit Production
SQL> drop table t;
Table dropped.
SQL> create table t as select * from dba_objects where 0=1;
Table created.
SQL> set autotrace on stat
SQL> insert into t select * from dba_objects;
4663 rows created.
Statistics
----------------------------------------------------------
9 recursive calls
657 db block gets
3953 consistent gets
0 physical reads
508488 redo size
647 bytes sent via SQL*Net to client
468 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
4663 rows processed
SQL> rollback;
Rollback complete.
SQL> insert /*+ append */ into t select * from dba_objects;
4663 rows created.
Statistics
----------------------------------------------------------
50 recursive calls
24 db block gets
3845 consistent gets
0 physical reads
2424 redo size
633 bytes sent via SQL*Net to client
482 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
4663 rows processed
SQL> exit
August 26, 2002 - 10:15 am UTC
You've got archivelogmode on and you didn't set the table to NOLOGGING.
read the very first comment on this page above for details....
Thanks!
Sean Bu, August 26, 2002 - 12:25 pm UTC
Just curious novice
HS, August 26, 2002 - 2:41 pm UTC
how do u display 'STATISTICS' report?
Relationship b/w logging/nologging and archive/noarchive mode-if any
HS, August 26, 2002 - 4:14 pm UTC
What is the 'official' oracle defined relationship between logging/nologging and archive/noarchive mode of a database. Please explain.
Thanks in advance for your answer.
August 26, 2002 - 7:25 pm UTC
In noarchivelog mode, you need not ALTER the underlying object to have the NOLOGGING attribute to perform a non-logged activity (since in noarchivelog mode, we only need to suport instance recovery, not media recovery -- and we can support that without generating the redo entries)
In archivelog mode, you must go out of your way to say "yes, I really mean to do this". It prevents "joe user" from doing a non-logged operation on some table -- destroying its recoverability from backups. "Joe user" cannot do an insert /*+ append */ without generating redo unless "Joe user" can also alter the table (something typically doable only by the DBA who really needs to be aware when non-logged things happen so they can backup)
db mode, table mode and insert mode - differences
A reader, August 27, 2002 - 9:52 am UTC
Tom,
I used to have confusion about the nologging option. Once I did an experiment, everything bEcame clear. As given below, there are 8 different possibilites with db mode, table mode and insert mode.
Here is the summary
Case DB Mode Table Mode Insert Mode Redo Size Undo Size
====== =========== ========== ============ ========= =========
1 Archivelog Logging No append 17373988 73
2 Archivelog Nologging No append 17374140 74
3 Archivelog Logging Append 17462504 1
4 Archivelog Nologging Append 138496 1
5 NoArchivelog Logging No append 17383188 73
6 NoArchivelog Nologging No append 17379244 74
7 NoArchivelog Logging Append 138824 1
8 NoArchivelog Nologging Append 139324 1
Here are the details:
=====================
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL>
SQL> drop table t;
Table dropped.
SQL> create table t as select * from all_objects where 1 = 0;
Table created.
SQL> select logging from user_tables where table_name = 'T';
LOG
---
YES
SQL> set autotrace on statistics
SQL> insert into t select * from all_objects ;
159568 rows created.
Statistics
----------------------------------------------------------
8341 recursive calls
12760 db block gets
1679474 consistent gets
4076 physical reads
17373988 redo size
860 bytes sent via SQL*Net to client
566 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
159568 rows processed
SQL> set autotrace off
SQL> select used_ublk from v$transaction;
USED_UBLK
----------
73
SQL>
SQL> drop table t;
Table dropped.
SQL> create table t nologging as select * from all_objects where 1 = 0;
Table created.
SQL> select logging from user_tables where table_name = 'T';
LOG
---
NO
SQL> set autotrace on statistics
SQL> insert into t select * from all_objects ;
159568 rows created.
Statistics
----------------------------------------------------------
9005 recursive calls
12769 db block gets
1680085 consistent gets
3585 physical reads
17374140 redo size
862 bytes sent via SQL*Net to client
566 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
159568 rows processed
SQL> set autotrace off
SQL> select used_ublk from v$transaction;
USED_UBLK
----------
74
SQL>
SQL> drop table t;
Table dropped.
SQL> create table t as select * from all_objects where 1 = 0;
Table created.
SQL> select logging from user_tables where table_name = 'T';
LOG
---
YES
SQL> set autotrace on statistics
SQL> insert /*+ append */ into t select * from all_objects ;
159568 rows created.
Statistics
----------------------------------------------------------
9309 recursive calls
1101 db block gets
1678076 consistent gets
3599 physical reads
17462504 redo size
847 bytes sent via SQL*Net to client
580 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
159568 rows processed
SQL> set autotrace off
SQL> select used_ublk from v$transaction;
USED_UBLK
----------
1
SQL>
SQL> drop table t;
Table dropped.
SQL> create table t nologging as select * from all_objects where 1 = 0;
Table created.
SQL> select logging from user_tables where table_name = 'T';
LOG
---
NO
SQL> set autotrace on statistics
SQL> insert /*+ append */ into t select * from all_objects ;
159568 rows created.
Statistics
----------------------------------------------------------
9387 recursive calls
1103 db block gets
1678141 consistent gets
3557 physical reads
138496 redo size
847 bytes sent via SQL*Net to client
580 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
159568 rows processed
SQL> set autotrace off
SQL> select used_ublk from v$transaction;
USED_UBLK
----------
1
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL>
SQL> drop table t;
Table dropped.
SQL> create table t as select * from all_objects where 1 = 0;
Table created.
SQL> select logging from user_tables where table_name = 'T';
LOG
---
YES
SQL> set autotrace on statistics
SQL> insert into t select * from all_objects ;
159568 rows created.
Statistics
----------------------------------------------------------
10499 recursive calls
12771 db block gets
1681361 consistent gets
6052 physical reads
17383188 redo size
853 bytes sent via SQL*Net to client
566 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
159568 rows processed
SQL> set autotrace off
SQL> select used_ublk from v$transaction;
USED_UBLK
----------
73
SQL>
SQL> drop table t;
Table dropped.
SQL> create table t nologging as select * from all_objects where 1 = 0;
Table created.
SQL> select logging from user_tables where table_name = 'T';
LOG
---
NO
SQL> set autotrace on statistics
SQL> insert into t select * from all_objects ;
159568 rows created.
Statistics
----------------------------------------------------------
8862 recursive calls
12769 db block gets
1679977 consistent gets
3824 physical reads
17379244 redo size
857 bytes sent via SQL*Net to client
566 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
159568 rows processed
SQL> set autotrace off
SQL> select used_ublk from v$transaction;
USED_UBLK
----------
74
SQL>
SQL> drop table t;
Table dropped.
SQL> create table t as select * from all_objects where 1 = 0;
Table created.
SQL> select logging from user_tables where table_name = 'T';
LOG
---
YES
SQL> set autotrace on statistics
SQL> insert /*+ append */ into t select * from all_objects ;
159568 rows created.
Statistics
----------------------------------------------------------
9852 recursive calls
1108 db block gets
1678166 consistent gets
3653 physical reads
138824 redo size
843 bytes sent via SQL*Net to client
580 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
15 sorts (memory)
0 sorts (disk)
159568 rows processed
SQL> set autotrace off
SQL> select used_ublk from v$transaction;
USED_UBLK
----------
1
SQL>
SQL> drop table t;
Table dropped.
SQL> create table t nologging as select * from all_objects where 1 = 0;
Table created.
SQL> select logging from user_tables where table_name = 'T';
LOG
---
NO
SQL> set autotrace on statistics
SQL> insert /*+ append */ into t select * from all_objects ;
159568 rows created.
Statistics
----------------------------------------------------------
9731 recursive calls
1104 db block gets
1678440 consistent gets
3405 physical reads
139324 redo size
844 bytes sent via SQL*Net to client
580 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
159568 rows processed
SQL> set autotrace off
SQL> select used_ublk from v$transaction;
USED_UBLK
----------
1
Confusion clearing up but...
HS, August 27, 2002 - 10:20 am UTC
Tom here what you said :
"In noarchivelog mode, you need not ALTER the underlying object to have the NOLOGGING attribute to perform a non-logged activity (since in noarchivelog mode, we only need to suport instance recovery, not media recovery -- and we can support that without generating the redo entries)
In archivelog mode, you must go out of your way to say "yes, I really mean to do this". It prevents "joe user" from doing a non-logged operation on some table
-- destroying its recoverability from backups. "Joe user" cannot do an insert /*+ append */ without generating redo unless "Joe user" can also alter the table (something typically doable only by the DBA who really needs to be aware when non-logged things happen so they can backup)"
From this I infer the following :
(A)In 'noarchive log' mode, logging/nologging attribute of an object has no effect. As Oracle will perform only instance recovery and this can be achieved without logged entries. Hence my question is ;
if i have logging option enabled for objects in 'noarchive log' mode database this means I am asking Oracle to do more work for nothing. hence I should have nologging option enabled for a database in noarchive log mode?
(B)And for a database in archive log mode all oobjects should have logging option enabled-default. I do it at the tablespace level and let objects inherit this attribute.
And finally what operations are logged anyway regardless of logging/nologging option? i am thinking all DML, is this correct.
Appreciate your answer. thank you.
August 27, 2002 - 10:40 am UTC
(A) no, 100% wrong.
It means that in NOARCHIVELOG mode, whenever we can, we skip redo generation. If you say "insert /*+ append */ into t select ...", in noarchivelog mode, we KNOW we can safely skip redo generation and hence we do.
(B) correct.
Everything is logged EXCEPT for statements that listed above in the original question
Thanks and follow up
HS, August 27, 2002 - 10:53 am UTC
Tom,
with reference to my earlier question bullet (A) and your answer, i have to ask that why would i want to have logging option enabled if oracle can recover the instance with nologging option.
I am thinking in terms of my test/QA env. where we drop/create indexes left and right in a datawarehouse situation. Prod DB is in archive log mode.
Thanks again.
August 27, 2002 - 10:57 am UTC
It doesn't matter, you don't need to have it on, or off -- in noarchive log mode it is not very meaningful.
So, don't rush out there and turn it "off", it won't do anything for you. It is already as good as off. the potential damage could be that the nologging attribute finds its way into production by accident. we don't want that to happen!
Perfect - thank you and unrelated comment :)
HS, August 27, 2002 - 11:06 am UTC
Hey Tom,
I understand that this is a pro. site and I would like it to be that way also.However did you guys think about adding some fun things like graphical expressions, theme's like beach, ski, nature etc?
I think it will spice up a things little bit in a good different way....:)and would be nice to express ourselves in a different way....
August 27, 2002 - 11:27 am UTC
If I had my way, this site would be black text on a white background with links and no pictures whatsoever....
Themes -- no, I doubt I would be going down that path. My favorite font is Courier, images -- find them just getting the way.
nologging and standby database
Logan Palanisamy, August 27, 2002 - 1:39 pm UTC
Tom,
If we perform nologging operations such as "insert /*+append +/ select .." and "alter index ... rebuild nologging" on a production database in archive log mode with standby database, there is no archive log generated for them. Then how does the standby database syncup with the production db? Will the standby database have this data? If yes, how?
August 27, 2002 - 1:50 pm UTC
No, it won't -- that is exactly why using NOLOGGING should be left upto the experienced DBA who is aware of such issues and will make sure that if they do happen (cause they want it to) they fix the standby
Note in 9iR2, you can make it so that even if
a) nologging is on the table
b) you do a non-loggable thing (like append)
c) the database is in archive log mode
we ignore you and generate redo log anyway (for just such total protection).
nologging and standby
Logan Palanisamy, August 27, 2002 - 3:13 pm UTC
Does it then mean, there is no such thing as non-loggable operation in 9iR2? Redo is generated for everything including 'nologging' operations and tables.
August 27, 2002 - 3:57 pm UTC
No, it means there is the ability for the DBA to make it so that everything is logged.
If the DBA doesn't do that, caveat emptor -- you are back to where you began.
Reader
A reader, August 27, 2002 - 9:55 pm UTC
Tom
There is no "direct read buffer" parameter in Oracle 8i.
How to tune the direct load process. Are there parameters
relevent to "direct load/insert" process which will be
helpful for us to comprehend
Thanks
August 28, 2002 - 9:35 am UTC
not familar with "direct_read_buffer" -- what tool/version did you use this with?
Reader
A reader, August 28, 2002 - 6:48 pm UTC
Tom,
I was actually referring to the init<SID>.ora
parameter. I have read it in the article
"The Mysteries of DBWR Tuning" by Steve Adams
<quote>
From version 7.1.5 onwards, this (DBWR load) can be
avoided by using the parallel query option to force blocks to be read into "direct read buffers", rather than into
the database buffer cache
<quote>
Thanks
August 29, 2002 - 9:02 am UTC
It is *not* a parameter -- it is an "activity" if you will.
Sort reads - use direct read buffers (eg: no need to use the buffer cache and read consistency -- we KNOW you are the only one that owns these blocks and the only one who will use them -- putting them into the LRU would be pointless and expensive)
Parallel query -- we can do a global checkpoint before running the query to flush the dirty blocks to disk. Now, PQ can bypass the buffer cache and just read from disk -- not putting stuff in the cache, not wasting time looking in the cache for data. It just "happens" tho, there is no "parameter"
Reader
A reader, September 01, 2002 - 12:26 pm UTC
Tom,
I have a table t with 104,190 rows created from DBA_OBJECTS
The HWM is 1287 blocks. Arraysize is set at default
I performed two queries
1. do an insert update owner = owner
2. select /*+ parallel(t 4) */ * from t;
For Each of this statement, before and after the statement
1. I found the blocks that are dirty
2. I gathered statistics from v$sysstat DBWR%, and
"table scan%" and found the delta
1. When I did parallel query, DBWR checkpoint buffers witten is only 46
where as v$bh.dirty = 103. Physical reads however shows 1292 close to
HWM which is correct
2. When I did parallel query, there were 2628 CR and 428 block gets
What are these used for
Thanks
SQL*Plus: Release 8.1.7.0.0 - Production on Sun Sep 1 07:53:24 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
SQL> select count(*), dirty from v$bh group by dirty;
COUNT(*) D
---------- -
1247 N
33 Y
SQL> insert into stats_t select'first',statistic#, name,class,value from stats;
18 rows created.
Update the table T
==================
SQL> update t set owner = owner;
104192 rows updated.
SQL> commit;
Commit complete.
SQL> insert into stats_t select'next',statistic#, name,class,value from stats;
18 rows created.
SQL> select count(*), dirty from v$bh group by dirty;
COUNT(*) D
---------- -
1177 N
103 Y
SQL> select f.name, f.value, n.value, n.value-f.value from ....
SQL> /
NAME VALUE VALUE N.VALUE-F.VALUE
---------------------------------------- ---------- ---------- -------------
DBWR buffers scanned 0 250 250
DBWR checkpoint buffers written 5 2459 2454
DBWR checkpoints 1 25 24
DBWR cross instance writes 0 0 0
DBWR free buffers found 0 250 250
DBWR lru scans 0 1 1
DBWR make free requests 0 1 1
DBWR revisited being-written buffer 0 0 0
DBWR summed scan depth 0 250 250
DBWR transaction table writes 0 32 32
DBWR undo block writes 0 1152 1152
table scan blocks gotten 649 1945 1296
table scan rows gotten 54684 158883 104199
table scans (cache partitions) 0 0 0
table scans (direct read) 0 0 0
table scans (long tables) 42 43 1
table scans (rowid ranges) 0 0 0
table scans (short tables) 33 34 1
18 rows selected.
SQL> truncate table stats_t;
Table truncated.
Select from Table t in Parallel Mode
====================================
SQL>select /*+ parallel(t 4) */ * from t;
104192 rows selected.
Elapsed: 00:00:19.29
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=196 Card=105123 Byte
s=19973370)
1 0 TABLE ACCESS* (FULL) OF 'T' (Cost=196 Card=105123 Bytes=19 :Q17000
973370)
1 PARALLEL_TO_SERIAL SELECT /*+ Q17000 NO_EXPAND ROWID(A1) */ A1.
"SEQ#",A1."OWNER",A1."OBJECT_NAME",A
Statistics
----------------------------------------------------------
567 recursive calls
428 db block gets
2628 consistent gets
1292 physical reads
684 redo size
11575319 bytes sent via SQL*Net to client
771431 bytes received via SQL*Net from client
6948 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
104192 rows processed
SQL> select count(*), dirty from v$bh group by dirty;
COUNT(*) D
---------- -
1202 N
78 Y
SQL> select f.name, f.value, n.value, n.value-f.value from ....
NAME VALUE VALUE N.VALUE-F.VALUE
---------------------------------------- ---------- ---------- -------------
DBWR buffers scanned 250 250 0
DBWR checkpoint buffers written 2459 2505 46
DBWR checkpoints 25 27 2
DBWR cross instance writes 0 0 0
DBWR free buffers found 250 250 0
DBWR lru scans 1 1 0
DBWR make free requests 1 1 0
DBWR revisited being-written buffer 0 0 0
DBWR summed scan depth 250 250 0
DBWR transaction table writes 32 32 0
DBWR undo block writes 1152 1152 0
table scan blocks gotten 1958 3265 1307
table scan rows gotten 159034 263254 104220
table scans (cache partitions) 0 0 0
table scans (direct read) 0 52 52
table scans (long tables) 43 95 52
table scans (rowid ranges) 0 52 52
table scans (short tables) 39 58 19
SQL> select table_name , blocks from dba_tables where table_name = 'T';
TABLE_NAME BLOCKS
------------------------------ ----------
T 1287
September 01, 2002 - 2:23 pm UTC
and this relates to logging vs nologging how??
Reader
A reader, September 02, 2002 - 10:46 am UTC
I agree , it is not directly related.
I thought nologging relates to => direct insert
direct insert relates to => direct read
direct read relates to => parallel query
BTW, I figured it out by joining v$bh and dba_tables on
file_id and block_id where segment_name = 'T' and found
that all the buffers used by 'T' were flushed to disk
Thanks
September 02, 2002 - 11:09 am UTC
Thats like the 7 degrees of Kevin Bacon there.
rollback and index maintenance
A reader, September 17, 2002 - 6:47 am UTC
Hi Tom
How come direct insert generates minimum ROLLBACK?
Also in a nologging direct insert why index still generates so much redo?
September 17, 2002 - 8:08 am UTC
a direct path insert writes above the high water mark of the table. In order to "rollback" such an insert -- all we have to do is push the high water mark back to where it was. We don't need to go back to each block and undo the insert -- we just undo the advancing of the high water mark on the table and wah-lah -- it is undone.
An index is a complex data structure, unlike a HEAP table. Hence, when we add something to an index -- that data goes into the MIDDLE of the structure (eg: you add 'B' to an index containing 'A' and 'C' -- 'B' goes in between A and C). So, in the index, we are maintaining a data structure, not just adding at the "end". So, in order to rollback we do need to go back and undo the inserts (we need the rollback data). We cannot simply "unadvance a high water mark" and in effect erase that which we added like we can with a heap table.
Direct path insert/Insert - Append : Aren't they same
Logan Palanisamy, September 17, 2002 - 8:48 am UTC
Tom,
Are the "direct path insert" and "Insert /* +Append */" the same or different?
I thought the "insert /* +Append */" inserts into a brand new extent, rather than above the HWM. You are saying "direct path insert" inserts above HWM? Inserting above HWM doesn't necessarily mean inserting into a brand new extent.
Would appreciate if you could highlight the differences?
Direct mode insert and index maintenance
Rob, September 17, 2002 - 2:57 pm UTC
Tom:
This is from the Oracle doc referenced by your link.
Index Maintenance
For direct-load INSERT on nonpartitioned tables or partitioned tables that have local or global indexes, index maintenance is done at the end of the INSERT operation. This index maintenance is performed by the parallel execution servers for parallel direct-load INSERT or by the single process for serial direct-load INSERT on partitioned or nonpartitioned tables.
If your direct-load INSERT modifies most of the data in a table, you can avoid the performance impact of index maintenance by dropping the index before the INSERT and then rebuilding it afterwards.
My question is:
What is the "performance impact" that is avoided by dropping the index before the INSERT and then rebuilding it afterward. Since index maintenance is done at the end of the INSERT operation by the parallel execution servers for parallel direct-load INSERT, it seems like the index is maintained in parallel. Is the performance advantage achieved by recreating or rebuilding the index with nologging? Is there any other advantage?
September 17, 2002 - 8:07 pm UTC
there can be significant advantages AND disadvantages.
some (not all, i'm sure someone will add something else -- they always do) advantages:
o you can rebuild the index with nologging on (no rollback, no redo for the index maintenance)
o you can rebuild the index in parallel
o you get a nicely compacted index at the end
some disadvantages:
o if you added 100meg to a 5 gig table, rebuilding the index might take significant amounts of time
o the index is unavailable -- meaning the table is "offline" sort of during this entire load/reindex operation (this index just won't be there)
Clarification
Rob, September 17, 2002 - 8:41 pm UTC
This index maintenance is performed by the parallel execution servers for parallel direct-load INSERT
One of the advantages you mention concerns rebuilding the index in parallel. What are the parallel execution servers (note the plural) mentioned above doing if not rebuilding the index in parallel?
September 17, 2002 - 8:55 pm UTC
They are MAINTAINING the index in parallel (during a parallel direct path insert)
If you REBUILD and index in parallel, that is much different from MAINTAINING and index (adding new entries to existing index) in parallel.
REBUILD = full scan table in parallel (since index doesn't have all of the data), sort it and build new index.
MAINTAIN = build your little index pieces for the data you inserted and then add it to the existing index in bulk at the end.
One more clarification
Rob, September 17, 2002 - 9:19 pm UTC
Tom:
Sorry to be a nag, I guess I am being dense about this but I am not understanding the maintain process.
1) If the index is maintained after all of the rows are inserted into the table, what info is telling the process what rows in the index to maintain. Does it know based upon the rows that have been inserted into the table but not committed?
3) If multiple processes are updating the index why is it slower than multiple processes building a new index.
MAINTAIN = build your little index pieces for the data you inserted and then add it to the existing index in bulk at the end.
September 17, 2002 - 9:34 pm UTC
1) yes, the rows it processed are the ones it is maintaining -- it writes above the HWM to the table and creates some index data off to the side. it'll merge that in after the fact, when the insert is done.
3) didn't say that it would be did I? If you add 100m to a 5gig table, it would probably be faster to maintain.
if you add 5 gig to a 100meg table, it would probably be faster to rebuild.
Redo Size
John, February 25, 2003 - 8:12 am UTC
Hi Tom,
I'm inserting about 30M records into a truncated NOLOGGING table using INSERT /*+ APPEND */ after dropping all unique indexes, marking all other indexes as unusable (and altering session) and disabling all constraints. The statistics are telling me I'm generating about 180Mb of redo. Without the APPEND it's about 850Mb. What I'm keen to know is if 180Mb is really as small as the redo gets. What size redo would you expect for this insert?
Is the redo because of the generation of a sequence number, managing the extents, marking the inserted rows, or ... something else?
Your comments would be appreciated.
February 25, 2003 - 9:52 am UTC
You may well have missed an index. It could be recursive SQL due to a sequence (alter your sequences and set the cache = 100,000 or more -- it'll go MUCH faster as well).
redo for recursive sql (auditing, sequences, anything you call as a side effect)
redo for space mgmt (extents, quotas, etc)
You can use log miner to see more clearly what the redo is.
Problem in Creating Database
MWG, March 04, 2003 - 5:09 am UTC
Hi Tom,
I have a script to create database, as
startup nomount pfile="e:\oracle\ora92\database\initTESTORA.ora";
CREATE DATABASE TESTORA controlfile reuse
LOGFILE GROUP 1 ('e:\oracle\oradata\TESTORA\REDO\redo01.log', 'z:\oracle\MIRLOG\TESTORA\redo01.log') SIZE 10M,
GROUP 2 ('e:\oracle\oradata\TESTORA\REDO\redo02.log', 'z:\oracle\MIRLOG\TESTORA\redo02.log') SIZE 10M,
GROUP 3 ('e:\oracle\oradata\TESTORA\REDO\redo03.log', 'z:\oracle\MIRLOG\TESTORA\redo03.log') SIZE 10M
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
DATAFILE 'e:\oracle\oradata\TESTORA\SYS\system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
UNDO TABLESPACE "undotbs01" DATAFILE 'e:\oracle\oradata\TESTORA\UNDO\undo01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'e:\oracle\oradata\TESTORA\TEMP\temp01.dbf' SIZE 55M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
CHARACTER SET WE8ISO8859P15
NATIONAL CHARACTER SET AL16UTF16;
spool off
spool e:\oracle\ora92\assistants\dbca\logs\CreateDBFiles.log
CREATE TABLESPACE "USERS" LOGGING DATAFILE 'e:\oracle\oradata\TESTORA\USR\users01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE "RBS" LOGGING DATAFILE 'e:\oracle\oradata\TESTORA\RBS\rbs01.dbf' SIZE 20M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE "INDX" LOGGING DATAFILE 'e:\oracle\oradata\TESTORA\INDX\indx01.dbf' SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;
spool off
spool e:\oracle\ora92\assistants\dbca\logs\CreateDBCatalog.log
@e:\oracle\ora92\rdbms\admin\catalog.sql;
@e:\oracle\ora92\rdbms\admin\catexp7.sql;
@e:\oracle\ora92\rdbms\admin\catblock.sql;
@e:\oracle\ora92\rdbms\admin\catproc.sql;
@e:\oracle\ora92\rdbms\admin\catoctk.sql;
@e:\oracle\ora92\rdbms\admin\owminst.plb;
exit;
My question is, After I create database with this script, and try to connect to the database, then it says, 'sys user doesn't have create session privilege'.
Whats wrong with my script?? could you please help me.
thank you in advance.
MWG from Germany
March 04, 2003 - 6:06 pm UTC
You should not use SYS (or system for that matter) anyway. Probably best you cannot log in with SYS, that is our account, you don't need to use it (in fact 9i makes it excessively hard by default to use it at all!)
connect ... AS SYSDBA
should do it.
MWG, March 06, 2003 - 4:13 am UTC
Hi Tom,
Thanks for the earlier reply.
But Tom, as I want to run the pupbld.sql, so I do need to connect as system. but with my script, I cannot connect to system ( because, system doesn't have create session privilege), so I connect to system as sysdba and then ran the pupbld.sql, but after that, when I try to connect to another user, then oracle says, "pupbld.sql should run when connecting to system, therefore cannot load data to SQLPLUS_PRODUCT_PROFILE".
So could you please tell what I have done wrong???
Thanks,
MWG from Germany
March 06, 2003 - 7:42 am UTC
you didn't follow the directions to run it as system.
run it as system as it says. fix your system account, run it, unfix the system account.
Doubt
Cefers.br, May 30, 2003 - 5:14 pm UTC
I read the Oracle Documentation link you mentioned above and I found that:
[...]
Advantage over Parallel Direct Load (SQL*Loader)
Direct-path INSERT operations ensure atomicity of the transaction.
[...]
Can you explain what is ATOMICITY of the transaction?
Thanks in advance...
May 31, 2003 - 11:41 am UTC
it either happens entirely or not at all. It is a single statement. it is atomic.
how can i prevent DML from SQL*PLUS
MUBARAK MUNSOUR ELNOUR, July 14, 2003 - 6:46 am UTC
Dear
Tom
what is the value taht i insert into TABLE sqlplus_product_profile to prevent any DML OPERATION FROM SQL*PLUS
THANKS
MUBARAK
July 14, 2003 - 9:24 am UTC
Regarding /*+ APPEND */ problem by P from LA
A reader, July 14, 2003 - 4:30 pm UTC
Hi Tom,
I was just browsing this thread and when I came to the problem face by "P from LA" I could see that he was giving the hint in his query as "/*+APPEND*/" no SPACE between the + and APPEND. I suppose that his hint was never used and hence it was doing a conventional path insert using the RBS and generating REDO. hence his inserts were failing. Am I right in this regard? Or was his problem solved by you offline?
Thanks
July 15, 2003 - 1:07 am UTC
that space after the + was only needed in earlier releases and then only inside PLSQL -- did not apply
the problem was the sequence and the massive recursive sql taking place. altering the sequence to have a much larger cache size not only worked around it but made it lots faster
Thank you.. I tried it on 9iR2 and it worked without the spaces as well :)
A reader, July 15, 2003 - 1:10 am UTC
NOLOGGING at Tablespace level
A reader, July 16, 2003 - 10:36 pm UTC
Hi Tom,
Can I get any improvement in performance by placing tablespace in NOLOGGING mode for DW application running in NOARCHIVE mode?
July 17, 2003 - 10:19 am UTC
not really -- the operations such as
create table as select
insert /*+ APPEND */
direct path loads
will already skip logging in a noarchivelog mode database as they do direct writes to the datafiles
other operations such as insert/update/delete/merge will always generate redo regardless of the mode
Why this small difference?
A reader, July 17, 2003 - 10:32 pm UTC
Tom,
Thanks for your quick response. But I am noticing small difference as below:
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL> select substr(segment_name,1,30), tablespace_name from user_segments where tablespace_name = 'TEST_TS';
no rows selected
SQL> select LOGGING from user_tablespaces where tablespace_name = 'TEST_TS';
LOGGING
---------
NOLOGGING
SQL> create table test tablespace test_ts as select * from dba_objects where 1=0;
Table created.
SQL> set autotrace on statistics
SQL> insert into test select * from dba_objects;
8733 rows created.
Statistics
----------------------------------------------------------
9 recursive calls
350 db block gets
10304 consistent gets
219 physical reads
945336 redo size
652 bytes sent via SQL*Net to client
470 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
8733 rows processed
SQL> rollback;
SQL> alter tablespace TEST_TS logging;
Tablespace altered.
SQL> select LOGGING from user_tablespaces where tablespace_name = 'TEST_TS';
LOGGING
---------
LOGGING
SQL> insert into test select * from dba_objects;
8733 rows created.
Statistics
----------------------------------------------------------
35 recursive calls
362 db block gets
10302 consistent gets
0 physical reads
947424 redo size
653 bytes sent via SQL*Net to client
470 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
8733 rows processed
July 18, 2003 - 1:43 am UTC
that is totally insignificant -- the extra redo could happen for any number of reasons -- eg: recursive sql executed in order to grab a new extent.
nologging would take the redo down to a really small number, it is not coming into play here AT ALL
SQL*Loader Direct Load
K, October 04, 2003 - 2:37 pm UTC
SQL*Loader Direct LOAD bypasses the archive logs. If that is the case how do one replicate the data in contigency database ie 8i standby. Secondly is there a command to find out in primary and in standby database to see if nologging operation was performed on data load and on on any DML performed. If so can you please explain with an example of how it works. You have mentioned in 9i even with nologging on table level Oracle ignores and generates redo log anyways for total protection. Is there a command to be set on primary or contigency or is it by default setting in Oracle 9i.
Thanks
October 04, 2003 - 3:18 pm UTC
no it doesn't.
only if the object was marked as NOLOGGING in the database AND the database allows for nonlogged operations.
a direct path load does not bypass the redo logs. not unless you go way way out of your way to do so -- and in a standby environment -- you would not "do so"
IS APPEND WORKING ?
Abhi malhotra, November 10, 2003 - 3:42 am UTC
hello sir,
You have given comment in the middle of this discussion.
"
Note in 9iR2, you can make it so that even if
a) nologging is on the table
b) you do a non-loggable thing (like append)
c) the database is in archive log mode
we ignore you and generate redo log anyway (for just such total protection).
"
----------------------------------------------------------
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
SQL> conn j/j
Connected.
SQL> create table a as select * from dba_objects where rownum<1;
Table created.
SQL> set autot on stat
SQL> insert into a select * from dba_objects;
21947 rows created.
Statistics
----------------------------------------------------------
429 recursive calls
7397 db block gets
20771 consistent gets
737 physical reads
3041696 redo size <<--- First Time Redo
SQL> truncate table a;
Table truncated.
SQL>
SQL> insert /*+APPEND*/ into a select * from dba_objects;
21947 rows created.
Statistics
----------------------------------------------------------
357 recursive calls
224 db block gets
19504 consistent gets
3 physical reads
2779828 redo size <<--- Second Time Redo
SQL> truncate table a;
Table truncated.
SQL> alter table a nologging;
Table altered.
SQL> insert /*+APPEND*/ into a select * from dba_objects;
21947 rows created.
Statistics
----------------------------------------------------------
528 recursive calls
225 db block gets
19527 consistent gets
0 physical reads
24308 redo size <<--- Third time very less Redo
Q1.
Is there --APPEND-- working with 9iR2,
Database in Archivelog mode,
Table is in Nologging ?
Q2.
Why There diff. with First Time and Second Time Redo.
Thanx
Abhi
November 10, 2003 - 7:48 am UTC
q1) your database wasn't altered to force logging
ops$tkyte@ORA920PC> select force_logging from v$database;
FOR
---
NO
q2)
insert append and insert work differently, i would expect them to do different things. one writes above the high water mark, maintains indexes in bulk. The other does not. you cannot compare them.
global temporary table, archivelog mode, direct path insert
Dusan Valasek, February 16, 2004 - 11:04 am UTC
Hi Tom,
I consider using "alter table exchange partition with table", where table for exchange would by global temporary table. To populate temporary fast, I would use insert /*+ append*/ approach, then rebuild indexes and finally exchange with proper partition. Do I need to do backup immediately after this operation in archivelog mode?
thanks,
Dushan
February 16, 2004 - 11:12 am UTC
it'll not be happening. you won't be using a GTT for such an operation. (doesn't really make sense, the data "disappears" and the data would be in a "tempfile", not a "datafile" in most all 'well configured' setups)
You'll be using a "real" table.
If you use NOLOGGING operations you'll want to backup the affected datafiles, yes.
insert /*+ append */ by itself is "redo safe" (generates redo)
you would have to alter the table to be NOLOGGING to bypass redo generation (and even then, make sure the database supports no logging, the dba can disable that feature)
bad
A reader, May 14, 2004 - 11:48 am UTC
May 15, 2004 - 10:41 am UTC
good
i win?
A reader, July 08, 2004 - 9:10 am UTC
Tom:
Does "INSERT /*+ NOLOGGING APPEND */ INTO TABLE T SELECT * FROM Y " make any sense?
NOLOGGING is not an optimizer hint right? It has to defined at the table right?
Thanks
July 08, 2004 - 9:37 am UTC
it makes sense.
it probably isn't doing what the programmer intended :)
nologging is not a hint, it would be a table attribute.
append makes sense, nologging is just a comment in this case
All indexes set to nologging ?
Neil, July 19, 2004 - 5:15 am UTC
Tom
would setting all indexes to nologging help speed up the import process, as presumably, the DDL used to create the indexes in the target database would be done so with nologging?
What impact would this have in the live (source) database, ie having all indexes altered to be nologgin instead of logging ?
thanks
Neil
July 19, 2004 - 7:55 am UTC
nologging only affects index creates and index rebuilds.
normal day to day insert/update/delete activity is ALWAYS logged.
a DBA might take exception to you doing non-logged operations on their database as they have to schedule a hot backup very very soon after you complete your task.
You need to coordinate with them before doing this (in 9ir2 they can make no-logging a non-option actually -- for standby databases)
Indexes nologging
Neil, July 23, 2004 - 5:43 am UTC
>>nologging only affects index creates and index rebuilds.
So once created, altering an index to nologging has no effect.The maintenance of the index by Oracle does not change, and gets logged ?
I was just thinking that if all the indexes were set to nologging, then when I do a full exportof the database,
the indexes would be built nologging during the import into
our development database, and therefore speed up the operation a bit. I was thinking of doing this rather than creating a sperate indexfile and changing the clauses to nologging. Does this make sense ?
Thanks
Neil
July 23, 2004 - 8:53 am UTC
that is correct, the index maintanence during insert/update/delete is always logged.
You are correct in your thinking the index statement would be generated with "nologging" and the import would not create redo for the index if the database permits it.
Confimation
Muhammad Riaz Shahid, August 12, 2004 - 11:30 am UTC
Specifying Nologging is usefull for a database running in archive mode .... right ?
August 12, 2004 - 11:38 am UTC
it only makes sense in an archive log mode since in noarchive log mode, anything that doesn't need to generate redo won't.
so, in noarchivelog, insert /*+ append */ won't generate redo. In archivelog it will -- unless the table is nologging, but even then nologging can be ignored if the dba said "force logging"
Direct Path Insert in LOGGING mode
Charu, September 08, 2004 - 3:51 am UTC
Hi Tom,
Can any performance benefit be obtained by using direct path insert instead of conventional insert IN LOGGING MODE?
I did a few quick tests and at the end of SQL statements are my findings (Not conclusions):
<NOTE: DB is in ARCHIVELOG mode>
alter table tb1 logging;
alter table tb2 logging;
begin runstats_pkg.rs_start; end;
insert into tb1 select * from all_objects;
commit;
begin runstats_pkg.rs_middle; end;
insert /*+ APPEND */ into tb2 select * from all_objects;
commit;
begin runstats_pkg.rs_stop; end;
I ran these multiple times and what I found was:
-- Negligble difference in time taken by conventional and direct path inserts in LOGGING mode are
-- Negligible difference in redo generated by the two inserts.
-- Direct path insert takes significantly less number of latches compared to conventional path (obviously due to it's way of operation).
-- Direct path insert should consume a lot more PGA. Didn't check this but a guess.
Given the findings above, my hunch is that the direct path insert *might* scale better than conventional path.
Looking for your definitive advice.
Thanks & regards,
Charu.
September 08, 2004 - 9:57 am UTC
check out the concepts guide, it goes into this in some detail.
direct path inserts can be done by a single session. (so scale up with users, eh, no)
they always write above the high water mark (will NOT reuse space).
you cannot read from a table you direct path insert into until you commit.
If you are doing large bulk loads, they may make sense.
Direct mode insert in D/W environments
Charu, September 08, 2004 - 10:44 am UTC
Thanks Tom.
Yes I know a little about the constraints that you have mentioned. I was thinking in terms of *size* of data when I said 'scale' - my mistake. (Does the term 'scale' not include increase in data?)
Ours is a D/W environment and I am rewriting a batch process with lots of INSERT AS SELECTs. So these constraints you mentioned won't hamper the scenario. However, the DBA wouldn't accept NOLOGGING, so I am thinking of trying this.
So back to the original question - can direct path insert be used for enhancing performance even in LOGGING mode, given the other constraints won't cause problem?
A little more than yes/no/maybe please, Tom.
Thanks & regards,
Charu.
create table with nologgin option
Sean, October 16, 2004 - 8:28 pm UTC
Hi Tom,
I don't why it still generated a lot of archive log files even I used nologging;
>create table audit_data_temp as select * from audit_data nologging;
Thanks so much for your help.
Sean
October 16, 2004 - 8:36 pm UTC
create table audit_data_temp
as
select *
from audit_data I_AM_A_CORRELATION_NAME_NOT_A_KEYWORD;
now does it make more sense? nologging was a big old "nothing" the way you used it.
ops$tkyte@ORA9IR2> @mystat "redo size"
ops$tkyte@ORA9IR2> set echo off
NAME VALUE
------------------------------ ----------
redo size 3372608
ops$tkyte@ORA9IR2> create table t as select * from all_objects <b>NOLOGGING;</b>
Table created.
ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> set echo off
NAME V DIFF
------------------------------ ---------- ----------
redo size 6591036 <b> 3218428</b>
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2> @mystat "redo size"
ops$tkyte@ORA9IR2> set echo off
NAME VALUE
------------------------------ ----------
redo size 6625532
ops$tkyte@ORA9IR2> create table t <b>NOLOGGING</b> as select * from all_objects;
Table created.
ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> set echo off
NAME V DIFF
------------------------------ ---------- ----------
redo size 6696156 <b>70624</b>
ops$tkyte@ORA9IR2>
<b>just like in real estate, it is all about location, location, location...</b>
failed to extend rollback segment
Sanjay Talati, February 01, 2005 - 5:32 am UTC
hi Tom,
First of all thanks for such prompt and apt replies...
I have an error
ORA-01562: failed to extend rollback segment number 8
ORA-01650: unable to extend rollback segment PRBS04 by 2560 in tablespace ROLLBACK_SPACE1
this error occurs while i use sql loader to load bulk data into table...
could u please guide me as to whats the reason for this... and what could be the possible solution..
Thanks a lot in advance
Sanjay Talati
February 01, 2005 - 8:54 am UTC
"u" is out sick today. I still want to meet that person, "u" is so in demand but we never seem to cross paths.
Ok if "I" answer?
You ran out of rollback, add more space to it (your rollback tablespace),
Your RBS, they are too small (sqlldr commits pretty frequently so you must have tiny RBS's configured)
Questions and doubts
A reader, February 16, 2005 - 8:49 pm UTC
Tom,
Pls. clarify me for the following :
Suppose I do a direct load insert using the /*+ APPEND */ hint, say insert /*+ APPEND */ into t1 select * from t2;
1. Is my understanding shown below correct ?
a. Read the blocks from t1 into the session's PGA
b. Format the data blocks
c. Write the formatted data blocks directly into
the datafiles that comprise the tablespace
containing the table t2.(using data saves that
bypass the buffer cache in the SGA)
2. If 1 is true, is there any way we can control the
data save frequency for this operation?
3. Since the data blocks are read directly into PGA
and formatted and written directly to datafiles
bypassing the SGA and since /*+ APPEND */ is
normally used for large bulk data loads, we should
have sufficient PGA/process memory. Correct? If not
what will happen to the huge INSERT operation.
4. Since this operation does not generate UNDO in
archivelog/noarchivelog mode and also does not
generate REDO in archivelog mode with NOLOGGING
(for databases that support non-loggable
operations), how will rollback operations performed
in these cases (since we do not have undo to
rollback). Does this mean this operation cannot be
rolled back at all?
Please clarify
February 17, 2005 - 7:28 am UTC
1) pretty much
2) not with insert /*+ append */, it'll just keep writing out, but there is no "checkpoint" or "savepoint". SQL either happens or it doesn't. Entirely.
3) it just reads and writes and reads and writes. The size of the load is not really a factor here other then it takes longer to read/write more data. It is not like we load up 10% of the data and then write, we read some, write some.
4) all we need to do is forget the newly added data. We don't advance the high water mark of the table. It is as if the insert never took place because we were writing to a space that no one would think to look at.
How to get the list of objects affected by nologging operations?
Edgar, June 10, 2005 - 9:41 am UTC
Hi,
Let say i need the complete list of objects:
i am unable to recover if crash will happen right now,
assuming i have n-hours old full online backup and all the redo.
Which SQL query to use?
Sincerely,
June 10, 2005 - 10:38 am UTC
look at v$datafile, there are unrecoverable columns in there. any datafile with that filled in has unrecoverable changes in it, all objects in that file are subject to having unrecoverable changes.
Ops, i find already
Edgar, June 10, 2005 - 10:06 am UTC
Sorry, i am too fool/lazy somtimes.
I find the answer: Metalink Note:290161.1
THE GAINS AND PAINS OF NOLOGGING OPERATIONS
Seems, the answer is - no way with simple query:
...
Currently in Oracle 9i and Oracle 10gR1, only the primaryÂ’s database V$DATAFILE view reflects nologging operations..
SELECT NAME, UNRECOVERABLE_CHANGE#,UNRECOVERABLE_TIME
FROM V$DATAFILE;
...
Maybe, audit of user actions can help?
June 10, 2005 - 10:42 am UTC
from dba_extents, you can get a distinct list of potentially affected objects.
if this is a FREQUENT concern of yours (that would be a problem, a bad practice, logging should be done in most every case) separate out objects you do non-logged operations on from the rest.
Thanks for reply,
Edgar, June 20, 2005 - 8:44 am UTC
no, it is not becouse of my poor work.
I am investigating possibility of implementation of physical standby (with read-only windows for reporting) for one running system i am not administrating currently.
Discrepancies between LOGGING and NOLOGGING
Logan Palanisamy, September 12, 2005 - 2:27 am UTC
Tom,
I am trying to convert a LONG column on table T1 with 20 million records into a CLOB column. To start with, I am testing on a small set of records (~15,000).
As expected NOLOGGING generates less redo logs. But takes more time for some unknown reason.
There are four insert statements in my demo below:
1. Regular insert (logging)
2. Insert /*+ APPEND / (nologging) using TO_LOB
3. Insert /*+ APPEND / (nologging) using rpad(*,10000,*)
4. Insert /*+ APPEND / (nologging) using rpad(*,50000,*)
My questions are:
1. Why is the second insert taking more time than the first even though it generates less redo log than the first?
2. Why is the second insert generating more redo log than the third and fourth inserts? Under nologging, shouldn't all insert/*+ APPEND*/ generate the same amount of redo? The interesting thing here is, the third and fourth inserts generate the same amount of redo.
Thanks.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for Solaris: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> select extent_management, allocation_type from dba_tablespaces where tablespace_name = (select default_tablespace from user_users);
EXTENT_MAN ALLOCATIO
---------- ---------
LOCAL UNIFORM
SQL>
SQL> drop table T2;
Table dropped.
SQL> create table t2 (id number, NOTES CLOB);
Table created.
SQL> desc T1
Name Null? Type
--------------------------------------------- -------- ------------
FNAME VARCHAR2(31)
LNAME VARCHAR2(31)
MNAME VARCHAR2(31)
STREET VARCHAR2(51)
CITY VARCHAR2(31)
ZIP VARCHAR2(11)
STATE VARCHAR2(3)
CTIME NOT NULL DATE
NOTES LONG
SQL> desc t2
Name Null? Type
--------------------------------------------- -------- ------------
ID NUMBER
NOTES CLOB
SQL>
SQL> select table_name, logging from user_tables where table_name like 'T2';
TABLE_NAME LOG
------------------------------ ---
T2 YES
SQL> select table_name, logging from user_lobs where table_name like 'T2';
TABLE_NAME LOG
------------------------------ ---
T2 YES
SQL>
SQL> select count(*), sum(bytes) from user_extents where segment_name = 'T2';
COUNT(*) SUM(BYTES)
------------ ------------
1 5,242,880
SQL> set autotrace traceonly timing on
---- The first insert
SQL> Insert into T2 select rownum, to_lob(p.NOTES) from T1 p where p.ctime > trunc(sysdate) - 24;
14489 rows created.
Elapsed: 00:01:26.99
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=84383 Card=762721 By
tes=7627210)
1 0 COUNT
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=84383 Ca
rd=762721 Bytes=7627210)
3 2 INDEX (RANGE SCAN) OF 'T1_CTIME_DESC' (NON-UNIQUE)
(Cost=728 Card=762721)
Statistics
----------------------------------------------------------
819 recursive calls
58205 db block gets
52936 consistent gets
16111 physical reads
95825640 redo size
866 bytes sent via SQL*Net to client
632 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
14489 rows processed
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> set autotrace off timing off
SQL> select max(dbms_lob.getlength(NOTES)), avg(dbms_lob.getlength(NOTES)), count(*) from t2;
MAX(DBMS_LOB.GETLENGTH(NOTES)) AVG(DBMS_LOB.GETLENGTH(NOTES)) COUNT(*)
------------------------------- ------------------------------- ------------
40,001 3,220 14,489
SQL>
SQL> select count(*), sum(bytes) from user_extents where segment_name = 'T2';
COUNT(*) SUM(BYTES)
------------ ------------
7 36,700,160
SQL> alter table T2 nologging modify lob (NOTES) (nocache nologging);
Table altered.
SQL> select table_name, logging from user_tables where table_name like 'T2';
TABLE_NAME LOG
------------------------------ ---
T2 NO
SQL> select table_name, logging from user_lobs where table_name like 'T2';
TABLE_NAME LOG
------------------------------ ---
T2 NO
SQL> set autotrace traceonly timing on
---- The second insert
SQL> Insert /*+ APPEND */ into T2 select rownum, to_lob(p.NOTES) from T1 p where p.ctime > trunc(sysdate) - 24;
14489 rows created.
Elapsed: 00:02:48.76
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=84383 Card=762721 By
tes=7627210)
1 0 COUNT
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=84383 Ca
rd=762721 Bytes=7627210)
3 2 INDEX (RANGE SCAN) OF 'T1_CTIME_DESC' (NON-UNIQUE)
(Cost=728 Card=762721)
Statistics
----------------------------------------------------------
662 recursive calls
58182 db block gets
51578 consistent gets
16215 physical reads
27318688 redo size
866 bytes sent via SQL*Net to client
646 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
14489 rows processed
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> set autotrace off timing off
SQL> select max(dbms_lob.getlength(NOTES)), avg(dbms_lob.getlength(NOTES)), count(*) from t2;
MAX(DBMS_LOB.GETLENGTH(NOTES)) AVG(DBMS_LOB.GETLENGTH(NOTES)) COUNT(*)
------------------------------- ------------------------------- ------------
40,001 3,220 28,978
SQL>
SQL>
SQL> select count(*), sum(bytes) from user_extents where segment_name = 'T2';
COUNT(*) SUM(BYTES)
------------ ------------
13 68,157,440
SQL> set autotrace traceonly timing on
---- The third insert
SQL> Insert /*+ APPEND */ into T2 select rownum, rpad('*', 10000, '*') from T1 p where p.ctime > trunc(sysdate) - 24;
14489 rows created.
Elapsed: 00:06:10.20
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=728 Card=762721 Byte
s=5339047)
1 0 COUNT
2 1 INDEX (RANGE SCAN) OF 'T1_CTIME_DESC' (NON-UNIQUE) (
Cost=728 Card=762721 Bytes=5339047)
Statistics
----------------------------------------------------------
657 recursive calls
122938 db block gets
534 consistent gets
27 physical reads
5084696 redo size
866 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
14489 rows processed
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> set autotrace off timing off
SQL> select max(dbms_lob.getlength(NOTES)), avg(dbms_lob.getlength(NOTES)), count(*) from t2;
MAX(DBMS_LOB.GETLENGTH(NOTES)) AVG(DBMS_LOB.GETLENGTH(NOTES)) COUNT(*)
------------------------------- ------------------------------- ------------
40,001 3,480 43,467
SQL>
SQL>
SQL> select count(*), sum(bytes) from user_extents where segment_name = 'T2';
COUNT(*) SUM(BYTES)
------------ ------------
13 68,157,440
SQL> set autotrace traceonly timing on
---- The fourth insert
SQL> Insert /*+ APPEND */ into T2 select rownum, rpad('*', 50000, '*') from T1 p where p.ctime > trunc(sysdate) - 24;
14489 rows created.
Elapsed: 00:06:11.85
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=728 Card=762721 Byte
s=5339047)
1 0 COUNT
2 1 INDEX (RANGE SCAN) OF 'T1_CTIME_DESC' (NON-UNIQUE) (
Cost=728 Card=762721 Bytes=5339047)
Statistics
----------------------------------------------------------
665 recursive calls
122966 db block gets
544 consistent gets
2 physical reads
5086068 redo size
866 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
14489 rows processed
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
SQL> set autotrace off timing off
SQL> select max(dbms_lob.getlength(NOTES)), avg(dbms_lob.getlength(NOTES)), count(*) from t2;
MAX(DBMS_LOB.GETLENGTH(NOTES)) AVG(DBMS_LOB.GETLENGTH(NOTES)) COUNT(*)
------------------------------- ------------------------------- ------------
40,001 3,610 57,956
SQL>
SQL>
SQL> select count(*), sum(bytes) from user_extents where segment_name = 'T2';
COUNT(*) SUM(BYTES)
------------ ------------
13 68,157,440
SQL> spool off
September 12, 2005 - 7:26 am UTC
nologging is doing direct path writes, you are not writing the data to the buffer cache. so, you are waiting for the physical IO to complete.
for 20,000,000 records, that probably won't matter anyway -- parallel direct path will likely be faster than filling up the buffer cache and waiting for dbwr to write them out.
You generally won't find redo generation to be your bottleneck (common misconception). You generate redo and LGWR writes it, but you don't wait for LGWR to write it until you commit - LGWR does it in the background.
direct path load between NOLOGGING and regular
Logan Palanisamy, September 12, 2005 - 7:07 pm UTC
Tom,
I understand what you are saying about direct-path-load/insert-append bypassing the buffer cache.
But what I don't understand is why the direct-path insert with NOLOGGING (the second insert in the example below) takes more time than the similar one under normal logging. (the first insert append).
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> select extent_management, allocation_type from dba_tablespaces where tablespace_name = (select default_tablespace from user_users);
EXTENT_MAN ALLOCATIO
---------- ---------
LOCAL UNIFORM
SQL>
SQL> drop table T2;
Table dropped.
SQL> create table t2 (id number, resume CLOB);
Table created.
SQL>
SQL> select table_name, logging from user_tables where table_name like 'T2';
TABLE_NAME LOG
------------------------------ ---
T2 YES
SQL> select table_name, logging from user_lobs where table_name like 'T2';
TABLE_NAME LOG
------------------------------ ---
T2 YES
---- The first insert with LOGGING.
SQL> set autotrace traceonly timing on
SQL> Insert /*+ APPEND */ into T2 select rownum, to_lob(p.resume) from person p
2 where p.ctime > trunc(to_date('18-AUG-2005', 'DD-MON-YYYY'));
14489 rows created.
Elapsed: 00:00:53.49
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=4 Card=2 Bytes=20)
1 0 COUNT
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PERSON' (Cost=4 Card=2
Bytes=20)
3 2 INDEX (RANGE SCAN) OF 'PERSON_CTIME_DESC' (NON-UNIQUE)
(Cost=3 Card=2)
Statistics
----------------------------------------------------------
740 recursive calls
58206 db block gets
52907 consistent gets
16210 physical reads
95820252 redo size
857 bytes sent via SQL*Net to client
672 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
14489 rows processed
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> alter table T2 nologging modify lob (resume) (nocache nologging);
Table altered.
SQL> select table_name, logging from user_tables where table_name like 'T2';
TABLE_NAME LOG
------------------------------ ---
T2 NO
SQL> select table_name, logging from user_lobs where table_name like 'T2';
TABLE_NAME LOG
------------------------------ ---
T2 NO
SQL>
SQL> alter system switch logfile;
System altered.
---- The Second Insert with NOLOGGING turned on.
SQL> set autotrace traceonly timing on
SQL> Insert /*+ APPEND */ into T2 select rownum, to_lob(p.resume) from person p
2 where p.ctime > trunc(to_date('18-AUG-2005', 'DD-MON-YYYY'));
14489 rows created.
Elapsed: 00:02:02.30
Execution Plan
----------------------------------------------------------
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=4 Card=2 Bytes=20)
1 0 COUNT
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'PERSON' (Cost=4 Card=2
Bytes=20)
3 2 INDEX (RANGE SCAN) OF 'PERSON_CTIME_DESC' (NON-UNIQUE)
(Cost=3 Card=2)
Statistics
----------------------------------------------------------
1534 recursive calls
58182 db block gets
51777 consistent gets
16215 physical reads
27318740 redo size
860 bytes sent via SQL*Net to client
672 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
31 sorts (memory)
0 sorts (disk)
14489 rows processed
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
September 13, 2005 - 11:40 am UTC
conventional path:
o you write to the buffer (don't wait for IO to the table)
o you write to the redo log buffer (don't wait for IO to redo until commit, LGRW is writing this out as you are generating it, so by the time you say commit, most has already been written out)
so, in this small example, you didn't wait for physical IO to the datafile or redo log file really.
direct path:
o you write directly to disk, you wait for that IO to complete - YOU do the writing, not DBWR, not LGWR, you do the writing.
Small example here, you didn't fill the buffer cache, you didn't really have to wait for LGWR.
reader
A reader, September 29, 2005 - 9:40 am UTC
"
that is correct, the index maintanence during insert/update/delete is always
logged.
"
Is there any other way except drop/create indexes to
reduce redo log (logging) due to indexes if deletes to a table takes place in archiving mode.
September 30, 2005 - 7:48 am UTC
nope.
and you wouldn't like it if there was. For it would imply that a simple instance failure during a delete would cause you to rebuild the indexes upon restart.
This is what databases do - protect data.
how does NOLOGGING affect Update ?
R.S.Karthik, February 15, 2006 - 6:23 pm UTC
Hello Tom
Sorry if this is a repeat question.... tried to search in the site but could not find specifically to this....
Setting the table property to NOLOGGING, I was under the impression that it will not generate redo/undo for both inserts and updates.
The insert with /*+ APPEND */ hint works fine with minimal redo generated.
But when I try to update that table, it 's generating a lot of redo.
Below are the details :
--------------------------
16:55:23 SQL> create table test_k (i varchar2(100),j date) nologging;
Table created.
16:56:12 SQL> insert /*+ APPEND */ into test_k select rownum,sysdate from all_objects;
329531 rows created.
Statistics
----------------------------------------------------------
108 recursive calls
15 db block gets
4350300 consistent gets
16866 physical reads
3360 redo size
345 bytes sent via SQL*Net to client
351 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
329531 rows processed
16:58:04 SQL> commit;
Commit complete.
17:00:38 SQL> update test_k set j=sysdate;
329531 rows updated.
Statistics
----------------------------------------------------------
100 recursive calls
337514 db block gets
1074 consistent gets
896 physical reads
83281684 redo size
367 bytes sent via SQL*Net to client
304 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
329531 rows processed
17:01:11 SQL>
-------------------------------
There are no indexes on this table and log_mode is "NOARCHIVELOG" for the database.
Is there a specific reason for Oracle to do it that way ?
any pointers on this is highly appreciated....
Thanks
Karthik.
February 15, 2006 - 9:58 pm UTC
Thanks !
Karthik, February 16, 2006 - 12:02 am UTC
Thanks Tom.... it 's clear now....
Like you have mentioned : "Normal inserts/updates/deletes on the table are logged as normal. nologging
only affects certain operations like direct path loads and such on the object."
But I think the reason people like me, get very confused is because Oracle decided to name the property as "LOGGING" instead of "INSERTLOGGING"....
I believe they named it to take care of future releases where hopefully NOLOGGING would be nologging (or minimal logging) in all cases ! :)
Thanks !
February 16, 2006 - 8:12 am UTC
umm, no - it'll never ever happen.
If you want no logging, please just use grep sed and awk.
databases are born to protect your data, doing what you suggest would be "less than responsible". indexes, tables, whatever - they are complex data structures and must be protected.
In fact however, NOLOGGING does what you asked for already "minimal logging" - we do that. As little as we can, as much as we NEED to.
grep, sed and awk
Karthik, February 17, 2006 - 12:03 am UTC
Thanks Tom....
Your statement about using "grep, sed and awk" kinda made me curious about what you were talking about....
<quote>
If you want no logging, please just use grep sed and awk.
</quote>
Once I went through this link : </code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:744625626787 <code>
I realized what you implied.... very funny and straight to the point :)
I forgot to mention that the database where I was testing was a development instance and was set to NOARCHIVELOG mode.... the production system is in archivelog mode though....
Thanks for the great site !
Karthik
effect of truncate table
A reader, February 21, 2006 - 11:01 am UTC
Is there a difference (in performance) between
INSERT /*+ APPEND */ INTO T1
and
INSERT INTO T1
when I did TRUNCATE TABLE T1 before executing
any of the above INSERTS.
Thanks
February 22, 2006 - 8:01 am UTC
yes. there is. they are very different.
the append does direct IO (which you wait for), bypasses undo generation for the table (which you don't have to wait for), and optionally may bypass redo generation (which on a properly configured system won't affect your runtime performance much at all)
the non-append writes to the buffer cache (which you likely won't wait for unless and until you 'fill it up'), deletes undo (same comment as the buffer cache comment just before this), but will never bypass redo generation.
catproc.sql execution time
Deepak, March 07, 2006 - 7:36 am UTC
Hi Tom,
Have recently been asked to tune the Database creation process.
Previously we were executing the following scripts after creating the database...
ORACLE_HOME
dbmsadmincatalog.sql;
ORACLE_HOME
dbmsadmincatexp7.sql;
ORACLE_HOME
dbmsadmincatblock.sql;
ORACLE_HOME
dbmsadmincatproc.sql;
ORACLE_HOME
dbmsadmincatoctk.sql;
ORACLE_HOME
dbmsadminowminst.plb;
connect SYSTEM/manager
ORACLE_HOMEsqlplusadminpupbld.sql;
connect SYSTEM/manager
ORACLE_HOMEsqlplusadminhelphlpbld.sql helpus.sql;
I tried the following operations:
Removed the following scripts from the above list
> catexp7.sql (As we are not going to use Oracle 7 export utility)
> catoctk.sql (As we do not use Oracle Cryptographic Toolkit package) from our application.
Then took a note of the time taken for execution of each of the above mentioned scripts.
Found that catproc.sql is taking most of the time, i.e., 14 mins.
Also monitored the buffer cache hit ratio and the library cache GETHITRATIO and found that both were under the desired limits (Buffer Pool Hit Ratio ~ 99% & GETHITRATIO of library cache to be above 90%) during the script execution.
Can you please help me in the following...
-- Did I do the right thing in removing the two scripts i.e., catexp7.sql & catoctk.sql.? Does Oracle recommend executing the catoctk.sql script always?
-- How can I tune the database creation process further.
Please help me out.
March 08, 2006 - 4:29 pm UTC
To make a database create go really fast - just restore a seed database from a backup.
That is, just create a database. When you want a new database - restore that one. The time to create a database becomes the time to restore.
why would you be creating databases so often that you need to tune this particular operation?
DB creation tuning
Deepak, March 09, 2006 - 12:40 am UTC
Thanks for your comments...
DBs get created whenever we install our product. So many times we need to create the DB.
Tom how about the scripts part in my previous question. Did I do the right thing in eliminating the said scripts.
Can I take a dump of the database after running the scripts and restore it to the target database after creating the database?
March 09, 2006 - 1:07 pm UTC
use the technique we use - ship with a seed database, restore it.
Tell you what - download Oracle XE from otn.oracle.com and install it, see how fast (about 5 minutes from "click" to "done")
backup of terabyte database with some tables in nologging mode
vijaya, March 16, 2006 - 12:20 am UTC
We have 5 tables in terabyte database in nologging mode. the database is in archive log mode. It is obvious that we cannot take point in time recovery. What is the best method of backup. We take daily incremental backup and weekly full backup. Our storage is EMC and using veritas netbackup.
Please advise best method of backup for this type of terabyte db.
Force logging option is set to no.
March 16, 2006 - 11:35 am UTC
you would need to do a full hot backup of any datafile with an unrecoverable change# in it after (soon, really really soon after) performing that non-logged operation.
I would suggest rethinking the whole "skip logging" thing on any object that means something to you and your business.
Backups with nologging events
Elaine, May 11, 2006 - 1:32 pm UTC
Tom, as usual you provide excellent information! I understand about taking a backup as soon as possible after a nologging activity, but have a strange situation to run by you:
We have a multi-terabyte database where 2 loads of million+ rows occur daily with an additional load added once per week. One runs using insert append and the other 2 run using direct path sqlldr. There are multiple update jobs that process daily.
We tried putting the database in archivelog mode, but the amount of redo activity, despite the nologging activity, was extensive and our connection to the SAN is ridiculously slow, so even though it shouldn't have caused a problem, it was a major performance hit.
Now the database is running in noarchivelog. Backing up is quite a task. The last backup we took involved pausing all the jobs, using transportable tablespaces to backup the main table and its partitioned indexes (about 2.6 Tb) and then using RMAN to back up the database incrementally in a startup mount mode (about 3 hours downtime).
Does that sound like a reasonable approach to you? Our intent is to create a duplicate database that can be switched to in the event of a failure and then we would just "replay" any transactions since the last backup.
Would we be buying anything (but trouble) if we put the database into archivelog mode right before issuing the RMAN command and then going back to noarchivelog when the database has backed up so that it could remain up during those 3 hours -- or is that just a dumb/bad idea?
Any suggestions are greatly appreciated.
May 11, 2006 - 8:01 pm UTC
... and our connection to
the SAN is ridiculously slow ..
you know, that is killing the DIRECT PATH operations then, they write directly to disk!
in noarchivelog mode, you can only backup in a 'cold' fashion.
I don't understand:
...
using transportable
tablespaces to backup the main table and its partitioned indexes (about 2.6 Tb)
and then using RMAN to back up the database incrementally in a startup mount
mode (about 3 hours downtime). ...
what did you mean by "incrementally"?
you could decrease the "down time windows" by
a) shutdown
b) put into archive log mode + startup
c) backup hot
d) switch log files and archive all - back the archives up
e) shutdown
f) put into noarchive log mode + startup
the stuff you get from c and d would be sufficient to restore a consistent backup from.
Poor SAN performance
Elaine H, May 15, 2006 - 2:49 pm UTC
Unfortunately until we move to our new hardware (some undetermined time this year) we are stuck with having the slow performance through the SAN. The insert/append and direct path sqlldr suffer, but it would be worse if we weren't using these tools.
I am using RMAN to do the cold backup. I originally wrote the script for a hot backup, so I wrote the script using incremental level 1 -- which in a cold backup is a contradiction in terms so sorry for the misnomer.
Do I really need to do an explicit log switch if I use RMAN to backup the database in archivelog mode? I remember reading somewhere that when you issue the backup archivelogs command following the backup, it automatically does the log switch to make the backup consistent.
"At the beginning of every BACKUP ... ARCHIVELOG command that does not specify an UNTIL clause, RMAN attempts to automatically switch out of and archive the current online redo log. In this way, RMAN can include the current redo log in the backup set.
If the database is open, then at the start of an archived log backup RMAN tries to switch out of and archive the current online log according to these rules:
RMAN runs ALTER SYSTEM ARCHIVE LOG CURRENT.
If the UNTIL clause or SEQUENCE parameter is specified, RMAN does not try to switch or archive online logs. "
Do I still need to do the explicit switch too?
May 15, 2006 - 3:58 pm UTC
Rman will do the right thing with a command like that.
If you use the "right command" - one that says "we will switch them for you", you would not need to.
index handling before batch inserts to a large table
A Reader, June 17, 2006 - 1:11 am UTC
Hi Tom,
Thanks a buch for the invaluable service to the oracle community ... have some q's about "index handling before batch inserts to a large table"
<quote>
there can be significant advantages AND disadvantages.
some (not all, i'm sure someone will add something else -- they always do)
advantages:
o you can rebuild the index with nologging on (no rollback, no redo for the
index maintenance)
o you can rebuild the index in parallel
o you get a nicely compacted index at the end
some disadvantages:
o if you added 100meg to a 5 gig table, rebuilding the index might take
significant amounts of time
o the index is unavailable -- meaning the table is "offline" sort of during this
entire load/reindex operation (this index just won't be there)
</quote>
<our environment>
oracle 9i R2 - archivelog , all tables - logging
we have a non-partioned fact table with about 90 million rows - each monthly batch cycle adds about 1.5 million rows.
the inserts are done using set base direct path loads :
insert into ... select /*+ append/
</our environment>
<questions>
a) do bitmap indexes have any additional considerations - eg. should always be made unuable before such voluminious inserts and then rebuild - or are the considerations same as B*Tree indexes (specifically in a set base direct path load situation - if for some reason it was a row-by-row insert then i know bitmaps would kill it - and you have to diable them)
b) for such volumes - that is about 90 M rows in table - adding 1.5 million rows in the batch process - can there be a 'generic recommendation'
c) if we go with the approach of diabling (making unusable) and rebuilding - and alter index ... rebuild nologging - and say there is a media failure fater the batch (before a backup is taken) - i am trying to access the impact on recovery - my understanding is - the only thing we may not be able to recover is the indexes build this way - but this will not impact the recovery of other database objects - is that correct ?
d) and lastly - promise - currently this index disabling/enabling is happening for EVERY table in the system - some of them with 500 records ! think its silly to do that for tables less than a threshold number of recs - say approx 500,000 - does this make sense to do index disabling/enabling only if # of rows is above a threshold ?
</questions>
Thanks in advance
June 17, 2006 - 7:10 am UTC
a) never say always, never say never, I always say.
If you do large bulk inserts - bitmaps are maintained very nicely. It would be the slow by slow processing that kills them.
b) not likely. You have the original list of things to consider.
c) you would have to rebuild the index again from the base table, just like you did after the load. Everything else that was logged is OK.
d) threshold - no, see original list of considerations for considerations.
backup after nologging operations
martina, July 14, 2006 - 11:36 am UTC
i accept but i do not really understand that nologging objects cannot be redone.
i create all indexes nologging as i think an index can always be rebuilt.
I also create tables nologging, always as select from ...
this is ok if i can recreate tha table at any time, right?
what i did lately is the following:
create table x nologging as select * from a;
create table y nologging as select * from a;
drop a;
"use" x; drop x;
"use" y; drop y;
Can that lead to a dangerous situation?
we have a job which rebuilds all indexes which habe changed their size, nologging compute statistics. Similar to that i have a job which compresses old tables/table partitions copied from the first job. Now i learned that "alter table .. move nologging" is synonymous to "alter table .. move; alter table nologging". here the nologging is pointless as i'm not performing any --+ append. Correct? or is it harmful?
the database is backed up every night, the jobs doing the creates run all the time so i have no chance get an unscheduled backup.
will these creates be passed to a standby database?
Actually i do not understand what the redo information could be more than the "create table" or "create index" statement itself and so i do not understand how that can be lost.
thank you for your help,
martina
July 14, 2006 - 12:42 pm UTC
YOU can always rebuild your index. True.
However, if you build the index nologging and do not backup the datafiles affected right after that, you cannot RECOVER your index (there is no redo, recovery uses REDO)
Let us look at the table scenario here:
time 0) you "create table t NOLOGGING as select ....."
time 1) it is finished, no redo generated
time 2) the CEO inserts the most important piece of data every created into T
time 3) they commit.
time 4) they go home
time 5) the disk that T is on fails
You have lost the CEO's data, you cannot get it back, you cannot recover table T from backups. You have lost data.
That is the point.
What if...
A reader, July 14, 2006 - 1:23 pm UTC
Comment to your answer above.
A slight change of events.
After, doing a CTAS in nologging, the DBA/developer was smart enough to turn it back into logging mode.
Then the CEO inserted the data, went back home, disk crashes.
Then want........
And the story continues ;)
July 14, 2006 - 1:54 pm UTC
same thing - you STILL cannot recover that table.
The no-logging only affects BULK operations, not normal inserts/updates/deletes.
So, in all cases, the CEO's insert was logged, but unfortunately NOTHING ELSE WAS - so upon recovery - the table is "junk", garbage, no good, lost, zippo, gone, disappeared.
You cannot RECOVER this table from backup since a LARGE PORTION (actually "some portion") of the information needed to recover this table isn't in the redo stream.
Nologging
AD, July 15, 2006 - 4:17 pm UTC
Tom,
The database is in acrhive log mode.
step 1:
1) I create a set of temp tables using CTAS with nologging option
2) I then use these table to update a main table.
1) the tables in step 1 can be re-created if necessary. My question is can I safely use nologging option or it may cause problem for recovery etc.
Regards
July 16, 2006 - 9:34 am UTC
If you don't care about recovering these tables from backups in the event of media failure (eg: you CREATE them, you READ them, and you have the knowledge of how to RECREATE them - no one modifies them), you can do whatever you like, sure.
Table/Partition created NOLOGGING
Arjun, February 07, 2007 - 5:13 pm UTC
Followup:
If you don't care about recovering these tables from backups in the event of media failure (eg: you
CREATE them, you READ them, and you have the knowledge of how to RECREATE them - no one modifies
them), you can do whatever you like, sure.
Oracle 9i (Release 2)
Am I following this correctly (ARCHIVELOG mode database
with FORCED Logging set to NO)?
1. A table is created / or a partiton is added to an existing table
using NOLOGGING clause
2. We keep inserting/deleting/updating data in this table/partiton
3. had a disk failure
4. Oracle does have a record of all the redo/undo for this table..but no record of it's creation? and that is why it (and the data in it) is not recoverable?
Did I get that right?
February 07, 2007 - 7:18 pm UTC
only direct path operations are affected by nologging, your #2 step would always be fully logged.
To Correct this situation
Arjun, February 07, 2007 - 6:06 pm UTC
Also this recovery scenerio (an object that was created as NOLOGGING) will be fixed with next backup correct?
It is just not recoverable from the backup "before" NOLOGGING object creation...
how to findout corrupted indexes
Reader, March 15, 2007 - 3:58 pm UTC
Hi Tom,
We brought up our DEV database with the production copy
after applying 1 day archivelogs and while using we found
out some errors for indexes as they rebuild indexes in nologging.
Is there any way to find out all the indexes which got corrupted because of nologging.
1 -- In the alert log of Dev database we did not find all the blocks information [ but got info for some blocks ] that
these have corruption because of nologging.
-- We could not figure out this behaviour, why there were some blocks not all blocks.
We map given blocks with indexes and rebuild them.
So, just want to know is there any other way to find out all
the indexes.
We did rman check using "backup validate database" it filled
all blocks in v$database_block_corruption but there are
lot of blocks in that view which were not belongs to
any segment.
do you think any other way is there to find out those corrupted indexes if we can not got this information from
production database.
And Why alert log not having all the blocks and rman still
reporting some blocks after rebuild ALL the indexes of database.
March 16, 2007 - 3:01 pm UTC
query v$datafile, it has an unrecoverable date/time in it, it'll tell you if there is unrecoverable data in a file.
1) it would likely never be ALL blocks because you tend to generate redo after the fact and that would be "applied" to the blocks in question making them 'recovered'
if you do a non-logged operation, but later update the table/index so much that enough redo gets generated - you might well have everything you need. it all depends.
NOLOGGING - change in PLAN
Karteek, April 16, 2007 - 10:37 am UTC
Hi Tom,
I am finding differences in Explain plan for a DELETE statement with and without NOLOGGING option. This is looking peculiar to me. Can you please help me...?
--------------------------------------------------------------------------------------------------------------------------------
DELETE /*+ INDEX(XSELL_PRODUCT_OFFER XS_PROOFF_UNIQ) */ FROM XSELL_PRODUCT_OFFER
WHERE (ACCOUNT_KEY, CAMPAIGN_CODE, CHANNEL_CODE, PRODUCT_CODE, XSELL_OFFER_TYPE_CODE, CONSUMER_TYPE_CODE )
IN (SELECT /*+ INDEX(LOOKALIKE_PRODUCT_OFFER LA_PROOFF_UNIQ) */ ACCOUNT_KEY, CAMPAIGN_CODE, CHANNEL_CODE, PRODUCT_CODE, XSELL_OFFER_TYPE_CODE, CONSUMER_TYPE_CODE
FROM LOOKALIKE_PRODUCT_OFFER )
--------------------------------
Explain Plan for below delete statement is
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
DELETE STATEMENT Optimizer Mode=CHOOSE 15 M 16 M
DELETE XSOFR_OWNER.XSELL_PRODUCT_OFFER
NESTED LOOPS 15 M 1G 16 M
INDEX FULL SCAN XSOFR_OWNER.LA_PROOFF_UNIQ 15 M 699 M 137625
INDEX RANGE SCAN XSOFR_OWNER.XS_PROOFF_UNIQ 1 70 3
--------------------------------------------------------------------------------------------------------------------------------
DELETE /*+ INDEX(XSELL_PRODUCT_OFFER XS_PROOFF_UNIQ) */ FROM XSELL_PRODUCT_OFFER NOLOGGING
WHERE (ACCOUNT_KEY, CAMPAIGN_CODE, CHANNEL_CODE, PRODUCT_CODE, XSELL_OFFER_TYPE_CODE, CONSUMER_TYPE_CODE )
IN (SELECT /*+ INDEX(LOOKALIKE_PRODUCT_OFFER LA_PROOFF_UNIQ) */ ACCOUNT_KEY, CAMPAIGN_CODE, CHANNEL_CODE, PRODUCT_CODE, XSELL_OFFER_TYPE_CODE, CONSUMER_TYPE_CODE
FROM LOOKALIKE_PRODUCT_OFFER )
--------------------------------
For this, Explain Plan is
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
DELETE STATEMENT Optimizer Mode=CHOOSE 15 M 3375089
DELETE XSOFR_OWNER.XSELL_PRODUCT_OFFER
HASH JOIN 15 M 1G 3375089
INDEX FULL SCAN XSOFR_OWNER.LA_PROOFF_UNIQ 15 M 699 M 137625
PARTITION RANGE ALL 1 7
PARTITION LIST ALL 1 6
TABLE ACCESS FULL XSOFR_OWNER.XSELL_PRODUCT_OFFER 137 M 8G 1747536 1 42
--------------------------------------------------------------------------------------------------------------------------------
If you observe later doing is doing a full table scan, but the only change is adding NOLOGGING option. To my knowledge NOLOGGING is just to reduce the amount of log that sql statement supposed to generate and is nothing to do with method of execution.
Please help me with your feedback.
Thank you so much!
Karteek
April 16, 2007 - 1:32 pm UTC
delete /*+ index( T T_IDX */ from T where ....
that works because the table is named T and you did not alias it....
delete /*+ index( T T_IDX */ from T MY_ALIAS where ....
will not use the hint, since there is no longer an object named T and
delete /*+ index( T T_IDX */ from T NOLOGGING where ....
which is what you have coded, is the same as using MY_ALIAS
NOLOGGING is not a hint
NOLOGGING is not something you stick in DML
NOLOGGING is part of DDL - you can alter a table - nologging, you can create a table - nologging. You do not use nologging in DML on the table.
You could change NOLOGGING in your delete to any other valid "word" and observe the same behavior
It has nothing to do with nologging, you are not doing a non-logged operation.
Changing from Logging to Nologging and back
A reader, April 17, 2007 - 11:50 am UTC
Tom,
We use this method for archiving our large tables. We rename our live table, create a new table with NOLOGGING and runs a direct insert from the live table into the new one and finally changes the new table to LOGGING mode. This is to supress any redo generation during the process.
Thinking about a recovery scenario, I guess the only part we cannot recover here is our archival stage which is really not required. Does this changing from NOLOGGING to LOGGING cause any issues for us if we need to recover anything at a later stage?
thanks,
April 18, 2007 - 10:36 am UTC
as long as you get a backup of the affected datafiles after the non-logged operation - you are OK from a recovery perspective.
reduce LOG generated
Karteek, April 18, 2007 - 12:02 pm UTC
Thank you so much for reply Tom.
Is there any way to reduce the amount of log that gets generated during the DML operation, especially while DELETING. As you said NOLOGGING can be used while creating a table (DDL), do we have similar option for DMLs also?. We have a scenario where we need to delete about 30 million records from a partitioned table with 350 million records. Your insight would really help us.
Many Thanks!
Karteek
April 18, 2007 - 12:49 pm UTC
make sure to use a single DELETE statement, that'll minimize the redo.
look into truncate (of a partition) if possible.
Nologging - What happens to a standby database
Rama Subramanian G, June 20, 2007 - 2:27 am UTC
Hi Tom
Considering a scenario where a standy database (or dataguard now) exists for a production database and we are doing a nologging operation on a table in that database, will the standby database be back in synch, if we copy the datafile of the table on which nologging operation has been done and copy to the standby database afterwards ?
nologging tables and phyiscal standby issue
Shahram, August 17, 2007 - 7:12 pm UTC
Hi
We have a 3 node RAC , 10gR2 for data warehouse. for our fact tables we have nologging mode for table.
We have physical standby database for this primary database.
DBAs found issue that because of no redo logs being generated, they are not shipped to standby and having problems there.
I am reading in answers from you to enable force_logging.
select force_logging from v$database;
Is this correct? what/how will be the performance and will this resolve physical standby issue?
We are using /*+ APPEND */ and sql loader direct loads in many places in data warehouse.
What are alternative solutions to make DBAs happy while still have nologging ? What is your suggestion?
Thanks
August 22, 2007 - 9:58 am UTC
by setting force logging to true, your nologging request will be silently ignored.
You cannot have both
a) no-logging
b) physical standby (which is done via LOGS)
This is not about making DBAs happy.
This is entirely about having your standby work, if you do no logging, they would have to manually resynch datafiles after all of your loads.
CTAS Vs DP performance
Vijay, November 21, 2008 - 9:15 am UTC
Dear Tom,
In this regard, I always wondered that between CTAS and Direct-Path(DP), which one is highly performant/optimized and hence recommended.
I am asking this because, in one of our applications, developers create lots of temporary tables(not global ones though) using CTAS statements using dynamic
SQL. I was of the opinion that TRUNCATE/insert is a preferred option as it may improve the performance of the code.
Hence, keeping this in mind, I have carried out few sample tests (ensuring that environment is alike for all the three cases
by flushing shared_pool and buffer_cache) which have been detailed below :-
SQL> sho user
USER is "SCOTT"
SQL>
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL>
SQL>
SQL> column SEGMENT_NAME format a15
SQL>
SQL> select segment_name,segment_type,bytes/1024/1024 "Size in MB" from user_segments where
2 segment_name = 'TEST_TABLE';
SEGMENT_NAME SEGMENT_TYPE Size in MB
--------------- ------------------ ----------
TEST_TABLE TABLE 165
SQL>
SQL> select tablespace_name,extent_management,segment_space_management from user_tablespaces
2 where tablespace_name = 'APPL_DATA';
TABLESPACE_NAME EXTENT_MAN SEGMEN
------------------------------ ---------- ------
APPL_DATA LOCAL AUTO
METHOD :- 1
***********
SQL>
SQL> alter session set sql_trace=true;
Session altered.
SQL>
SQL> create table test_table_3
2 tablespace appl_data
3 nologging
4 as
5 select * from test_table
6 /
Table created.
SQL> alter session set sql_trace=false;
Session altered.
After formatting tracefile,using TKPROF, this is what I have found (the o/p has been truncated to enhance readability):-
**********************************************************************************
create table test_table_3
tablespace appl_data
nologging
as
select * from test_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.05 0 0 0 0
Execute 1 1.58 9.17 20235 20512 23780 1579392
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.59 9.22 20235 20512 23780 1579392
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 29 (SCOTT)
Rows Execution Plan
------- ---------------------------------------------------
0 CREATE TABLE STATEMENT MODE: ALL_ROWS
0 LOAD AS SELECT OF 'TEST_TABLE_3'
0 TABLE ACCESS MODE: ANALYZED (FULL) OF 'TEST_TABLE' (TABLE)
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.05 0 0 0 0
Execute 3 1.58 9.17 20235 20512 23780 1579392
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 1.59 9.22 20235 20512 23780 1579392
Misses in library cache during parse: 2
Misses in library cache during execute: 1
**********************************************************************************************
METHOD -> 2
***********
Now, I will try carrying out the same operation albeit using truncate/direct-path insert method:-
PLease note that before proceeding, both "shared pool" and "buffer cache" have been flushed to maintain sanctity of the environment.
SQL> truncate table test_table_3;
Table truncated.
SQL>
SQL> alter session set sql_trace=true;
Session altered.
SQL> insert /*+ append */ into test_table_3
2 select * from test_table;
1579392 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> alter session set sql_trace=false;
Session altered.
The trace file output has been presented below:-
insert /*+ append */ into test_table_3
select * from test_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.08 0.10 3 3 1 0
Execute 1 1.69 9.22 20245 20508 22904 1579392
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.77 9.33 20248 20511 22905 1579392
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 29 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
1 LOAD AS SELECT (cr=21891 pr=20267 pw=20225 time=9426983 us)
1579392 TABLE ACCESS FULL TEST_TABLE (cr=20252 pr=20238 pw=0 time=3166713 us)
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT MODE: ALL_ROWS
1 LOAD AS SELECT OF 'TEST_TABLE_3'
1579392 TABLE ACCESS MODE: ANALYZED (FULL) OF 'TEST_TABLE' (TABLE)
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.08 0.11 3 3 1 0
Execute 4 1.69 9.23 20245 20509 23294 1579392
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 1.77 9.34 20248 20512 23295 1579392
Misses in library cache during parse: 2
**********************************************************************************************
METHOD -> 3
***********
Finally, following is the repetition of previous method with a slight difference that table has now been truncated using "reuse storage" clause.
This has been done so that storage associated with the segment is not released to the tablespace.
After flushing "shared pool" and "buffer cache"....
SQL> truncate table test_table_3 reuse storage;
Table truncated.
SQL>
SQL> alter session set sql_trace=true;
Session altered.
SQL> insert /*+ append */ into test_table_3
2 select * from test_table;
1579392 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> alter session set sql_trace=false;
Session altered.
SQL>
Analyzing the trace file output:-
insert /*+ append */ into test_table_3
select * from test_table
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.09 0.11 3 3 1 0
Execute 1 1.53 9.99 20421 20434 20962 1579392
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.62 10.11 20424 20437 20963 1579392
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 29 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
1 LOAD AS SELECT (cr=20436 pr=20422 pw=20225 time=10011161 us)
1579392 TABLE ACCESS FULL TEST_TABLE (cr=20252 pr=20237 pw=0 time=3166719 us)
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT MODE: ALL_ROWS
1 LOAD AS SELECT OF 'TEST_TABLE_3'
1579392 TABLE ACCESS MODE: ANALYZED (FULL) OF 'TEST_TABLE' (TABLE)
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.09 0.11 3 3 1 0
Execute 4 1.53 10.01 20422 20435 21352 1579392
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 1.62 10.13 20425 20438 21353 1579392
Misses in library cache during parse: 2
Misses in library cache during execute: 1
***********************************************************************
Now, I'm now bit confused after seeing these results as I was expecting method-3 to be the more efficient of all.
So because, Oracle need not have to start reallocating the storage from scratch. Accepted that tablespace is LMT wherein this has
very little bearing..but still..
So, unfortunately it's not the case as Method-1 tops the list with least cpu,disk i/o,query consumptions.
Can you please put forward your views on the same.
November 24, 2008 - 2:19 pm UTC
I would say all three took the same time, the differences are so tiny - you would have to run a couple of dozen of each, average them out.
call count cpu elapsed disk query current rows
------- ----- ----- -------- ----- ------ ---------- ----------
total 2 1.59 9.22 20235 20512 23780 1579392
total 2 1.77 9.33 20248 20511 22905 1579392
total 2 1.62 10.11 20424 20437 20963 1579392
They are all so close as to be the same if you ask me.
and actually, the last one did the "least" work logical IO wise (buffer cache is never devoid of results, it doesn't make sense truth be told to flush stuff)
Direct Path in 10g
Sagar, December 03, 2008 - 3:53 pm UTC
Hi Tom,
I ran your followup example (May 30, 2002 - 4pm US/Eastern) in oracle 10g RAC db and got different results.
Please see my example below.
For table t1t, the amount of redo generated is almost same.
Table t1t_nolog created with nologging options shows less redo when append hint is used.
Please clarify why redo generation is not decreasing for table t1t with append hint.
Thanks
Sagar
SQL*Plus: Release 9.2.0.3.0 - Production on Wed Dec 3 12:17:38 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
sql> create table t1t as select * from all_objects where 1=0;
Table created.
sql> set autotrace on statistics;
sql> insert /*+ append */ into t1t select * from all_objects;
44896 rows created.
Statistics
----------------------------------------------------------
5975 recursive calls
1136 db block gets
245681 consistent gets
53 physical reads
5154688 redo size <<<--------------------------
491 bytes sent via SQL*Net to client
479 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
44896 rows processed
sql> rollback;
Rollback complete.
sql> insert into t1t select * from all_objects;
44896 rows created.
Statistics
----------------------------------------------------------
5058 recursive calls
11269 db block gets
247330 consistent gets
0 physical reads
5062784 redo size <<<--------------------------
507 bytes sent via SQL*Net to client
465 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
44896 rows processed
sql> create table t1t_nolog nologging as select * from all_objects where 1=0;
Table created.
sql> insert /*+ append */ into t1t_nolog select * from all_objects;
44897 rows created.
Statistics
----------------------------------------------------------
5931 recursive calls
1140 db block gets
245670 consistent gets
0 physical reads
46496 redo size <<<--------------------------
495 bytes sent via SQL*Net to client
485 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
44897 rows processed
sql> rollback;
Rollback complete.
sql> insert into t1t_nolog select * from all_objects;
44897 rows created.
Statistics
----------------------------------------------------------
5058 recursive calls
5675 db block gets
246532 consistent gets
0 physical reads
5062480 redo size <<<--------------------------
508 bytes sent via SQL*Net to client
471 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
44897 rows processed
sql> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
sql> select table_name, logging from user_tables where table_name like 'T1T%';
TABLE_NAME LOG
------------------------------ ---
T1T YES
T1T_NOLOG NO
sql>
December 09, 2008 - 11:09 am UTC
I would expect the output you received. What exactly did you read on this page that makes you think it would have been different? I might need to make something more clear if it resulted in confusion....
In noarchivelog mode - direct path loads bypass undo on the table AND redo on the table.
In archivelog mode - direct path loads bypass undo on the table BUT MUST generate redo for the table. There might be a little less redo generated since UNDO generates redo as well - but that depends.
In archivelog mode, a NOLOGGING table behaves like any table in noarchivelog mode would with respect to redo using a direct path insert.
Thanks for the clarification.
Sagar, December 10, 2008 - 2:39 pm UTC
Deleting Records in Nologging Mode.
Yaseen AR, June 30, 2010 - 3:47 am UTC
Tom,
In my Production Server I want to Delete Million of Records. When Ever I am deleting it take 1 hour. In that time every application getting hang (near to that).
My Database running in Archivelog mode. So 200 more archive files are generating in that time. and also I am running out of space in that time.
So with out generating Archive log files Can you suggest me how to delete the record.
Becuase of production
1)I cant remove(Change) the archive log for Temporarily.
2) Removing the Index it little bit though (Management not agreed)
3) I read few OTN Forum Discussion Nologging option wll not work for Delete.
4) Tell me how i can Over come this Issue.
If my Engllish is Bad or my Question is very simple
Apologize for that.
The Discussion I read fully but not clear about Deleting.
Regards
Yaseen AR
July 06, 2010 - 2:02 pm UTC
... In that time every application getting hang ( ...
why? Unless those applications are trying to update records you are deleting - or unless your machine is so small it cannot really do more than one thing - they should not.
You cannot delete "no logging", it does not ever happen that way.
To make this more efficient you would look to organizing the data to be more easily purged. For example, if you "delete from t where dt_column < sysdate-30", you could partition by dt_column and simply truncate the data to purge (that can be done without generating redo/undo).
Log switches during large load?
Ditto, March 10, 2011 - 8:14 am UTC
Hi Tom,
I thought I understood this, however, there's obviously something I'm still missing, I was hoping you (or anyone else if they know?) had a minute to look this over and offer any suggestions ..
Goal: (ie the "Why"):
I am working in a large conversion project at the moment where we are bringing in a large amount of data into our system from another company we merged with. I am still testing in DEV trying to establish a process that works consistently with the largest table we have.
The DEV database is in NOARCHIVELOG, however, our PROD database is in ARCHIVELOG mode.
The setup:
The data we're bringing in is in a table in a temp schema and is about 6G in size (yeah, ok, I know, that's not *that* big ...) :)
And we are trying to load this data into are permanent table which is currently about 90G. This table also has a PK, which is about 25G.
We've already queried and verified no duplicates on this key (we used the sequence, in fact, to generate the new numbers and records in this loader table .. so now all we need to do is push it over).
The table (unfortunately) is not partitioned, so no "partition-magic" possible .. (that bugs me, I've been trying to get this table, and a few others partitioned for a few years now ... oh well ..)
The table also has a 50G index, for now, however, I have dropped it ... (it blew out my UNDO, so first test is without the Index, I'll rebuild it later. Second test, I'll increase the UNDO and see how much I need .. ) :)
So yeah, summary:
90G table + 25G PK.
6G data being pushed in via:
insert /*+ append parallel(t,4) */ into target_tbl t
select * from source_tbl s;
Earlier tests on smaller cases (~20G) took about 15 min ... so I'm expecting this one to take up to 2 hrs ... maybe 3 ..
I left it running the other night for almost 20 hrs .
What I noticed after further investigation, in the ALERT log, was it was doing a log switch every 2-3 minutes ..
So my question:
- I'm in NOARCHIVELOG
- I've run "alter table target_tbl nologging" and "alter index target_tbl_pk nologging" (mostly for script consistency for when we go to PROD)
- using APPEND hint ...
- no other significant processes running on system
- 10 REDO log groups (2 logs in each group), each 1G size
- target table + PK are in 2 different tablespaces, both LOCALly managed.
- Target table's tablespace: uniform size: 1G
- Target table's PK tablespace: uniform size: 100M
oh wait ... looking at dba_tablespaces ... those tablespaces are set to "LOGGING" ... is that my problem? (but again, I'm in NOARCHIVELOG mode ... ??)
That's all the info I can think of ... what else could be causing the redo log switches, considering everything I've setup above?
Hope you have some ideas ...
Thanks!
March 10, 2011 - 10:47 am UTC
The DEV database is in NOARCHIVELOG, however, our PROD database is in
ARCHIVELOG mode.
probably shouldn't be - yes, I know it is something extra to manage - but then so is "building and testing on something that doesn't represent real life". It is less to have to manage the archives than to try to fix something in real life in my experience.
Index maintenance from an INSERT (yes, even direct path), UPDATE, DELETE, or MERGE must always be logged.
With an INSERT /*+ APPEND */ - what happens (ASSUMING THE APPEND WAS OBEYED, foreign keys/triggers would make us ignore it) is:
a) the data is loaded above the high water mark - UNDO and REDO can be skipped
b) the newly loaded data is temporarily indexed into temp - UNDO and REDO can be skipped
c) when the load is finished loading the tables, the mini indexes generated in step (b) are merged into the existing indexes.
Step (c) will always - always - generate REDO and UNDO for this operation. The existing indexes are being modified now - their structure MUST be recoverable. if we failed during the merge - the indexes MUST be able to be restored.
If you want no redo, no undo, you will have to disable the indexes - load - and then rebuild the indexes. That might take longer than doing it the way you are currently (when you add a little to a lot - it is many times faster just to maintain indexes than to have to rebuild them all later)
Thanks!
Ditto, March 10, 2011 - 11:01 am UTC
Gotcha .. thank-you for your time!