Find SQL statements that consume a large CPU

Statement
Find SQL statements that consume a large CPU



First, use UNIX system commands to view resources

#sar 1 9

12:15:27 73 27 0 012:15:28 70 30 0 012:15:29 80 20 0 012 : 15:30 84 16 0 012:15:31 21 5 1 73

#top

TTY PID USERNAME PRI NI SIZE RES State time%wcpu%cpu COMMAND?    23093 Oracle 154 29748K 3112K run 20:53 4.59 4.58 ORACLEBV? 23087 Oracle 154 29636K 3016K run 24:18 0.93 0.93 ORACLEBV

Use Oracle data dictionary to find SQL

Sql>set line 240sql>set Verify offsql>column SID format 999sql>column PID format 999 sql>column s_# format 999sql>column username format A9 heading "ORA User" sql>column program format a29sql>column SQL format a60sql> COLUMN osname format A9 Heading "OS User" Sql>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,a.sql_text sqlfrom v$process P, V$session S,v$ Sqlarea A WHERE p.addr = S.paddrand s.sql_address = a.address (+) and p.spid like '%&1% ';
Enter value for 1:23,209 (System consumes a larger process ID)
Find the result:

SELECT * from Cc_por where Status=1 and deleted=0

Use the Autotrace parsing statement.

Sql>set Timing on

Sql>set Autotrace on

Sql>select * from Cc_por where Status=1 and deleted=0;

Analysis and improvement based on the results.