目录
一、问题现象：
1、现象描述
有10分钟左右的时间报工不显示生产单元。还特别容易退，具体时间是周末的6点，工作日的11点40左右
2、查看根据描述，基本断定和统计信息收集优化有关。查看日志。
Sun Apr 16 06:00:00 2023 Setting Resource Manager plan SCHEDULER[0x32DF]:DEFAULT_MAINTENANCE_PLAN via scheduler window Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter Sun Apr 16 06:00:00 2023 Starting background process VKRM Sun Apr 16 06:00:00 2023 VKRM started with pid=120, OS id=290809 Sun Apr 16 06:00:01 2023 Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK" ...... Sun Apr 16 14:19:07 2023 DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file. Errors in file /oracle/app/oracle/diag/rdbms/mesorcl/mesorcl/trace/mesorcl_j000_12304.trc: ORA-04021: timeout occurred while waiting to lock object
Mon Apr 17 11:49:34 2023 Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 134217728 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query: select total_size,awr_flush_emergency_count from v$ash_info; Mon Apr 17 11:52:43 2023
3、同时查看此期间的AWR日志
发现存在pin的锁争夺。
二、解决问题
1、调整ash大小
（1）查看当前（默认1m)
select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and
x.ksppinm ='_ash_size'
order by
translate(x.ksppinm, ' _', ' ');
（2）建议为128M，一般我们设置128*1.5=192m
SQL>alter system set "_ash_size"=201326592;
2、调整更改硬解析模式
alter system set cursor_sharing=force scope=both;
绑定变量和常量都可以进行软解析。
3、更改tunning运行事件（一般默认是20分钟）
（1）查看默认时长20分钟（1200秒）
select parameter_name, parameter_value from dba_advisor_parameters where task_name = ‘sys_auto_sql_tuning_task’ and parameter_name = ‘local_time_limit’;
（2）调整为2小时（7200秒）
begin dbms_sqltune.set_tuning_task_parameter(‘sys_auto_sql_tuning_task’, ‘local_time_limit’, 7200); end; /
（3）查看是否已经完成
status是状态。可以查看开始运行时间和结束时间。
select * from dba_advisor_tasks where task_name = 'SYS_AUTO_SQL_TUNING_TASK';
