ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 性能优化 - Oracle Tuning 总结 2-1 Statspack

性能优化 - Oracle Tuning 总结 2-1 Statspack

原创 Linux操作系统 作者:tolywang 时间:2009-08-09 16:19:05 0 删除 编辑

 

2.2 对statspack报告的分析
从上面的描述可以看出,产生一个statspack报告是比较简单的,但是如何读懂statspack报告却不是那么容易,需要对Oracle的体系架构、内存结构、等待事件

以及应用系统有充分的了解,加上不断的实践,才能基本读懂statspack报告并且从报告中找到调整优化Oracle的途径。
下面接合一个实际的statspack报告,大致分析一下。

2.2.1 基本信息分析
DB Name         DB Id    Instance     Inst Num Release     OPS Host
------------ ----------- ------------ --------          ----------- ---      --------- ---
RES           2749170756 res                 1 8.1.7.0.0   NO res

                Snap Id     Snap Time      Sessions
                ------- ------------------ --------
Begin Snap:          2 26-Jul-03 16:37:08       38
   End Snap:          3 26-Jul-03 17:03:23       38
    Elapsed:                  26.25 (mins)

Statspack报告首先描述了数据库的基本情况,比如数据库名、实例名、实例个数、oracle版本号等等;然后是该报告的开始快照和结束快照的信息,包括 snap

id , snap time 等等;最后是该报告经过的时间跨度,单位是分钟(mins)。

Cache Sizes
~~~~~~~~~~~
db_block_buffers:      61440          log_buffer:     163840
db_block_size:         8192     shared_pool_size:   52428800

然后描述了Oracle内存结构中几个重要的参数。

2.2.2 内存信息分析
Load Profile
~~~~~~~~~~~~                       Per Second       Per Transaction
                                   ---------------       ---------------
              Redo size:              4,834.87             11,116.67
          Logical reads:                405.53                932.43
          Block changes:                 60.03                138.02
          Physical reads:                138.63                318.75
          Physical writes:                 54.27                124.79
          User calls:                     62.69                144.13
          Parses:                        19.14                 44.00
          Hard parses:                    2.26                  5.20
                  Sorts:                  1.83                  4.20
                 Logons:                  0.21                  0.47
               Executes:                 21.10                 48.50
            Transactions:                  0.43

% Blocks changed per Read:   14.80    Recursive Call %:   34.45
Rollback per transaction %:    0.00       Rows per Sort:   20.57

Redo size: 是日志的生成量,分为每秒和每事务所产生的,通常在很繁忙的系统中日志生成量可能达到上百k,甚至几百k;

Logical reads: 逻辑读实际上就是logical IO=buffer gets表示的含义,我们可以这样认为,block在内存中,我们每一次读一块内存,就相当于一次逻辑读;

Parses 和 Hard parses: Parse 和 hard parse通常是很容易出问题的部分,80%的系统的慢都是由于这个原因所导致的。
所谓parse分soft parse 和hard parse,soft parse是当一条sql传进来后,需要在shared pool中找是否有相同的sql,如果找到了,那就是soft parse,如果没

有找着,那就开始hard parse,实际上hard parse主要是检查该sql所涉及到的所有的对象是否有效以及权限等关系,hard parse之后才根据rule/cost模式生成

执行计划,再执行sql。
而hard parse的根源,基本都是由于不使用bind var所导致的,不使用bind var违背了oracle的shared pool的设计的原则,违背了这个设计用来共享的思想,这

样导致shared_pool_size里面命中率下降。因此不使用bind var,将导致cpu使用率的问题,极有使得性能急剧下降。
还有就是为了维护internal structure,需要使用latch,latch是一种Oracle低级结构,用于保护内存资源,是一种内部生命周期很短的lock,大量使用latch将

消耗大量的cpu资源。

Sorts: 表示排序的数量;

Executes: 表示执行次数;

Transactions: 表示事务数量;

Rollback per transaction %: 表示数据库中事务的回退率。如果不是因为业务本身的原因,通常应该小于10%为好,回退是一个很消耗资源的操作。


Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
           Buffer Nowait %: 100.00       Redo NoWait %:   99.98
           Buffer Hit   %:   65.82    In-memory Sort %:   99.65
           Library Hit   %:   91.32        Soft Parse %:   88.18
         Execute to Parse %:    9.28         Latch Hit %:   99.99
Parse CPU to Parse Elapsd %:   94.61     % Non-Parse CPU:   99.90

Buffer Hit %: 数据缓冲区命中率,通常应该大于90%;

