Re: how to monitor the progress of inserts

On Tue, 01 Feb 2005 21:53:14 -0500, Mladen Gogala <mgogala_at_allegientsystems.com> wrote:
> Larry Elkins wrote:
>
> >I've found V$SESSION_LONGOPS to be very useful, especially if you take the
> >time to watch it while a pig of a query is running you can track how much
> >time is being spent in each part of a query. For example you might notice a
> >lot of time is spent in a sort merge phase, or on a full table scan, etc.
> >
> >
>
> Now, this is a neat idea that I havent thought about before. Thanks.

You should use a GUI occasionally - oh alright I'll stop swearing. So far as I can tell (and lets face it I hate OEM) V$SESSION_LONGOPS is only used in one place in the java OEM for 9i. It isn't used to say how long the whole query will take, but it is used in conjunction with an explain plan tab (that I really hope is a query on V$SQL_PLAN) to explain how how long the *current stage* of the execution plan will take. I've aborted jobs that are scheduled to take another hour just on the intermediate sort/merge stage because, well it was a great tip off that I'd screwed up on the sql.

I don't know of any other way to get this info *while the query is still running*. 10046 will tell me all the gory details, but only *after* I have suffered the consequences (or been paid the overtime for my own bad estimate - you decide). longops can tell me I screwed up big time* mid error*. Previously it was obviously the fault of

1. the network or
2. the desktop build team

Niall

>
> >I have seen occasional cases where the TOTALWORK column (estimated number of
> >blocks for example) was off, so the estimate of time remaining was off. This
> >has primarily been in the case of hash and sort joins, so you end up seeing
> >the time remaining start going negative as the SOFAR value begins to exceed
> >to TOTALWORK value. That's frustrating ;-)
> >
> >
> I noticed that. I somehow stopped trusting that table when I saw the
> negative time for the first time.
> The next thing that I expected to see in the OPNAME column was "Being
> beamed up, by Scottie".
>
> >But when dealing with large complex queries it can be very helpful in
> >tracking how much time is being spent in the various operations -- e.g. hash
> >join, sort output, an fts, combinations of those, etc.
> >
> >
>
> Thanks again for this suggestion. I haven't thought of using the table
> for optimization purposes.
> I was using it for "are we there yet" questions. The answer
> approximately as precise as the usual "soon".
>
> --
> Mladen Gogala
> Oracle DBA
>
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
http://www.freelists.org/webpage/oracle-l