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

• 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.

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.
>
> Though, explain plan can retrieve the link between an SQL and the gets
> on the tables. So they must be a X$ table somewhere that does the job.
>
> However I recognise that the solution is not evident : A query may
> access various tablespaces, so resulting IO counts will not tell me
> which tablespace provides which part of the IO count. Nevertheless, I
> would be happy If I can produce only a list of session that are
> currently addressing the tablespace and reversibly produce a list of
> all session currently accessing a tablespace. After that, I can easily
> find in the SQLarea all potential SQL.
>
>
> The only way I found would be to decode the SQL and packages in SQLarea
> and search for the table name and hence find all tablespaces. This
> solution would require an SQL decoder to find all tables after the
> from clause. I swear that this is not an easy problem.
>
>
> Has anybody ever met something that does the job of linking user
> activity to tablespace IO ?
>
> --
> Bernard Polarski
>
> http://www.geocities.com/bpolarsk
> Email:bpolarsk_at_yahoo.com
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Dec 10 1999 - 08:13:32 CST