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#的值)
标签:--,object,BUFFER,session,监控,oracle,GETS,优化,id From: https://www.cnblogs.com/Jiu-er/p/17275714.html