oracle中audsid,oracle 性能与调整学习笔记.doc

oracle 性能与调整学习笔记

Performance Tuning Study Guide

Lesson 2 Tuning Overview

Tuning Steps

Tune the design.

Tune the application

Tune memory

Tune I/O

Tune contention

Tune the operating system.

Tuning Goals

Access the least number of blocks

Cache blocks in memory

Share application code

Read and write data as quickly as possible

Ensure that users do not wait for resources

Perform backups and housekeeping while minimizing impact

Lesson 3 Alert and Trace Files

Objectives

Describe the location and usefulness of the alert log file.

Describe the location and usefulness of the background and user process trace files.

The alert log file of database contains the following information:

Internal errors(ORA-600)，and block corruption errors(ORA-1578)

Operations that affect database structures and parameters, and statement such as CREATE DATABASE, STARTUP, SHUTDOWN, ARCHIVE LOG and RECOVER

The value of all nondefault initialization parameters

Background Process Trace Files

If an error is detected by a background process, the information is dumped into a trace file.

User Trace Files

When resource consumption occurs during statement processing,server processes can generate trace files at the user’s request. This is called user trace files.

Initialization parameter:

BACKGROUND_DUMP_DEST: Stores alert log file and

background processes trace files

USER_DUMPT_DEST: Stores user trace files.

SQL_TRACE: Used to enable or disable sql trace files

MAX_DUMP_FILE_SIZE: Limits the size of user trace files,specified in O/S blocks.

在会话期间打开允许用户级别跟踪：(Session-Level Tracing)

EXECUTE dbms_system.set_sql_trace_in_session (8,12 TRUE);

注意8是SID，12是SERIAL#,可以通过V$SESSION来查找，并且DBMS_SYSTEM包属于SYS用户，普通的用户无法执行。

Lesson 4 Utilities and Dynamic Performance Views

学习目标

通过下列手段来搜集统计信息：

性能视图和故障解决视图

UTLBSTAT和UTLESTAT 报告输出

Oracle等待事件

ORACLE企业管理器提供的相应工具

描述闩类型

使用企业管理器(Enterprise Manager,EM) 来为预定的环境设定事件(event)

Oracle 提供的视图、实用脚本和工具：

动态故障解决、性能视图和数据字典：

实用性能分析脚本：UTLBSTAT.SQL和UTLESTAT.SQL

Oracle等待事件

企业管理器事件服务

Oracle分析和调整包

V$视图和X$表的比较

V$视图是基于X$表来产生的