Oracle高级培训 第7课 学习笔记

 

Oracle高级培训 第7课 学习笔记

 

作者：JackYang ([e-mail])

日期：2006-10-20

 

本课实验已通过上机验证。

 

性能调优

以下笔记内容与老师提供的文件ORA_TRAIN6.PPT相对应

 

性能调优到底由谁调优，不仅是DBA的事情，和系统开发人员密切相关。

在应用设计和编程阶段对性能调优很重视，那么很多性能问题不会出现。

 

误解：

开发完毕再调优。

开发完毕再调优很困难。

 

和系统管理员也有关，UNIX和WINDOWS系统参数是否调优。

 

如何调优？

调优哪些东西？

 

调优目标包括：

响应时间

数据库的可用性

数据库的命中率。ORACLE里面很多事情在内存中进行。如果所要的数据在内存中找不到。那么就要从文件中读取。效率就会受到影响。

 

调优目标包括：

尽可能少的访问硬盘上的数据块。尽量访问内存中的数据。

在内存中缓冲数据块。

希望执行一条SQL语句时，不要编译和解析。而是从共享内存中访问。属于命中率的一种。在内存中找到SQL已经解析过的代码。在内存中找不到叫脱靶。

如果一定要进行I/O操作，那么希望I/O操作能尽量快。

任何用户的操作，不需要等待资源。通过调优调整资源的争用。

希望备份操作和日常的管理操作能够对ORACLE数据库的正常运行产生最小的影响。尽量在晚上做。

 

主要调整的项目：

优化实例

提高SQL语句重用的概率。希望在内存中找到已经编译的代码。

空间的管理。包括，内存空间管理和数据文件的管理

优化对数据的访问。

 

调优步骤

调整设计

调整应用              即调整SQA

调整内存              调整redo log buffer，shared buffer，large buffer

调整IO                

调整争用(冲突)     调整锁，latch(锁存器)

调整操作系统。    UNIX，WINDOWS

 

ORACLE中和性能调整密切相关的文件：

Alert log file 警告文件，ORACLE的重要操作和内部错误，会在ALERT LOG FILE中产生一些信息。

非缺省的参数也会显示。DBA每天应该看一次ALERT LOG FILE，不管数据库是否正常。因为有些问题时积累的，越早发现越容易解决。

ALERT log file会不断增长。每天检查后，觉得没问题，就清空。也可以进行备份。

 

通过BACKGROUP_DUMP_DEST参数设置ALERT_LOG_FILE的位置。

对每一个数据库都应该设置一个此参数，保证每个数据库的ALERT文件在不同地方，找起来比较方便。

 

在ALERT log file中可以看到不同进程产生的信息。

 

BACKGROUND TRACE file

ORACLE能够跟踪每一个后台进程，跟踪之后能够产生TRACE file。不同进程产生的跟踪信息，放在不同的trace文件。

trace文件和每一个后台进程对应。每一个今后台进程有很多个trace文件。

 

user trace file

用户也可以产生TRACE文件，记载SQL语句。

用户把TRACE设为TRUE，每执行一条SQL语句，都会在TRACE文件中记载。需要通过ORACLE的工具来对这些TRACE文件进行翻译。

这个功能影响性能，所以一般都关闭这个功能，只有在调试的时候才打开。

可以有3级

初始化参数文件中SQL_TRACE参数为TRUE，就打开用户TRACE

使用ALTER SESSION命令，在当前用户会话中，激活用户TRACE功能

ORACLE的程序包中有一个procedure SET_SQL_TRACE_IN_SESSION,可以在应用程序中调用。

 

USER_DUMP_DEST这个参数，在初始化参数文件中设置，指定用户TRACE文件的路径。

 

 

调优的工具：

动态视图 V$打头的视图

DBA_打头的视图

UTLBSTAT.SQL和UTLESTAT.SQL

UTLBSTAT.SQL开始收集ORACLE的数据。

UTLESTAT.SQL结束收集ORACLE的数据。

oracle8之前用这两个SQL文件，不推荐使用，产生的报表不方便看，对结果不保存。

 

ORACLE9i之后使用statspack来代替这两个SQL文件，不需要开始和结束的时候都运行。可以在任何时刻运行这个statspack，运行这个程序，就会产生当时数据库的快照，包括：CPU、内存、IO、锁、锁存器、争用的各种情况。而且可以产生statspack的报表，这个报表易读。

 

Enterprise Manager，图形界面好用。

ORACLE的诊断调整包。可以通过ENTERPRISE MANAGER来调用。

www.oracle.com.cn上常会有些ORACLE专家提供的工具下载。

 

一些用的较多的视图

DBA_TABLES

