DBA常用脚本2-性能监控

--DBA常用脚本2-性能监控

/*1、数据缓冲区的命中率已经不是性能调整中的主要问题了，但是，过低的命中率肯定是不可以的，在任何情况下，我们必须保证有一个大的data buffer和一个高的命中率。

这个语句可以获得整体的数据缓冲命中率，越高越好

Code: [Copy to clipboard]*/

SELECT a.VALUE + b.VALUE logical_reads,
       c.VALUE phys_reads,
       round(100 * (1 - c.value / (a.value + b.value)), 4) hit_ratio
  FROM v$sysstat a, v$sysstat b, v$sysstat c
 WHERE a.NAME = 'db block gets'
   AND b.NAME = 'consistent gets'
   AND c.NAME = 'physical reads';
  
/*   10、捕捉运行很久的SQL

column username format a12
column opname format a16
column progress format a8 */

select username,
       sid,
       opname,
       round(sofar * 100 / totalwork, 0) as progress,
       time_remaining,
       sql_text
  from v$session_longops, v$sql
 where time_remaining <> 0
   and sql_address = address
   and sql_hash_value = hash_value;

/*2、库缓冲说明了SQL语句的重载率，当然，一个SQL语句应当被执行的越多越好，如果重载率比较高，就考虑增加共享池大小或者是提高Bind变量的使用以下语句查询了Sql语句的重载率，越低越好

Code: [Copy to clipboard]*/
SELECT SUM(pins) total_pins,
       SUM(reloads) total_reloads,
       SUM(reloads) / SUM(pins) * 100 libcache_reload_ratio
       FROM V$librarycache;

/*3、用户锁，数据库的锁有的时候是比较耗费资源的，特别是发生锁等待的时候，我们必须找到发生等待的锁，有可能的话，杀掉该进程。

这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。

可以通过alter system kill session ‘sid,serial#’来杀掉会话

Code: [Copy to clipboard]*/

