我的应用程序使用Oracle数据库时运行缓慢或似乎完全停止了。
如何找出最耗费资源的查询,以便我进一步调查?
这个示例展示了当前“活动”的SQL:
select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
/
select
object_name,
object_type,
session_id,
type, -- Type or system/user lock
lmode, -- lock mode in which session holds lock
request,
block,
ctime -- Time since current mode was granted
from
v$locked_object, all_objects, v$lock
where
v$locked_object.object_id = all_objects.object_id AND
v$lock.id1 = all_objects.object_id AND
v$lock.sid = v$locked_object.session_id
order by
session_id, ctime desc, object_name
/
这是一个很好的工具,用于查找长时间运行的操作(例如全表扫描)。如果是由于大量的短操作而导致的,则不会显示任何内容。
COLUMN percent FORMAT 999.99
SELECT sid, to_char(start_time,'hh24:mi:ss') stime,
message,( sofar/totalwork)* 100 percent
FROM v$session_longops
WHERE sofar/totalwork < 1
/
试试这个,它会给你当前运行时间超过60秒的查询。请注意,如果SQL有多行,则每个正在运行的查询会打印多行。查看sid和serial#以了解哪些内容属于一起。
select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s
join v$sqltext_with_newlines q
on s.sql_address = q.address
where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by sid,serial#,q.piece
v$session_longops
如果您查找sofar != totalwork,您将看到尚未完成的操作,但是当操作完成时,条目不会被删除,因此您还可以在那里查看很多历史记录。
Step 1:Execute the query
column username format 'a10'
column osuser format 'a10'
column module format 'a16'
column program_name format 'a20'
column program format 'a20'
column machine format 'a20'
column action format 'a20'
column sid format '9999'
column serial# format '99999'
column spid format '99999'
set linesize 200
set pagesize 30
select
a.sid,a.serial#,a.username,a.osuser,c.start_time,
b.spid,a.status,a.machine,
a.action,a.module,a.program
from
v$session a, v$process b, v$transaction c,
v$sqlarea s
Where
a.paddr = b.addr
and a.saddr = c.ses_addr
and a.sql_address = s.address (+)
and to_date(c.start_time,'mm/dd/yy hh24:mi:ss') <= sysdate - (15/1440) -- running for 15 minutes
order by c.start_time
/
Step 2: desc v$session
Step 3:select sid, serial#,SQL_ADDRESS, status,PREV_SQL_ADDR from v$session where sid='xxxx' //(enter the sid value)
Step 4: select sql_text from v$sqltext where address='XXXXXXXX';
Step 5: select piece, sql_text from v$sqltext where address='XXXXXX' order by piece;
您可以使用v$sql_monitor视图查找运行时间超过5秒的查询。这可能仅适用于Oracle的企业版本。例如,此查询将识别来自我的TEST_APP服务的慢查询:
select to_char(sql_exec_start, 'dd-Mon hh24:mi'), (elapsed_time / 1000000) run_time,
cpu_time, sql_id, sql_text
from v$sql_monitor
where service_name = 'TEST_APP'
order by 1 desc;
SELECT SID, SERIAL#, OPNAME, CONTEXT, SOFAR,
TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK;
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
select sq.PARSING_SCHEMA_NAME, sq.LAST_LOAD_TIME, sq.ELAPSED_TIME, sq.ROWS_PROCESSED, ltrim(sq.sql_text), sq.SQL_FULLTEXT
from v$sql sq, v$session se
order by sq.ELAPSED_TIME desc, sq.LAST_LOAD_TIME desc;
alter system kill session
:https://docs.oracle.com/cd/B28359_01/server.111/b28310/manproc008.htm#ADMIN11192 - WW.