Oracle数据库性能监控语句汇总

1、Oracle数据库性能监控 2016年1月Oracle数据库性能监控语句一、查看临时表空间使用情况2二、查看使用临时表空间的SQL2三、收缩临时表空间3四、重建索引3五、查看表空间使用情况3六、查询表空间的总容量4七、查询表空间使用率4八、查找当前表级锁4九、监控当前数据库谁在运行什么SQL语句5十、找使用CPU多的用户session5十一、查看死锁信息5十二、具有最高等待的对象5十三、查看具有最高等待的对象6十四、查看等待最多的SQL6十五、显示正在等待锁的所有会话7十七、查数据库中正在执行的SQL7十八、每天执行慢的SQL8十九、查看非绑定变量的SQL9二十、查看LOG切换频率10二十一、

2、查看SQL执行进度10二十二、查询外键字段在主键表中没有索引的11二十三、 查看软硬解析，游标数12二十四、查看未提交的事物的会话和锁的对象12二十五、通过系统中PID去数据库中找执行的SQL13二十六、序列/索引差异 比对结果后的创建语句13二十七、查看热点块的对象15二十八、查看某用户表大小/总数情况15二十九、 重新编译失效存储/包语句16三十、 Oracle 查看各表空间使用情况和最大最小块16三十一、 Oracle 查看TEMP表空间使用情况17三十二、 Oracle 查看回滚进度情况用的几个SQL17三十三、Oracle 查询锁之间的依赖关系18三十四、Oracle 查找锁之间依赖

3、关系的最源头SID20写于2016年1月，所有语句经过测试一、查看临时表空间使用情况Selectf.tablespace_name,sum(f.bytes_free + f.bytes_used)/1024/1024/1024 "total GB",sum(f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)/1024/1024/1024 "Free GB",sum(nvl(p.bytes_used, 0)/1024/1024/1024 "Used GB"from sys.v_$tem

4、p_space_header f, dba_temp_files d, sys.v_$temp_extent_pool pwhere f.tablespace_name(+) = d.tablespace_nameand f.file_id(+) = d.file_idand p.file_id(+) = d.file_idgroup byf.tablespace_name二、查看使用临时表空间的SQLSelect se.username,se.sid,su.extents,su.blocks * to_number(rtrim(p.value) as Space,tablespace,seg

5、type,sql_textfrom v$sort_usage su, v$parameter p, v$session se, v$sql swhere = 'db_block_size'and su.session_addr = se.saddrand s.hash_value = su.sqlhashand s.address = su.sqladdrorder by se.username, se.sid三、收缩临时表空间alter tablespace temp shrink space;alter tablespace temp shrink tempf

6、ile ''四、重建索引alter index PK_CROSSRELATION rebuild;五、查看表空间使用情况SELECT UPPER(F.TABLESPACE_NAME) "表空间名",D.TOT_GROOTTE_MB "表空间大小(M)",D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",TO_CHAR(ROUND(D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99'

7、;) | '%' "使用比",F.TOTAL_BYTES "空闲空间(M)",F.MAX_BYTES "最大块(M)"FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTESFROM SYS.DBA_FREE_SPACEGROUP BY TABLESPACE_NAME) F,(SELECT DD.TABLESPACE_NAME,R

8、OUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MBFROM SYS.DBA_DATA_FILES DDGROUP BY DD.TABLESPACE_NAME) DWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAMEORDER BY 1;六、查询表空间的总容量select tablespace_name, sum(bytes) / 1024 / 1024 as MBfrom dba_data_filesgroup by tablespace_name;七、查询表空间使用率select total.table

9、space_name,round(total.MB, 2) as Total_MB,考试大论坛round(total.MB - free.MB, 2) as Used_MB,round(1 - free.MB / total.MB) * 100, 2) | '%' as Used_Pctfrom (select tablespace_name, sum(bytes) / 1024 / 1024 as MBfrom dba_free_spacegroup by tablespace_name) free,(select tablespace_name, sum(bytes) /

10、1024 / 1024 as MBfrom dba_data_filesgroup by tablespace_name) totalwhere free.tablespace_name = total.tablespace_name;八、查找当前表级锁select sess.sid,sess.serial#,lo.oracle_username,lo.os_user_name,ao.object_name,lo.locked_modefrom v$locked_object lo,dba_objects ao,v$session sesswhere ao.object_id = lo.obj

11、ect_id and lo.session_id = sess.sid;杀掉锁表进程：alter system kill session '436,35123'九、监控当前数据库谁在运行什么SQL语句select osuser, username, sql_textfrom v$session a, v$sqltext bwhere a.sql_address =b.address order by address, piece;十、找使用CPU多的用户sessionselect a.sid,spid,status,substr(gram,1,40) prog, a.

