Shulou(Shulou.com)06/01 Report--

This article introduces what to do when ORACLE takes up a lot of system CPU and causes system downtime. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Oracle database often encounters the situation of high CPU utilization. At this time, there are serious low-performance SQL statements in the database. This kind of SQL statement consumes CPU resources greatly, resulting in poor performance of the whole system. Of course, there are many reasons for the serious poor performance of SQL statements, and the specific reasons should be analyzed in detail. Here is an actual case to illustrate how to diagnose and solve this kind of problem with high CPU utilization.

Operating system: Linux7.0

Database: Oracle11.2.0.4

Problem description: the field engineer reports that the database is so slow that almost all application operations cannot be carried out normally. Soon after, the system was disconnected and went down.

First, after restarting the system, start the database. Executing top found that CPU resources are almost exhausted, and there are many processes with high CPU consumption, but both memory and Icano are not high, as shown below:

Last pid: 26136; load averages: 8.89,8.91,8.12

216 processes: 204 sleeping, 8 running, 4 on cpu

CPU states: 0.6% idle, 97.3% user, 1.8% kernel, 0.2% iowait, 0.0% swap

Memory: 8192M real, 1166M free, 14M swap in use, 8179M swap free

PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND

25725 oracle 1 500 4550M 4508M cpu2 12:23 11.23% oracle

25774 oracle 1 41 0 4550M 4508M run 14:25 10.66% oracle

26016 oracle 1 31 0 4550M 4508M run 5:41 10.37% oracle

26010 oracle 1 41 0 4550M 4508M run 4:40 9.81% oracle

26014 oracle 1 51 0 4550M 4506M cpu6 4:19 9.76% oracle

25873 oracle 1 41 0 4550M 4508M run 12:10 9.45% oracle

25723 oracle 1 500 4550M 4508M run 15:09 9.40% oracle

26121 oracle 1 41 0 4550M 4506M cpu0 1:13 9.28% oracle

25745 oracle 1 41 0 4551M 4512m run 9:33 9.28% oracle

26136 oracle 1 41 0 4550M 4506M run 0:06 5.61% oracle

409 root 15 59 0 7168K 7008K sleep 173.1H 0.52% picld

25653 oracle 1 59 0 4550M 4508M sleep 1:01 0.46% oracle

25565 oracle 1 59 0 4550M 4508M sleep 0:07 0.24% oracle

25703 oracle 1 59 0 4550M 4506M sleep 0:08 0.13% oracle

25701 oracle 1 59 0 4550M 4509M sleep 0:23 0.10% oracle

So first check the database alarm log ALERT file, and found that there is no error, the log shows that the database is running normally, eliminating problems in the database itself.

Then to see what these CPU-intensive Oracle processes are doing, use the following SQL statement:

Select sql_text,spid,v$session.program,process from

Vandalism qlarea, vandalism, session, and process.

Where v$sqlarea.address=v$session.sql_address

And v$sqlarea.hash_value=v$session.sql_hash_value

And v$session.paddr=v$process.addr

And v$process.spid in (PID)

Replace the PID in the script with the PID of the process with high CPU in top, and get the SQL statement executed by the corresponding Oracle process. It is found that the process with high CPU resource is all executing the same SQL statement:

Select username "username", to_char (timestamp,'DD-MON-YYYY HH24:MI:SS') "time_stamp", action_name "statement", os_username "os_username", userhost "userhost", returncode | | decode (returncode,'1004','-Wrong Connection','1005','-NULL Password','1017','-Wrong Password','1045','-Insufficient Priviledge','0','-Login Accepted','--') "returncode" from sys.dba_audit_session where (sysdate-timestamp) * 24

< 1 and returncode 0 order by timestamp; 基本上可以肯定是这个SQL引起了系统CPU资源大量被占用，那究竟是什么原因造成这个SQL这么大量占用CPU资源呢，从上面的SQL语句中我们可以看到sys.dba_audit_session这张表，由此可以确定是由于审计的原因导致数据库占用大量CPU。 查看数据库审计信息： SQL>

Show parameter audit

NAME TYPE VALUE

-

Audit_file_dest string / u01/app/oracle/admin/orcl/adump

Audit_sys_operations boolean FALSE

Audit_syslog_level string

Audit_trail string DB

You can see that the database audit is open, and the audited record is stored in the database (sys.aud$).

Problem handling methods:

1. If audit is not necessary, you can turn off the audit function

SQL > alter system set audit_trail=none scope=spfile

SQL > showdown immediate

SQL > startup

two。 Delete existing audit information

You can directly truncate table aud$

Or take dbms_audit_mgmt to clean up.

3. Or move the aud$ table to another tablespace to reduce the pressure on the system tablespace and the risk of bursting.

Attachment: 11g setting instructions on audit_trail parameters:

AUDIT_TRAILPropertyDescriptionParameter typeStringSyntaxAUDIT_TRAIL = {none | os | db [, extended] | xml [, extended]} Default valuenoneModifiableNoBasicNo

AUDIT_TRAIL enables or disables database auditing.

Values:

None

Disables standard auditing. This value is the default if the AUDIT_TRAIL parameter was not set in the initialization parameter file or if you created the database using a method other than Database Configuration Assistant. If you created the database using Database Configuration Assistant, then the default is db.

Os

Directs all audit records to an operating system file. Oracle recommends that you use the os setting, particularly if you are using an ultra-secure database configuration.

Db

Directs audit records to the database audit trail (the SYS.AUD$ table), except for records that are always written to the operating system audit trail. Use this setting for a general database for manageability.

If the database was started in read-only mode with AUDIT_TRAIL set to db, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.

Db, extended

Performs all actions of AUDIT_TRAIL=db, and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available. These two columns are populated only when this parameter is specified.

If the database was started in read-only mode with AUDIT_TRAIL set to db, extended, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.

Xml

Writes to the operating system audit record file in XML format. Records all elements of the AuditRecord node except Sql_Text and Sql_Bind to the operating system XML audit file.

Xml, extended

Performs all actions of AUDIT_TRAIL=xml, and populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$table, wherever possible. These columns are populated only when this parameter is specified.

You can use the SQL AUDIT statement to set auditing options regardless of the setting of this parameter.

About ORACLE occupies a lot of system CPU caused by the system downtime what to do to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.