语句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
语句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
语句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
语句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
语句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
语句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
语句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
--失效的索引重建 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;
总结
以上为个人经验,希望能给大家一个参考,也希望大家多多支持IT俱乐部。
