Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: statspack report on sql

Re: statspack report on sql

From: Ray Stell <stellr_at_cns.vt.edu>
Date: Mon, 19 May 2003 17:21:42 -0800
Message-ID: <F001.0059D58D.20030519172142@fatcity.com>


On Mon, May 19, 2003 at 02:27:03PM -0800, DENNIS WILLIAMS wrote:
> 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.

This fit my case well since the maroon was firing at me every second for the entire day, statistically anyway, 91k executions/day. Some loop out of control. Maybe they mistook prod for devel. It happens.

Anyway, I'll dig a little deeper into how the sql is collected. I haven't done my homework at all, I was hoping to copy somebody elses before class.

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

-- 
===============================================================
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).
Received on Mon May 19 2003 - 20:21:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US