From: | "R(dot)P(dot) Aditya" <aditya(at)grot(dot)org> |
---|---|
To: | SF Postgres <sfpug(at)postgresql(dot)org> |
Subject: | Re: Can Oracle do what PostgreSQL can? |
Date: | 2007-12-13 03:17:13 |
Message-ID: | 20071213031713.GB18742@mighty.grot.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
On Thu, Dec 13, 2007 at 02:13:27AM +0000, R.P. Aditya wrote:
> I'm used to being able to consult the pg_stat_{all,user}_tables and look at
> the statistics, like:
>
> seq_scan,
> seq_tup_read,
> idx_scan,
> idx_tup_fetch,
> n_tup_ins,
> n_tup_upd,
> n_tup_del
>
> and poll them every 5 minutes to get near-realtime trending -- it seems that
> Oracle only keeps DML update and index access stats in tables like
> all_tab_modifications, but the data is only updated per table when statistics
> are gathered per table -- in some cases the threshold is 10% of the rows have
> changed, so for very large tables it could be a long time, so I can't depend
> on the near-realtimeness...
>
> I know this is the "opposite" forum for asking a question about Oracle, but I
> can't even seem to construct searches in google that lead me to info about
> Oracle pertaining to this -- perhaps if I ask of PostgreSQL folks, who know
> what I'm saying, they'll have suggestions?
okay, for completeness I'll follow up to my own post :-)
turns out that in Oracle 10g AWR (and likely statspack in 9i) snapshots
control how often that data is collected, by default every hour, so you can
get a list of objects and a count of what happened to them with something
like (adjust for snapshot frequency) -- so it is doable, but really annoying:
select
to_char(sn.begin_interval_time,'yy-mm-dd hh24:mi:ss'),
p.object_owner, p.object_name,
p.operation,
p.options,
count(1)
from
dba_hist_sql_plan p,
dba_hist_sqlstat s,
dba_hist_snapshot sn
where
p.object_owner <> 'SYS'
and p.sql_id = s.sql_id
and s.snap_id = sn.snap_id
and sn.begin_interval_time > (sysdate - interval '30' minute(1))
group by
to_char(sn.begin_interval_time,'yy-mm-dd hh24:mi:ss'),
p.object_name,
p.operation,
p.options, p.object_owner
order by
1, 3, 4, 2, 5 desc;
thanks,
Adi
From | Date | Subject | |
---|---|---|---|
Next Message | Brian Ghidinelli | 2007-12-28 01:43:16 | 8.2 slower than 8.1? |
Previous Message | R.P. Aditya | 2007-12-13 02:13:27 | Can Oracle do what PostgreSQL can? |