1.---------------------------------- hang分析
sqlplus -prelim / as sysdba
sysdate-1/24;
create table opencursor1031 as select * from gv$open_cursor ;
create table session1031 as select * from gv$session ;
create table sql1031 as select * from gv$sql ;
--------锁表
select b.username,b.sid,b.serial#,logon_time,a.LOCKED_MODE,d.OBJECT_NAME from v$locked_object a , v$session b,dba_objects d
where a.session_id = b.sid
and a.OBJECT_ID=d.OBJECT_ID
order by b.LOGON_TIME;
---------------------查看慢SQL
with m as (select * from ( select inst_id, PARSING_SCHEMA_NAME, sql_id, last_active_time,sum(t.EXECUTIONS), round(sum(t.ELAPSED_TIME)/1e6)||'s' ELAPSED_TIME,
round(sum(t.ROWS_PROCESSED)/decode(sum(t.EXECUTIONS),0,1,sum(t.EXECUTIONS))) avg_rows,
round(sum(t.ELAPSED_TIME)/decode(sum(t.EXECUTIONS),0,1,sum(t.EXECUTIONS))/1e3,2) avg_ms, round(ratio_to_report(sum(t.ELAPSED_TIME)) over(),4)*100||'%' as time_ratio
from gv$sql t where LAST_ACTIVE_TIME >sysdate -1/24 and PARSING_SCHEMA_NAME not in ('SYS')
group by inst_id,PARSING_SCHEMA_NAME,sql_id , last_active_time
order by sum(t.ELAPSED_TIME) desc ) where rownumsysdate -1/24
)
select m.*, sql_fulltext from l ,m where l.sql_id = m.sql_id and l.inst_id = m.inst_id
and row1=1 order by time_ratio;
----------------------------------查看行锁
select 'blocker('||lb.sid||':'||sb.username||')-sql:'|| qb.sql_text blockers,
'waiter ('||lw.sid||':'||sw.username||')-sql:'|| qw.sql_text waiters
from v$lock lb,
v$lock lw,
v$session sb,
v$session sw,
v$sql qb,
v$sql qw
where lb.sid=sb.sid
and lw.sid=sw.sid
and sb.prev_sql_addr=qb.address
and sw.sql_address=qw.address
and lb.id1=lw.id1
and sw.lockwait is not null
and sb.lockwait is null
and lb.block=1;
------------------------------查慢 sql
select a.sql_id,trunc(a.elapsed_time/a.executions) avg_us from v$sqlarea a,
(select sql_id,trunc(sum(b.elapsed_time_total)/sum(b.executions_total)) avg_us from dba_hist_sqlstat b
where b.snap_id=(select max(snap_id) from dba_hist_sqlstat c where b.sql_id=c.sql_id)
and b.parsing_schema_name not in ('XS$NULL','APEX_PUBLIC_USER','SPATIAL_CSW_ADMIN_USR','ORACLE_OCM','MDDATA','DIP','SPATIAL_WFS_ADMIN_USR','DBSNMP','FLOWS_FILES','MDSYS','WMSYS','ORDDATA','CTXSYS','ANONYMOUS','SI_INFORMTN_SCHEMA','ORDSYS','EXFSYS','APPQOSSYS','APEX_030200','OWBSYS_AUDIT','SYSMAN','XDB','ORDPLUGINS','OWBSYS','OLAPSYS','SYS','SYSTEM','OUTLN','MGMT_VIEW','SCOTT','TSMSYS')
and b.executions_total>10 group by sql_id) d
where a.sql_id=d.sql_id and a.EXECUTIONS>10
and a.elapsed_time/a.executions>10*d.avg_us
and a.elapsed_time/a.executions>100000 and last_active_time >sysdate - 1/24/8
----------------重建索引
sqlplus / as sysdba
