2

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?

Improve this question
5
  • 4
    They will all be updated at once or none. There is no in-between. Commented Jan 16, 2019 at 10:28
  • 1
    By design the update is atomic. You probably want to break the update down in multiple statements. If you still want eventual consistency, then use multiple updates inside a transaction. Commented Jan 16, 2019 at 11:40
  • did you update every single row? if so, it's often faster to just build a new table using a create table as select than doing an update Commented Jan 16, 2019 at 12:48
  • Possible duplicate: stackoverflow.com/questions/15938040/… Commented Jan 16, 2019 at 16:29
  • Put a trigger, do update, disable trigger. Trigger should write log Commented Jan 16, 2019 at 23:25

2 Answers 2

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.