sql学习 占用CPU严重的SQL语句.docx
占用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'Execution 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 FULLDATA_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=:table_nameanda. TABLE_UPDATE_CONFIGD=b.TABLE_UPDATE_CONFIGD and a. STATE:'OSA'Execution 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_UPDATE_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_l'PDATE_NOTIFY;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. REGION_ID = TO_NIMBER(:B1 ) AND A. STATE = 'OSA'Execution Plan回Operation|Name |RowsByteCost (%CPU)Time |丁SELECT STATEMENT137 (100)1TABLE ACCESS HSnNET_ELEMENT118137 (1)00:00:02SQL_ID='g6jh6fxr0mqhv.占用CPU100%, 7天执行30775075次,平均每次执行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, :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 update notify id) fi'om 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 FULLDATA_U PDATE.NOTFY437352476175(2)00:00:03SQL I D=，z 7z 7gbaxzkpru5，z.占用CPU99乐7天执行64153次，平均每次执行L2秒 注：SQL> select count (*) from DATA_l'PDATE_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<=sysdateExecution PlanElOperation|Name | Rows |Bytes |Cost (%CPU) |Time |Pstart | Pstop |0SELECTSTATEMENT190 (100)1HASH UNIQUE1115190 (1)00:00:032FLTER3PARTmON RANGE fTERATOR1115189(0)00:00:03KEYKEY4TABLE ACCESS FULLNE_PERF_MSG1115189 (0)00:00:03KEYKEYBack to Plan 1(PHV: 187229518)Back to TopSQL_ID=，f8psrmrx8k08q，z.占用CPU 100%, 7天执行43197次,平均每次执行1. 9秒 注：1. SQL> select count(*) from ne_perf_msg;COUNT(*)334852268
