您好,登錄后才能下訂單哦!
這篇文章主要講解了“oracle數(shù)據(jù)庫(kù)CPU過(guò)高問(wèn)題分析”,文中的講解內(nèi)容簡(jiǎn)單清晰,易于學(xué)習(xí)與理解,下面請(qǐng)大家跟著小編的思路慢慢深入,一起來(lái)研究和學(xué)習(xí)“oracle數(shù)據(jù)庫(kù)CPU過(guò)高問(wèn)題分析”吧!
一、執(zhí)行一條sql查詢無(wú)索引的大表,使服務(wù)器一核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
二、通過(guò)v$sqlstats視圖根據(jù)cpu時(shí)間進(jìn)行排序,查出當(dāng)前CPU消耗最大的前兩條sql,可以看到排名第一的就是剛才執(zhí)行的測(cè)試sql,至此,即可定位到引發(fā)高CPU的語(yǔ)句,下一步進(jìn)行語(yǔ)句分析;
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
三、根據(jù)第2步得到的sqlid,通過(guò)視圖dba_hist_sql_plan可以查看到此sql的執(zhí)行計(jì)劃,可以看到進(jìn)行了全表掃描
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
四、如果問(wèn)題已經(jīng)出現(xiàn)一段時(shí)間,在v$sqlstats視圖中已搜索不到數(shù)據(jù),可以前往查看視圖dba_hist_sqlstat,里面會(huì)有歷史的sql數(shù)據(jù)記錄,根據(jù)下面sql可以查看到第四條即為剛才測(cè)試的語(yǔ)句,然后根據(jù)sqlid仍可至dba_hist_sql_plan視圖中查看執(zhí)行計(jì)劃
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數(shù)據(jù)庫(kù)CPU過(guò)高問(wèn)題分析”的內(nèi)容了,經(jīng)過(guò)本文的學(xué)習(xí)后,相信大家對(duì)oracle數(shù)據(jù)庫(kù)CPU過(guò)高問(wèn)題分析這一問(wèn)題有了更深刻的體會(huì),具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云,小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章,歡迎關(guān)注!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。