转如何观察 undo Oracle DML语句回滚开销估算

https://searchdatabase.techtarget.com.cn/7-20392/

--use_urec 详细解读：

select USED_UREC from v$transaction;

4.1、delete 操作　　

4.1.2、一般删除　　

计算公式：USED_UREC=删除表记录数+删除表索引记录数(每个索引每行记录算一条记录) 　　

假设表有2个索引，删除10条记录

4.1.2、通过索引范围条件删除记录　　

USED_UREC=删除表记录数+更新索引块数　　

假设表有1个单字段普通索引，通过索引范围查询10000条记录并删除，每个索引块大块保存200条记录

4.2、update 操作　　

4.2.1、一般更新　　

USED_UREC=更新表记录数+更新索引记录变更数*2(每行索引变更有2个记录，一个是记录原索引指针，另外是记录新索引指针) 　

　假设表有2个索引，更新10条记录的2个字段，其中要更新1个是普通字段，1个是索引字段

4.2.2、通过索引范围条件更新该索引字段　　

USED_UREC=更新表记录数+更新索引块数*2

4.3、insert 操作　　

4.3.1、单条insert (insert into t1 values …) 　　

USED_UREC=新增记录数+表索引个数*新增记录数　　

假设表有2个索引，新增3条记录

3.2、批量insert(insert into t1 select …) 　　

这个非常难准确计算，因为新增记录会利用以前空闲的数据块，只有数据块有记录变化都需要保存数据块对应的回滚记录，同时也保存索引的回滚记录，

所以　　USED_UREC≈新增记录变更表数据块数+∑每个变更表数据块对应变更的索引块数

(也指求和，这种写法表示的就是∑j=1+2+3+…+n。)

假设表有2个索引，新增1000条记录，每个数据块大约可保存600条记录，新增第一个数据块保存了600条记录，同时变更了第1个索引30个索引块，第2个索引40个数据块，新增第2个数据块保存了400条记录，同时变更了第1个索引20个索引块，第2个索引60个数据块

---undo checking

REM srdc_db_undo_ora-30036.sql
REM collect Undo parameters and segment details for troubleshooting ORA-30036 issues.
define SRDCNAME='DB_Undo_ORA-30036'
set pagesize 200 verify off sqlprompt "" term off entmap off echo off
set markup html on spool on
COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'|| to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
spool &&SRDCSPOOLNAME..htm
select 'Diagnostic-Name ' "Diagnostic-Name ", '&&SRDCNAME' "Report Info" from dual
union all
select 'Time ' , to_char(systimestamp, 'YYYY-MM-DD HH24MISS TZHTZM' ) from dual
union all
select 'Machine ' , host_name from v$instance
union all
select 'Version ',version from v$instance
union all
select 'DBName ',name from v$database
union all
select 'Instance ',instance_name from v$instance
/
set echo on

--***********************Undo Parameters**********************

SELECT a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm in ( '_undo_autotune' , '_smu_debug_mode' ,
'_highthreshold_undoretention' ,
'undo_tablespace' , 'undo_retention' , 'undo_management' )
order by 2
/
--**********************Tuned Undo Retention**********************
SELECT MAX(TUNED_UNDORETENTION), MAX(MAXQUERYLEN),SUM(NOSPACEERRCNT),SUM(EXPSTEALCNT) FROM V$UNDOSTAT;

--**********************Status of the undo blocks**********************

select tablespace_name,
round(sum(case when status = 'UNEXPIRED' then bytes else 0 end) / 1048675,2) unexp_MB ,
round(sum(case when status = 'EXPIRED' then bytes else 0 end) / 1048576,2) exp_MB ,
round(sum(case when status = 'ACTIVE' then bytes else 0 end) / 1048576,2) act_MB
from dba_undo_extents group by tablespace_name
/
select count(*) from dba_rollback_segs where status='OFFLINE'
/
--**********************Free space available within the Undo tablespace**********************

