[size=16.0pt] [size=16.0pt]占用CPU严重的SQL语句 问题一（2个相似SQL）： selectcount(a.DATA_UPDATE_NOTIFY_ID), nvl(max(a.DATA_UPDATE_NOTIFY_ID), 0), nvl(max(a.TABLE_UPDATE_CONFIG_ID), 0) from DATA_UPDATE_NOTIFY a, TABLE_UPDATE_CONFIG b where b.TABLE_NAME='EXEC_SCRIPT' anda. TABLE_UPDATE_CONFIG_ID=b.TABLE_UPDATE_CONFIG_ID and a.STATE='0SA';[img=560,195]file:///C:/Users/%E9%99%88/AppData/Local/Temp/msohtmlclip1/01/clip_image002.jpg[/img]这个语句sql_id= "9gyr3kh1gmrj6",[color=#0070C0]在7天内被执行了157097次,单次执行1.2秒,占用99%CPU-------------------------------------------------------------------------------- selectcount(a.DATA_UPDATE_NOTIFY_ID), nvl(max(a.DATA_UPDATE_NOTIFY_ID), 0), nvl(max(a.TABLE_UPDATE_CONFIG_ID), 0) from DATA_UPDATE_NOTIFY a, TABLE_UPDATE_CONFIG b whereb.TABLE_NAME=:table_name anda. TABLE_UPDATE_CONFIG_ID=b.TABLE_UPDATE_CONFIG_ID and a.STATE='0SA'[img=560,176]file:///C:/Users/%E9%99%88/AppData/Local/Temp/msohtmlclip1/01/clip_image004.jpg[/img]这个语句sql_id= "czhr13utwfj8g",[color=#0070C0]在7天内被执行157098 次，单次执行时间也是1.2秒，,占用99%CPU [color=#0070C0]很奇怪，为什么这样的两个语句如此类似？ 注:SQL> select count(*) from DATA_UPDATE_NOTIFY; COUNT(*)---------- 10 SQL> select count(*) from TABLE_UPDATE_CONFIG; COUNT(*)---------- 13问题二: SELECT DISTINCT A.NE_ID FROM NET_ELEMENT AWHERE A.NE_TYPE_ID = :B2 ANDA.REGION_ID = TO_NUMBER(:B1 ) AND A.STATE = '0SA'[img=502,117]file:///C:/Users/%E9%99%88/AppData/Local/Temp/msohtmlclip1/01/clip_image006.jpg[/img]SQL_ID="g6jh6fxr0mqhv".占用CPU100%，7天执行30775075次，平均每次执行0.0063秒 注:SQL> select count(*) from NET_ELEMENT; COUNT(*)---------- 38963问题三： insertinto INP_DATA_PERF(FILE_ID,NE_ID,NE_TYPE_ID,AUDIT_ID,AREA_CODE ,SYSLOC,MSG_SOURCE,KPI_ID,KPI_NAME,KBP,KBP_NAME,MIN,AVG,MAX,MSG_TIME) values(:1,pkp_inp_convert.convert_inp_data_perf(:2,:3),:4,:5,:6,:7,1 ,replace(:8,'-',''),:9,:10,:11,:12,:13,:14,to_date(:15,'YYYYMMDDHH24M I')) 说明：6vv2w2k5jan6d " areresponsible for 99% of the database time spent on the SELECTstatement with SQL_ID "g6jh6fxr0mqhv". 单条插入时间居然是0.5秒注:SQL> select count(*) from INP_DATA_PERF; COUNT(*)---------- 52821086问题四: selecttable_update_config_id,max(data_update_notify_id) from data_update_notify WHERE STATE=:STATE and table_update_config_id in (1,2) group by table_update_config_id order by 1,2[img=560,143]file:///C:/Users/%E9%99%88/AppData/Local/Temp/msohtmlclip1/01/clip_image008.jpg[/img]SQL_ID="7z7gbaxzkpru5".占用CPU99%，7天执行64153次，平均每次执行1.2秒 注:SQL> select count(*) from DATA_UPDATE_NOTIFY; COUNT(*)---------- 10问题五: selectdistinct to_char(receive_date,'YYYY/MM/DD HH24:MI:SS'), to_char(region_id), nvl(perf_origin, ' '), to_char(nvl(data_msg_filelist_id, 0)), nvl(kpi_detail_class, ' '), to_char(dr_id) from ne_perf_msg where state = '10' and perf_class='2' andreceive_date >=sysdate-1 and receive_date<=sysdate [img=560,170]file:///C:/Users/%E9%99%88/AppData/Local/Temp/msohtmlclip1/01/clip_image010.jpg[/img]SQL_ID="f8psrmrx8k08q".占用CPU100%，7天执行43197次，平均每次执行1.9秒注:1. SQL> select count(*) from ne_perf_msg; COUNT(*) ---------- 334852268