oracle数据库CPU过高问题定位、分析

留念土豆

1297人浏览 · 2021-04-01 10:37:04

留念土豆 · 2021-04-01 10:37:04 发布

转载于: http://blog.itpub.net/31479729/viewspace-2168477/

oracle数据库CPU过高问题定位、分析

top - 00:05:18 up 21:37, 7 users, load average: 0.54, 0.17, 0.14

Tasks: 236 total, 3 running, 233 sleeping, 0 stopped, 0 zombie

Cpu0 :100.0%us, 0.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0

Cpu1 : 99.7%us, 0.3%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0

Mem: 2039644k total, 1935444k used, 104200k free, 10604k buffers

Swap: 4095992k total, 41048k used, 4054944k free, 1473288k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

14468 oracle 20 0 540m 46m 43m R 100.0 2.4 0:17.76 oracle

14470 oracle 20 0 540m 26m 23m R 99.8 1.4 0:16.51 oracle

1 root 20 0 19396 1184 916 S 0.0 0.1 0:00.88 init

2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd

3 root RT 0 0 0 0 S 0.0 0.0 0:00.31 migration/0

4 root 20 0 0 0 0 S 0.0 0.0 0:22.38 ksoftirqd/0

5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0

6 root RT 0 0 0 0 S 0.0 0.0 0:00.02 watchdog/0

7 root RT 0 0 0 0 S 0.0 0.0 0:00.32 migration/1

8 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/1

9 root 20 0 0 0 0 S 0.0 0.0 0:00.31 ksoftirqd/1

10 root RT 0 0 0 0 S 0.0 0.0 0:00.03 watchdog/1

11 root 20 0 0 0 0 S 0.0 0.0 0:13.22 events/0

12 root 20 0 0 0 0 S 0.0 0.0 0:00.23 events/1

13 root 20 0 0 0 0 S 0.0 0.0 0:00.00 cpuset

14 root 20 0 0 0 0 S 0.0 0.0 0:00.00 khelper

15 root 20 0 0 0 0 S 0.0 0.0 0:00.00 netns

16 root 20 0 0 0 0 S 0.0 0.0 0:00.00 async/mgr

17 root 20 0 0 0 0 S 0.0 0.0 0:00.00 pm

18 root 20 0 0 0 0 S 0.0 0.0 0:00.00 sync_supers

19 root 20 0 0 0 0 S 0.0 0.0 0:00.00 bdi-default

20 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kintegrityd/

21 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kintegrityd/

22 root 20 0 0 0 0 S 0.0 0.0 0:00.53 kblockd/0

23 root 20 0 0 0 0 S 0.0 0.0 0:00.11 kblockd/1

24 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kacpid

25 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kacpi_notify

26 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kacpi_hotplu

27 root 20 0 0 0 0 S 0.0 0.0 0:00.00 ata/0

28 root 20 0 0 0 0 S 0.0 0.0 0:00.00 ata/1

29 root 20 0 0 0 0 S 0.0 0.0 0:00.00 ata_aux

30 root 20 0 0 0 0 S 0.0 0.0 0:00.00 ksuspend_usb

31 root 20 0 0 0 0 S 0.0 0.0 0:00.01 khubd

32 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kseriod

33 root 20 0 0 0 0 S 0.0 0.0 0:00.00 md/0

34 root 20 0 0 0 0 S 0.0 0.0 0:00.00 md/1

35 root 20 0 0 0 0 S 0.0 0.0 0:00.00 md_misc/0

36 root 20 0 0 0 0 S 0.0 0.0 0:00.00 md_misc/1

通过top监控可以发现服务器的CPU被oracle进程14468与14470给消耗殆尽

一、定位问题

1、通过PID与v$process、v$session两张视图快速定位出语句的sql_id，如下：

SQL> select sql_id from v$session where paddr= (select addr from v$process where spid ='14468');

SQL_ID

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

gmkaj9nz7vyvw

2、通过sql_id查看具体sql

SQL> select sql_text from v$sql where sql_id='gmkaj9nz7vyvw';

SQL_TEXT

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

select * from scott.t3 where name=dbms_random.string('u', 10)

二、分析问题

A、通过oracle工具脚本awrsqrpt.sql分析具体sql

SQL> @awrsqrpt

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

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

1506959389 ORCL1 orcl

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type:

Type Specified: html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name InstanceHost

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

* 1506959389 1 ORCL orcl localhost.lo

caldomain

Using 1506959389 for database Id

Using 1 for instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed. Pressing <return> without

specifying a number lists all completed snapshots.

Enter value for num_days:

Listing all Completed Snapshots

Snap

Instance DB Name Snap Id Snap Started Level

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

orcl ORCL 21 25 Jul 2018 22:30 1

22 25 Jul 2018 23:42 1

23 25 Jul 2018 23:45 1

24 25 Jul 2018 23:47 1

25 25 Jul 2018 23:49 1

26 25 Jul 2018 23:51 1

27 26 Jul 2018 00:04 1

28 26 Jul 2018 00:07 1

29 26 Jul 2018 00:08 1

30 26 Jul 2018 00:09 1

31 26 Jul 2018 18:49 1

32 26 Jul 2018 19:11 1

33 26 Jul 2018 19:16 1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 32

Begin Snapshot Id specified: 32

Enter value for end_snap: 33

End Snapshot Id specified: 33

Specify the SQL Id

~~~~~~~~~~~~~~~~~~

Enter value for sql_id: gmkaj9nz7vyvw

SQL ID specified: gmkaj9nz7vyvw

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrsqlrpt_1_32_33.html. To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: /home/oracle/awrsql1.html

Using the report name /home/oracle/awrsql1.html

查看生成的html结果

B、通过dbms_xplan.display_awr与sql_id查看语句详情

SQL> select * from table(dbms_xplan.display_awr('gmkaj9nz7vyvw'));

PLAN_TABLE_OUTPUT

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

SQL_ID gmkaj9nz7vyvw

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

select * from scott.t3 where name=dbms_random.string('u', 10)

Plan hash value: 4161002650

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

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

| 0 | SELECT STATEMENT | | | | 3632 (100)| |

| 1 | TABLE ACCESS FULL| T3 | 24590 | 816K| 3632 (6)| 00:00:44 |

PLAN_TABLE_OUTPUT

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

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

Note

-----

- dynamic sampling used for this statement (level=2)

17 rows selected.

C、通过sql语句直接分析

SQL> explain plan for select * from scott.t3 where name=dbms_random.string('u', 10);

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 4161002650

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

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

| 0 | SELECT STATEMENT | | 24590 | 816K| 3632 (6)| 00:00:44 |

|* 1 | TABLE ACCESS FULL| T3 | 24590 | 816K| 3632 (6)| 00:00:44 |

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

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

1 - filter("NAME"="DBMS_RANDOM"."STRING"('u',10))

Note

-----

- dynamic sampling used for this statement (level=2)

17 rows selected.

魔乐社区

魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。

更多推荐

小参数・大码力・易部署 | Qwen3.6-27B上线魔乐社区，基于昇腾的部署教程来了

继一周前模型开源发布后，千问再度开源Qwen3.6-27B —— 一个拥有270亿参数的稠密多模态模型，也是社区呼声最高的模型规格。Qwen3.6-27B 依然支持多模态思考与非思考模式，在智能体编程方面达到了旗舰级表现，全面超越前代开源旗舰 Qwen3.5-397B-A17B（总参数397B / 激活参数17B的MoE模型）。作为稠密架构，它无需MoE路由即可部署，是开发者在实用、可广泛部署规模