DBA_TAB_COLUMNS

DBA_CLUSTERS

DBA_INDEXES

INDEX_STATS

 

 

在这些表里面有些统计数据是没有的，只有运行ANALYZE命令之后，才会有相应的统计数据。

 

X$tables 是ORACLE开发人员和ORACLE全球支持使用的。内容格式不公开。

 

在调优时会碰到的表/动态视图，分以下几类

和实例有关

和内存有关

和DISK有关

和争用有关

和用户的会话有关

 

最重要的5个视图

V$STATNAME

包含所有参数的名称

 

V$SYSSTAT

重中之重，整个系统的统计数据。从数据库启动时开始统计，累计的数据。statspack工具的源数据就是从这个动态视图得到。

包含所有参数的统计值

statistic# 统计标号

name      参数名称

class       类别

value       参数的值

 

V$SGASTAT

调整ORACLE内存参数

POOL     用于区分各种用途的POOL

name       相应的参数名称

bytes       内存的大小

 

V$EVENT_NAME

对ORACLE数据库进行调整，一个要看统计参数，另一个要看EVENT

每次等待会产生一个EVENT

找到EVENT，就找到了问题所在，定位问题。

event#

name

parameter1

parameter2

parameter3

 

 

V$SYSTEM_EVENT

total_waits     总共等待了多少时间

total_timeouts总共超时多少次

time_waited   

average_wait   平均等待的时间

 

 

同样重要的几个表

前面是SYSSTAT，这里是SESSIONSTAT

V$SESSTAT

和V$STATNAME勾连起来能够找到SESSION的name。

 

V$SESSION

 

 

V$SESSION_EVENT

 

 

V$EVENT_NAME

 

 

V$SESSION_WAIT

 

在刚才这些动态视图中看到统计参数，就把TIMED_STATISTICS设置为true

oracle9i中这个参数默认为true，ORACLE8i中这个参数默认为false

8i到9i主要就是实现了管理的自动化。

 

statistics报告中可以看到的内容

 

 

ORACLE中很重要的一张表

stats$lib

 

decode(gethits,0,1,gethits)

gethits如果为0，就变成1，不为0就等于gethits。避免gethits为0。

 

 

实验一：

实验目的：性能调优

1.         dos> cd ora9lab

2.         dos> set ORACLE_SID=DB91

3.         dos> sqlplus /nolog

4.         SQL> connect sys/ora123 as sysdba

5.         SQL> startup

6.         SQL> select name from v$datafile;

       看看数据文件在哪个目录下

 

7.         SQL> create tablespace perfstat datafile ‘D:/perfstat/perfstat1.dbf’ size 200m autoextend on;

       创建表空间

       之前先在D盘下创建perfstat子目录

 

8.         SQL> @?/rdbms/admin/spcreate

       输入perfstat_password的值：输入ora123

       输入default_tablespace的值：输入perfstat

       输入 temporary_tablespace的值：输入TEMP，一个临时表空间

 

       相对应的命令是@?/rdbms/admin/spdrop

 

      

9.         SQL> exec statspack.snap

       开始统计，照张快照。做些操作之后，再照张快照。

       进入了perfstat目录

 

10.     再开一个窗口

11.     dos> cd ora9lab

12.     dos> sqlplus /nolog

13.     SQL> connect sys/ora123@DB91D as sysdba

14.     SQL> select * from scott.dept;

 

15.     回来原来窗口

16.     SQL> exec statspack.snap

       再做一张快照

 

17.     SQL> select username from v$session

       查看当前用户

 

18.     SQL> @?/rdbms/admin/spreport

       创建一个报表

       可以看到做了几张快照

       输入begin_snap的值：输入1

       输入end_snap的值：输入2

       输入report_name的值：可以之间按回车，报表名称产生在默认目录下。

 

19.     SQL> host dir

       查看报表在哪个目录下

       名称是sp_1_2.LST

       sp_起始快照序号_结束快照序号.LST

 

       redo nowait 要大于90%以上，小于的时候要进行调整。

       in-memory sort要大于90%,70%以下就有问题

       Execute to Parse    执行解析的次数

 

       Top 5 Timed Events

       等待时间最长的5个事件，是发现性能问题的关键。

       log file parallel write 从redo log buffer写入redo log file

      

       Background Wait Events       后台进程的等待

       Waits一般是指次数

      

       对这张报告要仔细研究。

 

20.     SQL> @?/rdbms/admin/sppurge

       删除一个范围内的快照

       输入 losnapid的值：范围开始的id号，1

       输入 hisnapid的值：范围结束的id号，2

 

两个快照之间运行应用程序，统计数据就比较准确了。