Record one Oracle SQL optimization experience-excessive CPU consumption

My colleague called and told me that the CPU usage of the database is too high and swap is frequently exchanged. I 'd like to take a look at it now. Here, I will record the entire process for your reference and let you provide some comments.

$ Topas

Topas Monitor for host:Fjlt_wb_db01EVENTS/QUEUESFILE/TTY

Mon Feb 13 10:10:09 2012Interval:2Cswitch13932Readch2718.1 K

Syscall344.8 KWritech162.7 K

CPUUser %Kern %Wait %Idle %  Reads1231Rawin0

ALL89.110.90.00.0 Writes861Ttyout725

Forks6Igets0

NetworkKBPSI-PackO-PackKB-InKB-OutExecs6Namei414

Total97.6 K24.9 K51.5 K30.8 K66.9 K Runqueue43.0Dirblk0

Waitqueue0.0

DiskBusy %KBPSTps kb-Read KB-WritMEMORY

Total13.022.6 K 2715.021.7 K932.5PAGINGReal, MB31744

Faults4337% Comp85

FileSystemKBPSTps kb-Read KB-WritSteals0% Noncomp4

Total638.7398.5637.41.3PgspIn0% Client4

PgspOut0

NamePID CPU %PgSp Owner PageIn0   PAGING SPACE

Oracle6684703217.310.4 oracle PageOut1   Size, MB32768

Oracle337515464.310.7 oracle Sios1 % Used39

Oracle370936062.610.6 oracle   % Free61

Oracle515770902.410.4 oracleNFS (CILS/sec)

Oracle607523822.410.2 oracleSerV20WPAR Activ0

Oracle24251842.310.5 oracleCliV20WPAR Total0

Oracle385355162.110.6 oracleSerV30Press: "h"-help

Oracle654049542.010.5 oracleCliV30"Q"-quit

Oracle402394862.010.3 oracle

Oracle652085902.011.6 oracle

Oracle605556281.910.5 oracle

Oracle236586561.910.3 oracle

Oracle478416581.910.5 oracle

Oracle523635521.810.3 oracle

Oracle23596841.810.5 oracle

Oracle319161541.710.6 oracle

Oracle465305801.710.9 oracle

Oracle7213821.611.7 oracle

Oracle28840601.610.5 oracle

Oracle56364141.610.5 oracle

  Found CPU Usage is 100% , Swap Frequent partition exchanges, where the process number is 66847032 Of Oracle User Process CPU Usage is 17.3% .

Kthr memory page faults cpu
---------------------------------------------------------------
R B avm fre re pi po fr sr cy in sy cs us sy id wa
2 0 7506815 658832 0 0 0 0 0 6913 154614 31 12 58 0
5 0 7506576 659071 0 0 0 0 0 5763 193844 53 11 36 0
5 0 7506767 658880 0 0 0 0 0 8699 157452 43 11 45 0
2 0 7506557 659089 0 0 0 0 0 6392 135539 27 9 64 0
2 0 7506286 659359 0 0 0 0 0 6518 126193 21 10 69 0

Pi , Po Both 0 , Description Swap Partitions are not frequently exchanged. Locate CPU High usage
Based on the current time Ash Report. Found CPU Wait events take up the activity time 67.62%

See Sqlid Is Dczhdxppd0fmm Of SQL Occupied 41.4 Activity time

After waiting for half a day, I did not respond. It is estimated that Sga Too large, resulting in slow query, put it first to let him run.

Use PL \ SQL Execute the following SQL statement to check the wait events in the current system.

Select * from v $ session_wait where wait_class # <> 6;

 

1 2 3 Next Page