《sql学习 占用CPU严重的SQL语句.docx》由会员分享，可在线阅读，更多相关《sql学习 占用CPU严重的SQL语句.docx（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_LPDATE_NOTIFY a, TABLE_UPDATE_CONFIG b where b. TABLE_NAME=，EXEC_SCRIPT， and a. TABLE_UPDATE_CONFIGD=b.TABLE_UPDATE_CONFIGD and a. STATEiOSA;Executio
2、n PlanElOperation1Name11 Rows IBytes ICost(%CPU) ITime I|oSELECT STATEMENT248(100)1SORT AGGREGATE1312MERGE JOIN52616306248 (3)00:00:033TABLE ACCESS BY INDEX ROWDTABLE_UPDATE_CONFIG1192(0)00:00:014INDEX FULL SCANPK_TABLE_UPDATE_CONFIG131(0)00:00:015SORT JOIN420550460246 (3)00:00:036TABLE ACCESS FULLD
3、ATA_U POATE_NOTFY420550460244 (2)00:00:03这个语句 sql_id=，9gyr3khlgmrj6，在7天内被执行了 157097次,单次执行1. 2秒，占用99%CPUselect count(a.DATA_UPDATE_NOTIFY_ID), nv 1 (max (a. I)ATA_UP1)ATE_NOT I FY_1 D), 0),nvl(max(a. TABLE_UPDATE CONFIG ID), 0) from DATA_LPDATE NOTIFY a, TABLE_UPDATE_CONFIG b where b. TABLE_NAME=:tab
4、le_nameanda. TABLE_UPDATE_CONFIGD=b.TABLE_UPDATE_CONFIGD and a. STATE:OSAExecution PlanElOperation|Name|Rows |Bytes |Cost (%CPU) |Time |bSELECT STATEMENT248 (100)1SORT AGGREGATE1312MERGE JOUS2616306248(3)00:00:033TABLE ACCESS BY WDX ROWDTABLE_UPDATE_CONFIG1192(0)00:00:014INDEX FULL SCANPK_TABLE_UPDA
5、TE_CONFIG131(0)00:00:015SORT JON420550460246 (3)00:00:036TABLE ACCESS FULLDATA_U PDATE_NOTFY420550460244 (2)00:00:03这个语句 sql_id= czhrl3utwf j8g”,在7天内被执行157098次，单次执行时间也是1. 2秒，占用99%CPU很奇怪，为什么这样的两个语句如此类似？注：SQL select count (*) from D/TA_lPDATE_NOTIFY;10SQL select count(*) from TABLE_UPDATE_CONFIG;COUNT
6、(*)13问题二：SELECT DISTINCT A. NE_ID FROM NET_ELEMENT A WHERE A. NE_TYPE_ID = :B2 AND A. REGION_ID = TO_NIMBER(:B1 ) AND A. STATE = OSAExecution Plan回Operation|Name |RowsByteCost (%CPU)Time |丁SELECT STATEMENT137 (100)1TABLE ACCESS HSnNET_ELEMENT118137 (1)00:00:02SQL_ID=g6jh6fxr0mqhv.占用CPU100%, 7天执行3077
7、5075次,平均每次执行0. 0063秒注：SQL select count(*) from NET_ELEMENT;COUNT(*)38963问题三：insert into INP_DATA_PERF(FILE_ID, NED, NE_TYPE_ID, AUD I TD, 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, :
8、7, 1 ,replace(:8,:9, :10, :11, :12, :13, :14, to_date(:15, YYYYMMDDHH24M D)说明：6vv2w2k5jan6d are responsible for 99% of the database time spent on the SELECT statement with SQL_ID g6jh6fxr0mqhv”.单条插入时间居然是0.5秒注：SQL select count(*) from INPDATAPERF;52821086问题四：select tabie update config id, max (data u
9、pdate notify id) fiom data_update_notify WHERE STATE=:STATEandtableupdateconfigid in (1,2) group by tableupdatcconfigid order by 1,2Execution Plan回OperationjNameRows |BytesCost (%CPU) |Time |0SELECT STATEMENT178 (100)1SORT ORDER BY224178 (4)00:00:0300:00:032HASH GROUP BY224178 (4)3TABLE ACCESS FULLD
10、ATA_U PDATE.NOTFY437352476175(2)00:00:03SQL I D=，z 7z 7gbaxzkpru5，z.占用CPU99乐7天执行64153次，平均每次执行L2秒 注：SQL select count (*) from DATA_lPDATE_NOTIFY;COUNT(*)10问题五：select distinct to char(receive date,1 YYYY/MM/DD IIH24:MI:SS?), to_char(region_id), nvl(perforigin,), 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-l and receive date select count(*) from ne_perf_msg;COUNT(*)334852268