CODEWITHSUNDEEP
sqloracle-databaseviewsql-update
Rajesh
Rajesh

Reputation: 45

Oracle (11.2.0.1) : How to identify the row which is currently updated by the UPDATE statement

My table contains around 1 Billion Records. My UPDATE statement took more time to update the huge volume of records.

Is there any Oracle view to check how many rows are updated currently?

Upvotes: 2

Views: 1466

Answers (2)

Dmitry Demin
Dmitry Demin

Reputation: 2113

You can use the query to monitor long-running DML operations and rollback. If the update field is not included in the index, then the value of the used_urec field from the v$transaction view will be very close to the number of rows. When the update operation is performed, these values increase, if rollback is performed, the values are reduced to zero.

V$TRANSACTION lists the active transactions in the system.
    USED_UREC Number of undo records used
    USED_UBLK Number of undo blocks used
select
   substr(s.username,1,28) username,
   substr(s.program,1,25) program,
   s.command,
   t.used_urec,
   t.used_ublk,
   decode(s.command,
     0,'No Command',
     1,'Create Table',
     2,'Insert',
     3,'Select',
     6,'Update',
     7,'Delete',
     9,'Create Index',
     15,'Alter Table',
     21,'Create View',
     23,'Validate Index',
     35,'Alter Database',
     39,'Create Tablespace',
     41,'Drop Tablespace',
     40,'Alter Tablespace',
     53,'Drop User',
     62,'Analyze Table',
     63,'Analyze Index',
     s.command||': Other') command
from 
   v$session     s,
   v$process     p,
   v$transaction t
where s.paddr = p.addr
and s.taddr = t.addr 
order by 1

For example 1. If you update a column that is not indexed, then the number of rows 39915830 and USED_UREC 40000562 approximately coincide .

create table test_update(p1,p2,p3,p4 )
  PCTFREE     1
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  arhiv_data
as 
SELECT a.n_p_u, a.id_reg, a.id_vag, a.vrsvop
  FROM a_vag_atr a;

SELECT count(*)
          FROM test_update a
==>
COUNT(*)                                     
-------------------------------------------- 
                                    39915830

Session 1

update test_update 
set p2=1234567890
==>
39915830 row(s) updated

Session 2 start update

USERNAME       PROGRAM         COMMAND  USED_UREC   USED_UBLK   COMMAND_1                                             
---------------- ---------------------- ------------------- --------------------
ASUDS          sqlnavigator.exe      6  4181959    62690           Update

stop update 

USERNAME       PROGRAM         COMMAND  USED_UREC   USED_UBLK   COMMAND_1                                             
---------------- ---------------------- ------------------- --------------------
ASUDS          sqlnavigator.exe      6   40000562   601871       Update

For example 2. if you update the field indexed then the number of lines * 3 is approximately the USED_UREC. 39915830 *3=~116705429 

create table test_update(p1,p2,p3,p4 )
  PCTFREE     1
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  arhiv_data
as 
SELECT a.n_p_u, a.id_reg, a.id_vag, a.vrsvop
  FROM a_vag_atr a;

SELECT count(*) FROM test_update a
==>
COUNT(*)                                     
-------------------------------------------- 
                                    39915830 

CREATE INDEX test_ind ON test_update
  (
    p1                              ASC
  )

Session 1

update test_update 
set p1=12
==>
39915830 row(s) updated

Session 2 stop update

USERNAME       PROGRAM         COMMAND  USED_UREC   USED_UBLK   COMMAND_1                                             
---------------- ---------------------- ------------------- --------------------
ASUDS          sqlnavigator.exe      6  116705429   1392538        Update

For example 3. if you insert into table not indexed then the number of rows is exactly the USED_UREC.

create table test_update(p1,p2,p3,p4 )
      PCTFREE     1
      INITRANS    1
      MAXTRANS    255
      TABLESPACE  arhiv_data

 SELECT count(*)
              FROM test_update a
==>
    COUNT(*)                                     
    -------- 
          0

Session 1

declare
 i pls_integer:=1;
begin
for i in 1..500000 loop
insert into test_update(p1,p2,p3,p4)
values(1,2,3,sysdate); 
end loop;
end;

select count(*) from  test_update
==>
COUNT(*)                                     
----------- 
     500000

Session 2

USERNAME       PROGRAM         COMMAND  USED_UREC   USED_UBLK   COMMAND_1                                             

    ASUDS          sqlnavigator.exe      2     500000    5815    Insert

For example 4. if you delete from table not indexed then the number of rows is exactly the USED_UREC.

Session 1

 SELECT count(*) FROM test_update a
    ==>
        COUNT(*)                                     
        -------- 
         500000
delete from  test_update
==>
500000 row(s) deleted

Session 2

    USERNAME       PROGRAM         COMMAND  USED_UREC   USED_UBLK   COMMAND_1                                             
---------------- ---------------------- ------------------- --------------------
        ASUDS      sqlnavigator.exe   7      500000      9616         Delete

Upvotes: 1

eaolson
eaolson

Reputation: 15094

Are you saying you want to monitor the progress of a long-running UPDATE statement? No, I don't know any straightforward way of doing that. No session outside the one running the update statement can see the changes in the table until you commit them.

What you could do is write a PL/SQL program that does it in batches and issues a COMMIT statement in between. That way, a different session could watch the table and see it as pieces of it were updated.

Upvotes: 1

Related Questions