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

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

SQL>select*fromscott.t3wherename=dbms_random.string('u',10);

top-19:35:32up1day,12:26,6users,loadaverage:0.15,0.04,0.01Tasks:236total,2running,234sleeping,0stopped,0zombieCpu0:0.0%us,0.0%sy,0.0%ni,100.0%id,0.0%wa,0.0%hi,0.0%si,0.0%stCpu1:99.3%us,0.7%sy,0.0%ni,0.0%id,0.0%wa,0.0%hi,0.0%si,0.0%stMem:2039644ktotal,1935220kused,104424kfree,140204kbuffersSwap:4095992ktotal,46008kused,4049984kfree,1273692kcachedPIDUSERPRNIVIRTRESSHRS%CPU%MEMTIME+COMMAND20394oracle200542m54m50mR99.82.80:08.82oracle1root200193961204936S0.00.10:01.28init2root200000S0.00.00:00.00kthreadd3rootRT0000S0.00.00:00.44migration/04root200000S0.00.00:22.40ksoftirqd/05rootRT0000S0.00.00:00.00migration/06rootRT0000S0.00.00:00.04watchdog/07rootRT0000S0.00.00:00.48migration/18rootRT0000S0.00.00:00.00migration/19root200000S0.00.00:00.50ksoftirqd/110rootRT0000S0.00.00:00.06watchdog/111root200000S0.00.00:13.44events/012root200000S0.00.00:00.51events/113root200000S0.00.00:00.00cpuset14root200000S0.00.00:00.00khelper15root200000S0.00.00:00.00netns16root200000S0.00.00:00.00async/mgr17root200000S0.00.00:00.00pm18root200000S0.00.00:00.00sync_supers19root200000S0.00.00:00.00bdi-default20root200000S0.00.00:00.00kintegrityd/021root200000S0.00.00:00.00kintegrityd/122root200000S0.00.00:00.62kblockd/023root200000S0.00.00:00.19kblockd/124root200000S0.00.00:00.00kacpid25root200000S0.00.00:00.00kacpi_notify26root200000S0.00.00:00.00kacpi_hotplug27root200000S0.00.00:00.00ata/028root200000S0.00.00:00.00ata/129root200000S0.00.00:00.00ata_aux30root200000S0.00.00:00.00ksuspend_usbd31root200000S0.00.00:00.02khubd32root200000S0.00.00:00.00kseriod33root200000S0.00.00:00.00md/034root200000S0.00.00:00.00md/135root200000S0.00.00:00.00md_misc/036root200000S0.00.00:00.00md_misc/137root200000S0.00.00:00.01khungtaskd38root200000S0.00.00:05.76kswapd039root255000S0.00.00:00.00ksmd

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

SQL>select*from(selects.sql_id,s.SQL_TEXT,s.CPU_TIME/1000000cpufromv$sqlstatssorderbys.CPU_TIMEdesc)whererownum<=2;SQL_IDSQL_TEXTCPU-------------------------------------------------------------------------------------------------------gmkaj9nz7vyvwselect*fromscott.t3wherename=dbms_random.string('u',10)60.965731SQL_IDSQL_TEXTCPU-------------------------------------------------------------------------------------------------------d2dkktkqvxhp6SELECTT.TABLE_NAME,T.TABLESPACE_NAME,T.NUM_ROWS,TC.COMMENTS,T.LOGGING,T.TABLE_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.OBJECT_ID_TYPE,T.TABLE_TYPE_OWNER,T.TABLE_TYPE,T.COMPRESS_FOR,T.STATUSDROP_TABLE_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,(SELECTSTATUSFROMSYS.ALL_OBJECTSWHEREOWNER=T.OWNERANDOBJECT_NAME=T.TABLE_NAMEANDOBJECT_TYPE='TABLE'ANDSUBOBJECT_NAMEISNULL)STATUS,(SELECTGENERATEDFROMSYS.ALL_OBJECTSWHEREOWNER=.776882

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

SQL>selectid,operation,options,object_owner,object_name,object_type,optimizer,cost,bytes,cpu_cost,timefromdba_hist_sql_planwheresql_id='gmkaj9nz7vyvw';IDOPERATIONOPTIONSOBJECT_OWNEROBJECT_NAMEOBJECT_TYPEOPTIMIZERCOSTBYTESCPU_COSTTIME------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0SELECTSTATEMENTALL_ROWS36321TABLEACCESSFULLSCOTTT3TABLE3632836060800816736244

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

SQL>selectt.*,(selectsql_textfromdba_hist_sqltextwheresql_id=t.sql_id)sql_textfrom(selectsql_id,sum(cpu_time_total),sum(cpu_time_delta)fromdba_hist_sqlstatgroupbysql_idorderby2desc)twhererownum<=5;SQL_IDSUM(CPU_TIME_TOTAL)SUM(CPU_TIME_DELTA)SQL_TEXT-----------------------------------------------------------------------------------------------------------------------------------46hjcvyssg7a2922170810545961002BEGINDBMS_SQLPA.execute_analysis_task(task_name=>:sts_task);END;5hrxg25g8bdpd502210755308952092INSERTINTOT1VALUES(S_T1_ID.NEXTVAL,DBMS_RANDOM.STRING('u',10),SYSDATE)7j89gjdpf4m4u398767377265823588beginforiin1..1000000loopinsertintot1values(s_t1_id.Nextval,dbms_rangmkaj9nz7vyvw324451675261535240select*fromscott.t3wherename=dbms_random.string('u',10)a0qbnz3z4x4ns179105773179105773select*fromscott.t1wherename=dbms_random.string('u',10)SQL>selectid,operation,options,object_owner,object_name,object_type,optimizer,cost,bytes,cpu_cost,timefromdba_hist_sql_planwheresql_id='gmkaj9nz7vyvw'；IDOPERATIONOPTIONSOBJECT_OWNEROBJECT_NAMEOBJECT_TYPEOPTIMIZERCOSTBYTESCPU_COSTTIME------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0SELECTSTATEMENTALL_ROWS36321TABLEACCESSFULLSCOTTT3TABLE3632836060800816736244

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