Re: Can Oracle do what PostgreSQL can?

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