Shared Pool Statistics        Begin    End

                              ------  ------

             Memory Usage %:   84.64   79.67

    % SQL with executions>1:   93.77   24.69

  % Memory for SQL w/exec>1:   85.36   34.8

该环节提供一个大致的SQL重用及shared pool内存使用的评估。 应用是否共享SQL? 有多少内存是给只运行一次的SQL占掉的,对比共享SQL呢？

如果该环节中% SQL with executions>1的 比例 小于%90 , 考虑用下面链接的SQL去抓 硬编码的非绑定变量SQL语句。

Memory Usage %                        : (shared pool 的实时大小- shared pool free memory)/ shared pool 的实时大小, 代表shared pool的空间使用率,虽然有使用率但没有标明碎片程度

% SQL with executions>1          : 复用的SQL占总的SQL语句的比率,数据来源 DBA_HIST_SQL_SUMMARY 的 SINGLE_USE_SQL和TOTAL_SQL：1 – SINGLE_USE_SQL / TOTAL_SQL

% Memory for SQL w/exec>1  :执行2次以上的SQL所占内存占总的SQL内存的比率,数据来源DBA_HIST_SQL_SUMMARY 的SINGLE_USE_SQL_MEM和TOTAL_SQL_MEM：1 – SINGLE_USE_SQL_MEM / TOTAL_SQL_MEM

==》上面2个指标也可以用来大致了解shared pool中的内存碎片程序,因为SINGLE_USE_SQL 单次执行的SQL多的话,那么显然可能有较多的共享池内存碎片

如果memory usage%比率一直很高,则可以关注下后面sga breakdown中的shared pool free memory大小,一般推荐至少让free memroy有个300~500MB 以避免隐患。

基于Wait Interface的调优是目前的主流！每个指标都重要！

基于命中比例的调优,好比是统计局的报告, 张财主家财产100万,李木匠家财产1万, 平均财产50.5万。

基于等待事件的调优,好比马路上100辆汽车的行驶记录表,上车用了几分钟,红灯等了几分钟,拥堵塞了几分钟。。。

Waits : 该等待事件发生的次数,对于DB CPU此项不可用

Times : 该等待事件消耗的总计时间,单位为秒, 对于DB CPU 而言是前台进程所消耗CPU时间片的总和,但不包括Wait on CPU QUEUE

Avg Wait(ms)        : 该等待事件平均等待的时间,实际就是  Times/Waits, 单位ms, 对于DB CPU此项不可用

% Total Call Time  : 该等待事件占总的call time的比率

% Total Call Time  =  time for each timed event / total call time

total call time    =  total CPU time + total wait time for non-idle events

Wait Class: 等待类型：

Concurrency,System I/O,User I/O,Administrative,Other,Configuration,Scheduler,Cluster,Application,Idle,Network,Commit

Operating System Statistics         Snaps: 70719-70723

TIME statistic values are diffed.

   All others display actual values.  End Value is displayed if different

-> ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name

数据来源于V$OSSTAT  / DBA_HIST_OSSTAT,,  TIME相关的指标单位均为百分之一秒

统计项         描述

NUM_CPU_SOCKETS       物理CPU的数目

NUM_CPU_CORES           物理CPU的核数

NUM_CPUS                       逻辑CPU的数目

SYS_TIME                       在内核态被消耗掉的CPU时间片,单位为百分之一秒

USER_TIME                     在用户态被消耗掉的CPU时间片,单位为百分之一秒

BUSY_TIME                     Busy_Time=SYS_TIME+USER_TIME 消耗的CPU时间片,单位为百分之一秒

IDLE_TIME                     空闲的CPU时间片,单位为百分之一秒

所有CPU所能提供总的时间片         BUSY_TIME + IDLE_TIME = ELAPSED_TIME * CPU_COUNT

OS_CPU_WAIT_TIME               进程等OS调度的时间,CPU queuing

VM_IN_BYTES                   换入页的字节数

VM_OUT_BYTES           换出页的字节数,部分版本下并不准确,例如Bug 11712010 Abstract: VIRTUAL MEMORY PAGING ON 11.2.0.2 DATABASES,仅供参考

