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