The query would work if the transaction (SQL)
generating the redo was still in the shared pool.
If the SQL was executed a few hours ago and is
a one-off SQL (ie, occuring say once a day) and the DBA doesn't know at what
time-of-the-day
the SQL is executed, he'd better keep running
this query repeatedly in an attempt to catch it.
Hemant K Chitale
http://hkchital.tripod.com
"Mladen Gogala" <mgogala_at_earthlink.net> wrote in message
news:pan.2002.03.03.13.54.02.606399.1204_at_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 Mon Mar 04 2002 - 07:37:56 CST
