oracle性能相关sql

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

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';

 

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

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#’来杀掉会话

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、锁与等待，如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待 以下的语句可以查询到谁锁了表，而谁在等待。

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

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;

 

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

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');

 

8、在ORACLE 9i中，可以监控索引的使用，如果没有使用到的索引，完全可以删除掉，减少DML操作时的操作。 以下就是开始索引监控与停止索引监控的脚本

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;

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

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

spool off set heading on set echo on set feedback on;