《sql学习 占用CPU严重的SQL语句.doc》由会员分享，可在线阅读，更多相关《sql学习 占用CPU严重的SQL语句.doc（4页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、 占用CPU严重的SQL语句问题一（2个相似SQL）： select count(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;这个语句sql
2、_id= 9gyr3kh1gmrj6,在7天内被执行了157097次,单次执行1.2秒,占用99%CPU- select count(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=:table_name anda. TABLE_UPDATE_CONFIG_ID=b.TABLE_UPDATE_CONFIG_
3、ID and a.STATE=0SA这个语句sql_id= czhr13utwfj8g,在7天内被执行157098 次，单次执行时间也是1.2秒，,占用99%CPU很奇怪，为什么这样的两个语句如此类似？注:SQL select count(*) from DATA_UPDATE_NOTIFY; COUNT(*)- 10SQL select count(*) from TABLE_UPDATE_CONFIG; COUNT(*)- 13问题二: SELECT DISTINCT A.NE_ID FROM NET_ELEMENT A WHERE A.NE_TYPE_ID = :B2 AND A.REG
4、ION_ID = TO_NUMBER(:B1 ) AND A.STATE = 0SASQL_ID=g6jh6fxr0mqhv.占用CPU100%，7天执行30775075次，平均每次执行0.0063秒注:SQL select count(*) from NET_ELEMENT; COUNT(*)- 38963问题三： insert into 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)
5、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 are responsible for 99% of the database time spent on the SELECT statement with SQL_ID g6jh6fxr0mqhv. 单条插入时间居然是0.5秒注:SQL select count(*) from INP_
6、DATA_PERF; COUNT(*)- 52821086问题四: select table_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,2SQL_ID=7z7gbaxzkpru5.占用CPU99%，7天执行64153次，平均每次执行1.2秒注:SQL select count(*) from DATA_UPD
7、ATE_NOTIFY; COUNT(*)- 10问题五: select distinct 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 and receive_date =sysdate-1 and receive_date select count(*) from ne_perf_msg; COUNT(*) - 334852268