Re: how to monitor the progress of inserts

On Tue, 01 Feb 2005 11:04:35 -0500, Mladen Gogala <mgogala_at_allegientsystems.com> wrote:
> Juan Carlos Reyes Pacheco wrote:
>
> >Hi in 10g is automatic,
> >
> What is?

everything - haven't you read the press releases

automatic storage
automatic tuning
automatic query rewriting at every opportunity automatic coffee brewing

ok one of these enhancements didn't quite make it  

> >In 9i I never tried but enablig monitoring to
> >the table, it will update statistics, of deletes and inserts, but
> >maybe will need to comit.
> >
> >
>
> Maybe it will?? Of course it will. Oracle obeys ACID properties which
> means that unwashed reads are not allowed. Besides, monitoring is used
> for something entirely different.

Sadly, and for this purpose Juan is entirely correct. table monitoring doesn't take account of rollbacks so unwashed 'reads' are allowed for the purpose of deciding whether the approximate stats are approximately out of date by an arbitrary amount. They are however recorded precisely if not accurately.

> If Sonja is inserting a huge batch of
> rows into the database, it might be logical to assume that the table
> she's inserting rows into will be shown
> as used in V$OBJECT_USAGE. That is all that monitoring will show you.

Fortunately for sonia who is interested in a long running statement there is a dictionary view with the rather handy name V$SESSION_LONGOPS which will contain pertinent information for her. The only gotcha that I can see is that because the time estimates are made for the operation that the session is performing (eg a tablescan) rather than for the entire statement you don't know how long the *statement* will take in advance.

> --
> Mladen Gogala
> Oracle DBA
> Ext. 121

I keep ringing extension 121 but get number unobtainable, perhaps this is a good thing.

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