| nologging issue [message #521558] |
Wed, 31 August 2011 02:40  |
andy huang
Messages: 498
Registered: July 2011
|
Senior Member |
|
|
Dear all,
If a table has set nologging,i know it does not generate redo log when insert operation,but i don't know whether it generate undo data when insert operation.
|
|
|
|
|
|
|
|
| Re: nologging issue [message #521567 is a reply to message #521566] |
Wed, 31 August 2011 03:44  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I'll have a go a summarizing this. All normal DML generates redo and undo. Punkt.
For a direct load insert you can (optionally) disable redo with NOLOGGING. Direct load inserts never generate undo, because the inserts are above the segment highwater mark. So if you issue a COMMIT, the hwm is moved; if you issue a ROLLBACK, it isn't.
Is that a fair description?
|
|
|
|
|
|
|
|
| Re: nologging issue [message #521572 is a reply to message #521567] |
Wed, 31 August 2011 04:20 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
John Watson wrote on Wed, 31 August 2011 09:44For a direct load insert you can (optionally) disable redo with NOLOGGING. Direct load inserts never generate undo, because the inserts are above the segment highwater mark. So if you issue a COMMIT, the hwm is moved; if you issue a ROLLBACK, it isn't.
Is that a fair description?
I hadn't realised this, though makes sense since it won't let you query the table until commit/rollback is done.
Although, at the risk of being pedantic, is it not impossible to ever complete lose redo/undo due to things like recursive sql etc and other internal operations creating it?
Certainly it can be minimised though. I could have sworn (and will now have to check) that you'll never get it down to nil.
[Updated on: Wed, 31 August 2011 04:21] Report message to a moderator |
|
|
|
| Re: nologging issue [message #521576 is a reply to message #521572] |
Wed, 31 August 2011 05:15 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
@RC: You right, me wrong! All that internal stuff would have to generate undo/redo.
I wonder how much? For example, if you append a load of rows nologging, then restore and recover, we know the rows won't be there any more - but will the extents still be allocated? Reverse engineering needed....
|
|
|
|
|
|
| Re: nologging issue [message #521661 is a reply to message #521577] |
Wed, 31 August 2011 20:10 |
andy huang
Messages: 498
Registered: July 2011
|
Senior Member |
|
|
hi,
I think it generate undo when insert operation,a test as flowing,I found the undo size change from 62889832 to 75116252.
Create Table Tb_Hxl_Id
(
Id Number
)
nologging;
Declare
Begin
For i In 1 .. 100000000 Loop
Insert/*+ append*/ Into Tb_Hxl_Id Values (i);
End Loop;
End;
Select Value
From v$sysstat a
Where a.name='undo change vector size';
VALUE
----------
62889832
SQL> Select Value
2 From v$sysstat a
3 Where a.name='undo change vector size';
VALUE
----------
68065960
SQL> Select Value
2 From v$sysstat a
3 Where a.name='undo change vector size';
VALUE
----------
75116252
|
|
|
|
|
|
|
|
| Re: nologging issue [message #522381 is a reply to message #521684] |
Fri, 09 September 2011 02:44 |
andy huang
Messages: 498
Registered: July 2011
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 01 September 2011 01:01With this you do not measure the undo you generated but the overall system did.
Query v$sesstat from the interested session.
Regards
Michel
Hi,
The flowing test make sure it generate undo when insertion.
SQL> Select Sid
2 From v$mystat Where Rownum<=1;
SID
----------
145
SQL> Create Table Tb_Hxl_Id
2 (
3 Id Number
4 )
5 nologging;
Table created.
SQL> Declare
2 Begin
3 For i In 1 .. 100000000 Loop
4 Insert/*+ append*/ Into Tb_Hxl_Id Values (i);
5 End Loop;
6 End;
7 /
open other session:
SQL> Select Value
2 from v$sesstat aa
3 Where aa.STATISTIC# = 261
4 And Sid=145;
VALUE
----------
69488564
SQL>
SQL> Select Value
2 from v$sesstat aa
3 Where aa.STATISTIC# = 261
4 And Sid=145;
VALUE
----------
73125380
SQL>
SQL> Select Value
2 from v$sesstat aa
3 Where aa.STATISTIC# = 261
4 And Sid=145;
VALUE
----------
78225948
SQL> Select Value
2 from v$sesstat aa
3 Where aa.STATISTIC# = 261
4 And Sid=145;
VALUE
----------
130852384
|
|
|
|
|
|
| Re: nologging issue [message #522472 is a reply to message #522381] |
Sat, 10 September 2011 05:01 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Nice test! Proves that you can't to a direct load for INSERT...VALUES, what do get if you run it again with INSERT ...SELECT...FROM ?
Also, it is an example of why one must always give the version of the product, and take account of variations: in my database, statistic 261 is very different from yours:orcl> select * from v$statname where statistic#=261;
STATISTIC# NAME CLASS STAT_ID
---------- ------------------------------ ---------- ----------
261 cell smart IO memory bytes hwm 64 3834681635
orcl>orcl> select * from v$statname natural join v$sesstat
2 where name='undo change vector size' and sid=(select distinct sid from v$mystat);
STATISTIC# NAME CLASS STAT_ID SID VALUE
---------- ------------------------- ---------- ---------- ---------- ----------
284 undo change vector size 128 1465971540 22 5020
orcl>
|
|
|
|
| Re: nologging issue [message #522618 is a reply to message #522472] |
Mon, 12 September 2011 20:30 |
andy huang
Messages: 498
Registered: July 2011
|
Senior Member |
|
|
Hi,
I use the insert select operation,and found it generate undo data also,test as flowing:
SQL> Select Sid
2 From v$mystat Where Rownum<=1;
SID
----------
125
SQL> Create Table Tb_Hxl_Id
2 (
3 Id Number
4 )
5 nologging;
Table created.
SQL> Declare
2 Begin
3 For i In 1 .. 100000000 Loop
4 Insert/*+ append*/ Into Tb_Hxl_Id
5 select i from dual;
6 Commit;
7 End Loop;
8 End;
9 /
open other session:
SQL> Select Value
2 from v$sesstat aa
3 Where aa.STATISTIC# = 261
4 And Sid=125;
VALUE
----------
4997476
SQL> Select Value
2 from v$sesstat aa
3 Where aa.STATISTIC# = 261
4 And Sid=125;
VALUE
----------
5761716
SQL> Select Value
2 from v$sesstat aa
3 Where aa.STATISTIC# = 261
4 And Sid=125;
VALUE
----------
6570372
SQL> select * from v$statname where statistic#=261;
STATISTIC# NAME
---------- ----------------------------------------------------------------
CLASS STAT_ID
---------- ----------
261 undo change vector size
128 1465971540
SQL> Select * From v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
|
|
|
|
|
|
| Re: nologging issue [message #522620 is a reply to message #521567] |
Mon, 12 September 2011 21:12 |
andy huang
Messages: 498
Registered: July 2011
|
Senior Member |
|
|
John Watson wrote on Wed, 31 August 2011 03:44I'll have a go a summarizing this. All normal DML generates redo and undo. Punkt.
For a direct load insert you can (optionally) disable redo with NOLOGGING. Direct load inserts never generate undo, because the inserts are above the segment highwater mark. So if you issue a COMMIT, the hwm is moved; if you issue a ROLLBACK, it isn't.
Is that a fair description?
Hi,
The flowing is wrong?
Direct load inserts never generate undo, because the inserts are above the segment highwater mark
|
|
|
|
|
|
|
|
|
|
|
|
| Re: nologging issue [message #522676 is a reply to message #522632] |
Tue, 13 September 2011 02:20 |
andy huang
Messages: 498
Registered: July 2011
|
Senior Member |
|
|
hi,
Select Sid
From v$mystat Where Rownum<=1;
drop table Tb_Hxl_Id;
Create Table Tb_Hxl_Id
(
Id Number
)
nologging;
desc Tb_Hxl_Id_bak;
select count(1) from Tb_Hxl_Id_bak;
Declare
Begin
Insert/*+ append*/ Into Tb_Hxl_Id
select id from Tb_Hxl_Id_bak;
End;
another session,there is generate undo data.
SQL> Select Value
2 from v$sesstat aa
3 Where aa.STATISTIC# = 261
4 And Sid=27;
VALUE
----------
4024
SQL> Select Value
2 from v$sesstat aa
3 Where aa.STATISTIC# = 261
4 And Sid=27;
VALUE
----------
14096
|
|
|
|
| Re: nologging issue [message #522684 is a reply to message #522676] |
Tue, 13 September 2011 02:45 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
create table foo nologging as select * from all_objects where 1=2
/
insert into foo (Select * from all_objects)
/
commit
/
insert /*+ append*/ into foo select * from foo
/
That last line, consistently generates 80 as a value for undo change vector size, if the append hint is removed, it is in the 305000 range, varying upwards as foo grows.
With the append hint there are still recursive calls, I suggest what you are seeing is internal undo generation necessary for oracle to function.
So, then, you cannot disable undo/redo permanently else the database would cease to function. However you can disable both for a single transaction, in isolation, if you ignore recursive calls.
To all intents and purposes a direct nologging load generates no undo and no redo. Don't worry about what the internals are doing unless it's a problem, I assume it is not a problem.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
