RE: typical active table count

Real time or periodically after log switches?

For diagnostics, after log switches is probably good enough, though you might not strictly count things that are rolled back before a log flush (which is potentially unbounded), and your question smacks of activity measurement rather than committed changes. I suppose you lose some unrecoverable operations also.

Some sort of log miner would probably be good enough for that and is probably the least performance affecting way (rather than the useful queries that have been mentioned). Grabbing 10 second windows from the log shouldn't be too hard IF you have access to the files. If you have the logminer source code it is conceivable a trivial filter/summary analysis widget could be tacked on to the hopefully modularly built reader.

I find it increasingly difficult to get permissions to do useful lightweight measures at the system level of production systems, and folks tend to not want extra analytical queries to be run at peak load (when this would be most interesting).

IF this is post mortem type analysis rather than real time, maybe you can do it on a recovery server.

All the preceding answers are useful, most especially 42.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jeremy Schneider Sent: Tuesday, June 27, 2023 5:30 PM
To: oracle-l_at_freelists.org
Cc: tim.evdbt_at_gmail.com; 'Clay Jackson (cjackson)' Subject: Re: typical active table count

On 6/27/23 1:00 PM, Jonathan Lewis wrote:
> 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.

The original context was a conversation related to logical replication of DB changes, and that particular conversation was concerned with user tables and not system ones.

But then I got interested in the general question and topic - and system tables are a really good point which hadn't occurred to me. Thanks!

Besides partitioning, another interesting dimension of the conversation has been thinking about different categories of workloads. For example: SaaS or multitenant applications with many copies of a similar schema, and ISVs or large enterprise databases with lots of development history, and the ERPs that Clay mentioned. All of these categories can easily ramp up the counts too.

And naturally, those millions of tiny databases behind wordpress websites might easily stay under 10 tables being updated within a 10 second window of time. :)

-Jeremy

PS. I haven't been regularly reading this list and hadn't posted in awhile. What fun to see so many familiar names around here!

--
http://about.me/jeremy_schneider

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





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