Library Hit %: libaray cache的命中率,通常应该大于98%;

In-memory Sort %: 排序在内存的比例,如果这个比例过小,可以考虑增大sort_area_size,使得排序在内存中进行而不是在temp表空间中进行;

Soft Parse %: 软解析的百分比,这个百分比也应该很大才好,因为我们要尽量减少hard parse。 soft parse 百分比=soft/(soft+hard);

Execute to Parse %: 这个数字也应该是越大越好,接近100%最好。有些报告中这个值是负的,看上去很奇怪。事实上这表示一个问题,sql如果被age out的话

就可能出现这种情况,也就是sql老化,或执行alter system flush shared_pool等。


Shared Pool Statistics          Begin   End
                             ------   ------
         Memory Usage %:    90.63   87.19
   % SQL with executions>1:   71.53   75.39
% Memory for SQL w/exec>1: 59.45   65.17

% SQL with executions>1: 这个表示SQL被执行次数多于一次的比率,也应该大为好,小则表示很多sql只被执行了一次,说明没有使用bind var;

2.2.3 等待事件分析
接下来,statspack报告中描述的是等待事件(Wait Events),这是Oracle中比较复杂难懂的概念。
Oracle 的等待事件是衡量Oracle 运行状况的重要依据及指标。
等待事件的概念是在Oracle7.0.1.2 中引入的,大致有100 个等待事件。在Oracle 8.0 中这个数目增加到了大约150 个,在Oracle8i 中大约有200 个事件,在

Oracle9i 中大约有360 个等待事件。
主要有两种类别的等待事件,即空闲(idle)等待事件和非空闲(non-idle)等待事件。
空闲事件指Oracle 正等待某种工作,在诊断和优化数据库的时候,我们不用过多注意这部分事件。
常见的空闲事件有:
? dispatcher timer
? lock element cleanup
? Null event
? parallel query dequeue wait
? parallel query idle wait - Slaves
? pipe get
? PL/SQL lock timer
? pmon timer- pmon
? rdbms ipc message
? slave wait
? smon timer
? SQL*Net break/reset to client
? SQL*Net message from client
? SQL*Net message to client
? SQL*Net more data to client
? virtual circuit status
? client message

非空闲等待事件专门针对Oracle 的活动,指数据库任务或应用运行过程中发生的等待,这些等待事件是我们在调整数据库的时候应该关注与研究的。
一些常见的非空闲等待事件有:
? db file scattered read
? db file sequential read
? buffer busy waits
? free buffer waits
? enqueue
? latch free
? log file parallel write
? log file sync

下面接合statspack中的一些等待事件进行讲述。

Top 5 Wait Events
~~~~~~~~~~~~~~~~~                              Wait     % Total
Event                                    Waits Time (cs)   Wt Time
--------------------------------------------           ------------ ------------    -------
db file scattered read                      26,877       12,850   52.94
db file parallel write                         472        3,674   15.13
log file parallel write                         975        1,560    6.43
direct path write                           1,571        1,543    6.36
control file parallel write                     652        1,290    5.31
          -------------------------------------------------------------

db file scattered read: DB文件分散读取。这个等待事件很常见,经常在top5中出现,这表示,一次从磁盘读数据进来的时候读了多于一个block的数据,而这

些数据又被分散的放在不连续的内存块中,因为一次读进来的是多于一个block的。
通常来说我们可以认为是全表扫描类型的读,因为根据索引读表数据的话一次只读一个block,如果这个数字过大,就表明该表找不到索引,或者只能找到有限的

索引,可能是全表扫描过多,需要检查sql是否合理的利用了索引,或者是否需要建立合理的索引。
当全表扫描被限制在内存时,它们很少会进入连续的缓冲区内,而是分散于整个缓冲存储器中。尽管在特定条件下执行全表扫描可能比索引扫描更有效,但如果

出现这种等待时,最好检查一下这些全表扫描是否必要,是否可以通过建立合适的索引来减少对于大表全表扫描所产生的大规模数据读取。
对于经常使用的小表,应该尽量把他们pin 在内存中,避免不必要的老化清除及重复读取。

db file sequential read: DB文件连续读取。通常显示单个块的读取(通常指索引读取),表示的是读进磁盘的block被放在连续的内存块中。
事实上大部分基本代表着单个block的读入,可以说象征着 IO 或者说通过索引读入的比较多 。因为一次IO若读进多个的block,放入连续的内存块的几率是很小

