这篇文章主要讲解了"神谕数据库中央处理器过高问题分析",文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"神谕数据库中央处理器过高问题分析"吧！
一、执行一条结构化查询语言查询无索引的大表，使服务器一核中央处理器使用近100%
sqlse从Scott中选择*。t3where name=DBMS _ random。字符串(' u '，10)；top-19:35336032上涨1天，12:26，6用户，loadaverage:0.15，0.04，0.01
任务：236总计，2运行，234休眠，0停止，0僵尸
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:2039644ktotal总计总计，1935220kused已用，104424kfree可用，140204k缓冲区
Swap:4095992ktotal，46008kused，4049984kfree，1273692kcached
PidUserPrNivirtreshrs % CPU % MEMTIME命令
20394 racle 200542 m54 m50 r99。82 .82 raclenbs 200542。82
p;
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 where name=dbms_random.string('u', 10) 60.965731
SQL_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 = 'TABLE' AND SUBOBJECT_NAME IS NULL) STATUS, (SELECT GENER
ATED FROM SYS.ALL_OBJECTS WHERE OWNER =
.776882
三、根据第2步得到的sqlid，通过视图dba_hist_sql_plan可以查看到此sql的执行计划，可以看到进行了全表扫描
SQL> select id,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) from dba_hist_sqlstat group 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 T1 VALUES (S_T1_ID.NEXTVAL,DBMS_RANDOM.STRING('u', 10),SYSDATE)
7j89gjdpf4m4u398767377 265823588
begin
for i in 1 .. 1000000 loop
insert into t1 values (s_t1_id.Nextval,dbms_ran
gmkaj9nz7vyvw324451675 261535240select * from scott.t3 where name=dbms_random.string('u', 10)
a0qbnz3z4x4ns179105773 179105773select * from scott.t1 where name=dbms_random.string('u', 10)
SQL> select id,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
感谢各位的阅读，以上就是“oracle数据库CPU过高问题分析”的内容了，经过本文的学习后，相信大家对oracle数据库CPU过高问题分析这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！
内容来源网络，如有侵权，联系删除，本文地址：https://www.230890.com/zhan/82734.html