Oracle性能问题sql调优脚本集

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

来自：《Oracle 11g性能优化攻略》

引用地址：http://www.cnblogs.com/raol/p/performance_tuning.html

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

--检查报警日志->性能差的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;
--索引监控
SELECT UA.INDEX_NAME,UA.TABLE_NAME,UA.MONITORING,UA.USED FROM V$OBJECT_USAGE UA WHERE UA.USED='NO' AND UA.TABLE_NAME LIKE 'FM%' ;
SELECT IO.NAME,T.NAME,DECODE(BITAND(I.FLAGS,65535),0,'NO','YES'),DECODE(BITAND(OU.FLAGS,1),0,'NO','YES'),OU.START_MONITORING,OU.END_MONITORING FROM SYS.OBJ$ IO,SYS.OBJ$ T,SYS.IND$ I,SYS.OBJECT_USAGE OU WHERE I.OBJ#=OU.OBJ# AND IO.OBJ#=OU.OBJ# AND T.OBJ#=I.BO#;
--加快索引的创建速度
ALTER INDEX ISS_FM_ALARM_CLEARID REBUILD PARALLEL/*并行*/ NOLOGGING/*少日志*/;
--keep缓冲池(长期占有)、recycle缓冲池(一天用那么一两次且是大对象),查询多少个数据块在缓冲区
SELECT O.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS FROM DBA_OBJECTS O,V$BH V WHERE O.DATA_OBJECT_ID = V.OBJD AND O.OWNER='ISS' GROUP BY O.OBJECT_NAME ORDER BY COUNT(*) DESC;
--统计级别AWR
SHOW PARAMETER STATISTICS_LEVEL;
--保存30=43200分钟,天时间间隔30分钟。
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(RETENTION=>43200，INTERVAL=>30);
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(START_SNAP_ID=>256,END_SNAP_ID=>288,BASELINE_NAME=>'BASELINE #1');

SELECT BASELINE_NAME,START_SNAP_ID, TO_CHAR(START_SNAP_TIME,'YYYY-MM-DD HH24:MI') START_TIME, END_SNAP_ID,TO_CHAR(END_SNAP_TIME,'YYYY-MM-DD HH24:MI') END_TIME,EXPIRATION FROM DAB_HIST_BASELINE ORDER BY BASELINE_ID;
--过去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 WAIT_CLASS,SUM(TIME_WAITED), SUM(TIME_WAITED)/SUM(TOTAL_WAITS) SUM_WAITS FROM V$SYSTEM_WAIT_CLASS GROUP BY WAIT_CLASS ORDER BY 3 DESC;
--等待事件类型
SELECT WAIT_CLASS,NAME FROM V$EVENT_NAME WHERE NAME LIKE 'enq%' AND WAIT_CLASS <> 'Other' ORDER BY 1 DESC;
--找出是那种等待事件导致总等待事件过长
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 A.SID,A.EVENT,A.TOTAL_WAITS,A.TIME_WAITED,A.AVERAGE_WAIT FROM V$SESSION_EVENT A,V$SESSION B WHERE A.TIME_WAITED >0 AND A.SID=B.SID AND B.USERNAME IS NOT NULL AND A.EVENT='enq: TX - row lock contention' ORDER BY 5 DESC;
--最近被锁住的会话
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查看对应连接正在运行的sql
select /*+ PUSH_SUBQ */ command_type,sql_text,sharable_mem, persistent_mem,runtime_mem,sorts,version_count,loaded_versions,open_versions,users_opening,executions, users_executing,loads,first_load_time,invalidations, parse_calls,disk_reads,buffer_gets,rows_processed,sysdate start_time,sysdate finish_time,'>'|| address sql_address,'N' status from v$sqlarea where address = (select sql_address from v$session where sid='&&SID');
--根据pid查看sql语句:
select sql_text from v$sql where address in(select sql_address from v$session where sid in(select sid from v$session where paddr in (select addr from v$process where spid=&pid)));
--根据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)');
--查看数据库的创建日期和归档方式:
Select Created, Log_Mode, Log_Mode From V$Database;

 

 

附图：oracle性能问题检修流程

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

来自：《Oracle 11g性能优化攻略》

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