Oracle database Server CPU resource consumption is high (75%)

Environment:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0-64bit Production

CentOS Release 6.5 X64

Symptom: System CPU usage reaches 75%, viewing system process resource status.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6F/2A/wKiom1WTeJHRpKQUAAQoNpVWXMs155.jpg "title=" 1.png " alt= "Wkiom1wtejhrpkquaaqonpvwxms155.jpg"/>

View in Database

Select T.sql_text,s.sid, s.serial#,s.program,s.process,s.username,p.spid from V$sqlarea T, v$session S, v$process p wher e t.address=s.sql_address and T.hash_value=s.sql_hash_value and S.paddr=p.addr and P.spid in (PID);

PID is the PID in the operating system.

View result SQL statements and program programs,

To view the execution plan:

Plan_table_output

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------

Plan Hash value:3345675291

------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

------------------------------------------------------------------------------------------------

| 0 |                        SELECT STATEMENT |     |    1 | 66 | 48343 (1) | 00:09:41 |

|  1 |                        SORT AGGREGATE |     |    1 |            66 |          | |

|   2 | VIEW |     Vm_nwvw_1 |    1 | 66 | 48343 (1) | 00:09:41 |

|    3 |                        HASH GROUP by |     |    1 | 67 | 48343 (1) | 00:09:41 |

|* 4 |                        HASH JOIN ANTI NA |   | 426 | 28542 | 48342 (1) | 00:09:41 |

|* 5 | TABLE ACCESS full| T_pps |   42578 | 997k| 37806 (1) | 00:07:34 |

Plan_table_output

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------

|* 6 |  TABLE ACCESS full|  push_usent |   2883k| 118m| 10521 (1) | 00:02:07 |

------------------------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

4-access ("QN". CIMEI0 "=" Cimei ")

5-filter ("QN". CDATE ">=to_date (' 2015-06-07 00:00:00 ', ' syyyy-mm-dd hh24:mi:ss ')

and (LENGTH ("QN". " CIMEI0 ") =14 OR LENGTH (" QN "." CIMEI0 ") =15) and" QN "." CDATE "<=to_date ('

2015-06-28 00:00:00 ', ' syyyy-mm-dd hh24:mi:ss '))

6-filter ("Clastime" >to_date (' 2015-07-01 00:00:00 ', ' syyyy-mm-dd hh24:mi:ss '))

Plan_table_output

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------

Do is a full table scan, there are hundreds of of such SQL are executing simultaneously, causing CPU resources to skyrocket.

Solve:

1. End the program process release resources:
Alter system kill session ' sid,serial# ';

2, if too many PID occupancy process, you can consider restarting the library to release resources.

3, optimization of SQL statements and index optimization.

This article is from the "Bug" blog, please be sure to keep this source http://worms.blog.51cto.com/969144/1669715

Oracle database Server CPU resource consumption is high (75%)