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

最新推荐文章于 2024-07-04 13:59:02 发布

csdn7755

最新推荐文章于 2024-07-04 13:59:02 发布

阅读量403

点赞数

文章标签：数据库

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
--------------------------------------------------------------------------
| 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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31479729/viewspace-2168477/，如需转载，请注明出处，否则将追究法律责任。

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