12、terminal,osuser,value/60/100 valuefrom v$session a,v$process b,v$sesstat cwhere c.statistic#=12 andc.sid=a.sid anda.paddr=b.addrorder by value desc;十一、查看死锁信息SELECT (SELECT usernameFROM v$sessionWHERE SID = a.SID) blocker, a.SID, 'is blocking',(SELECT usernameFROM v$sessionWHERE SID = b.SID)

13、blockee, b.SIDFROM v$lock a, v$lock bWHERE a.BLOCK = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2;十二、具有最高等待的对象SELECT o.OWNER,o.object_name, o.object_type, a.event,SUM (a.wait_time + a.time_waited) total_wait_timeFROM v$active_session_history a, dba_objects oWHERE a.sample_time BETWEEN

14、SYSDATE - 30 / 2880 AND SYSDATEAND a.current_obj# = o.object_idGROUP BY o.OWNER,o.object_name, o.object_type, a.eventORDER BY total_wait_time DESC;十三、查看具有最高等待的对象SELECT a.session_id, s.osuser, s.machine, gram, o.owner, o.object_name,o.object_type, a.event,SUM (a.wait_time + a.time_waited) total_

15、wait_timeFROM v$active_session_history a, dba_objects o, v$session sWHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATEAND a.current_obj# = o.object_idAND a.session_id = s.SIDGROUP BY o.owner,o.object_name,o.object_type,a.event,a.session_id,gram,s.machine,s.osuserORDER BY total_wait_tim

16、e DESC;十四、查看等待最多的SQLSELECT gram, a.session_id, a.user_id, d.username, s.sql_text,SUM (a.wait_time + a.time_waited) total_wait_timeFROM v$active_session_history a, v$sqlarea s, dba_users dWHERE a.sample_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATEAND a.sql_id = s.sql_idAND a.user_id = d.user_idG

17、ROUP BY gram, a.session_id, a.user_id, s.sql_text, d.username;十五、显示正在等待锁的所有会话SELECT * FROM DBA_WAITERS;十七、查数据库中正在执行的SQL SELECT SE.INST_ID, -实例 SQ.SQL_TEXT, /*SQL文本*/ SQ.SQL_FULLTEXT, /*SQL全部文本*/ SE.SID, /*会话的唯一标识，通常要对某个会话进行分析前，首先就需要获得该会话的SID。*/ -SE.SERIAL#, /*会话的序号*/ SQ.OPTIMIZER_COST AS COST_,

18、 /* COST 值*/ SE.LAST_CALL_ET CONTINUE_TIME, /*执行时间 可能是单个sql也可能是整个功能*/ SE.PREV_EXEC_START, /*SQL execution start of the last executed SQL statement*/ SE.EVENT, /*等待事件*/ SE.LOCKWAIT, /*是否等待LOCK(SE，P)*/ SE.MACHINE, /*客户端的机器名。(WORKGROUPPC-201211082055)*/ SQ.SQL_ID, /*SQL_ID*/ SE.USERNAME, /*创建该会话的用户名*/

19、SE.LOGON_TIME /*登陆时间*/ -SE.TERMINAL, /*客户端运行的终端名。(PC-201211082055)*/ -,SQ.HASH_VALUE, /*一个SQL 产生的HASH 值*/ -SQ.PLAN_HASH_VALUE /*执行SQL的HASH值(解析后HASH值)，与SQL_ADDRESS关联查询其他SQL相关视图后即可查询会话当前正在执行的SQL语句*/ FROM GV$SESSION SE, /*会话信息。每一个连接到ORACLE数据库的会话都能在该视图中对应一条记录，根据该视图中的信息可以查询该会话使用的用户，正在执行或者刚刚执行的SQL语句*/ /*G

20、V$SQLAREA 多节点 */ GV$SQLAREA SQ /*跟踪所有SHARED POOL中的共享CURSOR信息，包括 执行次数，逻辑读，物理读等*/ WHERE SE.SQL_HASH_VALUE = SQ.HASH_VALUE AND SE.STATUS = 'ACTIVE' AND SE.SQL_ID = SQ.SQL_ID AND SQ.INST_ID = SE.INST_ID AND SE.USERNAME is not null; -过滤条件 -AND SE.USERNAME = 'FWSB' -用户名 -AND SQ.COMMAND_TYP

