ITPub博客

首页 > Linux操作系统 > Linux操作系统 > EMD_MAINTENANCE 引起统计信息收集

EMD_MAINTENANCE 引起统计信息收集

原创 Linux操作系统 作者:yangzhangyue 时间:2013-07-30 18:11:30 0 删除 编辑
trace文件内容
Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONEOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/product/11.2.0/db

System name: Linux

Node name:     warehouse.99bill.com

Release:  2.6.18-164.el5

Version:   #1 SMP Tue Aug 18 15:51:48 EDT 2009

Machine: x86_64

Instance name: warehouse

Redo thread mounted by this instance: 1

Oracle process number: 100

Unix process pid: 9624, image: (J001)

 

 

*** 2013-07-27 18:12:16.894

*** SESSION ID:(160.14115) 2013-07-27 18:12:16.894

*** CLIENT ID:() 2013-07-27 18:12:16.894

*** SERVICE NAME:(SYS$USERS) 2013-07-27 18:12:16.894

*** MODULE NAME:(DBMS_SCHEDULER) 2013-07-27 18:12:16.894

*** ACTION NAME:(ORA$AT_OS_OPT_SY_11) 2013-07-27 18:12:16.894

 

ORA-20000: Unable to analyze TABLE "DW001"."I$_T_TXN_CTRL", insufficient privileges or does not exist

 

*** 2013-07-27 18:12:16.895

DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"DW001"','"I$_T_TXN_CTRL"','""', ...)

DBMS_STATS: ORA-20000: Unable to analyze TABLE "DW001"."I$_T_TXN_CTRL", insufficient privileges or does not exist

 

可以确认是收集统计信息造成的错误。

 

通过查看回收站,可以找到相关被droptable

 

这个错误不是重点,重点是这个时候为什么会收集统计信息

数据库自动收集统计信息的已经关闭。

11:41:49 sys@warehous>select t.program_name,t.schedule_name,t.schedule_type,t.enabled,t.state from dba_scheduler_jobs t where job_name='GATHER_STATS_JOB';

 

PROGRAM_NAME                   SCHEDULE_NAME                            SCHEDULE_TYP ENABL STATE

------------------------------ ---------------------------------------- ------------ ----- ---------------

GATHER_STATS_PROG              MAINTENANCE_WINDOW_GROUP                 WINDOW_GROUP FALSE DISABLED

 

Elapsed: 00:00:00.00

11:41:54 sys@warehous>

 

我们设置的统计信息收集没有放在这个时间点。

 

从错误看,应该是个job造成的错误。

11:51:56 sys@warehous>select t.JOB,t.SCHEMA_USER,t.interval, what from dba_jobs t where broken='N' ;

 

       JOB SCHEMA_USER          INTERVAL                                           WHAT

---------- -------------------- -------------------------------------------------- ----------------------------------------------------------------------------------------------------

       211 OWF_MGR              SYSDATE + (1440/(24*60))                           FND_SVC_COMPONENT.EXECUTE_REQUEST (p_component_request_id => 1);

       212 OWF_MGR              sysdate + 4/24                                     declare errbuf varchar2(4000); retcode varchar2(4000); begin WF_BES_CLEANUP.CLEANUP_SUBSCRIBERS(errb

                                                                                   uf, retcode); end;

 

       213 OWF_OWF              SYSDATE + (1440/(24*60))                           FND_SVC_COMPONENT.EXECUTE_REQUEST (p_component_request_id => 1);

       165 SYS                  TRUNC(SYSDATE+1)+6/24                              begin

                                                                                   --PRC_FREESPACE_MONITOR;

                                                                                  null;

                                                                                   end;

 

       214 OWF_OWF              sysdate + 4/24                                     declare errbuf varchar2(4000); retcode varchar2(4000); begin WF_BES_CLEANUP.CLEANUP_SUBSCRIBERS(errbuf, retcode); end;

 

       386 OWB_OWNER            sysdate + (6/1440)                                 wb_rti_service_job.check_service(1);

       409 SYSMAN                  sysdate + 1 / (24 * 60)                            EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();

 

