服务器top中oracle百分之百,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  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  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

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

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

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.