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

 

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

 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

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，如下：

 

1

2

3

4

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

SQL_ID

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

gmkaj9nz7vyvw

 

  • 2、通过sql_id查看具体sql

 

1

2

3

4

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

 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

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查看语句详情

 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

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

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

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

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

|   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语句直接分析

 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

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

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

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

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

|   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.
# oracle
Logo
魔乐社区

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

更多推荐

cover

「极限压缩 量化未来」Modelers GeekDay上海站圆满落幕

avatar 魔乐社区
cover

量化挑战赛冠军专访：4小时啃下W4A8量化，我靠的是这些经验

avatar 魔乐社区
cover

魔乐社区月度精选（26年3月）

avatar 魔乐社区