oracle优化9

找出消耗 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 )) SQLFROM 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( 这句放在最后执行 )

CPU 用率最高的 2 条 SQL 语句的获取
执行： top ，通过 top 获得 CPU 占用率最高的进程的 pid 。
SQL >SELECT SQL_TEXT,SPID,V$SESSION.PROGRAM,PROCESS FROM V$SQLAREA,V$SESSION,V$PROCESS WHERE V$SQLAREA.ADDRESS=V$SESSION.SQL_ADDRESS AND V$SQLAREA.HASH_VALUE=V$SESSION.SQL_HASH_VALUE AND V$SESSION.PADDR=V$PROCESS.ADDR AND V$PROCESS.SPID IN (PID);
COL MACHINE FORMAT A30
COL PROGRAM FORMAT A40
SET LINE 200
SQL >SELECT SID,SERIAL# ,USERNAME,OSUSER,MACHINE,PROGRAM,PROCESS,TO_CHAR(LOGON_TIME, 'YYYY/MM/DD HH24:MI:SS' ) FROM V$SESSION WHERE PADDR IN (SELECT ADDR FROM V$PROCESS WHERE SPID IN ([$SPID]));

SELECT SQL_TEXT FROM V$SQLTEXT_WITH_NEWLINES
WHERE HASH_VALUE=(SELECT SQL_HASH_VALUE FROM V$SESSION WHERE SID=&SID) ORDER BY PIECE;