Re: Tuning: which applications are writing too much

"Gerard H. Pille" <ghp_at_skynet.be> wrote in message news:44098a6f$0$31480$ba620e4c_at_news.skynet.be...
> Hallo,
>
> our applications - or some of them - are generating more data than our
> hardware can stomach. The question is of course which ones. Should be
> easy to answer if I can determine which tables are written to most.
>
> Don't tell me to RTFM, I know all about the F, and I'm starting with the
> Oracle manuals the minute I've posted this.
>
> Any hints are well come, even the obvious ones, I can always have
> overlooked something stupid, haven't been doing any general tuning for
> quite a while now.
>
> Thanks,
>
> Gerard
>
> PS: Most of the databases are 9.2.0.7, all are version 9.

Since you are on 9i, you can make use of v$segstat. For each active segment it holds a dozen or so stats, the names are listed in v$segstat_name. These inclue:

db block changes
physical writes
physical writes direct

Be a little cautious with v$segstat, using it used to cause a memory leak in the SGA, but an "order by" clause seemed to stop the leak. Select where value greater than some sensible limit for the statistic you want.

Don't use v$segment_statistics - which is the expensive version of the same thing.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html