How didn't I see that!
Rich, March 06, 2003 - 4:45 pm UTC
Sorry for a stupid question. It was so obvious it almost got me blind...
Rollback
Peter, March 06, 2003 - 6:40 pm UTC
"no difference - in archive log, append surpressed ROLLBACK but NOT redo, "
Tom what is showing you that ROLLBACK was supressed??
March 06, 2003 - 6:53 pm UTC
I know it was, that is why :)
Here is the "tangible evidence"
ops$tkyte@ORA9I> create table t as select * from all_objects where 1=0;
Table created.
ops$tkyte@ORA9I>
ops$tkyte@ORA9I> insert into t select * from all_objects;
44876 rows created.
ops$tkyte@ORA9I> select used_ublk
2 from v$transaction
3 where addr = ( select taddr from v$session where sid =
4 ( select sid from v$mystat where rownum = 1 )
5 );
USED_UBLK
----------
22
ops$tkyte@ORA9I> commit;
Commit complete.
ops$tkyte@ORA9I>
ops$tkyte@ORA9I> insert /*+ APPEND */ into t select * from all_objects;
44876 rows created.
ops$tkyte@ORA9I> select used_ublk
2 from v$transaction
3 where addr = ( select taddr from v$session where sid =
4 ( select sid from v$mystat where rownum = 1 )
5 );
USED_UBLK
----------
1
ops$tkyte@ORA9I> commit;
Commit complete.
That is what append does. It writes above the HWM of the table, bypassing the need for rollback. That is why you cannot query the table after a direct path insert without committing -- it sort of "breaks" our consistent read mechanism for that session on that table.
rollback for inserts is very very small (delete rowid is all that is made)...
Excellent. But why doesn't oracle allow you to read the rows you just inserted?
gs, March 06, 2003 - 8:05 pm UTC
Tom,
I was always wondering, why oracle doesn't allow to read the data inserted thru "direct insert" without doing a commit?
Is it because, oracle doesn't have any info on this insert in the RBS? But how come, it allows me to read after "commit"?
March 07, 2003 - 7:36 am UTC
We don't have the information needed in the RBS for our consistent read mechanism to work (as stated above).
...
That is what append does. It writes above the HWM of the table, bypassing the
need for rollback. That is why you cannot query the table after a direct path
insert without committing -- it sort of "breaks" our consistent read mechanism
for that session on that table
......
Insert /*+APPEND*/ is a "special" thing, a tool that you may or may not choose to use. It does what it does.
If rollback generation was surpressed then why we got the same amount of redo?
Dmitry, March 07, 2003 - 1:33 pm UTC
Hi Tom,
You wrote in the middle of the first reply:
*no difference - in archive log, append surpressed ROLLBACK but NOT redo,*
If rollback generation was surpressed then why we got the same amount of redo as without append?
My understanding (from your book actually) was that redo protects *both data blocks and rollback entries*. As we do not have rollback for the operation we should get less redo. Why the numbers are the same?
Thank you,
March 07, 2003 - 1:39 pm UTC
because in the insert we generated
21 blocks of undo without append
1 blocks of undo with append
and CREATED
ops$tkyte@ORA9I> analyze table t compute statistics for table;
Table analyzed.
ops$tkyte@ORA9I> select blocks from user_tables where table_name = 'T';
BLOCKS
----------
1228
new blocks.
So, in one case we had redo for 1249 blocks, in the other case 1229 blocks.
No significant difference there..... that is why the same amount of redo was generated more or less -- we had about the same amount of blocks to log redo for.
Excellent
Dmitry, March 07, 2003 - 4:00 pm UTC
Yes, number of generated undo blocks times less than created data block. That's why the difference in redo is not significant.
Thank you,
SQL Loader Direct Insert and Append Hint Difference
Nurlan, March 14, 2003 - 7:51 am UTC
Hello Tom, let me continue the discussion. The question is whether any difference between SQL Loader Direct Insert and Insert with the Append Hint.
... SQL*Loader then uses the column array structure to format Oracle data blocks and build index keys. The newly formatted database blocks are then written directly to the database (multiple blocks per I/O request using asynchronous writes if the host platform supports asynchronous I/O).
Is there anything like this for Inserts with append hint?
Namely, will such inserts skip the buffer cache or any preprocessing? I did not find anything on any differences, but they are called the same, direct inserts, or loads.
March 14, 2003 - 6:04 pm UTC
they are not 100% the same but are for all intents and purposes "so similar as to be not really distinguisable by us".
direct path inserts also do direct io.
testing
sam, June 19, 2003 - 2:19 pm UTC
Tom I did some testing as follows
CREATE TABLE A1 unrecoverable parallel (degree 30)
tablespace sdmuser01
as
select Cust_num , 'PFS' as PFS_CD from tdmcustinsight a
where a.rm_lob_code = '03'
and a.pfs_offer_code is not null
/
Elapsed: 00:00:05.66
No rollback .
insert into a1 (cust_num,PFS_CD)
select Cust_num , 'PFS' as PFS_CD from tdmcustinsight a
where a.rm_lob_code = '03'
and a.pfs_offer_code is not null
/
Elapsed: 00:00:14.81
Statistics
----------------------------------------------------------
9313840 redo size
366527 rows processed
rollback usage
NAME USED_KB SID PID SPID
R08 896 67 118 15906
insert /*+ APPEND */ into a1 (cust_num,PFS_CD)
select Cust_num , 'PFS' as PFS_CD from tdmcustinsight a
where a.rm_lob_code = '03'
and a.pfs_offer_code is not null
/
Elapsed: 00:00:12.45
Statistics
----------------------------------------------------------
13760 redo size
366527 rows processed
NAME USED_KB SID PID SPID
R05 16 67 118 15906
CREATE TABLE A1
tablespace sdmuser01
as
select Cust_num , 'PFS' as PFS_CD from tdmcustinsight a
where a.rm_lob_code = '03'
and a.pfs_offer_code is not null
/
Elapsed: 00:00:10.46
insert into a1 (cust_num,pfs_Cd)
select Cust_num , 'PFS' from tdmcustinsight a
where a.rm_lob_code = '03'
and a.pfs_offer_code is not null
/
Elapsed: 00:00:13.11
Statistics
----------------------------------------------------------
9427752 redo size
366527 rows processed
NAME USED_KB SID PID SPID
R04 896 67 118 15906
insert /*+ APPEND */ into a1 (cust_num,pfs_Cd)
select Cust_num , 'PFS' from tdmcustinsight a
where a.rm_lob_code = '03'
and a.pfs_offer_code is not null
/
Elapsed: 00:00:12.43
Statistics
----------------------------------------------------------
36104 redo size
366527 rows processed
NAME USED_KB SID PID SPID
R01 16 67 118 15906
Couple of questions.
When we create a table there is lot of difference in time as against we insert or insert with append hint.
Why is that??
If the answer is no REDO & ROLLBACK , the numbers don't justify the difference.
But if we compare the timing between normal table creation (Without unrecoverable option) & insert then elapsed time
are more meaningful.
So exactly what happens with unrecoverable option ??
Also can you show some light (or link) on parallel option for table creation.
Also if such a minimal rollback is created with append option how we can rollback transaction successfully ???
Thanks
June 20, 2003 - 3:33 pm UTC
a couple of seconds wall clock difference is not meaningful -- you are taking like 2 seconds on the wall clock.
check out the concepts guide, chapter on parallel operations.
read the chapter near the parallel stuff on direct path operations for information on APPEND (append writes straight to disk, bypasses the buffer cache and writes ABOVE the current high water mark for the table -- hence, if you need to rollback, we simply put the HWM back -- data "disappears"
testing
sam, June 20, 2003 - 4:04 pm UTC
"a couple of seconds wall clock difference is not meaningful -- you are taking
like 2 seconds on the wall clock."
What do u mean by that??
Also let me rephrase my question.
If we don't care about redo generation (In read intensive data warehouse) should it be standard practice to insert data using /*+ APPEND */ hint ??
When I say only 2 sec difference what I was referring to was it's not that great as compared to create table with unrecoverable option.
create table 05.66
insert append 12.45
insert normal 14.81
I was expecting insert append timings would be closer to create table timings. Can you throw some light ??
Meantime I am going through concept guide too.
Sam
June 20, 2003 - 5:54 pm UTC
I mean, in the grand scheme of things -- 2 seconds between 10 and 12 seconds is "noise".
maybe the disks spun slower.
maybe someone else was doing something.
your query ran faster one time, slower the next time.
everything will affect that time. You need to use tracing and tkprof to see what's up there.
In a data warehouse, during bulk loads, you can definitely use this feature as long as you BACKUP right afterwards. You can use this in any situation -- as long as you BACKUP.
I just don't observe what you do:
create table t NOLOGGING as select * from all_objects
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 4.23 4.34 0 146054 522 31578
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 4.24 4.36 0 146054 522 31578
insert into t select * from all_objects
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.08 0 0 0 0
Execute 1 4.10 4.79 0 146487 4326 31578
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 4.11 4.88 0 146487 4326 31578
insert /*+ APPEND */ into t select * from all_objects
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.06 0 0 0 0
Execute 1 4.04 4.12 0 146050 123 31578
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 4.05 4.18 0 146050 123 31578
so, I just put it down to "you have other things going on on your system, they are affecting you, things will run slower and faster depending on who is doing what".
A reader, February 09, 2007 - 9:10 am UTC
HI Tom,
If I issue
inset /*+ append */ into test select * from test@other_db;
commit;
Assume about 10,000,000 records loaded. If I later issue:
select * from test, testB where test.A = testB;
And later "snapshot too old" error reported, does this mean that the error is not related with table test, and only related with table testB (since append suppress rollback)?
Thanks
David
February 09, 2007 - 9:28 am UTC
"it depends"
did test have data from before.
indexes on test being used (append relates to the table segment only).
"probably" was testb, likely.
A reader, February 09, 2007 - 9:36 am UTC
Hi Tom,
But if
1. No record in test
2. insert /*+ append */ into test select * from test@other_db;
3. 10,000,000 records loaded.
4. commit;
5. Build indexes (nologging) on test
6. select * from test, testB where test.A = testB.A;
And later "snapshot too old" error reported, does this mean that the error is not related with table test, and only related with table testB (since append suppress rollback)?
Thanks
David
February 12, 2007 - 8:30 am UTC
it is not so much that append surpressed undo as append did a direct path (clean blocks) load and block cleanout would not be necessary.
probably it was testB that was the cause, yes, but we already stated that.
Eric, February 09, 2007 - 5:14 pm UTC
Previous post...
"And later "snapshot too old" error reported, does this mean that the error is not related with table test, and only related with table testB (since append suppress rollback)?"
The rollback suppression is only relevant to your direct path insert. Post-commit, if both tables are subject to DML, each table's blocks are potentially subject to rollback entries? If users start modifying data in TEST and/or TESTB after your insert then your ORA-1555 could be a result of DML against either table, right?
February 12, 2007 - 9:41 am UTC
yes, "probably" this was testb in this particular case.
A reader, February 09, 2007 - 11:37 pm UTC
Hi,
In my case, there is no DML on table TEST after the direct-loading. Moreover, I issue "select count(*) from TEST" after the direct-loading. However, the "snapshot too old" still reported. I want to know is this only related with the other table or with table TEST (TEST has 3 indexes).
Thanks
LOGGING + APPEND HINT = NO REDO ?
Sameer Kumar, September 08, 2012 - 8:23 am UTC
Hi Tom,
You said:
no difference - in archive log, append surpressed ROLLBACK but NOT redo, so...
But there is another post of yours:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5280714813869 Where you say:
Table Mode Insert Mode ArchiveLog mode result
----------- ------------- ----------------- ----------
LOGGING APPEND ARCHIVE LOG redo generated
NOLOGGING APPEND ARCHIVE LOG no redo
LOGGING no append "" redo generated
NOLOGGING no append "" redo generated
LOGGING APPEND noarchive log mode no redo
NOLOGGING APPEND noarchive log mode no redo
LOGGING no append noarchive log mode redo generated
NOLOGGING no append noarchive log mode redo generated
I am a bit confused here.
Also, one of the advantages of using APPEND hint is :
It writes above the HWM of the table,But if I always Truncate a Table with reuse storage and then repopulate using APPEND hint will I get a significant gain compared to Normal (conventional) insert?
September 10, 2012 - 8:13 pm UTC
Table Mode Insert Mode ArchiveLog mode result
----------- ------------- ----------------- ----------
LOGGING APPEND ARCHIVE LOG redo generated
NOLOGGING APPEND ARCHIVE LOG no redo
LOGGING no append "" redo generated
NOLOGGING no append "" redo generated<b>
LOGGING APPEND noarchive log mode no redo</b>
NOLOGGING APPEND noarchive log mode no redo
LOGGING no append noarchive log mode redo generated
NOLOGGING no append noarchive log mode redo generated
I am a bit confused here.
why??? I said:
no difference - in archive log, append surpressed ROLLBACK but NOT redo, so...
but you are pointing to a NOLOGGING (no archive log mode) line???? what confusion could you have. I was talking about "A", you are talking about "B"
But if I always Truncate a Table with reuse storage and then repopulate using APPEND hint will I get a significant gain compared to Normal (conventional) insert?
doubtful, the storage re-allocation would typically be a small portion of the load time.
To Sameer Kumar
Raj, September 12, 2012 - 3:59 am UTC
Also, one of the advantages of using APPEND hint is :
It writes above the HWM of the table,
But if I always Truncate a Table with reuse storage and then repopulate using APPEND hint will I get a significant gain compared to Normal (conventional) insert?
I would assume if you do this you are making the subsequent scans more slower and slower reason behind is that direct path will write above HWM and you will end up making the table segment bigger and bigger as it will never reuse the existing blocks/extents. Atleast this is my understanding about direct path insert.
Tom,
Correct me if I am wrong ?
Thanks
Raj
September 14, 2012 - 6:21 pm UTC
truncate puts the high water mark back down to zero, after a truncate - none of the blocks/extents are under the HWM.
so no, your understanding is wrong. If you replace truncate with delete - then you would be correct.
LOGGING + APPEND HINT = NO REDO ?
Sameer Kumar, September 22, 2012 - 2:08 pm UTC
Hi Tom,
I had actually realized about my "confusing?" comment a few days latter... I was explaining the post to a colleague and realized that my confusion was just a misunderstanding.
About 2nd question APPEND hint has below benefits:
1) Overlooks the HWM
2) No UNDO (which I think will not be as huge for inserts as it is just the rowid info i.e. list of rowids to deleted if rollback occurs)
3) No REDO if table is in nologging mode
Now my recovery strategy does not allow me to use nologging (rather to put it simply I am using forced logging). So in that scenario is APPEND gonna be of huge help? Especially if I always use truncate table before inserting?
September 26, 2012 - 12:58 pm UTC
append does direct path writes, by passing the buffer cache.
it can be significantly faster than using the buffer cache.
it can be significantly slower than using the buffer cache.
in short - it depends.
if you have a massive amount of data to load, direct path is probably the right way to go (consider compressing if appropriate)
if you have a small to medium bit of data, conventional path (buffered) is probably the way to go.