Monitore High-Frequency Statistics

Today, we’re going to see a little script in order to check in High-Frequency Statistics inside the database every day.

Before seeing the script, we’re going to talk about High-Frequency Statistics. This feature, who runs automatically in maintenance windows, was born in 19c. By default, the high-frequency statistics collection occurs every 15 minutes.

Somehow this feature doesn’t replace the standard statistics collection job inside is a complement.

How can we set it? We should set with the procedure DBMS_STATS.SET_GLOBAL_PREFS. in order to do such as switch on/switch off, change the execution interval or even maximum run time.

Switch on/ Switch off (By default).

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON');

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','OFF');

Maximum run time (By default, it’s 3600)

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_MAX_RUN_TIME','3600');

Change the execution interval (By default, it’s 900)

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','900');

Below you can see the script together the email with the summary.

Script:

#!/bin/bash

. /home/oracle/.bashrc

getInfo() {
   sqlplus -s ${VUSER}/${VPASS}@${LOCAL_SID} <<EOF

   COL STATUS      FOR a11    HEAD 'STATUS'
   COL ORIGIN      FOR a20    HEAD 'ORIGIN'
   COL COMPLETED   FOR 99999  HEAD 'COMPLETED'
   COL FAILED      FOR 99999  HEAD 'FAILED'
   COL TIMEOUT     FOR 99999  HEAD 'TIMEOUT'
   COL INPROG      FOR 99999  HEAD 'INPROG'
   COL BEGIN_TIME  FOR a40    HEAD 'BEGIN_TIME'
   COL END_TIME    FOR a40    HEAD 'END_TIME'

   set pagesize 1000 linesize 1000 heading on feedback off echo off
   set space  1 colsep ' ' underline off

   break on ORIGIN
   compute sum of COMPLETED on ORIGIN
   compute sum of FAILED on ORIGIN
   compute sum of TIMEOUT on ORIGIN
   compute sum of INPROG on ORIGIN

   Select Origin,
          Status,
          Start_Time  As Begin_Time,
          End_Time    As End_Time,
          Completed,
          Failed,
          Timed_Out   As Timeout,
          In_Progress As Inprog
     From Dba_Auto_Stat_Executions
    Where Trunc(Start_Time) >= (Sysdate - 7)
    Order By Opid;
    spool off
    exit;
EOF
}

   (
   echo "To: ${EMAIL}"
   echo "Subject:Check high-frequency task"
   echo
   echo "$(getInfo)"
   ) | mailx -t