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)
Is there a way to find out activities due to redo logs [message #665548] Sun, 10 September 2017 05:21 Go to next message
juniordbanewbie
Messages: 209
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
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 Go to previous messageGo to next message
BlackSwan
Messages: 26056
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.
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 Go to previous messageGo to next message
juniordbanewbie
Messages: 209
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65578
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
juniordbanewbie
Messages: 209
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65578
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

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 Go to previous messageGo to next message
juniordbanewbie
Messages: 209
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65578
Registered: March 2007
Location: Nanterre, France, http://...
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.

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 Go to previous messageGo to next message
juniordbanewbie
Messages: 209
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!
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 Go to previous messageGo to next message
juniordbanewbie
Messages: 209
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  /

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 Go to previous messageGo to next message
Michel Cadot
Messages: 65578
Registered: March 2007
Location: Nanterre, France, http://...
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.

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 Go to previous messageGo to next message
BlackSwan
Messages: 26056
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
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 Go to previous messageGo to next message
juniordbanewbie
Messages: 209
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
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 Go to previous messageGo to next message
juniordbanewbie
Messages: 209
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
Re: Is there a way to find out activities due to redo logs [message #668935 is a reply to message #668934] Sat, 24 March 2018 09:02 Go to previous messageGo to next message
BlackSwan
Messages: 26056
Registered: January 2009
Location: SoCal
Senior Member
In the intervening 6 months did you enable supplemental logging?
If not, why not?
Re: Is there a way to find out activities due to redo logs [message #669143 is a reply to message #668935] Wed, 04 April 2018 23:52 Go to previous message
juniordbanewbie
Messages: 209
Registered: April 2014
Senior Member
No I did not enable supplemental logging when the incident happen
Previous Topic: Better query for max(date) per account in big table
Next Topic: Finding downgraded sql
Goto Forum:
  


Current Time: Sun Jul 15 14:08:54 CDT 2018