statspack报告的分析_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
statspack报告的分析
上传于||文档简介
&&statspack报告的分析
阅读已结束，如果下载本文需要使用2下载券
想免费下载本文？
定制HR最喜欢的简历
下载文档到电脑，查找使用更方便
还剩6页未读，继续阅读
定制HR最喜欢的简历
你可能喜欢Statspack报告中top sql语句-oracle-电脑编程网Statspack报告中top sql语句作者： 西方经济学的百草园 　和相关&&
&
使用snapshot在level=5（默认级别）或者以上时，可以在statspack报告中收集一些按各种统计值排序的sql语句：
1、sql ordered by gets：
&& 包含获取buffer 访问较多的sql语句。默认的阙值是10000 buffer gets，超过这个值的sql语句将被收集在这个列表中。
&& 显示的列有buffer gets、executions、gets per exec，%total，cpu time（s），elapsed time（s）和hash value。
2、sql ordered by reads：
&& 包含从磁盘read io比较高的sql语句。默认的阙值是1000 disk reads，超过这个值的所有sql语句被显示出来。
&& 显示的列有：physical reads、executions、reads per exec，
%total，cpu time（s），elapsed time（s）和hash value。
3、sql ordered by executions：
包含执行次数较多的sql语句。默认的阙值是100。执行次数超过这个阙值的所有sql语句将被列出在此。
显示的列有：executions、rows processed、rows per exec，cpu per exec（s），elapsed per exec（s）和hash value。
4、sql ordered by parse calls：
包含soft parse calls较多的sql语句。默认的阙值是1000。即soft parse calls超过1000的sql语句将全部列出在此。
显示的列有：parses calls、executions、%total parses和hash value。
5、sql ordered by sharable memory：
包含占用library cache memory较多的sql语句。默认的阙值是1M（1048576 bytes）。
显示的列有sharable mem（b），executions，%total和hash value。
6、sql ordered by version count：
包含版本较多的sql语句，即parent cursors相同，而children cursors不同的sql语句，一般表现为sql文本相同，但是执行的环境不同，比如不同的username等等。默认的阙值是20。
显示的列有：version count，executions和hash value。
相关资料：｜｜｜｜｜｜｜Statspack报告中top sql语句来源网络，如有侵权请告知，即处理！编程Tags：　　　　　　　　　　　　　　　　&　　　　　　　　　　　　　　　　　　　　博客访问： 2271799
博文数量： 126
注册时间：
认证徽章：
Oracle DBA
ITPUB论坛APP
ITPUB论坛APP
APP发帖 享双倍积分
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Oracle
Oracle 部署 Statspack
[实验环境]
操作系统 RedHat 5.5
主机名 lxh
数据库版本 Oracle 10.2.0
字符集 GHS16GBK
生产库实例名 prod
[实验过程]
1.部署statspack
1.1、创建一个专门用于statspack的表空间statspack
SYS@ prod>create tablespace statspack datafile '/u01/app/oracle/oradata/prod/statspack01.dbf' size 300m;
1.2、以sysdba身份执行创建prefstat对象的脚本
SYS@ prod>@?/rdbms/admin/spcreate.sql
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password:oracle
Choose the Default tablespace for the PERFSTAT user
---------------------------------------------------
Below is the list of online tablespaces in this database which can
store user data. &Specifying the SYSTEM tablespace for the user's
default tablespace will result in the installation FAILING, as
using SYSTEM for performance data is not supported.
Choose the PERFSTAT users's default tablespace. &This is the tablespace
in which the STATSPACK tables and indexes will be created.
TABLESPACE_NAME & & & & & & & &CONTENTS &STATSPACK DEFAULT TABLESPACE
------------------------------ --------- ----------------------------
EXAMPLE & & & & & & & & & & & &PERMANENT
STATSPACK & & & & & & & & & & &PERMANENT
SYSAUX & & & & & & & & & & & & PERMANENT *
TBS1 & & & & & & & & & & & & & PERMANENT
TEST3 & & & & & & & & & & & & &PERMANENT
USERS & & & & & & & & & & & & &PERMANENT
will result in STATSPACK's recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace:statspack
Using tablespace STATSPACK as PERFSTAT default tablespace.
Choose the Temporary tablespace for the PERFSTAT user
-----------------------------------------------------
Below is the list of online tablespaces in this database which can
store temporary data (e.g. for sort workareas). &Specifying the SYSTEM
tablespace for the user's temporary tablespace will result in the
installation FAILING, as using SYSTEM for workareas is not supported.
Choose the PERFSTAT user's Temporary tablespace.
TABLESPACE_NAME & & & & & & & &CONTENTS &DB DEFAULT TEMP TABLESPACE
------------------------------ --------- --------------------------
TEMP & & & & & & & & & & & & & TEMPORARY *
will result in the database's default Temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace:回车
SPCPKG complete. Please check spcpkg.lis for any errors.
PERFSTAT@ prod>
Statstack安装完成。
1.3、设置statspack自动产生快照的间隔时间为15分钟
[oracle@James ~]$vi /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/spauto.sql
&select instance_number into :instno from v$
&dbms_job.submit(:jobno, 'statspack.',trunc(sysdate+1/96,'MI'), 'trunc(SYSDATE+1/96,''MI'')', TRUE, :instno);
1.4、设置快照的默认级别为7级
SYS@ prod>exec statspack.modify_statspack_parameter(i_snap_level=>7);
2.产生statspack报告
2.1、启动statspack的自动快照
SCOTT@ prod>conn perfstat/oracle
PERFSTAT@ prod>@?/rdbms/admin/spauto
2.2、验证statspack自动生成的报告
PERFSTAT@prod>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
PERFSTAT@prod>select snap_id,snap_time,snap_level from stats$snapshot order by snap_
& SNAP_ID SNAP_TIME & & & & & SNAP_LEVEL
---------- ------------------- ----------
17:52:02 & & & & &7
19:05:03 & & & & &7
19:20:04 & & & & &7
19:35:05 & & & & &7
19:50:05 & & & & &7
20:05:01 & & & & &7
2.3、生成statspack分析报告
SQL>@?/rdbms/admin/spreport
Current Instance
~~~~~~~~~~~~~~~~
& DB Id & &DB Name & & &Inst Num Instance
----------- ------------ -------- ------------
& PROD & & & & & & & &1 prod
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
& DB Id & &Inst Num DB Name & & &Instance & & Host
----------- -------- ------------ ------------ ------------
& & & & &1 PROD & & & & prod & & & & lxh
Using & for database Id
Using & & & & &1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. &Pressing
specifying a number lists all completed snapshots.
Listing all Completed Snapshots
& & & & & & & & & & & & & & & & & & & & & & & & & & & Snap
Instance & & DB Name & & & &Snap Id & Snap Started & &Level Comment
------------ ------------ --------- ----------------- ----- --------------------
prod & & & & PROD & & & & & & & & 1 25 Aug
& & & & & & & & & & & & & & & & &2 25 Aug
& & & & & & & & & & & & & & & & &3 25 Aug
& & & & & & & & & & & & & & & & &4 25 Aug
& & & & & & & & & & & & & & & & &5 25 Aug
& & & & & & & & & & & & & & & & &6 25 Aug
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:2
Enter value for end_snap: 3
Enter value for report_name:
@?/rdbms/admin/spreport
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 3
Enter value for end_snap: 4
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 4
Enter value for end_snap: 5
Enter value for report_name:
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 5
Enter value for end_snap: 6
Enter value for report_name:
2.4、取消statspack自动生成快照
查看job ID
PERFSTAT@ prod>select job,log_user,last_date,next_date from user_
& & & JOB LOG_USER & & & & & & & & & & & LAST_DATE & & & & & NEXT_DATE
---------- ------------------------------ ------------------- -------------------
& & & &22 PERFSTAT & & & & & & & & & & &
PERFSTAT@ prod>exec dbms_job.remove('22');
阅读(9984) | 评论(0) | 转发(0) |
相关热门文章
给主人留下些什么吧！~~
请登录后评论。博客访问： 136473
博文数量： 47
博客积分： 2014
博客等级： 上尉
技术积分： 480
注册时间：
IT168企业级官微
微信号：IT168qiye
系统架构师大会
微信号：SACC2013
分类： Oracle
环境：红旗操作系统Redflag Server 5
DB： RAC（postdb1、postdb2）
Statspack报告说明 & & & & 1& & & & .& & & & 数据库总体信息& & & & 2& & & & .& & & & 每秒每事务的资源消耗情况& & & & 3& & & & .& & & & 实例的各组件的命中率& & & & 4& & & & .& & & & 共享池总体情况& & & & 5& & & & .& & & & 前5个等待事件& & & & 6& & & & .& & & & DB所有等待事件& & & & 7& & & & .& & & & 后台进程等待事件& & & & 8& & & & .& & & & 根据BufferGets进行排序的SQL& & & & 9& & & & .& & & & 按物理IO进行排序的SQL& & & & 10& & & & .& & & & 按执行次数排序的SQL& & & & 11& & & & .& & & & 按分析次数排序的SQL& & & & 12& & & & .& & & & 实例的当前活动的统计数据& & & & 13& & & & .& & & & tablespaceIO统计数据& & & & 14& & & & .& & & & 表空间文件IO统计数据& & & & 15& & & & .& & & & buffer池统计数据& & & & 16& & & & .& & & & 实例恢复统计数据& & & & 17& & & & .& & & & Buffer池的参考数据& & & & 18& & & & .& & & & Buffer等待统计数据& & & & 19& & & & .& & & & PGA总体统计数据1& & & & 20& & & & .& & & & PGA总体统计数据2& & & & 21& & & & .& & & & PGA内存参考数据& & & & 22& & & & .& & & & 回滚段统计& & & & 23& & & & .& & & & 回滚段存储统计& & & & 24& & & & .& & & & undo段总体情况& & & & 25& & & & .& & & & undo段统计& & & & 26& & & & .& & & & 锁存器的当前情况& & & & 27& & & & .& & & & 锁存器睡眠等待统计& & & & 28& & & & .& & & & 锁存器失败情况(对系统影响不大)& & & & 29& & & & .& & & & 数据字典cache性能统计(由oracle自己管理，可以忽略)& & & & 30& & & & .& & & & 库cache性能统计& & & & 31& & & & .& & & & 共享池性能统计(遗漏，没有总结)& & & & 32& & & & .& & & & SGA区总体情况& & & & 33& & & & .& & & & SGA各组件的活动情况& & & & 34& & & & .& & & & 系统配置参数
以下是生成报告的各部分信息的解释(只列出了每一部分的头部和描述)：& & & & STATSPACK report for ------------------------------------------------------------------------------------------1. DB的总体信息------------------------------------------------------------------------------------------------------（数据库和实例的名字）DB Name DB Id Instance Inst Num Release Cluster Host -------------------------- -------- ----------- ------- ------------ MYDB
mydb 1 9.2.0.1.0 NO VCS-SERVER1 Snap Id Snap Time Sessions Curs/Sess Comment ------- ------------------ ------- --------- ------------------- Begin Snap: 1 09-Aug-04 19:28:12 32 2.7 End Snap: 2 09-Aug-04 19:33:06 32 3.0 Elapsed: 4.90 (mins) (本次报告的间隔时间) Cache Sizes (end) （当前缓冲区主要部件的配置）~~~~~~~~~~~~~~~~~ Buffer Cache: 1,536M Std Block Size: 8K Shared Pool Size: 112M Log Buffer: 16,000K ------------------------------------------------------------------------------------2.每秒每事务的资源消耗情况-------------------------------------------------------------------------------------------------Load Profile （加载配置文件）------------------ Per Second (每秒) Per Transaction(每事务) ------- Redo size: & & & & & & & & 38,498.93 & & & && &&&6,733.30 –每秒/每事务产生的redo大小 Logical reads: & & & & 593.28 & & & & & & & & 103.76 –每秒/每事务逻辑读 Block changes: & & & & 77.60 & & & & & & & & & & & & 13.57 –每秒/每事务修改的块数 Physical reads: & & & & 2.65 & & & & & & & & & & & & 0.46 -- 每秒/每事务物理读 Physical writes: & & & & 8.17 & & & & & & & & & & & & 1.43 —每秒/每事务物理写 User calls: & & & & & & & & 38.32 & & & & & & & & & & & & 6.70 Parses: & & & & & & & & & & & & 6.52 & & & & & & & & & & & & 1.14 --SQL分析的次数 Hard parses: & & & & & & & & 0.05 & & & & & & & & & & & & 0.01 –SQL硬分析的次数 Sorts:& & & & & & & & & & & & & & & & 0.73 & & & & & & & & & & & & 0.13-- Logons:& & & & & & & & & & & & 0.01 & & & & & & & & & & & & 0.00 Executes: & & & & & & & & & & & & 39.64 & & & & & & & & & & & & 6.93 Transactions: & & & & 5.72 % Blocks changed per Read: 13.08 Recursive Call %: 24.84 Rollback per transaction %: 0.00 Rows per Sort: 138.04 说明： Redo Size——Per Second：每秒产生的日志大小(单位字节)可标志数据库任务的繁重与否 Redo Size——Per Transaction：平均每个事务的日志生成量 （单位字节）Logical Reads——Per Second(逻辑读)：平均每秒产生的逻辑读,单位是block Logical Reads——Per Transaction：平均每个事务产生的逻辑读,单位是block&&Block Changes——Per Second：每秒block变化数量，数据库事务带来改变的块数量&&Block Changes——Per Transaction：平均每个事务所导致的改变的块数Physical Reads——Per Second：平均每秒数据库从磁盘读取的block数 ,单位是block Physical Reads——Per Transaction：平均每个事务从磁盘读取的block数,单位是blockPhysical Write——Per Second：平均每秒写磁盘的block数 Physical Write——Per Transaction：平均每个事务写磁盘的block数User Calls——Per Second：每秒用户call次数 User Calls——Per Transaction：每事务用户call次数Parses——Per Second：每秒解析次数，近似反应了每秒语句的执行次数 Parses——Per Transaction：每事务产生的解析次数Hard Parses——Per Second：每秒产生的硬解析次数 Hard Parses——Per Transaction：每事务产生的硬解析次数Sorts——Per Second：每秒产生的排序次数 Sorts——Per Transaction：#5#每事务产生的排序次数Transactions——Per Second：每秒产生的事务数Executes——Per Second：每秒执行次数 Execute——Per Transaction：每个事务执行次数Logons——Per Second ：每秒钟有多少次logon硬分析：就是之前不存在此SQL，是第一次解析。 如果SQL重用度很高，则硬解析应保持很低。 % Blocks changed per Read：表示逻辑读用于只读而不是修改的块的比例。发生变化的块数/读次数，即每次Read有百分之多少的block发生了变化。变化的块Recursive Call %:递归操作占所有操作的比率Rollback per transaction %: 事务的回滚率 Rows per Sort: 每次排序所涉及到的行数 -------------------------------------------------------------------------------------3.实例的各组件的命中率------------------------------ ---------------------------------------------------------------------Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 & & & & & & & & & & & & Redo NoWait %: 100.00 Buffer Hit %: 99.55 & & & & & & & & & & & & & & & & In-memory Sort %: 100.00 Library Hit %: 99.33 & & & & & & & & & & & & Soft Parse %: 99.16 Execute to Parse %: 83.56 & & & & & & & & Latch Hit %: 99.99 Parse CPU to Parse Elapsd %: % Non-Parse CPU: 说明： Buffer Nowait %：即Buffer Nowait Ratio，在缓冲区中获取buffer 的未等待比率。Redo Nowait：写日志的不等待比率,太低可调整log_buffer(增加)和 _log_io_size(减小，默认为1/3*log_buffer/log_block_size,使得 _log_io_size 为合适的值，比如128k/log_block_size)。Buffer Hit：即Data Buffer Hit Ratio，数据块在数据缓冲区中的命中率，通常应该在90%以上，否则考虑加大db_block_buffers(9i 以上可是db_cache_size)。In-memory Sort %：即In Memory Sort Ratio，如果过低说明有大量的排序在临时表空间中进行，可尝试增加sort_area_size ，很多时候也需要检查一下是否存在不良SQL。Library Hit %：即Library Hit Ratio，主要代表着sql在共享区的命中率，通常在98%以上 Soft Parse %：即Soft Parse Ratio，近似当作sql在共享区的命中率，通常高代表使用了绑定变量,太低需要调整应用使用绑定变量，或者参考 cursor_sharing = force。Execute to Parse %: 是语句执行与分析的比例，如果要SQL重用率高，则这个比例会很高。该值越高表示一次解析后被重复执行的次数越多，如果过低可以考虑设置session_cached_cursors > 0 Latch Hit ：内部结构维护锁命中率，高于99%,通常低是因为shared_pool_size过大和没有使用绑定变量导致硬解析过多，可参考 _spin_count 参数设置。 Soft Parse %:软分析：即在共享池中重复使用的SQL，系统应保持较高的软分析率，否则说明系统的SQL没有绑定变量。Percent Non-Parse CPU：查询实际运行时间/(查询实际运行时间+sql解析时间)，该值大于95％比较好，太低表示解析消耗时间过长。 Parse CPU to Parse Elapsd %: 用于分析每个CPU 花费的秒数，应该处于较高比例。如果=100%,说明CPU没有等待。 ------------------------------------------------------------------------------------------4.共享池总体情况------------------------------- ---------------------------------------------------------------------Shared Pool Statistics Begin End Memory Usage %: 89.91 90.55 % SQL with executions>1: 32.14 32.67 % Memory for SQL w/exec>1: 31.30 33.38 说明： Memory Usage %: 正在使用的共享池的%,这个值应保持在75%~90%,如果这个值太低浪费内存，如果太高内存不足，会使共享池外部的组件老化，如果SQL语句被再次执行，则就会发生硬分析。 % SQL with executions>1:共享池中执行次数大于1的sql的比率（若太小可能是没有使用绑定变量） % Memory for SQL w/exec>1: 频繁使用的SQL语句消耗内存多少的比例。即Percent of Memory for SQl with Execution，执行次数大于1的sql消耗内存/(所有sql消耗内存) ---------------------------------------------------------------------------------------------5.前5个等待事件----------------------------- ---------------------------------------------------------------------Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event& && && && && && && && && && && & Waits& &Time (s)&&Ela Time --------------------------------------------------------------------- log file sync& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & 1,682 & & & && &2 & & & & & & & &&&32.30 db file sequential read& && && & & & & & & & & & & & 623 & & & && &3 & & & & & & & &&&46.70 db file parallel write & & & & & & & & & & & & & & & & & & & & 7,780& & 114,321& &3.97db file scattered read & & & & & & & & & & & & & & & & & & & & 8,560& & 70,945& & 2.46buffer busy waits & & & & & & & & & & & & & & & & & & & & & & & & 12,303& &40,058& & 1.39--------------------------------------------------------------------- 说明： log file sync:当一个用户的会话提交时，会话的重写信息需要刷新到重做日志文件中，这个用户会话将发送LGWR将日志缓冲写到重做日志文件，当LGWR已经完成写入操作时，它将发送这个用户会话。当commit发生时，会产生log file sync.减少commit频率，加快redo的写速度来改善此等待。另外也需要考察IO子系统的配置是否存在问题，例如可以考虑将redo log file 与数据文件分开存放，并将redo log file放到raw device上面。db file sequential read：和索引扫描有关，查看IO统计，解决IO冲突；加大db_block_buffers；修改应用，减少IO。db file scattered read：当ORACLE全表扫描时,一次需读多个数据块，此时使用这一等待事件。i n i t .o r a中的db▁ file▁mutiblock▁read▁count定义了多数据块读取时，一次能读取的最大块数。一般此参数定义为4—16，与数据库大小无关。但值越大DB▁BLOCK▁SlZE应越小。如果db file scattered read所占比例较大,必须减少IO的代价（如使用更快的磁盘,均衡IO分布），或减少全表扫描的次数（优化SQL语句）。(多blocks读;解决方法是修改db_file_multiblock_read_让表用下列语句查找IO过量的session:SELECT sid,total_waits, time_waitedFROM v$session_eventWHERE event='db file scattered read'and total_waits>0ORDER BY 3,2;buffer busy waits：有两个原因会引起buffer busy waits, 1.其他的session在将数据读到block中，2.其他的session对block加了锁，与请求的锁不兼容。如果buffer busy waits等待的时间过长的话，我们要去查询是哪一部分(查看report.txt中buffer busy waits statistics的统计):SELECT count, file#, nameFROM x$kcbfwait, v$datafileWHERE indx + 1 = file#ORDER BY count；SELECT p1 "File", p2 "Block", p3 "Reason"FROM v$session_waitWHERE event='buffer busy waits'；SELECT distinct owner, segment_name, segment_typeFROM dba_extentsWHERE file_id= &FILE_IDand &BLOCK_NUMBER between block_id and block_id+blocks-1；Wait Time:等待时间包括日志缓冲的写入和发送操作。 --------------------------------------------------------------------- -------------------------6.DB所有等待事件--------------------------- --------------------------------------------------------------------- Wait Events for DB: MYDB Instance: mydb Snaps: 1 -2 -> 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) 下面是非后台进程的等待事件：这一部分很重要，它指出了oracle在等待资源时话了多少时间。忽略那些等待时间占总时间比例少的等待event,把剩余的等待时间加到一起，计算每个event占总时间的比例。Avg Total Wait wait Waits Event& && && && && && && && && &Waits Timeouts Time (s) (ms) /txn --------------------------------------------------------------------- db file sequential read& & & & & & & & & & & & 623 & & & & 0 & & & & & & & & & & & & 3 & & & & & & & & 5 & & & & & & & & 0.4 log file sync & & & & & & & & & & & & & & & & & & & & 1,682 & & & & 0 & & & & & & & & & & & & 2 & & & & & & & & 1 & & & & & & & & 1.0 control file parallel write & & & & 95 & & & & & & & & 0 & & & & & & & & & & & & 1 & & & & & & & & 6 & & & & & & & & 0.1 direct path read & & & & & & & & & & & & & & & & & & & & 727 & & & & 0 & & & & & & & & & & & & 3,462 & & & & 48 & & & & & & & & 0.0db file parallel write & & & & & & & & & & & & 190 & & & & 95 & & & & & & & & & & & & 0 & & & & & & & & 2 & & & & & & & & 0.1 log file parallel write & & & & & & & & & & & & 1,674 & & & & 1,664 & & & & & & & & 0 & & & & & & & & 0 & & & & & & & & 1.0 db file scattered read & & & & & & & & & & & & 25 & & & & & & & & 0 & & & & & & & & & & & & 0 & & & & & & & & 2 & & & & & & & & 0.0 log file switch completion & & & & & & & & 60 & & & & & & & & 0 & & & & & & & & & & & & 2,029 & & & & 338& & & & & & & & 0.0control file sequential read & & & & 78 & & & & & & & & 0 & & & & & & & & & & & & 0 & & & & & & & & 0 & & & & & & & & 0.0 LGWR wait for redo copy & & & & & & & & & & & & 13 & & & & & & & & 0 & & & & & & & & & & & & 0 & & & & & & & & 0 & & & & & & & & 0.0 SQL*Net break/reset to clien & & & & 4 & & & & & & & & 0 & & & & & & & & & & & & 0 & & & & & & & & 0 & & & & & & & & 0.0 buffer busy waits & & & & & & & & & & & & & & & & 2 & & & & & & & & 0 & & & & & & & & & & & & 0 & & & & & & & & 0 & & & & & & & & 0.0 library cache pin & & & & & & & & & & & & & & & & 1 & & & & & & & & 0 & & & & & & & & & & & & 0 & & & & & & & & 0 & & & & & & & & 0.0enqueue & & & & & & & & & & & & & & & & & & & & & & & & & & & & 706 & & & & 2 & & & & & & & & & & & & 1,759 & & & & 25 & & & & & & & & 0.0latch free & & & & & & & & & & & & & & & & & & & & & & & & 1 & & & & & & & & 0 & & & & & & & & & & & & 0 & & & & & & & & 0 & & & & & & & & 0.0 SQL*Net message from client & & & & 10,830 0 & & & & & & & & & & & & 4,364 403 & & & & & & & & 6.4 SQL*Net more data from clien & & & & 1,596 & & & & 0 & & & & & & & & & & & & 0 & & & & & & & & 0 & & & & & & & & 0.9 SQL*Net message to client & & & & & & & & 10,830 0 & & & & & & & & & & & & 0 & & & & & & & & 0 & & & & & & & & 6.4 --------------------------------------------------------------------- db file sequential read：表示ORACLE顺序读数据块，一般出现在读索引。如果db file sequential read等待很长，必须减少IO的代价（如使用更快的磁盘，均衡IO分布），或增加缓冲区log file sync:任何时候一个事物提交时，它将通知LGWR将LOG▁BUFFER写入日志文件，如果此部分占用时间较长，应减少COMMIT的次数，此外应使用性能更好的IO系统，另一个相关的事件是”log buffer parallel write” ，也与IO系统或CPU资源太少有关。buffer busy waits：多个进程访问（修改）缓冲区中同一数据块时出现此等待事件。当表没有free lists而对表并行插入时，或回滚段个数太少时，会出现此事件，V$WA|TSTAT及 STATSPACK报告可辅助找出原因。latch free：(通过下列语句找到子糟糕的latch(查看report.txt中latch的统计和分析部分）)SELECT latch#, name, gets, misses, sleepsFROM v$latchWHERE sleeps>0ORDER BYdirect path read:直接读到process private buffer,与oracle buffer无关。与OS的IO系统有关，如果有问题，可以尝试关闭文件系统的direct IO选项.log file switch completion:因为会话还没有滚动到下一个日志，日志文件switch（检查点未完成）将等待下一个日志切换。因为日志的检查点没有完成，log switch就不能执行。Enqueue:一般为应用程序使用的锁，例如SEELECT ... FOR UPDATE。如果此部分占用的时间较大，需分析应用系统，尤其是长时间占有锁资源的代码。要分析每个锁的等待时间不太可能，虽然V$LOCK记录了每种所等待的次数。(普通的锁（LOCAL LOCK）)SELECT ksqsttyp "Lock",ksqstget "Gets",ksqstwat "Waits"FROM X$KSQST where KSQSTWAT>0;library cache pin:这个latch发生在一个session要使用shared pool中一个对象时，其他session将这个对象以不兼容的模式pin.一般发生在一个对象或语句有比较严重的冲突。& & & & SELECT p1 "Handle"FROM v$session_waitWHERE event='library cache pin'SELECT address, to_char(hash_value,'9'), version_count, sql_textFROM v$sqlareaWHERE version_count>10ORDER BY version_SQL*Net message from client: 进程等待从client发送数据，可以忽略.--------------------------------------------------------------------- -----------------------7.后台进程等待事件---------------------------- --------------------------------------------------------------------- Background Wait Events for DB: MYDB Instance: mydb Snaps: 1 -2 -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & Waits Timeouts Time(s) (ms) /txn --------------------------------------------------------------------- control file parallel write & & & & 95 & & & & & & & & 0 & & & & & & & & & & & & 1 & & & & & & & & 6 & & & & & & & & 0.1 db file parallel write & & & & & & & & & & & & 190 & & & & 95 & & & & & & & & & & & & 0 & & & & & & & & 2 & & & & & & & & 0.1 log file parallel write & & & & & & & & & & & & 1,674 & & & & 1,664 & & & & & & & & 0 & & & & & & & & 0 & & & & & & & & 1.0 control file sequential read & & & & 36 & & & & & & & & 0 & & & & & & & & & & & & 0 & & & & & & & & 0 & & & & & & & & 0.0 LGWR wait for redo copy & & & & & & & & & & & & 13 & & & & & & & & 0 & & & & & & & & & & & & 0 & & & & & & & & 0 & & & & & & & & 0.0 rdbms ipc message & & & & & & & & & & & & & & & & 5,352 & & & & 3,687 & & & & & & & & 1,148 & & & & & & & & 214 3.2 smon timer & & & & & & & & & & & & & & & & & & & & & & & & 1 & & & & & & & & 1 & & & & & & & & & & & & 281 & & & & & & & & ### 0.0 --------------------------------------------------------------------- ----------------8.根据BufferGets进行排序的SQL----------------------- --------------------------------------------------------------------- SQL ordered by Gets for DB: MYDB Instance: mydb Snaps: 1 -2 -> End Buffer Gets Threshold: 10000 -> Note that resources reported for PL/SQL includes the resources used by all SQL statements called within the PL/SQL code. As individual SQL statements are also reported, it is possible and valid for the summed total % to exceed 100 CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value ---------&&----------&&-----------&&-----&&------&&------&&---------- 74,380 & & & & & & & & 20 & & & & & & & & & & & & 3,719.0&&& & & & & & & & 42.6 0.00 & & & && &5.03& & select count(*) from myuser.userbaseinfo 10,920 & & & & & & & & 1,291 & & & & & & & & 8.5 & & & & & & & & & & & & 6.3&&0.00 & & & && &0.71& & insert into Refence_tabvalues(1, 2, 3, 4, 5,6) …………………………省略部分内容………………………………………………-------------------------------------------------------------------- 说明： Top SQL with High Buffer Gets:这类sql进行了大量的block的读，要检查该sql是否用到了索引，或者说表上是否存在合理的索引，对于必须全表扫描的大表可以考虑recycle buffer ,对于频繁进行全表扫描的小表可以考虑keep buffer，还有一种需要注意的情况就是如果通过索引获取数据比例占表数据比例过大，比如20%(举例数据)，就能导致buffer gets过大。如果需要在有序表中获取低于40%的数据，或者在无序表中获取低于7%的数据，那么可以考虑使用索引代替全表扫表。--------------------------------------------------------------------- -------------------9.按物理IO进行排序的SQL-------------------------- --------------------------------------------------------------------- SQL ordered by Reads for DB: MYDB Instance: mydb Snaps: 1 -2 -> End Disk Reads Threshold: 1000 CPU Elapsd Phy Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------&&---------- ------------&&------&&------ -------- ---------246 & & & & & & & & 1,962 & & & & & & & & & & & & 0.1 & & & & & & & & 31.5 & & & & 0.00 & & & &&&1.72& &&& select * from destinfo where sub_isdn=1 149 & & & & & & & & 20 & & & & & & & & & & & & & & & & 7.5 & & & & & & & & 19.1 & & & & 0.00 & & & && &5.03& &
select count(*) from myuser.userbaseinfo …………………………省略部分内容………………………………………………-------------------------------------------------------------------- 说明： 　　如果发现系统的IO有限制，则可检查上述的SQL语句。Top SQL with High Execution Count：这类sql是需要重点关注的，也许这些sql本身一次执行并没有消耗大量的时间或者空间，但由于频繁的执行对系统影响极大，所以只要有优化的可能到要对这些sql进行优化。还有另外一些情况，就是某些程序中可能大量频繁地使用dual表来获取一些信息(比如时间的计算等)，尽可能的使这类sql转化为应用本地能解决的函数，或者还存在一些由于设计上的缺陷导致不必要的查询，都要在设计的角度避免这些查询。--------------------------------------------------------------------- ----------------------10.按执行次数排序的SQL------------------------ --------------------------------------------------------------------- SQL ordered by Executions for DB: MYDB Instance: mydb Snaps: 1 -2 -> End Executions Threshold: 100 CPU per Elap per Executions& &Rows Processed& &Rows per Exec Exec(s) Exec (s) Hash Value ----------&&----------------&&-----------&&----& &------& & -------- 2,907 & & & & & & & & & & & & 904 & & & & & & & & & & & & & & & & 0.3 & & & & & & & &&&0.00& &0.00& &&& select * from userinfo where sub_isdn=1 2,435 & & & & & & & & & & & & 2,435 & & & & & & & & & & & & & & & & 1.0 & & & & & & & &&&0.00& &0.00& &&& select * from msginfo where msg_id=1 …………………………省略部分内容………………………………………………--------------------------------------------------------------------- 说明： 对于频繁执行的SQL语句，应注意跟踪。 --------------------------------------------------------------------- --------------------11.按分析次数排序的SQL------------------------- --------------------------------------------------------------------- SQL ordered by Parse Calls for DB: MYDB Instance: mydb Snaps: 1 -2 -> End Parse Calls Threshold: 1000 % Total Parse Calls & & & & & & & & Executions & & & & & & & & Parses & & & & & & & & Hash Value ------------ & & & & & & & & ------------ & & & & & & & & -------- & & & & & & & & ---------- 1,291 & & & & & & & & & & & & & & & & 1,291 & & & & & & & & & & & & & & & & 67.38 & & & & & & & & & & & &
insert into Refence_tabvalues(1, :p2, :p3, :p4, :p5,:p6) 118 & & & & & & & & & & & & & & & & 118 & & & & & & & & & & & & & & & & 6.16 & & & & & & & & & & & &
select ISDN,pass from smReq where isDel = '0' …………………………省略部分内容………………………………………………--------------------------------------------------------------------- ----------------12.实例的当前活动的统计数据-------------------------- --------------------------------------------------------------------- Instance Activity Stats for DB: MYDB Instance: mydb Snaps: 1 -2 （实例活动统计）Statistic & & & & & & & & & & & & & & & & & & & & Total & & & & & & & & per Second & & & & & & & & per Trans ---------& & & & & & & & & & & & & & & & & & --------& & & & ---------- & & & &&&------------ CR blocks created & & & & & & & & 8 & & & & & & & & & & & & & & & & 0.0 & & & & & & & & & & & & 0.0 DBWR buffers scanned & & & & 0 & & & & & & & & & & & & & & & & 0.0 & & & & & & & & & & & & 0.0 DBWR checkpoint buffers written 2,402&&8.2 & & & & & & & & & & & & 1.4 DBWR checkpoints & & & & & & & & & & & & 0 & & & & & & & & & & & & & & & & 0.0 & & & & & & & & & & & & 0.0 DBWR free buffers found 0 & & & & & & & & & & & & & & & & 0.0 & & & & & & & & & & & & 0.0 DBWR lru scans & & & & & & & & & & & & 0 & & & & & & & & & & & & & & & & 0.0 & & & & & & & & & & & & 0.0 DBWR make free requests&&0 & & & & & & & & & & & & & & & & 0.0 & & & & & & & & & & & & 0.0 DBWR revisited being-written buff 0 & & & & 0.0 & & & & & & & & & & & & 0.0 DBWR summed scan depth & & & & 0 & & & & & & & & & & & & & & & & 0.0 & & & & & & & & & & & & 0.0 …………………………省略部分内容………………………………………………-------------------------------------------------------------------- 说明： 可通过是面详细数据计算各种命中率等。 如：soft parse= (1-hard parse/total parse)*100%= (1-16/%=99.16%就是报表开头的soft parse值。 parse time cpu :分析（parsing）SQL是一个开销很大的，它可以通过SQL语句的重用来避免。在预编译程序中，可通过增加MAXOPENCURSORS参数减少这部分开销。V$SQL的PARSE▁CALLS和EXECUTIONS可用来找出经常parse的语句。CPU used by this session：有很多因素会引起cpu过载，1、比较parse time cpu和CPU used by session, 如果比例高的话a、SQL重用比例低，有一些效率不高的语句(过多的buffer get)，通过以下语句查询SELECT address, hash_value,buffer_gets, executions, buffer_gets/executions "Gets/Exec",sql_textFROM v$sqlareaWHERE buffer_gets > 50000 and executions>0ORDER BY 3;b、寻找CPU使用过量的sessionSELECT v.sid, substr(s.name,1,30) "Statistic", v.valueFROM v$statname s , v$sesstat vWHERE s.name = 'CPU used by this session'and v.statistic#=s.statistic#and v.value>0ORDER BY 3;recursive cpu usage：如果处理大量的PLSQL此成分可能很高，这里不深入讨论此问题产生的原因，但你需要找出你所有的PLSQL，包括存储过程。找出CPU开销最大的PLSQL过程并对其优化。如果PLSQL中的主要工作是完成过程处理而非执行SQL，高开销的recursive cpu usage可能是需要优化的成分。--------------------------------------------------------------------- --------------------13.tablespace IO统计数据------------------------- --------------------------------------------------------------------- Tablespace IO Stats for DB: MYDB Instance: mydb Snaps: 1 -2 ->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) ----- ------- ------ ------ ------- ------- ----&&-------------------&&USERS 444 & & & & 2 & & & & & & & & 5.0 & & & & 1.3 & & & & 874 & & & & & & & & 3 & & & & & & & & 0 & & & & & & & & 0.0 SHTMSG 131 & & & & 0 & & & & & & & & 1.9 & & & & 1.0 & & & & 1,005 & & & & & & & & 3 & & & & & & & & 1 & & & & & & & & 0.0 UNDOTBS 0 & & & & & & & & 0 & & & & & & & & 0.0 & & & & 353 & & & & 1 & & & & & & & & & & & & 1 & & & & & & & & 0 & & & & & & & & 0.0 IDEX 73 & & & & & & & & 0 & & & & & & & & 3.7 & & & & 1.0 & & & & 147 & & & & & & & & 1 & & & & & & & & 0 & & & & & & & & 0.0 SYSTEM 0 & & & & & & & & 0 & & & & & & & & 0.0 & & & & 1& & & & & & & & 1 & & & & & & & & & & & & 0 & & & & & & & & 0 & & & & & & & & 0.0 --------------------------------------------------------------------- Table Space I/O：表示各表空间在IO上的分布，若出现严重不均衡，则要重新考虑对象的存储规划和表空间中数据文件的磁盘规划------------------------------------------------------------------------------------------14.表空间文件 IO统计数据-------------------------------------------------------------------------------------------- File IO Stats for DB: MYDB Instance: mydb Snaps: 1 -2 ->ordered by Tablespace, File Tablespace Filename --------------------------------------------------------------------- Av & & & & & & & & Av & & & & & & & &&&Av & & & & & & & & & & & & & & & & & & & & Av & & & & & & & &&&Buffer & & & & Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s&&Waits & & & & & & & & Wt(ms) ---- -------&&------ ----- -------- -------&&--------&&-------------- IDEX /dev/lvidex 73 & & & & & & & & 0 & & & & & & & & 3.7 & & & & 1.0 & & & & 147 & & & & & & & & & & & & 1 & & & & & & & & & & & & 0 SHTMSG /dev/lvshtmsg 131 & & & & 0 & & & & & & & & 1.9 & & & & 1.0 & & & & 1,005 & & & & 3 & & & & & & & & & & & & & & & & 1 & & & & & & & & & & & & 0.0 SYSTEM /dev/lvsystem 0 & & & & & & & & 0 & & & & & & & & 11 & & & & & & & & 0 & & & & & & & & 0 UNDOTBS /dev/lvundotbs 0 & & & & & & & & 0 & & & & & & & & 353 & & & & 1 & & & & & & & & 1& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & &&&0.0 USERS /dev/lvusers 444 & & & & 2 & & & & & & & & 5.0 & & & & & & & & 1.3 & & & & & & & & 874 & & & & & & & & & & & & 3 & & & & & & & & & & & & & & & & 0 --------------------------------------------------------------------- Datafile I/O：表示各数据文件的IO分布，若不均衡则需要重新考虑对象的存储规划。-----------------------------------------------------------------------------------------15.buffer池统计数据--------------------------------------------------------------------------------------------------Buffer Pool Statistics for DB: MYDB Instance: mydb Snaps: 1 -2 -> 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 190,560 99.7 258,404 780 2,390 0 0 2 --------------------------------------------------------------------- ----------------------16.实例恢复统计数据---------------------------- ---------------------------------------------------------------------Instance Recovery Stats for DB: MYDB Instance: mydb Snaps: 1 -2 -> 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 161 & & & & 28 & & & & & & & & 11145 & & & & & & & & 100666 & & & & 100000 & & & & 450000 & & & & 000 E 161 & & & & 28 & & & & & & & & 11608 & & & & & & & & 100043 & & & & 100000 & & & & 450000 & & & & 000 --------------------------------------------------------------------- ---------------------17.Buffer池的参考数据-------------------------- ---------------------------------------------------------------------Buffer Pool Advisory for DB: MYDB Instance: mydb End Snap: 2 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate Size for Size Buffers for Est Physical Estimated P Estimate (M) Factr Estimate Read Factor Physical Reads --- ----------- ----- ---------------- ------------- ------------------ D 160 .1 19,850 1.92 81,500,147 D 320 .2 39,700 1.69 71,754,201 D 480 .3 59,550 1.57 66,774,777 D 640 .4 79,400 1.49 63,154,327 D 800 .5 99,250 1.40 59,432,741 D 960 .6 119,100 1.22 52,009,931 D 1,120 .7 138,950 1.08 46,041,143 D 1,280 .8 158,800 1.04 44,306,471 D 1,440 .9 178,650 1.01 42,974,849 D 1,536 1.0 190,560 1.00 42,478,849 D 1,600 1.0 198,500 0.99 42,032,350 D 1,760 1.1 218,350 0.97 41,128,356 D 1,920 1.3 238,200 0.94 39,877,785 D 2,080 1.4 258,050 0.92 39,058,138 D 2,240 1.5 277,900 0.91 38,475,363 D 2,400 1.6 297,750 0.88 37,173,210 D 2,560 1.7 317,600 0.85 35,983,344 D 2,720 1.8 337,450 0.83 35,325,000 D 2,880 1.9 357,300 0.81 34,595,023 D 3,040 2.0 377,150 0.79 33,731,234 D 3,200 2.1 397,000 0.77 32,711,726 --------------------------------------------------------------------- ---------------------18.Buffer等待统计数据-------------------------- ---------------------------------------------------------------------Buffer wait Statistics for DB: MYDB Instance: mydb Snaps: 1 -2 -> ordered by wait time desc, waits desc Tot Wait Avg Class Waits Time (s) Time (ms) ------------------ ----------- ---------- --------------------------- data block 1 0 0 undo block 1 0 0 -------------------------------------------------------------------- -----------------19.PGA总体统计数据 1------------------------------ ---------------------------------------------------------------------PGA Aggr Target Stats for DB: MYDB Instance: mydb Snaps: 1 -2 -> B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval) -> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory -> Auto PGA Target - actual workarea memory target -> W/A PGA Used - amount of memory used for all Workareas (manual + auto) -> %PGA W/A Mem - percentage of PGA memory allocated to workareas -> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt -> %Man W/A Mem - percentage of workarea memory under manual control PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ---------------- - ------------------------ 100.0 7 0 %PGA %Auto %Man PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K) - --------- --------- ---------- ---------- ------ ------ ------ ---------- B 200 174 27.1 0.0 .0 .0 .0 10,240 E 200 174 27.1 0.0 .0 .0 .0 10,240 --------------------------------------------------------------------- -------------------20.PGA统计数据 2--------------------------------- ---------------------------------------------------------------------PGA Aggr Target Histogram(柱状图) for DB: MYDB Instance: mydb Snaps: 1 -2 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ----------- ------------ 8K 16K 98 98 0 0 16K 32K 12 12 0 0 32K 64K 2 2 0 0 64K 128K 3 3 0 0 2M 4M 2 2 0 0 -------------------------------------------------------------------- ----------------------21.PGA内存参考数据 --------------------------- ---------------------------------------------------------------------PGA Memory Advisory for DB: MYDB Instance: mydb End Snap: 2 -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0 Estd Extra Estd PGA Estd PGA PGA Target Size W/A MB W/A MB Read/ Cache Overalloc Est (MB) Factr Processed Written to Disk Hit % Count --------- ------- ---------------- --------------- -------- ---------- 25 0.1 6,180.6 3,466.6 64.0 0 50 0.3 6,180.6 3,459.1 64.0 0 100 0.5 6,180.6 861.1 88.0 0 150 0.8 6,180.6 861.1 88.0 0 200 1.0 6,180.6 861.1 88.0 0 240 1.2 6,180.6 861.1 88.0 0 280 1.4 6,180.6 861.1 88.0 0 320 1.6 6,180.6 861.1 88.0 0 360 1.8 6,180.6 861.1 88.0 0 400 2.0 6,180.6 861.1 88.0 0 600 3.0 6,180.6 861.1 88.0 0 800 4.0 6,180.6 861.1 88.0 0 1,200 6.0 6,180.6 861.1 88.0 0 1,600 8.0 6,180.6 629.9 91.0 0--------------------------------------------------------------------- ---------------------22.回滚段统计----------------------------------- ---------------------------------------------------------------------Rollback Segment Stats for DB: MYDB Instance: mydb Snaps: 1 -2 ->A high value for "Pct Waits" suggests more rollback segments may be required ->RBS stats may not be accurate between begin and end snaps when using Auto Undo managment, as RBS may be dynamically created and dropped as needed Trans Table Pct Undo Bytes RBS No Gets Waits Written Wraps Shrinks Extends ------ -------------- ------- --------------- ------- -------- -------- 0 2.0 0.00 0 0 0 0 1 345.0 0.00 388,926 1 0 0 2 458.0 0.00 519,636 1 0 0 3 397.0 0.00 348,516 1 0 0 4 330.0 0.00 319,184 0 0 0 5 467.0 0.00 561,346 0 0 0 6 403.0 0.00 387,552 0 0 0 7 431.0 0.00 711,298 0 0 0 8 444.0 0.00 348,148 1 0 0 9 393.0 0.00 376,176 1 0 0 10 334.0 0.00 320,640 0 0 0 --------------------------------------------------------------------- -------------------23.回滚段存储统计--------------------------------- ---------------------------------------------------------------------Rollback Segment Storage for DB: MYDB Instance: mydb Snaps: 1 -2 ->Optimal Size should be larger than Avg Active RBS No Segment Size Avg Active 　 Optimal Size Maximum Size ----- ------------- -------------　--------------　　 --------------- 0 　　 385,024 0 385,024 1 24,240,128 953,522 38,920,192 2 29,483,008 1,012,839 50,520,064 3 29,483,008 999,571 50,454,528 4 27,385,856 943,570 38,920,192 5 28,434,432 965,310 117,563,392 6 30,531,584 1,036,116 84,008,960 7 27,385,856 918,922 151,183,360 8 28,434,432 1,009,932 50,520,064 9 29,483,008 1,036,116 50,454,528 10 25,288,704 953,522 37,871,616 -------------------------------------------------------------------- ------------------24.undo段总体情况--------------------------------- ---------------------------------------------------------------------Undo Segment Summary for DB: MYDB Instance: mydb Snaps: 1 -2 -> Undo segment block stats: -> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed -> eS - expired Stolen, eR - expired Released, eU - expired reUsed Undo Undo 　 Num 　 Max Qry Max Tx Snapshot Out of uS/uR/uU/ TS# Blocks Trans 　Len (s) Concurcy Too Old Space eS/eR/eU --- - ---------- --------- ------- --------- ------- ------ ------------- 1 2,970 ########## 357 2 0 0 0/0/0/0/0/0 -------------------------------------------------------------------- ---------------------25.undo段统计---------------------------------- ---------------------------------------------------------------------Undo Segment Stats for DB: MYDB Instance: mydb Snaps: 1 -2 -> ordered by Time desc 　　 Undo Num Max Qry Max Tx Snap Out of uS/uR/uU/ End Time Blocks Trans 　Len (s) Concy Too Old Space eS/eR/eU ---------- ---------- ------- ------ ------- ------ ------ ------------- 09-Aug 19:42 1,306 ######## 16 2 0 0 0/0/0/0/0/0 09-Aug 19:32 1,664 ######## 357 2 0 0 0/0/0/0/0/0 --------------------------------------------------------------------- ------------------26.锁存器的当前情况-------------------------------- ---------------------------------------------------------------------Latch Activity for DB: MYDB Instance: mydb Snaps: 1 -2 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------- ------------ ----- ------ ------ ------------ ------ Consistent RBA 1,673 0.0 0 0 SQL memory manager latch 1 0.0 0 96 0.0 SQL memory manager worka 6,565 0.0 0 0 active checkpoint queue 192 0.0 0 0 cache buffer handles 452 0.0 0 0 cache buffers chains 366,694 0.0 0.0 0 1,707 0.0 cache buffers lru chain 3,980 0.0 0 552 0.0 channel handle pool latc 4 0.0 0 0 channel operations paren 199 0.0 0 0 checkpoint queue latch 13,445 0.1 0.0 0 2,741 0.0 child cursor hash table 201 0.0 0 0 dml lock allocation 8,361 0.0 0 0 dummy allocation 4 0.0 0 0 enqueue hash chains 12,371 0.0 0.0 0 0 enqueues 2,662 0.0 0 0 event group latch 2 0.0 0 0 file number translation 14 0.0 0 0 hash table column usage 7 0.0 0 0 job_queue_processes para 5 0.0 0 0 ktm global data 1 0.0 0 0 lgwr LWN SCN 1,674 0.0 0 0 library cache 80,405 0.0 0.1 0 0 library cache load lock 46 0.0 0 0 library cache pin 28,408 0.0 0 0 library cache pin alloca 7,888 0.0 0 0 list of block allocation 8 0.0 0 0 messages 12,581 0.0 0.0 0 0 mostly latch-free SCN 1,733 2.4 0.0 0 0 multiblock read objects 54 0.0 0 0 ncodef allocation latch 5 0.0 0 0 post/wait queue 3,346 0.0 0 1,681 0.0 process allocation 2 0.0 0 2 0.0 process group creation 4 0.0 0 0 redo allocation 15,281---------------------------------------------------------------------------------------27& & & & .锁存器睡眠等待统计--------------------------------------------------------------------------------------------------Latch Sleep breakdown for DB: ALEPH0 Instance: aleph0 Snaps: 3 -5-> ordered by misses desclatch对oracle性能有比较大的影响通过下列语句查询有问题的latch：SELECT latch#, name, gets, misses, sleepsFROM v$latchWHERE sleeps>0ORDER BY sleeps；通过下列语句查询具体latch类型为LATCH_NUMBER_WANTED的情况（如果没有的话，说明这类latch只有一个，即仅存在于v$latch中），如果有几个latch占了大部分的sleep时间，说明有问题（注意addr）SELECT addr, latch#, gets, misses, sleepsFROM v$latch_childrenWHERE sleeps>0and latch# = &LATCH_NUMBER_WANTEDORDER BY sleeps ；Get Spin &Latch Name Requests Misses Sleeps Sleeps 1->4------------------- -------------- ----------- ----------- ------------library cache 5,166,288 27,612 6,374 /1482/403/0在soft parsing和hard parsing时都会大量使用此latch，如有可能应修改应用程序，减少竞争。在init.ora中设置cursor▁sharing为force可减少soft parsing和hard parsing需要的library cache。此外定义session▁cached▁cursors也能减少同一session中soft parsing对library cache的竞争。此外还可以定义cursor▁space▁for▁time=true。从oracle7.2开始，library cache latches有child latch。多使用绑定变量，可以提高性能cache buffers chains 19,043,430 7,399 2,948 /344/0block的竞争会引起这个latch的问题，每个cache buffers chains latch覆盖一部分buffer cache,如果一两个child latches在V$LATCH_CHILDREN比较突出,查询相应的buffer blockSELECT File# , dbablk, class, stateFROM x$bhWHERE hladdr='&ADDR_OF_CHILD_LATCH';问题是由hot block引起的，oracle 8i后，由字段X$BH.TCH（touch-count）也可以看出hot buffer block.session idle bit 7,228,262 3,131 48 /0/0session allocation 396,123 2,377 220 /19/0redo writing 339,651 2,170 95 /0/0cache buffers lru chain 522,371 959 45 915/43/1/0/0如果设置了DB_BLOCK_LRU_STATISTICS=TRUE,将会很不利的影响这个latch,确认DB_BLOCK_LRU_STATISTICS=FALSE.可以通过修改DB_BLOCK_LRU_LATCHES,解决问题enqueues 597,561 598 23 575/23/0/0/0redo allocation 421,232 489 16 473/16/0/0/0checkpoint queue latch 813,418 369 11 358/11/0/0/0enqueue hash chains 418,078 357 35 322/35/0/0/0transaction allocation 299,655 337 81 293/28/5/11/0undo global data 306,679 329 10 319/10/0/0/0messages 382,796 204 49 188/4/3/9/0dml lock allocation 214,252 76 3 73/3/0/0/0list of block allocation 192,162 73 4 69/4/0/0/0shared pool 37,496 36 65 12/1/14/9/0latch wait list 3,734 28 1 27/1/0/0/0row cache objects 91,062 18 34 9/4/0/5/0row cache保护字典信息，如表和列的信息。hard parsing需要row cache 。在init.ora中设置cursor▁sharing为force可减少竞争。longop free list 7,363 3 7 1/0/1/1/0process allocation 988 3 2 1/2/0/0/0user lock 3,938 2 5 1/0/0/1/0---------------------------------------------------------------------------------------28& & & & .锁存器失败情况------------------------------------------------------------------------------------------------------Latch Miss Sources for DB: ALEPH0 Instance: aleph0 Snaps: 3 -5-> only latches with sleeps are shown-> ordered by name, sleeps descno_wait latch misses没有上面的latch sleep那样对系统影响大NoWait WaiterLatch Name Where Misses Sleeps Sleeps------------------- ----------------------- ------- ---------- -------cache buffers chains kcbgcur: kslbegin 0 1,527 222cache buffers chains kcbgtcr: kslbegin 0 635 1,300cache buffers chains kcbget: pin buffer 0 506 178cache buffers chains kcbchg: kslbegin: bufs not 0 209 106cache buffers chains kcbbxsv 0 34 23cache buffers chains kcbzwb 0 18 9cache buffers chains kcbrls: kslbegin 0 10 93cache buffers chains kcbzgb: scan from tail. no 0 3 0cache buffers chains kcbbwb1 0 1 300cache buffers chains kcbnlc 0 1 82cache buffers chains kcbget: exchange rls 0 1 0cache buffers chains kcbesc: escalate 0 1 0cache buffers chains kcbchg: kslbegin: call CR 0 1 14cache buffers chains kcbbwdb 0 1 5,227cache buffers lru chain kcbzgb: multiple sets nowa 0 20 0cache buffers lru chain kcbbiop: lru scan 0 13 0cache buffers lru chain kcbzgb: posted for free bu 0 10 39cache buffers lru chain kcbbioc 0 1 2cache buffers lru chain kcbzgb: retry 0 1 27checkpoint queue latch kcbklbc: Link buffer into 0 5 11checkpoint queue latch kcbbxsv: move to being wri 0 3 0checkpoint queue latch kcbbcrcv: check recovery q 0 2 0checkpoint queue latch kcbbwthc: thread checkpoin 0 1 0cost function kzulrl 0 5 0dml lock allocation ktaidm 0 2 1dml lock allocation ktaiam 0 1 2enqueue hash chains ksqrcl 0 22 14enqueue hash chains ksqgtl3 0 11 21enqueue hash chains ksqcmi: get hash chain lat 0 2 0enqueues ksqgtl2 0 10 2enqueues ksqrcl 0 6 5enqueues ksqies 0 4 15enqueues ksqgel: create enqueue 0 3 0latch wait list kslfre 5 1 1library cache kglhdgn: child: 0 3,888 1,399library cache kglic 0 1,158 25library cache kglpnal: child: alloc spac 0 518 1,049library cache kglpnal: child: before pro 0 386 1,817library cache kgllkdl: child: cleanup 0 105 92library cache kglpnp: child 0 99 0library cache kgldte: child 0 0 87 1,082library cache kglhdgc: child: 0 84 224library cache kgllkdl: child: free pin 0 11 269library cache kglobpn: child: 0 11 1library cache kglpnc: child 0 7 239library cache kglupc: child 0 5 163library cache kgldti: 2child 0 4 8library cache kglati 0 2 0library cache kglget: child: KGLDSBRD 0 2 10list of block allocation ktlabl 0 2 2list of block allocation ktlbbl 0 2 2longop free list ksuloget 0 7 7messages ksaamb: after wakeup 0 48 42Latch Miss Sources for DB: ALEPH0 Instance: aleph0 Snaps: 3 -5-> only latches with sleeps are shown-> ordered by name, sleeps descNoWait WaiterLatch Name Where Misses Sleeps Sleeps------------------ ------------------------ ------- ---------- -------messages ksarcv: after wait 0 1 0process allocation ksuapc 1 2 2redo allocation kcrfwr: redo allocation 0 16 16redo writing kcrfss 0 85 91redo writing kcrfwi: after write 0 9 0redo writing kcrfsr 0 1 2row cache objects kqrpre: find obj 0 25 33row cache objects kqrpsc: incr stat 0 9 0session allocation ksuxds: KSUSFCLC not set 0 121 0session allocation ksuxds: not user session 0 63 91session allocation ksucri 0 36 129session idle bit ksupuc: set busy 0 20 12session idle bit ksupuc: clear busy 0 14 27session idle bit ksuxds 0 14 9shared pool kghalo 0 47 15shared pool kghfre 0 18 0transaction allocation ktcxba 0 75 68transaction allocation ktcdso 0 6 13undo global data ktubnd 0 9 8undo global data ktudba: KSLBEGIN 0 1 1---------------------------------------------------------------------------------------29& & & & .数据字典cache性能统计----------------------------------------------------------------------------------------------Dictionary Cache Stats for DB: ALEPH0 Instance: aleph0 Snaps: 3 -5->"Pct Misses" should be very low (< 2% in most cases)->"Cache Usage" is the number of cache entries being used->"Pct SGA" is the ratio of usage to allocated size for that cache字典cache的性能，由oracle自己管理，可以忽略Get Pct Scan Pct Mod Final PctCache Requests Miss Requests Miss Req Usage SGA---------------------- ------------ ------ -------- ----- -------- ------ ----dc_constraints 0 0 0 236 100dc_database_links 0 0 0 0 0dc_files 0 0 0 71 92dc_free_extents 139 6.5 31 0.0 45 1,978 91dc_global_oids 0 0 0 0 0dc_histogram_data 0 0 0 0 0dc_histogram_data_valu 0 0 0 0 0dc_histogram_defs 0 0 0 274 96dc_object_ids 6,977 0.0 0 0 642 100dc_objects 2,440 0.0 0 1 813 99dc_outlines 0 0 0 0 0dc_profiles 1,966 0.0 0 0 2 67dc_rollback_segments 96 0.0 0 0 13 68dc_segments 37 0.0 0 31 607 99dc_sequence_grants 0 0 0 3 27dc_sequences 238 0.0 0 238 7 44dc_synonyms 0 0 0 91 92dc_tablespace_quotas 124 0.8 0 31 15 83dc_tablespaces 117 0.0 0 0 9 90dc_used_extents 31 100.0 0 31 748 99dc_user_grants 4,059 0.0 0 0 28 60dc_usernames 3,421 0.0 0 0 25 61dc_users 14,009 0.0 0 0 29 85ifs_acl_cache_entries 0 0 0 0 0---------------------------------------------------------------------------------------30& & & & .库cache性能统计----------------------------------------------------------------------------------------------------Library Cache Activity for DB: ALEPH0 Instance: aleph0 Snaps: 3 -5->"Pct Misses" should be very lowlibrary cache是oracle存储对象定义，SQL语句等 的地方，每个namespace包含一类对象，Reload列比较重要Get Pct Pin Pct Invali-Namespace Requests Miss Requests Miss Reloads dations------------ ----------- ------ ------------- ------ ---------- --------BODY 6 0.0 6 0.0 0 0CLUSTER 0 0 0 0INDEX 0 0 0 0OBJECT 0 0 0 0PIPE 0 0 0 0SQL AREA 257,072 -0.0 1,181,401 0.1 1,470 0TABLE/PROCEDURE 9,756 0.0 14,635 0.0 0 0TRIGGER 0 0 0 0---------------------------------------------------------------------------------------32& & & & .SGA区总体情况------------------------------------------------------------------------------------------------------SGA Memory Summary for DB: ALEPH0 Instance: aleph0 Snaps: 3 -5SGA regions Size in Bytes------------------------------ ----------------Database Buffers 327,680,000Fixed Size 73,620Redo Buffers 2,629,632Variable Size 212,111,360----------------sum 542,494,612---------------------------------------------------------------------------------------33& & & & .SGA各组件的活动情况------------------------------------------------------------------------------------------------SGA breakdown difference for DB: ALEPH0 Instance: aleph0 Snaps: 3 -5Pool Name Begin value End value Difference-------- ------------------- -------------- -------------- -----------java pool free memory 20,000,768 20,000,768 0large pool free memory 50,000,000 50,000,000 0shared pool KGFF heap 41,440 41,440 0shared pool KGK heap 3,572 3,572 0shared pool KQLS heap 2,358,348 2,358,348 0shared pool PL/SQL DIANA 2,154,612 2,171,584 16,972shared pool PL/SQL MPCODE 251,336 251,336 0shared pool PL/SQL SOURCE 4,776 4,776 0shared pool PLS non-lib hp 2,096 2,096 0shared pool State objects 472,944 472,944 0shared pool VIRTUAL CIRCUITS 722,732 722,732 0shared pool branches 117,600 117,600 0shared pool db_block_buffers 5,440,000 5,440,000 0shared pool db_block_hash_buckets 771,240 771,240 0shared pool db_handles 200,000 200,000 0shared pool dictionary cache 1,679,028 1,683,276 4,248shared pool event statistics per ses 1,545,040 1,545,040 0shared pool fixed allocation callbac 992 992 0shared pool free memory 101,754,560 101,392,080 -362,480shared pool ktlbk state objects 209,292 209,292 0shared pool library cache 6,186,792 6,197,856 11,064shared pool long op statistics array 110,000 110,000 0shared pool message pool freequeue 124,552 124,552 0shared pool miscellaneous 1,607,556 1,619,980 12,424shared pool processes 316,800 316,800 0shared pool sessions 959,420 959,420 0shared pool sql area 8,996,848 9,003,820 6,972shared pool state objects 5,468,404 5,779,204 310,800shared pool table columns 54,756 54,756 0shared pool table definiti 9,148 9,148 0shared pool transaction_branches 89,792 89,792 0shared pool transactions 436,188 436,188 0shared pool trigger defini 196 196 0shared pool trigger inform 816 816 0shared pool trigger source 320 320 0shared pool view columns d 1,072 1,072 0db_block_buffers 327,680,000 327,680,000 0fixed_sga 73,620 73,620 0log_buffer 2,621,440 2,621,440 0-------------------------------------------------------------------------------------------34& & & & .系统配置参数----------------------------------------------------------------------------------------------------init.ora Parameters for DB: ALEPH0 Instance: aleph0 Snaps: 3 -5End valueParameter Name Begin value (if different)--------------------- ------------------------------ --------------background_dump_dest /aleph/app/oracle/admin/aleph0/bdcompatible 8.1.7.0.0control_files /aleph/oradata/aleph0/control01.ccore_dump_dest /aleph/app/oracle/admin/aleph0/cdcursor_space_for_time TRUEdb_block_buffers 40000db_block_size 8192db_file_multiblock_read_count 16db_files 80db_name aleph0dml_locks 100global_names FALSEifile /aleph/app/oracle/admin/aleph0/pfinstance_name aleph0large_pool_size log_archive_dest_1 location=/back/archlog_archive_dest_2 location=/aleph/oradata/aleph0/arlog_archive_dest_state_1 ENABLElog_archive_dest_state_2 ENABLElog_archive_format arch_aleph0_%t_%s.dbflog_archive_start TRUElog_buffer 2621440log_checkpoint_interval 10000max_dump_file_size 10240mts_dispatchers (protocol=ipc)(dispatchers=4), (pmts_listener_address (ADDRESS=(PROTOCOL=tcp)(host=alepmts_max_dispatchers 20mts_max_servers 40mts_servers 4mts_service aleph0open_cursors 2000parallel_max_servers 5processes 400remote_login_passwordfile EXCLUSIVErollback_segments r01, r02, r03, r04, r05, r06, r07session_max_open_files 20shared_pool_size sort_area_retained_size 300000sort_area_size 1000000timed_statistics TRUEuser_dump_dest /aleph/app/oracle/admin/aleph0/udutl_file_dir *-------------------------------------------------------------End of Report---------------------------------------------------------------------
阅读(1292) | 评论(2) | 转发(1) |
相关热门文章
给主人留下些什么吧！~~
好东西，楼主总结的比较详细，适合新手
请登录后评论。