Re: how to monitor the progress of inserts

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.

>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