Re: How to monitor the progress of a SQL command?

Sybrand,

On Thu, 01 Feb 2001 13:23:53 GMT, sybrandb_at_my-deja.com wrote:

>In article <8pai7tc4r63o0imeoh6ln3u5492hkt8k29_at_4ax.com>,
> Dino Hsu <dino1_at_ms1.hinet.net> wrote:
>> Dear all,
>>
>> When we are running a SQL command in a SQL Plus window, the program
>> stops responding and enters a pending state until it completes.
>> However, if this is a long process, we don't know either the
>> percentage of completion nor the expected comletion time, it can be
>> confusing and annoying. Is there any way of doing this? Thanks in
>> advance.
>>
>> Regards,
>> Dino
>>
>
>Sure.
>You need to have the sid and the serial# of the process (at least the
>sid). You can get the sid by
>select sid from v$session where audsid=userenv('sessionid')
>Then you can query v$session_wait
>v$sess_io
>and v$sqlarea.
>V$sqlarea contains the sqlstatement and progress info
>The current sqlstatement is kept by
>sql_address and sql_hash_value in v$session. Both columns act as key
>to v$sqlarea.
>
>
>Hth,

I try to observe the 'sys' views you mentioned in the process of SQL execution or report generation, the columns consistent_gets and physical_reads in v$sess_io are changing, but no columns in v$sqlarea are changing. Most important of all, I still cannot find out the expected completion time of the jobs or what percentage of the jobs have been done. Thanks again.

Regards,
Dino Received on Thu Mar 01 2001 - 22:04:32 CST