on 07-09-2009 12:20 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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)
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
User | Count |
---|---|
93 | |
10 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.