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 Go to next message
juniordbanewbie
Messages: 217
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 #652590 is a reply to message #652588] Tue, 14 June 2016 06:58 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
You do realise that if your DB crashes you're going to need the archive log to recover it?
If you're getting a lot generated you need to look at the SQL and see if it's as efficient as it can be.
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 Go to previous messageGo to next message
John Watson
Messages: 7620
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 #652596 is a reply to message #652588] Tue, 14 June 2016 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition, APPEND hint is ignored on INSERT VALUES, it applies only on INSERT SELECT.

Re: How to make sure no archivelog being generated for a particular table [message #652597 is a reply to message #652592] Tue, 14 June 2016 07:39 Go to previous messageGo to next message
juniordbanewbie
Messages: 217
Registered: April 2014
Senior Member
dear cookiemonster, yes I'm aware that we need archivelog to recover.

dear John,

the difference I see is number of physical reads,

but still i still don't see any proof of archivelog generation in either cases.

perhaps I should shift my focus to NOT concentrate on archivelog being generated or not but instead should shift to physical reads, right?

thanks
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 Go to previous messageGo to next message
Roachcoach
Messages: 1571
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 #652599 is a reply to message #652597] Tue, 14 June 2016 07:48 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
Quote:
dear John,

the difference I see is number of physical reads,

but still i still don't see any proof of archivelog generation in either cases.
You need to pay more attention. Did you not notice the figures for redo size?
Re: How to make sure no archivelog being generated for a particular table [message #652600 is a reply to message #652599] Tue, 14 June 2016 07:53 Go to previous messageGo to next message
juniordbanewbie
Messages: 217
Registered: April 2014
Senior Member
noted, I see the difference thanks John.
Re: How to make sure no archivelog being generated for a particular table [message #652601 is a reply to message #652600] Tue, 14 June 2016 08:02 Go to previous messageGo to next message
BlackSwan
Messages: 26203
Registered: January 2009
Location: SoCal
Senior Member
When all else fails Read The Fine Manual

"logging_clause

Use the logging_clause to change the logging attribute of the table. The logging_clause specifies whether subsequent ALTER TABLE ... MOVE and ALTER TABLE ... SPLIT operations will be logged or not logged."

so what is impact of LOGGING_CLAUSE on DML statements?
Re: How to make sure no archivelog being generated for a particular table [message #652602 is a reply to message #652596] Tue, 14 June 2016 08:03 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
Michel Cadot wrote on Tue, 14 June 2016 13:35

In addition, APPEND hint is ignored on INSERT VALUES, it applies only on INSERT SELECT.

There is an APPEND_VALUES hint. I've never used it.
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 Go to previous message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

John Watson wrote on Tue, 14 June 2016 15:03
There 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).

Previous Topic: Is there a way to find out a killed session associated sql
Next Topic: Performance worse for given workflow query
Goto Forum:
  


Current Time: Sat Oct 20 18:40:38 CDT 2018