How can I get the user/io per tablespace ?

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 :

1. Session SID collect IO on the whole DB
2. 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 - 03:55:26 CST