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/18953778/viewspace-674892/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/18953778/viewspace-674892/