Re: How can I get the user/io per tablespace ?

Eugene Firyago <efiryago_at_bisys.com> wrote in message news:82r1r2$6ju$1_at_autumn.news.rcn.net...
> - The script $ORACLE/rdbms/admin/catio.sql: see its content and comments how
> to use it.
> - Oracle audit: you can evaluate the volume of DMLs against particular
> tables/tablespaces.
> - Oracle Trace facility.
>
> Eugene.

No. These can not be the correct solutions. Oracle does not keep recording the I/O amount per tablespace per user.

1. catio.sql does NOT collect the real I/O. It gets statistics from buffer cache, not from the real I/O. If you read data from a large table using full table scan, it should flush out many buffer blocks and the cached buffer blocks should be flushed out soon, catio.sql will not be able to "capture" these statistics. And, it does NOT record which user caused the cache.
2. You'd better not user audit. Do you want to audit every user's every statement, to cram your disk full? Even though you had done it, you still can't know how many I/O every statement took.
3. Trace facility does not show you I/O per tablespace.

> Bernard Polarski <bpolarsk_at_yahoo.com> wrote in message
> news:82qime$jgr$1_at_nnrp1.deja.com...
> > This is a difficult question for battle scared DBA's :
> >
> > On a 200 Gigs DB with 50 tablespaces and 1500 users, I would like to
> > find which user is aggressing a peculiar tablespace. I went through all
> > the V$ views, searched usual web site scripts, looked in Oracle Press
> > books and found no way to link sessions SID to IO of a specific table,
> > datafile or tablespace.
> >
> > It seems that :
> >
> > A) Session SID collect IO on the whole DB
> > B) Datafile exports IO counts without referencing
> > the SQL and sessions that originate them.

You had made an effort.
IMO, you can't get the info you want. Received on Fri Dec 10 1999 - 23:37:55 CST