Oracle如何查询正在执行的语句、锁表、耗费资源的语句

语句1--正在执行的语句查询和拼接杀进程select 'alter system kill session '''||b.sid||','|| b.serial#||''';' as kil

语句1
1
2
3
4
5
6
7
8
9
10
11
12
--正在执行的语句查询和拼接杀进程
select 'alter system kill session '''||b.sid||','||
b.serial#||''';' as killsql,b.sid oracleid,
b.username oracle用户,
b.serial#,
spid 操作系统id,
paddr,
sql_text 正在执行的sql,
b.machine 计算机名,c.sql_fulltext
from v$process a, v$session b, v$sqlarea c
where a.addr = b.paddr
and b.sql_hash_value = c.hash_value;

语句2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--锁表语句杀进程
select 'alter system kill session '''||s.sid||','||
s.serial#||''';', l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
s.user#,
l.os_user_name,
s.machine,
s.terminal,
a.sql_text,
a.action
from v$sqlarea a, v$session s, v$locked_object l
where l.session_id = s.sid
and s.prev_sql_addr = a.address
order by sid, s.serial#;

语句3
1
2
3
4
5
6
7
8
9
10
11
12
--最耗费cpu
select *
from (select sql_text,
buffer_gets,
disk_reads,
sorts,
cpu_time / 1000000 cpu_sec,
executions,
rows_processed
from v$sqlstats
order by cpu_time desc)
where rownum < 11;

语句4
1
2
3
4
5
6
7
8
9
10
11
12
select *
from (select a.sid session_id,
a.sql_id,
a.status,
a.cpu_time / 1000000 cpu_sec,
a.buffer_gets,
a.disk_reads,
b.sql_text sql_text
from v$sql_monitor a, v$sql b
where a.sql_id = b.sql_id
order by a.cpu_time desc)
where rownum <= 20;

语句5
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select from (select t.inst_id,t.sql_id,t.last_active_time,t.sql_profile, --如果该字段有值，就是按固化走执行计划
t.plan_hash_value,t.sql_fulltext,t.child_number 执行计划版本号,
trunc((t.cpu_time/t.executions/1000000),4) 每次cpu时间,trunc((t.elapsed_time-t.cpu_time)/t.executions/1000000,4)"每次等待时间",t.executions 总执行次数,
--trunc(t.executions/((t.last_active_time-to_date(t.last_load_time,'yyyy/mm/dd hh24:mi:ss'))86400)) 平均每秒执行次数,
round(t.rows_processed/t.executions,2) 平均返回行数,trunc(t.elapsed_time / t.executions / 1000000,4) "每次执行（秒）",
trunc((t.buffer_gets / t.executions/1000000),4) 每次逻辑读,trunc((t.disk_reads / t.executions/1000000),4) 每次物理读,
trunc((t.cluster_wait_time/t.executions/1000000),4) 每次集群等待,trunc((t.user_io_wait_time/t.executions/1000000),4) 每次io等待,
trunc((t.application_wait_time/t.executions/1000000),4) 每次应用等待,trunc((t.concurrency_wait_time/t.executions/1000000),4) 每次并发等待,
t.first_load_time 首次硬解析时间,t.last_load_time 上次硬解析时间,
t.module,t.action,t.parsing_schema_name,trunc(t.elapsed_time/1000000,4) "执行时间（秒）",
trunc(t.cpu_time/1000000,4) cpu时间,t.parse_calls 总解析次数, t.loads 硬解析次数, t.buffer_gets,t.cluster_wait_time,
t.user_io_wait_time,t.application_wait_time,t.concurrency_wait_time,t.plan_hash_value
from gv$sql t
where t.executions >0
--and t.sql_id='cjrpgh8gqybs0'
order by t.cpu_time desc) xx
where rownum<=20;

语句6
1
2
3
4
5
6
7
8
9
--最耗缓存
select * from (
select sql_fulltext sql,
buffer_gets, executions, buffer_gets/executions "gets/exec",
hash_value,address,last_active_time
from v$sqlarea
where buffer_gets > 10000
order by buffer_gets desc)
where rownum <= 10 ;

语句7
1
2
3
4
5
6
7
8
9
-- 最多物理读取
select * from (
select sql_fulltext sql,
disk_reads, executions, disk_reads/executions "reads/exec",
hash_value,address,last_active_time
from v$sqlarea
where disk_reads > 1000
order by disk_reads desc)
where rownum <= 10 ;

