oralce 性能调试

V$SESSION,V$SESSTAT,V$SESSION_EVENT,V$SESSION_LONGOPS,

V$SESSION_WAIT,V$SESS_IO,V$SESSION_CONNECT_INFO

select--会话信息

      a.SADDR,  --session's address

      a.SID,    --session id

      a.SERIAL#,--when sesseion reuse,the serial + 1

      a.TYPE,   --the type of session

      a.PADDR,  --process address

      a.PROCESS,--process id of client

      a.SQL_ADDRESS,      --the address of the session's sql

      a.SQL_HASH_VALUE,   --the hash_value of the session's sql

      a.SQL_ID,           --the id of the session's sql

      a.EVENT#,           --the event number

      a.EVENT,            --the describe of the event number

      a.WAIT_CLASS#,      --wait class number

      a.WAIT_CLASS,       --wait class describe

      a.WAIT_TIME,         --wait time

      a.USER#,             --the use id

      a.USERNAME,          --use name

      a.OSUSER             --the operation system user name

 from v$session a where rownum = 1 ;

 

可以发现这些session相关的表中最核心的就是session表，这些表都有sid可以互相关联，分别存放着session的相关属性或者统计信息。例如：

SELECT a.PADDR,

      a.SQL_ADDRESS,

      a.SQL_HASH_VALUE,

      a.SQL_ID,

      b.BLOCK_GETS,

      b.CONSISTENT_GETS,

      b.PHYSICAL_READS,

      c.AUTHENTICATION_TYPE,

      c.OSUSER,

      d.EVENT_ID,

      d.EVENT,

      d.TOTAL_WAITS,

      d.TIME_WAITED,

      d.WAIT_CLASS#,

      d.WAIT_CLASS

 from v$session             a,

      v$sess_io             b,

      v$session_connect_info c,

      v$session_event       d

 where a.SID = (select sid from v$mystat where rownum = 1)

  and a.SID = b.SID

  and a.SID = c.sid

  and d.SID = a.SID

;

 

 

 

系统状态相关视图：

v$statname和v$mystat, v$sysstat

select a.STATISTIC#,a.NAME,a.CLASS,a.STAT_ID from V$STATNAME a ;

其中的class代表意义：

1代表事例活动

2代表Redo buffer活动

4代表锁

8代表数据缓冲活动

16代表OS活动

32代表并行活动

64代表表访问

128代表调试信息

 

下面的语句可以查询当前session的状态

select * from

