Re: how to get the list of SQL's

  • From: Tim Gorman <tim@xxxxxxxxx>
  • Date: Fri, 02 Apr 2010 09:11:08 -0600

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 ("").  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 <>
RoughSea Channel on Youtube <>

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.,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 <>
RoughSea Channel on Youtube <>

Ojha, Ajeet (GE Healthcare, consultant) wrote:

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 and AWR snaps are being taken at every 30

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.






Other related posts: