Shulou(Shulou.com)06/01 Report--

AWR (Automatic Workload Repository) is a tool for diagnosing database performance, collecting statistical data related to performance, and tracking potential problems according to performance indicators in these statistical data. If the relevant data cannot be collected due to certain conditions, the database performance diagnosis will be greatly compromised.

Here are a few common cases where AWR collection is slow, pending, or missing:

STATISTICS_LEVEL parameter is not ALL or TYPICAL

SYSAUX Insufficient Tablespace

System resource I/O, CPU utilization is too high

MMON/MMNL process anomaly

Related FIXED TABLE statistics are inaccurate

STATISTICS_LEVEL parameter is not ALL or TYPICAL

Initialize parameter STATISTICS_LEVEL, AWR acquisition information is affected by parameter STATISTICS_LEVEL. This parameter has three values:

BASIC: AWR statistics off, only a small amount of database statistics collected.

TYPICAL: Default values, only partial statistics collection, typical behavior required to monitor oracle databases.

ALL: All possible statistics are captured, and there is some information about the operating system. This level of capture requires less, such as more sql diagnostic information.

Generally, this parameter will not be modified casually, and the default value TYPICAL will be used, so in this case, AWR will not be able to collect statistics.

2. SYSAUX table space is insufficient

Statistics collected by AWR are stored in tables named in the format WRM$_* and WRH$_* on the SYSAUX table space (M for metadata and H for historical). Available via @?/ rdbms/admin/awrinfo.sql or x$kewrtb Query related table information. Although AWR cannot be generated due to insufficient SYSAUX table space is a low-level problem, there is a situation that needs to be noted, because BUG and other causes ASH/AWR base table data cannot be cleaned up. For example:

SQL> select * from dba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL ---------- -------------------- -------------------- ---------262389084 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT

A normal SNAPSHOT is produced every hour for 7 days. However, some base tables such as WRH$_ACTIVE_SESSION_HISTORY are not cleaned according to the sys.wrm$_wr_control settings for some reason. SNAPSHOT snapshots are retained for more than 7 days, which can lead to SYSAUX bursting and slow collection of AWR reports. There are two specific solutions:

1）alter session set "_swrf_test_action"=72;

2)Manually delete obsolete snapshots:

exec dbms_workload_repository.drop_snapshot_range(low_snap_id => xxx, high_snap_id => xxxx, dbid => 262389084);

MOS documentation:

WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (Doc ID 387914.1 )

3, system resource I/O, CPU utilization is too high

When the system load is very high, many user processes are competing for resources, AWR report collection needs to consume the performance of the system host, when AWR report collection time exceeds 15 minutes, if the database is in a rather busy state at this time, the database in order to ensure the normal operation of the business, automatically turn off AWR function, reduce system overhead, this is 11g function enhancement. This situation is basically as follows:

The following warning message appears in alert. log:

Suspending MMON slave action xxx for 82800 seconds

Or the following alarm message appears in the mm trc:

Unable to schedule a MMON slave at: Auto Flush Main 1 Slave action has been temporarily suspended - Slave action had prior policy violations. Unknown return code: If the system is so over-loaded that it takes over 15 minutes to gather statistics or other MMON tasks, this error is expected.It is a functionality enhancement in 11g, as it prevents MMON from locking resources those other processes might be waiting for. In 10g , mmon slaves are allowed to run indefinitely.

From the logs, there are a large number of Slave actions that have been temporarily suspended, which is an enhancement of 11g functionality. When the system is overloaded, MMON processes collect statistics for more than 15 minutes and terminate the task. 10g will be postponed indefinitely. Therefore, insufficient system resources will also cause AWR statistics to be unable to be collected properly.

Why 15 minutes? Please refer to MOS documentation:

Troubleshooting ORA-12751 "cpu time or run time policy violation" Errors (doc ID 761298.1)

Troubleshooting: Missing Automatic Workload Repository (AWR) Snapshots and Other Collection Issues (doc ID 1301503.1)

4. MMON/MMNL process abnormality

Memory Monitor(MMON): MMON is mainly used for AWR, ADDM, MMON writes statistics from SGA to system tables

The Memory Monitor Light (MMNL): The mun process is mainly the collection of sql information and ash information in memory. If these information needs to be written to disk (i.e. some data dictionary tables), then the MMNL process is responsible for writing.

MMON, MMNL, and Mnnn are processes that populate the Automatic Workload Repository (AWR), a new feature in Oracle 10g. The MMNL process refreshes statistics from the SGA to database tables according to schedule. MMON processes are used to "automatically detect" database performance problems and implement new self-tuning features. The Mnnn process is similar to the Jnnn or Qnnn processes of the job queue; the MMON process requests these slave processes to do work on its behalf. It can be seen that MMON and MMNL process anomalies are the root cause of AWR not automatically collected.

In case AWR cannot collect, you can investigate according to the document (ID 1301503.1) to check whether the processes of mm and mmnl are normal.

ps -ef| egrep "mmon| mmnl" #Check whether the mun and mmnl processes have oracle 32674 1 0 21:22 ? 00:00:01 ora_mmon_oracle 32676 1 0 21:22 ? 00:00:01 ora_mmnl_

These two processes are oracle non-core processes that can be killed, they automatically start processes, and they are automatically maintained. If there are no problems with these two processes, you can manually generate AWR to see if it works:

exec dbms_workload_repository.create_snapshot(); then diagnose the problem further.

Because both processes are non-core processes, many documents say kill, re-invoking the process so AWR can continue to build. 11.2.0.4 AWR Snapshots Are Not Being Created Because MMON Is Not Being Respawned (Document ID 2023652.1)

Fixed Table statistics are inaccurate

Looking at the trace file of the emon process, the following error appears:

** KEWROCISTMTEXEC - encountered error: (ORA-12751: cpu time or run time policy violation) *** SQLSTR: total-len=295, dump-len=240, STR={insert into WRH$_SERVICE_STAT (snap_id, dbid, instance_number, service_name_hash, stat_id, value) select :snap_id, :dbid, :instance_number, stat.service_name_hash, stat.stat_id, stat.value from v$active_services asvc, v$service_st} DDE rules only execution for: ORA-12751

Looking at why this SQL executes slowly, we find that the v$service_stats view count is high. This view records a minimal set of performance statistics with an automatic service_name v$services that displays information about the service. e xpdp Each time a backup starts, a new service name is added. After the backup ends, the service name is removed. This action will be recorded in the alert log. This action will cause many unknown records to appear in the v$service_stats view.

Wrong execution plan:

Each time logical export, records with service_name= unknown will be added to the v $service_stats view, resulting in a large number of records with unknown service name accumulated in the v$service_stats view. During the execution of the above SQL in the AWR snapshot generation process, oracle selects a less efficient execution plan due to inaccurate or no statistical information of the fixed table. The execution of SQL consumes a lot of time. Causes CPU time policy violation of oracle maintenance task, AWR snapshot generation interruption.

The solution is: manually collect fixed table statistics (before 12c, fixed statistics are not automatically collected, it is the collection of statistics for all X$base tables, this collection is relatively long, and evaluate the impact of collection on other SQL statements. For example, V$SESSION, V$PROCESS, V$LOCK and other common views related to SQL statement execution plan impact)

select table_name,num_rows,last_analyzed from dba_tab_statistics where last_analyzed is not null order by last_analyzed desc;

exec dbms_stats.gather_fixed_objects_stats(no_invalidate => false);

Fixed Table Statistics (GATHER_FIXED_OBJECTS_STATS) Considerations (Doc ID 798257.1)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.