ORACLE 性能调整杂记

--最主要的等待视图
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的冲突。