Home » RDBMS Server » Server Administration » Archive log destination is generating more redo (Oracle 11.2.0.3.0 , HP-UX)
Archive log destination is generating more redo [message #619757] Thu, 24 July 2014 19:51 Go to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

Hi,

I found archive destination /arch is 90% used.

I ran below query to know which sessions currently generate redo.
SQL> select value/((sysdate-logon_time) * 1440) redo_per_minute,s.sid,serial#,logon_time,value,ss.statistic#
from v$statname sn,
v$sesstat ss,
v$session s
where s.sid = ss.sid
and sn.statistic# = ss.statistic#
and name = 'redo size'
and value>0  2    3    4    5    6    7    8
  9  /

REDO_PER_MINUTE        SID    SERIAL# LOGON_TIM      VALUE STATISTIC#
--------------- ---------- ---------- --------- ---------- ----------
     .166043623          9          1 06-JUN-14      11664        178
     .137250406         14      16861 12-JUL-14       2424        178
      2773.0988        571          1 06-JUN-14  194801640        178
      3.7447453        573         13 06-JUN-14     263056        178
     16.4247786        576       4291 09-JUL-14     370664        178
     .135435707        579      50737 12-JUL-14       2392        178
     1035.50669       1139          1 06-JUN-14   72741152        178
      16.364438       1705       2977 09-JUL-14     369296        178
      55.407528       1709          1 06-JUN-14    3892208        178

9 rows selected.

How to find which session is generating more redo from the above output ?

Regards,
Srini
Re: Archive log destination is generating more redo [message #619758 is a reply to message #619757] Thu, 24 July 2014 20:05 Go to previous messageGo to next message
BlackSwan
Messages: 23048
Registered: January 2009
Senior Member
>How to find which session is generating more redo from the above output ?
open your eyes & think about the presented results.
Re: Archive log destination is generating more redo [message #619759 is a reply to message #619758] Thu, 24 July 2014 20:07 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

what do you mean by presented results ?
Re: Archive log destination is generating more redo [message #619760 is a reply to message #619759] Thu, 24 July 2014 20:08 Go to previous messageGo to next message
BlackSwan
Messages: 23048
Registered: January 2009
Senior Member

REDO_PER_MINUTE        SID    SERIAL# LOGON_TIM      VALUE STATISTIC#
--------------- ---------- ---------- --------- ---------- ----------
     .166043623          9          1 06-JUN-14      11664        178
     .137250406         14      16861 12-JUL-14       2424        178
      2773.0988        571          1 06-JUN-14  194801640        178
      3.7447453        573         13 06-JUN-14     263056        178
     16.4247786        576       4291 09-JUL-14     370664        178
     .135435707        579      50737 12-JUL-14       2392        178
     1035.50669       1139          1 06-JUN-14   72741152        178
      16.364438       1705       2977 09-JUL-14     369296        178
      55.407528       1709          1 06-JUN-14    3892208        178

9 rows selected.
Re: Archive log destination is generating more redo [message #619770 is a reply to message #619757] Fri, 25 July 2014 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 59775
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
How to find which session is generating more redo from the above output ?


Laughing

You're the great joker.

Re: Archive log destination is generating more redo [message #619837 is a reply to message #619770] Fri, 25 July 2014 08:18 Go to previous messageGo to next message
BlackSwan
Messages: 23048
Registered: January 2009
Senior Member
from where did you find the posted SQL?
Re: Archive log destination is generating more redo [message #619870 is a reply to message #619837] Fri, 25 July 2014 11:23 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

Hi Blackswan,

<from where did you find the posted SQL? >

I got it from google.

Regards,
Srini
Re: Archive log destination is generating more redo [message #619917 is a reply to message #619870] Sat, 26 July 2014 00:09 Go to previous message
Michel Cadot
Messages: 59775
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And you use it without understanding it!

Well, thinking a bit, you are right, if you had to understand something to use it you'd use nothing. Grin

Previous Topic: Message found in alert log
Next Topic: SYS,SYSDBA accounts locked
Goto Forum:
  


Current Time: Tue Nov 25 15:23:25 CST 2014

Total time taken to generate the page: 0.26780 seconds