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 #652592 is a reply to message #652588] |
Tue, 14 June 2016 07:13 |
John Watson
Messages: 8960 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Is your test over complicated? I get reasonable results with this,orclz> create table all_objects2 as select * from all_objects where 1=2;
Table created.
orclz> set autot on stat
orclz> insert into all_objects2 select * from all_objects;
89970 rows created.
Statistics
----------------------------------------------------------
10386 recursive calls
11730 db block gets
102702 consistent gets
40 physical reads
12337848 redo size
861 bytes sent via SQL*Net to client
859 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2484 sorts (memory)
0 sorts (disk)
89970 rows processed
orclz> alter table all_objects2 nologging;
Table altered.
orclz> insert /*+ append */ into all_objects2 select * from all_objects;
89970 rows created.
Statistics
----------------------------------------------------------
3801 recursive calls
2061 db block gets
79187 consistent gets
1 physical reads
41192 redo size
848 bytes sent via SQL*Net to client
873 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1788 sorts (memory)
0 sorts (disk)
89970 rows processed
orclz>
orclz> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
|
|
|
|
|
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
|
|
|
|
|
|
|
Re: How to make sure no archivelog being generated for a particular table [message #652603 is a reply to message #652602] |
Tue, 14 June 2016 09:37 |
|
Michel Cadot
Messages: 68716 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
John Watson wrote on Tue, 14 June 2016 15:03There is an APPEND_VALUES hint. I've never used it.
As stated by the documentation:
Quote:The APPEND_VALUES hint can be used to greatly enhance performance. Some examples of its uses are:
* In an Oracle Call Interface (OCI) program, when using large array binds or array binds with row callbacks
* In PL/SQL, when loading a large number of rows with a FORALL loop that has an INSERT statement with a VALUES clause
Using it to INSERT one row is really a waste of space (unless your row size is closed to a block) and energy (to either backup the tablespace file or recover in case of disk failure).
|
|
|