If the problem relates to CPU bound applications then CPU information for each session can be examined to determine the culprits. The v$sesstat view can be queried to find high cpu using sessions and then SQL can be listed.
1.Find the 'CPU used by this session' statistic.
SQL>SELECT name ,statistic# FROM v$statname WHERE name LIKE '%CPU%session';
NAME STATISTIC#
----------------------------------- ----------
CPU used by this session 14
2. Then determine which session is using most of the cpu.
SQL>SELECT * FROM v$sesstat WHERE statistic# = 14;
SID STATISTIC# VALUE
---------- ---------- ----------
1 14 0
2 14 0
3 14 0
4 14 0
5 14 0
6 14 0
7 14 0
8 14 0
9 14 0
10 14 0
11 14 0
12 14 0
16 14 1930
3. Lookup details for the session which is using most of the cpu.
SQL>SELECT address ,SUBSTR(sql_text,1,20) Text, buffer_gets, executions,buffer_gets/executions AVG
FROM v$sqlarea a, v$session s
WHERE sid = 16
AND s.sql_address = a.address
AND executions > 0
ORDER BY 5;
4. Use v$sqltext to extract the whole SQL text.
set pages 50000 lines 32767
col SPID for a10
col PROGRAM for a15
col OSUSER for a10
col ACTION for a10
col EVENT for a25
col SQL_TEXT for a25
col MACHINE for a10
col P1TEXT for a10
col P2TEXT for a10
col P3TEXT for a10
SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine, b.type,
b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.sid = '&sid' ORDER BY a.spid, c.piece
/
5. Once the whole SQL statement has been identified it can be tuned.
Explain the queries and examine their access paths.
Autotrace is a useful tool for examining access paths.
No comments:
Post a Comment