RE: Database tracking

Tom,  

Hate to say it, but what you really need is a robot tool that can mimic what your application is doing and to keep the results for reviewing later.  

any query against DBA views are notoriously slow - mostly because the tables that these views are based on are not really relational at all - they've been "denormalized" to make them as fast as possible, but are actually expensive to query against.  

you'd be better off querying against one of your application tables for a test like this. but just remember, whatever you do will have an impact against the sga. soo, a count(*) against a table will cause a full index scan to count all of the entries in the primary key. do you really want this query to push a production query out of the sga every five minutes? it would be a case of the monitor causing more of an overhead than a user.  

just my 2 cents but, good luck!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

Sent: Wednesday, January 15, 2003 1:59 PM To: Multiple recipients of list ORACLE-L

I guess I would also like to test out the network response time. If I run the same SQL from one UNIX box to the production databases at other sites (via sqlnet), I can record total run time and sql statement run time (I assume the difference would be network response time?). If I keep this information forever then I will know if the databases are slowing down or speeding up. I could also determine if particular boxes are speeding up or slowing down.  

The question is, what would be a good SQL statement to test? Is SQL> select count(*) from dba_tables;
as good as another?

-----Original Message-----

Sent: Wednesday, January 15, 2003 1:09 PM To: Multiple recipients of list ORACLE-L

Statspack ??  

Raj

Rajendra Jamadagni MIS, ESPN Inc.

Rajendra dot Jamadagni at ESPN dot com

Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-----Original Message-----

Sent: Wednesday, January 15, 2003 12:54 PM To: Multiple recipients of list ORACLE-L

All, I would like to track the performance of my production databases by running the same SQL statement against each database every 5 minutes or so and recording the results. For example: sql> set timing on;
sql> select count(*) from dba_tables;  

That was I would know if they are getting faster or slower over time. As anyone already done this? Would there be a good SQL statement to use?  

Thanks,
Tom Terrian  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).