经常使用的oracle监控语句-凯发app官方网站

--根据file_id & block_id获得对象名称
select owner, segment_name, segment_type
  from dba_extents
 where file_id = &file_id
   and &block_id between block_id and block_id blocks - 1;

--根据操作系统pid,查询session信息
select a.sid, a.serial#, b.spid, a.terminal, a.machine, a.program, a.osuser
  from v$session a, v$process b
 where a.paddr = b.addr and b.spid = '&spid';

--根据session sid,查询操作系统pid
select a.sid, a.serial#, b.spid, a.terminal, a.machine, a.program, a.osuser
  from v$session a, v$process b
 where a.paddr = b.addr and a.sid = '&sid';

--查询用户正在执行的sql
select sql_text
  from v$sqltext
 where hash_value = (select sql_hash_value
                       from v$session
                      where sid = &sid)
 order by piece;

--查询当前的系统等待事件
select *
  from v$session_wait
 where event not like '%sql*net%'
   and event not like '%rdbms%'
   and event not like '%timer%'
   and event not like '%jobq%'
 order by event, seconds_in_wait;

--查询详细的当前系统等待事件
select s.sid, s.username, w.seq#, w.event, w.p1text, w.p1, w.p2text, w.p2, w.p3text, w.p3,
       w.seconds_in_wait, w.state, s.logon_time, s.osuser, s.program
  from v$session s, v$session_wait w
 where s.sid = w.sid
   and w.event not like '%sql*net%'
   and w.event not like '%rdbms%'
   and w.event not like '%timer%'
   and w.event not like '%jobq%'
 order by w.event, w.seconds_in_wait;

--查询等待db file sequential/scattered read的session正在执行的sql
select s.sid, s.username, t.hash_value, t.piece, t.sql_text
  from v$session s, v$session_wait w, v$sqltext t
 where s.sid = w.sid
   and s.sql_hash_value = t.hash_value
   and w.event in ('db file sequential read', 'db file scattered read')
 order by s.sid, t.piece;

--查询等待db file sequential/scattered read对应的数据库对象
select s.sid, s.username, w.seq#, w.event,
       d.segment_type, d.owner || '.' || d.segment_name as segment_name,
       w.seconds_in_wait, w.state, s.logon_time
  from v$session s, v$session_wait w, dba_extents d
 where s.sid = w.sid
   and d.file_id = w.p1
   and w.p2 between d.block_id and d.block_id d.blocks - 1
   and w.event in ('db file sequential read', 'db file scattered read')
 order by w.event, segment_name;

--查询导致lock的sid,spid,locked_object,lock_type等信息
select
       l.sid, p.spid, s.username,s.logon_time, s.osuser, s.program, l.type,
       case l.type when 'tm' then o.object_name when 'tx' then '' end as object_name,
       decode (l.lmode, 0, '0=none', 1, '1=null', 2, '2=rs', 3, '3=rx', 4, '4=s', 5, '5=srx', 6, '6=x') lmode,
       case l.request when 0 then '' else 'blocked by ' || l.id2 end as blocked,
       case l.block when 0 then '' else l.id2 || ' is blocking' end as blocking,
       l.request, l.ctime
  from v$lock l, v$session s, dba_objects o, v$process p
 where l.type in ('tx', 'tm')
   and s.paddr = p.addr
   and l.sid = s.sid
   and l.id1 = o.object_id( )
 order by s.username, l.sid, l.ctime;

--查询导致ddl lock的详细信息
select s.sid, p.spid, s.username, a.owner || '.' || a.name as object_name,
       a.type, a.mode_held, a.mode_requested, s.osuser, s.logon_time, s.program
  from dba_ddl_locks a, v$session s, v$process p
 where s.sid = a.session_id
   and s.paddr = p.addr
   and (a.mode_held = 'exclusive' or a.mode_requested = 'exclusive')
 order by s.username, a.name;

--查询事务使用的回滚段
select s.username, s.sid, s.serial#, t.ubafil "uba filenum",
       t.ubablk "uba block number", t.used_ublk "number of 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;
####################################################################################################


--查询library cache pin等待事件等待的对象
--视图缩写:[k]ernel [g]eneric [l]ibrary cache manager [ob]ject
select addr, kglhdadr, kglhdpar, kglnaobj, kglnahsh, kglhdobj
  from x$kglob
 where kglhdadr in (select p1raw
                      from v$session_wait
                     where event like '%library%');

