--最主要的等待视图
select * from v$session_wait;
select * from v$session_event;
select * from v$system_event;
select * from v$waitstat where count>0;
--相关视图
select * from v$session_wait
select * from v$filestat
select * from dba_data_files
select * from dba_extents where OWNER='WANG'
SELECT * FROM SYS.UET$
SELECT * FROM V$LATCH
select * from v$filestat where file# in(select p1 from v$session_wait )
--基于等待事件的性能优化方法
--常见等待事件
select * from V$system_event
where event in('buffer busy waits','free buffer waits','db file sequential read',
'db file scattered read','enqueue','latch free','log file parallel write','log file sync')
--查看对上面显示有贡献的等待事件
select B.USERNAME,B.PROGRAM,B.STATUS ,A.EVENT,A.TOTAL_WAITS,A.TOTAL_TIMEOUTS,A.TIME_WAITED,A.AVERAGE_WAIT
from V$SESSION_EVENT A , V$SESSION B WHERE B.USERNAME is not null and A.SID=B.SID
AND A.EVENT NOT LIKE 'SQL*Net%'
and b.status='ACTIVE'
--为了找到与连接的会话有关的当前等待事件,使用下面的查询.这些信息是动态的,为了查看一个会话的等待最多的事件是什么,需要多次执行此查询
select sw.sid,s.Username,sw.Event,sw.wait_time,
sw.state,sw.seconds_in_wait SEC_IN_WAIT
from V$SESSION s,V$SESSION_WAIT sw
where s.Username is not null
and s.sid=sw.sid
and sw.event NOT LIKE 'SQL*Net%'
order by sw.wait_time desc;
--查询显示了有关试验的等待事件的其他信息
select sid ,event,p1text,p1,p2text,p2,p3text,p3
from v$session_wait
where event not like '%SQL%'
AND EVENT NOT LIKE '%rdbms%'
--利用 p1 和 p2 的信息很容易发现这个段是什么段
select owner,segment_name, segment_type,tablespace_name from dba_extents
where file_id=&fileid_in
and &blockid_in between block_id and block_id + blocks-1
--访问表ITEM_MASTER
--GETSQLTXT.SQL
CREATE OR REPLACE function GetSQLTxt(HashAddr_in in V$SQLTEXT.Hash_Value%Type , Addr_in in V$SQLTEXT.Address%Type) return varchar2
is
Temp_SQLTxt varchar2(32767);
cursor SQLPiece_Cur
is
select Piece,SQL_text
from v$sqltext
where Hash_value=HashAddr_in
and Address=Addr_in
order by Piece;
begin
for SQLPiece_Rec IN SQLPiece_Cur
loop
Temp_SQLTxt:=Temp_SQLTxt||SQLPiece_Rec.Sql_Text;
end loop
return Temp_SQLTxt;
end GetSQLTxt;
/
--看数据库启动以来 发生多少全表扫描!
select * from v$sysstat where name like '%table scan%'
--监视全表扫描
select sid,serial#,opname,to_char(start_time,'HH:MI:SS') "START TIME", SOFAR/TOTALWORK "% COMPLETE"
from v$session_longops
********************************************************************************************************************
--全表扫描时间长 解决过程
当前oracle系统性能
查询 MUSICSONG 表 ,全表查询用了 400秒
查询 使用 ____多少资源
最基本的时间花费
1.数据库读取,把某个表装入内存
2.计算部分(整个cpu),处理这些表
3.数据库写回
--确定oracle系统瓶颈
select * from v$system_event where total_timeouts>0 order by total_timeouts desc
drop table begin_sys_event ;
drop table end_sys_event;
/*create table begin_sys_event at time T1 */
create table begin_sys_event as
select * from v$system_event
/*wait n seconds or n minutes */
/*create table end_sys_event at time T2 */
create table end_sys_event as
select * from v$system_event
select t1.event,(T2.total_waits-T1.total_waits) "Delta waits",
(T2.total_timeouts-T1.total_timeouts) "Delta timeouts",
(T2.time_waited-T1.time_waited) "Delta time waited",
(T2.Average_wait-t2.Average_wait) "Delta Average Wait"
from begin_sys_event t1, end_sys_event t2
where t1.event=t2.event and t2.total_waits!=0;
select * From v$event_name;
select * from v$system_event where event not in '%pmon timer%'
and event not like '%rdbms ipc message%'
--指定 LRU 闩锁集数量的上限。只有在 V$LATCH 中的失败率超过 3% 的情况下,才需要增大该值。
select * From V$LATCH;
--如何标识内部latch的冲突
Server manager monitor是一个相当有用的来监视latch等待、请求和冲突的工具。
也可查询相关的数据字典表:
v$latch, v$latchholder, v$latchname。
--latch有40余种,但作为DBA关心的主要应有以下几种:
Cache buffers chains latch: 当用户进程搜索SGA寻找database cache buffers时需要使用此latch。
Cache buffers LRU chain latch: 当用户进程要搜索buffer cache中包括所有 dirty blocks的LRU (least recently used) 链时使用该种latch。
Redo log buffer latch: 这种latch控制redo log buffer中每条redo entries的空间分配。
Row cache objects latch: 当用户进程访问缓存的数据字典数值时,将使用Row cache objects latch
Redo Copy Latch只应用于多CPU的系统。在多CPU的instance中,如果一个redo entry太大,超过了LOG_SMALL_ENTRY_MAX_SIZE定义值,则不能进行“在redo allocation latch上的拷贝”, 此时用户进程必须获取redo copy latch。一个instance中可以有多个redo copy latch,其数目由初始参数LOG_SIMULTANEOUS_COPIES决定,缺省值为CPU数目。
在单CPU情况下,不存在redo copy latch,所有的redo entry无论大小, 都进行“在redo allocation latch上的拷贝”。
对redo log buffer的过多访问将导致redo log buffer latch的冲突,latch冲突将降低系统性能,我们可通过如下查询来检测这种latch冲突:
col name for a40
SELECT ln.name,gets,misses,immediate_gets,immediate_misses
FROM v$latch l,v$latchname ln
WHERE ln.name IN('redo allocation','redo copy') AND ln.latch#=l.latch#
/
若misses与gets的比例超过1%或immediate_misses与(immediate_gets+immediate_misses)比例超过1%时,应考虑采取措施减少latch的冲突。