OraFAQ Forum: Performance Tuning » Is there a way to find out activities due to redo logs

Home » RDBMS Server » Performance Tuning » Is there a way to find out activities due to redo logs (11.2.0.4 Windows 2008 R2 Data Center)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Is there a way to find out activities due to redo logs [message #665548]

Sun, 10 September 2017 05:21

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear all,

here' the hours where the redo log switches are the lowest.

SYS@berlin>SELECT * FROM(
  2  SELECT row_number() OVER (ORDER BY redo_count) rn, partial_first_time, redo_count FROM(
  3  SELECT to_char(first_time,'YYYY-MM-DD HH24') partial_first_time, count(0) redo_count
  4  FROM v$log_history GROUP BY to_char(first_time,'YYYY-MM-DD HH24') ORDER BY count(0)
  5  )
  6  ) WHERE rn>=1 and rn<=5;

 RN PARTIAL_FIRST REDO_COUNT
--- ------------- ----------
  1 2017-09-10 11          1
  2 2017-08-20 11          2
  3 2017-08-20 16          2
  4 2017-08-21 01          2
  5 2017-08-23 03          2

here' the hours where the redo log switches are the highest.

SYS@berlin>
SYS@berlin>--top 5 lowest redo log period

SYS@berlin>SELECT * FROM(
  2  SELECT row_number() OVER (ORDER BY redo_count DESC) rn, partial_first_time, redo_count FROM(
  3  SELECT to_char(first_time,'YYYY-MM-DD HH24') partial_first_time, count(0) redo_count
  4  FROM v$log_history GROUP BY to_char(first_time,'YYYY-MM-DD HH24') ORDER BY count(0)
  5  )
  6  ) WHERE rn>=1 and rn<=5;
RN PARTIAL_FIRST REDO_COUNT
--- ------------- ----------
  1 2017-08-23 11         67
  2 2017-09-07 22         61
  3 2017-08-18 22         61
  4 2017-09-07 21         61
  5 2017-09-07 01         60

it flunctues a lot.

Is there a way to find out which are the sql or activies that cause a high number of redo log switches?

thanks

Report message to a moderator

Re: Is there a way to find out activities due to redo logs [message #665552 is a reply to message #665548]

Sun, 10 September 2017 07:39

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

juniordbanewbie wrote on Sun, 10 September 2017 03:21

Dear all,

here' the hours where the redo log switches are the lowest.

SYS@berlin>SELECT * FROM(
  2  SELECT row_number() OVER (ORDER BY redo_count) rn, partial_first_time, redo_count FROM(
  3  SELECT to_char(first_time,'YYYY-MM-DD HH24') partial_first_time, count(0) redo_count
  4  FROM v$log_history GROUP BY to_char(first_time,'YYYY-MM-DD HH24') ORDER BY count(0)
  5  )
  6  ) WHERE rn>=1 and rn<=5;

 RN PARTIAL_FIRST REDO_COUNT
--- ------------- ----------
  1 2017-09-10 11          1
  2 2017-08-20 11          2
  3 2017-08-20 16          2
  4 2017-08-21 01          2
  5 2017-08-23 03          2

here' the hours where the redo log switches are the highest.

SYS@berlin>
SYS@berlin>--top 5 lowest redo log period

SYS@berlin>SELECT * FROM(
  2  SELECT row_number() OVER (ORDER BY redo_count DESC) rn, partial_first_time, redo_count FROM(
  3  SELECT to_char(first_time,'YYYY-MM-DD HH24') partial_first_time, count(0) redo_count
  4  FROM v$log_history GROUP BY to_char(first_time,'YYYY-MM-DD HH24') ORDER BY count(0)
  5  )
  6  ) WHERE rn>=1 and rn<=5;
RN PARTIAL_FIRST REDO_COUNT
--- ------------- ----------
  1 2017-08-23 11         67
  2 2017-09-07 22         61
  3 2017-08-18 22         61
  4 2017-09-07 21         61
  5 2017-09-07 01         60

it flunctues a lot.

Is there a way to find out which are the sql or activies that cause a high number of redo log switches?

thanks

DBMS_LOGMNR can reveal details about the DML that results in the REDO activity.

also statspack/AWR report should reveal activity during these periods.

Report message to a moderator

Re: Is there a way to find out activities due to redo logs [message #667953 is a reply to message #665548]

Thu, 25 January 2018 18:34

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear Black Swan,
you are right.

log miner is the most accurate. but to use log miner you need to turn on supplemental logging. In case the incident happen before supplemental logging is turn on we have to use statspack for SE customers.

also we cannot use log miner when archivelog are being deleted. this happens all the time when we do a backup archivelog and delete archivelog.

do when archivelog is being backup and deleted, we can only use statspack for SE customers.

Report message to a moderator

Re: Is there a way to find out activities due to redo logs [message #667955 is a reply to message #667953]

Fri, 26 January 2018 01:51

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

but to use log miner you need to turn on supplemental logging.

This is not correct, Log Miner is available without supplemental logging (and even BEFORE supplemental logging existed) .
See 2 recent examples I posted in 8i and 11gR2.

[Updated on: Fri, 26 January 2018 01:52]

Report message to a moderator

Re: Is there a way to find out activities due to redo logs [message #668121 is a reply to message #665548]

Thu, 08 February 2018 02:14

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear Michel,

you are right. but if I'm did not remember wrongly if you did not enable supplemental logging, there's some information that may be missing.

Thanks

Report message to a moderator

Re: Is there a way to find out activities due to redo logs [message #668125 is a reply to message #668121]

Thu, 08 February 2018 02:47

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So did you try it?
Did you solve your problem? How?

Report message to a moderator

Re: Is there a way to find out activities due to redo logs [message #668130 is a reply to message #668125]

Thu, 08 February 2018 06:20

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear Michel,

below supplemental logging not enable

SYS@ol73-12102-se-si orcl>select table_space, username, seg_name,count(0) archivelog_count from v$logmnr_contents where seg_name is not null
  2  AND SEG_NAME NOT LIKE '%$'
  3  AND SEG_OWNER NOT IN ('SYS', 'SYSTEM')
  4  GROUP BY table_space, username, seg_name ORDER BY count(0) DESC;

TABLE_SPACE          USERNAME             SEG_NAME                                ARCHIVELOG_COUNT
-------------------- -------------------- ---------------------------------------- ----------------
TEST                 UNKNOWN              FORALL_TEST                           10
SYSAUX               UNKNOWN              CLI_SWP$b77ce52e$1$1                   2
                     UNKNOWN              FORALL_TEST                            1

below with supplemental logging with supplemental logging you can know the user who executes the statement

					 SYS@ol73-12102-se-si orcl>select table_space, username, seg_name,count(0) archivelog_count from v$logmnr_contents where seg_name is not null
  2  AND SEG_NAME NOT LIKE '%$'
  3  AND SEG_OWNER NOT IN ('SYS', 'SYSTEM')
  4  GROUP BY table_space, username, seg_name ORDER BY count(0) DESC;

TABLE_SPACE          USERNAME             SEG_NAME                                 ARCHIVELOG_COUNT
-------------------- -------------------- ---------------------------------------- ----------------
SYSAUX               UNKNOWN              CLI_SWP$b77ce52e$1$1                                    4
TEST                 TEST                 FORALL_TEST                                             1

Yes I did try it. It did solve my problem but not directly because the statement I mined are those normal application sqls which the application team has confirmed. I did not tuned any sql. I simply add create large redo log group and more redo log group.

Report message to a moderator

Re: Is there a way to find out activities due to redo logs [message #668135 is a reply to message #668130]

Thu, 08 February 2018 08:47

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can also use it without supplemental logging but as you didn't show what you did to activate Log Miner we cannot say what you did wrong.
I prove it (example taken from the links I provided):

SQL> select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL from v$database;
SUP SUP
--- ---
NO  NO

SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 as select level val from dual connect by level <= 10;

Table created.

SQL> select * from t1;
       VAL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

SQL> begin
  2    sys.dbms_logmnr.add_logfile (logfilename=>'E:\ORACLE\BASES\MIKB2\RL_G1_1.RDO',
  3                                 options=>sys.dbms_logmnr.NEW);
  4    sys.dbms_logmnr.add_logfile (logfilename=>'E:\ORACLE\BASES\MIKB2\RL_G2_1.RDO',
  5                                 options=>sys.dbms_logmnr.ADDFILE);
  6    sys.dbms_logmnr.add_logfile (logfilename=>'E:\ORACLE\BASES\MIKB2\RL_G3_1.RDO',
  7                                 options=>sys.dbms_logmnr.ADDFILE);
  8    sys.dbms_logmnr.add_logfile (logfilename=>'E:\ORACLE\BASES\MIKB2\RL_G4_1.RDO',
  9                                 options=>sys.dbms_logmnr.ADDFILE);
 10    sys.dbms_logmnr.start_logmnr (
 11      startTime => trunc(sysdate),
 12      endTime   => sysdate,
 13      options   =>   sys.dbms_logmnr.skip_corruption
 14                   + sys.dbms_logmnr.committed_data_only
 15                   + sys.dbms_logmnr.dict_from_online_catalog
 16     );
 17  end;
 18  /

PL/SQL procedure successfully completed.

SQL> select operation, table_space, username from v$logmnr_contents where seg_name='T1' and seg_owner='MICHEL';
OPERATION                        TABLE_SPACE                      USERNAME
-------------------------------- -------------------------------- ------------------------------
DDL                                                               MICHEL
DDL                                                               MICHEL
DIRECT INSERT                    TS_D01                           MICHEL
DIRECT INSERT                    TS_D01                           MICHEL
DIRECT INSERT                    TS_D01                           MICHEL
DIRECT INSERT                    TS_D01                           MICHEL
DIRECT INSERT                    TS_D01                           MICHEL
DIRECT INSERT                    TS_D01                           MICHEL
DIRECT INSERT                    TS_D01                           MICHEL
DIRECT INSERT                    TS_D01                           MICHEL
DIRECT INSERT                    TS_D01                           MICHEL
DIRECT INSERT                    TS_D01                           MICHEL

12 rows selected.

SQL>  exec sys.dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

Report message to a moderator

Re: Is there a way to find out activities due to redo logs [message #668144 is a reply to message #668135]

Thu, 08 February 2018 11:16

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear Michel,

SYS@ol73-12102-se-si orcl>BEGIN
  2  DBMS_LOGMNR.ADD_LOGFILE(
  3    LOGFILENAME => '/u03/app/oracle/fast_recovery_area/orcl/archivelog/2018_02_08/o1_mf_1_18_f7rgrs3h_.arc',
  4    OPTIONS => DBMS_LOGMNR.NEW);
  5  END;
  6  /

what's wrong with my activation that it makes the log miner not able to detect the user without supplemental logging?

thanks in advance!

Report message to a moderator

Re: Is there a way to find out activities due to redo logs [message #668145 is a reply to message #668144]

Thu, 08 February 2018 11:19

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member


SYS@ol73-12102-se-si orcl>SET SERVEROUTPUT ON
SYS@ol73-12102-se-si orcl>DECLARE
  2  err_code NUMBER;
  3  err_msg VARCHAR2(200);
  4  v_startscn NUMBER;
  5  v_endscn NUMBER;
  6  BEGIN
  7  	     BEGIN
  8  		     SELECT first_change#, next_change# INTO v_startscn, v_endscn FROM V$ARCHIVED_LOG
  9  WHERE NAME LIKE '%o1_mf_1_18_f7rgrs3h_.arc%';
 10  
 11  	     dbms_output.put_line('v_startscn ' || v_startscn);
 12  	       dbms_output.put_line('v_endscn ' || v_endscn);
 13  
 14  	     EXCEPTION
 15  	     WHEN OTHERS THEN
 16  	       err_code := SQLCODE;
 17  	   err_msg := SUBSTR(SQLERRM, 1, 200);
 18  	       dbms_output.put_line('select err_code ' || err_code);
 19  	       dbms_output.put_line('select err_msg ' || err_msg);
 20  	     END;
 21  
 22  	     IF      v_startscn >0 AND v_endscn >0 THEN
 23  		     BEGIN
 24  		     DBMS_LOGMNR.START_LOGMNR(
 25  		STARTSCN => v_startscn,
 26  		ENDSCN	 => v_endscn,
 27  		OPTIONS  => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG +
 28  					DBMS_LOGMNR.COMMITTED_DATA_ONLY +
 29  					DBMS_LOGMNR.PRINT_PRETTY_SQL +
 30  					DBMS_LOGMNR.CONTINUOUS_MINE);
 31  
 32  		     EXCEPTION
 33  		     WHEN OTHERS THEN
 34  		       err_code := SQLCODE;
 35  		       err_msg := SUBSTR(SQLERRM, 1, 200);
 36  		       dbms_output.put_line('dbms_logmnr err_code ' || err_code);
 37  		       dbms_output.put_line('dbms_logmnr err_msg ' || err_msg);
 38  
 39  		     END;
 40  	     END IF;
 41  END;
 42  /

Report message to a moderator

Re: Is there a way to find out activities due to redo logs [message #668146 is a reply to message #668145]

Thu, 08 February 2018 11:27

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Execute the EXACT same test case I posted (replacing my online redo logs by yours of course) and post the listing to see if you get the same thing than me.

Report message to a moderator

Re: Is there a way to find out activities due to redo logs [message #668147 is a reply to message #668146]

Thu, 08 February 2018 13:07

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

The EXCEPTION handler codes are fatal flaws which should be removed, deleted & never used anywhere again.

http://www.orafaq.com/wiki/WHEN_OTHERS

Report message to a moderator

Re: Is there a way to find out activities due to redo logs [message #668933 is a reply to message #668146]

Sat, 24 March 2018 05:10

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

Dear Michel,

I did what you told me.


SYSTEM@berlin>select SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL from v$database;

SUP SUP
--- ---
NO  NO

TEST@berlin>create table t1 as select level val from dual connect by level <= 10;

Table created.

TEST@berlin>select * from t1;

       VAL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SYSTEM@berlin> alter system switch logfile;

System altered.

  1  begin
  2        sys.dbms_logmnr.add_logfile (logfilename=>'/u02/app/oracle/oradata/o121space/redo02.log',
  3                                     options=>sys.dbms_logmnr.NEW);
  4        sys.dbms_logmnr.add_logfile (logfilename=>'/u02/app/oracle/oradata/o121space/redo03.log',
  5                                     options=>sys.dbms_logmnr.ADDFILE);
  6        sys.dbms_logmnr.add_logfile (logfilename=>'/u02/app/oracle/oradata/o121space/redo01.log',
  7                                     options=>sys.dbms_logmnr.ADDFILE);
  8       sys.dbms_logmnr.start_logmnr (
  9         startTime => trunc(sysdate),
 10         endTime   => sysdate,
 11         options   =>   sys.dbms_logmnr.skip_corruption
 12                      + sys.dbms_logmnr.committed_data_only
 13                      + sys.dbms_logmnr.dict_from_online_catalog
 14        );
 15*    end;
 16
 17  /

PL/SQL procedure successfully completed.


select operation, table_space, username from v$logmnr_contents where seg_name='T1' and seg_owner='TEST';

SYSTEM@berlin>select operation, table_space, username from v$logmnr_contents where seg_name='T1' and seg_owner='TEST';

OPERATION                        TABLE_SPACE
-------------------------------- --------------------------------
USERNAME
------------------------------
DDL
TEST

DIRECT INSERT                    TEST
TEST

DIRECT INSERT                    TEST
TEST


OPERATION                        TABLE_SPACE
-------------------------------- --------------------------------
USERNAME
------------------------------
DIRECT INSERT                    TEST
TEST

DIRECT INSERT                    TEST
TEST

DIRECT INSERT                    TEST
TEST


OPERATION                        TABLE_SPACE
-------------------------------- --------------------------------
USERNAME
------------------------------
DIRECT INSERT                    TEST
TEST

DIRECT INSERT                    TEST
TEST

DIRECT INSERT                    TEST
TEST


OPERATION                        TABLE_SPACE
-------------------------------- --------------------------------
USERNAME
------------------------------
DIRECT INSERT                    TEST
TEST

DIRECT INSERT                    TEST
TEST


11 rows selected.

SYSTEM@berlin>exec sys.dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

exactly the same.

thanks

Report message to a moderator

Re: Is there a way to find out activities due to redo logs [message #668934 is a reply to message #668933]

Sat, 24 March 2018 05:18

juniordbanewbie
Messages: 250
Registered: April 2014

Senior Member

when I mined the archivelog


1  begin
  2        sys.dbms_logmnr.add_logfile (logfilename=>'/u03/app/oracle/fast_recovery_area/O121SPACE/archivelog/2018_03_24/o1_mf_1_26_fcd7zbtt_.arc',
  3                                     options=>sys.dbms_logmnr.NEW);
  4        sys.dbms_logmnr.add_logfile (logfilename=>'/u03/app/oracle/fast_recovery_area/O121SPACE/archivelog/2018_03_24/o1_mf_1_27_fcd863z7_.arc',
  5                                     options=>sys.dbms_logmnr.ADDFILE);
  6       sys.dbms_logmnr.start_logmnr (
  7         startTime => trunc(sysdate),
  8         endTime   => sysdate,
  9         options   =>   sys.dbms_logmnr.skip_corruption
 10                      + sys.dbms_logmnr.committed_data_only
 11                      + sys.dbms_logmnr.dict_from_online_catalog
 12        );
 13*    end;
SYSTEM@berlin>/

PL/SQL procedure successfully completed.

SYSTEM@berlin>select operation, table_space, username from v$logmnr_contents where seg_name='T1' and seg_owner='TEST';

OPERATION                        TABLE_SPACE
-------------------------------- --------------------------------
USERNAME
------------------------------
DDL
TEST

DIRECT INSERT                    TEST
TEST

DIRECT INSERT                    TEST
TEST


OPERATION                        TABLE_SPACE
-------------------------------- --------------------------------
USERNAME
------------------------------
DIRECT INSERT                    TEST
TEST

DIRECT INSERT                    TEST
TEST

DIRECT INSERT                    TEST
TEST


OPERATION                        TABLE_SPACE
-------------------------------- --------------------------------
USERNAME
------------------------------
DIRECT INSERT                    TEST
TEST

DIRECT INSERT                    TEST
TEST

DIRECT INSERT                    TEST
TEST


OPERATION                        TABLE_SPACE
-------------------------------- --------------------------------
USERNAME
------------------------------
DIRECT INSERT                    TEST
TEST

DIRECT INSERT                    TEST
TEST


11 rows selected.

outcome also the same.

thanks

Report message to a moderator