概述

之前看的Oracle 11g性能优化攻略，整理如下：

sql整理

--检查报警日志->性能差的sql->会话满了->阻塞->IO->锁->CPU->FRA闪回恢复区->hanganalyze

--DEFAULT_PERMANENT_TABLESPACE 默认的永久表空间 DEFAULT_TEMP_TABLESPACE 默认的临时表空间

select * from DATABASE_PROPERTIES where property_name IN('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');

--如何查看当前SQL*PLUS用户的sid和serial#:

select sid, serial#, status from v$session where audsid=userenv('sessionid');

--UNDO表空间的设置

SELECT NAME,VALUE FROM V$PARAMETER WHERE NAME IN('undo_tablespace','undo_management');

--是否使用本地管理及ASM

SELECT TABLESPACE_NAME, EXTENT_MANAGEMENT, SEGMENT_SPACE_MANAGEMENT,BIGFILE FROM DBA_TABLESPACES;

--/*+APPEND */ INSERT/*+APPEND */ INTO TABLE SELECT * FROM

SELECT /*RECENTSQL */SQL_ID,CHILD_NUMBER,HASH_VALUE,ADDRESS,EXECUTIONS,SQL_TEXT FROM V$SQL WHERE PARSING_USER_ID = (SELECT USER_ID FROM ALL_USERS WHERE USERNAME = 'ISS') AND COMMAND_TYPE IN (2,3,6,7,189) AND UPPER(SQL_TEXT) NOT LIKE UPPER('%RECENTSQL%');

--虚拟索引虚拟索引的目的，是在不必耗cpu,耗IO以及消耗大量存储空间去实际创建索引的情况，来判断一个索引是否能够对sql优化起到作用。

--CREATE INDEX IDX_NAME TABLE_NAME(COL) NOSEGMENT;

--加快索引的创建速度

ALTER INDEX ISS_FM_ALARM_CLEARID REBUILD PARALLEL/*并行*/ NOLOGGING/*少日志*/;

--过去15分钟的等待事件

SELECT ASH.EVENT,SUM(ASH.WAIT_TIME+ASH.TIME_WAITED) TITAL_WAIT FROM V$ACTIVE_SESSION_HISTORY ASH WHERE ASH.SAMPLE_TIME BETWEEN SYSDATE-1/24/4 AND SYSDATE GROUP BY ASH.EVENT ORDER BY 2 DESC;

--哪些用户经历了等待

SELECT S.SID,S.USERNAME,SUM(a.WAIT_TIME+a.TIME_WAITED) TOTAL_WAIT_TIME FROM V$ACTIVE_SESSION_HISTORY A,V$SESSION S WHERE A.SAMPLE_TIME BETWEEN SYSDATE-30/2880 AND SYSDATE AND A.SESSION_ID =S.SID GROUP BY S.SID, S.USERNAME ORDER BY TOTAL_WAIT_TIME DESC;

--等待事件最长的sql

SELECT A.USER_ID,U.USERNAME,TO_NCHAR(S.SQL_TEXT),SUM(A.WAIT_TIME+A.TIME_WAITED) TOTAL_WAIT_TIME FROM V$ACTIVE_SESSION_HISTORY A, V$SQLAREA S,DBA_USERS U WHERE A.SAMPLE_TIME BETWEEN SYSDATE-30/2880 AND SYSDATE AND A.SQL_ID=S.SQL_ID AND A.USER_ID=U.USER_ID GROUP BY A.USER_ID,S.SQL_TEXT,U.USERNAME;

--由于锁定所花费的等待时间

SELECT WAIT_CLASS,EVENT,TIME_WAITED/100 TIME_SECS FROM V$SYSTEM_EVENT E WHERE E.WAIT_CLASS<>'Idle' AND TIME_WAITED >0 UNION SELECT 'Time Model',STAT_NAME NAME, ROUND((value/1000000),2) TIME_SECS FROM V$SYS_TIME_MODEL WHERE STAT_NAME NOT IN('background elapsed time','background cpu time') ORDER BY 3 DESC;

--过去15分钟使用最多cpu的会话

