oracle 优化监控相关

SELECT   sn.username, m.SID,sn.SERIAL#, m.TYPE,
         DECODE (m.lmode,
                 0, 'None',
                 1, 'Null',
                 2, 'Row Share',
                 3, 'Row Excl.',
                 4, 'Share',
                 5, 'S/Row Excl.',
                 6, 'Exclusive',
                 lmode, LTRIM (TO_CHAR (lmode, '990'))
                ) lmode,
         DECODE (m.request,
                 0, 'None',
                 1, 'Null',
                 2, 'Row Share',
                 3, 'Row Excl.',
                 4, 'Share',
                 5, 'S/Row Excl.',
                 6, 'Exclusive',
                 request, LTRIM (TO_CHAR (m.request, '990'))
                ) request,
         m.id1, m.id2
    FROM v$session sn, v$lock m
   WHERE (sn.SID = m.SID AND m.request != 0)         --存在锁请求，即被阻塞
      OR (    sn.SID = m.SID                         --不存在锁请求，但是锁定的对象被其他会话请求锁定
          AND m.request = 0
          AND lmode != 4
          AND (id1, id2) IN (
                        SELECT s.id1, s.id2
                          FROM v$lock s
                         WHERE request != 0 AND s.id1 = m.id1
                               AND s.id2 = m.id2)
         )
ORDER BY id1, id2, m.request;



--存储过程编译一直未响应  查看session情况
select event,sid,p1,p2,p3 from v$session_wait where event not like 'SQL*%' and event not like 'rdbms%';
/*
P1—与等待相关的数据文件的全部文件数量。 
P2—P1中的数据文件的块数量。 
P3—描述等待产生原因的代码。
*/
--
select object_id,owner,object_name,object_type from dba_objects where object_id=&ID1;


SELECT  EXECUTIONS , DISK_READS, BUFFER_GETS, 
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, 
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, 
SQL_TEXT 
FROM  V$SQLAREA 
WHERE  EXECUTIONS>0 
AND  BUFFER_GETS > 0 
AND  (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 
ORDER BY  4 DESC;
--EXECUTIONS 所有子游标的执行这条语句次数
--DISK_READS  所有子游标运行这条语句导致的读磁盘次数
--BUFFER_GETS  所有子游标运行这条语句导致的读内存次数 
--Hit_radio 命中率
--Reads_per_run 每次执行读写磁盘数
--笼统的说EXECUTIONS，BUFFER_GETS，Hit_radio越高表示读内存多，磁盘少是比较理想的状态，因此越高越好 
--另外两个越高读磁盘次数越多，因此低点好

--查看消耗资源最多的SQL：
SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls  
FROM V$SQLAREA  
WHERE buffer_gets > 10000000 OR disk_reads > 1000000  
ORDER BY buffer_gets + 100 * disk_reads DESC;  

--查找前10条性能差的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 ; 


--分析性能差的sql
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,   
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,   
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,   
SQL_TEXT   
FROM V$SQLAREA   
WHERE EXECUTIONS>0   
AND BUFFER_GETS >0   
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8

--选出最占用资源的查询 
select b.username username,a.disk_reads reads,a.executions exec,  
    a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,  
    a.sql_text statement  
    from v$sqlarea a,dba_users b  
    where a.parsing_user_id=b.user_id  
    and a.disk_reads>100000 

--查询是否锁表
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT Null;


select sess.sid, 
   sess.serial#, 
   lo.oracle_username, 
   lo.os_user_name, 
   ao.object_name, 
   lo.locked_mode 
   from v$locked_object lo, 
   dba_objects ao, 
   v$session sess 
where ao.object_id = lo.object_id and lo.session_id = sess.sid; 

--查看锁表进程语句
select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;

--查看表空间下的表名
select * from all_tables where tablespace_name='NGDW_DATATBS_BAS_PARTS';
select TABLE_NAME,tablespace_name from dba_tables where tablespace_name ='NGDW_DATATBS_BAS_PARTS';

--数据库未提交事务导致锁表解锁
SELECT s.sid, s.serial#,ao.object_name FROM v$locked_object lo, dba_objects ao, v$session s WHERE ao.object_id = lo.object_id AND lo.session_id = s.sid;  

select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b
where a.session_id = b.sid order by b.logon_time;

SELECT  SESSION_ID   FROM   V$LOCKED_OBJECT,USER_OBJECTS WHERE   V$LOCKED_OBJECT.OBJECT_ID   =   USER_OBJECTS.OBJECT_ID

--查看序列号
SELECT SERIAL# FROM V$SESSION  WHERE SID='134'

--杀session
ALTER   SYSTEM   KILL   SESSION  '134,45311'--(48为SESSION_ID的值, 2476为SERIAL#的值)