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

<cut>
 3. Trace facility does not show you I/O per tablespace. </cut>

  Yes, but they show me IO per table and table can be located.   Am i right in assuming that a possible solution is to set all   heavy loaded IO session in 'trace mode' and have a script decode   and analyse the produced trace file.
  I have already developped such a tool, but this solution requires   some minutes (at least 5 min) and you are already taken by time.   Worse : on the 1500 users, some of them may be regular heavy users   ( +-200) connected since many days. Their statitics will be high but   reflects a consistent activity.

  What I am looking for a method to detect recent heavy loaded sessions   and determine which tablespace or table they access. For the moment   I rely on 'ps -ef ' and the E10k returns +- 5000 lines ready for   awk. Session IO hit parade scripts fails to point out the guilty, so   I wonder if these statistics are update only when the full table scan   is over. And then, it seems that the session disapear,   so I am a bit disapointed.
  pain, pain...

  B. Polarski

In article <82snvj$p88$7_at_news.seed.net.tw>,   "fumi" <fumi_at_tpts5.seed.net.tw> wrote:
>
> 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.
>
>

--
B.Polarski

http://www.geocities.com/bpolarsk
Email : bpolarsk_at_yahoo.com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Dec 13 1999 - 06:55:37 CST