Re: reading redo log files

On 8 Oct 1998, dke wrote:
>
> The problem with that suggestion is that you should monitor this on the
> time it happens. As those things often happen on weekend or night times I
> should prefer a method making it posible to do the analyse offline ( on
> monday then ).

You could use the auditing feature to trigger statistics collection whenever a session disconnects. That way you could keep a record of the statistics for each session that was using the database.

By auditing sessions, you will get an entry in the AUD$ table for each connect and disconnect. The disconnects can be identfied by their value for ACTION#. By placing a trigger on AUD$ that collects the values from v$session and v$sesstat for that session (where userenv('SESSIONID') = v$session.audsid) into another table for each disconnect, you will have the statistics for each session that comes and goes.

To accomplish this, you would have to recreate the AUD$ table under a schema other than SYS (such as SYSTEM), and create a synonym owned by SYS pointing at it. You have to do it this way because Oracle will not let you put a trigger on a table owned by SYS.

This should allow you to identify who is generating all that redo, and it gets you a great mechanism for tracking resource usage on your server over time. You couls do this with data from v$session_event too, and get some really interesting information.

Also, back to your original question, I am going to attend a cool sounding seminar at OpenWorld next month called "Mining Oracle Redo Log Files." Apparently, a new utility is available that will allow better use of information that happens to be stored in the redolog files. I'm pretty sure that that approach would not give you the information you want, though.

Good luck,

--
Jeremiah Wilton http://www.wolfenet.com/~jeremiah

> Jeremiah Wilton <jeremiah_at_wolfenet.com> wrote in article
> <Pine.OSF.3.95.981007151822.16181A-100000_at_gonzo.wolfenet.com>...
> > On 7 Oct 1998, dke wrote:
> > >
> > > Is there something to read the contents of the redo log files.
> > >
> > > I think I use too much redo-log space, so I want to know who is doing this
> >
> > A much better way to find out who is responsible for excessive redo
> > generation is to monitor the growth of statistic# 97 (for Oracle8) in
> > v$sesstat for each session connected. If one session is generating more
> > redo than the others, then its value for v$sesstat statistic# 97 should
> > grow conspicuously.
> >
> > I think the same statistic ('redo size') for Oracle7 is 85, but check that
> > in v$statname before you try it.
Received on Fri Oct 09 1998 - 10:22:54 CDT