SQL> select t2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid order by t2.logon_time;            

no rows selected

SQL>

SQL>

select /*+ rule */ lpad(' ', decode(t1.xidusn,0,3,0)) || t1.oracle_username user_name,o.owner,o.object_name,o.object_type,s.sid,s.serial#,p.spid                

from v$locked_object t1,dba_objects o, v$session s,v$process p

where t1.object_id=o.object_id

and t1.session_id=o.object_id

and t1.session_id=s.sid

  6  and s.paddr=p.addr;

no rows selected

SQL>

# 查看inactive进程，kill掉这些进程

 SELECT SID, SERIAL#,MODULE, STATUS

   FROM V$SESSION S

   WHERE S.USERNAME IS NOT NULL

   AND UPPER(S.PROGRAM) IN ('TOAD.EXE', 'W3WP.EXE')

   AND S.LAST_CALL_ET >= 60*60*2

   AND S.STATUS = 'INACTIVE'

   ORDER BY SID DESC;

# 通过如下sql获取kill命令：

         SELECT 'alter system kill session '''||SID||','|| SERIAL#||''';'    FROM V$SESSION S   WHERE S.USERNAME IS NOT NULL   AND S.LAST_CALL_ET >= 10   AND S.STATUS = 'INACTIVE'   ORDER BY S.MACHINE,SID DESC;