IOWAIT_TIME                   所有CPU花费在等待I/O完成上的时间  单位为百分之一秒

RSRC_MGR_CPU_WAIT_TIME         是指当resource manager控制CPU调度时,需要控制对应进程暂时不使用CPU而进程到内部运行队列中,以保证该进程对应的consumer group(消费组)没有消耗比指定resource manager指令更多的CPU。RSRC_MGR_CPU_WAIT_TIME指等在内部运行队列上的时间,在等待时不消耗CPU

AVG_BUSY_TIME= BUSY_TIME/NUM_CPUS

“Load Average”  begin/end值代表每个CPU的大致运行队列大小。上例中快照开始到结束,平均 CPU负载增加了 12.84-8.41 =  4.43   与《2-5 Operating System Statistics》中的LOAD相呼应。

%User+%System=> 总的CPU使用率,在这里是31.8%

Busy Time=Elapsed Time * NUM_CPUS * CPU utilization= 60.23 (mins)  * 24 * 31.8% = 459.67536 mins

%Total CPU,该实例所使用的CPU占总CPU的比例  % of total CPU for Instance

%Busy CPU,该实例所使用的CPU占总的被使用CPU的比例  % of busy CPU for Instance

例如共4个逻辑CPU,其中3个被完全使用,3个中的1个完全被该实例使用,则%Total CPU= 1/4= 25%,而%Busy CPU= 1/3= 33%

当CPU高时一般看%Busy CPU可以确定CPU到底是否是本实例消耗的,还是主机上其他程序

计算公式：

% of busy CPU for Instance= （DB CPU+ background CPU time) / (BUSY_TIME /100)= (20,649.1  + 1,980.9)/ (2,894,855 /100)= 78.17%

% of Total CPU for Instance = ( DB CPU+ background CPU time)/( BUSY_TIME+IDLE_TIME/100) = (20,649.1  + 1,980.9)/ ((2,894,855+5,568,240) /100) = 26.73%

%DB time waiting for CPU (Resource Manager)= (RSRC_MGR_CPU_WAIT_TIME/100)/DB TIME

SQL ordered by CPU Time             Snaps: 70719-70723

-> Resources reported for PL/SQL code includes the resources used by all SQL

   statements called by the code.

-> %Total - CPU Time      as a percentage of Total DB CPU

-> %CPU   - CPU Time      as a percentage of Elapsed Time

-> %IO    - User I/O Time as a percentage of Elapsed Time

-> Captured SQL account for   34.9% of Total CPU Time (s):          20,649

-> Captured PL/SQL account for    0.5% of Total CPU Time (s):          20,649

Module: MZIndexer

SELECT t0.BOOLEAN_VALUE, t0.CLASS_CODE, t0.CREATED, t0.END_DATE, t0.PRODUCT_ATTR

IBUTE_ID, t0.LAST_MODIFIED, t0.OVERRIDE_FLAG, t0.PRICE, t0.PRODUCT_ATTRIBUTE_TYP

E_ID, t0.PRODUCT_ID, t0.PRODUCT_PUB_RELEASE_TYPE_ID, t0.PRODUCT_VOD_TYPE_ID, t0.

SAP_PRODUCT_ID, t0.START_DATE, t0.STRING_VALUE FROM mz_product_attribute t0 WHER

CPU TIME         : 该SQL 在快照时间内累计执行所消耗的CPU 时间片,单位为s

Executions       : 该SQL在快照时间内累计执行的次数

CPU per Exec (s) ：该SQL 平均单次执行所消耗的CPU时间 ,  即  ( SQL CPU TIME / SQL Executions )

%Total           : 该SQL 累计消耗的CPU时间 占  该时段总的 DB CPU的比例,  即 ( SQL CPU TIME /  Total DB CPU)

%CPU             : 该SQL 所消耗的CPU 时间 占 该SQL消耗的时间里的比例, 即 (SQL CPU Time / SQL Elapsed Time) ,该指标说明了该语句是否是CPU敏感的

%IO              : 该SQL 所消耗的I/O 时间 占 该SQL消耗的时间里的比例, 即(SQL I/O Time/SQL Elapsed Time) ,该指标说明了该语句是否是I/O敏感的

SQL ordered by Gets               DB/Inst: ITSCMP/itscmp2  Snaps: 70719-70723

-> Resources reported for PL/SQL code includes the resources used by all SQL

   statements called by the code.

-> %Total - Buffer Gets   as a percentage of Total Buffer Gets

-> %CPU   - CPU Time      as a percentage of Elapsed Time

-> %IO    - User I/O Time as a percentage of Elapsed Time

-> Total Buffer Gets:   2,021,476,421

-> Captured SQL account for   68.2% of Total

注意 buffer gets 逻辑读是消耗CPU TIME的重要源泉, 但并不是说消耗CPU TIME的只有buffer gets。 大多数情况下 SQL order by CPU TIME 和 SQL order by buffers gets 2个部分的TOP SQL 及其排列顺序都是一样的,此种情况说明消耗最多buffer gets的 就是消耗最多CPU 的SQL ,如果我们希望降低系统的CPU使用率,那么只需要调优SQL 降低buffer gets 即可。

但也并不是100%的情况都是如此, CPU TIME的消耗者 还包括 函数运算、PL/SQL 控制、Latch /Mutex 的Spin等等, 所以SQL order by CPU TIME 和 SQL order by buffers gets 2个部分的TOP SQL 完全不一样也是有可能的, 需要因地制宜来探究到底是什么问题导致的High CPU,进而裁度解决之道。

Buffer Gets    : 该SQL在快照时间内累计运行所消耗的buffer gets,包括了consistent read 和 current read

Executions     : 该SQL在快照时间内累计执行的次数

Gets  per Exec : 该SQL平均单次的buffer gets , 对于事务型transaction操作而言 一般该单次buffer gets小于2000

% Total  该SQL : 累计运行所消耗的buffer gets占 总的db buffer gets的比率, (SQL buffer gets / DB total buffer gets)

SQL ordered by Executions         Snaps: 70719-70723

-> %CPU   - CPU Time      as a percentage of Elapsed Time

-> %IO    - User I/O Time as a percentage of Elapsed Time

-> Total Executions:      48,078,147

-> Captured SQL account for   50.4% of Total

按照 执行次数来排序的话,也是性能报告对比时一个重要的参考因素,因为如果TOP SQL的执行次数有明显的增长,那么 性能问题的出现也是意料之中的事情了。 当然执行次数最多的,未必便是对性能影响最大的TOP SQL

Executions     : 该SQL在快照时间内累计执行的次数

Rows Processed ：该SQL在快照时间内累计执行所处理的总行数

Rows per Exec：：SQL平均单次执行所处理的行数,  这个指标在诊断一些 数据问题造成的SQL性能问题时很有用

SQL ordered by Parse Calls          Snaps: 70719-70723

-> Total Parse Calls:       2,160,124

-> Captured SQL account for   58.3% of Total

Parse Calls   : 解析调用次数, 与上文的 Load Profile中的Parse 数一样 包括 软解析soft parse和硬解析hard parse

Executions    : 该SQL在快照时间内累计执行的次数

%Total Parses : 本SQL 解析调用次数 占 该时段数据库总解析次数的比率, 为 (SQL Parse Calls / Total DB Parse Calls)

Version Count  Oracle中的执行计划可以是多版本的,即对于同一个SQL语句有多个不同版本的执行计划,这些执行计划又称作子游标, 而一个SQL语句的文本可以称作一个父游标。 一个父游标对应多个子游标,产生不同子游标的原因是 SQL在被执行时无法共享之前已经生成的子游标, 原因是多种多样的,例如 在本session中做了一个优化器参数的修改 例如optimizer_index_cost_adj 从100 修改到99,则本session的优化环境optimizer env将不同于之前的子游标生成环境,这样就需要生成一个新的子游标,例如：

SQL> create table emp as select * from scott.emp;

SQL> select * from emp where empno=1;

no rows selected

SQL> select /*+ MACLEAN */ * from emp where empno=1;

no rows selected

SQL> select SQL_ID,version_count from V$SQLAREA WHERE SQL_TEXT like ‘%MACLEAN%‘ and SQL_TEXT not like ‘%like%‘;

SQL_ID        VERSION_COUNT

------------- -------------

bxnnm7z1qmg26             1

SQL> select count(*) from v$SQL where SQL_ID=‘bxnnm7z1qmg26‘;

  COUNT(*)

----------

         1

SQL> alter session set optimizer_index_cost_adj=99;

Session altered.

SQL> select /*+ MACLEAN */ * from emp where empno=1;

no rows selected

SQL> select SQL_ID,version_count from V$SQLAREA WHERE SQL_TEXT like ‘%MACLEAN%‘ and SQL_TEXT not like ‘%like%‘;

SQL_ID        VERSION_COUNT

------------- -------------

bxnnm7z1qmg26             2

SQL> select count(*) from v$SQL where SQL_ID=‘bxnnm7z1qmg26‘;

  COUNT(*)

----------

         2

SQL> select child_number ,OPTIMIZER_ENV_HASH_VALUE,PLAN_HASH_VALUE from v$SQL where SQL_ID=‘bxnnm7z1qmg26‘;

CHILD_NUMBER OPTIMIZER_ENV_HASH_VALUE PLAN_HASH_VALUE

------------ ------------------------ ---------------

           0               3704128740      3956160932

           1               3636478958      3956160932

可以看到上述演示中修改optimizer_index_cost_adj=99 导致CBO 优化器的优化环境发生变化, 表现为不同的OPTIMIZER_ENV_HASH_VALUE,之后生成了2个子游标,但是这2个子游标的PLAN_HASH_VALUE同为3956160932,则说明了虽然是不同的子游标但实际子游标里包含了的执行计划是一样的；  所以请注意 任何一个优化环境的变化 (V$SQL_SHARED_CURSOR)以及相关衍生的BUG 都可能导致子游标无法共享,虽然子游标无法共享但这些子游标扔可能包含完全一样的执行计划,这往往是一种浪费。

注意V$SQLAREA.VERSION_COUNT 未必等于select count(*) FROM V$SQL WHERE SQL_ID=”  ,即 V$SQLAREA.VERSION_COUNT 显示的子游标数目 未必等于当前实例中还存有的子游标数目, 由于shared pool aged out算法和其他一些可能导致游标失效的原因存在,所以子游标被清理掉是很常见的事情。 V$SQLAREA.VERSION_COUNT只是一个计数器,它告诉我们曾经生成了多少个child cursor,但不保证这些child 都还在shared pool里面。

此外可以通过v$SQL的child_number字段来分析该问题,如果child_number存在跳号则也说明了部分child被清理了。

子游标过多的影响, 当子游标过多(例如超过3000个时),进程需要去扫描长长的子游标列表child cursor list以找到一个合适的子游标child cursor,进而导致cursor sharing 性能问题 现大量的Cursor: Mutex S 和 library cache lock等待事件。

Executions : 该SQL在快照时间内累计执行的次数

Hash Value : 共享SQL 的哈希值

Tablespace IO Stats               DB/Inst: ITSCMP/itscmp2  Snaps: 70719-70723

-> ordered by IOs (Reads + Writes) desc

reads        : 指 该表空间上发生的物理读的次数(单位不是块，而是次数)

Av Reads/s   : 指该表空间上平均每秒的物理读次数 (单位不是块，而是次数)

Av Rd(ms)    : 指该表空间上每次读的平均读取延迟

Av Blks/Rd   : 指该表空间上平均每次读取的块数目，因为一次物理读可以读多个数据块；如果Av Blks/Rd>>1 则可能系统有较多db file scattered read 可能是诊断FULL TABLE SCAN或FAST FULL INDEX SCAN，需要关注table scans (long tables) 和index fast full scans (full)   2个指标

Writes       : 该表空间上发生的物理写的次数 ;  对于那些Writes总是等于0的表空间 不妨了解下是否数据为只读，如果是可以通过read only tablespace来解决 RAC中的一些性能问题。

Av Writes/s  : 指该表空间上平均每秒的物理写次数

buffer Waits :  该表空间上发生buffer busy waits和read by other session的次数( 9i中buffer busy waits包含了read by other session)。

Av Buf Wt(ms):  该表空间上发生buffer Waits的平均等待时间，单位为ms

File IO Stats                    Snaps: 70719-70723

-> ordered by Tablespace, File

Tablespace    : 表空间名

FileName      : 数据文件的路径

Reads         : 该数据文件上累计发生过的物理读次数，不是块数

Av Reads/s    : 该数据文件上平均每秒发生过的物理读次数，不是块数

Av Rd(ms)     : 该数据文件上平均每次物理读取的延迟，单位为ms

Av Blks/Rd    : 该数据文件上平均每次读取涉及到的块数，OLTP环境该值接近 1

Writes        : 该数据文件上累计发生过的物理写次数，不是块数

Av Writes/s   : 该数据文件上平均每秒发生过的物理写次数，不是块数

buffer Waits  : 该数据文件上发生buffer busy waits和read by other session的次数( 9i中buffer busy waits包含了read by other session)。

Av Buf Wt(ms) : 该数据文件上发生buffer Waits的平均等待时间，单位为ms

若某个表空间上有较高的IO负载，则有必要分析一下 是否其所属的数据文件上的IO 较为均匀 还是存在倾斜， 是否需要结合存储特征来 将数据均衡分布到不同磁盘上的数据文件上，以优化 I/O

Latch Activity        Snaps: 70719-70723

-> "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

对于高并发的latch例如cache buffers chains,其Pct Misses应当十分接近于0

一般的调优原则：

如果latch : cache buffers chains是 Top 5 事件,则需要考虑优化SQL减少 全表扫描 并减少Top buffer gets SQL语句的逻辑读

如果latch : redo copy 、redo allocation 等待较多,则可以考虑增大LOG_BUFFER

如果latch : library cache 发生较多,则考虑增大shared_pool_size

Segments by Logical Reads         DB/Inst: MAC/MAC2  Snaps: 70719-70723

-> Total Logical Reads:   2,021,476,421

-> Captured Segments account for   83.7% of Total

owner          : 数据段的所有者

Tablespace Name: 数据段所在表空间名

Object Name    : 对象名

Subobject Name ：子对象名,例如一个分区表的某个分区

obj Type       : 对象类型 一般为TABLE /INDEX  或者分区或子分区

Logical Reads  ：该数据段上发生过的逻辑读 , 单位为 块数*次数

%Total         : 占总的逻辑读的百分比 ,   (当前对象上发生过的逻辑读/ Total DB 逻辑读)

Segments by Physical Reads         DB/Inst: MAC/MAC2  Snaps: 70719-70723

-> Total Physical Reads:      56,839,035

-> Captured Segments account for   51.9% of Total

Physical Reads : 该数据段上发生过的物理读 , 单位为 块数*次数

%Total         : 占总的物理读的百分比 ,   (当前对象上发生过的逻辑读/ Total DB 逻辑读)

Segments by Row Lock Waits        DB/Inst: MAC/MAC2  Snaps: 70719-70723

-> % of Capture shows % of row lock waits for each top segment compared

-> with total row lock waits for all segments captured by the Snapshot

Row  Lock Waits :是指行锁的等待次数  数据来源于 dba_hist_seg_stat.ROW_LOCK_WAITS_DELTA

Segments by Buffer Busy Waits      DB/Inst: MAC/MAC2  Snaps: 70719-70723

-> % of Capture shows % of Buffer Busy Waits for each top segment compared

-> with total Buffer Busy Waits for all segments captured by the Snapshot

Buffer Busy Waits : 该数据段上发生 buffer busy wait的次数   数据来源 dba_hist_seg_stat.buffer_busy_waits_delta