Re: check how many rows processed of a sql statement

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Wed, 8 Jul 1998 12:08:31 +0200
Message-ID: <6nvgbh$m68$1@hermes.is.co.za>

David Shi wrote in message <6nrnj5$s6j$0_at_204.179.92.135>...
>Thanks Tom, I was actually wondering whether it is possible to check before
a
>long-running sql statement get finished, that how many rows has been
processed,
>which gives me an idea about how far is the job. Is this possible?
>

Try the V$SESSTAT table and look at the values for statistic 123, 124 and 125, i.e.

id            descr
123        table scan rows gotten
124        table scan blocks gotten
125        table fetch by rowid

(stats per session)

This will give you some idea of the amount of rows processed thus far. But be careful when using these stats, as you need to consider the execution plan, parallel query slaves if PQ (if enabled and used by the query), etc.

With some queries (doing monthly updates on a data warehouse) we've been getting some pretty accurate estimates regarding the status of the jobs ito the amount of data processed thus far, using the above stats. But as usual, when opening the hood of Oracle and scratching around inside, you always need to make sure that you're not mistaken the radiator for carburator or something else. :-)

regards,
Billy Received on Wed Jul 08 1998 - 05:08:31 CDT