RE: how to monitor the progress of inserts

Here is what I use:

• import progress=20 select to_char(rows_processed, '999,999,999') as "COUNT", sysdate, sql_text from ( select rows_processed, sysdate, sql_text from v$sqlarea where sql_text like 'INSERT %INTO %' AND COMMAND_TYPE =3D2 AND OPEN_VERSIONS > 0 order by 1 desc )=20 where rownum =3D 1

Eric Yen

-----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 5:09 PM To: Mladen Gogala
Cc: MGogala_at_allegientsystems.com; 'Niall Litchfield'; Oracle-L (E-mail) Subject: Re: how to monitor the progress of inserts

At 03:50 PM 2/1/2005, Mladen Gogala wrote:
> > 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).
>
>Yes, but then this statistics is meaningless. What does it mean to have

>1000 inserts without any consequence to the table?

You have to use the v$ views in the context they are meant for. This=20 particular table is meant to help dbms_stats to decide whether the=20 statistics are "stale" and should be refreshed. It is NOT an audit view. If=20
you talk about useless views this is a truly useless view, even if it=20 didn't suffer from the demonstrated shortcoming. What does it mean to have=20
1000 inserts, even if those inserts actually made into the table??=20 Statistics could be "stale" if much less than 10% of the content has=20 "changed" and for other tables nothing changes materially in the statistics=20
even if you exchange 100% for new data. I would never base statistics=20 gathering on the content of this view.
PS. there are people who are very knowledgeable about Oracle who consider=20
the data in many of the "cherished" v$ views as useless, meaningless, even=20
misleading.

>I wouldn't generalize just like that. I don't find V$SESSION_LONGOPS
very=20
>useful,
>period. It has never helped me with anything. It most certainly did not

>help me
>to see how much is left of any large job. While V$ tables in general
are=20
>remarkably
>useful, this particular one is useless.

Obviously our mileages vary. I use it frequently to see the progress of=20 full scans and even calculate the estimated finish time. I generally find=20
it more precise than Microsoft's progress bar. And as I said. if the=20 progress is not linear but slows down ( who cares if it speeds up :-) )=20 then that is a good piece of information as well to go and find the cause=20
for the slowdown.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com=20

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



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