Re: how can I proove Oracle doesn't measure every SQL-execution

From: Mathias Magnusson <>
Date: Tue, 21 Apr 2009 10:50:43 +0200
Message-ID: <>

Managed to miss the list again. Trying to remedy this in this reply so this becomes part of the public conversation on this topic.

Wouldn't the traditional solution to that problem be to dig deeper and deeper into OWI and understand what actually causes the problem? This does not seem to be a situation where you want or need ALL statements, just lots of data about the ones causing you a problem. Starting with OEM and AWR reports and then digging into OWI by hand is probably the approach you want to take. It is not a five minute remedy, but knowing how long SQL statements take does not in any way measure their impact on the overall system.

And it gives everyone a chance to learn more about OWI as every chance to dig into it surely teaches everyone, no matter the skill level, something new.


On Tue, Apr 21, 2009 at 10:34 AM, Martin Berger <>wrote:

> Mathias,
> thank you for the reply.
> I think sql_trace on db_level will not be an potion, as it's a diagnostic
> tool and not a monitoring tool. (and of course, it would kill the
> performance totally).
> The main reason behind the question is:
> *) we have performance problems
> => the application-owners prefer bashing the infrastructure (my boss)
> => he is to weak to go the right way = tell them to instrument their code
> and measure it
> => he forces me to do this stupid research instead telling the
> apps-owners how to tune.
> so MY benefit is to tell my boss to get bigger balls AND tell the apps guys
> to write instrumented code.
> THEIR benefit is to KNOW they cannot bash us any longer without the
> drawback of additional work on their side.
> I'm not sure if I can write this on the list in these words, but I can in a
> private fork ;-)
> best regards,
> Martin
> Am 21.04.2009 um 10:24 schrieb Mathias Magnusson:
> Even without discussing the possibility (I guess you could have sql_trace
> on for the whole DB...), doing it with no impact would be hard... Depending
> on what they want, but *every* requires logging and not sampling.
> Still, what is the benefit they want to derive from this. Maybe it's
> possible to satisfy their requirement in a different way than what they have
> decided the solution to be?
> Mathias
> On Tue, Apr 21, 2009 at 10:17 AM, Martin Berger <
> > wrote:
>> Hi List,
>> Today I have to face a more or less non technical question:
>> I have to search, ifthere is any possibility to measure the execution time
>> of [any|set of|every] SQL thrown onto a database.
>> Even I'm pretty sure my boss doesn't know exactly what he wants, I would
>> translate it to an ideal view similar to
>> instance_id, session_id, username, start-time, sql_id,
>> sql_child_id, end-time, ...
>> for a given periode into the past (let's say 30 min minimum).
>> Does anyone know how to convince the management there is no way (either
>> oracle internals or 3rd party products) to do this?
>> In this particular case I doesn't even know how to open a SR at metalink
>> or ask for external support to clairfy.
>> thanks in advance,
>> Martin
>> --

Received on Tue Apr 21 2009 - 03:50:43 CDT

Original text of this message