1、正在执行的语句查问和拼接杀过程
SELECT 'alter system kill session '''||b.sid||','|| b.serial#||''';',b.sid oracleID,b.username Oracle用户,b.serial#,spid 操作系统ID,paddr,sql_text 正在执行的SQL,b.machine 计算机名FROM v$process a, v$session b, v$sqlarea cWHERE a.addr = b.paddrAND 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、通过SQL_ID查看数据状态剖析
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 Twhere t.EXECUTIONS >0 --and t.sql_id='cjrpgh8gqybs0' order by t.CPU_TIME desc) xx where rownum<=20;
5、最耗缓存
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 > 10000ORDER BY buffer_gets DESC)WHERE rownum <= 10 ;
6、最多物理读取
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 > 1000ORDER BY disk_reads DESC)WHERE rownum <= 10 ;
7、最多执行sql
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 > 100ORDER BY executions DESC)WHERE rownum <= 10 ;
8、最耗内存的sql
SELECT * FROM (SELECT substr(sql_text,1,40) sql, sharable_mem, executions, hash_value,address,LAST_ACTIVE_TIME FROM V$SQLAREA WHERE sharable_mem > 1048576ORDER BY sharable_mem DESC)WHERE rownum <= 10 ;
9、生效的索引重建
select 'alter index '||index_name||' rebuild online;' from user_indexes where status <> 'VALID' and index_name not like'%$$';