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

可以確認是收集統計資訊造成的錯誤。

透過檢視回收站，可以找到相關被drop的table

這個錯誤不是重點，重點是這個時候為什麼會收集統計資訊

資料庫自動收集統計資訊的已經關閉。

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了的配置

EMD_MAINTENANCE 引起統計資訊收集

相關文章