Re: how to monitor the progress of inserts

  • From: Mladen Gogala <mgogala@xxxxxxxxxxxxxxxxxxxx>
  • To: elkinsl@xxxxxxxxx
  • Date: Tue, 01 Feb 2005 21:53:14 -0500

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


Other related posts: