Re: typical active table count

I can't come up with any meaningful value, though I'd certainly expect the answer to be more than "10 or so", given how many tables a little bit of Oracle background activity can modify and how frequently little background activities take place.

If you wanted to get some idea for your system you could take snapshots of V$segstat every 10 seconds, restricted to just statistic_name = 'db block changes' - though there are several problems about interpreting the results - partitions, as you mentioned, but also index organized tables where the change is to an index (partition); then "db block changes" is about block changes which doesn't count the DML statements and doesn't count the rows changes; then there's the problem that it is one of the few statistics that is sampled.

Regards
Jonathan Lewis

On Tue, 27 Jun 2023 at 16:57, Jeremy Schneider <schneider_at_ardentperf.com> wrote:

> Question for other Oracle users
>
> On your moderately busy DB, how many different tables might receive at
> least one change/DML during a 10 second window?
>
> 10? 50? 100? More? Ballpark guess off the top of your head.
>
> I'm in a discussion & there's questions about whether it's unusual to have
> more than 10 or so. The answer isn't clear to me.
>
> Probably worthwhile to call out partitioning explicitly (ie. if this
> factors into an answer then mention that fact)
>
> -Jeremy
>
> --
> http://about.me/jeremy_schneider
>

--
http://www.freelists.org/webpage/oracle-l