oracle性能监控

阅读量：2385 次

发布时间：2019-05-10

本文共 7424 字，大约阅读时间需要 24 分钟。

死锁后的解决办法

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

生成Kill Session语句

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

SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"FROM v$sessionWHERE 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;

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

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;

查看前台正在发出的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;