Oracle的sql监控
程序员文章站
2022-06-10 21:54:12
...
<!--StartFragment -->
线上SQL抓取分析：
依照硬盘IO排列
SELECT * FROM (SELECT SQL_ID,PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS FROM V$SQLAREA WHERE PARSING_USER_ID != 0 ORDER BY DISK_READS DESC ) WHERE ROWNUM<11
依照CPU使用排列
SELECT * FROM (SELECT SQL_ID,PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,CPU_TIME FROM V$SQLAREA WHERE PARSING_USER_ID != 0 ORDER BY CPU_TIME DESC ) WHERE ROWNUM<11
依照SQL ID查询实际的SQL
SELECT SQL_TEXT FROM V$SQLTEXT WHERE SQL_ID='9brygt3kuuz7k' ORDER BY PIECE ASC
根据SPID查询SQL
SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text FROM v$session a,v$process b,v$sqltext c WHERE b.spid='&spid' AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece;
根据SPID查询连接信息
SELECT PID,SPID,USERNAME,SERIAL#,PROGRAM FROM V$PROCESS WHERE spid=
依照硬盘IO排列
SELECT * FROM (SELECT SQL_ID,PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS FROM V$SQLAREA WHERE PARSING_USER_ID != 0 ORDER BY DISK_READS DESC ) WHERE ROWNUM<11
依照CPU使用排列
SELECT * FROM (SELECT SQL_ID,PARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS,CPU_TIME FROM V$SQLAREA WHERE PARSING_USER_ID != 0 ORDER BY CPU_TIME DESC ) WHERE ROWNUM<11
依照SQL ID查询实际的SQL
SELECT SQL_TEXT FROM V$SQLTEXT WHERE SQL_ID='9brygt3kuuz7k' ORDER BY PIECE ASC
根据SPID查询SQL
SELECT a.username,a.machine,a.program,a.sid,a.serial#,a.status,c.piece,c.sql_text FROM v$session a,v$process b,v$sqltext c WHERE b.spid='&spid' AND b.addr=a.paddr AND a.sql_address=c.address(+) ORDER BY c.piece;
根据SPID查询连接信息
SELECT PID,SPID,USERNAME,SERIAL#,PROGRAM FROM V$PROCESS WHERE spid=