ORACLE常用性能监控SQL【一】

系列

ORACLE常用性能监控SQL【一】

ORACLE常用性能监控SQL【二】

Oracle-动态性能视图解读

---

---

死锁后的解决办法

如果死锁不能自动释放，就需要我们手工的 kill session

生成Kill Session语句

1. 查看有无死锁对象，如有 kill session

SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"  FROM v$session WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);

如果有，会返回类似与如下的信息：

alter system kill session '761,876';.....

kill session：
执行 alter system kill session ‘761,876’(sid 为 761);

注意： 应当注意对于 sid 在 100 以下的应当谨慎，可能该进程对应某个application，如对应某个事务,可以 kill

---

查看导致死锁的 SQL

SELECT s.sid, q.sql_textFROM v$sqltext q, v$session sWHERE q.address = s.sql_address AND s.sid = &sid -- 这个&sid 是第一步查询出来的ORDER BY piece;

执行后，输入对应的sid即可查看对应的sql.

---

查看谁锁了谁

SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid ||       ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' ||       s2.sid || ' ) ' AS blocking_status  FROM v$lock l1, v$session s1, v$lock l2, v$session s2 WHERE s1.sid = l1.sid   AND s2.sid = l2.sid   AND l1.BLOCK = 1   AND l2.request > 0   AND l1.id1 = l2.id1   AND l2.id2 = l2.id2;

或者

推荐这个，因为使用的是 v$locked_object

SELECT LPAD(' ', DECODE(l.xidusn, 0, 3, 0)) || l.oracle_username User_name,       o.owner,       o.object_name,       o.object_type,       s.sid,       s.serial#  FROM v$locked_object l, dba_objects o, v$session s WHERE l.object_id = o.object_id   AND l.session_id = s.sid ORDER BY o.object_id, xidusn DESC;

V$LOCKED_OBJECT只能报发生等待的表级锁,不能报发生等待的行级锁。

---

ORA-00054 资源正忙，要求指定 NOWAIT

演示：

select * from emp for update ;--通过for update 获取一个排它锁

SQL>select object_name as 对象名称,s.sid,s.serial#,p.spid as 系统进程号from v$locked_object l , dba_objects o , v$session s , v$process pwhere l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;对象名称                                                                         SID    SERIAL# 系统进程号-------------------------------------------------------------------------------- ---------- ---------- ------------------------EMP                                                                               1411       8865 32720

在另外一个会话中执行

ALTER SYSTEM KILL SESSION '1411,8865';

---

查询绑定变量使用的实际值

1, SQL还在shared pool中，没有被aged out 替换SQL ID 值即可

select sql_id, name, datatype_string, last_captured, value_string    from v$sql_bind_capture  where sql_id = '7nqt558g5gmyr'  order by LAST_CAPTURED,       POSITION;

2.请自行替换sql_id，此时是从awr中查询(sql 被 aged out 出 shared pool)

select instance_number,         sql_id,       name,       datatype_string,       last_captured,       value_string  from dba_hist_sqlbind where sql_id = 'fahv8x6ngrb50' order by LAST_CAPTURED, POSITION;

---

监控事例的等待

select event,sum(decode(wait_Time,0,0,1)) "Prev", sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot" from v$session_Wait group by event order by 4 ;

---

回滚段的争用情况

select name, waits, gets, waits / gets "Ratio"  from v$rollstat a, v$rollname b where a.usn = b.usn;

---

查看回滚段名称及大小

SELECT segment_name,       tablespace_name,       r.status,       (initial_extent / 1024) initialextent,       (next_extent / 1024) nextextent,       max_extents,       v.curext curextent  FROM dba_rollback_segs r, v$rollstat v WHERE r.segment_id = v.usn(+) ORDER BY segment_name;

---

查看控制文件

SELECT NAME FROM v$controlfile; 

---

查看日志文件

SELECT MEMBER FROM v$logfile;

---

查看前台正在发出的SQL语句