# 查询等待sql语句

         SELECT t1.sid,

                      t1.serial#,

                      t1.username,

                      t1.event,

                      t2.sql_text,

                      t1.sql_id,

                      t1.seconds_in_wait,

                      t1.program,

                      t1.blocking_session,

                     -- object_type || ': ' || object_name object,                                                                                                                                 

                      t1.wait_class,

                      t1.state

           FROM v$session t1

           LEFT OUTER JOIN v$sqlarea t2

                   on (t1.sql_id = t2.sql_id)

           --LEFT OUTER JOIN dba_objects t3

                   --ON (object_id = row_wait_obj#)

          where t1.wait_class <> 'Idle';

SQL> show parameter cursor_sharing;

NAME                                        TYPE       VALUE

------------------------------------ ----------- ------------------------------                                                                                                                     

cursor_sharing                                 string     EXACT

SQL>

SQL>

select sql_id,count(*)

 from dba_hist_active_sess_history

 where event like 'cursor: mutex S' and sample_time >to_date('28-04-2016 00:00:00', 'dd-mm-yyyy hh24:mi:ss')                         

  4   group by sql_id;

SQL_ID              COUNT(*)

------------- ----------

                      94508

8n23w37kwdn3p            1

08nsmbsvfkzv6           353543

SQL>

SQL> select  snap_id,ELAPSED_TIME_DELTA/EXECUTIONS_DELTA,CPU_TIME_DELTA/EXECUTIONS_DELTA,EXECUTIONS_DELTA from dba_hist_sqlstat where sql_id='08nsmbsvfkzv6' order by snap_id;

no rows selected

 select sql_id,count(*)

 from dba_hist_active_sess_history

 where event like 'cursor: mutex S' and sample_time <to_date('28-04-2016 00:00:00', 'dd-mm-yyyy hh24:mi:ss') and sample_time >to_date('27-04-2016 00:00:00', 'dd-mm-yyyy hh24:mi:ss')

  4   group by sql_id;

SQL_ID              COUNT(*)

------------- ----------

                      11653

08nsmbsvfkzv6            38468

SQL>

select * from ( select * from (

(select (case when (instr(','||wm_concat(type)||',',',develop-plan,')>0) then 'develop-plan' else 'unRead' end) type,

         max(id) id,name,node_level,project,end,max(created_date) created_date,

             center,year_flg,sequence,

                  min(isRead) isRead,

                    MyReplace(wm_concat(msgCreateDate),',') msgCreateDate,wm_concat(group_name),max(planId),0 owe_days,0 owe_money

   from (select

         'develop-plan' as type,

         node.node_id as id,

         template_node.node_name as name,

         template_node.node_level,

         project.project_name as project,

         node.schedule_end_date as end,

         warning.CREATED_DATE as created_date,

         '' as center ,         '' as year_flg,          ROW_NUMBER() OVER(PARTITION BY node.templet_id, project.project_project_id ORDER BY warning.created_date ASC) as sequence,

                     (select min(t.is_read) from plan6_message_user_read t where t.node_id=node.node_id and t.need_reader='baolm1' group by t.node_id) isRead,                 '' msgCreateDate,info.name group_name,info.biz_cd planId,0 owe_days,0 owe_money           from plan6_warning      warning,

               plan6_node         node,

               project_distribution         info,project_period pp,

               project_project      project,

               plan6_templet_node template_node

         where warning.node_id = node.node_id

           and node.plan_id = info.biz_cd

           and info.project_period_id = pp.project_period_id and pp.is_enabled=1 and pp.project_project_id = project.project_project_id

           and template_node.node_id = node.templet_id

           and info.if_in_plan = 1

           and (warning.charger_cd = 'baolm1' OR node.charger_cd='baolm1' OR node.center_manager_cd='baolm1' OR project.project_charger_cd='baolm1' OR project.project_charger_cd2='baolm1' OR project.project_charger_cd_bis='baolm1' OR ('wubc'='baolm1' AND template_node.node_level in(1,2) )                 OR (('xuhf'='baolm1' OR 'zhanghf'='baolm1' ) and template_node.node_level=1)                                  )            and warning.stat = 2

           and node.status = 1 and node.is_enabled<>0 and warning.stat = 2

                 union

                   select      'unRead' tyep,node.node_id id,template_node.node_name as name,

                              template_node.node_level,project.project_name as project,

                            node.schedule_end_date as end,

                                to_date('','yyyymmdd hh24:mi:ss') as created_date,

                        '' as center ,'' as year_flg, 

                               ROW_NUMBER() OVER(PARTITION BY node.templet_id, 

                                        project.project_project_id ORDER BY r.created_date ASC) as sequence,

                                r.is_read isRead,to_char(r.created_date,'yyyymmdd hh24:mi:ss') msgCreateDate,info.name group_name,info.biz_cd planId,0 owe_days,0 owe_money

                      from plan6_message_user_read r,plan6_node node,

                                    project_distribution         info,project_period pp,project_project project,

                                    plan6_templet_node template_node 

                   where r.node_id = node.node_id

                            and node.plan_id = info.biz_cd

                            and info.project_period_id = pp.project_period_id and pp.is_enabled=1 and pp.project_project_id = project.project_project_id

                            and template_node.node_id = node.templet_id

                            and info.if_in_plan = 1

                            and node.is_enabled<>0 

                            and r.need_reader='baolm1' 

                            and r.is_read='0'         ) warning

 where warning.sequence = 1 group by name,node_level,project,end,center,year_flg,sequence )

 union   (select 'oa_meeting' as type,

       metting.oa_meeting_id as id,

       TO_CHAR (metting.business) as name,

       10 as node_level,

       '' as project,

       metting.target_date as end,

       warning.created_date as created_date,

       '' as center,       '' as year_flg,       0 as sequence,'' isRead,'' msgCreateDate,'' group_name,'' planId,0 owe_days,0 owe_money

  from oa_meeting metting, plan_warning warning

 where metting.oa_meeting_id = warning.target

   and metting.HIDDEN_FLG = 0

   and metting.status in (1,2, 4)

   and metting.target_date is not null    and instr(';'||warning.responsible_person ,';baolm1;')>0 ) union

  (select 'work-center-plan' as type,          cen.plan_work_center_id as id,          cen.content as name,          10 as node_level,          '' as project,          cen.target_date as end,          warning.created_date as created_date,          '' center ,                '' year_flg,                0 as sequence,'' isRead,'' msgCreateDate,'' group_name,'' planId ,0 owe_days,0 owe_money    from plan_work_center cen,plan_warning warning    where cen.plan_work_center_id = warning.target      and cen.status_cd in (1)          and cen.open_type = '0'     and cen.target_date is not null         and instr(';'||cen.principal ,';baolm1;')>0) union (select 'project_detail' as type,  pd.project_detail_id as id, pd.content as name,12 as node_level, '' as project, pd.target_date as end,  pd.created_date as created_date,'' as center , '' as year_flag, 0 as sequence, '' isRead, '' msgCreateDate, '' group_name,'' planId,0 owe_days,0 owe_money from project_detail pd where pd.status in ('1','2','4') and pd.project_detail_id in (select pp.project_detail_id from project_principal pp where pp.principal_cd='baolm1'))) warning order by   (case when type='work-zc-plan' then -1 else 1 end) asc, (case when type='unRead' then 0 else 1 end) asc,    (case when isRead=0 then 0 else 1 end) asc,       (case when msgCreateDate is not null then 0 else 1 end) asc,  warning.type asc,(case when warning.node_level=0 then 4 else warning.node_level end) asc, trunc(warning.end)-trunc(sysdate) asc,warning.created_date desc,owe_money desc ) where rownum <= :1

select sql_id,to_char(sample_time,'yyyy-mm-dd hh24:mi'),count(*)

 from dba_hist_active_sess_history

 where event like'cursor: mutex S'

 and sample_time > to_date('28-04-2016 22:30:00', 'dd-mm-yyyy hh24:mi:ss')

 AND sample_time < to_date('28-04-2016 23:10:00', 'dd-mm-yyyy hh24:mi:ss')                                                                                                                                                                    

 groupby sql_id,to_char(sample_time,'yyyy-mm-dd hh24:mi')

 orderby sql_id,to_char(sample_time,'yyyy-mm-dd hh24:mi') desc;