oracle找出消耗 CPU 最高的进程对应的 SQL 语句

SET LINE 240
SET VERIFY OFF
COLUMN SID FORMAT 999
COLUMN PID FORMAT 999
COLUMN S_# FORMAT 999
COLUMN USERNAME FORMAT A9 HEADING "ORA USER"
COLUMN PROGRAM FORMAT A29
COLUMN SQL     FORMAT A60
COLUMN OSNAME FORMAT A9 HEADING "OS USER"
SELECT P.PID PID,S.SID SID,P.SPID SPID,S.USERNAME USERNAME,S.OSUSER OSNAME,P.SERIAL# S_#,
P.TERMINAL,P.PROGRAM PROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT, 1 , 80 )) SQL
FROM V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR 
AND S.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE '%&1%' ;

ENTER VALUE FOR 1 : PID?   --(这里输入占用 CPU 最高的进程对应的 PID )
SET TERMOUT OFF
SPOOL MAXCPU.TXT
SELECT '++' ||S.USERNAME USERNAME,RTRIM(REPLACE (A.SQL_TEXT,CHR( 10 ), '' ))|| ';' 
FROM V$PROCESS P, V$SESSION S,V$SQLAREA A WHERE P.ADDR = S.PADDR 
AND S.SQL_ADDRESS = A.ADDRESS (+) AND P.SPID LIKE '%&&1%' ;
Enter value for 1: PID     --(这里输入占用 CPU 最高的进程对应的 PID )
spool off

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle,linux