7 rows selected.

并没有发现有收集统计信息的job

?????

但我们看看红色部分字体的内容,每分钟执行一次

11:58:14 sys@warehous>select count(*) from dba_tab_statistics where last_analyzed >sysdate-1;

 

  COUNT(*)

----------

       601

 

Elapsed: 00:00:00.10

 

在过去一天中有600多个object收集了统计信息。

 

之前发生错误的时候我查过,在发生错误的1小时内也有统计信息的收集。

 

我查了EMD_MAINTENANCE包相关信息:

Article-ID:         Note 285012.1
Circulation:        UNDER_EDIT (EXTERNAL)
Folder:             ST.EM.GridControl
Topic:              Metrics (Config,Collection,Baseline,Template,UDM)
Title:              Understanding the EMD_MAINTENANCE package from EM 10g Grid
Open-Remarks:       See RemarksOn:NOTE:285012.1
Document-Type:      BULLETIN
Impact:             MEDIUM
Skill-Level:        NOVICE
Updated-Date:       03-DEC-2004 14:07:38
References:         
Authors:            MRONCATI.US, WGRUYTER.BE
Attachments:        NONE
Content-Type:       TEXT/X-HTML
Products:           1370;
Platforms:          GENERIC;  


Information in this article applies to:
Oracle Enterprise Manager Grid Control Release 1 (10.1.0.2.0, 10.1.0.3.0)

Goal:
To provide a better understanding of the sql package 'emd_maintenance' (also called admin_maintenance_pkgbody.sql script)

The emd_maintenance is a package that ships with Enterprise Manager 10g Grid Control.  The script. is an administration script. that under normal circumstances an administrator should not have to execute.  However, if the notifications become backlogged for some reason, you can execute this script. as the sysman user and it will restart the DBMS jobs needed for the notification subsystem.

Location of script.:
The file is located in the OMS's $ORACLE_HOME/sysman/admin/emdrep/sql/core/latest/admin/admin_maintenance_pkgbody.sql.  This same SQL is in the emd_maintanance package in the repository database.

Description of the package:
This maintanence package handles statistics maintenance for the EMD Schema.  It contains the following procedures:

update_stale_stats
This procedure collects stale statistics for the specified schema.
It collects it every Saturday at 2 am by default.
These parameters can be configured.
delete_all_stats
This procedure deletes all stats for the specified schema. It will
delete all schema statistics once on Saturday at 1am by default. These
parameters can be configured.
analyze_emd_schema
This procedure is called by update_stale_stats.  It gathers
statistics for all objects in the schema.  It runs only once.
It calls GATHER_STALE for objects that have monitoring turned on
and already have statistics or just analyzes objects that do not
have statistics.
pin_plsql
This procedure is called by update_stale_stats.  This procedure will
pin all the critical EMD PL/SQL packages in memory.  This is critical
to ensure that we do not fragment the shared pool.
Intended Usage of the Package:
If you want to stop and restart the jobs, you can do this using these routines:
   - Stop
     exec emd_maintenance.remove_em_dbms_jobs;
   - Start/Restart:
     exec emd_maintenance.submit_em_dbms_jobs;

Comments:

To see scheduled/completed jobs use the dba_jobs (or user_jobs) table.
To see running jobs use dba_jobs_running or user_jobs_running.
To see running jobs use dba_jobs_running or user_jobs_running.
To cancel a job use the above tables to find the job number ( job ) and then call: dbms_jobs.remove_job(job)
@Reference:
@3092790: PERF: EXECUTE_EM_DBMS_JOB_PROCS RUNNING BEHIND SCHEDULE
@3323187: DBMS_JOB STUCK ON LIBCACHE LOCK AFTER ORA-4020 ERROR

 

脚本内容我还没有仔细研究,但这个任务和EM的定时操作相关。如果不使用EM,可以停用这个任务

我觉得这个报错也和这个job有关

这个应该是创建数据库时选择EM了的配置


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29033984/viewspace-767501/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-09

  • 博文量
    36
  • 访问量
    219790