的,分布在不同block的大量记录被读入就会遇到此事件。因为根据索引读数据的话,假设100条记录,根据索引,不算索引本身的读,而根据索引每个值去读一

下表数据,理论上最多可能产生100 buffer gets,而如果是full table scan,则100条数据完全可能在一个block里面,则几乎一次就读过这个block了,就会产

生这么大的差异。
这种等待的数目很多时,可能显示表的连接顺序不佳,或者不加选择地进行索引。
对于高级事务处理(high-transaction)、调整良好(welltuned)的系统,这一数值很大是很正常的,但在某些情况下,它可能暗示着系统中存在问题。
你应当将这一等待统计量与Statspack 报告中的已知问题(如效率较低的SQL)联系起来。检查索引扫描,以保证每个扫描都是必要的,并检查多表连接的连接顺

序。
DB_CACHE_SIZE 也是这些等待出现频率的决定因素。有问题的散列区域(Hash-area)连接应当出现在PGA 内存中,但它们也会消耗大量内存,从而在顺序读取时

导致大量等待。它们也可能以直接路径读/写等待的形式出现。

Free Buffer Wait: 释放缓冲区。
这种等待表明系统正在等待内存中的缓冲,因为内存中已经没有可用的缓冲空间了。如果所有SQL 都得到了调优,这种等待可能表示你需要增大DB_BUFFER_CACHE

。释放缓冲区等待也可能表示不加选择的SQL 导致数据溢出了带有索引块的缓冲存储器,没有为等待系统处理的特定语句留有缓冲区。
这种情况通常表示正在执行相当多数量的DML(插入/更新/删除),并且可能说明DBWR 写的速度不够快,缓冲存储器可能充满了相同缓冲器的多个版本,从而

导致效率非常低。为了解决这个问题,可能需要考虑增加检查点、利用更多的DBWR 进程,或者增加物理磁盘的数量。

Buffer Busy Wait: 缓冲区忙。
该等待事件表示正在等待一个以unshareable方式使用的缓冲区,或者表示当前正在被读入buffer cache。也就是当进程想获取或者操作某个block的时候却发现

被别的进程在使用而出现等待。一般来说Buffer Busy Wait不应大于1%。
检查缓冲等待统计部分(或V$WAITSTAT),看一下等待是否位于段头。如果是,可以考虑增加自由列表(freelist,对于Oracle8i DMT)或者增加freelist

groups.
其修改语法为:
SQL> alter table sp_item storage (freelists 2);
Table altered。

对于Oracle8i而言,增加freelist参数,在很多时候可以明显缓解等待,如果使用LMT,也就是Local Manangement Tablespace,区段的管理就相对简单还可以考

虑修改数据块的pctused\pctfree值,比如增大pctfree可以扩大数据的分布,在某种程度上就可以减少热点块的竞争。

如果这一等待位于undo header,可以通过增加回滚段(rollback segment)来解决缓冲区的问题。
如果等待位于undo block上,我们可能需要检查相关应用,适当减少大规模的一致性读取,或者降低一致性读取(consistent read)的表中的数据密度或者增大

DB_CACHE_SIZE。
如果等待处于data block,可以考虑将频繁并发访问的表或数据移到另一数据块或者进行更大范围的分布(可以增加pctfree 值 ,扩大数据分布,减少竞争),

以避开这个"热点"数据块,或者可以考虑增加表中的自由列表或使用本地化管理的表空间(Locally Managed Tablespaces)。
如果等待处于索引块,应该考虑重建索引、分割索引或使用反向键索引。反向键索引在很多情况下,可以极大地缓解竞争,其原理有点类似于hash分区的功效。

反向键索引(reverse key index)常建在一些值是连续增长的列上,例如列中的值是由sequence产生的。

为了防止与数据块相关的缓冲忙等待,也可以使用较小的块:在这种情况下,单个块中的记录就较少,所以这个块就不是那么"繁忙";或者可以设置更大的

pctfree,使数据扩大物理分布,减少记录间的热点竞争。
在执行DML (insert/update/ delete)时,Oracle向数据块中写入信息,对于多事务并发访问的数据表,关于ITL的竞争和等待可能出现,为了减少这个等待,可

以增加initrans,使用多个ITL槽。
以下是一个生产系统v$waitstat 试图所显示的等待信息:
SQL> select * from v$waitstat where count<>0 or time <>0;
CLASS      COUNT TIME
------------------ ---------- ----------
data block       453   6686
undo header      391   1126
undo block       172      3

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-616753/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13204329