Hello world!

Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!

what’s EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS()

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 2am 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:

 

TOP EVENTS

log file sync — 当一个用户提交或回滚数据时，LGWR 将session 会话的重做由redo buffer 写入到重做日志中。log file sync 必须等待这一过程成功完成(Oracle 通过写redo log file 保 证commit 成功的数据不丢失)，这个事件说明提交可能过于频繁，批量提交可以最大化LGWR 的效率，过分频繁的提交会引起LGWR频繁的激活，扩大了LGWR 的写代价。为了减少这种等待事件，可以尝试每次提交更多的记录。将重做日志置于较快的磁盘上，或者交替使用不同物理磁盘上的重做日志，以降低归档对LGWR的影响。对于软RAID，一般来说不要使用RAID 5，RAID5 对于频繁写入得系统会带来较大的性能损失，可以考虑使用文件系统直接输入/输出，或者使用裸设备（raw device），这样可以获得写入的性能提高。

 

 

log file parallel write — 从log buffer 写redo 记录到redo log 文件，主要指常规写操作(相对于log file sync)。如果你的Log group 存在多个组成员，当flush log buffer 时，写操作是并行的，这时候此等待事件可能出现。尽管这个写操作并行处理，直到所有I/O 操作完成该写操作才会完成(如果你的磁盘支持异步IO或者使用IO SLAVE，那么即使只有一个redo log file member,也有可能出现此等待)。这个参数和log file sync 时间相比较可以用来衡量log file 的写入成本。通常称为同步成本率。

control file parallel write — 当server 进程更新所有控制文件时，这个事件可能出现。如果等待很短，可以不用考虑。如果等待时间较长，检查存放控制文件的物理磁盘I/O 是否存在瓶颈。多个控制文件是完全相同的拷贝，用于镜像以提高安全性。对于业务系统，多个控制文件应该存放在不同的磁盘上，一般来说三个是足够的，如果只有两个物理硬盘，那么两个控制文件也是可以接受的。在同一个磁盘上保存多个控制文件是不具备实际意义的。减少这个等待，可以考虑如下方法：
 

• 减少控制文件的个数(在确保安全的前提下)
•  如果系统支持，使用异步IO
•  转移控制文件到IO 负担轻的物理磁盘

 

tracing listener

add below text to listener.ora

 

TRACE_FILE_LISTENER = LISTENERTEST.trc
TRACE_DIRECTORY_LISTENER = /opt
LOGGING_LISTNER = on
TRACE_LEVEL_LISTENER = SUPPORT

AWR

AWR Features

 

The AWR is used to collect performance statistics including:

 

• Wait events used to identify performance problems.
• Time model statistics indicating the amount of DB time associated with a process from the v$sess_time_model and v$sys_time_model views.
• Active Session History(ASH) statistics from the v$active_session_history view.
• Some system and session statistics from the v$sysstat and v$sesstat views.
• Object usage statistics.
• Resource intensive SQL statements.
• advisors

 

Snapshots

By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using:

BEGIN

    DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(RETENTION => 43200, INTERVAL=>30);  //minutes

END;

The changes to the settings are reflected in the DBA_HIST_WR_CONTROL view. Typically the retention period should capture at least one complete workload cycle. If your system has monthly archive and loads a 1 month retention time would be more eneficial that the default 7 days. An interval of "0" switches off snapshot collection, which in turn stops much of the self-tuning functionality, hence this is not recommended. Automatics collection is only possible if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If the values is set to BASIC manual snapshots can be taken, but they will be missing some statistics.

 

Extra snapshots can be taken and existing snapshots can be removed using:

BEGIN

     DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id=>22, high_snap_id=>32);

END;

Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.

 

Baselines

A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance agaginst similar periods in the past. You may with to create baseline to represent a period of batch processing like:

BEGIN

    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id=>210,end_snap_id=>220,baseline_name=>’batch baseline’);

END;

The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted:

BEGIN

     DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(baseline_name=>’batchline’,cascade=>false); //deltes associated snapshots if TRUE

END;

Baseline information can be queried from the DBA_HIST_BASELINE view.

 

Workload Repository Views

The following workload repository views are available:

• v$active_session_history     displays the active session history (ASH) sampled every second
• v$metric     displays metric information.
• v$metricname        displays the metrics associated with each metric group.
• v$metric_history     displays historical metrics
• v$metricgroup        displays all metrics groups
• dba_hist_active_sess_history     displays the history contents of the active session history.
• dba_hist_baseline                     displays baseline information.
• dba_hist_database_instance      displays database environment information.
• dba_hist_snapshot                    displays snapshot information
• dba_hist_sql_plan                     displays SQL execution plans.
• dba_hist_wr_control                  displays AWR settings.

