RE: how to get the list of SQL's
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <tim@xxxxxxxxx>, <gus.spier@xxxxxxxxx>
- Date: Thu, 1 Apr 2010 09:07:43 -0500
More precisely - queries are not captured in the REDO stream; hence log miner
can not show you something that isn't in the REDO files.
Log miner doesn't capture anything; it only shows you what is in the redo
stream.
-----------------------
Ric Van Dyke
Hotsos Enterprises
-----------------------
Hotsos Symposium
March 7 - 11, 2010
I was there, were you?
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Tim Gorman
Sent: Thursday, April 01, 2010 9:57 AM
To: gus.spier@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx; Ojha, Ajeet (GE Healthcare, consultant);
sfaroult@xxxxxxxxxxxx
Subject: Re: how to get the list of SQL's
Log Miner does not capture queries.
Gus Spier wrote:
Is this not a case for LogMiner?
I, myself, have not had the opportunity (or the leisure) to try my hand at it,
but it sounds like it should fit the bill.
Regards,
Gus
On Thu, Apr 1, 2010 at 4:45 AM, Stephane Faroult <sfaroult@xxxxxxxxxxxx> 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>
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
>>
>>
>
>
>
>
--
//www.freelists.org/webpage/oracle-l
-- //www.freelists.org/webpage/oracle-l
Other related posts:
