找出消耗CPU最高的进程对应的SQL语句[教材]

找出消耗CPU最高的进程对应的SQL语句[教材]找出消耗CPU最高的进程对应的SQL语句根据SID找ORACLE的某个进程:SQL>SELECTPRO.SPIDFROMV$SESSIONSES,V$PROCESSPROWHERESES.SID=21ANDSES.PADDR=PRO.ADDR;监控当前数据库谁在运行什么SQL语句:SQL>SELECTOSUSER,USERNAME,SQL_TEXTFROMV$SESSIONA,V$SQLTEXTBWHEREA.SQL_ADDRESS=B.ADDRESSORDERBYADDRESS,PIECE;如何查看数据库中某用户，正在运行什么SQL语句SQL>SELECTSQL_TEXTFROMV$SQLTEXTT,V$SESSIONSWHERET.ADDRESS=S.SQL_ADDRESSANDT.HASH_VALUE=S.SQL_HASH_VALUEANDS.MACHINE='XXXXX'ORUSERNAME='WACOS';如何查出前台正在发出的sql语句:SQL>SELECTUSER_NAME,SQL_TEXTFROMV$OPEN_CURSORWHERESIDIN(SELECTSIDFROM(SELECTSID,SERIAL#FROMV$SESSIONWHERESTATUS='ACTIVE'));查询当前所执行的SQL语句:SQL>SELECTPROGRAM,SQL_ADDRESSFROMV$SESSIONWHEREPADDRIN(SELECTADDRFROMV$PROCESSWHERESPID=3556);PROGRAMSQL_ADDRESS----------------------------------------------------------------SQLPLUS@CTC20(TNSV1-V3)000000038FCB1A90SQL>SELECTSQL_TEXTFROMV$SQLAREAWHEREADDRESS='000000038FCB1A90';找出消耗CPU最高的进程对应的SQL语句:SETLINE240SETVERIFYOFFCOLUMNSIDFORMAT999COLUMNPIDFORMAT999COLUMNS_#FORMAT999COLUMNUSERNAMEFORMATA9HEADING"ORAUSER"COLUMNPROGRAMFORMATA29COLUMNSQLFORMATA60COLUMNOSNAMEFORMATA9HEADING"OSUSER"SELECTP.PIDPID,S.SIDSID,P.SPIDSPID,S.USERNAMEUSERNAME,S.OSUSEROSNAME,P.SERIAL#S_#,P.TERMINAL,P.PROGRAMPROGRAM,P.BACKGROUND,S.STATUS,RTRIM(SUBSTR(A.SQL_TEXT,1,80))SQLFROMV$PROCESSP,V$SESSIONS,V$SQLAREAAWHEREP.ADDR=S.PADDRANDS.SQL_ADDRESS=A.ADDRESS(+)ANDP.SPIDLIKE'%&1%';ENTERVALUEFOR1:PID?(这里输入占用CPU最高的进程对应的PID)SETTERMOUTOFFSPOOLMAXCPU.TXTSELECT'++'||S.USERNAMEUSERNAME,RTRIM(REPLACE(A.SQL_TEXT,CHR(10),''))||';'FROMV$PROCESSP,V$SESSIONS,V$SQLAREAAWHEREP.ADDR=S.PADDRANDS.SQL_ADDRESS=A.ADDRESS(+)ANDP.SPIDLIKE'%&&1%';Entervaluefor1:PID(这里输入占用CPU最高的进程对应的PID)spooloff(这句放在最后执行)CPU用率最高的2条SQL语句的获取执行:top，通过top获得CPU占用率最高的进程的pid。SQL>SELECTSQL_TEXT,SPID,V$SESSION.PROGRAM,PROCESSFROMV$SQLAREA,V$SESSION,V$PROCESSWHEREV$SQLAREA.ADDRESS=V$SESSION.SQL_ADDRESSANDV$SQLAREA.HASH_VALUE=V$SESSION.SQL_HASH_VALUEANDV$SESSION.PADDR=V$PROCESS.ADDRANDV$PROCESS.SPIDIN(PID);COLMACHINEFORMATA30COLPROGRAMFORMATA40SETLINE200SQL>SELECTSID,SERIAL#,USERNAME,OSUSER,MACHINE,PROGRAM,PROCESS,TO_CHAR(LOGON_TIME,'YYYY/MM/DDHH24:MI:SS')FROMV$SESSIONWHEREPADDRIN(SELECTADDRFROMV$PROCESSWHERESPIDIN([$SPID]));SELECTSQL_TEXTFROMV$SQLTEXT_WITH_NEWLINESWHEREHASH_VALUE=(SELECTSQL_HASH_VALUEFROMV$SESSIONWHERESID=&SID)ORDERBYPIECE;16、查看锁(lock)情况:SQL>SELECTLS.OSUSEROS_USER_NAME,LS.USERNAMEUSER_NAME,DECODE(LS.TYPE,'RW','ROWWAITENQUEUELOCK','TM','DMLENQUEUELOCK','TX','TRANSACTIONENQUEUELOCK','UL','USERSUPPLIEDLOCK')LOCK_TYPE,O.OBJECT_NAMEOBJECT,DECODE(LS.LMODE,1,NULL,2,'ROWSHARE',3,'ROWEXCLUSIVE',4,'SHARE',5,'SHAREROWEXCLUSIVE',6,'EXCLUSIVE',NULL)LOCK_MODE,O.OWNER,LS.SID,LS.SERIAL#SERIAL_NUM,LS.ID1,LS.ID2FROMSYS.DBA_OBJECTSO,(SELECTS.OSUSER,S.USERNAME,L.TYPE,L.LMODE,S.SID,S.SERIAL#,L.ID1,L.ID2FROMV$SESSIONS,V$LOCKLWHERES.SID=L.SID)LSWHEREO.OBJECT_ID=LS.ID1ANDO.OWNER<>'SYS'ORDERBYO.OWNER,O.OBJECT_NAME;SQL>SELECTSYS.V_$SESSION.OSUSER,SYS.V_$SESSION.MACHINE,V$LOCK.SID,SYS.V_$SESSION.SERIAL#,DECODE(V$LOCK.TYPE,'MR','MEDIARECOVERY','RT','REDOTHREAD','UN','USERNAME','TX','TRANSACTION','TM','DML','UL','PL/SQLUSERLOCK','DX','DISTRIBUTEDXACTION','CF','CONTROLFILE','IS','INSTANCESTATE','FS','FILESET','IR','INSTANCERECOVERY','ST','DISKSPACETRANSACTION','TS','TEMPSEGMENT','IV','LIBRARYCACHEINVALIDA-TION','LS','LOGSTARTORSWITCH','RW','ROWWAIT','SQ','SEQUENCENUMBER','TE','EXTENDTABLE','TT','TEMPTABLE','UNKNOWN')LOCKTYPE,RTRIM(OBJECT_TYPE)||''||RTRIM(OWNER)||'.'||OBJECT_NAMEOBJECT_NAME,DECODE(LMODE,0,'NONE',1,'NULL',2,'ROW-S',3,'ROW-X',4,'SHARE',5,'S/ROW-X',6,'EXCLUSIVE','UNKNOWN')LOCKMODE,DECODE(REQUEST,0,'NONE',1,'NULL',2,'ROW-S',3,'ROW-X',4,'SHARE',5,'S/ROW-X',6,'EXCLUSIVE','UNKNOWN')REQUESTMODE,CTIME,BLOCKBFROMV$LOCK,ALL_OBJECTS,SYS.V_$SESSIONWHEREV$LOCK.SID>6ANDSYS.V_$SESSION.SID=V$LOCK.SIDANDV$LOCK.ID1=ALL_OBJECTS.OBJECT_ID;以DBA角色,查看当前数据库里锁的情况可以用如下SQL语句:COLOWNERFORA12COLOBJECT_NAMEFORA16SELECTB.OWNER,B.OBJECT_NAME,L.SESSION_ID,L.LOCKED_MODEFROMV$LOCKED_OBJECTL,DBA_OBJECTSBWHEREB.OBJECT_ID=L.OBJECT_ID;SQL>SELECTT2.USERNAME,T2.SID,T2.SERIAL#,T2.LOGON_TIMEFROMV$LOCKED_OBJECTT1,V$SESSIONT2WHERET1.SESSION_ID=T2.SIDORDERBYT2.LOGON_TIME;SQL>SELECTSQL_ADDRESSFROMV$SESSIONWHERESID=;SQL>SELECT*FROMV$SQLTEXTWHEREADDRESS=;SQL>SELECTCOMMAND_TYPE,PIECE,SQL_TEXTFROMV$SQLTEXTWHEREADDRESS=(SELECTSQL_ADDRESSFROMV$SESSIONAWHERESID=18);SQL>SELECTOBJECT_IDFROMV$LOCKED_OBJECT;SQL>SELECTOBJECT_NAME,OBJECT_TYPEFROMDBA_OBJECTSWHEREOBJECT_ID='';如果有长期出现的一列，可能是没有释放的锁。我们可以用下面SQL语句杀掉长期没有释放非正常的锁:SQL>ALTERSYSTEMKILLSESSION'SID,SERIAL#';17、查看等待(wait)情况:SQL>SELECTV$WAITSTAT.CLASS,V$WAITSTAT.COUNTCOUNT,SUM(V$SYSSTAT.VALUE)SUM_VALUEFROMV$WAITSTAT,V$SYSSTATWHEREV$SYSSTAT.NAMEIN('DBBLOCKGETS','CONSISTENTGETS')GROUPBYV$WAITSTAT.CLASS,V$WAITSTAT.COUNT;18、查看sga情况:SQL>SELECTNAME,BYTESFROMSYS.V_$SGASTATORDERBYNAMEASC;19、查看catchedobject:SQL>SELECTOWNER,NAME,DB_LINK,NAMESPACE,TYPE,SHARABLE_MEM,LOADS,EXECUTIONS,LOCKS,PINS,KEPTFROMV$DB_OBJECT_CACHE;20、查看V$SQLAREA:SQL>SELECTSQL_TEXT,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,SORTS,VERSION_COUNT,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,EXECUTIONS,USERS_EXECUTING,LOADS,FIRST_LOAD_TIME,INVALIDATIONS,PARSE_CALLS,DISK_READS,BUFFER_GETS,ROWS_PROCESSEDFROMV$SQLAREA;21、查看object分类数量:SELECTDECODE(O.TYPE#,1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE','OTHER')OBJECT_TYPE,COUNT(*)QUANTITYFROMSYS.OBJ$OWHEREO.TYPE#>1GROUPBYDECODE(O.TYPE#,1,'INDEX',2,'TABLE',3,'CLUSTER',4,'VIEW',5,'SYNONYM',6,'SEQUENCE','OTHER')UNIONSELECT'COLUMN',COUNT(*)FROMSYS.COL$UNIONSELECT'DBLINK',COUNT(*)FROMALL_OBJECTS;22、有关connection的相关信息:1)查看有哪些用户连接SELECTS.OSUSEROS_USER_NAME,DECODE(SIGN(48-COMMAND),1,TO_CHAR(COMMAND),'ACTIONCODE#'||TO_CHAR(COMMAND))ACTION,P.PROGRAMORACLE_PROCESS,STATUSSESSION_STATUS,S.TERMINALTERMINAL,S.PROGRAMPROGRAM,S.USERNAMEUSER_NAME,S.FIXED_TABLE_SEQUENCEACTIVITY_METER,''QUERY,0MEMORY,0MAX_MEMORY,0CPU_USAGE,S.SID,S.SERIAL#SERIAL_NUMFROMV$SESSIONS,V$PROCESSPWHERES.PADDR=P.ADDRANDS.TYPE='USER'ORDERBYS.USERNAME,S.OSUSER;2)根据v.sid查看对应连接的资源占用等情况SELECTN.NAME,V.VALUE,N.CLASS,N.STATISTIC#FROMV$STATNAMEN,V$SESSTATVWHEREV.SID=18ANDV.STATISTIC#=N.STATISTIC#ORDERBYN.CLASS,N.STATISTIC#;3)根据sid查看对应连接正在运行的sqlSELECTCOMMAND_TYPE,SQL_TEXT,SHARABLE_MEM,PERSISTENT_MEM,RUNTIME_MEM,SORTS,VERSION_COUNT,LOADED_VERSIONS,OPEN_VERSIONS,USERS_OPENING,EXECUTIONS,USERS_EXECUTING,LOADS,FIRST_LOAD_TIME,INVALIDATIONS,PARSE_CALLS,DISK_READS,BUFFER_GETS,ROWS_PROCESSED,SYSDATESTART_TIME,SYSDATEFINISH_TIME,'>'||ADDRESSSQL_ADDRESS,'N'STATUSFROMV$SQLAREAWHEREADDRESS=(SELECTSQL_ADDRESSFROMV$SESSIONWHERESID=8);根据pid查看sql语句:SELECTSQL_TEXTFROMV$SQLWHEREADDRESSIN(SELECTSQL_ADDRESSFROMV$SESSIONWHERESIDIN(SELECTSIDFROMV$SESSIONWHEREPADDRIN(SELECTADDRFROMV$PROCESSWHERESPID=&PID)));