SELECT /*+ rule */
 s.username,
 decode(l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
 o.owner,
 o.object_name,
 o.object_type,
 s.sid,
 s.serial#,
 s.terminal,
 s.machine,
 s.program,
 s.osuser
  FROM v$session s, v$lock l, dba_objects o
 WHERE l.sid = s.sid
   AND l.id1 = o.object_id(+)
   AND s.username is NOT NULL;

/*4、锁与等待，如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待以下的语句可以查询到谁锁了表，而谁在等待。

Code: [Copy to clipboard]*/

SELECT /*+ rule */
 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$rollname，其中xidusn就是回滚段的USN

5、如果发生了事务或锁，想知道哪些回滚段正在被使用吗？其实通过事务表，我们可以详细的查询到事务与回滚段之间的关系。同时，如果关联会话表，我们则可以知道是哪个会话发动了这个事务。

Code: [Copy to clipboard]*/

SELECT s.USERNAME,
       s.SID,
       s.SERIAL#,
       t.UBAFIL "UBA filenum",
       t.UBABLK "UBA Block number",
       t.USED_UBLK "Number os undo Blocks Used",
       t.START_TIME,
       t.STATUS,
       t.START_SCNB,
       t.XIDUSN RollID,
       r.NAME RollName
  FROM v$session s, v$transaction t, v$rollname r
 WHERE s.SADDR = t.SES_ADDR
   AND t.XIDUSN = r.usn;
/*
6、如果利用会话跟踪或者是想查看某个会话的跟踪文件，那么查询到OS上的进程或线程号是非常重要的，因为文件的令名中，就包含这个信息，以下的语句可以查询到进程或线程号，由此就可以找到对应的文件。

Code: [Copy to clipboard]*/

SELECT p1.value || '/' || p2.value || '_ora_' || p.spid filename
  FROM v$process p, v$session s, v$parameter p1, v$parameter p2
 WHERE p1.name = 'user_dump_dest'
   AND p2.name = 'db_name'
   AND p.addr = s.paddr
   AND s.audsid = USERENV('SESSIONID');

/*7、在ORACLE 9i中，可以监控索引的使用，如果没有使用到的索引，完全可以删除掉，减少DML操作时的操作。

以下就是开始索引监控与停止索引监控的脚本

Code: [Copy to clipboard]*/

set heading off
set echo off
set feedback off
set pages 10000
spool start_index_monitor.sql

SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;'
FROM dba_indexes
WHERE owner = USER;

spool off
set heading on
set echo on
set feedback on
------------------------------------------------
set heading off
set echo off
set feedback off
set pages 10000
spool stop_index_monitor.sql

SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
FROM dba_indexes
WHERE owner = USER;

spool off
set heading on
set echo on
set feedback on

/*如果需要监控更多的用户，可以将owner=User改写成别的监控结果在视图v$object_usage中查询
*/
Code: [Copy to clipboard]
CREATE OR REPLACE PROCEDURE show_space(p_segname    IN VARCHAR2,
                                       p_owner　    IN VARCHAR2 DEFAULT USER,
                                       p_type　　IN VARCHAR2 DEFAULT 'TABLE',
                                       p_partition  IN VARCHAR2 DEFAULT NULL)
-- This procedure uses AUTHID CURRENT USER so it can query DBA_*
  -- views using privileges from a ROLE and so it can be installed
  -- once per database, instead of once per user who wanted to use it.
AUTHID CURRENT_USER as
  l_free_blks　　　　　　　　          number;
  l_total_blocks　　　　　　　number  ;
  l_total_bytes　　　　　　　          number;
  l_unused_blocks　　　　　　          number;
  l_unused_bytes　　　　　　　number  ;
  l_LastUsedExtFileId　　　　          number;
  l_LastUsedExtBlockId　　　　number  ;
  l_LAST_USED_BLOCK　　　　　          number;
  l_segment_space_mgmt　　　　varchar2(255);
  l_unformatted_blocks                 number;
  l_unformatted_bytes                  number;
  l_fs1_blocks                         number;
  l_fs1_bytes                          number;
  l_fs2_blocks                         number;
  l_fs2_bytes                          number;
  l_fs3_blocks                         number;
  l_fs3_bytes                          number;
  l_fs4_blocks                         number;
  l_fs4_bytes                          number;
  l_full_blocks                        number;
  l_full_bytes                         number;

  -- Inline procedure to print out numbers nicely formatted
  -- with a simple label.
  PROCEDURE p(p_label in varchar2, p_num in number) IS
  BEGIN
    dbms_output.put_line(rpad(p_label, 40, '.') ||
                         to_char(p_num, '999,999,999,999'));
  END;
BEGIN
  -- This query is executed dynamically in order to allow this procedure
  -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
  -- via a role as is customary.
  -- NOTE: at runtime, the invoker MUST have access to these two
  -- views!
  -- This query determines if the object is an ASSM object or not.
  BEGIN
    EXECUTE IMMEDIATE 'select ts.segment_space_management
FROM dba_segments seg, dba_tablespaces ts
WHERE seg.segment_name　　　= :p_segname
AND (:p_partition is null or
seg.partition_name = :p_partition)
AND seg.owner = :p_owner
AND seg.tablespace_name = ts.tablespace_name'
      INTO l_segment_space_mgmt
      USING p_segname, p_partition, p_partition, p_owner;
  EXCEPTION
    WHEN too_many_rows THEN
      dbms_output.put_line('This must be a partitioned table, use p_partition => ');
      RETURN;
  END;

  -- If the object is in an ASSM tablespace, we must use this API
  -- call to get space information; else we use the FREE_BLOCKS
  -- API for the user managed segments.
  IF l_segment_space_mgmt = 'AUTO' THEN
    dbms_space.space_usage(p_owner,
                           p_segname,
                           p_type,
                           l_unformatted_blocks,
                           l_unformatted_bytes,
                           l_fs1_blocks,
                           l_fs1_bytes,
                           l_fs2_blocks,
                           l_fs2_bytes,
                           l_fs3_blocks,
                           l_fs3_bytes,
                           l_fs4_blocks,
                           l_fs4_bytes,
                           l_full_blocks,
                           l_full_bytes,
                           p_partition);
 
    p('Unformatted Blocks ', l_unformatted_blocks);
    p('FS1 Blocks (0-25)　', l_fs1_blocks);
    p('FS2 Blocks (25-50) ', l_fs2_blocks);
    p('FS3 Blocks (50-75) ', l_fs3_blocks);
    p('FS4 Blocks (75-100)', l_fs4_blocks);
    p('Full Blocks　　　　', l_full_blocks);
  ELSE
    dbms_space.free_blocks(segment_owner　　  => p_owner,
                           segment_name　　　 => p_segname,
                           segment_type　　　 => p_type,
                           freelist_group_id  => 0,
                           free_blks　　　　  => l_free_blks);
 
    p('Free Blocks', l_free_blks);
  END IF;

  -- And then the unused space API call to get the rest of the
  -- information.
  dbms_space.unused_space(segment_owner　　         => p_owner,
                          segment_name　　　        => p_segname,
                          segment_type　　　        => p_type,
                          partition_name　　        => p_partition,
                          total_blocks　　　        => l_total_blocks,
                          total_bytes　　　         => l_total_bytes,
                          unused_blocks　　         => l_unused_blocks,
                          unused_bytes　　　        => l_unused_bytes,
                          LAST_USED_EXTENT_FILE_ID  => l_LastUsedExtFileId,
                          LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
                          LAST_USED_BLOCK           => l_LAST_USED_BLOCK);

  p('Total Blocks', l_total_blocks);
  p('Total Bytes', l_total_bytes);
  p('Total MBytes', trunc(l_total_bytes / 1024 / 1024));
  p('Unused Blocks', l_unused_blocks);
  p('Unused Bytes', l_unused_bytes);
  p('Last Used Ext FileId', l_LastUsedExtFileId);
  p('Last Used Ext BlockId', l_LastUsedExtBlockId);
  p('Last Used Block', l_LAST_USED_BLOCK);
END;