Path: news.easynews.com!easynews!news-out.cwix.com!newsfeed.cwix.com!newsfeed1.cidera.com!Cidera!netnews.com!xfer02.netnews.com!newsfeed2.earthlink.net!newsfeed.earthlink.net!newsmaster1.prod.itd.earthlink.net!newsread1.prod.itd.earthlink.net.POSTED!not-for-mail From: Mladen Gogala Subject: Re: How to show biggest redo producers Newsgroups: comp.databases.oracle.server Message-ID: References: User-Agent: Pan/0.11.2 (Unix) Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Comment-To: "Mladen Gogala" Lines: 29 Date: Sun, 03 Mar 2002 18:52:06 GMT NNTP-Posting-Host: 158.252.37.239 X-Complaints-To: abuse@earthlink.net X-Trace: newsread1.prod.itd.earthlink.net 1015181526 158.252.37.239 (Sun, 03 Mar 2002 10:52:06 PST) NNTP-Posting-Date: Sun, 03 Mar 2002 10:52:06 PST Organization: EarthLink Inc. -- http://www.EarthLink.net Xref: easynews comp.databases.oracle.server:137308 X-Received-Date: Sun, 03 Mar 2002 11:55:23 MST (news.easynews.com) 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