Workload Repository Reports

Oracle provide two scripts to produce workload repository reports(awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql

 

sqlnet.log error

Fatal NI connect error 12170.

  VERSION INFORMATION:
        TNS for Linux: Version 10.2.0.1.0 – Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.1.0 – Production
        TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.1.0 – Production
Time: 29-NOV-2006 19:13:40
Tracing not turned on.
Tns error struct:
  ns main err code: 12535
  TNS-12535: TNS:operation timed out
  ns secondary err code: 12560
  nt main err code: 505
  TNS-00505: Operation timed out
  nt secondary err code: 110
  nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=219.239.13.3)(PORT=19948))

# oerr tns 12170
12170, 00000, "TNS:Connect timeout occurred"
Cause: The server shut down because connection establishment or
communication with a client failed to complete within the allotted time
interval. This may be a result of network or system delays; or this may
indicate that a malicious client is trying to cause a Denial of Service
attack on the server.

Action: If the error occurred because of a slow network or system,
reconfigure one or all of the parameters SQLNET.INBOUND_CONNECT_TIMEOUT,
SQLNET.SEND_TIMEOUT, SQLNET.RECV_TIMEOUT in sqlnet.ora to larger values.
If a malicious client is suspected, use the address in sqlnet.log to
identify the source and restrict access. Note that logged addresses may
not be reliable as they can be forged (e.g. in TCP/IP).

于是增大 sqlnet.ora 中的参数：
SQLNET.INBOUND_CONNECT_TIMEOUT = 30
SQLNET.RECV_TIMEOUT = 30
SQLNET.SEND_TIMEOUT = 30

在 listener.ora 增加：
INBOUND_CONNECT_TIMEOUT_LISTENER = 30

重启监听器：
lsnrctl reload

Buffer Cache 的管理

在hash chain 上没有找到所要的buffer header时， oracle 会发出I/O调用，到磁盘上的数据文件中获取数据块，并将该数据块的内容复制一份到buffer cache中的内存数据块里。这个时候，假如buffer cache是空的，比较好办，直接拿一个空的内存数据块来用即可。但是如果buffer cache 中的内存数据块全都被用掉了，没有空的内存数据块了，怎么办？应该冲洗使用哪一个内存数据块？

 

LRU 表示Least Recently Used, 也就是指最近最少使用的buffer header链表。LRU链表串联起来的buffer header都指向可用数据块。buffer 按照被使用的先后顺序挂在LRU链表上，先被使用的buffer 挂在LRU链表的后面，后被使用的buffer则被挂在LRU链表的前面。如果buffer 被DML语句修改了，则该buffer会从LRU链表上摘下来。LRU链表上的buffer header 所指向的buffer 都是可用数据块。当服务器进程无法找到空的buffer来存放新的数据请求时，则需要把已经存放了数据的buffer拿来使用，也就是用新的数据块的内容覆盖曾经使用过的buffer。在查找应该覆盖哪个buffer时，Oracle 会在LRU链表上的尾部开始扫描，如果扫描到的buffer正在被使用，则跳过该buffer,继续往下找，直到找到为止。如果扫描了一定数量的buffer以后还没找到可用的buffer，则说明脏块太多了，于是触发DBWn进程，将脏块刷新到数据文件里，刷新完毕以后，buffer的内容与数据文件里的一致，于是这些脏块就变成干净的buffer了，也就可以拿来覆盖其中的内容了。这些干净的buffer就会挂在LRU链表的尾部，供进程所使用。

 

当进程在LRU链表上扫描可用数据块时，会受到cache buffer lru chain latch 的保护。

Hash Algorithm

用HASH算法在1到100中找32：

 

[10][10]

 

32 MOD 10  = 2

 

只用11次就找到32的位置

 

卡巴又被黑了

永远都在和查毒软件做斗争，wasting too much of my time

 

I’ve started to think that is it worth to save money on it

 

 

 

hahaaaaaa… I did xml today

read consistency

SQL> set transaction read only;

 

Function: even the DML transaction commited, session won’t upate table.

 

Example:

 

—————–session 1———————–

SQL> set transaction read only;

 

 

SQL> select * from test;

 

A                             B
——————– ———-
test                         99
test                       1010

 

—————-session 2————————

SQL> insert into test  values(‘hit’, 888);

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from test;

 

A                             B
——————– ———-
test                         99
test                       1010
hit                           888

 

see session 1 again:

—————–session 1———————–

 

SQL> select * from test;

 

A                             B
——————– ———-
test                         99
test                       1010