21、E IN (2, 3, 5, 6, 189) -AND SE.SID != USERENV ('SID')/*rac集群环境误用*/ -AND MACHINE != 'WORKGROUPMHQ-PC' ;十八、每天执行慢的SQL SELECT S.SQL_TEXT, S.SQL_FULLTEXT, S.SQL_ID, ROUND(ELAPSED_TIME / 1000000 / (CASE WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN 1 ELSE EXECUTIONS END), 2) "

22、执行时间'S'", S.EXECUTIONS "执行次数", S.OPTIMIZER_COST "COST", S.SORTS, S.MODULE, -连接模式（JDBC THIN CLIENT：程序） - S.LOCKED_TOTAL, S.PHYSICAL_READ_BYTES "物理读", - S.PHYSICAL_READ_REQUESTS "物理读请求", S.PHYSICAL_WRITE_REQUESTS "物理写", - S.PHYSICAL_WRITE_

23、BYTES "物理写请求", S.ROWS_PROCESSED "返回行数", S.DISK_READS "磁盘读", S.DIRECT_WRITES "直接路径写", S.PARSING_SCHEMA_NAME, S.LAST_ACTIVE_TIME FROM GV$SQLAREA S WHERE ROUND (ELAPSED_TIME / 1000000 / ( CASE WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN 1 ELSE EXECUTION

24、S END), 2) > 5 -100 0000微秒=1S - AND S.PARSING_SCHEMA_NAME = USER AND TO_CHAR(S.LAST_LOAD_TIME, 'YYYY-MM-DD' ) = TO_CHAR( SYSDATE, 'YYYY-MM-DD' ) AND S.COMMAND_TYPE IN (2, 3, 5 , 6, 189) /*值对应类型 2：INSERT、3：SELECT、6：UPDATE、7：DELETE、189：MERGE 查询V$SQLCOMMAND*/ AND MODULE = 'JDBC T

25、hin Client' ORDER BY "执行时间'S'" DESC;十九、查看非绑定变量的SQL SELECT V.SQL_ID, V.SQL_FULLTEXT, V.PARSING_SCHEMA_NAME, FM.EXECUTIONS_COUNT, FM.ELAPSED_TIME FROM (SELECT L.FORCE_MATCHING_SIGNATURE MATHCES, MAX(L.SQL_ID | L.CHILD_NUMBER) MAX_SQL_CHILD, DENSE_RANK() OVER(ORDER BY COUNT(*) DES