select user_name,sql_text　　 from v$open_cursor　　 where sid in (select sid from (select sid,serial#,username,program　　 from v$session　　 where status='ACTIVE'));

---

数据表占用空间大小情况

select segment_name, tablespace_name, bytes, blocks  from user_segments where segment_type = 'TABLE' ORDER BY bytes DESC, blocks DESC;

---

查看表空间碎片大小

 select tablespace_name,round(sqrt(max(blocks)/sum(blocks))*           (100/sqrt(sqrt(count(blocks)))),2) FSFI    from dba_free_space    group by tablespace_name order by 1;

---

查看表空间占用磁盘情况

    select              b.file_id                                 文件ID号,             b.tablespace_name                         表空间名,             b.bytes                                 字节数,             (b.bytes-sum(nvl(a.bytes,0)))                 已使用,             sum(nvl(a.bytes,0))                         剩余空间,             sum(nvl(a.bytes,0))/(b.bytes)*100         剩余百分比              from dba_free_space a,dba_data_files b              where a.file_id=b.file_id              group by b.tablespace_name,b.file_id,b.bytes              order by b.file_id;

---

查看表的大小，倒序排列

每张表都是作为“段”来存储的，可以通过user_segments视图查看其相应信息。
段（segments）的定义：如果创建一个堆组织表，则该表就是一个段

SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MBYTESE  FROM USER_SEGMENTS WHERE SEGMENT_TYPE = 'TABLE' GROUP BY SEGMENT_NAME order by MBYTESE desc;

---

查看表空间物理文件的名称及大小

SELECT tablespace_name,       file_id,       file_name,       round(bytes / (1024 * 1024), 0) total_space  FROM dba_data_files ORDER BY tablespace_name;

---

查看Oracle 表空间使用率

SELECT D.TABLESPACE_NAME,         SPACE || 'M' "SUM_SPACE(M)",         SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",         ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'            "USED_RATE(%)",         FREE_SPACE || 'M' "FREE_SPACE(M)"    FROM (  SELECT TABLESPACE_NAME,                   ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,                   SUM (BLOCKS) BLOCKS              FROM DBA_DATA_FILES          GROUP BY TABLESPACE_NAME) D,         (  SELECT TABLESPACE_NAME,                   ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE              FROM DBA_FREE_SPACE          GROUP BY TABLESPACE_NAME) F   WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  UNION ALL                                                           --如果有临时表空间  SELECT D.TABLESPACE_NAME,         SPACE || 'M' "SUM_SPACE(M)",         USED_SPACE || 'M' "USED_SPACE(M)",         ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",         NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"    FROM (  SELECT TABLESPACE_NAME,                   ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,                   SUM (BLOCKS) BLOCKS              FROM DBA_TEMP_FILES          GROUP BY TABLESPACE_NAME) D,         (  SELECT TABLESPACE_NAME,                   ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,                   ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE              FROM V$TEMP_SPACE_HEADER          GROUP BY TABLESPACE_NAME) F   WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)  ORDER BY 1;

SELECT a.tablespace_name "表空间名",       total "表空间大小",       free "表空间剩余大小",       (total - free) "表空间使用大小",       total / (1024 * 1024 * 1024) "表空间大小(G)",       free / (1024 * 1024 * 1024) "表空间剩余大小(G)",       (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",       round((total - free) / total, 4) * 100 "使用率 %"  FROM (SELECT tablespace_name, SUM(bytes) free          FROM dba_free_space         GROUP BY tablespace_name) a,       (SELECT tablespace_name, SUM(bytes) total          FROM dba_data_files         GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name

---

查看Temp 表空间实际使用磁盘大小

Select f.tablespace_name,       d.file_name "Tempfile name",       round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2) "total MB",       round(((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024,             2) "Free MB",       round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) "Used MB",       round((round(nvl(p.bytes_used, 0) / 1024 / 1024, 2) /             round((f.bytes_free + f.bytes_used) / 1024 / 1024, 2)) * 100,             2) as "Used_Rate(%)"  from SYS.V_$TEMP_SPACE_HEADER f,       DBA_TEMP_FILES           d,       SYS.V_$TEMP_EXTENT_POOL  p where f.tablespace_name(+) = d.tablespace_name   and f.file_id(+) = d.file_id   and p.file_id(+) = d.file_id;

---

查看session使用回滚段

SELECT  r.name 回滚段名,        s.sid,        s.serial#,        s.username 用户名,        t.status,        t.cr_get,        t.phy_io,        t.used_ublk,        t.noundo,        substr(s.program, 1, 78) 操作程序FROM   sys.v_$session s,sys.v_$transaction t,sys.v_$rollname rWHERE  t.addr = s.taddr and t.xidusn = r.usnORDER  BY t.cr_get,t.phy_io;

---

查看当前临时表空间使用大小与正在占用临时表空间的sql语句

   select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text     from v$sort_usage sort, v$session sess, v$sql sql    where sort.SESSION_ADDR = sess.SADDR      and sql.ADDRESS = sess.SQL_ADDRESS    order by blocks desc;

---

Temp表空间上进程的查询

select a.tablespace, b.sid, b.serial#, a.blocks,c.sql_text  from v$sort_usage a,v$session b,v$sqltext c where a.session_addr = b.saddr   and b.sql_address = c.address order by a.tablespace,b.sid,b.serial#,c.address, c.piece;

---

查看SGA区剩余可用内存

select name,　　　　　　sgasize/1024/1024　　　　　　　　"Allocated(M)",　　　　　　bytes/1024　　　　　　　　　　　 "**空间(K)",　　　　　　round(bytes/sgasize*100, 2)　　　"**空间百分比(%)"　　 from   (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f　　 where  f.name = 'free memory';

---

监控表空间I/O比例

select df.tablespace_name name,df.file_name "file",f.phyrds pyr,        f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw from v$filestat f, dba_data_files df where f.file# = df.file_id order by df.tablespace_name;

---

监控SGA命中率

select a.value + b.value "logical_reads",        c.value "phys_reads",        round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 38 and       b.statistic# = 39 and       c.statistic# = 40 ;

---

监控 SGA 中字典缓冲区的命中率

select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio", (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio" from v$rowcache where gets+getmisses <>0 group by parameter, gets, getmisses ;

---

监控 SGA **享缓存区的命中率，应该小于1%

select sum(pins) "Total Pins", sum(reloads) "Total Reloads", sum(reloads)/sum(pins) *100 libcache from v$librarycache;

---

监控 SGA 中重做日志缓存区的命中率，应该小于1%

SELECT name, gets, misses, immediate_gets, immediate_misses, Decode(gets,0,0,misses/gets*100) ratio1, Decode(immediate_gets+immediate_misses,0,0, immediate_misses/(immediate_gets+immediate_misses)*100) ratio2 FROM v$latch WHERE name IN ('redo allocation', 'redo copy');

---

监控内存和硬盘的排序比率，最好使它小于 .10

SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)') ;

---

监控字典缓冲区

SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES" FROM V$ROWCACHE ;

---

非系统用户建在SYSTEM表空间中的表

SELECT owner,table_name FROM DBA_TABLESWHERE tablespace_name in('SYSTEM','USER_DATA') AND       owner NOT IN('SYSTEM','SYS','OUTLN', 'ORDSYS','MDSYS','SCOTT', 'HOSTEAC');

---

性能最差的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<100;

---

读磁盘数超100次的sql

select * from sys.v_$sqlarea where disk_reads>100;

---

查找消耗资源比较的sql语句

  Select se.username,          se.sid,          su.extents,          su.blocks * to_number(rtrim(p.value)) as Space,          tablespace,          segtype,          sql_text     from v$sort_usage su, v$parameter p, v$session se, v$sql s    where p.name = 'db_block_size'      and su.session_addr = se.saddr      and s.hash_value = su.sqlhash      and s.address = su.sqladdr    order by se.username, se.sid;

---

最频繁执行的sql

select * from sys.v_$sqlarea where executions>100;

---

查询使用CPU多的用户session

select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value from v$session a,v$process b,v$sesstat c where c.statistic#=12 and       c.sid=a.sid and       a.paddr=b.addr order by value desc;

---

当前每个会话使用的对象数

SELECT a.sid,s.terminal,s.program,count(a.sid) FROM V$ACCESS a,V$SESSION sWHERE a.owner <> 'SYS'AND s.sid = a.sid GROUP BY a.sid,s.terminal,s.programORDER BY count(a.sid) ;

---

查看数据库库对象

SELECT owner, object_type, status, COUNT(*) count#  FROM all_objects GROUP BY owner, object_type, status;

---

查看数据库的版本　

SELECT version FROM product_component_version WHERE substr(product, 1, 6) = 'Oracle'; 

---

查看数据库的创建日期和归档方式

SELECT created, log_mode, log_mode FROM v$database; 

---

检查角色和权限设置

根据用户名进行授权的对象级特权

select b.owner || '.' || b.table_name obj,       b.privilege what_granted,       b.grantable,       a.username  from sys.dba_users a, sys.dba_tab_privs b where a.username = b.grantee order by 1, 2, 3;

根据被授权人进行授权的对象级特权

Select owner || '.' || table_name obj,       privilege what_granted,       grantable,       grantee  from sys.dba_tab_privs where not exists (select 'x' from sys.dba_users where username = grantee) order by 1, 2, 3;

根据用户名进行授予的系统级特权

select b.privilege what_granted, b.admin_option, a.username  from sys.dba_users a, sys.dba_sys_privs b where a.username = b.grantee order by 1, 2;

根据被授权人进行授予的系统级特权

select privilege what_granted, admin_option, grantee  from sys.dba_sys_privs where not exists (select 'x' from sys.dba_users where username = grantee) order by 1, 2;

根据用户名授予的角色

select b.granted_role ||       decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted,       a.username  from sys.dba_users a, sys.dba_role_privs b where a.username = b.grantee order by 1;

根据被授权人授予的角色

select granted_role ||       decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted,       grantee  from sys.dba_role_privs where not exists (select 'x' from sys.dba_users where username = grantee) order by 1;

用户名及已被授予的相应权限

select a.username,       b.granted_role ||       decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted  from sys.dba_users a, sys.dba_role_privs b where a.username = b.granteeUNIONselect a.username,       b.privilege ||       decode(admin_option, 'YES', ' (With Admin Option)', null) what_granted  from sys.dba_users a, sys.dba_sys_privs b where a.username = b.granteeUNIONselect a.username,       b.table_name || '-' || b.privilege ||       decode(grantable, 'YES', ' (With Grant Option)', null) what_granted  from sys.dba_users a, sys.dba_tab_privs b where a.username = b.grantee order by 1;

查询用户名及相应的配置文件、默认的表空间和临时表空间

Select username, profile, default_tablespace, temporary_tablespace, created  from sys.dba_users order by username;

---

等待事件V$视图

在Oracle 10g中V$SESSION_WAIT中的所有等待事件列现在都在V$SESSION中。因此，确保查询等待信息的 V$SESSION，因为它是一个更快的视图。V$ACTIVE_SESSION_HISTORY (ASH)将许多重要统计数据合并为一个视图或一个报表(ASH报表)。

马上该谁等待–查询V$SESSION_WAIT / V$SESSION

select event,       sum(decode(wait_time, 0, 1, 0)) "Waiting Now",       sum(decode(wait_time, 0, 0, 1)) "Previous Waits",       count(*) "Total"  from v$session_wait group by event order by count(*);

马上该谁等待；SPECIFIC Waits–查询V$SESSION_WAIT

SELECT /*+ ordered */ sid, event, owner, segment_name, segment_type, p1, p2, p3  FROM v$session_wait sw, dba_extents de WHERE de.file_id = sw.p1   AND sw.p2 between de.block_id and de.block_id + de.blocks - 1   AND (event = 'buffer busy waits' OR event = 'write complete waits')   AND p1 IS NOT null ORDER BY event, sid;

谁在等待 - 最后10 个等待数–查询V$SESSION_WAIT_HISTORY

SELECT /*+ ordered */ sid, event, owner, segment_name, segment_type, p1, p2, p3  FROM v$session_wait sw, dba_extents de WHERE de.file_id = sw.p1   AND sw.p2 between de.block_id and de.block_id + de.blocks - 1   AND (event = 'buffer busy waits' OR event = 'write complete waits')   AND p1 IS NOT null ORDER BY event, sid;

查找P1, P2, P3代表什么–查询 V$EVENT_NAME

select event#, name, parameter1 p1, parameter2 p2, parameter3 p3  from v$event_name where name in ('buffer busy waits', 'write complete waits');

会话开始后的所有等待数–查询 V$SESSION_EVENT

select sid, event, total_waits, time_waited, event_id  from v$session_event where time_waited > 0 order by time_waited;

类的所有会话等待数–查询V$SESSION_WAIT_CLASS

select sid, wait_class, total_waits from  v$session_wait_class;

系统启动后的所有等待数–查询V$SYSTEM_EVENT

select event, total_waits, time_waited, event_id  from v$system_event where time_waited > 0 order by time_waited;

类的系统等待数–查询V$SYSTEM_WAIT_CLASS

select wait_class, total_waits  from v$system_wait_class order by total_waits desc;

类的系统等待数–查询V$ACTIVE_SESSION_HISTORY

–In the query below, the highest count session is leader in non-idle wait events.

select session_id, count(1)  from v$active_session_history group by session_id order by 2;

–In the query below, find the SQL for the leader in non-idle wait events.

select c.sql_id, a.sql_text  from v$sql a,       (select sql_id, count(1)          from v$active_session_history b         where sql_id is not null         group by sql_id order by 2 desc) c where rownum <= 5 order by rownum;

自动工作量仓库(AWR) 的基本信息

自动工作量仓库(AWR)在默认情况下，仓库用小时填充，保留期是7天。
AWR使用多少空间

SQL> Select occupant_name,occupant_desc,space_usage_kbytes from v$sysaux_occupants where occupant_name like '%AWR%';OCCUPANT_NAME       OCCUPANT_DESC            SPACE_USAGE_KBYTES----------------- ---------------------------------- ------------------SM/AWR        Server Manageability - Automatic Workload Repository             215616SQL> 

系统上最原始的AWR信息是什么？

SQL> select dbms_stats.get_stats_history_availability from dual;GET_STATS_HISTORY_AVAILABILITY-------------------------------------------------------------20-OCT-16 12.04.49.088829000 AM -04:00

什么是AWR信息的保留期？

SQL>  select dbms_stats.get_stats_history_retention from dual;GET_STATS_HISTORY_RETENTION---------------------------                         31

将AWR信息的保留期更改为15天？

SQL> EXEC dbms_stats.alter_stats_history_retention(15);PL/SQL 过程已成功完成。

---

获取生成的trace文件

开启SQL跟踪后，会生成一个trace文件，通过初始化参数user_dump_dest配置其所在目录，该参数的值可以通过下面方法获取到：

select name, value from v$parameter where name = 'user_dump_dest'

trace文件的名字是独立于版本和平台的，在大部分常见的平台下，命名结构如下：

{instance name}_{process name}_{process id}.trc

1）instance name
初始化参数instance_name的小写值。通过v$instance视图的instance_name列可以得到这个值。
2）process name
产生跟踪文件进程的名字的小写值。对于专有服务器进程，使用ora，对于共享服务器进程，可以通过v$diapatcher或v$shared_server视图的name列获得。对于并行从属进程，可以通过v$px_process视图server_name列获得，对于其他多数后台进程来说，可以通过v$bgprocess视图的name列获得。
3）process id
操作系统层面的进程标记。这个值可以通过v$process视图的spid列获取。

根据这些信息，可以通过下面的方式获取trace文件名：

select s.SID,       s.SERVER,       lower(case               when s.SERVER in ('DEDICATED', 'SHARED') then                i.INSTANCE_NAME || '_' || nvl(pp.SERVER_NAME, nvl(ss.NAME, 'ora')) || '_' ||                p.SPID || '.trc'               else                null             end) as trace_file_name  from v$instance      i,       v$session       s,       v$process       p,       v$px_process    pp,       v$shared_server ss where s.PADDR = p.ADDR   and s.SID = pp.SID(+)   and s.PADDR = ss.PADDR(+)   and s.TYPE = 'USER'   and s.SID = 'your sid' order by s.SID

将上面的’your sid’替换为你的session的sid就可以查出指定session生成的trace文件的名字，session的sid在v$session视图中得到，或者直接查询当前session的sid：

select userenv('sid') from dual或者select  sid  from v$mystat a where rownum=1 ;               

将路径（user_dump_dest）和文件名结合在一起，我们就得到了trace文件的完整路径。

而在Oracel 11g中，查询当前会话生成的trace文件则非常简单：

select value from v$diag_info where name = 'Default Trace File'