Re: how to get the list of SQL's

  • From: Jared Still <jkstill@xxxxxxxxx>
  • To: tim@xxxxxxxxx
  • Date: Mon, 12 Apr 2010 11:48:38 -0700

A simple proxy can be built to capture all SQL for experimental purposes.

Here's one that works for a single connection:

http://www.adp-gmbh.ch/blog/2006/01/24.php

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com



On Fri, Apr 2, 2010 at 8:11 AM, Tim Gorman <tim@xxxxxxxxx> wrote:

>  For a complete record of SQL, perhaps consider something that intercepts
> and passes-thru all SQL*Net traffic into and out from the database, such as
> Teleran iSight 
> ("http://www.teleran.com/PRODUCTS/isight.htm";<http://www.teleran.com/PRODUCTS/isight.htm>).
> Note:  I'm not affiliated with Teleran (or any other vendor), I just like
> the product from past experience...
>
> For retrieving what is available to you within the database, the
> DBA_HIST_SQLSTAT view has the basis of what you might be able to use from
> AWR.  Consider that view to contain "sightings" of SQL presently executing
> at each AWR snapshot, and then join back to DBA_HIST_SQLTEXT as needed.
>
> Hope this helps!
>
>
>
>
> Stephane Faroult wrote:
>
> The problem with i3 and the like is exactly the same - they just take
> pictures more often, they miss less but you cannot have any guarantee
> they'll miss nothing.
> The only way to get a really complete picture is to trap all queries
> BEFORE they are passed to Oracle, logging them in an applications server
> for instance (assuming boldly that nothing  comes from another source).
> But then you have the question of the overhead induced  by  trapping and
> logging  - whatever vendors pretend, there is no such thing as zero-impact.
>
> But I'm not obsessed by "missing nothing". By and large, you have three
> "problem statement" categories, the big bad SQL query that you cannot
> miss anyway and that will figure prominently in all reports, the very
> fast, PK search that is run zillions of times every minute because of
> poor algorithms, that should also appear in reports because of the
> cumulated effect (unless your queries aren't properly bound, which you
> should notice anyway), and what goes usually undetected for quite a
> while, the tons of mediocre statements that aren't bad enough to cause
> alarm but stay just below the radar and will be at the root of
> scalability issues. Even with snapshots, you can find all of them; and
> sophisticated, full-picture tools will not be of much use to you if you
> cannot say "this statement should run much faster than that"  (or "is
> completely useless")  when you see it.
>
> Stephane Faroult
> RoughSea Ltd <http://www.roughsea.com> <http://www.roughsea.com>
> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd> 
> <http://www.youtube.com/user/roughsealtd>
>
>
> Ojha, Ajeet (GE Healthcare, consultant) wrote:
>
>
>  Thanks. Well actually you can  have ..if you use some other tool like i3 - 
> indepth analayzer there they do have a  performance warehouse kind of thing, 
> I am wondering if   OEM can give this kind of ability...and I don't want  
> from 1920......even if I could get of the retention period says 7 days or 15 
> days..that should be fine..for now.
>
> -----Original Message-----
> From: Stephane Faroult [mailto:sfaroult@xxxxxxxxxxxx <sfaroult@xxxxxxxxxxxx>]
> Sent: Thursday, April 01, 2010 1:46 PM
> To: Ojha, Ajeet (GE Healthcare, consultant)
> Cc: oracle-l@xxxxxxxxxxxxx
> Subject: Re: how to get the list of SQL's
>
> >From Merriam-Webster:
>
> Main Entry: snap·shot
> Pronunciation: \?snap-?shät\
> Function: noun
> Date: 1890
>
> 1 : a casual photograph made typically by an amateur with a small
> handheld camera
> 2 : an impression or view of something brief or transitory <a snapshot
> of life back then>
>
> What you are asking is the equivalent of:
>
> "I have a picture of my hometown in the early 1920s. I have a picture of
> my hometown today. I'd like to know about all the
> constructions/destructions that occurred in between".
>
> I can tell you that with my own hometown if you miss some information
> about the 1940s it will be rather 
> difficult.http://virtedit.free.fr/app00014.JPG?41,45
>
> The only thing you have (in the DBA_HIST views) is counters about the
> number of executions in the interval, the equivalent (to keep with my
> image) of "that building that you see standing here has been
> photographed that many times since the previous snapshot you have".
>
> In other words, you CANNOT have a list of everything that happened. But
> if the pattern of what is running is regular enough, which is generally
> the case, you can with confidence say what put brought your database to
> its knees.
>
>
> Stephane Faroult
> RoughSea Ltd <http://www.roughsea.com> <http://www.roughsea.com>
> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd> 
> <http://www.youtube.com/user/roughsealtd>
>
>
> Ojha, Ajeet (GE Healthcare, consultant) wrote:
>
>
>
>  Hello
>
> I want to get the list of SQL's /pl/sql programs/ and any other thing
> which ran in database during a specific timeframe say 10am to 12 am.
>
> We are using oracle 10.2.0.4 and AWR snaps are being taken at every 30
> minutes.
>
> What I really want is write a SQL which will take the AWR snap numbers
> as input and then give me all the SQL's executed between those 2 sanps.
>
> Really appreciate if someone can throw some light on this.
>
> Thanks
>
>
>
>
>
>      --//www.freelists.org/webpage/oracle-l
>
>
>
>
>    -- //www.freelists.org/webpage/oracle-l

Other related posts: