1. 查找I/O,SWAP 嚴重時候的SQL語句
SELECT address,
hash_value,
disk_reads / executions disk_reads,
elapsed_time / 1000000 / executions AS "ELAPSD_TIME(s)",
buffer_gets / executions bgets_per,
executions,
first_load_time AS first_time,
sql_text
FROM v$sql
WHERE executions > 0
AND (disk_reads / executions > 500
OR buffer_gets / executions > 20000)
AND command_type = 3
ORDER BY first_load_time;
2. 查找佔用時間較長的SQL語句
SELECT sql_text sql_type,
'Executions=' || executions nl,
'Expected Response Time in Seconds= '||buffer_gets / DECODE (executions, 0, 1, executions) / 4000 as "Response_Time"
FROM v$sql
WHERE buffer_gets / DECODE (executions, 0, 1, executions) / 4000 > 10
AND executions > 0
3. 查找運行時使用TEMP表空間較多的SQL語句
SELECT sql_text
FROM v$sql
WHERE disk_reads > 1000
OR (executions > 0 AND buffer_gets / executions > 30000);
4. 查找當前用戶使用的操作
SELECT a.machine,
a.terminal,
a.osuser,
a.username,
b.sql_text
FROM v$session a, v$sqlarea b
WHERE a.sql_address = b.address
ORDER BY b.address
5. 查詢當前運行的SQL
select a.username,a.machine,a.program,b.spid,c.sql_text from v$session a,
v$process b,v$sqlarea c
where a.paddr=b.addr and c.hash_value=a.sql_hash_value and c.address=a.
sql_address
and b.spid='16323'
6. 查詢當前運行的程序
select a.username,a.machine,a.program,b.spid,a.serial#,a.sid from v$session a,
v$process b
where a.paddr=b.addr and b.spid in ('20739','14137')
SELECT a.machine,a.terminal,a.osuser, a.username, b.sql_text
from v$session a, v$sqlarea b, v$process c
where a.sql_address =b.address and a.paddr=c.addr
and c.spid in ('9336','9234')
order by b.address
7. 監控當前數據庫誰有運行什麼SQL
SELECT osuser, username, sql_text
FROM v$session a, v$sqltext b
WHERE a.sql_address = b.address
ORDER BY address, piece;
8. 查找使用CPU多的用戶SESSION,statistic# = 12 是CPU used by this session
SELECT a.sid,
spid,
status,
SUBSTR (a.program, 1, 40) prog,
a.terminal,
osuser,
VALUE / 60 / 100 VALUE
FROM v$session a, v$process b, v$sesstat c
WHERE c.statistic# = 12 AND c.sid = a.sid AND a.paddr = b.addr
ORDER BY VALUE DESC;
9. 誰在訪問數據庫
SELECT c.sid,
c.serial#,
c.username,
a.object_id,
b.object_name,
c.program,
c.status,
d.name,
c.osuser
FROM v$Locked_object a,
All_objects b,
v$session c,
audit_actions d
WHERE a.object_id = b.object_id
AND a.session_id = c.sid(+)
AND c.command = d.action;
10. 誰被鎖住
SELECT a.sid,
a.serial#,
a.username,
a.LOCKWAIT,
a.status,
a.program,
b.name
FROM v$session a, audit_actions b
WHERE a.command = b.action AND LOCKWAIT IS NOT NULL;
11. 誰在鎖表
SELECT a.sid,
a.serial#,
a.username,
a.LOCKWAIT,
a.status,
a.program,
b.name
FROM v$session a, audit_actions b
WHERE a.command = b.action AND STATUS = 'ACTIVE';
12. 查看哪些包要固定
SELECT owner,
name,
TYPE,
source_size + code_size + parsed_size + error_size BYPES
FROM dba_object_size
WHERE TYPE = 'PACKAGE BODY'
ORDER BY 4 DESC;
13. 能知道哪個SID最消耗資源,前提是timed_statistics=true
SELECT s.sid, s.VALUE "CPU Used"
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name = 'CPU used by this session'
AND s.VALUE > 0
ORDER BY 2 DESC;
14. 怎麼發現是誰鎖住了你需要的一張表
Select object_id from v$locked_object;
Select object_name, object_type from dba_objects where object_id='';
15.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25198367/viewspace-688903/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25198367/viewspace-688903/