Oracle (11.2.0.1):如何识别当前由 UPDATE 语句更新的行

2021-12-29 00:00:00 sql-update sql oracle view

我的表包含大约 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

相关文章