文章目录
1.About check_mk metric:ORA_Archive_LogSwitches
This is a custom monitoring metric on check_mk platform,
the threshold is defined in /usr/lib/check_mk_oracle/MAIN/conf1/perf_ORCL_arc_disk.json
[oracle@IDPCN-IYCost conf]$ cat perf_ORCL_logswitches.json
{"target_info":[{
"perf_min":"0",
"chk_interval":5000,
"output_cmd":"echo",
"post_action":"",
"perf_crit":"120",
"chk_cmd":"sh run_logswitches.sh",
"perf_max":"0",
"chk_result_separator":";",
"perf_warn":"60",
"database":"ORCL"
notice the two threshold value:“perf_warn”:“60”,“perf_crit”:“120”,mean the times of log switch , which will trigger warning alarm and 95%trigger critical alarm if reach the threshold.
2.Solution
2.1 get the times of log switching
select to_char(first_time,'yyyy-mm-dd hh24') first_time,count(*)
from v$log_history
WHERE TO_CHAR(FIRST_TIME, 'yyyymmdd')='20230324' group by to_char(first_time,'yyyy-mm-dd hh24') order by FIRST_TIME desc
The above output results show that the log switch reached 61 times at three o’clock in the morning
2.2 Find out the tables with most block changing
the sql text is as below:
select *
from (SELECT to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI') snap_time,
dhsso.object_name,
SUM(db_block_changes_delta)
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhsso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhsso.obj#
AND dhss.dataobj# = dhsso.dataobj#
AND to_char(begin_interval_time, 'YYYY-MM-DD HH24') like '2023-03-24 03%'
GROUP BY to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'), dhsso.object_name
order by 3 desc)
where rownum <= 5;
—Notice:in the “where” condition, to_char(begin_interval_time, ‘YYYY-MM-DD HH24’) like ‘2023-03-24 03%’ ,please replace with actual time happened
the output result will be:
2.3 find out the sql statement related to MOSALED
Here, The table with the most block changing is MOSALED, so we try to find out the sql statement related to MOSALED
SELECT to_char(begin_interval_time, 'YYYY_MM_DD HH24:MI'),
dbms_lob.substr(sql_text, 4000, 1),
dhss.instance_number,
dhss.sql_id,
executions_delta,
rows_processed_delta
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE UPPER(dhst.sql_text) LIKE '%MOSALED%'
AND dhss.snap_id = dhs.snap_id
AND dhss.instance_Number = dhs.instance_number
AND dhss.sql_id = dhst.sql_id
AND to_char(DHS.begin_interval_time, 'YYYY-MM-DD HH24')='2023-03-24 03'
output result is as below:
2.4 Share these sql information with relevant person
Final, we will pass these sql information to relevant person, and discuss root cause together with them