DBMS_STATS.GATHER_SCHEMA_STATS

[Former Member]

Hello,

we are runninig ECC6 on oracle 10.2.04 (we have recently upgraded the patchset from 10.2.0.2 to 10.2.0.4), and AIX 5.3.

i noticed that there is a job called DBMS_STATS.GATHER_SCHEMA_STATS which configured to run every day at 12:00 pm which makes a high disk usage and high paging usage and slows the work on the system.

i can't see anything regarding this job from the sap system (db13) - did the sap installation scheduled this job?

what is the recommended runtime of this job (every day?, every 2 days? , once a week?),

when is the recommended time for running?

Please advise

Regards,

Moshe

[Former Member]

Hi,

You are using DBMS_STATS.GATHER_SCHEMA_STATS while tool availale in sap. You can schedule through db13

in low activity hours.Check workload through st03n .Why it is using high disk read/write check process and session(v$process,v$sesstat,v$session) ,analysis st04 for performance

Check Db cache and buffer size.

-According to sap run on daily basis but better you can deceide how much you need?

I thnk twice a week is sufficent .You can schedule in low activity hour like 3 am etc

SurendraJain

Edited by: Surendrajain2003 on Jul 9, 2009 5:47 PM

[anindya_bose]

Hi Moshe

How did you see that DBMS_STATS.GATHER_SCHEMA_STATS is running? DBMS_STATS is a package of Pl/SQL.

This job collects statistics for your shcema. Check for brconnect log from DB14.

Also check Note 351163 - Creating ORACLE DB statistics using DBMS_STATS

Thanks and Regards

Anindya

[Former Member]

Hi,

"How did you see that DBMS_STATS.GATHER_SCHEMA_STATS is running?"

i checked for running jobs from the working process ID from the topas command.

i took the process ID and checked it using a join command from tables v$process and v$session and found the DBMS_STATS job.

in brconnect i didn't find anything regarding this job - only jobs scheduled from db13.

how can i schedule it from db13? - i already scheduled the check and update optimizer statistics job from db13 which i scheduled in 02:00 am - is it the same job like DBMS_STATS.GATHER_SCHEMA_STATS? - it doesn't appear to be?

how the DBMS_STATS.GATHER_SCHEMA_STATS job was scheduled?

by whom?

regards,

Moshe

[former_member475159]

Hi Moshe,

Please check all the released jobs in SM37 with the search criteria DBA*.

After that compare those jobs with the jobs schedule in DB13.

Any extra job released in SM37 with DBA prefix can be that job.

You can also look at the CRONJOB in OS level

Please check and reply.

THanks and Regards

Debdeep

[Former Member]

hello,

the only jobs in db13 regarding the dba* are the one i scheduled to run from db13 - check db and check and update optimizer statitics and are ran on 02:00 , and 06:00 am.

the DBMS_STATS.GATHER_SCHEMA_STATS job is ran in 12:00pm by the oracle user sys.

no jobs regarding dba were ran from the crontab os level user search

Regards,

Moshe

[former_member475159]

Hi Moshe,

I think the said job is an Oracle internal maintenace job. Please look at the Sapnote 974781.

This note speaks about intenal jobs in oracle 10g. You can check internal jobs and if necessary disable particular jobs

by the SQL given in the note

Thanks and Regards

Debdeep

[anindya_bose]

What is the value of database parameter "statistics_level" . Check it from SQL>Show parameter statistics_level ;

Run SQL

SELECT job_name, owner, enabled, state

FROM DBA_SCHEDULER_JOBS

WHERE JOB_NAME = 'GATHER_STATS_JOB';

you should get out put like > GATHER_STATS_JOB SYS FALSE DISABLED

if you see that stats_jobs are enabled then run sql

EXECUTE DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

You can checn note 838725

Some links you might find interesting are given below.

http://www.dba-oracle.com/concepts/tables_optimizer_statistics.htm

http://www.dba-oracle.com/concepts/tables_optimizer_statistics.htm

http://www.dba-oracle.com/t_alter_schema_tab_monitoring.htm

Thanks and Regards

Anindya

Edited by: Anindya Bose on Jul 9, 2009 6:27 PM

[Former Member]

Show parameter statistics_level = typical

is DBMS_STATS.GATHER_SCHEMA_STATS job a must run job in a sap enviroment?

i'm a bit confused - according to notes 974781, 838725 as of oracle 10G there are a few automatic statistics collection jobs are active by default and must be deactivated because they are not supported in yet in oracle 10.2.0.x and the only statistics jobs should be ran from brconnect method only.

one of these jobs that must be deactivated is GATHER_STATS_JOB - is this the same job as DBMS_STATS.GATHER_SCHEMA_STATS?

when i check the active oracle jobs i can see that GATHER_STATS_JOB is deactivated (false)

[former_member475159]

Hi Moshe,

As per as SAP's recomendation,

No DBMS_STAT job should be scheduled from Oracle. If any is there they should be disabled. SAP will take care of it from BRCONNECT.

Please run the sql

SELECT JOB_NAME, OWNER, ENABLED FROM DBA_SCHEDULER_JOBS

and check whether any STAT job is enabled. If any job is enabled, please execute the query

execute dbms_scheduler.disable('JOBNAME')

In our case job name is probably DBMS_STATS.GATHER_SCHEMA_STATS

Thanks and Regards

Debdeep