• Article Type: General
• Product: Aleph
• Product Version: 20

Description:
Every day on my server, at 3:10 p.m., something kicks in that jumps my disk I/O and CPU up dramatically. I've started keeping track and it is something that happens on a weekday only and always hits at that time.

I've been unable to find anything to account for this. We run all of our jobs through cron and job_list jobs on the server and there is nothing which should be kicking in at that time frame.

[Later:]

Here is some additional information which I was able to find by looking in Oracle's tables:

1 select log_id, job_name, status, CPU_USED,
2 to_char(log_date, 'DD-MON-YYYY HH24:MI') log_date
3 from dba_scheduler_job_run_details
4 where to_char(log_date, 'DD-MON-YYYY HH24:MI') like '%29-JUL-2010 15%'
5* order by log_date
sys@ALEPH20> /
**** Hit return to continue ****

LOG_ID JOB_NAME STATUS CPU_USED LOG_DATE
---------- ------------------------------ ---------- -------------------- -----------------------
7700 MGMT_CONFIG_JOB SUCCEEDED +000 00:00:00.03 29-JUL-2010 15:00
7701 MGMT_CONFIG_JOB_1 SUCCEEDED +000 00:00:00.25 29-JUL-2010 15:00
7699 DRA_REEVALUATE_OPEN_FAILURES SUCCEEDED +000 00:00:00.00 29-JUL-2010 15:00
7702 ORA$AT_SA_SPC_SY_456 SUCCEEDED +000 00:00:06.74 29-JUL-2010 15:04
7705 ORA$AT_OS_OPT_SY_455 SUCCEEDED +000 00:00:32.47 29-JUL-2010 15:05
7708 ORA$AT_SQ_SQL_SW_457 SUCCEEDED +000 00:02:02.69 29-JUL-2010 15:08
7711 RLM$SCHDNEGACTION SUCCEEDED +000 00:00:00.02 29-JUL-2010 15:11
7712 RLM$EVTCLEANUP SUCCEEDED +000 00:00:00.00 29-JUL-2010 15:30

The thing that takes up the CPU are the "ORA$AT_" lines, especially ORA$AT_SQ_SQL_SW. I see that this regularly runs in that 15:00 hour window each day. Can you help me track down what that might be?

Resolution:
These are related to Oracle Statistics-gathering. Checking with util a/8, I see many tables listed. There should be none.

The AIK (Aleph Installation Kit) has Statistics-gathering turned off, so if you used the AIK to create your Oracle, you should not be seeing this problem.

Looking at http://www.oracle-base.com/articles/11g/AutomatedDatabaseMaintenanceTaskManagement_11gR1.php , I find there are three automated database maintenance tasks:

1. Automatic Optimizer Statistics Collection - Gathers stale or missing statistics for all schema objects (more info). The task name is 'auto optimizer stats collection'.

2. Automatic Segment Advisor - Identifies segments that could be reorganized to save space (more info). The task name is 'auto space advisor'.

3. Automatic SQL Tuning Advisor - Identifies and attempts to tune high load SQL (more info). The task name is 'sql tuning advisor'.

The first is clearly not useful in an Oracle used for Aleph and should be disabled.

See KB 16384-31441 in regard to the second, which is independent of Aleph.

It seems that #3 (Automatic SQL Tuning Advisor) is also connected to the Oracle Statistics and, therefore, of no use to Aleph sites.

These tasks can take substantial CPU. You can disable them as follows:

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/

BEGIN
DBMS_AUTO_TASK_ADMIN.disable(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

when connected as aleph_dba/<password> as sysdba

or any other sys user.

• Article last edited: 10/8/2013