26、C ) RANKING, ROUND(SUM (ROUND(ELAPSED_TIME / 1000000 / (CASE WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN 1 ELSE EXECUTIONS END), 5) ELAPSED_TIME, SUM(L.EXECUTIONS) EXECUTIONS_COUNT FROM V$SQL L WHERE TO_CHAR(TO_DATE(LAST_LOAD_TIME, 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD') =

27、TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD') - 当天 LAST_LOAD_TIME(VARCHAR类型,LOADED INTO THE LIBRARY CACHE TIME) AND L.MODULE LIKE '%JDBC%' -程序连接 AND L.FORCE_MATCHING_SIGNATURE <> 0 AND L.PARSING_SCHEMA_NAME = UPPER ('&USERNAME') -用户 AND L.COMMAND_TYPE IN (2, 3, 5 , 6, 189) -命令

28、类型 2：INSERT、3：SELECT、6：UPDATE、7：DELETE、189：MERGE 查询V$SQLCOMMAND GROUP BY L.FORCE_MATCHING_SIGNATURE HAVING COUNT (*) > 5) FM, V$SQL V WHERE FM.MAX_SQL_CHILD = (V.SQL_ID | V.CHILD_NUMBER) AND EXECUTIONS_COUNT >= 50 -执行次数超过50次先筛选改写，后续慢慢在范围小 ORDER BY FM.RANKING; -V$SQL_BIND_CAPTURE -记录包含变量得表.包括 R

29、OWNUM<:1 变量二十、查看LOG切换频率 select b.SEQUENCE#, b.FIRST_TIME, a.SEQUENCE#, a.FIRST_TIME, round(a.FIRST_TIME - b.FIRST_TIME) * 24 ) * 60, 2) from v$log_history a, v$log_history b where a.SEQUENCE# = b.SEQUENCE# + 1 and b.THREAD# = 1 order by a.SEQUENCE# desc;二十一、查看SQL执行进度 -显示运行时间超过6秒的数据库操作的状态 SELECT A

30、.SID, A.SERIAL#, OPNAME, TARGET, -对象 TO_CHAR(START_TIME, 'YYYY-MM-DD HH24:MI:SS' ) START_TIME, -开始时间 (SOFAR / TOTALWORK) * 100 PROGRESS, -进度比 TIME_REMAINING, -估算剩余时间 ELAPSED_SECONDS, -运行时间S A.SQL_ID FROM V$SESSION_LONGOPS A WHERE SID = ; * 其中SID和SERIAL#是与V$SESSION中的匹配的， * OPNAME：指长时间执行的操作名.如

31、： TABLE SCAN * TARGET：被操作的OBJECT_NAME. 如：TABLEA * TARGET_DESC：描述TARGET的内容 * SOFAR：这个是需要着重去关注的，表示已要完成的工作数，如扫描了多少个块。 * TOTALWORK：指目标对象一共有多少数量（预计）。如块的数量。 * START_TIME：进程的开始时间 * LAST_UPDATE_TIM：最后一次调用SET_SESSION_LONGOPS的时间 * TIME_REMAINING： 估计还需要多少时间完成，单位为秒 * ELAPSED_SECONDS：指从开始操作时间到最后更新时间 * MESSAGE：对于

32、操作的完整描述，包括进度和操作内容。 * USERNAME：与V$SESSION中的一样。 * SQL_ADDRESS：关联V$SQL * SQL_HASH_VALUE：关联V$SQL * QCSID：主要是并行查询一起使用。 二十二、查询外键字段在主键表中没有索引的 SELECT C.*, C1.r_constraint_name, c2.table_name, T.NUM_ROWS, 'create index idx_' | c.table_name | '_' | column_name | ' on ' | c.table_name |

33、 '(' | column_name | ');' FROM USER_CONS_COLUMNS C JOIN USER_CONSTRAINTS C1 ON C1.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND C1.CONSTRAINT_TYPE = 'R' AND (C.TABLE_NAME, C.COLUMN_NAME) NOT IN ( SELECT TABLE_NAME, COLUMN_NAME FROM USER_IND_COLUMNS I) JOIN USER_TABLES T ON T.TABLE_

34、NAME = C.TABLE_NAME join USER_CONSTRAINTS c2 on c1.r_constraint_name = c2.constraint_name; 博客：为什么子表外键列需要建立索引? * 自己测试【外键字段不加索引时】 * update外键表，主键表delete任何数据都不允许；但update session1的范围 且set字段不是where字段就可以执行，加索引后，更改where字段的数据会报错二十三、 查看软硬解析，游标数 SELECT /*A.SID,*/ /* A.STATISTIC#,*/ SUM (A.VALUE), B.NAME, ( CAS

35、E WHEN NAME = 'PARSE COUNT (TOTAL)' THEN '表示总的解析次数' WHEN NAME = 'PARSE COUNT (HARD)' THEN '表示硬解析的次数' WHEN NAME = 'SESSION CURSOR CACHE COUNT' THEN '表示缓存的游标个数' WHEN NAME = 'SESSION CURSOR CACHE HITS' THEN '表示从缓存中找到游标的次数' WHEN NAME = '

36、;OPENED CURSORS CURRENT' THEN '表示SESSION中打开的游标数' END ) FROM V$SESSTAT A, V$STATNAME B WHERE A.STATISTIC# = B.STATISTIC# AND B.NAME IN ( 'PARSE COUNT (HARD)', 'PARSE COUNT (TOTAL)' , 'SESSION CURSOR CACHE COUNT' , 'SESSION CURSOR CACHE HITS' , 'OPENED C

37、URSORS CURRENT' ) - AND SID=11 GROUP BY B.NAME ORDER BY NAME; -#用于衡量 软硬解析/游标共享比.二十四、查看未提交的事物的会话和锁的对象 SELECT DISTINCT S.SID, S.SERIAL#, S.MACHINE, L.SQL_TEXT, S.LAST_CALL_ET, 'ALTER SYSTEM KILL SESSION ''' | S.SID | ',' | S.SERIAL# | '''' , LO.ORACLE_USERNA

38、ME, LO.OS_USER_NAME, AO.OBJECT_NAME, LO.LOCKED_MODE FROM V$SESSION S, V$TRANSACTION T, V$SQL L, V$LOCKED_OBJECT LO, DBA_OBJECTS AO WHERE S.TADDR = T.ADDR AND S.PREV_SQL_ADDR = L.ADDRESS AND AO.OBJECT_ID = LO.OBJECT_ID AND LO.SESSION_ID = S.SID;二十五、通过系统中PID去数据库中找执行的SQL SELECT A.USERNAME, A.PROGRAM, B

39、.SPID, C.SQL_TEXT, C.SQL_FULLTEXT FROM V$SESSION A, V$PROCESS B, V$SQLAREA C WHERE A.PADDR = B.ADDR AND A.SQL_HASH_VALUE = C.HASH_VALUE AND A.STATUS = 'ACTIVE' AND A.USERNAME NOT IN ( 'SYS', 'SYSTEM' , 'SYSMAN') AND A.SID != USERENV ('SID') AND B.SPID = 填写PID;

40、二十六、序列/索引差异 比对结果后的创建语句 （例如：将A用户index和B用户对比，将A用户多B用户的在B用户创建) 【如下2个SQL都需要在 缺少sequence/index A用户执行】 -#SEQUENCE的创建语句： SELECT 'CREATE SEQUENCE ' | SEQUENCE_NAME | ' MINVALUE ' | MIN_VALUE | ' MAXVALUE ' | MAX_VALUE | ' START WITH ' | LAST_NUMBER | ' INCREMENT BY '

41、| INCREMENT_BY | (CASE WHEN CACHE_SIZE = 0 THEN ' NOCACHE' ELSE ' CACHE ' | CACHE_SIZE END ) | '' FROM USER_SEQUENCES W WHERE -过滤掉登录用户存在的SEQUENCE NOT EXISTS ( SELECT 1 FROM USER_SEQUENCESDB_SINOSOFT W1 WHERE W.SEQUENCE_NAME = W1.SEQUENCE_NAME); -#索引差异 结果的创建语句 SELECT 'CREA

42、TE ' | INDEX_TYPE | ' INDEX ' | INDEX_NAME | ' ON ' | TABLE_NAME | ' (' | LISTAGG(CNAME, ',' ) WITHIN GROUP (ORDER BY COLUMN_POSITION) | ');' FROM (SELECT IC.INDEX_NAME, IC.TABLE_NAME, IC.COLUMN_NAME CNAME, IC.COLUMN_POSITION, COUNT (IC.INDEX_NAME) OVER (

43、PARTITION BY IC.INDEX_NAME, IC.TABLE_NAME) CON, I.INDEX_TYPE FROM USER_IND_COLUMNSDB_SINOSOFT IC JOIN USER_INDEXESDB_SINOSOFT I ON I.INDEX_NAME = IC.INDEX_NAME WHERE -过滤掉登录用户存在的INDEX NOT EXISTS ( SELECT 1 FROM USER_IND_COLUMNS IC1 WHERE IC1.INDEX_OWNER = UPPER ( '&TO_USERNAME') AND IC.IN

44、DEX_NAME = IC1.INDEX_NAME) -过滤掉主键，避免索引创建，在创建主键报错 对象已存在 AND IC.INDEX_NAME NOT IN ( SELECT C.CONSTRAINT_NAME FROM USER_CONSTRAINTSDB_SINOSOFT C) ORDER BY IC.INDEX_NAME, IC.COLUMN_POSITION) GROUP BY INDEX_TYPE, CON, INDEX_NAME, TABLE_NAME;二十七、查看热点块的对象 SELECT A.HLADDR, A.FILE#, A.DBABLK, A.TCH, A.OBJ, B

45、.OBJECT_NAME FROM X$BH A, DBA_OBJECTS B WHERE (A.OBJ = B.OBJECT_ID OR A.OBJ = B.DATA_OBJECT_ID) AND A.HLADDR = '0000000054435000' -V$SESSION_WAIT.P1RAW UNION SELECT HLADDR, FILE#, DBABLK, TCH, OBJ, NULL FROM X$BH WHERE OBJ IN ( SELECT OBJ FROM X$BH WHERE HLADDR = '0000000054435000' M

46、INUS SELECT OBJECT_ID FROM DBA_OBJECTS MINUS SELECT DATA_OBJECT_ID FROM DBA_OBJECTS) AND HLADDR = '0000000054435000' ORDER BY 4;二十八、查看某用户表大小/总数情况 SELECT T.TABLE_NAME, TC.COMMENTS, T.NUM_ROWS, ROUND (SUM (S.BYTES / 1024 / 1024 / 1024 ) GB FROM USER_TABLES T JOIN USER_SEGMENTS S ON S.SEGMENT_NAME = T.TABLE_NAME JOIN USER_TAB_COMMENTS TC ON TC.TABLE_NAME = T.TABLE_NAME GROUP BY T.TABLE_NAME, TC.COMMENTS, T.NUM_ROWS ORDER BY NUM_ROWS DESC NULLS LAST ;二十九、 重新编译失效存储/包语句 SELECT 'ALTER ' | (CASE WHEN OBJECT_TYPE = &#