Oracle宝典--常用SQL

体系结构

--等待事件（当前）
select t.event, count(*) count
  from v$session t
 group by event
 order by count desc;

--等待事件（历史汇集）
select t.event, t.total_waits
  from v$system_event t
 order by total_waits desc;

--游标使用情况
select inst_id, sid, count(*)
  from gv$open_cursor
 group by inst_id, sid
having count(*) >= 1000
 order by count(*) desc;

--PGA占用最多的进程
select p.spid,
       p.pid,
       s.sid,
       s.serial#,
       s.status,
       p.pga_alloc_mem,
       s.username,
       s.osuser,
       s.program
  from v$process p, v$session s
 where s.paddr(+) = p.addr
 order by p.pga_alloc_mem desc;

--登录时间最长的SESSION（同时获取到spid，方便主机层面 ps -ef | grep spid 来查看）
select *
  from (select t.sid,
               t2.spid,
               t.program,
               t.status,
               t.sql_id,
               t.prev_sql_id,
               t.event,
               t.logon_time,
               trunc(sysdate - logon_time)
          from v$session t, v$process t2
         where t.paddr = t2.addr
           and t.TYPE <> 'BACKGROUD'
         order by t.logon_time)
 where rownum <= 20;

--逻辑读最多的SQL
select *
  from (select sql_id,
               sql_text,
               s.executions,
               s.last_load_time,
               s.first_load_time,
               s.disk_reads,
               s.buffer_gets
          from v$sql s
         where s.buffer_gets > 300
         order by s.buffer_gets desc)
 where rownum <= 20;

--物理读最多的SQL
select *
  from (select sql_id,
               sql_text,
               s.executions,
               s.last_load_time,
               s.first_load_time,
               s.disk_reads,
               s.buffer_gets,
               s.parse_calls
          from v$sql s
         where s.disk_reads > 300
         order by s.disk_reads desc)
 where rownum <= 20;

--执行次数最多的SQL
select *
  from (select sql_id,
               sql_text,
               s.executions,
               s.last_load_time,
               s.first_load_time,
               s.disk_reads,
               s.buffer_gets,
               s.parse_calls
          from v$sql s
         order by s.executions desc)
 where rownum <= 20;

--解析次数最多的SQL
select *
  from (select sql_id,
               sql_text,
               s.executions,
               s.last_load_time,
               s.first_load_time,
               s.disk_reads,
               s.buffer_gets,
               s.parse_calls
          from v$sql s
         order by s.parse_calls desc)
 where rownum <= 20;

--求DISK_SORT严重的SQL
select sess.username, sql.sql_text, sql.address, sort1.blocks
  from v$session sess, v$sqlarea sql, v$sort_usage sort1
 where sess.serial# = sort1.session_num
   and sort1.sqladdr = sql.address
   and sort1.sqlhash = sql.hash_value
 --and sort1.blocks >200
 order by sort1.blocks desc;

表、索引

--普通索引失效查询  
select t.index_name,
       t.table_name,
       blevel,
       t.num_rows,
       t.leaf_blocks,
       t.distinct_keys
  from dba_indexes t
 where t.status = 'INVALID';

--分区索引失效  
select t2.owner,
       t1.blevel,
       t1.leaf_blocks,
       t1.index_name,
       t2.table_name,
       t1.partition_name,
       t1.status,
       t2.owner
  from dba_ind_partitions t1, dba_indexes t2
 where t1.index_name = t2.index_name
   and t1.status = 'UNUSABLE'
   and t2.owner in ('SYS');

--高水位表
select table_name, blocks, num_rows
  from user_tables
 where blocks / num_rows >= 0.2
   and num_rows is not null
   and num_rows <> 0
   and blocks >= 10000;

--未完待续