RE: how to monitor the progress of inserts

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.

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 ;-)

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.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Wolfgang Breitling
> Sent: Tuesday, February 01, 2005 3:53 PM
> To: MGogala_at_allegientsystems.com
> Cc: 'Niall Litchfield'; Oracle-L (E-mail)
> Subject: Re: how to monitor the progress of inserts
>
>
> As Niall demonstrated so beautifully, there are no relational rules when
> it comes to v$ views (actually the x$ "tables" upon which the views are
> built).
> As to V$SESSION_LONGOPS being inaccurate, cryptic and useless, I can't
> follow you there. I don't find them any more cryptic than any other
> table or view and within the confines of being an estimate, I find them
> remarkably accurate and extremely useful in gaging eta of a process - or
> detecting escalating deterioration which is a very useful observation in
> its own right.
>
> Gogala, Mladen wrote:
>
> > To do otherwise would seriously break the relational
> > rules. Sadly, V$SESSION_LONGOPS is inaccurate, cryptic and, generally
> > speaking, useless.
> --
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l