RE: statspack report on sql

Ray - What snapshot level are you using for STATSPACK? If you run it with Level 5, you will get the SQL statements. You have a pretty good idea, but the tradeoff is the level of detail vs. the storage for the extra details. I guess if I thought there was a free lunch somewhere I would go into sales. ;-)

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Monday, May 19, 2003 2:32 PM
To: Multiple recipients of list ORACLE-L

Thanks Dennis, some developer "developed" some load adding monster over the weekend in an unannounced way. I just wanted to see if I could catch them with the collected statspack data. I wasn't paying that close attention, but detected the load change during my Monday morning review of the statspack report. The weekend is a good time to do this here, so no harm done.

It does raise a good question, I think. I suppose that the statspack tables could be expanded to add this pretty easily by linking v$sql and v$session. Sounds like a decent feature request to me.

On Mon, May 19, 2003 at 09:41:42AM -0800, DENNIS WILLIAMS wrote:
> Ray - I'm a big fan of STATSPACK, but I think its job will be more to
alert
> you to something wrong. I believe that STATSPACK just reads the v$sql
table
> at the time it is executed.
> Sometimes this requires some detective work. Like the Bob Seger line
> "working on a mystery without any clues". Are you looking for ad-hoc
queries
> or something an application created? If the latter, then go talk to the
> developers. If the former, the easiest way is to see which process takes
up
> a lot of CPU, then trace it back to the SQL via the v$ tables.
> Sorry to be vague, but maybe you can give more details and I can be
more
> helpful.
>
> Dennis Williams
> DBA, 80%OCP, 100% DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Monday, May 19, 2003 8:11 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Are there any columns that could be tied to the standard sql
> reports from statspack to point the finger at the userid that
> is responsible for a certain load? Hoping to chase down a bad
> guy this moring.
> ===============================================================
> Ray Stell stellr_at_vt.edu (540) 231-4109 KE4TJC 28^D
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Ray Stell
> INET: stellr_at_cns.vt.edu
>
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> 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).

-- 
===============================================================
Ray Stell   stellr_at_vt.edu     (540) 231-4109     KE4TJC    28^D
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ray Stell
  INET: stellr_at_cns.vt.edu

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

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