Re: how can I proove Oracle doesn't measure every SQL-execution
Date: Tue, 21 Apr 2009 09:40:11 -0400
Message-ID: <9256898f0904210640l4dbed227u559b896706666576_at_mail.gmail.com>
On Tue, Apr 21, 2009 at 4:17 AM, Martin Berger <martin.a.berger_at_gmail.com> 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).
I know this isn't the answer your looking for but I would use statspack. (Unless you have the diagnostics pack or some other 3rd party software purchased.). Identify the top SQL over the period of time he wanted monitored and hand that over to the developers and say fix it. Execution time may not be a good indicator of bad queries depending on server performance at the time. If the server is IO bound you may want to base it on physical reads, etc. I have statspack scheduled to run every 30 minutes and keep the data for a month. That way I can go back and generate reports over any set time period.
Have you tried working directly with the developers? As a part of proactive monitoring, I try to identify poorly performing SQL and talk to the developer(s) directly. My personal experience has been that they are usually slightly embarrassed that their code is causing issues and will fix it. Sometimes there is no way around the bad queries (cost, business reasons, etc) but at least i've done my job and identified it.
My .02
L8r
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 21 2009 - 08:40:11 CDT