oracle性能监测语法

1. 耗时语法查询：

--用以下语句找出长时间操作的SQL语句：
select longops.SID              "Session标识",
       longops.TIMESTAMP        "运行时间",
       longops.OPNAME           "操作简要说明",
       longops.TARGET           "操作运行对象",
       longops.START_TIME       "操作开始时间",
       longops.ELAPSED_SECONDS  "总花费时间(秒)",
       longops.TIME_REMAINING   "预计完成时间(秒)",
       longops.LAST_UPDATE_TIME "最后更新时间",
       longops.USERNAME         "执行操作的用户",
       sqlarea.sql_text         "执行语法",
       sqlarea.SQL_FULLTEXT     "执行完整的语法",
       sqlarea.EXECUTIONS       "执行次数",
       sqlarea.MODULE           "执行模块"
  from v$session_longops longops, 
       v$sqlarea sqlarea
 where longops.elapsed_seconds > 6
   and longops.sql_id = sqlarea.sql_id
 order by longops.LAST_UPDATE_TIME desc;

2. 当天耗时

SELECT S.SQL_TEXT, 
             S.SQL_FULLTEXT, 
             S.SQL_ID, 
             ROUND(ELAPSED_TIME / 1000000 / (CASE 
                     WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN 
                      1 
                     ELSE 
                      EXECUTIONS 
                   END), 
                   2) "执行时间'S'", 
             S.EXECUTIONS "执行次数", 
             S.OPTIMIZER_COST "COST", 
             S.SORTS, 
             S.MODULE, --连接模式（JDBC THIN CLIENT：程序） 
             -- S.LOCKED_TOTAL, 
             --S.PHYSICAL_READ_BYTES "物理读", 
             -- S.PHYSICAL_READ_REQUESTS "物理读请求", 
             -- S.PHYSICAL_WRITE_REQUESTS "物理写", 
             -- S.PHYSICAL_WRITE_BYTES "物理写请求", 
             S.ROWS_PROCESSED      "返回行数", 
             S.DISK_READS          "磁盘读", 
             S.DIRECT_WRITES       "直接路径写", 
             S.PARSING_SCHEMA_NAME, 
             S.LAST_ACTIVE_TIME 
        FROM GV$SQLAREA S 
       WHERE ROUND (ELAPSED_TIME / 1000000 / ( CASE 
                     WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN 
                      1 
                     ELSE 
                     EXECUTIONS 
                   END), 
                   2) > 5 --100 0000微秒=1S 
         AND S.PARSING_SCHEMA_NAME = USER 
         AND TO_CHAR(S.LAST_LOAD_TIME, 'YYYY-MM-DD' ) = 
             TO_CHAR( SYSDATE, 'YYYY-MM-DD' ) 
         AND S.COMMAND_TYPE IN (2 , 3, 5 , 6 , 189) /*值对应类型 2：INSERT、3：SELECT、6：UPDATE、7：DELETE、189：MERGE 查询 V$SQLCOMMAND*/ 
       ORDER BY "执行时间'S'" DESC; 

3. 堵闭塞查询

在系统实施工具中发现死锁。通过堵塞sql查询堵塞者的sid及SERIAL#，通过sid和SERIAL#的查询sql_id.通过sql_id确定sql语句。


相关sql：


查询sql_id:
select sql_id from v$session where sid=堵塞者sid and serial#=堵塞者serial;

查询sql：
select * from v$sqlarea where sql_id=‘堵塞者SQL_ID’ ;

堵塞查询：

Select b_s.SQL_ID blocked_sql_id,
       '节点 ' || a_s.INST_ID || ' session ' || a_s.sid || ',' || a_s.SERIAL# ||' 阻塞了 节点 ' || b_s.INST_ID || ' session ' || b_s.SID || ',' ||
       b_s.SERIAL# blockinfo,
       a_s.INST_ID,
       a_s.SID,
       a_s.SCHEMANAME,
       a_s.MODULE,
       a_s.STATUS,
       '后为被阻塞信息',
       b_s.INST_ID blocked_inst_id,
       b_s.SID blocked_sid,
       b_s.SCHEMANAME blocked_SCHEMANAME,
       b_s.EVENT blocked_event,
       b_s.MODULE blocked_module,
       b_s.STATUS blocked_status,
       b_s.SQL_ID blocked_sql_id,
       obj.owner blocked_owner,
       obj.object_name blocked_object_name,
       obj.OBJECT_TYPE blocked_OBJECT_TYPE,
       case
         when b_s.ROW_WAIT_OBJ# <> -1 then
          dbms_rowid.rowid_create(1,
                                  obj.DATA_OBJECT_ID,
                                  b_s.ROW_WAIT_FILE#,
                                  b_s.ROW_WAIT_BLOCK#,
                                  b_s.ROW_WAIT_ROW#)
         else
          '-1'
       end blocked_rowid, --被阻塞数据的rowid
       decode(obj.object_type,
              'TABLE',
              'select * from ' || obj.owner || '.' || obj.object_name ||
              ' where rowid=''' ||
              dbms_rowid.rowid_create(1,
                                      obj.DATA_OBJECT_ID,
                                      b_s.ROW_WAIT_FILE#,
                                      b_s.ROW_WAIT_BLOCK#,
                                      b_s.ROW_WAIT_ROW#) || '''',
              NULL) blocked_data_querysql
  from gv$session a_s, 
       gv$session b_s, 
       dba_objects obj
 where b_s.BLOCKING_INSTANCE is not null
   and b_s.BLOCKING_SESSION is not null
   and a_s.INST_ID = b_s.BLOCKING_INSTANCE
   and a_s.SID = b_s.BLOCKING_SESSION
   and b_s.ROW_WAIT_OBJ# = obj.object_id(+)
 order by a_s.inst_id, a_s.sid;