Oracle日常操作: Performance

/*表信息*/
select * from dba_tables where table_name=upper('TNAME');
select bytes/1048576 from dba_segments where owner=upper('NAME')
and segment_name=upper('TNAME');

/*锁阻塞*/
SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
          sid sess, id1, id2, lmode, request, type
   FROM V$LOCK
 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
   ORDER BY id1, request;

/*非空闲等待*/
select s.sid,s.SERIAL#,s.USERNAME,s.MACHINE,s.OSUSER,w.event
from v$session s, v$session_wait w
where s.sid=w.sid
and w.event not like '%message%';

/*长事务*/
select s.sid,s.serial#,s.username,s.PROGRAM,t.START_TIME from v$transaction t, v$session s
where s.TADDR=t.ADDR
order by t.START_TIME asc;

/*SQL*/
select c.SQL_TEXT, c.FIRST_LOAD_TIME
from v$sqlarea c, v$session d
where c.ADDRESS= d.SQL_ADDRESS
and d.sid=&SID;

/*SPID*/
select f.SPID
from v$session e, v$process f
where e.PADDR=f.ADDR and
e.SID=&SID;

/*收集表的统计信息*/
exec dbms_stats.gather_table_stats(ownname => 'ORCL',tabname => 'TEST',method_opt=>'for all indexed columns size 254',estimate_percent=>dbms_stats.auto_sample_size,cascade => true);

/*分析索引，表拥有share锁*/
analyze index IND_PK validate structure;
SELECT NAME,(DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100 AS index_usage FROM index_stats;
alter index index_name rebuild online;

/*手动分配排序区，加快rebuild*/
ALTER SESSION FORCE PARALLEL QUERY | DDL | DML PARALLEL 5;
alter session set workarea_size_policy='manual';
alter session set sort_area_size=82428800;
alter index index_name rebuild online;

/*10046 trace SQL*/
alter session set events '10046 trace name context forever,level 12';
Your SQL;
alter session set events '10046 trace name context off';

/*查看表的行链接*/
analyze table TEAPP.MASTER_RESULT compute statistics;
select a.table_name,a.num_rows,a.blocks,a.empty_blocks,a.avg_space,a.chain_cnt,a.last_analyzed
from dba_tables a where a.table_name='MYTBL';

/*获取Hang analyze */
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug hanganalyze 3
#wait 3 minutes
SQL> oradebug hanganalyze 3

/*获取System State*/
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump systemstate 10
#wait 3 minutes
SQL> oradebug dump systemstate 10

/*长操作*/
select sid,OPNAME,sofar,TOTALWORK,trunc(sofar/totalwork*100,2)||'%' as perwork,elapsed_seconds,message
from v$session_longops where sofar!=totalwork;