语句8
1
2
3
4
5
6
7
8
9
-- 最多执行
select * from (
select substr(sql_text,1,40) sql,sql_fulltext,
executions, rows_processed, rows_processed/executions "rows/exec",
hash_value,address,last_active_time
from v$sqlarea
where executions > 100
order by executions desc)
where rownum <= 10 ;

语句9
1
2
3
4
5
6
7
8
-- 最耗内存
select * from (
select substr(sql_text,1,40) sql,
sharable_mem, executions, hash_value,address,last_active_time
from v$sqlarea
where sharable_mem > 1048576
order by sharable_mem desc)
where rownum <= 10 ;

语句10
1
2
--失效的索引重建
select 'alter index '||index_name||' rebuild online;' from user_indexes where status <> 'valid' and index_name not like'%$$';
阻塞等待语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
select
blocksession.sid as block_session_sid,
blocksession.serial# as block_session_serial#,
blocksession.username as block_session_username,
blocksession.osuser as block_session_osuser,
blocksession.machine as block_session_machine,
blocksession.status as block_session_status,
blockobject.object_name as blocked_table,
waitsession.sid as wait_session_sid,
waitsession.serial# as wait_session_serial#,
waitsession.username as wait_session_username,
waitsession.osuser as wait_session_osuser,
waitsession.machine as wait_session_machine,
waitsession.status as wait_session_status
from
v$lock blocklock,
v$lock waitlock,
v$session blocksession,
v$session waitsession,
v$locked_object lockedobject,
dba_objects blockobject
where
blocklock.block = 1
and blocklock.sid != waitlock.sid
and blocklock.id1 = waitlock.id1
and blocklock.id2 = waitlock.id2
and blocklock.sid = blocksession.sid
and waitlock.sid = waitsession.sid
and lockedobject.session_id = blocksession.sid
and lockedobject.object_id = blockobject.object_id;

总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
https://coub.com/view/3dsfalxy1s
https://coub.com/view/mfqbj0yvce
https://coub.com/view/334upjejcl
https://coub.com/view/0h3ffoou2k
https://coub.com/view/shz3k624uh
https://coub.com/view/n1502r0co6
https://coub.com/view/hvds50d4m8
https://coub.com/view/oka105tjv9
https://coub.com/view/22ep9k60uo
https://coub.com/view/oltx0jern2
https://coub.com/view/3vcl0z6ffw
https://coub.com/view/g1bg07g4tz
https://coub.com/view/o7kl8u3yk8
https://coub.com/view/3dzq5resx8
https://coub.com/view/iv521qvwfw
https://coub.com/view/65o2r0jgmu
https://coub.com/view/deyzo59qwa
https://coub.com/view/5l7ggqnhuw
https://coub.com/view/13b0uw1i56
https://coub.com/view/ta33x73o78
https://coub.com/view/y0dfk54gqr
https://coub.com/view/wsb526zctx
https://coub.com/view/t0ufu9kq83
https://coub.com/view/d5d07nyl00
https://coub.com/view/1f49yxllgn
https://coub.com/view/jsykuwbj0u
https://coub.com/view/mrcqfbv3q5
https://coub.com/view/diwjigd4cg
https://coub.com/view/2cug2r0le7
https://coub.com/view/0vu1ipaodi
https://coub.com/view/5ite05whoj
https://coub.com/view/4jkwxavo2j
https://coub.com/view/hh8x3be7us
https://coub.com/view/b8g2juptoz
https://coub.com/view/axh8k5gjc5
https://coub.com/view/a1d1fqk7p7
https://coub.com/view/vjyt1eovd0
https://coub.com/view/10qck2xtwb
https://coub.com/view/65x60o7d36
https://coub.com/view/33jpl3ofe1
https://coub.com/view/9k7ywp3928
https://coub.com/view/v0sp834e2l
https://coub.com/view/akcojbzic3
https://coub.com/view/6yjaps6wsy
https://coub.com/view/w4kg1t23e4
https://coub.com/view/rxzb197q88
https://coub.com/view/hwcqp82lkn
https://coub.com/view/vjilbj7obc
https://coub.com/view/zwm9ygvghg
https://coub.com/view/2pc7t36i90
https://coub.com/view/etkv66jzdj
https://coub.com/view/tncefwv07l
https://coub.com/view/cq3k7cxzvl
https://coub.com/view/sr5zawaxiz
https://coub.com/view/uftwarmhbk
https://coub.com/view/6f8c0vydaz
https://coub.com/view/yvjn3t7dzj
https://coub.com/view/yh6dvj4one
https://coub.com/view/az86rziagw
https://coub.com/view/ll7y98tuoi