DBA应该掌握的SQL语句(二)

该博文同时分享在http://topic.csdn.net/u/20090930/16/9dbdeab0-4670-4fa0-b6b4-45ca725281dd.html.

我综合oracle技术中国用户讨论组的一些知识，及自己掌握的一些知识，
把DBA人员应该掌握的一些SQL语句罗列了下，希望能对大家有所帮助。
下面罗列的这些SQL语句，我想，对于DBA人员来说是应该掌握的。

2.关于性能监控

①数据缓冲区的命中率已经不是性能调整中的主要问题了，但是，过低的命中率肯定是不可以的，在任何情况下，我们必须保证有一个大的data buffer和一个高的命中率。
这个语句可以获得整体的数据缓冲命中率，越高越好。
<SQL code>
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'

②库缓冲说明了SQL语句的重载率，当然，一个SQL语句应当被执行的越多越好，如果重载率比较高，就考虑增加共享池大小或者是提高Bind变量的使用。
以下语句查询了Sql语句的重载率，越低越好。
<SQL code>
SELECT SUM(pins) total_pins,
       SUM(reloads) total_reloads,
       SUM(reloads) / SUM(pins) * 100 libcache_reload_ratio
  FROM v$librarycache

③用户锁，数据库的锁有的时候是比较耗费资源的，特别是发生锁等待的时候，我们必须找到发生等待的锁，有可能的话，杀掉该进程。
这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。
可以通过alter system kill session ‘sid,serial#’来杀掉会话。
<SQL code>
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

④锁与等待，如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待
以下的语句可以查询到谁锁了表，而谁在等待。
<SQL code>
SELECT /*+ rule */
      lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username User_name,
       sysdate,
       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。

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

⑥想知道现在哪个用户正在利用临时段吗？这个语句将告诉你哪个用户正在利用临时段。
<SQL code>
SELECT b.tablespace,
       b.segfile#,
       b.segblk#,
       b.blocks,
       a.sid,
       a.serial#,
       a.username,
       a.osuser,
       a.status,
       c.sql_text
  FROM v$session a, v$sort_usage b, v$sql c
 WHERE a.saddr = b.session_addr
   AND a.sql_address = c.address(+)
 ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

⑦如果利用会话跟踪或者是想查看某个会话的跟踪文件，那么查询到OS上的进程或线程号是非常重要的，因为文件的令名中，就包含这个信息，以下的语句可以查询到进程或线程号，由此就可以找到对应的文件。
<SQL code>
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');

⑧在ORACLE 9i中，可以监控索引的使用，如果没有使用到的索引，完全可以删除掉，减少DML操作时的操作。
以下就是开始索引监控与停止索引监控的脚本。
<SQL code>
SQL>set heading off
SQL>set echo off
SQL>set feedback off
SQL>set pages 10000
SQL>spool start_index_monitor.sql
 
SQL>SELECT 'alter index '||owner||'.'||index_name||' monitoring usage;'
SQL>FROM dba_indexes
SQL>WHERE owner = USER; 
 
SQL>spool off 
set heading on
SQL>set echo on
SQL>set feedback on
----------------------------
SQL>set heading off
SQL>set echo off
SQL>set feedback off
SQL>set pages 10000
SQL>spool stop_index_monitor.sql
 
SQL>SELECT 'alter index '||owner||'.'||index_name||' nomonitoring usage;'
SQL>FROM dba_indexes
SQL>WHERE owner = USER; 
 
SQL>spool off 
SQL>set heading on
SQL>set echo on
SQL>set feedback on
如果需要监控更多的用户，可以将owner=User改写成别的
监控结果在视图v$object_usage中查询。