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

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二十一、查看SQL执行进度10二十二、查询外键字段在主键表中没有索引的11二十三、 查看软硬解析，游标数12二十四、查看未提交的事物的会话和锁的对象12二十五、通过系统中PID去数据库中找执行的SQL13二十六、序列/索引差异 比对结果后的创建语句13二十七、查看热点块的对象15二十八、查看某用户表大小/总数情况15二十九、 重新编译失效存储/包语句16三十、 Oracle 查看各表空间使用情况和最大最小块16三十一、 Oracle 查看TEMP表空间使用情况17三十二、 Oracle 查看回滚进度情况用的几个SQL17三十三、Oracle 查询锁之间的依赖关系18三十四、Oracle 查找锁之间依赖关系的最源头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 GBfrom sys.v_$temp_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,segtype,sql_textfrom v$sort_usage su, v$parameter p, v$session se, v$sql swhere = db_block_sizeand 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 tempfile 四、重建索引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) | % 使用比,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,ROUND(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.tablespace_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) / 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.object_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.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) 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 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_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_time 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_idGROUP 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_, /* 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, /*创建该会话的用户名*/ 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语句*/ /*GV$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_TYPE 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) 执行时间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_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 EXECUTIONS 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 Thin 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(*) DESC ) 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) = 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) -命令类型 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 -记录包含变量得表.包括 ROWNUM存储状态无效 -AND OWNER = USER AND OBJECT_NAME NOT LIKE %ETL% ORDER BY LAST_DDL_TIME DESC;三十、 Oracle 查看各表空间使用情况和最大最小块 SELECT UPPER (F.TABLESPACE_NAME) 表空间名, D.TOT_GROOTTE_MB 表空间大小(M), D.TOT_GR