转 如何观察 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
##sample 如何看历史上哪个sql 最消耗undo
##数据库报错ora-30036
# 现在想知道哪条sql 最消耗undo
##111
select
snap_id,
maxquerylen,
undoblks,
maxquerysqlid,
to_char(begin_time,'yyyy/mm/dd hh24:mi') begin,
to_char(end_time,'yyyy/mm/dd hh24:mi') end
from
dba_hist_undostat
order by
undoblks desc,
maxquerylen desc
##222
select
sql_text
from
dba_hist_sqltext
where
sql_id in
(select distinct maxquerysqlid from
(select * from
(select
snap_id,
maxquerylen,
undoblks,
maxquerysqlid,
to_char(begin_time,'yyyy/mm/dd hh24:mi') begin,
to_char(end_time,'yyyy/mm/dd hh24:mi') end
from
dba_hist_undostat
order by
undoblks desc,
maxquerylen desc
)
where rownum<11
)
);
|
|
DBA_HIST_UNDOSTAT.MAXQUERYSQLID will help to point out 'SQL identifier of the longest running SQL statement in the period' https://docs.oracle.com/cd/E18283_01/server.112/e17110/statviews_4068.htm You may also want to review top SQLs/Events in AWR report during this issue period. Also, you can configure a system-wide "triggered" event, use something like SQL> alter system set events '30036 trace name ERRORSTACK level 3'; A trace will be generated when ora-30036 happens After trace file is captured, please run below to turn off this event SQL> alter system set events '30036 trace name ERRORSTACK off'; However, please notice the sql that triggers ora-30036 may be just a victim. |