--查询library cache pin等待事件中持有被等待对象的session信息
--视图缩写:[k]ernel [g]eneric [l]ibrary cache manager object [p]i[n]s
select a.sid, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse,
       b.kglpnses, b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq
  from v$session a, x$kglpn b
 where a.saddr = b.kglpnuse
   and b.kglpnmod <> 0
   and b.kglpnhdl in (select p1raw
                        from v$session_wait
                       where event like '%library%');

--查询library cache pin等待事件中持有被等待对象的session执行的sql语句
select sql_text
  from v$sqlarea
 where (v$sqlarea.address, v$sqlarea.hash_value) in (
          select sql_address, sql_hash_value
            from v$session
           where sid in (
                    select sid
                      from v$session a, x$kglpn b
                     where a.saddr = b.kglpnuse
                       and b.kglpnmod <> 0
                       and b.kglpnhdl in (select p1raw
                                            from v$session_wait
                                           where event like '%library%')));

--查询哪个session正在使用某个对象(library cache)
select distinct s.sid,
                s.username,
                s.logon_time,
                s.osuser,
                s.program,
                b.kglnahsh as sql_hash_value,
                b.kglnaobj as sql_text
  from v$session s, x$kglpn n, x$kglob b
 where n.kglpnuse = s.saddr
   and upper(b.kglnaobj) like upper('%&object_name%')
   and n.kglpnhdl = b.kglhdadr;

--查询v$session_wait用户pin住了哪些对象(library cache)
select distinct s.sid,
                s.username,
                s.logon_time,
                s.osuser,
                s.program,
                n.kglpnmod,
                b.kglnahsh as sql_hash_value,
                b.kglnaobj as sql_text
  from v$session s, x$kglpn n, x$kglob b
 where n.kglpnuse = s.saddr
   and n.kglpnhdl = b.kglhdadr
   and s.sid in (select sid
                   from v$session_wait
                  where event not like '%sql*net%'
                    and event not like '%rdbms%'
                    and event not like '%timer%'
                    and event not like '%jobq%')
 order by s.username;

--查询哪些大对象被载入shared pool时导致其它对象被老化
select s.sid, s.username, s.logon_time, s.osuser, s.program,
       k.ksmlrcom, k.ksmlrsiz, k.ksmlrnum, k.ksmlrhon, k.ksmlrses
  from x$ksmlru k, v$session s
 where s.saddr = k.ksmlrses
   and ksmlrsiz > 0;
####################################################################################################


--查询schema哪些表是全表扫描
select o.name, x.tch
  from obj$ o, x$bh x, dba_users u
 where x.obj = o.dataobj#
   and standard.bitand(x.flag, 524288) > 0
   and u.username = upper('&username')
 order by x.tch desc;

--查询低效率的sql(buffer_gets排序)
select *
  from (select s.sid,
               b.spid,
               s.sql_hash_value,
               q.sql_text,
               q.executions,
               q.buffer_gets,
               round(q.buffer_gets / q.executions) as buffer_per_exec,
               round(q.elapsed_time / q.executions) as cpu_time_per_exec,
               q.cpu_time,
               q.elapsed_time,
               q.disk_reads,
               q.rows_processed
          from v$session s, v$process b, v$sql q
         where s.sql_hash_value = q.hash_value
           and s.paddr = b.addr
           and s.status = 'active'
           and s.type = 'user'
           and q.buffer_gets > 0
           and q.executions > 0
         order by buffer_per_exec desc)
 where rownum <= 10;
####################################################################################################


--监控buffercache命中率
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';

--监控librarycache命中率
select sum (pins) total_pins, sum (reloads) total_reloads,
       sum (reloads) / sum (pins) * 100 libcache_reload_ratio
  from v$librarycache;

--查询产生的跟踪文件名
select p1.value || '/' || p2.value || '_ora_' || p.spid || '.trc' 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');

--删除表中的重复记录
delete from table_name a
      where rowid >
               (select min (rowid)
                  from table_name b
                 where b.pk_column_1 = a.pk_column_1
                   and b.pk_column_2 = a.pk_column_2);