statspack

[Knarayan]

I have configured statspack to run every hour by running the spauto.sql script. In fact when I query dba_jobs I see the job# also. And I have job_queue_processes set to 1 also. But it just doesn't gather statistics. This has worked before on all other servers except in this one I am having problems.

Any clue. Thanx in advance

[fraze]

Check whether the job is "broken" in the dba_jobs view. If it is it can be "unbroken" by running dbms_job.broken

[Knarayan]

No it is not:

SQL> select broken,job,failures from dba_jobs;
�ŠB JOB FAILURES
- ---------- ----------
N 4

[fraze]

Can you post the whole dba_job view?

[Knarayan]

I cannot post the entire select * as it is too long, but I have posted all the relevant columns(columns that don't have nulls)
select job,log_user,priv_user,schema_user,next_date,next_sec,total_time,broken,interval,what,instance from dba_jobs

4 PERFSTAT PERFSTAT PERFSTAT 1/22/2002 13:00 13:00:00 0 N trunc(SYSDATE+1/24,'HH') statspack.snap; 1

Thanx

[fraze]

You can also try and force the job to run by doing

SQL>exec dbms_job.run(,TRUE);

[fraze]

should be a 4 as the first field!!

SQL> exec dbms_job.run(4,TRUE);

[fraze]

R U running parallel server? You've got 1 in the instance column but the default is 0 unless you're running parallel server.

[Knarayan]

No I am not runnig parallel server.
I think 1 is the default. I have parallel server set to false and the parallel server instances is set to 1 in v$parameter which is default, probably that's why it shows instances=1.

Thanx

[julian]

Originally posted by Knarayan
No I am not runnig parallel server.
I think 1 is the default. I have parallel server set to false and the parallel server instances is set to 1 in v$parameter which is default, probably that's why it shows instances=1.

Thanx

The default is 0, any_instance.

You can use a an init.ora parameter called INSTANCE_NUMBER to give a number to the instance. This is usually done in OPS. If one instance uses this parameter, all insances must use. The number is also used to assign free space to instances using the INSTANCE option in the ALLOCATE EXTENT caluse in ALTER TABLE. It is also used for assigning free list groups.