RE: statspack report on sql

Ray

   Good ideas. Since Oracle caches execution plans, theoretically a SQL statement could have been executed by multiple people. And for a given person, you would probably only be able to see the SQL they were executing at the time of the snapshot. I think your method might work to catch some types of miscreants, like those that execute the "query from hell", but not others.

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

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

On Mon, May 19, 2003 at 11:52:07AM -0800, DENNIS WILLIAMS wrote:
> 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.
> ;-)

I do collect the sql, I wanted the smoking gun, the username. Really I was seeking to learn a mechanism to retrieve the smoking gun for some possible future event, say some loose cannon does this kind of thing and makes a prod batch job run long. I'd like a way of closing in on the bad guy after the fact without the load of a system audit. Statspack seems so close. No cigar.

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

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