目录
一、问题现象:
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';