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

语句1

--正在执行的语句查询和拼接杀进程
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

--锁表语句杀进程
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

--最耗费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

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

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

--最耗缓存
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

-- 最多物理读取
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

-- 最多执行
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

-- 最耗内存
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

--失效的索引重建
select 'alter index '||index_name||' rebuild online;' from user_indexes where status <> 'valid' and index_name not like'%$$';

阻塞等待语句：

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;