Re: how to monitor the progress of inserts
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: "Gogala, Mladen" <MGogala@xxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 1 Feb 2005 21:01:28 +0000
On Tue, 1 Feb 2005 11:39:16 -0500, Gogala, Mladen
<MGogala@xxxxxxxxxxxxxxxxxxxx> wrote:
>
>
> Niall, monitoring will show table as used, but statistics is updated
> at the time of commit. First, there is no statistics in USER_TABLES that
> would count the number of times that blocks have been referenced by I/O
> and second, statistics for monitored tables is not updated until the
> transaction commits. To do otherwise would seriously break the relational
> rules.
Clearly I wasn't advocating using table monitoring but I would refer
you to the following piece of rule breaking
SQL> connect / as sysdba
Connected.
SQL> drop user u1;
User dropped.
SQL> create user u1 identified by u1
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users;
User created.
SQL> grant create session,create table to u1;
Grant succeeded.
SQL> connect u1/u1
Connected.
SQL> select to_char(sysdate,'hh24:mi:ss') from dual;
TO_CHAR(
--------
20:39:18
SQL> create table t1 (col1 number);
Table created.
SQL> insert into t1
2 select rn
3 from (select rownum rn,'x' from all_objects,all_objects where rownum < 1001
);
1000 rows created.
SQL> rollback;
Rollback complete.
SQL> host time 23:55
SQL> select to_char(sysdate,'hh24:mi:ss') from dual;
TO_CHAR(
--------
23:55:06
SQL> select table_name,inserts,updates,deletes from useR_tab_modifications;
TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ---------- ---------- ----------
T1 1000 0 0
SQL>
precise but wrong and certainly a violation of relational rules. This
is why Oracle refer to it as an estimate.
> Sadly, V$SESSION_LONGOPS is inaccurate, cryptic and, generally
> speaking, useless.
inaccurate yes - but it is only an estimate of progress of the current
operation
cryptic yes - so is a trace file
useless - I'm not so sure, I've found that TIME_REMAINING puts a
*reasonable* value on the time remaining for the current operation, it
does require that you know where you are in the execution plan which
can be tricky and it doesn't measure what you are directly interested
in (the eta for the query) but if it says that the current work has
half an hour left you can safely tell the end user that is hassling
you that it won't be done for at least an hour and probably more. :)
Of course Jonathan's suggestion that cost is the estimated elapsed
time for the query in multiples of the average single block io time
might give us another way to come up with an estimate for the elapsed
time of a query it should be 'estimated' as
cost*sreadtim -- I'm really not sure I'd be prepared to issue that
figure to anyone <vbg>.
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