SELECT * FROM (SELECT S.USERNAME,S.MODULE,S.SID,S.SERIAL#,COUNT(*) FROM V$ACTIVE_SESSION_HISTORY H, V$SESSION S WHERE H.SESSION_ID=S.SID AND H.SESSION_SERIAL#=S.SERIAL# AND SESSION_STATE='ON CPU' AND SAMPLE_TIME > SYSDATE-INTERVAL '15' MINUTE GROUP BY S.USERNAME,S.MODULE,S.SID,S.SERIAL# ORDER BY COUNT(*) DESC );

--查询数据库等待百分比

SELECT METRIC_NAME,VALUE FROM V$SYSMETRIC WHERE METRIC_NAME IN('Database CPU Time Ratio','Database Wait Time Ratio') AND INTSIZE_CSEC=(SELECT MAX(INTSIZE_CSEC) FROM V$SYSMETRIC);

--找出是哪种等待事件导致总等待事件过长

SELECT A.EVENT,A.TOTAL_WAITS,A.TIME_WAITED,A.AVERAGE_WAIT FROM V$SYSTEM_EVENT A, V$EVENT_NAME B, V$SYSTEM_WAIT_CLASS C WHERE A.EVENT_ID=B.EVENT_ID AND B.WAIT_CLASS#=C.WAIT_CLASS# AND C.WAIT_CLASS IN('Application','Administrative') ORDER BY 3 DESC;

--日志文件commit过多次数多而短

--enq: TM - contention 锁表

SELECT * FROM (SELECT C.TABLE_NAME,CO.COLUMN_NAME,CO.POSITION COLUMN_POSITION FROM USER_CONSTRAINTS C,USER_CONS_COLUMNS CO WHERE C.CONSTRAINT_NAME=CO.CONSTRAINT_NAME AND C.CONSTRAINT_TYPE='R' MINUS SELECT UI.TABLE_NAME,UIC.COLUMN_NAME,UIC.COLUMN_POSITION COLUMN_POSITION FROM USER_INDEXES UI, USER_IND_COLUMNS UIC WHERE UI.INDEX_NAME=UIC.INDEX_NAME ) ORDER BY TABLE_NAME, COLUMN_POSITION;

--最近被锁住的会话

SELECT TO_CHAR(H.SAMPLE_TIME,'HH24:MI:SS' ) TIME,H.SESSION_ID SID,DECODE(H.SESSION_STATE,'WAITTING',H.EVENT,H.SESSION_STATE ) STATE,H.SQL_ID,H.BLOCKING_SESSION BLOCKER FROM V$ACTIVE_SESSION_HISTORY H,DBA_USERS U WHERE U.USER_ID=H.USER_ID AND H.SAMPLE_TIME=SYSTIMESTAMP-(2/1440);

--引起最多等待的sql

SELECT ASH.USER_ID,U.USERNAME, S.SQL_TEXT,SUM(ASH.WAIT_TIME+ASH.TIME_WAITED ) TTL_WAIT_TIME FROM V$ACTIVE_SESSION_HISTORY ASH,V$SQLAREA S,DBA_USERS U WHERE ASH.SAMPLE_TIME BETWEEN SYSDATE-60/2880 AND SYSDATE AND ASH.SQL_ID=S.SQL_ID AND ASH.USER_ID=U.USER_ID GROUP BY ASH.USER_ID,S.SQL_TEXT,U.USERNAME ORDER BY TTL_WAIT_TIME DESC;

--捕捉运行很久的SQL

select LO.START_TIME,LO.TARGET, username,sid,opname,round(sofar*100 / totalwork,0) || '%' as progress,time_remaining,sql_text from v$session_longops lo, v$sql where time_remaining <> 0 and sql_address=address and sql_hash_value = hash_value;

--某个会话在等待什么

SELECT EVENT,COUNT(*) FROM V$SESSION_WAIT GROUP BY EVENT ORDER BY COUNT(*) DESC;

SELECT EVENT,STATE,SECONDS_IN_WAIT SIW FROM V$SESSION_WAIT W WHERE W.SID='2100';

--监控临时表空间

SELECT * FROM (SELECT A.TABLESPACE_NAME,SUM(A.BYTES/1024/1024) ALLOCATED_MB FROM DBA_TEMP_FILES A WHERE A.TABLESPACE_NAME=UPPER('&&TEMP_TSNAME') GROUP BY A.TABLESPACE_NAME ) X,(SELECT SUM(B1.BYTES_USED/1024/1024 ) USED_MB, SUM(B1.BYTES_FREE/1024/1024 ) FREE_MB FROM V$TEMP_SPACE_HEADER B1 WHERE B1.TABLESPACE_NAME=UPPER('&&TEMP_TSNAME') GROUP BY B1.TABLESPACE_NAME );

--哪条sql使用了temp表空间

SELECT S.SID||',',S.SERIAL# SID_SERIAL,S.USERNAME,O.BLOCKS*T.BLOCK_SIZE/1024/1024 MB_USED,O.TABLESPACE,O.SQLADDR,H.HASH_VALUE,H.SQL_TEXT FROM V$SORT_USAGE O, V$SESSION S,V$SQLAREA H,DBA_TABLESPACES T WHERE O.SESSION_ADDR = S.SADDR AND O.SQLADDR=H.ADDRESS(+) AND O.TABLESPACE = T.TABLESPACE_NAME ORDER BY S.SID;

--临时表空间使用率

SELECT S.SID||',',S.SERIAL# SID_SERIAL,S.USERNAME,S.OSUSER,P.SPID,S.MODULE,S.PROGRAM,SUM(O.BLOCKS )*T.BLOCK_SIZE/1024/1024 MB_USED,O.TABLESPACE,COUNT(*) SORTS FROM V$SORT_USAGE O, V$SESSION S,V$PROCESS P,DBA_TABLESPACES T WHERE O.SESSION_ADDR = S.SADDR AND S.PADDR=P.ADDR AND O.TABLESPACE = T.TABLESPACE_NAME GROUP BY S.SID,S.SERIAL#,S.USERNAME,S.OSUSER,P.SPID,S.MODULE,S.PROGRAM,T.BLOCK_SIZE,O.TABLESPACE ORDER BY S.SID;

--查看一下哪个用户在用临时段:

SELECT sid,serial#,sql_address,machine,program,tablespace,segtype,contents FROM v$session se,v$sort_usage su WHERE se.saddr=su.session_addr;

--查找前十条性能差的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 ;

--等待时间最多的5个系统等待事件的获取：

select * from (select * from v$system_event where event not like 'SQL%' order by total_waits desc) where rownum<=5;

--如何查看一下某个shared_server正在忙什么:

SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text FROM v$session a,v$process b,v$sqltext c WHERE b.spid=13161 AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece;

--数据库共享池性能检查:

Select namespace,gets,gethitratio,pins,pinhitratio,reloads,Invalidations from v$librarycache where namespace in('SQLAREA','TABLE/PROCEDURE','BODY','TRIGGER');

--检查数据字典的命中率:

select 1-sum(getmisses)/sum(gets) "data dictionary hit ratio" from v$rowcache;

--查看耗资源的进程(top session):

select s.schemaname schema_name,decode(sign(48 - command), 1, to_char(command), 'Action Code #' || to_char(command) ) action,status session_status,s.osuser os_user_name,s.sid,p.spid,s.serial# serial_num,nvl(s.username,'[Oracle process]') user_name,s.terminal terminal,s.program program,st.value criteria_value from v$sesstat st,v$session s,v$process p where st.sid = s.sid and st.statistic# = to_number('38') and ('ALL'='ALL' or s.status ='ALL') and p.addr=s.paddr order by st.value desc,p.spid asc,s.username asc,s.osuser asc;

--监控当前数据库谁在运行什么SQL语句:

SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece;

--如何查出前台正在发出的sql语句:

select user_name,sql_text from v$open_cursor where sid in(select sid from (select sid,serial# from v$session where status='ACTIVE'));

--查看锁（lock）情况:

select /*+ RULE */ ls.osuser os_user_name, ls.username user_name,decode(ls.type,'RW','Row wait enqueue lock','TM','DML enqueue lock','TX','Transaction enqueue lock','UL','User supplied lock') lock_type,o.object_name object,decode(ls.lmode, 1, null, 2,'Row Share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive',null)lock_mode,o.owner,ls.sid,ls.serial# serial_num,ls.id1,ls.id2 from sys.dba_objects o,(select s.osuser,s.username,l.type,l.lmode,s.sid,s.serial#,l.id1,l.id2 from v$session s,v$lock l where s.sid=l.sid)ls where o.object_id=ls.id1 and o.owner<>'SYS' order by o.owner, o.object_name;

--查看占io较大的正在运行的session:

SELECT se.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,se.program, se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changes FROM v$session se,v$session_wait st,v$sess_io si,v$process pr WHERE st.sid=se.sid AND st.sid=si.sid AND se.PADDR=pr.ADDR AND se.sid>6 AND st.wait_time=0 AND st.event NOT LIKE '%SQL%' ORDER BY physical_reads DESC;

--查看表空间数据文件的读写性能:(注意：如果phyblkrd与phyrds很接近的话，则表明这个表空间中存在全表扫描的表，这些表需要调整索引或优化SQL语句)

Select name,phyrds,phywrts,avgiotim,miniotim,maxiowtm,maxiortm from v$filestat,v$datafile where v$filestat.file#=v$datafile.file#;

Select fs.name name,f.phyrds,f.phyblkrd,f.phywrts,f.phyblkwrt ,f.readtim,f.writetim from v$filestat f, v$datafile fs where f.file# = fs.file# order by fs.name;

--根据SID找ORACLE的某个进程:

select pro.spid from v$session ses,v$process pro where ses.sid=&sid and ses.paddr=pro.addr;

--查询表空间的碎片程度:

select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name having count(tablespace_name)>10;

--查看排序段的性能:

SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');

后面会分享更多关于运维DBA的实用脚本，感兴趣的朋友可以关注下！！