找出提交频繁的SQL语句

找出提交频繁的SQL语句

标签（空格分隔）： Oracle 优化 脚本

---

检查是否有过分提交的语句(关键是得到sid就好办了，代入V$SESSION就可知道是什么进程，接下来还可以知道V$SQL)

• 提交次数最多的SESSION

set linesize 1000
column sid             format 99999
column program         format a20
column machine         format a20
column logon_time      format date
column wait_class      format a10
column event           format a32
column sql_id          format 9999
column prev_sql_id     format 9999
column WAIT_TIME       format 9999
column SECONDS_IN_WAIT format 9999
select t1.sid, t1.value, t2.name
  from v$sesstat t1, v$statname t2
 where t2.name like '%user commits%' 
   and t1.STATISTIC# = t2.STATISTIC#
   and value >= 10000
 order by value desc;

• 取得SID既可以代入到V$SESSION和V$SQL中去分析

 --得出SQL_ID
  select t.SID,
         t.PROGRAM,
		     t.EVENT,
         t.LOGON_TIME,
         t.WAIT_TIME,
         t.SECONDS_IN_WAIT,
         t.SQL_ID,
         t.PREV_SQL_ID
   from v$session t
  where sid in (194);

• 根据sql_id或prev_sql_id代入得到SQL

  select t.sql_id,
         t.sql_text,
         t.EXECUTIONS,
         t.FIRST_LOAD_TIME,
         t.LAST_LOAD_TIME
  from v$sqlarea t
 where sql_id in ('ccpn5c32bmfmf');

• 也请关注一下这个：

 select * from v$active_session_history where session_id=194