One. Look at the utilization of the CPU through the TOP command:
#top
The following is the result of TOP: Last Pid: 11225; Load Averages: 7.95, 6.63, 6.25 17: 19: 35273 Processes: 259 Sleeping, 3 Running, 5 Zombie, 3 Stopped, 3 On CPucpu States: 10.0% iDLE, 75.0% User 15.0% Kernel, 0.0% iowait, 0.0% SwapMemory: 8192m Real, 4839M Free, 2147M SWAP in Use, 12G Swap Free Pid Username THR Pri Nice Size Res State Time CPU Command 10929 Oracle 1 59 0 1048M 1022M CPU / 6 2: 52 21.59% Oracle 11224 Oracle 1 59 0 1047M 1018M Run 0:03 4.22% Oracle 8800 Oracle 1 59 0 1048M 1022M Run 1: 3.99% Oracle 4354 Oracle 1 59 0 1049M 1023M CPU / 4 0:28 3.46% Oracle 3537 Oracle 1 59 0 1048m 1022M Sleep 1:01 1.93% Oracle 29499 Oracle 1 59 0 1048M 1022M Sleep 30.0H 1.84% Oracle 11185 Oracle 1 59 0 1047M 1020M Sleep 0:01 0.74% Oracle 11225 WacOS 1 44 0 2832K 1928K CPU / 0 0 : 00 0.65% TOP 9326 Oracle 1 59 0 1047M 1020M Sleep 0:58 0.50% Oracle 410 Root 14 59 0 7048K 6896K R UN 76.3H 0.42% PICLD 21363 Oracle 1 59 0 1047M 1019M Sleep 574: 35 0.36% Oracle 10782 Oracle 11 59 0 1052M 1024M SLEEP 749: 05 0.28% Oracle 13415 Oracle 1 59 0 1047M 1019M Sleep 6:07 0.27% Oracle 5679 Oracle 11 59 0 1052M 1026M Sleep 79:23 0.19% Oracle 5477 Oracle 258 59 0 1056M 1021M Sleep 57:32 0.14% Oracle
two. By analyzing the SQL statement corresponding to the highest process consumed by the CPU:
SQL> Set line 240
SQL> SET VERIFY OFF
SQL> Column Sid Format 999
SQL> Column Pid Format 999
SQL> Column S_ # Format 999
SQL> Column UserName Format A9 Heading "ORA User"
SQL> Column Program Format A29
SQL> Column SQL Format A60
SQL> 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, Rtrim (Substr (A.SQL_Text, 1, 80)) SQL
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: 10929
The final SQL statement found is as follows:
SELECT NVL (SUM (RURALCHARGE), 0.00) as fee from localusage where serviceid = 219987 and starttime> = to_date ('2003/12/30 13:24:20', 'YYYY / MM / DD HH24: MI: SS') ;
three. Query the partition index on the localusage table:
There are two partition indexes on the Localusage table by query:
SQL> Select index_name from user_part_indexes where table_name = 'localusage';
INDEX_NAME ------------------------------ i_localusage_sidui_localusage_st_seq
By performing a plan to analyze this statement UI_LOCALUSAGE_ST_SEQ index, do not use the I_LOCALUSAGE_SID index, I counted the efficiency of the index of UI_LOCALUSAGE_ST_SEQ, return time for 2 minutes for 36 seconds, and use i_localusage_sid this index The return time is more than 1 second. And Oracle default is the UI_LOCALUSAGE_ST_SEQ index, thus occupying a large number of CPU resources, resulting in a decline in CPU utilization.
The following is an analysis process using AutoTrace:
SQL> Connect Wacos / OSS
SQL> Set autotrace on
SQL> SET TIMING ONSQL> SELECT NVL (SUM (RURALCHARGE), 0.00) AS Fee from localusage where serviceid = 219987 and starttime> = to_date ('2003/12/30 13:24:20', 'YYYY / mm / dd HH24 : Mi: ss');
FeE ---------- 107.25
ELAPSED: 00: 02: 36.19 (Return Time 2 36 seconds)
Execution Plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 10 Card = 1 Bytes = 35) 1 0 Sort (AggRegate) 2 1 Partition Range (ALL) 3 2 Table Access (by local index rowid) Of 'localusage' (c ost = 10 card = 10035 bytes = 351225) 4 3 INDEX (RANGE SCAN) of 'ui_localusage_st_seq' (COST = 2 Card = 10035)
Statistics --------------------------------------------------- --------- 0 recursive calls 0 db block gets 11000821 consistent gets 349601 physical reads 0 redo size 292 bytes sent via SQL * Net to client 359 bytes received via SQL * Net from client 2 SQL * Net roundtrips to / From Clom Cliant 1 Sorts (Memory) 0 Sorts (Disk) 1 ROWS Processed
Enforce Oracle with Hint Use the i_localusage_sid index, then view the execution plan:
SQL> Connect Wacos / OSS
SQL> Set autotrace on
SQL> Set Timing On
SQL> SELECT / * INDEX (Localusage i_localusage_sid) * / NVL (SUM (RURALCHARGE), 0.00) AS Fee from localusage where serviceid = 219987 and starttime> = to_date ('2003/12/30 13:24:20', ' YYYY / MM / DD HH24: MI: SS ');
FeE ---------- 107.25
ELAPSED: 00: 00: 01.15 (Return Time 1 second)
Execution Plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 15 Card = 1 Bytes = 35) 1 0 Sort (AggRegate) 2 1 Partition Range (ALL) 3 2 Table Access (by local index rowid) Of 'localusage' (COST = 15 card = 10035 bytes = 351225)
4 3 Index (Range Scan) of 'i_localusage_sid' (cost = 14 card = 10035) statistics ------------------------- -------------------------------- 0 Recursive Calls 0 DB Block Gets 307 Consistent Gets 232 Physical Reads 0 redo size 292 BYTES SENT VIA SQL * NET To Client 359 BYtes Received Via SQL * Net from Clom Clism 2 SQL * Net RoundTrips To / from Clom Clism 0 Sorts (Memory) 0 Sorts (Disk) 1 Rows Processed
It is recommended that the R & D staff adjust the statement so that this statement defaults to use the i_localusage_sid index, or use Hint to use Hint to use the I_Localusage_SID index in the statement.
