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  |
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 #433172 is a reply to message #433166] |
Mon, 30 November 2009 08:55  |
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 #433318 is a reply to message #433166] |
Tue, 01 December 2009 08:56 |
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
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 12:33:59 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
