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 [EMAIL PROTECTED] Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -----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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
