性能分析
动态视图的 3 大类型
Current State Views Counter/Accumulator Views Information Views
Table 24-1 Current State Views （当前状态视图）
| Fixed View | Description |
| V$LOCK | Locks currently held/requested on the instance |
| V$LATCHHOLDER | Sessions/processes holding a latch |
| V$OPEN_CURSOR | Cursors opened by sessions on the instance |
| V$SESSION | Sessions currently connected to the instance |
| V$SESSION_WAIT | Different resources sessions are currently waiting for |
Table 24-2 Summary Since Session Startup （累计视图）
| Fixed View | Description |
| V$DB_OBJECT_CACHE | Object level statistics in shared pool |
| V$FILESTAT | File level summary of the I/O activity |
| V$LATCH | Latch activity summary |
| V$LATCH_CHILDREN | Latch activity for child latches |
| V$LIBRARYCACHE | Namespace level summary for shared pool |
| V$LIBRARY_CACHE_MEMORY | Summary of the current memory use of the library cache, by library cache object type |
| V$MYSTAT | Resource usage summary for your own session |
| V$ROLLSTAT | Rollback segment activity summary |
| V$ROWCACHE | Data dictionary activity summary |
| V$SEGMENT_STATISTICS | User-friendly DBA view for real-time monitoring of segment-level statistics |
| V$SEGSTAT | High-efficiency view for real-time monitoring of segment-level statistics |
| V$SESSION_EVENT | Session-level summary of all the waits for current sessions |
| V$SESSTAT | Session-level summary of resource usage since session startup |
| V$LIBRARY_CACHE_MEMORY | Simulation of the shared pool's LRU list mechanism |
| V$SQL | Child cursor details for V$SQLAREA |
| V$SQLAREA | Shared pool details for statements/anonymous blocks |
| V$SYSSTAT | Summary of resource usage |
| V$SYSTEM_EVENT | Instance wide summary of resources waited for |
| V$UNDOSTAT | Histogram of undo usage. Each row represents a 10-minute interval. |
| V$WAITSTAT | Break down of buffer waits by block class |
Table 24-3 Information Views
| Fixed View | Description |
| V$MTTR_TARGET_ADVICE | Advisory information collected by MTTR advisory, when FAST_START_MTTR_TARGET is set |
| V$PARAMETER and V$SYSTEM_PARAMETER | Parameters values for your session Instance wide parameter values |
| V$PROCESS | Server processes (background and foreground) |
| V$SEGSTAT_NAME | Statistics property view for segment-level statistics |
| V$SQL_PLAN | Execution plan for cursors that were recently executed |
| V$SQL_PLAN_STATISTICS | Execution statistics of each operation in the execution plan |
| V$SQL_PLAN_STATISTICS_ALL | Concatenates information in V$SQL_PLAN with execution statistics from V$SQL_PLAN_STATISTICS and V$SQL_WORKAREA |
| V$SQLTEXT | SQL text of statements in the shared pool |
| V$STATISTICS_LEVEL | Status of the statistics or advisories controlled by the STATISTICS_LEVEL initialization parameter |
V$lock
Example 24-11 Finding the Sessions Holding the Lock
如果 TYPE 是 TM ， id1 表示 db_objects 的 object_id
Find the (ID1, ID2, type) for sessions waiting for a lock (LMODE=0).
Find the session holding the lock (REQUEST=0) for that ID1, ID2, type.
SELECT lpad(' ',DECODE(request,0,0,1))||sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
ORDER BY id1,request
SID ID1 ID2 LMODE REQUEST TY
------ ---------- ---------- ---------- ---------- --
1237 196705 200493 6 0 TX <- Lock Holder
1256 196705 200493 0 6 TX <- Lock Waiter
1176 196705 200493 0 6 TX <- Lock Waiter
938 589854 201352 6 0 TX <- Lock Holder
1634 589854 201352 0 6 TX <- Lock Waiter
Example 24-12 Finding the Statements being Executed by These Sessions
SELECT sid, sql_hash_value
FROM V$SESSION
WHERE SID IN (1237,1256,1176,938,1634);
SID SQL_HASH_VALUE
----- --------------
938 2078523611 <-Holder
1176 1646972797 <-Waiter
1237 3735785744 <-Holder
1256 1141994875 <-Waiter
1634 2417993520 <-Waiter
Time Model Statistics 时间模型（时间上的统计）
以下语句显示session sid 131的时间花在哪
WITH db_time AS (SELECT sid, value FROM v$sess_time_model WHERE sid = 131 AND stat_name = 'DB time') SELECT ses.stat_name AS statistic, round(ses.value / 1E6, 3) AS seconds, round(ses.value / nullif(tot.value, 0) * 1E2, 1) AS "%" FROM v$sess_time_model ses, db_time tot WHERE ses.sid = tot.sid AND ses.stat_name <> 'DB time' AND ses.value > 0 ORDER BY ses.value DESC;
STATISTIC SECONDS %
---------------------------------------------------------------- ---------- ----------
DB CPU .042 132.5
connection management call elapsed time .016 51.2
sql execute elapsed time .005 14.1
parse time elapsed .001 1.8
Active Session History 会话历史
v$session 只提供了当前 session 的状态，为了分析，需要知道过去一段时间的情况， active session history (ASH) V$ACTIVE_SESSION_HISTORY 显示数据库中的采样会话活动。 ASH 每秒从 v$session 中取快照，存在 V$ACTIVE_SESSION_HISTORY 中，并收集所有活动会话的等待信息。若 ASH 数据被刷新到磁盘，则需要从 DBA_HIS_ACTIVE_SESS_HISTORY 视图中查询相关信息。 V$ACTIVE_SESSION_HISTORY 类似 SQL Trace, 但好的是 V$ACTIVE_SESSION_HISTORY 总是可用的 .
过去10分钟DB Time最高的10条语句
SELECT activity_pct,db_time,sql_id FROM (SELECT round(100 * ratio_to_report(count(*)) OVER (), 1) AS activity_pct, count(*) AS db_time, sql_id FROM v$active_session_history WHERE sample_time > sysdate-10/(24 * 60) AND sql_id IS NOT NULL GROUP BY sql_id ORDER BY count(*) DESC ) WHERE rownum <= 10;
查找最近10分钟内，最消耗CPU的sql语句
SELECT sql_id, count(*), round(count(*)/sum(count(*)) over(), 2) pctload
FROM V$ACTIVE_SESSION_HISTORY
WHERE sample_time > sysdate-10 / (24 * 60)
AND session_type <> 'BACKGROUND'
AND session_state = 'ON CPU'
GROUP BY sql_id
ORDER BY count(*) desc;
ASH Report
也可以用 ash report 创建 ASH 报告查看一段时间数据库主要事件。
$ORACLE_HOME/rdbms/admin/ashrpt.sql or ashrpti.sql
查看一段时间系统的性能 , 经历了什么等待事件 ( 类似与上面的 ASH) 。
第一步：
create table sys_b(event varchar2(64),time_waited number,total_waits number);
create table sys_e(event varchar2(64),time_waited number,total_waits number);
第二步：
insert into sys_b select event,time_waited,total_waits from v$system_event;
第三步：
执行用户应用。。。（或者在高峰时期，需要诊断问题的时期）。
第四步：
insert into sys_e select event,time_waited,total_waits from v$system_event;
create table sys_dif as select e.event,e.time_waited - b.timed_waited time_waited,e.total_waits - b.total_waits total_waits from sys_b b,sys_e e where b.event=e.event;
select event "wait event",time_waited "time waited",time_waited / (select sum(time_waited) from sys_dif) "%time waited",total_waits "waits",total_waits / (select sum(total_waits) from sys_dif) "%waited"
from sys_dif order by 3 desc;
drop table sys_dif;
drop table sys_d;
drop table sys_e;
检查发生了多少全表扫描
SELECT name, value FROM v$sysstat WHERE name LIKE '%table scans%';
table scans (long tables) 和 table scans (short tables) 和 full table scan 相关。如果 table scans (long tables) 比较高，调整应用或加索引。
监控全表扫描，并且查看估计的时间等信息
SELECT sid, serial#, opname, TO_CHAR(start_time,’HH24:MI:SS’)AS “START”,
(sofar/totalwork)*100 AS “PERCENT_COMPLETE” FROM v$session_longops;
SID SERIAL# OPNAME START PERCENT_COMPLETE
--- -------- ------------ ---------- -----------------
8 219 TABLE SCAN 13:00:09 48.98098
v$session_longops 视图显示运行时间超过 6 秒的的各种操作的情况，包括很多备份、恢复、收集统计信息、数据查询等。
找出消耗 30,000 字节 Program Global Area (PGA) 内存的会话
select s.sid,username,name,value from v$statname n, v$session s, v$sesstat t where s.sid=t.sid and n.statistic#=t.statistic# and s.type='USER' and s.username is not null and n.name='session pga memory' and t.value > 30000;
查找前十条性能差的 sql
SELECT * FROM (SELECT PARSING_USER_ID, EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,sql_text FROM v$sqlarea ORDER BY disk_reads DESC) WHERE ROWNUM<10;
查看占用 CPU 资源很高的 Oracle 进程究竟是在做什么操作，使用如下 SQL 语句：
Ø select sql_text,spid,v$session.program,process from v$sqlarea,v$session,v$process where $sqlarea.address=v$session.sql_address and v$sqlarea.hash_value=v$session.sql_hash_value and v$session.paddr=v$process.addr and v$process.spid in (PID);
Ø SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text FROM v$session a,v$process b,v$sqltext c WHERE b.spid='&spid' AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece
Ø REM getsql.sql
REM author eygle
REM 在windows上,已知进程ID,得到当前正在执行的语句
REM 在windows上,进程ID为16进制,需要转换,在UNIX直接为10进制
SELECT /*+ ORDERED */
sql_text
FROM v$sqltext a
WHERE (a.hash_value, a.address) IN (
SELECT DECODE (sql_hash_value,
0, prev_hash_value,
sql_hash_value
),
DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
WHERE b.paddr = (SELECT addr
FROM v$process c
WHERE c.spid = TO_NUMBER ('&pid', 'xxxx')))
ORDER BY piece ASC /
根据游标查询正在执行的语句
select user_name,sql_text from v$open_cursor
where sid in (select sid from (select sid,serial#,username,program
from v$session where status='ACTIVE'))
用 trace 跟踪会话
SQL> exec dbms_system.set_sql_trace_in_session(17,178,true);
PL/SQL procedure successfully completed
SQL> exec dbms_system.set_sql_trace_in_session(17,178,false);
PL/SQL procedure successfully completed
C:\>tkprof D:\oracle\admin\e3\udump\e3_ora_2292.trc d:\trace.txt
db file scattered read 、 db file sequential read wait event
db file scattered read 表示把数据读进不连续的内存 buffer cache, 表示一个全表扫描
找到哪个会话正在发生 I/O 等以及正在执行的 SQL 语句
SELECT s.sid, s.sql_address, s.sql_hash_value FROM V$SESSION s, V$SESSION_WAIT w WHERE w.event LIKE 'db file%read'
AND w.sid = s.sid ;
根据 V$SESSION_WAIT (p1 and p2 columns) 从 DBA_EXTENTS 定位对象
SELECT owner, segment_name FROM DBA_EXTENTS
WHERE file_id = <&p1>
AND <&p2> BETWEEN block_id AND block_id + blocks - 1;
db file sequential read 与 db file scattered read 不同的是读到连续的内存中去，通常表示读一块
- db file sequential read (single block read into one SGA buffer)
- db file scattered read (multiblock read into many discontinuous SGA buffers)
- direct read (single or multiblock read into the PGA, bypassing the SGA)
- Figure 22-1 Scattered Read, Sequential Read, and Direct Path Read
Enqueue 表示串行化访问数据库资源的队列锁，该事件表示会话正在等一个其它会话占有的锁
此时 V$SESSION_WAIT 里参数含义：
- P1 - Lock TYPE (or name) and MODE
- P2 - Resource identifier ID1 for the lock
- P3 - Resource identifier ID2 for the lock
V$LOCK 里的对应：
- V$LOCK.ID1 = P2
- V$LOCK.ID2 = P3
找到锁和锁的持有者：
request>0 表示发生了 event enqueue 等待
SELECT DECODE(request,0,'Holder: ','Waiter: ')|| sid sess, id1, id2, lmode, request, type FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request;
free buffer waits 表示在 cache 里没有空闲的 buffer 了，且引发了写脏数据
可能的引起：
- The I/O system is slow.
- There are resources it is waiting for, such as latches.
- The buffer cache is so small that DBWR spends most of it's time cleaning out buffers for server processes.
- The buffer cache is so big that one DBWR process is not enough to free enough buffers in the cache to satisfy requests.
latch free
Latch 是低级别内部锁，被用来保护内存结构，当一个服务进程试图得到一个 latch 但没有成功，引发 latch free 事件
此时 V$SESSION_WAIT 里参数含义：
- P1 - Address of the latch
- P2 - Latch number
- P3 - Number of times process has already slept, waiting for the latch
找出正在发生等待的latch
SELECT n.name, SUM(w.p3) Sleeps FROM V$SESSION_WAIT w, V$LATCHNAME n WHERE w.event = `latch free' AND w.p2 = n.latch# GROUP BY n.name;
锁的例子
Example I
We want to change the Schedule from Tele-meeting to Tele-conference. We will lock such rows for Update so that other users cannot update them.
declare
v_schedule varchar2(200);
cursor my_cur is
select replace(schedule, 'Tele-meeting', 'Tele-conference')
from room_bookings
for update of schedule;
begin
open my_cur;
loop
fetch my_cur into v_schedule;
exit when my_cur%NOTFOUND;
update room_bookings set schedule = v_schedule
where current of my_cur;
end loop;
close my_cur;
end;
/
Example II
Open two SQL sessions and in each session issue the following
Session I
SQL> SELECT SCHEDULE
FROM room_bookings
WHERE booking_code = 'B00090'
FOR UPDATE OF schedule;
Session II
SQL> update room_bookings
set schedule='Test'
where booking_code='B00090';
The transaction in this session will fail to move ahead. This is because in Session I, the row has been locked. The transaction is Session II will move ahead if we issue the COMMIT or ROLLBACK command.
Session I
SQL> SELECT SCHEDULE
FROM room_bookings
WHERE booking_code = 'B00090'
FOR UPDATE OF schedule;
ROOM_ SCHEDULE BOOKING_CD
---------- --------------------------------------------------- -------------------
R0020 Booked for Tele-conference scheduled on 16/07/2003 B00090
SQL> rollback;
Rollback complete.
SQL>
Session II
SQL> update room_bookings
set schedule='Test'
where booking_code='B00090';
1 row updated.
v$session_wait 字段说明：
state4种状态：
waiting: 会话正在等待这个事件
waited unknow time: timed_statistics=false, so 不能得到相关时间信息
waited short time: 发生了等待，但等待时间非常小，so 不记录
waited known time: 当会话等待到了等待的source，状态从waiting进入waited known time.
wait_time,该字段的值取决state:
state＝waiting,该字段值忽略
state=waited unknown time,该字段值忽略
state=waited short time,该字段值忽略
state=waited known time,wait_time的值就是实际等待时间。看到这个值比较难，如果会话开始等待1个source，state将再次变成waiting,这个字段的值again no userful!
seconds_in_wait,该字段的值取决state:
state＝waiting,该值是实际等待时间，单位是秒
state=waited unknown time,该字段值忽略
state=waited short time,该字段值忽略
state=waited known time,该字段值忽略
查看自系统启动来的系统详细信息（但查看 1 段时间的系统性能变化情况才能更好找到问题）
select event "wait event",time_waited "time waited",time_waited/(select sum(time_waited) from v$system_event) "%time waited",total_waits "waits",total_waits/(select sum(total_waits) from v$system_event) "%waited" from v$system_event order by 3 desc;
用 V$BH 查看表来显示数据库里每个对象类型在数据缓冲区里数据块的数量，就可以看到数据缓冲区里最常用的表，以及它们所消耗的内存量
select owner,object_name,count(1) "number of buffer",(count(1)/(select count(*) from v$bh))*100 "%data buffer" from dba_objects o,v$bh bh where o.object_id = bh.objd and o.owner not in('SYS','SYSTEM','AURORA$JIS$UTILITY$') group by owner, object_name order by count(1) desc;
What’s in the Database Buffer Cache? 联合 v$bh 和 object_name
select o.owner,o.object_type,o.object_name,count(b.objd),count(b.objd)/(select count(*) from v$bh)*100 "%buffer" from v$bh b,dba_objects o where b.objd=o.object_id and o.owner not in('SYS','SYSTEM') group by o.owner,o.object_type,o.object_name order by count(b.objd) desc
group by o.owner,o.object_type,o.object_name ， count(b.objd) 表示在 v$bh 里有多少该对象的不同块在 buffer 里，同一个对象在 buffer cache 里可能不同的块被 cache 在 buffer 里。 V$bh 里的每一行代表一块，所以 select count(*) from v$bh 表示整个 buffer 有多少。
cache buffers chains
The cache buffers chains latches are used to protect a buffer list in the buffer cache. These latches are used when searching for, adding, or removing a buffer from the buffer cache. Contention on this latch usually means that there is a block that is greatly contended for (known as a hot block).
找出 10 条等待最多的 latch ：
select * from (select ADDR,LATCH#,GETS,MISSES,SLEEPS from V$LATCH_CHILDREN where name like 'cache buffers cha%' and MISSES>0 and sleeps>0 order by MISSES desc) where rownum<10;
ADDR LATCH# GETS MISSES SLEEPS
-------- ---------- ---------- ---------- ----------
217C59D8 97 1189858 57 1
217A5098 97 304127 57 1
217E37A8 97 226257 47 1
21771FF8 97 318259 47 2
217C5128 97 261580 46 1
217A8D68 97 201213 46 1
217E14E8 97 182655 43 1
217DACA8 97 208325 43 1
217D4468 97 229606 43 1
SELECT file#, dbablk, class, state, TCH FROM X$BH WHERE HLADDR='address of latch';
X$BH.TCH is a touch count for the buffer. A high value for X$BH.TCH indicates a hot block ！
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14377/viewspace-2288205/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/14377/viewspace-2288205/
802
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
