RE: Database tracking

[Mercadante, Thomas F]

Title: Message

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-----
From: Terrian, Tom (Contractor) (DAASC) [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 15, 2003 1:59 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Database tracking

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-----
From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 15, 2003 1:09 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Database tracking

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-----
From: Terrian, Tom (Contractor) (DAASC) [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 15, 2003 12:54 PM
To: Multiple recipients of list ORACLE-L
Subject: Database tracking

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