cpu使用高的语句

-----------------使用高的sid select s.sid,s.value "CPU Used" from v$sesstat s,v$statname n where s.statistic#=n.statistic# and n.name='CPU used by this session' and s.value>0 order by 2 desc;

------------------------使用高的sid的内容--sqltext select sql_text from v$sqltext a where a.hash_value = ( select sql_hash_value from v$session b where b.sid='&sid' ) order by piece ASC;

 

查询耗资源的SQL
SELECT ADDRESS,HASH_VALUE,
       SUBSTR(SQL_TEXT, 1, 20) TEXT,
       BUFFER_GETS,
       EXECUTIONS,
       BUFFER_GETS / EXECUTIONS AVG
  FROM V$SQLAREA
WHERE EXECUTIONS > 0
   AND BUFFER_GETS > 100000
ORDER BY 6;

查询耗CPU资源的SQL
SELECT SS.SID,
       Decode(SE.Command,0 ,'[ 0] NoCommand',
                         1 ,'[ 1] CreateTable',
                         2 ,'[ 2] Insert',
                         3 ,'[ 3] Select',
                         6 ,'[ 6] Update',
                         7 ,'[ 7] Delete',
                         9 ,'[ 9] CreateIndex',
                         15,'[15] AlterTable',
                         21,'[21] CreateView',
                         23,'[23] ValidateIndex',
                         35,'[35] AlterDatabase',
                         39,'[39] CreateTablespace',
                         41,'[41] DropTablespace',
                         40,'[40] AlterTablespace',
                         53,'[53] DropUser',
                         62,'[62] AnalyzeTable',
                         63,'[63] AnalyzeIndex',
                         SE.Command || ':Other'),
       SS.VALUE CPU,
       SE.USERNAME,
       SE.PROGRAM,
       SE.sql_address,
       SE.sql_hash_value
  FROM V$SESSTAT SS, V$SESSION SE
WHERE SS.STATISTIC# IN
       (SELECT STATISTIC#
          FROM V$STATNAME
         WHERE NAME = 'CPU used by this session')
   AND SE.SID = SS.SID
   AND SS.SID > 6
ORDER BY SS.SID;