SELECT SUM(BYTES) FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME in (select value from v$parameter where name= 'undo_tablespace')
/
SELECT file_name,autoextensible,bytes/ 1048576 FROM dba_data_files WHERE tablespace_name in (select value from v$parameter where name= 'undo_tablespace' )
/
--**********************FBDA related information**********************

SELECT a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE '%flashback_archiver%'
/
set echo off
set sqlprompt "SQL> " term on
set verify on
spool off
set markup html off spool off
PROMPT
PROMPT REPORT GENERATED : &SRDCSPOOLNAME..htm
set echo on

###########sample 2 ora-01555

SQL> select sum(a.bytes)/1024/1024 as undo_size from v$datafile a, v$tablespace b, dba_tablespaces c where c.contents = 'UNDO' and c.status = 'ONLINE' and b.name = c.tablespace_name and a.ts# = b.ts#;

UNDO_SIZE
----------
48045.875

select sum(bytes)/1024/1024 "mb" from dba_free_space where tablespace_name ='UNDOTBS1'
mb
----------
17704.5

select sum(bytes)/1024/1024 "mb" from dba_free_space where tablespace_name ='UNDOTBS2'
mb
----------
24456.6875

select tablespace_name , sum(blocks)*8/(1024) space_in_use from dba_undo_extents where status IN ('ACTIVE','UNEXPIRED') group by tablespace_name;

SQL> select tablespace_name , sum(blocks)*8/(1024) space_in_use from dba_undo_extents where status IN ('ACTIVE','UNEXPIRED') group by tablespace_name;

TABLESPACE_NAME SPACE_IN_USE
------------------------------ ------------
UNDOTBS2 392
UNDOTBS1 1089.375

select tablespace_name , sum(blocks)*8/(1024) reusable_space from dba_undo_extents where status='EXPIRED' group by tablespace_name;
TABLESPACE_NAME REUSABLE_SPACE
------------------------------ --------------
UNDOTBS2 772.0625
UNDOTBS1 3641.0625

with free_sz as ( select tablespace_name, sum(f.bytes)/1048576/1024 free_gb from dba_free_space f group by tablespace_name ) , a as ( select tablespace_name , sum(case when status = 'EXPIRED' then blocks end)*8/1048576 reusable_space_gb , sum(case when status in ('ACTIVE', 'UNEXPIRED') then blocks end)*8/1048576 allocated_gb from dba_undo_extents where status in ('ACTIVE', 'EXPIRED', 'UNEXPIRED') group by tablespace_name ) , undo_sz as ( select tablespace_name, df.user_bytes/1048576/1024 user_sz_gb from dba_tablespaces ts join dba_data_files df using (tablespace_name) where ts.contents = 'UNDO' and ts.status = 'ONLINE' ) select tablespace_name, user_sz_gb, free_gb, reusable_space_gb, allocated_gb , free_gb + reusable_space_gb + allocated_gb total from undo_sz join free_sz using (tablespace_name) join a using (tablespace_name) ;

SQL> /

TABLESPACE_NAME USER_SZ_GB FREE_GB REUSABLE_SPACE_GB ALLOCATED_GB TOTAL
------------------------------ ---------- ---------- ----------------- ------------ ----------
UNDOTBS2 .487304688 23.8834839 .754821777 .380981445 25.0192871
UNDOTBS2 24.5319824 23.8834839 .754821777 .380981445 25.0192871
UNDOTBS1 .487304688 17.2749023 3.56951904 1.05395508 21.8983765
UNDOTBS1 21.4110718 17.2749023 3.56951904 1.05395508 21.8983765

巴特西

转如何观察 undo Oracle DML语句回滚开销估算

最新文章

热门文章

巴特西

转 如何观察 undo Oracle DML语句回滚开销估算

最新文章

热门文章

转如何观察 undo Oracle DML语句回滚开销估算