Oracle性能问题sql调优脚本集

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103

--检查报警日志->性能差的sql->会话满了->阻塞->IO->锁->CPU->FRA闪回恢复区->hanganalyze --DEFAULT_PERMANENT_TABLESPACE 默认的永久表空间 DEFAULT_TEMP_TABLESPACE 默认的临时表空间 select* fromDATABASE_PROPERTIES whereproperty_name IN('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE'); --如何查看当前SQL*PLUS用户的sid和serial#: selectsid, serial#, status fromv$session whereaudsid=userenv('sessionid'); --UNDO表空间的设置 SELECTNAME,VALUE FROMV$PARAMETER WHERENAMEIN('undo_tablespace','undo_management'); --是否使用本地管理及ASM SELECTTABLESPACE_NAME, EXTENT_MANAGEMENT, SEGMENT_SPACE_MANAGEMENT,BIGFILE FROMDBA_TABLESPACES; --/*+APPEND */ INSERT/*+APPEND */ INTO TABLE SELECT * FROM SELECT/*RECENTSQL */SQL_ID,CHILD_NUMBER,HASH_VALUE,ADDRESS,EXECUTIONS,SQL_TEXT FROMV$SQL WHEREPARSING_USER_ID = (SELECTUSER_ID FROMALL_USERS WHEREUSERNAME = 'ISS') ANDCOMMAND_TYPE IN(2,3,6,7,189) ANDUPPER(SQL_TEXT) NOTLIKEUPPER('%RECENTSQL%'); --虚拟索引虚拟索引的目的，是在不必耗cpu,耗IO以及消耗大量存储空间去实际创建索引的情况，来判断一个索引是否能够对sql优化起到作用。 --CREATE INDEX IDX_NAME TABLE_NAME(COL) NOSEGMENT; --索引监控 SELECTUA.INDEX_NAME,UA.TABLE_NAME,UA.MONITORING,UA.USED FROMV$OBJECT_USAGE UA WHEREUA.USED='NO'ANDUA.TABLE_NAME LIKE'FM%'; SELECTIO.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 FROMSYS.OBJ$ IO,SYS.OBJ$ T,SYS.IND$ I,SYS.OBJECT_USAGE OU WHEREI.OBJ#=OU.OBJ# ANDIO.OBJ#=OU.OBJ# ANDT.OBJ#=I.BO#; --加快索引的创建速度 ALTERINDEXISS_FM_ALARM_CLEARID REBUILD PARALLEL/*并行*/NOLOGGING/*少日志*/; --keep缓冲池(长期占有)、recycle缓冲池(一天用那么一两次且是大对象),查询多少个数据块在缓冲区 SELECTO.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS FROMDBA_OBJECTS O,V$BH V WHEREO.DATA_OBJECT_ID = V.OBJD ANDO.OWNER='ISS'GROUPBYO.OBJECT_NAME ORDERBYCOUNT(*) DESC; --统计级别AWR SHOW PARAMETER STATISTICS_LEVEL; --保存30=43200分钟,天时间间隔30分钟。 EXECDBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(RETENTION=>43200，INTERVAL=>30); EXECDBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(START_SNAP_ID=>256,END_SNAP_ID=>288,BASELINE_NAME=>'BASELINE #1'); SELECTBASELINE_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 FROMDAB_HIST_BASELINE ORDERBYBASELINE_ID; --过去15分钟的等待事件 SELECTASH.EVENT,SUM(ASH.WAIT_TIME+ASH.TIME_WAITED) TITAL_WAIT FROMV$ACTIVE_SESSION_HISTORY ASH WHEREASH.SAMPLE_TIME BETWEENSYSDATE-1/24/4 ANDSYSDATE GROUPBYASH.EVENT ORDERBY2 DESC; --那些用户经历了等待 SELECTS.SID,S.USERNAME,SUM(a.WAIT_TIME+a.TIME_WAITED) TOTAL_WAIT_TIME FROMV$ACTIVE_SESSION_HISTORY A,V$SESSION S WHEREA.SAMPLE_TIME BETWEENSYSDATE-30/2880 ANDSYSDATE ANDA.SESSION_ID =S.SID GROUPBYS.SID, S.USERNAME ORDERBYTOTAL_WAIT_TIME DESC; --等待事件最长的sql SELECTA.USER_ID,U.USERNAME,TO_NCHAR(S.SQL_TEXT),SUM(A.WAIT_TIME+A.TIME_WAITED) TOTAL_WAIT_TIME FROMV$ACTIVE_SESSION_HISTORY A, V$SQLAREA S,DBA_USERS U WHEREA.SAMPLE_TIME BETWEENSYSDATE-30/2880 ANDSYSDATE ANDA.SQL_ID=S.SQL_ID ANDA.USER_ID=U.USER_ID GROUPBYA.USER_ID,S.SQL_TEXT,U.USERNAME; --由于锁定所花费的等待时间 SELECTWAIT_CLASS,EVENT,TIME_WAITED/100 TIME_SECS FROMV$SYSTEM_EVENT E WHEREE.WAIT_CLASS<>'Idle'ANDTIME_WAITED >0 UNIONSELECT'Time Model',STAT_NAME NAME, ROUND((value/1000000),2) TIME_SECS FROMV$SYS_TIME_MODEL WHERESTAT_NAME NOTIN('background elapsed time','background cpu time') ORDERBY3 DESC; --过去15分钟使用最多cpu的会话 SELECT* FROM(SELECTS.USERNAME,S.MODULE,S.SID,S.SERIAL#,COUNT(*) FROMV$ACTIVE_SESSION_HISTORY H, V$SESSION S WHEREH.SESSION_ID=S.SID ANDH.SESSION_SERIAL#=S.SERIAL# ANDSESSION_STATE='ON CPU'ANDSAMPLE_TIME > SYSDATE-INTERVAL '15'MINUTEGROUPBYS.USERNAME,S.MODULE,S.SID,S.SERIAL# ORDERBYCOUNT(*) DESC); --查询数据库等待百分比 SELECTMETRIC_NAME,VALUE FROMV$SYSMETRIC WHEREMETRIC_NAME IN('Database CPU Time Ratio','Database Wait Time Ratio') ANDINTSIZE_CSEC=(SELECTMAX(INTSIZE_CSEC) FROMV$SYSMETRIC); --当前等待情况 SELECTWAIT_CLASS,SUM(TIME_WAITED), SUM(TIME_WAITED)/SUM(TOTAL_WAITS) SUM_WAITS FROMV$SYSTEM_WAIT_CLASS GROUPBYWAIT_CLASS ORDERBY3 DESC; --等待事件类型 SELECTWAIT_CLASS,NAMEFROMV$EVENT_NAME WHERENAMELIKE'enq%'ANDWAIT_CLASS <> 'Other'ORDERBY1 DESC; --找出是那种等待事件导致总等待事件过长 SELECTA.EVENT,A.TOTAL_WAITS,A.TIME_WAITED,A.AVERAGE_WAIT FROMV$SYSTEM_EVENT A, V$EVENT_NAME B, V$SYSTEM_WAIT_CLASS C WHEREA.EVENT_ID=B.EVENT_ID ANDB.WAIT_CLASS#=C.WAIT_CLASS# ANDC.WAIT_CLASS IN('Application','Administrative') ORDERBY3 DESC; --日志文件commit过多次数多而短 --enq: TM - contention 锁表 SELECT* FROM(SELECTC.TABLE_NAME,CO.COLUMN_NAME,CO.POSITION COLUMN_POSITION FROMUSER_CONSTRAINTS C,USER_CONS_COLUMNS CO WHEREC.CONSTRAINT_NAME=CO.CONSTRAINT_NAME ANDC.CONSTRAINT_TYPE='R'MINUS SELECTUI.TABLE_NAME,UIC.COLUMN_NAME,UIC.COLUMN_POSITION COLUMN_POSITION FROMUSER_INDEXES UI, USER_IND_COLUMNS UIC WHEREUI.INDEX_NAME=UIC.INDEX_NAME ) ORDERBYTABLE_NAME, COLUMN_POSITION; --深入会话级 SELECTA.SID,A.EVENT,A.TOTAL_WAITS,A.TIME_WAITED,A.AVERAGE_WAIT FROMV$SESSION_EVENT A,V$SESSION B WHEREA.TIME_WAITED >0 ANDA.SID=B.SID ANDB.USERNAME ISNOTNULLANDA.EVENT='enq: TX - row lock contention'ORDERBY5 DESC; --最近被锁住的会话 SELECTTO_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 FROMV$ACTIVE_SESSION_HISTORY H,DBA_USERS U WHEREU.USER_ID=H.USER_ID ANDH.SAMPLE_TIME=SYSTIMESTAMP-(2/1440); --引起最多等待的sql SELECTASH.USER_ID,U.USERNAME, S.SQL_TEXT,SUM(ASH.WAIT_TIME+ASH.TIME_WAITED ) TTL_WAIT_TIME FROMV$ACTIVE_SESSION_HISTORY ASH,V$SQLAREA S,DBA_USERS U WHEREASH.SAMPLE_TIME BETWEENSYSDATE-60/2880 ANDSYSDATE ANDASH.SQL_ID=S.SQL_ID ANDASH.USER_ID=U.USER_ID GROUPBYASH.USER_ID,S.SQL_TEXT,U.USERNAME ORDERBYTTL_WAIT_TIME DESC; --捕捉运行很久的SQL selectLO.START_TIME,LO.TARGET, username,sid,opname,round(sofar*100 / totalwork,0) || '%'asprogress,time_remaining,sql_text fromv$session_longops lo, v$sql wheretime_remaining <> 0 andsql_address=address andsql_hash_value = hash_value; --某个会话在等待什么 SELECTEVENT,COUNT(*) FROMV$SESSION_WAIT GROUPBYEVENT ORDERBYCOUNT(*) DESC; SELECTEVENT,STATE,SECONDS_IN_WAIT SIW FROMV$SESSION_WAIT W WHEREW.SID='2100'; --监控临时表空间 SELECT* FROM(SELECTA.TABLESPACE_NAME,SUM(A.BYTES/1024/1024) ALLOCATED_MB FROMDBA_TEMP_FILES A WHEREA.TABLESPACE_NAME=UPPER('&&TEMP_TSNAME') GROUPBYA.TABLESPACE_NAME ) X,(SELECTSUM(B1.BYTES_USED/1024/1024 ) USED_MB, SUM(B1.BYTES_FREE/1024/1024 ) FREE_MB FROMV$TEMP_SPACE_HEADER B1 WHEREB1.TABLESPACE_NAME=UPPER('&&TEMP_TSNAME') GROUPBYB1.TABLESPACE_NAME ); --那条sql使用了temp表空间 SELECTS.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 FROMV$SORT_USAGE O, V$SESSION S,V$SQLAREA H,DBA_TABLESPACES T WHEREO.SESSION_ADDR = S.SADDR ANDO.SQLADDR=H.ADDRESS(+) ANDO.TABLESPACE = T.TABLESPACE_NAME ORDERBYS.SID; --临时表空间使用率 SELECTS.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 FROMV$SORT_USAGE O, V$SESSION S,V$PROCESS P,DBA_TABLESPACES T WHEREO.SESSION_ADDR = S.SADDR ANDS.PADDR=P.ADDR ANDO.TABLESPACE = T.TABLESPACE_NAME GROUPBYS.SID,S.SERIAL#,S.USERNAME,S.OSUSER,P.SPID,S.MODULE,S.PROGRAM,T.BLOCK_SIZE,O.TABLESPACE ORDERBYS.SID; --查看一下哪个用户在用临时段: SELECTsid,serial#,sql_address,machine,program,tablespace,segtype,contents FROMv$session se,v$sort_usage su WHEREse.saddr=su.session_addr; --查找前十条性能差的sql: SELECT* FROM(SELECTPARSING_USER_ID, EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS, sql_text FROMv$sqlarea ORDERBYdisk_reads DESC) WHEREROWNUM<10 ; --等待时间最多的5个系统等待事件的获取： select* from(select* fromv$system_event whereevent notlike'SQL%'orderbytotal_waits desc) whererownum<=5; --如何查看一下某个shared_server正在忙什么: SELECTa.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text FROMv$session a,v$process b,v$sqltext c WHEREb.spid=13161 ANDb.addr=a.paddr ANDa.sql_address=c.address(+) ORDERBYc.piece; --数据库共享池性能检查: Selectnamespace,gets,gethitratio,pins,pinhitratio,reloads,Invalidations fromv$librarycache wherenamespace in('SQLAREA','TABLE/PROCEDURE','BODY','TRIGGER'); --检查数据字典的命中率: select1-sum(getmisses)/sum(gets) "data dictionary hit ratio"fromv$rowcache; --查看耗资源的进程(top session): selects.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 fromv$sesstat st,v$session s,v$process p wherest.sid = s.sid andst.statistic# = to_number('38') and('ALL'='ALL'ors.status ='ALL') andp.addr=s.paddr orderbyst.value desc,p.spid asc,s.username asc,s.osuser asc; --监控当前数据库谁在运行什么SQL语句: SELECTosuser, username, sql_text fromv$session a, v$sqltext b wherea.sql_address =b.address orderbyaddress, piece; --如何查出前台正在发出的sql语句: selectuser_name,sql_text fromv$open_cursor wheresid in(selectsid from(selectsid,serial# fromv$session wherestatus='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 fromsys.dba_objects o,(selects.osuser,s.username,l.type,l.lmode,s.sid,s.serial#,l.id1,l.id2 fromv$session s,v$lock l wheres.sid=l.sid)ls whereo.object_id=ls.id1 ando.owner<>'SYS'orderbyo.owner, o.object_name; --查看占io较大的正在运行的session: SELECTse.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 FROMv$session se,v$session_wait st,v$sess_io si,v$process pr WHEREst.sid=se.sid ANDst.sid=si.sid ANDse.PADDR=pr.ADDR ANDse.sid>6 ANDst.wait_time=0 ANDst.event NOTLIKE'%SQL%'ORDERBYphysical_reads DESC; --查看表空间数据文件的读写性能:(注意：如果phyblkrd与phyrds很接近的话，则表明这个表空间中存在全表扫描的表，这些表需要调整索引或优化SQL语句) Selectname,phyrds,phywrts,avgiotim,miniotim,maxiowtm,maxiortm fromv$filestat,v$datafile wherev$filestat.file#=v$datafile.file#; Selectfs.namename,f.phyrds,f.phyblkrd,f.phywrts,f.phyblkwrt ,f.readtim,f.writetim fromv$filestat f, v$datafile fs wheref.file# = fs.file# orderbyfs.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 fromv$sqlarea whereaddress = (selectsql_address fromv$session wheresid='&&SID'); --根据pid查看sql语句: selectsql_text fromv$sql whereaddress in(selectsql_address fromv$session wheresid in(selectsid fromv$session wherepaddr in(selectaddr fromv$process wherespid=&pid))); --根据SID找ORACLE的某个进程: selectpro.spid fromv$session ses,v$process pro whereses.sid=&sid andses.paddr=pro.addr; --查询表空间的碎片程度: selecttablespace_name,count(tablespace_name) fromdba_free_space groupbytablespace_name havingcount(tablespace_name)>10; --查看排序段的性能: SELECTname, value FROMv$sysstat WHEREnameIN('sorts (memory)', 'sorts (disk)'); --查看数据库的创建日期和归档方式: SelectCreated, Log_Mode, Log_Mode FromV$Database;

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