Oracle数据库健康检查操作手册
广州市唯真数据服务有限公司
第1页
目 录
1
文档控制 ............................................................................................................................................................................. 3 1.1 1.2 1.3 1.4 2
修改记录 ..................................................................................................................................................................... 3 分发者 ......................................................................................................................................................................... 3 审阅记录 ..................................................................................................................................................................... 3 相关文档 ..................................................................................................................................................................... 3
查看LINUX机器软硬件负载情况 .................................................................................................................................... 3 2.1 2.2
REDHAT ........................................................................................................................................................................ 3 AIX 采用 ...................................................................................................................................................................... 3
3 ORACLE健康度情况 ........................................................................................................................................................ 5 3.1 3.2 3.3 3.4 3.5 3.6 3.7 3.8 3.9 3.10 3.11 3.12 3.13 3.14 3.15 3.16 3.17 3.18 3.19 3.20 3.21 3.22 3.23 3.24
ORACLE物理结构 ......................................................................................................................................................... 4 SESSION和CONNECTION ................................................................................................................................................ 5 ORACLE版本 ................................................................................................................................................................. 5
所有数据文件所占磁盘空间 ..................................................................................................................................... 5 数据库的内存大小 ..................................................................................................................................................... 5 DB_BLOCK_SIZE BYTES .............................................................................................................................................. 6 表空间个数,数据文件个数 ........................................................................................................................................ 6 控制文件个数 ............................................................................................................................................................. 6 日志文件 ..................................................................................................................................................................... 6 归档模式 ..................................................................................................................................................................... 7 ORACLE并发用户 ......................................................................................................................................................... 8 ORACLE补丁和产品安装情况 ..................................................................................................................................... 8 查看数据库的非默认参数 ....................................................................................................................................... 13 查看控制文件目录 ................................................................................................................................................... 14 查看日志文件最大设置 ........................................................................................................................................... 14 查看日志文件目录 ................................................................................................................................................... 14 归档日志文件 ........................................................................................................................................................... 14 数据文件 ................................................................................................................................................................... 14 数据库表空间使用情况 ........................................................................................................................................... 14 表空间缺省存储情况 ............................................................................................................................................... 16 查看回滚段 ............................................................................................................................................................... 16 查看用户拥有的权限 ............................................................................................................................................... 16 AWR查看ORACLE服务器性能 ................................................................................................................................... 16 索引维护 ................................................................................................................................................................... 16
健康检查操作手册
第2页
1 文档控制
本文档所有权和解释权归广州唯真数据服务有限公司所有，未经书面许可，不得复制或向第三方公开。
This document is the property of TDS FOSHAN BRANCH and can be neither reproduced nor disclosed to a third party without a written authorization.
1.1 修改记录
日期 作者 版本 修改记录 1.2 分发者
姓名 职位 1.3 审阅记录
姓名 职位 1.4 相关文档
2 查看Linux机器软硬件负载情况
2.1 Redhat
linux-hl0o2:/etc/ha.d # dmidecode | grep Product Product Name: VMware Virtual Platform
Product Name: 440BX Desktop Reference Platform
2.2 aix 采用
lsdev 显示设备
健康检查操作手册
第3页
prtconf 显示硬件信息 oslevel 查看系统版本 磁盘空间查看df cpu使用情况查看 topas vmstat 1 10
3 oracle架构
3.1 oracle物理结构
健康检查操作手册 第4页
3.2 session和connection
4 oracle健康度检查
4.1 oracle版本
linux-hl0o1:/etc/ha.d # su - oracle oracle@linux-hl0o1:~> sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 4 21:48:36 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved.
4.2 所有数据文件所占磁盘空间
select count(*),sum(bytes)/1024/1024/1024 ||' GB' from v$datafile;
4.3 数据库的内存大小
SQL> show parameter sga
NAME TYPE VALUE
健康检查操作手册
第5页
------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 2G sga_target big integer 2G
4.4 db_block_size bytes
db_block_size是最小逻辑单元，一次io读多个db_block_size的数据，有DB_FILE_MULTIBLOCK_READ_COUNT参数决定。
数据库高速缓存中缓冲区的大小主要由db_block_size决定.不是db_block_size SQL> show parameter db_block_size
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_block_size integer 8192
4.5 表空间个数,数据文件个数
select count(*) from dba_tablespaces； 数据文件个数;
select count(*) from dba_data_files
4.6 控制文件个数
show parameter control
4.7 日志文件
日志总大小
select sum(bytes)/1024/1024 from v$log;
健康检查操作手册 第6页
日志组个数，日志文件个数
select group#,thread#,bytes/1024/1024 size_MB , members, archived,status from v$Log; select * from v$logfile;
4.8 归档模式
1、先按正常方式关闭数据库 然后 %svrmgrl
SVRMGRL>connect internal
SVRMGRL>startup mount [database_name];
SVRMGRL>alter database [database_name] archivelog; SVRMGRL>archive log list;
SVRMGRL>alter database open;
2、设置数据库开启后自动启动archive进程
改参数文件initoraid.ora中 log_archive_start=true
log_archive_dest=directory or device name log_archive_format=filename format 再重新启动数据库，即可 3、注意事项
有足够的资源存放归档日志文件
定一个热备份计划，定期删除归档日志文件
健康检查操作手册 第7页
4.9 oracle并发用户
SQL> show parameter processes;
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ aq_tm_processes integer 0 db_writer_processes integer 1 gcs_server_processes integer 0 job_queue_processes integer 10 log_archive_max_processes integer 2 processes integer 1000
4.10oracle补丁和产品安装情况
先配置
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH; export PATH opatch lsinventory -all -detail
[oracle@sg_omss OPatch]$ opatch lsinventory -all -detail Invoking OPatch 10.2.0.1.0
Oracle interim Patch Installer version 10.2.0.1.0
Copyright (c) 2005, Oracle Corporation. All rights reserved.. Oracle Home : /oracle/product/10.2.0 Central Inventory : /oracle/oraInventory
from : /oracle/product/10.2.0/oraInst.loc OPatch version : 10.2.0.1.0 OUI version : 10.2.0.1.0
OUI location : /oracle/product/10.2.0//oui
Log file location : /oracle/product/10.2.0/cfgtoollogs/opatch/opatch-2010_Feb_22_12-10-39-CST_Mon.lo
健康检查操作手册 第8页
g
Lsinventory Output file location : /oracle/product/10.2.0/cfgtoollogs/opatch/lsinv/lsinventory-2010_ Feb_22_12-10-39-CST_Mon.txt
-------------------------------------------------------------------------------- List of Oracle Homes: Name Location
omss /oracle/product/10.2.0 Installed Top-level Products (1):
Oracle Database 10g There are 1 products installed in this Oracle Home. Installed Products (107):
Agent Required Support Files Assistant Common Files Bali Share Buildtools Common Files Character Set Migration Utility Database Configuration and Upgrade Assistants Database SQL Scripts Database Workspace Manager DBJAVA Required Support Files Enterprise Edition Options Enterprise Manager Agent Core Enterprise Manager Agent DB Enterprise Manager Baseline Enterprise Manager Common Files 健康检查操作手册 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 1.1.18.0.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0
第9页
Enterprise Manager Minimal Integration 10.2.0.1.0 Enterprise Manager plugin Common Files 10.2.0.1.0 Enterprise Manager plugin Common Files 10.2.0.1.0 Enterprise Manager Repository Core 10.2.0.1.0 Enterprise Manager Repository DB 10.2.0.1.0 Generic Connectivity Common Files 10.2.0.1.0 HAS Common Files HAS Files for DB Installation Common Files Installer SDK Component iSQL*Plus Java Runtime Environment JDBC Common Files LDAP Required Support Files OLAP SQL Scripts Oracle Advanced Security Oracle Call Interface (OCI) Oracle Clusterware RDBMS Files Oracle Code Editor Oracle Containers for Java Oracle Core Required Support Files Oracle Data Mining RDBMS Files Oracle Database 10g Oracle Database 10g Oracle Database 10g interMedia Files 健康检查操作手册 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 1.4.2.8.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 1.2.1.0.0I 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0
第10页
Oracle Database User Interface 2.2.13.0.0 Oracle Database Utilities 10.2.0.1.0 Oracle Display Fonts 9.0.2.0.0 Oracle Enterprise Manager Console DB 10.2.0.1.0 Oracle Extended Windowing Toolkit 3.4.38.0.0 Oracle Globalization Support 10.2.0.1.0 Oracle Help For Java Oracle Help for the Web Oracle Ice Browser Oracle interMedia Oracle interMedia Annotator Oracle interMedia Client Option Oracle interMedia Java Advanced Imaging Oracle interMedia Locator Oracle interMedia Locator RDBMS Files Oracle Internet Directory Client Oracle Java Client Oracle JDBC Thin Driver for JDK 1.2 Oracle JDBC Thin Driver for JDK 1.4 Oracle JDBC/OCI Instant Client Oracle JFC Extended Windowing Toolkit Oracle JVM Oracle LDAP administration Oracle Locale Builder Oracle Message Gateway Common Files 健康检查操作手册 4.2.6.1.0 1.1.10.0.0 5.2.3.6.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 4.2.33.0.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0
第11页
Oracle Net 10.2.0.1.0 Oracle Net Listener 10.2.0.1.0 Oracle Net Required Support Files 10.2.0.1.0 Oracle Net Services 10.2.0.1.0 Oracle Notification Service 10.1.0.3.0 Oracle ODBC Driver 10.2.0.1.0 Oracle OLAP Oracle OLAP API Oracle OLAP RDBMS Files Oracle One-Off Patch Installer Oracle Partitioning Oracle Programmer Oracle RAC Required Support Files-HAS Oracle Recovery Manager Oracle Spatial Oracle Starter Database Oracle Text Oracle UIX Oracle Universal Installer Oracle Wallet Manager Oracle XML Development Kit Parser Generator Required Support Files Perl Interpreter PL/SQL PL/SQL Embedded Gateway 健康检查操作手册 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 2.1.22.0.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 10.2.0.1.0 5.8.3.0.2 10.2.0.1.0 10.2.0.1.0
第12页
Platform Required Support Files 10.2.0.1.0 Precompiler Common Files 10.2.0.1.0 Precompiler Required Support Files 10.2.0.1.0 RDBMS Required Support Files 10.2.0.1.0 RDBMS Required Support Files for Instant Client 10.2.0.1.0 regexp 2.1.9.0.0 Required Support Files 10.2.0.1.0 Sample Schema Data 10.2.0.1.0 Secure Socket Layer 10.2.0.1.0 SQL*Plus 10.2.0.1.0 SQL*Plus Required Support Files 10.2.0.1.0 SQLJ Runtime 10.2.0.1.0 SSL Required Support Files for InstantClient 10.2.0.1.0
Sun JDK 1.4.2.0.8 Sun JDK extensions 10.1.2.0.0 XDK Required Support Files 10.2.0.1.0 XML Parser for Java 10.2.0.1.0 XML Parser for Oracle JVM 10.2.0.1.0 There are 107 products installed in this Oracle Home. There are no Interim patches installed in this Oracle Home. -------------------------------------------------------------------------------- OPatch succeeded.
4.11查看数据库的非默认参数
select v.name,v.value,v.ISMODIFIED from v$parameter v where isdefault='FALSE'
健康检查操作手册 第13页
4.12查看控制文件目录
select * from v$controlfile
4.13查看日志文件最大设置
SQL> show parameter max_dump_file_size
4.14查看日志文件目录
SQL> show parameter dump
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ background_core_dump string partial
background_dump_dest string /oracle/admin/omss/bdump core_dump_dest string /oracle/admin/omss/cdump max_dump_file_size string UNLIMITED shadow_core_dump string PARTIAL
user_dump_dest string /oracle/admin/omss/udump
4.15归档日志文件
select a.Group#,a.Thread#,a.Sequence#,a.Bytes,a.Members,a.Archived,a.Status,a.First_Change#,a.First_Time from v$log a select * from v$logfile;
4.16数据文件
select a.status, a.file_name,a.tablespace_name,a.file_id,a.relative_fno,a.bytes,a.autoextensible from dba_data_files a
4.17数据库表空间使用情况
select g.status,
f.tablespace_name, g.extent_management,
健康检查操作手册 第14页
g.segment_space_management, a.total, u.used, --f.free,
round((u.used / a.total) * 100) || '%' \"% used\" --,
--round((f.free / a.total) * 100) \"% Free\"
from (select tablespace_name, sum(bytes / (1024 * 1024)) total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes / (1024 * 1024))) used from dba_extents
group by tablespace_name) u,
(select tablespace_name, round(sum(bytes / (1024 * 1024))) free from dba_free_space group by tablespace_name) f, dba_tablespaces g
WHERE a.tablespace_name = f.tablespace_name and a.tablespace_name = u.tablespace_name and a.tablespace_name=g.tablespace_name;
ONLINE SMF
LOCAL
AUTO 1024
32
3 ONLINE UNDOTBS1 LOCAL MANUAL 28050 ONLINE SYSAUX LOCAL AUTO 10770
485
5 ONLINE USERS
LOCAL
AUTO
86015.92188
77436
健康检查操作手册 第15页
0
90
ONLINE BIE LOCAL AUTO 112 610 977 10 1024
5 9 2 7
20 1
ONLINE SYSTEM LOCAL ONLINE SGOMSS_SMS ONLINE SGOMSSSMS
MANUAL 10920 LOCAL LOCAL
AUTO AUTO
4.18表空间缺省存储情况
select a.tablespace_name,a.initial_extent,a.next_extent,a.min_extents,a.max_extents,a.min_extlen,a.pct_increase from dba_tablespaces a
4.19查看回滚段
select name,value from v$parameter where instr(name,'rollback')>0;
4.20查看用户拥有的权限
select * from dba_role_privs order by GRANTEE
4.21索引维护
select * From dba_indexes where BLEVEL>4; 树深度大于4，需要重建索引以提高效率
5 Oracle性能报表
5.1 awr查看oracle服务器性能
select * from dba_hist_wr_control;
1423236186 +00000 01:00:00.0 +00007 00:00:00.0 以上结果表示,每小时产生一个SNAPSHOT，保留7天 oracle用户登陆 # su - oracle $ sqlplus '/as sysdba'
DEFAULT
健康检查操作手册 第16页
在压力测试或者sql测试前
sql> execute dbms_workload_repository.create_snapshot(); 测试完成后，再次生成快照
sql> execute dbms_workload_repository.create_snapshot(); 如果有了两次生成的快照后，生成报表
sql> @ ?/rdbms/admin/awrrpt.sql;回车，然后输入一个报表名字，生成html格式的报表文件 awrrpt_1_10920_10921.html
5.2 性能报表查看方法
因为10g的awr大部分统计结果与statspack类似，所以可以参考。BTW:awr看起来太舒服了...囧rz
调整的先后次序
- Tune the design(Application designers). - Tune the application(Application developers). - Tune memory. - Tune I/O.
- Tune contention.
- Tune the operating system.
Statspack分析报告详解
statspack 输出结果中必须查看的十项内容 - 负载间档(Load profile)
- 实例效率点击率(Instance efficiency hit ratios) - 首要的5个等待事件(Top 5 wait events) - 等待事件(Wait events) - 闩锁等待
- 首要的SQL(Top sql)
- 实例活动(Instance activity) - 文件I/O(File I/O)
- 内存分配(Memory allocation) - 缓冲区等待(Buffer waits)
健康检查操作手册 第17页
1. 报表头信息
数据库实例相关信息，包括数据库名称、ID、版本号及主机等信息。
STATSPACK report for
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------ BLISSDB 4196236801 blissdb 1 9.2.0.4.0 NO BLISS Snap Id Snap Time Sessions Curs/Sess Comment
------- ------------------ -------- --------- ------------------- Begin Snap: 4 23-6月 -05 17:43:32 10 3.3 End Snap: 5 23-6月 -05 18:01:32 12 6.1 Elapsed: 18.00 (mins) Cache Sizes (end) ~~~~~~~~~~~~~~~~~
Buffer Cache: 24M Std Block Size: 8K Shared Pool Size: 48M Log Buffer: 512K
2. 负载间档
该部分提供每秒和每个事物的统计信息，是监控系统吞吐量和负载变化的重要部分。 Load Profile ~~~~~~~~~~~~
Per Second Per Transaction --------------- --------------- Redo size: 431,200.16 18,627,847.04z Logical reads: 4,150.76 179,312.72 Block changes: 2,252.52 97,309.00 Physical reads: 23.93 1,033.56 Physical writes: 68.08 2,941.04 User calls: 0.96 41.36 Parses: 1.12 48.44 Hard parses: 0.04 1.92 Sorts: 0.77 33.28 Logons: 0.00 0.20 Executes: 2.36 102.12 Transactions: 0.02
健康检查操作手册
第18页
% Blocks changed per Read: .27 Recursive Call %: 86.94 Rollback per transaction %: 12.00 Rows per Sort: 32.59
Redo size：
每秒产生的重做日志大小(单位字节)，可标志数据变更频率, 数据库任务的繁重与否。本例中平均每秒产生了430K左右的重做，每个事务品均产生了18M的重做。 Logical reads：
平次每秒产生的逻辑读，单位是block。 Block changes：
每秒block变化数量，数据库事物带来改变的块数量。 Physical reads：
平均每秒数据库从磁盘读取的block数。 Physical writes：
平均每秒数据库写磁盘的block数。
Logical reads和Physical reads比较：大约有0.55%的逻辑读导致了物理I/O，平均每个事务执行了大约18万个逻辑读，在这个例子中，有一些大的事务被执行，因此很高的读取数目是可以接受的。
User calls： 每秒用户call次数。 Parses/Hard parses：
每秒大约1.12个解析，其中有4%为硬解析，系统每25秒分析一些SQL，都还不错。对于优化好的系统，运行了好几天后，这一列应该达到0，所有的sql在一段时间后都应该在共享池中。 Sorts：
每秒产生的排序次数。 Executes： 每秒执行次数。 Transactions：
每秒产生的事务数，反映数据库任务繁重与否。
% Blocks changed per Read：
说明46%的逻辑读是用于那些只读的而不是可修改的块，该系统只更新%的块。 Rollback per transaction %：
事务回滚的百分比。计算公式为：Round(User rollbacks / (user commits + user
rollbacks) ,4)* 100%。本例中每8.33个事务导致一个回滚。如果回滚率过高，可能说明数据库经历了太多的无效操作。过多的回滚可能还会带来Undo Block的竞争。
健康检查操作手册 第19页
3. 实例命中率
该部分可以提前找出ORACLE潜在将要发生的性能问题，很重要。 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.42 In-memory Sort %: 100.00 Library Hit %: 98.11 Soft Parse %: 96.04 Execute to Parse %: 52.57 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 11.40 % Non-Parse CPU: 99.55
Buffer Nowait %：
在缓冲区中获取Buffer的未等待比率，Buffer Nowait<99%说明，有可能是有热块(查找x$bh的 tch和v$latch_children的cache buffers chains)。 Redo NoWait %：
在Redo缓冲区获取Buffer的未等待比率。 Buffer Hit %：
数据块在数据缓冲区中的命中率，通常应在90%以上，否则，小于95%，需要调整重要的参数，小于90%可能是要加db_cache_size，但是大量的非选择的索引也会造成该值很高（大量的db file sequential read）。如果一个经常访问的列上的索引被删除，可能会造成buffer hit 显著下降。如果增加了索引，但是它影响了ORACLE正确的选择表连接时的驱动顺序，那么可能会导致buffer hit 显著增高。如果命中率变化幅度很大，说明需要改变SQL模式。 In-memory Sort %： 在内存中的排序率。 Library Hit %：
主要代表sql在共享区的命中率，通常在95%以上，否则需要要考虑加大共享池，绑定变量，修改cursor_sharing等参数。 Soft Parse %：
近似看作sql在共享区的命中率，小于<95%,需要考虑到绑定，如果低于80%，那么就可能sql基本没有被重用。
Execute to Parse %：
一个语句执行和分析了多少次的度量。在一个分析，然后执行语句，且再也不在同一个会话中执行它的系统中，这个比值为0。计算公式为：Execute to Parse =100 * (1 - Parses/Executions)。所以如果系统Parses > Executions，就可能出现该比率小于0的情况。本例中，对于每个分析来说大约执行了2.1次。该值<0通常说明shared pool设置或效率存在问题，造成反复解析，reparse可能较严重,或者可是同snapshot有关，如果该值为负值或者极低，通常说明数据库性能存在问题。 Latch Hit %：
健康检查操作手册 第20页
要确保>99%，否则存在严重的性能问题，比如绑定等会影响该参数。 Parse CPU to Parse Elapsd %：
计算公式为：Parse CPU to Parse Elapsd %= 100*(parse time cpu / parse time elapsed)。即：解析实际运行时间/(解析实际运行时间+解析中等待资源时间)。此处为11.4%，非常低，用于解析花费的每个CPU秒花费了大约8.77秒的wall clock时间，这说明花了很多时间等待一个资源。如果该比率为100%，意味着CPU时间等于经过的时间，没有任何等待。 % Non-Parse CPU：
计算公式为：% Non-Parse CPU =round(100*1-PARSE_CPU/TOT_CPU),2)。太低表示解析消耗时间过多。与PARSE_CPU相比，如果TOT_CPU很高，这个比值将接近100%，这是很好的，说明计算机执行的大部分工作是执行查询的工作，而不是分析查询的工作。
4. Shared Pool相关统计数据
Shared Pool Statistics Begin End ------ ------
Memory Usage %: 60.45 62.42
% SQL with executions>1: 81.38 78. % Memory for SQL w/exec>1: 70.36 68.02
Memory Usage %：
正在使用的共 享池的百分率。这个数字应该长时间稳定在75%～90%。如果这个百分率太低，就浪费内存。如果这个百分率太高，会使共享池外部的组件老化，如果SQL语 句被再次执行，这将使得SQL语句被硬解析。在一个大小合适的系统中，共享池的使用率将处于75%到略低于90%的范围内。 % SQL with executions>1：
这 是在共享池中有多少个执行次数大于一次的SQL语句的度量。在一个趋向于循环运行的系统中，必须认真考虑这个数字。在这个循环系统中，在一天中相对于另一 部分时间的部分时间里执行了一组不同的SQL语句。在共享池中，在观察期间将有一组未被执行过的SQL语句，这仅仅是因为要执行它们的语句在观察期间没有 运行。只有系统连续运行相同的SQL语句组，这个数字才会接近100%。这里显示，在这个共享池中几乎有80%的SQL语句在18分钟的观察窗口中运行次 数多于一次。剩下的20%的语句可能已经在那里了--系统只是没有理由去执行它。 % Memory for SQL w/exec>1：
这是与不频繁使用的SQL语句相比，频繁使用的SQL语句消耗内存多少的一个度量。这个数字将在总体上与% SQL with executions>1非常接近，除非有某些查询任务消耗的内存没有规律。
在稳定状态下，总体上会看见随着时间的推移大约有75%～85%的共享池被使用。如果Statspack报表的时间窗口足够大到覆盖所有的周期，执行次数 大于一次的SQL语句的百分率应该接近于100%。这是一个受观察之间持续时间影响的统计数字。可以期望它随观察之间的时间长度增大而增大。
健康检查操作手册 第21页
5. 首要等待事件
常见等待事件说明：
oracle等待事件是衡量oracle运行状况的重要依据及指示，主要有空闲等待事件和非空闲等待事件。
TIMED_STATISTICS：=TRUE，等待事件按等待的时间排序，= FALSE，等待事件按等待的数量排序。 运行statspack期间必须session上设置TIMED_STATISTICS = TRUE。
空闲等待事件是oracle正等待某种工作，在诊断和优化数据库时候，不用过多注意这部分事件，非空闲等待事件专门针对oracle的活动，指数据库任务或应用程序运行过程中发生的等待，这些等待事件是我们在调整数据库应该关注的。 Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- -------- db file sequential read 22,1 259 62.14 CPU time 49 11.67
log file parallel write 2,439 26 6.30 db file parallel write 400 22 5.32 SQL*Net message from dblink 4,575 15 3.71
-------------------------------------------------------------
这里是比其他任何事件都能使速度减慢的事件。比较影响性能的常见等待事件：
db file scattered read：该事件通常与全表扫描有关。因为全表扫描是被放入内存中进行的进行的，通常情况下它不可能被放入连续的缓冲区中，所以就散布在缓冲区的缓存中。 该指数的数量过大说明缺少索引或者了索引的使用(也可以调整optimizer_index_cost_adj)。这种情况也可能是正常的，因为执行 全表扫描可能比索引扫描效率更高。当系统存在这些等待时，需要通过检查来确定全表扫描是否必需的来调整。如果经常必须进行全表扫描，而且表比较小，把该表 存人keep池。如果是大表经常进行全表扫描，那么应该是OLAP系统，而不是OLTP的。
db file sequential read：该事件说明在单个数据块上大量等待，该值过高通常是由于表间连接顺序很糟糕，或者使用了非选择性索引。通过将这种等待与statspack报表中已知其它问题联系起来(如效率不高的sql),通过检查确保索引扫描是必须的，并确保多表连接的连接顺序来调整, DB_CACHE_SIZE可以决定该事件出现的频率。
db file sequential read：该事件说明在单个数据块上大量等待，该值过高通常是由于表间连接顺序很糟糕，或者使用了非选择性索引。通过将这种等待与statspack报表 中已知其它问题联系起来（如效率不高的sql），通过检查确保索引扫描是必须的，并确保多表连接的连接顺序来调整，DB_CACHE_SIZE可以决定该 事件出现的频率。
buffer busy wait：当缓冲区以一种非共享方式或者如正在被读入到缓冲时，就会出现该等待。该值
健康检查操作手册 第22页
不应该大于1%，确认是不是由于热点块造成（如果是可以用反转索引，或者用更小块大小）。 latch free：常跟应用没有很好的应用绑定有关。闩锁是底层的队列机制（更加准确的名称应该是互斥机制），用于保护系统全局区（SGA）共享内存结构闩锁用于 防止对内存结构的并行访问。如果闩锁不可用，就会记录一次闩锁丢失。绝大多数得闩锁问题都与使用绑定变量失败（库缓存闩锁）、生成重作问题（重执行分配闩 锁）、缓存的争用问题(缓存LRU链) 以及缓存的热数据宽块(缓存链)有关。当闩锁丢失率高于0.5%时，需要调整这个问题。
log buffer space：日志缓冲区写的速度快于LGWR写REDOFILE的速度，可以增大日志文件大小，增加日志缓冲区的大小，或者使用更快的磁盘来写数据。
logfile switch：通常是因为归档速度不够快，需要增大重做日志。
log file sync：当一个用户提交或回滚数据时，LGWR将会话得重做操作从日志缓冲区填充到日志文件中，用户的进程必须等待这个填充工作完成。在每次提交时都出现，如果这个等待事件影响到数据库性能，那么就需要修改应用程序的提交频率, 为减少这个等待事件，须一次提交更多记录，或者将重做日志REDO LOG文件访在不同的物理磁盘上。 Wait time: 等待时间包括日志缓冲的写入和发送操作。
6. 数据库用户程序发生的所有等待事件
Wait Events for DB: BLISSDB Instance: blissdb Snaps: 4 -5 -> s - second
-> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last) Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ -------- db file sequential read 22,1 0 259 12 886.2 log file parallel write 2,439 2,012 26 11 97.6 db file parallel write 400 0 22 55 16.0 ...
-------------------------------------------------------------
7. 数据库后台进程发生的等待事件
Background Wait Events for DB: BLISSDB Instance: blissdb Snaps: 4 -5 -> ordered by wait time desc, waits desc (idle events last)
健康检查操作手册 第23页
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ -------- log file parallel write 2,439 2,012 26 11 97.6 db file parallel write 400 0 22 55 16.0 control file parallel write 406 0 5 13 16.2 ...
-------------------------------------------------------------
8. TOP SQL
调整首要的25个缓冲区读操作和首要的25个磁盘读操作做的查询，将可对系统性能产生5%到5000%的增益。
SQL ordered by Gets for DB: BLISSDB Instance: blissdb Snaps: 4 -5 -> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by ....
在报表的这一部分，通过Buffer Gets对SQL语句进行排序，即通过它执行了多少个逻辑I/O来排序。顶端的注释表明一个PL/SQL单元的缓存获得(Buffer Gets)包括被这个代码块执行的所有SQL语句的Buffer Gets。因此将经常在这个列表的顶端看到PL/SQL过程，因为存储过程执行的单独的语句的数目被总计出来。
SQL ordered by Reads for DB: BLISSDB Instance: blissdb Snaps: 4 -5 -> End Disk Reads Threshold: 1000 ...
这部分通过物理读对SQL语句进行排序。这显示引起大部分对这个系统进行读取活动的SQL，即物理I/O。
SQL ordered by Executions for DB: BLISSDB Instance: blissdb Snaps: 4 -5 -> End Executions Threshold: 100 ...
这部分告诉我们在这段时间中执行最多的SQL语句。为了隔离某些频繁执行的查询，以观察是否有某些更改逻辑的方法以避免必须如此频繁的执行这些查询，这可 能是很有用的。或许一个查询正在一个循环的内部执行，而且它可能在循环的外部执行一次，可以设计简单的算法更改以减少必须执行这个查询的次数。即使它运行 的飞快，任何被执行几百万次的操作都将开始耗尽大量的时间。
健康检查操作手册 第24页
9. 实例活动
Instance Activity Stats for DB: BLISSDB Instance: blissdb Snaps: 4 -5 Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------ CPU used by this session 4,870 4.5 194.8 CPU used when call started 4,870 4.5 194.8 CR blocks created 45 0.0 1.8
DBWR buffers scanned 24,5 22.8 983.6
DBWR checkpoint buffers written 14,013 13.0 560.5 DBWR checkpoints 5 0.0 0.2 „„
dirty buffers inspected 38,834 36.0 1,553.4 --脏缓冲的个数
free buffer inspected 40,463 37.5 1,618.5 --如果数量很大,说明缓冲区过小 „„
10.I/O
下面两个报表是面向I/O的。通常，在这里期望在各设备上的读取和写入操作是均匀分布的。要找出什么文件可能非常“热”。一旦DBA了解了如何读取和写入这些数据，他们也许能够通过磁盘间更均匀的分配I/O而得到某些性能提升。
Tablespace IO Stats for DB: BLISSDB Instance: blissdb Snaps: 4 -5 ->ordered by IOs (Reads + Writes) desc Tablespace
------------------------------ Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------ BLISS_DATA
17,9 16 12.3 1.2 44,134 41 0 0.0 UNDOTBS1
4,484 4 9.6 1.0 29,228 27 0 0.0 SYSTEM
340 0 31.0 1.1 36 0 0 0.0
File IO Stats for DB: BLISSDB Instance: blissdb Snaps: 4 -5 ->ordered by Tablespace, File Tablespace Filename
------------------------ ----------------------------------------------------
健康检查操作手册
第25页
Av Av Av Av Buffer Av Buf
Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)
-------------- ------- ------ ------- ------------ -------- ---------- ------ BLISS_DATA D:ORACLEORADATABLISSDBBLISS01.DBF 5,779 5 12.0 1.2 14,4 13 0 D:ORACLEORADATABLISSDBBLISS02.DBF 5,8 5 12.1 1.2 14,772 14 0 D:ORACLEORADATABLISSDBBLISS03.DBF 5,981 6 12.6 1.2 14,908 14 0
11.缓冲池
Buffer Pool Statistics for DB: BLISSDB Instance: blissdb Snaps: 4 -5 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k Free Write Buffer
Number of Cache Buffer Physical Physical Buffer Complete Busy P Buffers Hit % Gets Reads Writes Waits Waits Waits
--- ---------- ----- ----------- ----------- ---------- ------- -------- ------ D 3,000 99.4 4,482,816 25,756 73,470 11 9 0
-------------------------------------------------------------
如果我们使用多缓冲池的功能，上面的报表会告诉我们缓冲池引起的使用故障。实际上这只是我们在报表的开头看到的信息的重复。
12.回滚段活动
Instance Recovery Stats for DB: BLISSDB Instance: blissdb Snaps: 4 -5 -> B: Begin snapshot, E: End snapshot Targt Estd Log File Log Ckpt Log Ckpt
MTTR MTTR Recovery Actual Target Size Timeout Interval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
- ----- ----- ---------- ---------- ---------- ---------- ---------- ---------- B 37 17 169 4012 3453 184320 3453 E 37 32 1385 57132 184320 184320 436361
-------------------------------------------------------------
一般期望活动在各回滚段间(除了SYSTEM回滚段外)均匀分布。在检查报表的这一部分时，报表标题
健康检查操作手册 第26页
也具有需要记住的最有用信息。尤其是，如果完全使用最佳设置时关于Optmal比Avg Active更大的建议。因为这是与DBA最有关的活动(I/O和回滚段信息)。
6 Oracle异常分析
1． 跟踪数据库的变化：可以离线的跟踪数据库的变化，而不会影响在线系统的性能。 2． 回退数据库的变化：回退特定的变化数据，减少point-in-time recovery的执行。 3． 优化和扩容计划：可通过分析日志文件中的数据以分析数据增长模式。 二、安装LogMiner
要安装LogMiner工具，必须首先要运行下面这样两个脚本， l $ORACLE_HOME/rdbms/admin/dbmslm.sql 2 $ORACLE_HOME/rdbms/admin/dbmslmd.sql.
这两个脚本必须均以SYS用户身份运行。其中第一个脚本用来创建DBMS_LOGMNR包，该包用来分析日志文件。第二个脚本用来创建DBMS_LOGMNR_D包，该包用来创建数据字典文件。 三、使用LogMiner工具
下面将详细介绍如何使用LogMiner工具。 1、创建数据字典文件（data-dictionary）
前面已经谈到，LogMiner工具实际上是由两个新的PL/SQL内建包
（(DBMS_LOGMNR 和 DBMS_ LOGMNR_D）和四 个V$动态性能视图（视图是在利用过程
DBMS_LOGMNR.START_LOGMNR启动LogMiner时创建）组成。在使用LogMiner工具 分析redo log文件之前，可以使用DBMS_LOGMNR_D 包将数据字典导出为一个文本文件。该字典文件是可选的，但是如果没有 它，LogMiner解释出来的语句中关于数据字典中的部分（如表名、列名等）和数值都将是16进制的形式，我们是无法直接理解的。例如，下面的sql语 句：
INSERT INTO dm_dj_swry (rydm, rymc) VALUES (00005, '张三'); LogMiner解释出来的结果将是下面这个样子，
insert into Object#308(col#1, col#2) values (hextoraw('c30rte567e436'), hextoraw('4a6f686e20446f65'));
创建数据字典的目的就是让LogMiner引用涉及到内部数据字典中的部分时为他们实际的名字，而不是系统内部的16进制。数据字典文件是一个文 本文件，使用包DBMS_LOGMNR_D来创建。如果我们要分析的数据库
健康检查操作手册
第27页
中的表有变化，影响到库的数据字典也发生变化，这时就需要重新创建该字典文件。 另外一种情况是在分析另外一个数据库文件的重作日志时，也必须要重新生成一遍被分析数据库的数据字典文件。
首先在init.ora初始化参数文件中，指定数据字典文件的位置，也就是添加一个参数UTL_FILE_DIR，该参数值为服务器中放置数据字典文件的目录。如： UTL_FILE_DIR = (e:\\Oracle\\logs)
重新启动数据库，使新加的参数生效，然后创建数据字典文件： SQL>CONNECT SYS
SQL>EXECUTE dbms_logmnr_d.build( dictionary_filename =>; ' v816dict.ora', dictionary_location =>; 'e:\\oracle\\logs'); 2、创建要分析的日志文件列表
Oracle的重作日志分为两种，在线（online）和离线（offline）归档日志文件，下面就分别来讨论这两种不同日志文件的列表创建。 （1）分析在线重作日志文件 A. 创建列表
SQL>; EXECUTE dbms_logmnr.add_logfile( LogFileName=>;' e:\\Oracle\\oradata\\sxf\\redo01.log', Options=>;dbms_logmnr.new); B. 添加其他日志文件到列表
SQL>; EXECUTE dbms_logmnr.add_logfile( LogFileName=>;' e:\\Oracle\\oradata\\sxf\\redo02.log', Options=>;dbms_logmnr.addfile);（2）分析离线日志文件 A.创建列表
SQL>; EXECUTE dbms_logmnr.add_logfile(
LogFileName=>;' E:\\Oracle\\oradata\\sxf\\archive\\ARCARC09108.001',
健康检查操作手册 第28页
Options=>;dbms_logmnr.new); B.添加另外的日志文件到列表
SQL>; EXECUTE dbms_logmnr.add_logfile(
LogFileName=>;' E:\\Oracle\\oradata\\sxf\\archive\\ARCARC09109.001',
Options=>;dbms_logmnr.addfile);关于这个日志文件列表中需要分析日志文件的个数完全由你自己决定，但这里建议最好是每次只添加一个需要分析的日志文件，在对该文件分析完毕后，再添加另外的文件。
和添加日志分析列表相对应，使用过程 'dbms_logmnr.removefile' 也可以从列表中移去一个日志文件。下面的例子移去上面添加的日志文件e:\\Oracle\\oradata\\sxf\\redo02.log。 SQL>; EXECUTE dbms_logmnr.add_logfile( LogFileName=>;' e:\\Oracle\\oradata\\sxf\\redo02.log', Options=>;dbms_logmnr. REMOVEFILE);
创建了要分析的日志文件列表，下面就可以对其进行分析了。 3、使用LogMiner进行日志分析 （1）无条件
SQL>; EXECUTE dbms_logmnr.start_logmnr( DictFileName=>;' e:\\oracle\\logs\\ v816dict.ora '); （2）有条件
通过对过程DBMS_ LOGMNR.START_LOGMNR中几个不同参数的设置（参数含义见表1），可以缩小要分析日志文件的范围。通过设置起始时间和终止时间参数我们可以只分析某一时间范围的日志。如下面的例子，我们仅仅分析2001年9月18日的日志，： SQL>; EXECUTE dbms_logmnr.start_logmnr( DictFileName =>; ' e:\\oracle\\logs\\ v816dict.ora ',
StartTime =>; to_date('2001-9-18 00:00:00','YYYY-MM-DD HH24:MI:SS') EndTime =>; to_date(''2001-9-18 23:59:59','YYYY-MM-DD HH24:MI:SS ')); 也可以通过设置起始SCN和截至SCN来要分析日志的范围：
健康检查操作手册 第29页
SQL>; EXECUTE dbms_logmnr.start_logmnr( DictFileName =>; ' e:\\oracle\\logs\\ v816dict.ora ', StartScn =>; 20, EndScn =>; 50);
表1 DBMS_LOGMNR.START__LOGMNR过程参数含义
4、观察分析结果（v$logmnr_contents）
到现在为止，我们已经分析得到了重作日志文件中的内容。动态性能视图v$logmnr_contents包含LogMiner分析得到的所有的信息。
SELECT sql_redo FROM v$logmnr_contents;
如果我们仅仅想知道某个用户对于某张表的操作，可以通过下面的SQL查询得到，该查询可以得到用户DB_ZGXT对表SB_DJJL所作的一切工作。
SQL>; SELECT sql_redo FROM v$logmnr_contents WHERE username='DB_ZGXT' AND tablename='SB_DJJL'; 需要强调一点的是，视图v$logmnr_contents中的分析结果仅在我们运行过程'dbms_logmrn.start_logmnr' 这个会话的生命期中存在。这是因为所有的LogMiner存储都在PGA内存中，所有其他的进程是看不到它的，同时随着进程的结束，分析结果也随之消失。
最后，使用过程DBMS_LOGMNR.END_LOGMNR终止日志分析事务，此时PGA内存区域被清除，分析结果也随之不再存在。 四、其他注意事项
我们可以利用LogMiner日志分析工具来分析其他数据库实例产生的重作日志文件，而不仅仅用来分析本身安装LogMiner的数据库实例的redo logs文件。使用LogMiner分析其他数据库实例时，有几点需要注意： 1. LogMiner必须使用被分析数据库实例产生的字典文件，而不是安装LogMiner的数据库产生的字典文件，另外必须保证安装LogMiner数据库的字符集和被分析数据库的字符集相同。
2. 被分析数据库平台必须和当前LogMiner所在数据库平台一样，也就是说如果我们要分析的文件是由运行在UNIX平台上的 Oracle 8i产生的，那么也必须在一个运行在UNIX平台上的Oracle实例上运行LogMiner，而不能在其他如Microsoft NT上 运行LogMiner。当然两者的硬件条件不一定要求完全一样。
3. LogMiner日志分析工具仅能够分析Oracle 8以后的产品，对于8以前的产品，该工具也为力。
健康检查操作手册 第30页
因篇幅问题不能全部显示，请点此查看更多更全内容
Copyright © 2019- sceh.cn 版权所有 湘ICP备2023017654号-4
违法及侵权请联系：TEL:199 1889 7713 E-MAIL:2724546146@qq.com
本站由北京市万商天勤律师事务所王兴未律师提供法律服务