oracle数据库CPU过高问题分析

这篇文章主要讲解了“oracle数据库CPU过高问题分析”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“oracle数据库CPU过高问题分析”吧！

一、执行一条sql查询无索引的大表，使服务器一核CPU使用近100%

SQL> select * from scott.t3 where name=dbms_random.string(u, 10);top - 19:35:32 up 1 day, 12:26,  6 users,  load average: 0.15, 0.04, 0.01 Tasks: 236 total,   2 running, 234 sleeping,   0 stopped,   0 zombie Cpu0  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st Cpu1  : 99.3%us,  0.7%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st Mem:   2039644k total,  1935220k used,   104424k free,   140204k buffers Swap:  4095992k total,    46008k used,  4049984k free,  1273692k cached    PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                              20394 oracle    20   0  542m  54m  50m R 99.8  2.8   0:08.82 oracle                                                                                                    1 root      20   0 19396 1204  936 S  0.0  0.1   0:01.28 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.44 migration/0                                                                                               4 root      20   0     0    0    0 S  0.0  0.0   0:22.40 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.04 watchdog/0                                                                                                7 root      RT   0     0    0    0 S  0.0  0.0   0:00.48 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.50 ksoftirqd/1                                                                                              10 root      RT   0     0    0    0 S  0.0  0.0   0:00.06 watchdog/1                                                                                               11 root      20   0     0    0    0 S  0.0  0.0   0:13.44 events/0                                                                                                 12 root      20   0     0    0    0 S  0.0  0.0   0:00.51 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/0                                                                                            21 root      20   0     0    0    0 S  0.0  0.0   0:00.00 kintegrityd/1                                                                                            22 root      20   0     0    0    0 S  0.0  0.0   0:00.62 kblockd/0                                                                                                23 root      20   0     0    0    0 S  0.0  0.0   0:00.19 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_hotplug                                                                                            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_usbd                                                                                            31 root      20   0     0    0    0 S  0.0  0.0   0:00.02 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                                                                                                37 root      20   0     0    0    0 S  0.0  0.0   0:00.01 khungtaskd                                                                                               38 root      20   0     0    0    0 S  0.0  0.0   0:05.76 kswapd0                                                                                                  39 root      25   5     0    0    0 S  0.0  0.0   0:00.00 ksmd

二、通过v$sqlstats视图根据cpu时间进行排序，查出当前CPU消耗最大的前两条sql，可以看到排名第一的就是刚才执行的测试sql，至此，即可定位到引发高CPU的语句，下一步进行语句分析；

