Oracle-L: RE: Statspack Intervals

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Wed, 10 Jul 2002 13:39:54 -0800
Message-ID: <F001.00494538.20020710133954@fatcity.com>

I don't think it will. V$SQLAREA gives information on executions, disk reads, logical reads, etc. It tells nothing about waits. Joining the two tables you mention does not show you who has been running the queries, just who parsed them originally. Yes, the rest of statspack does have information on wait events but it is not tied back to the queries which caused them.

Developers sometimes call complaining of problems with third party products. There are products on the market which manipulate data by fetching it from the database, performing the change on the client, and pushing it back to the database. If a user called while the process was running, you'd look at V$SESSION_WAIT and see that the database was waiting on the client to process the data. You would then tell the developer, that it's that #$%!*! third party product causing the delay.

What happens if the developer mentions the problem the next day. Do you ask them to repeat the job so it can be monitored? I want to be able to look at the wait events of the session that ran the job the previous day.

Ian MacGregor
Stanford Linear Accelerator Center
ian_at_SLAC.Stanford.edu

-----Original Message-----
Sent: Wednesday, July 10, 2002 10:19 AM
To: Multiple recipients of list ORACLE-L

Hey Ian,

I'm a little confused (hopefully not just because of this stupid cold I'm fighting). Offending SQL in your DB is purged out of the shared pool in less than five minutes? I would think that joining V$SQLTEXT to V$SQLAREA, even after a particularly nasty query/DML was completed, would point to the offender(s).

Or am I missing something here (there's a good chance of that)?

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

-----Original Message-----
Sent: Wednesday, July 10, 2002 11:13 AM
To: Multiple recipients of list ORACLE-L

Statspack is a very nice tool, however if one really wants to find problems lurking in a database even five minute intervals can be too long. By finding problems I mean locating inefficiencies proactively. As good as the first few chapters of "Database Tuning 101" are, the book preaches a reactive and not a proactive tuning method. Both are needed.

I have reached the conclusion that some data such as that in V$SESSION_WAIT and V$SQLTEXT should be collected each minute. I don't mean to collect everything just the active sessions and those that have been idle for a minute or less. It would be nice to collect session stats every minute as well, but that becomes too expensive. I choose to collect the session stats which best mirror what tkprof puts out.

Running statspack is not ideal for this. It will record information which I do not need that fine-grained. I do however need it for comparison purposes.

I can better answer questions such as who is accessing the data, what fields are being accessed, what is the query plan used, how expensive is the access, etc. I can also better answer questions, such as, "One of my overnight jobs ran very slowly, can you tell me why?"
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: MacGregor, Ian A.
INET: ian_at_SLAC.Stanford.EDU

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------