`

经常使用的Oracle监控语句

 
阅读更多

转载:

 

经常使用的Oracle监控语句

最近项目需要,性能、数据准确性等问题一个接一个解决,很累,但我是很看好这种机遇。

客户有时会说服务器CPU占用100%,妈啊,小型机啊,为了要证明是程序问题还是SQL语句的问题,或者是程序死锁问题,通过各种方式进行检查,发现有一个明细表的查询出问题了,真是大快人心!于是把一些在监控过程中学到的知识记录下来,做个标记,日后有用啊,现在的DBA身价不菲。

 

--根据FILE_ID & BLOCK_ID获得对象名称
SELECT /*+ RULE*/ 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 /*+ RULE*/ 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 /*+ RULE*/
       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 /*+ RULE*/ 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 /*+ RULE*/ 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 /*+ RULE*/ 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);

分享到:
评论

相关推荐

    多年积累的oracle监控语句,没有最全只有更全

    本资料集合了多年的Oracle监控经验,涵盖了多个关键领域的监控语句,包括表空间、索引、慢SQL、等待事件、锁机制、回滚段(undo)等重要方面。下面将详细阐述这些监控知识点。 1. 表空间监控:表空间是Oracle存储...

    oracle常用监控SQL语句集合

    在Oracle数据库管理中,了解和掌握一些常用的监控SQL语句是至关重要的,这些语句能够帮助DBA(数据库管理员)实时了解数据库的运行状态、性能瓶颈以及资源使用情况。"oracle常用监控SQL语句集合"提供了这样的工具集...

    Oracle监控数据库性能的语句

    根据给定的文件信息,以下是对Oracle监控数据库性能的关键SQL语句的知识点详细解析: ### 1. 监控会话等待事件 SQL语句: ```sql select event, sum(decode(wait_time, 0, 0, 1)) "Prev", sum(decode(wait_time,...

    oracle常用性能监控SQL语句

    ### Oracle常用性能监控SQL语句知识点详解 #### 一、SQL Area Disk Reads 监控 - **SQL**: `select * from sys.v_$sqlarea where disk_reads &gt; 100` - **用途**: 此SQL语句用于查看Oracle数据库中SQL执行时磁盘读取...

    Oracle 监控 分析工具MyOra 3.0

    Oracle数据库是全球广泛使用的大型企业级关系型数据库管理系统,对于其高效、稳定运行的监控与分析至关重要。"Oracle 监控 分析工具MyOra 3.0"是一款专为DBA(数据库管理员)设计的实用工具,它可以帮助用户实时掌握...

    oracle性能监控常用语句集合

    通过学习和应用这些Oracle性能监控语句,你可以深入了解数据库的运行状态,及时发现并解决性能瓶颈,确保系统的高效稳定运行。在实际工作中,结合日志分析、性能测试工具以及Oracle的其他管理工具,如Enterprise ...

    Oracle SQL语句性能优化

    使用Oracle的性能监视工具,如V$视图,监控SQL执行情况,找出性能瓶颈,并进行针对性的调整。 综上所述,Oracle SQL语句性能优化涉及多个方面,包括选择合适的优化器、优化数据访问方式、共享SQL语句、索引策略、...

    oracle性能监控sql 监控当前会话 执行的sql及io等信息

    Oracle性能监控SQL——监控当前会话执行的SQL及IO等信息 Oracle性能监控是数据库管理员的重要职责之一,通过...但是,这些SQL语句只是Oracle性能监控的一部分,实际操作中还需要结合实际情况来选择合适的监控策略。

    oracle常用监控脚本

    Oracle数据库是全球广泛使用的大型企业级关系型数据库管理系统,对于其高效、稳定运行,监控显得尤为重要。"Oracle常用监控脚本"通常包含了多种用于检查数据库性能、资源使用情况以及问题排查的工具和脚本。这些脚本...

    oracle sql 语句暗示 hints

    ### Oracle SQL 语句暗示 (Hints) 全面解析 #### 一、引言 在当前企业级应用环境中,为了实现成本控制与性能优化的目标,高效编写应用程序变得尤为重要。这不仅涉及程序的设计与实现,还包括运行时资源的有效利用...

    压测Oracle的SQL语句的性能情况

    5. **资源监控**:在压测过程中,应监控CPU使用率、内存占用、I/O操作等,以识别系统瓶颈。 6. **数据库参数调优**:调整数据库参数如PGA、SGA、并行度等,可以改善性能,但也需谨慎,避免过度调优。 7. **负载...

    Oracle数据库sql语句 跟踪器

    Oracle数据库SQL语句跟踪器,通常被称为SQL Monitor,是一种强大的工具,用于监控和分析数据库中的SQL语句执行情况。在Oracle环境中,理解SQL语句的行为是优化数据库性能的关键。SQL Monitor提供实时视图,帮助DBA...

    oracle利用批处理文件执行SQL语句,bat连接oracle数据库并执行语句

    在这个场景中,我们可以利用批处理文件来简化Oracle数据库的操作,如删除用户、创建用户、创建表和触发器,以及执行其他SQL语句。 首先,我们需要了解如何在批处理文件中连接Oracle数据库。这通常通过Oracle的...

    oracle监控系统

    Oracle 监控系统是一款专为数据库管理员(DBA)设计的工具,其程序版本为1.0.2,采用先进的Qt4框架进行开发。Qt4是一个跨平台的应用程序开发框架,支持多种操作系统,如Windows、Linux和macOS,使得这款监控工具具有...

    Oracle语句规划详解

    - **规则描述**:使用SQL监控工具来识别低效的SQL语句,从而进行针对性的优化。 - **示例**:使用`DBMS_SQL_MONITOR`包或`v$sql`视图来查找执行效率低下的SQL语句。 ##### 23. 使用TKPROF工具查询SQL性能状态 - **...

    oracle数据库语句工具

    本文将详细介绍如何使用工具监听Oracle数据库的SQL语句,特别是针对使用Hibernate框架时遇到的带问号(?)占位符的语句。 在Oracle数据库中,SQL语句是用于查询、插入、更新和删除数据的主要工具。当开发人员使用...

    利用SQL语句实现数据库性能监控.pdf

    根据提供的文件内容,以下是对数据库性能监控使用SQL语句的知识点的详细解析: 数据库性能监控的重要性: 数据库是构建在信息技术系统上的核心组件,尤其在气象信息系统中,数据库的稳定性和性能直接影响着整个系统...

    Oracle实用SQL语句

    Oracle实用SQL语句 监控数据库性能的SQL 查看tablespace的空间使用情況

    [自己开发]一款非常好用的抓取Oracle数据库SQL语句的工具

    Oracle SQL Profiler,自己设计算法写的一款非常好用的抓取Oracle数据库SQL语句的工具,可以再没有源码的情况下监控ORACLE数据库服务器的v$sqlarea视图抓取出从点击开始按钮到点击结束按钮期间执行过的SQL语句。...

Global site tag (gtag.js) - Google Analytics