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"). 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:
-- http://www.freelists.org/webpage/oracle-lThe 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> RoughSea Channel on Youtube <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] 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> RoughSea Channel on Youtube <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