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

问题背景:

在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+cdatapatch执行。

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)