Oracle-数据库更新RU补丁出现执行缓慢问题

勇敢牛牛的笔记 2023-02-05

1497

问题背景:

在Oracle19c进行19.15RU补丁升级时，数据库通过datapatch工具进行RU补丁更新，datapatch执行缓慢(1个小时+)，长时间未返回信息。

问题:

数据库补丁更新datapatch执行缓慢(1个小时+)，长时间会返回信息。

问题原因:

datapatch进行补丁更新，会对数据库的数据字典进行统计信息收集，由于历史统计信息字典表OPTSTAT_XXX数据量太大(195G+),导致统计信息收集长时间未完成，数据库补丁更新datapatch执行缓慢。

问题分析:

查看datapatch执行窗口,datapatch -verbose命令已经执行超过1个小时，命令没有任何输出，日志sqlpatch_xxxx.log也没有任何的输出。

进到数据库里面查看当前的长时间执行会话。

SELECT SE.SID,
 OPNAME,
 TRUNC(SOFAR  TOTALWORK * 100, 2) || '%' AS PCT_WORK,
 ELAPSED_SECONDS ELAPSED,
 ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR)  SOFAR) REMAIN_TIME,
 SQL_FULLTEXT
FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE
WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE and SOFAR>0
 AND SL.SID = SE.SID
 AND SOFAR != TOTALWORK
 ORDER BY START_TIME

可以看到datapatch的会话正在进行数据字典的统计信息收集，当前收集的对象为历史统计信息表WRI$_OPTSTAT_HISTGRM_HISTORY，语句已经执行了2160秒，估算完成时间为18090秒。

查看当前OPTSTAT组件表总大小，当前的大小为195G。

COL OCCUPANT_NAME FOR A25
COL OCCUPANT_DESC FOR A20
COL SCHEMA_NAME FOR A20
COL MOVE_PROCEDURE FOR A40
COL MOVE_PROCEDURE_DESC FOR A20
SET LINES 200 PAGES 1200
SELECT SCHEMA_NAME,OCCUPANT_NAME,SPACE_USAGE_KBYTES/1024/1024 GB
FROM V$SYSAUX_OCCUPANTS WHERE ROWNUM <20 ORDER BY 3;

综合上述信息，我们可以确认datapatch执行缓慢的原因为正在进行数据库的数据字典统计信息收集，而由于当前历史统计信息字典表OPTSTAT_XXX数据量太大195G+,导致统计信息收集长时间未完成，数据库补丁更新datapatch执行缓慢。

问题解决:

由于统计信息收集的预估完成时间为18090秒超过了补丁升级窗口时间，所以没法等待统计信息收集的完成，需要人为进行干预解决。

根据Oracle官方文档Datapatch Catbundle script hung at "execute dbms_registry.loaded('SDO')" (Doc ID 2102142.1)里面的说明，可以通过隐含参数_optim_dict_stats_at_db_cr_upg临时禁用在数据库补丁更新时进行数据字典统计信息收集。

_optim_dict_stats_at_db_cr_upg默认的参数为TRUE。

所以解决的方案是先取消datapatch的执行，再临时禁用数据库补丁更新时进行数据字典统计信息收集，最后重跑datapatch

取消（ctrl+c）datapatch执行。

Died at u01/app/oracle/product/19.0.0/db_1/rdbms/admin/catcon.pm line 18217
--如果无法取消datapatch命令，可以Kill掉datapatch的OS进程
bash-5.0$ ps -ef |grep -i datapat 
  oracle 14418408 11077094   0 22:33:09  pts/6  0:00 grep -i datapat
  oracle  7471996 21102938   0 20:42:38  pts/3  0:00 bin/sh ./datapatch -verbose
bash-5.0$ kill -9 7471996

执行datapatch rollback回滚数据库的补丁信息，确保先前执行的datapatch信息被回退干净。

datapatch -rollback -verbose

如果执行回滚失败，出现Unable to acquire sqlpatch global lock in EXCLUSIVE mode because another datapatch session is currently running，则说明之前的datapatch会话还存在于数据库，需要进行清理。

 --查杀等待事件enq: UL - contention所在的堵塞会话
 --因为datapatch会话请求持有的是用户定义的'UL'  (User-defined Lock)锁
