oracle metrics monitoring and reporting in real time

I am stress testing a database table

I am looking for any software that can connect to my database and show me some metrics like no of rows in a table, time for inserts , inserts/time, table fragmentation[logical/physical] etc .

It would be great if the reporting tool can do the following:

1] Report in real time or atleast after some interval so that I do not have to wait for test to finish to get first look at the data

2] Ability to do stuff with the data later, like get 99.99 percentile, avg etc.

Is mostly freely available :)

Does anyone have any suggestion of something I can use with my Oracle table. Any pointers would be great.

I can actually write scripts to logg stuff like select count(*) etc .. but then I will have to spend a lot of time parsing and changing the data reporting rather than the tests. I think some intelligent thing might already be out there ??

Thanks

Edit:开发者_如何学运维

• I am looking at a piece of design for a new architecture
• The tests are "comparison" tests for different designs and hence as far as I do it on same hardware and same schema etc they are comparable to some granularity.
• I want to monitor index fragmentation, and response times etc.
• If you think there are other things that can change please let me know. I am trying to roll back the table to particular state[basically truncate] for each new iteration of the test

---

First, Oracle has built-in functionality for telling you the number of rows in a table (either use count(*) or search 'gather statistics oracle' for another option).

But "stress testing a table" sounds to me like you're going down the wrong path. Most of the metrics you're mentioning ("time for inserts , inserts/time, table fragmentation[logical/physical] etc") are highly dependent on many factors:

• what OS Oracle's running on
• how the OS is tuned (i.e. other services running)
• how the specific Oracle instance is configured
• what underlying storage architecture Oracle's using (and how tablespaces are configured)
• what other queries are being executed in the database at the exact same time as your test

But NONE of them would be related to the table design itself.

Now, if you're wondering if your normalized (or de-normalized) table schema is hurting your application, that's another matter. As is performance being degraded by improper/unneeded/missing indexes, triggers, or a host of other problems.

But if you really want an app that will give you real-time monitoring, check out Quest Software's Spotlight on Oracle. But it's definitely not free.

---

Just to add to the other comments, I believe what you really want is to stress test the queries you're running and not the table. The table is just a bunch of data blocks on a disk and the query is what will make the difference in performance as far as development is concerned. That will tell you if you need different indexes or need to redesign the query.

On the other hand, if you're looking at it as a DBA or system administrator, you're probably more interested in OS level statistics especially disk latency, memory paging, and CPU utilization.

All this is available in the enterprise manager which is my primary tuning tool for development and DBA. If you don't have that, read up on using sql_trace to profile your queries and your OS specific documentation on how to get those stats.