Oracle性能问题sql调优脚本集
---------------------------------------------------------------------------------------------------------
来自:《Oracle 11g性能优化攻略》
引用地址:http://www.cnblogs.com/raol/p/performance_tuning.html
---------------------------------------------------------------------------------------------------------
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 * 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性能优化攻略》
---------------------------------------------------------------------------------------------------------
【推荐】还在用 ECharts 开发大屏?试试这款永久免费的开源 BI 工具!
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步