Home » RDBMS Server » Performance Tuning » How to make sure no archivelog being generated for a particular table (11.2.0.2 SE, windows 2008 r2)
How to make sure no archivelog being generated for a particular table [message #652588] |
Tue, 14 June 2016 06:43  |
 |
juniordbanewbie
Messages: 250 Registered: April 2014
|
Senior Member |
|
|
Dear all,
The best reference on this site is http://www.orafaq.com/wiki/Nologging_and_force_logging, but still I could not figure out.....
Production database is generating a big amount of archivelog within small amount of time. I was asked how to reduce the archivelog generation log rate. So I started mining the archivelog to see which tables which generates the most archivelog within the timeframe.
Next I did a test,
CREATE table logging_test(
logging_test_id NUMBER,
logging_test_desc VARCHAR2(30)
);
alter TABLE logging_test ADD CONSTRAINT logging_test_PK PRIMARY KEY(logging_test_id);
ALTER table logging_test nologging;
alter index logging_test_pk nologging;
Step 1
next run a insert statement
INSERT INTO logging_test (logging_test_id, logging_test_desc) values (2, 'TEST 2');
commit;
Step 2
do a archive log current
ALTER SYSTEM archive log current;
step 3
next determine the range of scn to select from
SELECT * FROM (SELECT sequence#,first_change#, next_change# FROM v$archived_log FROM v$archived_log ORDER BY sequence# DESC) WHERE rownum<=20;
step 4 start logmining
BEGIN
DBMS_LOGMNR.START_LOGMNR(
STARTSCN => &1,
ENDSCN => &2,
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +
DBMS_LOGMNR.COMMITTED_DATA_ONLY +
DBMS_LOGMNR.PRINT_PRETTY_SQL +
DBMS_LOGMNR.CONTINUOUS_MINE);
END;
/
&1 and &2 determine in step 3
step 5 check logmining contents
SELECT SCN, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) as XID, SQL_REDO
FROM V$LOGMNR_CONTENTS;
"insert into "OLAF"."LOGGING_TEST"
values
"LOGGING_TEST_ID" = 2,
"LOGGING_TEST_DESC" = 'test 2';"
[/results]
step 6 end log mining contents
EXECUTE DBMS_LOGMNR.END_LOGMNR();
repeat step 1 to step 5 except this time step 1 is as follow:
INSERT /*+ APPEND */ INTO logging_test (logging_test_id, logging_test_desc) values (9, 'TEST 9');
outcome for INSERT /*+ APPEND */ INTO logging_test (logging_test_id, logging_test_desc) values (9, 'TEST 9'); still the same
"insert into "OLAF"."LOGGING_TEST"
values
"LOGGING_TEST_ID" = 9,
"LOGGING_TEST_DESC" = 'test 9';"
so first question is it possible to have no archive log being logged for table with nologging.
if yes, am I testing the correct way of testing tables with no logging?
If I am testing the wrong way, what should be the correct way?
|
|
|
|
|
|
|
Re: How to make sure no archivelog being generated for a particular table [message #652598 is a reply to message #652596] |
Tue, 14 June 2016 07:45   |
Roachcoach
Messages: 1576 Registered: May 2010 Location: UK
|
Senior Member |
|
|
Logging is extremely hard to get rid of. In fact I'm sure it's not even possible for anything but an insert. If you're updating or deleting it will still happen and the indexes will be generating redo for those inserts regardless.
The only reliable way to really cut it down (note, not eliminate) is to push the DML into DDL.
I recommend you shift the question to what the problem was, did the archiver hang? Is the volume of data being loaded required? Can you simply grow the filessytem?
[Updated on: Tue, 14 June 2016 07:46] Report message to a moderator
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun May 04 14:54:24 CDT 2025
|