以下的文章主要是教會你如何正確的查看Oracle CPU的情況,如果你對其相關的實際操作有興趣的話,以下的文章主要是介紹正確的查看Oracle CPU的情況,下面就是相關內容的具體描述。
發現那些SQL運行了大量的PARSE
- select sql_text, parse_calls, executions from v$sqlarea order by parse_calls desc;
SYS的總的PARSE情況
- select name, value from v$sysstat where name like 'parse count%';
CPU空間及繁忙情況
- select * from v$osstat;
查看每個Session的CPU利用情況:
- select ss.sid,se.command,ss.value CPU ,se.username,se.program
- 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 CPU desc;
比較一下哪個session的CPU使用時間最多,然後查看該Session的具體情況:
- select s.sid, s.event, s.MacHINE,s.OSUSER,s.wait_time, w.seq#, q.sql_text
- from v$session_wait w, v$session s, v$process p, v$sqlarea q
- where s.paddr=p.addr and
- s.sid=&p and
- s.sql_address=q.address;
占用CPU最高的10個Session及其SQL語句
- select s.sid,w.wait_time,w.seconds_in_wait,w.state,w.wait_time_micro,w.time_remaining_micro,w.time_since_last_wait_micro,p.USERNAME,
- status,server,schemaname,osuser,Machine,p.terminal,p.program,logon_time,w.event,w.wait_class,tracefile,sql_text,last_active_time
- from v$session_wait w, v$session s, v$process p, v$sqlarea q
- where s.paddr=p.addr and
- s.sid
- in
- ( select sid from
- (
- select ss.sid
- 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.value desc
- )where rownum<11
- )
- and s.sql_address=q.address;