Home » RDBMS Server » Performance Tuning » Nologging Feature in Archivelog mode : strange Results (Oracle 9.2.0.6.0 on HP-UX)
Nologging Feature in Archivelog mode : strange Results [message #433166] Mon, 30 November 2009 08:34 Go to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hi,

I am trying to understand the Nologging feature, but I am unable to undestand how come this value 8528 appear as shown below?

The Direct Path Insert as well as Normal Insert statement shall generate same amount of redo if the table is in logging mode and database in archivelog mode, Isnt' it?

In following example every insert is inserting '8 rows' through out the example :


Archivelog	
-----------
Table logging	
insert /*+ append */ into emp select * from emp; 8528 bytes
insert into emp select * from emp;	         744 bytes

Table Nologging	
insert /*+ append */ into emp select * from emp;   296 bytes
insert into emp select * from emp;	           744 bytes


NoArchivelog
------------	
Table logging	
insert /*+ append */ into emp select * from emp;  396 bytes
insert into emp select * from emp;	          796 bytes

Table Nologging	
insert /*+ append */ into emp select * from emp;   396 bytes
insert into emp select * from emp;	           796 bytes



Thanks and Regards,
Chetana
Re: Nologging Feature in Archivelog mode : strange Results [message #433168 is a reply to message #433166] Mon, 30 November 2009 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to show us what you did to get these numbers.
You have to show us the setup.
We should be able to reproduce your test.

Quote:
The Direct Path Insert as well as Normal Insert statement shall generate same amount of redo if the table is in logging mode and database in archivelog mode, Isnt' it?

No. Oracle has not to protect old blocks in direct mode as old blocks do not exist. In normal mode you generate redo for data blocks and undo blocks.

Regards
Michel
Re: Nologging Feature in Archivelog mode : strange Results [message #433172 is a reply to message #433166] Mon, 30 November 2009 08:55 Go to previous messageGo to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Sorry Michel, I should have posted the related scripts as well

In fact I am using Tom's(Kyte) scripts mystat.sql and mystat2.sql

First I am creating emp table with 8 rows on each of the databases (one in archivelog mode and other in Noarchivelog mode)
as follows
CREATE TABLE EMP
      (EMPNO NUMBER(4) NOT NULL,
       ENAME VARCHAR2(10),
       JOB VARCHAR2(9),
       MGR NUMBER(4),
       HIREDATE DATE,
       SAL NUMBER(7, 2),
       COMM NUMBER(7, 2),
       DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

commit;


Then I am executing each case of logging/nologging,direct-path insert / normal insert etc
At the end of each Test case I am issuing 'Rollback' and 'exit' to get same environment and stats at start of each session

SQL>  @/u05/scripts/mystat.sql "redo size"

old   4: and lower(a.name) like '%'||lower('&S')||'%'
new   4: and lower(a.name) like '%'||lower('redo size')||'%'

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                                 608

SQL> insert /*+ append */ into emp select * from emp;

8 rows created.

SQL> @/u05/scripts/mystat2.sql

old   1: Select a.name, b.value, b.value-&V diff
new   1: Select a.name, b.value, b.value-       608 diff
old   4: and lower(a.name) like '%'||lower('&S')||'%'
new   4: and lower(a.name) like '%'||lower('redo size')||'%'

NAME                                                    VALUE       DIFF
-------------------------------------------------- ---------- ----------
redo size                                                9136       8528

SQL> rollback;

Rollback complete.

SQL> exit



Tom's Script for Reference (though I am sure everybody must be knowing)

Name : mystat.sql
-----------------
Column value new_val V
Define S="&1"
Col name format a50
Set feedback off
Prompt
Select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%'||lower('&S')||'%'
/
Prompt
Set feedback on

Name : mystat2.sql
------------------
Col name format a50
Set feedback off
Prompt
Select a.name, b.value, b.value-&V diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%'||lower('&S')||'%'
/
Prompt
Set feedback on



Thanks and Regards,
Chetana
Re: Nologging Feature in Archivelog mode : strange Results [message #433280 is a reply to message #433166] Tue, 01 December 2009 03:29 Go to previous messageGo to next message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hi Michel,

Is anything missing in the test data provided by me?

Thanks and Regards,
Chetana
Re: Nologging Feature in Archivelog mode : strange Results [message #433282 is a reply to message #433280] Tue, 01 December 2009 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No but I can't reproduce your behaviour with 10.2.0.4, you use a 9.2.0.6 maybe a too old version to take time to investigate on it.
Also you use too few data. 8K is tiny, maybe the side effect of something else, take an example with thousand or better 1000000 rows.

Regards
Michel
Re: Nologging Feature in Archivelog mode : strange Results [message #433318 is a reply to message #433166] Tue, 01 December 2009 08:56 Go to previous message
chetanaZ
Messages: 132
Registered: October 2009
Location: UK
Senior Member
Hi Michel,

You are correct
The data wasn't enough to run the Test Case

Now I am getting consistent results

I populated 131072 rows in emp and then conducted 2 rounds of each test case

However even if table is set to Nologging in Archivelog mode, Direct Path Inserts are generating 10 times more redo than in Noarchivelog mode.

What could be that?

Following are the results


Archivelog	
-----------
Table logging					    
insert /*+ append */ into emp select * from emp; 
Round1  Round2
6541448	6541448
insert into emp select * from emp;	         
6723656	6735400

Table Nologging	
insert /*+ append */ into emp select * from emp;   
14936	14888
insert into emp select * from emp;	           
6805072	6735384


NoArchivelog
------------	
Table logging	
insert /*+ append */ into emp select * from emp;  
1496		1448
insert into emp select * from emp;	          
6730728	6733896

Table Nologging	
insert /*+ append */ into emp select * from emp;   
1448		1448
insert into emp select * from emp;	           
6734224	6735512


Thanks and Regards,
Chetana
Previous Topic: After Version Enabled (Workspace Manager)
Next Topic: DBMS_OUTLINE to override HINTS [Merged]
Goto Forum:
  


Current Time: Tue Dec 06 12:31:42 CST 2016

Total time taken to generate the page: 0.10448 seconds