(select b.name,a.SID,a.VALUE from v$mystat a ,v$sysstat b

where a.STATISTIC# = b.STATISTIC# order by value desc )

where rownum < 30;

或者各session的各项值：

select * from

(select b.name,a.SID,a.VALUE from v$sesstat a ,v$sysstat b

where a.STATISTIC# = b.STATISTIC# order by value desc ) ;

 

列出常见的name值和解释：

CPU used by this session：

所有session的cpu占用量，不包括后台进程。这项统计的单位是百分之x秒.完全调用一次不超过10ms

 

db block changes：

那部分造成SGA中数据块变化的insert,update或delete操作数 这项统计可以大概看出整体数据库状态。

在各项事务级别，这项统计指出脏缓存比率。

 

execute count：

执行的sql语句数量(包括递归sql)

 

logons current：

当前连接到实例的Sessions。如果当前有两个快照则取平均值。

 

logons cumulative：

自实例启动后的总登陆次数。

 

parse count (hard)：

在shared pool中解析调用的未命中次数。

当sql语句执行并且该语句不在shared pool或虽然在shared pool但因为两者存在部分差异而不能被使用时产生硬解析。

如果一条sql语句原文与当前存在的相同，但查询表不同则认为它们是两条不同语句，则硬解析即会发生。

硬解析会带来cpu和资源使用的高昂开销，因为它需要oracle在shared pool中重新分配内存，然后再确定执行计划，最终语句才会被执行。

 

parse count (total)：

解析调用总数，包括软解析和硬解析。当session执行了一条sql语句，该语句已经存在于shared pool并且可以被使用则产生软解析。

当语句被使用(即共享)所有数据相关的现有sql语句(如最优化的执行计划)必须同样适用于当前的声明。这两项统计可被用于计算软解析命中率。

 

parse time cpu：

总cpu解析时间(单位：10ms)。包括硬解析和软解析。

 

parse time elapsed：

完成解析调用的总时间花费。

 

physical reads：

OS blocks read数。包括插入到SGA缓存区的物理读以及PGA中的直读这项统计并非i/o请求数。

 

physical writes：

从SGA缓存区被DBWR写到磁盘的数据块以及PGA进程直写的数据块数量。

 

redo log space requests：

在redo logs中服务进程的等待空间，表示需要更长时间的log switch。

 

redo size：redo

发生的总次数(以及因此写入log buffer)，以byte为单位。这项统计显示出update活跃性。

 

session logical reads：

逻辑读请求数。

 

sorts (memory) and sorts (disk)：sorts(memory)

是适于在SORT_AREA_SIZE(因此不需要在磁盘进行排序)的排序操作的数量。sorts(disk)则是由于排序所需空间太大，SORT_AREA_SIZE不能满足而不得不在磁盘进行排序操作的数量。这两项统计通常用于计算in-memory sort ratio。

 

sorts (rows):

列排序总数。这项统计可被'sorts (total)'统计项除尽以确定每次排序的列。该项可指出数据卷和应用特征。

 

table fetch by rowid：

使用ROWID返回的总列数(由于索引访问或sql语句中使用了'where rowid=&rowid'而产生)

 

table scans (rows gotten)：

全表扫描中读取的总列数

 

table scans (blocks gotten)：

全表扫描中读取的总块数，不包括那些split的列。

 

 

 

可以查看v$sysmetric中的awr信息，获得一些比率的计算方法：

SQL> select a.metric_name,a.METRIC_UNIT

 2 from v$sysmetric a where a.METRIC_UNIT like '%!%%' escape '!';

 

METRIC_NAME                                                     METRIC_UNIT

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

Buffer Cache Hit Ratio                                          % (LogRead - PhyRead)/LogRead

Memory Sorts Ratio                                             % MemSort/(MemSort + DiskSort)

Redo Allocation Hit Ratio                                       % (#Redo - RedoSpaceReq)/#Redo

User Commits Percentage                                         % (UserCommit/TotalUserTxn)

User Rollbacks Percentage                                      % (UserRollback/TotalUserTxn)

Cursor Cache Hit Ratio                                          % CursorCacheHit/SoftParse

Execute Without Parse Ratio                                     % (ExecWOParse/TotalExec)

Soft Parse Ratio                                                % SoftParses/TotalParses

User Calls Ratio                                                % UserCalls/AllCalls

Host CPU Utilization (%)                                        % Busy/(Idle+Busy)

User Limit %                                                    % Sessions/License_Limit

Database Wait Time Ratio                                        % Wait/DB_Time

Database CPU Time Ratio                                         % Cpu/DB_Time

Row Cache Hit Ratio                                             % Hits/Gets

Row Cache Miss Ratio                                            % Misses/Gets

Library Cache Hit Ratio                                         % Hits/Pins

Library Cache Miss Ratio                                        % Misses/Gets

Shared Pool Free %                                              % Free/Total

PGA Cache Hit %                                                 % Bytes/TotalBytes

Process Limit %                                                 % Processes/Limit

Session Limit %                                                 % Sessions/Limit

Streams Pool Usage Percentage                          % Memory allocated / Size of Streams pool

Buffer Cache Hit Ratio                                          % (LogRead - PhyRead)/LogRead

Memory Sorts Ratio                                             % MemSort/(MemSort + DiskSort)

Execute Without Parse Ratio                                     % (ExecWOParse/TotalExec)

Soft Parse Ratio                                                % SoftParses/TotalParses

Host CPU Utilization (%)                                        % Busy/(Idle+Busy)

Database CPU Time Ratio                                         % Cpu/DB_Time

Library Cache Hit Ratio                                         % Hits/Pins

Shared Pool Free %                                              % Free/Total

 

30 rows selected.

 

SQL>

 

有了这些公式，就可以按照自己的定义来查看一些比率来帮助诊断性能问题（下列计算方法均为推测或参考网络上的一些方法）

Buffer Cache Hit Ratio                                          % (LogRead - PhyRead)/LogRead

Buffer cache命中率=（所有的逻辑读-所有物理读）/所有的逻辑读

select 1 - ((a.value - b.value - c.value) / d.value)

 from v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d

 where a.name = 'physical reads'

  and b.name = 'physical reads direct'

  and c.name = 'physical reads direct (lob)'

  and d.name = 'session logical reads';

 

Memory Sorts Ratio                                             % MemSort/(MemSort + DiskSort)

内存中完成的排序所占比例。最理想状态下，在OLTP系统中，大部分排序不仅小并且能够完全在内存里完成排序。

select a.value/(b.value+c.value)

 from v$sysstat a,v$sysstat b,v$sysstat c

 where a.name='sorts (memory)' and

        b.name='sorts (memory)' and c.name='sorts (disk)';

 

Redo Allocation Hit Ratio                                      % (#Redo - RedoSpaceReq)/#Redo

select (b.VALUE - a.VALUE) / b.VALUE

 from v$sysstat a, v$sysstat b

 where a.name = 'redo log space requests'

  and b.NAME = 'redo size';

 

User Commits Percentage                                         % (UserCommit/TotalUserTxn)

User Rollbacks Percentage                                      % (UserRollback/TotalUserTxn)

TotalUserTxn为系统事务起用次数。当需要计算其它统计中每项事务比率时该项可以被做为除数。例如，计算事务中逻辑读，可以使用下列公式：session logical reads / (user commits + user rollbacks)。

select e.VALUE/(b.VALUE + a.VALUE + c.value + d.value)

 from v$sysstat a, v$sysstat b,v$sysstat c,v$sysstat d,v$sysstat e

 where a.name = 'user commits'

  and b.NAME = 'user rollbacks'

  and c.name = 'user calls'

  and d.name = 'user I/O wait time'

  and e.name = 'session logical reads'

  ;

或者当前session的事务逻辑读数：

select e.VALUE/(b.VALUE + a.VALUE + c.value + d.value)

 from v$mystat a, v$mystat b,v$mystat c,v$mystat d,v$mystat e,

      v$statname aa,v$statname bb,v$statname cc,v$statname dd,v$statname ee

 where aa.name = 'user commits'

  and bb.NAME = 'user rollbacks'

  and cc.name = 'user calls'

  and dd.name = 'user I/O wait time'

  and ee.name = 'session logical reads'

  and a.STATISTIC# = aa.STATISTIC#

  and b.STATISTIC# = bb.STATISTIC#

  and c.STATISTIC# = cc.STATISTIC#

  and d.STATISTIC# = dd.STATISTIC#

  and e.STATISTIC# = ee.STATISTIC#

  ;

 

 

而User commits和rollbacks的百分比可以由下面的sql显示

select a.VALUE/(b.VALUE + a.VALUE + c.value + d.value)

 from v$sysstat a, v$sysstat b,v$sysstat c,v$sysstat d

 where a.name = 'user commits'

  and b.NAME = 'user rollbacks'

  and c.name = 'user calls'

  and d.name = 'user I/O wait time';

 

select b.VALUE/(b.VALUE + a.VALUE + c.value + d.value)

 from v$sysstat a, v$sysstat b,v$sysstat c,v$sysstat d

 where a.name = 'user commits'

  and b.NAME = 'user rollbacks'

  and c.name = 'user calls'

  and d.name = 'user I/O wait time';

 

Cursor Cache Hit Ratio                                          % CursorCacheHit/SoftParse

当前session下的cursor cache hit ratio计算

select cc.value / (aa.value - bb.value)

 from (select b.STATISTIC#, a.SID, b.NAME, a.VALUE

         from v$mystat a, v$statname b

        where a.STATISTIC# = b.STATISTIC#

          and b.NAME = 'parse count (total)') aa,

      (select b.STATISTIC#, a.SID, b.NAME, a.VALUE

         from v$mystat a, v$statname b

        where a.STATISTIC# = b.STATISTIC#

          and b.NAME = 'parse count (hard)') bb,

      (select b.STATISTIC#, a.SID, b.NAME, a.VALUE

         from v$mystat a, v$statname b

        where a.STATISTIC# = b.STATISTIC#

          and b.NAME like 'session cursor cache hits') cc;

 

当这个比率非常低的时候，可能意味着当前的session_cached_cursors设置的过低，需要稍稍调大这个值。

当sql解析的时候，如果当前session已经有缓存了的cursor，则可以直接使用对应的cursor，这个过程也叫做softer soft parse，显然适当的softer soft parse，使得session下的游标能够复用是可以提高一定的性能的。

SQL> show parameter session_cached_cursors

 

NAME                                TYPE       VALUE

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

session_cached_cursors              integer    20

SQL>

 

Execute Without Parse Ratio                                     % (ExecWOParse/TotalExec)

未解析的执行的比率

select 1-b.VALUE / a.VALUE

 from v$sysstat a, v$sysstat b

 where a.name = 'execute count'

  and b.name = 'parse count (total)';

 

Soft Parse Ratio                                                % SoftParses/TotalParses

这项将显示系统是否有太多硬解析。该值将会与原始统计数据对比以确保精确。例如，软解析率仅为0.2则表示硬解析率太高。不过，如果总解析量(parse count total)偏低，这项值可以被忽略。

公式：1 - ( parse count (hard) / parse count (total) )

执行：

select 1-(a.value/b.value)

 from v$sysstat a,v$sysstat b

 Where a.name='parse count (hard)' and b.name='parse count (total)';

 

User Calls Ratio                                                % UserCalls/AllCalls

select a.value / (a.value + b.value)

 from v$sysstat a, v$sysstat b

 where a.name = 'user calls'

  and b.name = 'recursive calls';

 

Host CPU Utilization (%)                                        % Busy/(Idle+Busy)

select b.VALUE / (a.value + b.value)

 from V$OSSTAT a, V$OSSTAT b

 where a.STAT_NAME = 'IDLE_TIME'

  and b.STAT_NAME = 'BUSY_TIME';

 

Row Cache Hit Ratio                                             % Hits/Gets

SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE HIT RATIO"

 FROM V$ROWCACHE;

 

User Limit %                                                    % Sessions/License_Limit

 

Database Wait Time Ratio                                        % Wait/DB_Time

DB time即为

Select * fromfrom v$sysstat a where a.NAME = 'DB time';

（注：Wait待解析oracle wait interface后再来看。）

 

Database CPU Time Ratio                                         % Cpu/DB_Time

 

Library Cache Hit Ratio                                         % Hits/Pins

Pins - The number of times a pin was requested for objects of this namespace.

Reloads - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

A pin hits发生在当要执行SQL或者PL/SQL的时候，他已经在library cache中，并且可以被执行。

Library cache的命中率，往往意味着share pool的大小是否合适，或者sql解析是否存在问题（结合soft parse ratio来看，如果2者都较低，很可能就是sql解析有问题）。

selectsum(PINS) Pins,

       sum(RELOADS) Reloads,

       round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio

from  v$librarycache;

 

Shared Pool Free %                                              % Free/Total

select f.POOL,

      name,

      sgasize / 1024 / 1024　　allocated,

      bytes / 1024　　　　　　free,

      round(bytes / sgasize * 100, 2)　　　free_rate　　　　

 from (select sum(bytes) sgasize from sys.v_$sgastat) s,

      sys.v_$sgastat f　　　　

 where f.name = 'free memory'

               and f.name = 'shared pool'

;

 

 

 

 

 

Parse CPU to total CPU ratio：

该项显示总的CPU花费在执行及解析上的比率。如果这项比率较低，说明系统执行了太多的解析。

公式：1 - (parse time cpu / CPU used by this session)

执行：

select 1-(a.value/b.value)

 from v$sysstat a,v$sysstat b

 where a.name='parse time cpu' and

        b.name='CPU used by this session';

 

 

Parse time CPU to parse time elapsed：

通常，该项显示锁竞争比率。这项比率计算是否时间花费在解析分配给CPU进行周期运算(即生产工作)。解析时间花费不在CPU周期运算通常表示由于锁竞争导致了时间花费

公式：parse time cpu / parse time elapsed

执行：

select a.value/b.value

 from v$sysstat a,v$sysstat b

 where a.name='parse time cpu' and b.name='parse time elapsed';

V$SQL, V$SQLTEXT，V$SQLAREA，V$SQL_PLAN

V$SQL_BIND_DATA，V$SQLSTATS，V$SQL_CURSOR，V$SQL_SHARED_CURSOR

V$SQL_WORKAREA_ACTIVEV$SQL_WORKAREA，V$SQL_WORKAREA_HISTOGRAM

一系列sql相关视图，sql调优的重要视图。

SQL> select a.object_name from dba_objects a

 2 where a.object_name like 'V$SQL%' order by object_name;

 

OBJECT_NAME

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

V$SQL

V$SQLAREA

V$SQLAREA_PLAN_HASH

V$SQLSTATS

V$SQLTEXT

V$SQLTEXT_WITH_NEWLINES

V$SQL_BIND_CAPTURE

V$SQL_BIND_DATA

V$SQL_BIND_METADATA

V$SQL_CURSOR

V$SQL_JOIN_FILTER

V$SQL_OPTIMIZER_ENV

V$SQL_PLAN

V$SQL_PLAN_STATISTICS

V$SQL_PLAN_STATISTICS_ALL

V$SQL_REDIRECTION

V$SQL_SHARED_CURSOR

V$SQL_SHARED_MEMORY

V$SQL_WORKAREA

V$SQL_WORKAREA_ACTIVE

V$SQL_WORKAREA_HISTOGRAM

 

21 rows selected

 

SQL>

 

Sql相关的视图，几个字段很重要，标示sql的id，sql的地址，sql的hash value，还有plan的hash value。

通过与session里头的sql相关字段关联，可以查询sql的session和system状态或其他信息。

 

通常有人喜欢问v$sql,v$sqlarea,v$sqltext这三个视图的区别，一般还真很少注意这个(这个很重要么？)，看看就晓得了：

SELECT view_definition FROM v$fixed_view_definition       WHERE view_name='GV$SQL';

SELECT view_definition FROM v$fixed_view_definition       WHERE view_name='GV$SQLAREA';

SELECT view_definition FROM v$fixed_view_definition       WHERE view_name='GV$SQLTEXT';

这里可以看到每个视图的定义，可以发现者三个视图分别基于下面三个表：

x$kglcursor_child

x$kglcursor_child_sqlid

x$kglna

这么来看，前面两个貌似差不多，看看区别：

SQL> select count(distinct a.kglobt03),count(*) from x$kglcursor_child a ;

 

COUNT(DISTINCTA.KGLOBT03)  COUNT(*)

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

                     722       789

SQL> select count(distinct a.kglobt03) sqlid,count(*) from x$kglcursor_child_sqlid a ;

 

    SQLID  COUNT(*)

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

      723       723

SQL> select count(distinct a.kglnasqlid),count(*) from x$kglna a where kgloboct != 0 ;

 

COUNT(DISTINCTA.KGLNASQLID)  COUNT(*)

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

                       820      4011

 

从上面可以看出v$sql和v$sqlarea还是很相近的，x$kglcursor_child_sqlid相当于按照sqlid等字段对x$kglcursor_child做了一个汇总后的结果：

SQL> select b.SQL_ID,count(*) from v$sqlarea b

 2 group by b.SQL_ID having count(*) > 1

 3 ;

 

SQL_ID         COUNT(*)

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

SQL> select count(*) from (

 2 select b.SQL_ID,count(*) from v$sql b

 3 group by b.SQL_ID having count(*) > 1)

 4 ;

 

 COUNT(*)

----------

       50

 

再来看v$sqltext

select d.SQL_TEXT, d.SQL_FULLTEXT, a.PIECE, a.SQL_TEXT

 from v$sqltext a,

      (select sid from v$mystat b where rownum = 1) b,

      v$session c,

      v$sql d

 where c.Sid = b.sid

  and c.SQL_ADDRESS = d.ADDRESS

  and c.SQL_HASH_VALUE = d.HASH_VALUE

  and c.SQL_ID = d.SQL_ID

  and d.ADDRESS = a.ADDRESS

  and d.HASH_VALUE = a.HASH_VALUE

  and d.SQL_ID = a.SQL_ID

 order by a.ADDRESS, a.HASH_VALUE, a.SQL_ID, a.PIECE;

 

SELECT osuser, username,piece, sql_text

 from v$session a, v$sqltext b

 where a.sql_address = b.address

 order by address, piece;

显然v$sqltext是分片了的sql。如果是9i，v$sql里面找不到完整的sql的时候，则需要从v$sqlarea中找或者拼v$sqltext的sql片。

 

V$sql_plan

对于sql调优来说非常重要的一个视图，不过一般都喜欢从plan_table中看，也可以通过灵活的sql来获得更多信息。

select c.PROGRAM,

      c.PROCESS,

      c.USERNAME,

      a.OPERATION,

      a.OPTIONS,

      a.OBJECT_NAME,

      a.OBJECT_TYPE,

      a.OPTIMIZER,

      a.ID,

      a.PARENT_ID,

      a.DEPTH,

      a.COST,

      a.CARDINALITY,

      a.BYTES,

      a.CPU_COST,

      a.IO_COST,

      a.ACCESS_PREDICATES,

      a.FILTER_PREDICATES,

      a.PROJECTION

 from v$sql_plan a,

      (select sid from v$mystat where rownum = 1) b,

      v$session c,

      v$sql d

 where b.sid = c.SID

  and c.SQL_ADDRESS = d.ADDRESS

  and c.sql_id = d.sql_id

  and c.SQL_HASH_VALUE = d.HASH_VALUE

  and d.PLAN_HASH_VALUE = a.PLAN_HASH_VALUE

  and a.ADDRESS = d.ADDRESS

  and a.sql_id = d.sql_id

  and a.HASH_VALUE = d.HASH_VALUE;

其中这里的PLAN_HASH_VALUE就是autotrace后信息中的Plan hash value: xxxxxxxx

 

V$sql_Shared_Memory

可以查看在shared pool里头的sql以及分配的内存状况，例如：

select a.CHUNK_SIZE,

      a.CHUNK_TYPE,

      a.CHUNK_PTR,

      a.ALLOC_CLASS,

      a.HEAP_DESC,

      a.SQL_TEXT

 from V$sql_Shared_Memory a,

      (select sid from v$mystat where rownum = 1) b,

      v$sql c,

      v$session d

 where d.SID = b.sid

  and d.SQL_ADDRESS = c.ADDRESS

  and d.SQL_HASH_VALUE = c.HASH_VALUE

  and d.SQL_ID = c.SQL_ID

  and c.HASH_VALUE = a.HASH_VALUE

  and c.SQL_ID = a.SQL_ID

;

 

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

转载于:http://blog.itpub.net/9954871/viewspace-674526/