1. 耗时语法查询:
--用以下语句找出长时间操作的SQL语句:
select longops.SID "Session标识",
longops.TIMESTAMP "运行时间",
longops.OPNAME "操作简要说明",
longops.TARGET "操作运行对象",
longops.START_TIME "操作开始时间",
longops.ELAPSED_SECONDS "总花费时间(秒)",
longops.TIME_REMAINING "预计完成时间(秒)",
longops.LAST_UPDATE_TIME "最后更新时间",
longops.USERNAME "执行操作的用户",
sqlarea.sql_text "执行语法",
sqlarea.SQL_FULLTEXT "执行完整的语法",
sqlarea.EXECUTIONS "执行次数",
sqlarea.MODULE "执行模块"
from v$session_longops longops,
v$sqlarea sqlarea
where longops.elapsed_seconds > 6
and longops.sql_id = sqlarea.sql_id
order by longops.LAST_UPDATE_TIME desc;
2. 当天耗时
SELECT S.SQL_TEXT,
S.SQL_FULLTEXT,
S.SQL_ID,
ROUND(ELAPSED_TIME / 1000000 / (CASE
WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN
1
ELSE
EXECUTIONS
END),
2) "执行时间'S'",
S.EXECUTIONS "执行次数",
S.OPTIMIZER_COST "COST",
S.SORTS,
S.MODULE, --连接模式(JDBC THIN CLIENT:程序)
-- S.LOCKED_TOTAL,
--S.PHYSICAL_READ_BYTES "物理读",
-- S.PHYSICAL_READ_REQUESTS "物理读请求",
-- S.PHYSICAL_WRITE_REQUESTS "物理写",
-- S.PHYSICAL_WRITE_BYTES "物理写请求",
S.ROWS_PROCESSED "返回行数",
S.DISK_READS "磁盘读",
S.DIRECT_WRITES "直接路径写",
S.PARSING_SCHEMA_NAME,
S.LAST_ACTIVE_TIME
FROM GV$SQLAREA S
WHERE ROUND (ELAPSED_TIME / 1000000 / ( CASE
WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN
1
ELSE
EXECUTIONS
END),
2) > 5 --100 0000微秒=1S
AND S.PARSING_SCHEMA_NAME = USER
AND TO_CHAR(S.LAST_LOAD_TIME, 'YYYY-MM-DD' ) =
TO_CHAR( SYSDATE, 'YYYY-MM-DD' )
AND S.COMMAND_TYPE IN (2 , 3, 5 , 6 , 189) /*值对应类型 2:INSERT、3:SELECT、6:UPDATE、7:DELETE、189:MERGE 查询 V$SQLCOMMAND*/
ORDER BY "执行时间'S'" DESC;
3. 堵闭塞查询
在系统实施工具中发现死锁。通过堵塞sql查询堵塞者的sid及SERIAL#,通过sid和SERIAL#的查询sql_id.通过sql_id确定sql语句。
相关sql:
查询sql_id:
select sql_id from v$session where sid=堵塞者sid and serial#=堵塞者serial;
查询sql:
select * from v$sqlarea where sql_id=‘堵塞者SQL_ID’ ;
堵塞查询:
Select b_s.SQL_ID blocked_sql_id,
'节点 ' || a_s.INST_ID || ' session ' || a_s.sid || ',' || a_s.SERIAL# ||' 阻塞了 节点 ' || b_s.INST_ID || ' session ' || b_s.SID || ',' ||
b_s.SERIAL# blockinfo,
a_s.INST_ID,
a_s.SID,
a_s.SCHEMANAME,
a_s.MODULE,
a_s.STATUS,
'后为被阻塞信息',
b_s.INST_ID blocked_inst_id,
b_s.SID blocked_sid,
b_s.SCHEMANAME blocked_SCHEMANAME,
b_s.EVENT blocked_event,
b_s.MODULE blocked_module,
b_s.STATUS blocked_status,
b_s.SQL_ID blocked_sql_id,
obj.owner blocked_owner,
obj.object_name blocked_object_name,
obj.OBJECT_TYPE blocked_OBJECT_TYPE,
case
when b_s.ROW_WAIT_OBJ# <> -1 then
dbms_rowid.rowid_create(1,
obj.DATA_OBJECT_ID,
b_s.ROW_WAIT_FILE#,
b_s.ROW_WAIT_BLOCK#,
b_s.ROW_WAIT_ROW#)
else
'-1'
end blocked_rowid, --被阻塞数据的rowid
decode(obj.object_type,
'TABLE',
'select * from ' || obj.owner || '.' || obj.object_name ||
' where rowid=''' ||
dbms_rowid.rowid_create(1,
obj.DATA_OBJECT_ID,
b_s.ROW_WAIT_FILE#,
b_s.ROW_WAIT_BLOCK#,
b_s.ROW_WAIT_ROW#) || '''',
NULL) blocked_data_querysql
from gv$session a_s,
gv$session b_s,
dba_objects obj
where b_s.BLOCKING_INSTANCE is not null
and b_s.BLOCKING_SESSION is not null
and a_s.INST_ID = b_s.BLOCKING_INSTANCE
and a_s.SID = b_s.BLOCKING_SESSION
and b_s.ROW_WAIT_OBJ# = obj.object_id(+)
order by a_s.inst_id, a_s.sid;
oracle性能监测语法
最新推荐文章于 2024-03-15 23:38:37 发布