Re: How to show biggest redo producers

From: Mladen Gogala <mgogala_at_earthlink.net>
Date: Sun, 03 Mar 2002 18:52:06 GMT
Message-ID: <pan.2002.03.03.13.54.02.606399.1204@earthlink.net>

On Sun, 03 Mar 2002 13:49:52 -0500, Mladen Gogala wrote:

> On Fri, 01 Mar 2002 08:39:23 -0500, Tom wrote:
>

>> I have a database producing tremendous amounts of redo, and I'd like to
>> show which tables' updates are the biggest offenders.  Is there a way to
>> get these statistics?  Will log miner work?  Is it the only way?
>> 
>> Tom

>
> Here's a query that will give you SID which is esponsible for the most of
> redo generation. You can then drill deeper by using v$sqlarea which
> contains columns like 'DISK_READS' and 'BUFFER_GETS'. It should be
> rather easy guess once you determine which SID is the worst offender. The
> other method would be to use profiles and put a limit of 1000 logical
> reads per call. The offenders would be the people that are screaming.
>

Sorry, I forgot the query:

  select s.sid,n.name,s.value
    from v$sesstat s,v$statname n
    where n.name = 'redo blocks written'

             and s.statistic#=n.statistic#
    order by value
/

-- 
Mladen Gogala

Received on Sun Mar 03 2002 - 12:52:06 CST