SQL> select * from (select s.sql_id,s.SQL_TEXT,s.CPU_TIME / 1000000 cpu from v$sqlstats s order by s.CPU_TIME desc) where rownum<=2; SQL_IDSQL_TEXT       CPU ------------------------------------------------------------------------------------------------------- gmkaj9nz7vyvwselect *from scott.t3 wherename=dbms_random.string(u, 10) 60.965731SQL_IDSQL_TEXT       CPU ------------------------------------------------------------------------------------------------------- d2dkktkqvxhp6 SELECT T.TABLE_NAME, T.TABLESPACE_NAME, T.NUM_ROWS, TC.COMMENTS, T.LOGGING, T.TA BLE_LOCK, T.ROW_MOVEMENT, T.CLUSTER_NAME, T.PCT_FREE, T.PCT_USED, T.INI_TRANS, T.MAX_TRANS, T.INITIAL_EXTENT, T.NEXT_EXTENT, T.MIN_EXTENTS, T.MAX_EXTENTS, T.PCT _INCREASE, T.FREELISTS, T.FREELIST_GROUPS, T.BLOCKS, T.EMPTY_BLOCKS, T.AVG_SPACE , T.CHAIN_CNT, T.AVG_SPACE_FREELIST_BLOCKS, T.NUM_FREELIST_BLOCKS, T.SAMPLE_SIZE , T.GLOBAL_STATS, T.USER_STATS, T.DURATION, T.SKIP_CORRUPT, T.AVG_ROW_LEN, T.OBJ ECT_ID_TYPE, T.TABLE_TYPE_OWNER, T.TABLE_TYPE, T.COMPRESS_FOR, T.STATUS DROP_TAB LE_STATUS, T.COMPRESSION, T.DROPPED, T.CLUSTER_OWNER, T.DEPENDENCIES, T.IOT_NAME , T.BACKED_UP, T.DEGREE, T.INSTANCES, T.CACHE, T.LAST_ANALYZED, T.PARTITIONED, T.IOT_TYPE, T.TEMPORARY, T.SECONDARY, T.NESTED, T.BUFFER_POOL, T.MONITORING, (SEL ECT STATUS FROM SYS.ALL_OBJECTS WHERE OWNER = T.OWNER AND OBJECT_NAME = T.TABLE_ NAME AND OBJECT_TYPE =TABLEAND SUBOBJECT_NAME IS NULL) STATUS, (SELECT GENER ATED FROM SYS.ALL_OBJECTS WHERE OWNER =.776882

三、根据第2步得到的sqlid，通过视图dba_hist_sql_plan可以查看到此sql的执行计划，可以看到进行了全表扫描

SQL> selectid,operation,options,object_owner,object_name,object_type,optimizer,cost,bytes,cpu_cost,time from dba_hist_sql_plan where sql_id=gmkaj9nz7vyvw; IDOPERATIONOPTIONSOBJECT_OWNEROBJECT_NAMEOBJECT_TYPEOPTIMIZERCOST  BYTES   CPU_COST      TIME-------------------- -------------------- ---------- ---------- ------------------------------ -------------------- ---------- ---------- ------------------------------ -------------------- ---------- ---------- ---------- 0SELECT STATEMENTALL_ROWS3632 1TABLE ACCESSFULLSCOTTT3TABLE3632836060800816736244

四、如果问题已经出现一段时间，在v$sqlstats视图中已搜索不到数据，可以前往查看视图dba_hist_sqlstat，里面会有历史的sql数据记录，根据下面sql可以查看到第四条即为刚才测试的语句，然后根据sqlid仍可至dba_hist_sql_plan视图中查看执行计划

SQL> select t.*,(select sql_text from dba_hist_sqltext where sql_id=t.sql_id) sql_text from (select sql_id,sum(cpu_time_total),sum(cpu_time_delta) fromdba_hist_sqlstatgroup by sql_id order by 2 desc) t where rownum<=5; SQL_ID      SUM(CPU_TIME_TOTAL) SUM(CPU_TIME_DELTA)SQL_TEXT --------------------------------------------------------------------------------------------- ------------------- ------------------- 46hjcvyssg7a2922170810    545961002BEGIN DBMS_SQLPA.execute_analysis_task(task_name=>:sts_task); END; 5hrxg25g8bdpd502210755    308952092INSERT INTO T1VALUES (S_T1_ID.NEXTVAL,DBMS_RANDOM.STRING(u, 10),SYSDATE) 7j89gjdpf4m4u398767377    265823588 beginfor i in 1 .. 1000000 loop insert into t1 values (s_t1_id.Nextval,dbms_ran gmkaj9nz7vyvw324451675261535240select * from scott.t3 where name=dbms_random.string(u, 10) a0qbnz3z4x4ns179105773    179105773select * from scott.t1 where name=dbms_random.string(u, 10) SQL> selectid,operation,options,object_owner,object_name,object_type,optimizer,cost,bytes,cpu_cost,timefrom dba_hist_sql_plan where sql_id=gmkaj9nz7vyvw； IDOPERATIONOPTIONSOBJECT_OWNEROBJECT_NAMEOBJECT_TYPEOPTIMIZERCOST  BYTES   CPU_COST      TIME -------------------- -------------------- ---------- ---------- ------------------------------ -------------------- ---------- ---------- ------------------------------ -------------------- ---------- ---------- ----------0SELECT STATEMENTALL_ROWS3632 1TABLE ACCESSFULLSCOTTT3TABLE3632836060800816736244

感谢各位的阅读，以上就是“oracle数据库CPU过高问题分析”的内容了，经过本文的学习后，相信大家对oracle数据库CPU过高问题分析这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！