BSLN_MAINTAIN_STATS_JOB

 BSLN_MAINTAIN_STATS_JOB

ORACLE Defined automatic moving window baseline statistics computation job


ORA-12012: error on auto execute of job “SYS”.”BSLN_MAINTAIN_STATS_JOB”
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “DBSNMP.BSLN_INTERNAL”, line 2073
ORA-06512: at line 1

Resolution : — Login as sys user.

SQL> sqlplus / as sysdba
— Drop the DBSNMP user by executing catnsnmp.sql script.
SQL> @$ORACLE_HOME/rdbms/admin/catnsnmp.sql
— Create the DBSNMP user by executing catsnmp.sql
SQL> @$ORACLE_HOME/rdbms/admin/catsnmp.sql |

Why do we need this Job

BSLN_MAINTAIN_STATS_JOB  gathers BASELINE “PERFORMANCE” statistics ( waits , etc) used for monitoring the database performance. 
Baselining is a new concept in 11g  which introduces “automatic moving window” during which the performance statistcs are computed.

Disabling that job will stop the moving window baseline from being maintained. 
That’s kind-of important in 11g as a bunch of the automated tuning stuff uses that information. 
If this baseline is not progressed it will not reflect the current usage of your system.



To check the BASELINE DETAILS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> select JOB_NAME,ENABLED,LAST_START_DATE,NEXT_RUN_DATE from dba_scheduler_jobs;

JOB_NAME ENABL LAST_START_DATE NEXT_RUN_DATE
——————————————————————————
AUTO_SPACE_ADVISOR_JOB FALSE 30-JUN-10 11.00.34.034314 AM +08:00
GATHER_STATS_JOB FALSE
FGR$AUTOPURGE_JOB FALSE
PURGE_LOG TRUE 24-MAR-13 03.00.00.332822 AM US/CENTRAL 25-MAR-13 03.00.00.500000 AM US/CENTRAL
SM$CLEAN_AUTO_SPLIT_MERGE TRUE 25-MAR-13 12.00.00.783180 AM +08:00 26-MAR-13 12.00.00.800000 AM +08:00
RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 25-MAR-13 12.00.00.587977 AM +08:00 26-MAR-13 12.00.00.600000 AM +08:00
BSLN_MAINTAIN_STATS_JOB FALSE 24-MAR-13 12.00.00.949599 AM +08:00 31-MAR-13 12.00.00.000000 AM +08:00
DRA_REEVALUATE_OPEN_FAILURES TRUE 24-MAR-13 06.00.02.222367 AM +08:00
ORA$AUTOTASK_CLEAN TRUE 25-MAR-13 03.00.00.220585 AM +08:00 26-MAR-13 03.00.00.300000 AM +08:00
HM_CREATE_OFFLINE_DICTIONARY FALSE
FILE_WATCHER FALSE
MGMT_CONFIG_JOB FALSE
MGMT_STATS_CONFIG_JOB FALSE 01-MAR-11 01.01.01.300000 AM +08:00

13 rows selected.




SQL> select log_date,status from dba_scheduler_job_run_details where job_name=’BSLN_MAINTAIN_STATS_JOB’;

LOG_DATE STATUS
————————————————————————— ——————————
17-MAR-13 12.00.33.954713 AM +08:00 SUCCEEDED
24-MAR-13 12.00.01.458464 AM +08:00 SUCCEEDED
03-MAR-13 12.00.33.502308 AM +08:00 SUCCEEDED
10-MAR-13 12.00.01.042798 AM +08:00 SUCCEEDED



SQL> select * from DBSNMP.BSLN_BASELINES;

DBID INSTANCE_NAME BASELINE_ID BSLN_GUID TI A STATUS LAST_COMPUTE_DA
———- —————- ———– ——————————– — – —————- —————
797835917 EAX 0 13B416E24D693ED2847D9D51D3E22BDF HX Y ACTIVE 17-MAR-13

enable/disable/run the job

exec dbms_scheduler.run_job(‘BSLN_MAINTAIN_STATS_JOB’,false);

exec dbms_scheduler.disable(‘BSLN_MAINTAIN_STATS_JOB’);

exec dbms_scheduler.enable(‘BSLN_MAINTAIN_STATS_JOB’);