Oracle(11.2.0.1):如何识别当前由UPDATE语句更新的行 [英] Oracle (11.2.0.1) : How to identify the row which is currently updated by the UPDATE statement

问题描述

我的表包含约10亿条记录.我的UPDATE语句花了更多时间来更新大量记录.

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

是否有任何Oracle视图可检查当前更新了多少行?

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

推荐答案

您可以使用该查询监视长时间运行的DML操作和回滚. 如果索引中未包含更新字段，则v$transaction视图中的used_urec字段的值将非常接近行数. 当执行更新操作时，这些值会增加，如果执行回滚，则这些值将减少为零.

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

例如1.如果更新未索引的列，则行数39915830和USED_UREC 40000562大约重合.

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 

会议1

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

会议2 开始更新

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

停止更新

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

例如2.如果更新索引的字段，则行数* 3约为USED_UREC. 39915830 * 3 =〜116705429

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
  )

会议1

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

会议2 停止更新

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

例如3.如果您插入未索引的表，则行数恰好是USED_UREC.

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

会议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

会议2

USERNAME       PROGRAM         COMMAND  USED_UREC   USED_UBLK   COMMAND_1                                             

---

    ASUDS          sqlnavigator.exe      2     500000    5815    Insert

例如4.如果您从未建立索引的表中删除，则行数正是USED_UREC.

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

会议1

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

会议2

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

这篇关于Oracle(11.2.0.1):如何识别当前由UPDATE语句更新的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！