一些常用查询性能的sql

--、监控等待事件
select event,
       sum(decode(wait_time, 0, 0, 1)) prev,
       sum(decode(wait_time, 0, 1, 0)) curr,
       count(*)
  from v$session_wait
 group by event
 order by 4;

--利用v$system_event视图执行下面的查询查看数据库中某些常见的等待事件：
select *
  from v$system_event
 where event in ('buffer busy waits', 'db file sequential read',
        'db file scattered read', 'enqueue', 'free buffer waits',
        'latch free', 'log file parallel write', 'log file sync',
        'enq: TX - row lock contention');

--接着，利用下面对v$session_event和v$session视图进行的查询，研究具有对上面显示的内容有贡献的等待事件的会话：
select se.sid,
       s.username,
       se.event,
       se.total_waits,
       se.time_waited,
       se.average_wait
  from v$session s, v$session_event se
 where s.sid = se.sid
   and se.event not like 'SQL*Net%'
   and s.status = 'ACTIVE'
   and s.username is not null;

--还可以组合v$session和v$session_wait视图进行查询：
select sw.sid,
       s.username,
       sw.event,
       sw.wait_time,
       sw.state,
       sw.seconds_in_wait SEC_IN_WAIT
  from v$session s, v$session_wait sw
 where s.sid = sw.sid
   and sw.event not like 'SQL*Net%'
   and s.username is not null
 order by sw.wait_time desc;

--查询具体会话等待事件的详细信息
select sid, event, p1text, p1, p2text, p2, p3text, p3
  from v$session_wait
 where sid between &1 and &2
   and event not like '%SQL%'
   and event not like '%rdbms%';

--在查出会话执行了什么SQL语句发生等待事件：
select s1.sid, s1.event, s2.sql_text
  from v$session s1, v$sql s2
 where s1.sid = &sid_in
   and s1.event in ('enq: TX - row lock contention')
   and s1.SQL_ID = s2.sql_id;

--2、监控表空间的I/O比例：
select df.tablespace_name name,
       df.file_name       "file",
       f.phyrds           pyr,
       f.phyblkrd         pbr,
       f.phywrts          pyw,
       f.phyblkwrt        pbw
  from v$filestat f, dba_data_files df
 where f.file# = df.file_id

--3、查询是否有长时间的操作
--用以下语句找出长时间操作的SQL语句：
  select longops.sid,
         longops.elapsed_seconds,
         longops.opname,
         s.sql_text,
         longops.MESSAGE
    from v$session_longops longops, v$sql s
   where longops.elapsed_seconds > 6
     and longops.sql_address = s.address;

--或者：
SELECT SE.SID,
       OPNAME,
       TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK,
       ELAPSED_SECONDS ELAPSED,
       ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,
       SQL_TEXT
  FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE
 WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
   AND SL.SID = SE.SID
   AND SOFAR != TOTALWORK
 ORDER BY START_TIME;

--调整PGA优化排序：
--首先查看Oracle的v$pga_target_advice：
SELECT ROUND(pga_target_for_estimate / 1024 / 1024) AS target_mb,
       estd_pga_cache_hit_percentage AS hit_ratio,
       estd_overalloc_count
  FROM v$pga_target_advice
 ORDER BY target_mb;
--然后调整PGA
alter system set pga_aggregate_target = M;

/*在OLTP系统中，典型PGA内存设置应该是总内存的较小部分（例如20％），剩下80％分配给SGA。
OLTP：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 20%
在DSS系统中，由于会运行一些很大的查询，典型的PGA内存最多分配70％的内存。
DSS：PGA_AGGREGATE_TARGET = (total_mem * 80%) * 50%  */

--Oracle的排序操作：
Select * from v$sysstat where name like '%sort%';
--Sort(disk):要求IO去临时表空间的排序数目
--Sort(memory)：完全在memory中完成的排序数目
--Sort(rows)：被排序的行数合计
--Sort(disk)/ Sort(memory)<5%, 如果超过5%，增加sort_area_size的值(调整PGA)。

select (442/17649049)*100 from dual;

 --查cursor: pin S wait on X类等待时间有用。
select a.sid,
       b.sid l_sid,
       a.osuser,
       b.osuser l_user,
       a.event,
       (select sql_text
          from v$sqlarea a1
         where a1.sql_id = a.sql_id
           and a.prev_hash_value <> 0
            or a1.sql_id = a.prev_sql_id
           and a.prev_hash_value = 0) c_sql,
       (select sql_text
          from v$sqlarea a1
         where (a1.sql_id = b.sql_id and b.prev_hash_value <> 0 or
               a1.sql_id = b.prev_sql_id and b.prev_hash_value = 0)) l_sql,
       c.pname c_pname,
       d.pname l_pname,
       a.p1text,
       a.p1,
       a.p1raw,
       a.p2text,
       a.p2,
       a.p2raw,
       a.p3text,
       a.p3,
       a.p3raw
  from v$session a, v$session b, v$process c, v$process d
 where a.wait_class# <> 6
   and a.blocking_session = b.sid(+)
   and a.paddr = c.addr
   and b.paddr = d.addr(+);

select * from x$kgllk a where a.kgllkreq > 0;
select * from x$kglpn a where a.kglpnreq > 0;
select * from x$kglob a where a.kglhdpmd > 0;