cursor：mutex S事件查看

1.查看version_count大于500的sql_id

select sql_id,version_count from v$sqlarea where version_count> 500 order by 2 desc;

SQL_ID        VERSION_COUNT
------------- -------------
50u4uqg9vjk78           1705

2.查看sql的子游标个数

select count(CHILD_NUMBER) from v$sql_shared_cursor where sql_id='50u4uqg9vjk78';

COUNT(CHILD_NUMBER)
-------------------
                935

3.查看sql游标不能共享的原因

select * from v$sql_shared_cursor where sql_id='50u4uqg9vjk78' and rownum<10;

select child_number,optimizer_mismatch,optimizer_mode_mismatch from v$sql_shared_cursor where sql_id='50u4uqg9vjk78' and rownum<10;

4.查看sql占用内存大小

SELECT SUM (sharable_mem) / 1024 / 1024 || 'M' FROM v$sqlarea where sql_id='50u4uqg9vjk78';

SUM(SHARABLE_MEM)/1024/1024||'M'
-----------------------------------------
21.51724147796630859375M

5.查看等待事件，及其个数

SELECT event, COUNT(9) FROM v$session GROUP BY event;

6.查看cursor: mutex S等待事件的个数

SELECT sql_id, COUNT(1) FROM v$session WHERE event = 'cursor: mutex S' GROUP BY sql_id;

SELECT sql_text FROM v$sql WHERE sql_id = '50u4uqg9vjk78';

7.查看等待事件的相关SID等

select r.root_sid, s.serial#,

       r.blocked_num, r.avg_wait_seconds,
       s.username,s.status,s.event,s.MACHINE,
       s.PROGRAM,s.sql_id,s.prev_sql_id
  from (select root_sid, avg(seconds_in_wait) as avg_wait_seconds,
               count(*) - 1 as blocked_num
          from (select CONNECT_BY_ROOT sid as root_sid, seconds_in_wait
                  from v$session
                 start with blocking_session is null
                connect by prior sid = blocking_session)
         group by root_sid
        having count(*) > 1) r,
       v$session s
 where r.root_sid = s.sid
 order by r.blocked_num desc, r.avg_wait_seconds desc

8.通过进程号定位，用占用cpu最高的oracle用户的进程号定位相关的oracle session

如有个进程号12345：
SELECT sql_fulltext
  FROM v$sql
WHERE sql_id = (SELECT sql_id
                   FROM v$session
                  WHERE paddr = (SELECT addr
                                   FROM v$process
                                  WHERE spid = '12345'));


定位session信息：
SELECT *
  FROM v$session
WHERE paddr = (SELECT addr
                  FROM v$process
                 WHERE spid = '12345');


SELECT sid,serial#,username,paddr
  FROM v$session
WHERE paddr = (SELECT addr
                  FROM v$process
                 WHERE spid = '12345');


杀session：
alter system kill session 'SID,SERIAL#';


http://prefectliu.blog.163.com/blog/static/2363081820123652347371/
http://blog.itpub.net/25099483/viewspace-1068104/

9.查看绑定失败的原因

SELECT COUNT(*) FROM v$sql_shared_cursor where sql_id='3axf89cxy7cv5' and bind_mismatch='Y';

select position,LAST_CAPTURED,datatype_string,value_string from v$sql_bind_capture where sql_id='9fvb89xs5rc37' and rownum<50;

10.修改参数已解决11.2.0.1版本的cursor:mutex S和library catch lock事件

SQL> alter system set "_cursor_features_enabled"=34 scope=spfile;
System altered.
SQL> alter system set event='106001 trace name context forever,level 1024' scope=spfile;
System altered.

SQL> alter system set "_cursor_features_enabled"=34 scope=spfile;
System altered.
SQL> alter system set event='106001 trace name context forever,level 1024' scope=spfile;
System altered.

AWR报告生成方法：


1.生成标准统计报表
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
2.生成指定数据库实例的统计报表
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
3.生成指定SQL语句的统计报表
@$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
4.生成指定数据库实例中指定SQL语句的统计报表
@$ORACLE_HOME/rdbms/admin/awrsqrpi.sql
5.生成不同时间段时的统计对比报表
@$ORACLE_HOME/rdbms/admin/awrddrpt.sql