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屋！