select blocking_session
from gv$session
where event='enq: UL - contention'


BLOCKING_SESSION
----------------
            6051
SQL> select spid
  2  from v$process
  3  where addr in (select paddr from v$session where sid=6051);


SPID
----------------------------------
17564290


bash-5.0$ kill -9 17564290

修改_optim_dict_stats_at_db_cr_upg参数为FALSE。

alter system set "_optim_dict_stats_at_db_cr_upg"=FALSE;

重新执行datapatch -verbose，这一次补丁顺利完成安装用时19分钟，问题得到解决。

./datapatch -verbose


Installing patches...
Patch installation complete.  Total patches installed: 12


Validating logfiles...done
Patch 30128191 rollback (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30128191/23105199/30128191_rollback_XXXX_CDBROOT_2022Dec27_22_46_19.log (no errors)
Patch 33806152 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33806152/24747429/33806152_apply_XXXX_CDBROOT_2022Dec27_22_46_20.log (no errors)
Patch 33808367 apply (pdb CDB$ROOT): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33808367/24710816/33808367_apply_XXXX_CDBROOT_2022Dec27_22_46_19.log (no errors)
Patch 30128191 rollback (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30128191/23105199/30128191_rollback_XXXX_PDBSEED_2022Dec27_22_56_05.log (no errors)
Patch 33806152 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33806152/24747429/33806152_apply_XXXX_PDBSEED_2022Dec27_22_56_08.log (no errors)
Patch 33808367 apply (pdb PDB$SEED): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33808367/24710816/33808367_apply_XXXX_PDBSEED_2022Dec27_22_56_07.log (no errors)
Patch 30128191 rollback (pdb XXXX): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30128191/23105199/30128191_rollback_XXXX_XXXX_2022Dec27_22_56_05.log (no errors)
Patch 33806152 apply (pdb XXXX): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33806152/24747429/33806152_apply_XXXX_XXXX_2022Dec27_22_56_06.log (no errors)
Patch 33808367 apply (pdb XXXX): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33808367/24710816/33808367_apply_XXXX_XXXX_2022Dec27_22_56_06.log (no errors)
Patch 30128191 rollback (pdb XXXX): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/30128191/23105199/30128191_rollback_XXXX_XXXX_2022Dec27_22_56_05.log (no errors)
Patch 33806152 apply (pdb XXXX): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33806152/24747429/33806152_apply_XXXX_XXXX_2022Dec27_22_56_06.log (no errors)
Patch 33808367 apply (pdb XXXX): SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/33808367/24710816/33808367_apply_XXXX_XXXX_2022Dec27_22_56_06.log (no errors)

回退_optim_dict_stats_at_db_cr_upg参数为TRUE

alter system set "_optim_dict_stats_at_db_cr_upg"=TRUE;

OPTSTAT组件问题:

OPTSTAT表数据量大的问题:

OPTSTAT主要用于存放历史的统计信息，默认保留31天，数据量主要由以下因素影响

1 数据库表，索引的数量，表，索引的数量越大，存放历史信息的数据量就越多

2 统计信息收集的方式，频率，每执行一次统计信息收集，都会将旧的统计信息存放到历史统计信息里面，统计信息收集频率越多，存放历史信息的数据量就越多

3 数据保留的期限

数据设置的保留时间越长，存放的数据量就越多

--查看当前的保留期限
select dbms_stats.get_stats_history_retention from dual;
--修改默认的保留期限
exec dbms_stats.alter_stats_history_retention(10);

4 数据清理失败

MMON进程会自动的进行OPTSTAT表数据的清理，但执行的超时时间为5分钟，所以，如果表的数据量过大或者MMON进程资源存在问题，可能导致MMON进程清理表数据超时失败

OPTSTAT表数据清理:

1 默认MMON进程会根据保留期限，定期进行清理，5分钟超时

2 执行命令进行清理

--按天进行清理
begin
for i in reverse 10..100
loop
dbms_stats.purge_stats(sysdate-i);
end loop;
end;
/
--truncate全部表数据
exec DBMS_STATS.PURGE_STATS(DBMS_STATS.PURGE_ALL)

文章转载自勇敢牛牛的笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle-数据库更新RU补丁出现执行缓慢问题

评论