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

转载于:  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.