体系结构
--等待事件(当前)
select t.event, count(*) count
from v$session t
group by event
order by count desc;
--等待事件(历史汇集)
select t.event, t.total_waits
from v$system_event t
order by total_waits desc;
--游标使用情况
select inst_id, sid, count(*)
from gv$open_cursor
group by inst_id, sid
having count(*) >= 1000
order by count(*) desc;
--PGA占用最多的进程
select p.spid,
p.pid,
s.sid,
s.serial#,
s.status,
p.pga_alloc_mem,
s.username,
s.osuser,
s.program
from v$process p, v$session s
where s.paddr(+) = p.addr
order by p.pga_alloc_mem desc;
--登录时间最长的SESSION(同时获取到spid,方便主机层面 ps -ef | grep spid 来查看)
select *
from (select t.sid,
t2.spid,
t.program,
t.status,
t.sql_id,
t.prev_sql_id,
t.event,
t.logon_time,
trunc(sysdate - logon_time)
from v$session t, v$process t2
where t.paddr = t2.addr
and t.TYPE <> 'BACKGROUD'
order by t.logon_time)
where rownum <= 20;
--逻辑读最多的SQL
select *
from (select sql_id,
sql_text,
s.executions,
s.last_load_time,
s.first_load_time,
s.disk_reads,
s.buffer_gets
from v$sql s
where s.buffer_gets > 300
order by s.buffer_gets desc)
where rownum <= 20;
--物理读最多的SQL
select *
from (select sql_id,
sql_text,
s.executions,
s.last_load_time,
s.first_load_time,
s.disk_reads,
s.buffer_gets,
s.parse_calls
from v$sql s
where s.disk_reads > 300
order by s.disk_reads desc)
where rownum <= 20;
--执行次数最多的SQL
select *
from (select sql_id,
sql_text,
s.executions,
s.last_load_time,
s.first_load_time,
s.disk_reads,
s.buffer_gets,
s.parse_calls
from v$sql s
order by s.executions desc)
where rownum <= 20;
--解析次数最多的SQL
select *
from (select sql_id,
sql_text,
s.executions,
s.last_load_time,
s.first_load_time,
s.disk_reads,
s.buffer_gets,
s.parse_calls
from v$sql s
order by s.parse_calls desc)
where rownum <= 20;
--求DISK_SORT严重的SQL
select sess.username, sql.sql_text, sql.address, sort1.blocks
from v$session sess, v$sqlarea sql, v$sort_usage sort1
where sess.serial# = sort1.session_num
and sort1.sqladdr = sql.address
and sort1.sqlhash = sql.hash_value
--and sort1.blocks >200
order by sort1.blocks desc;
表、索引
--普通索引失效查询
select t.index_name,
t.table_name,
blevel,
t.num_rows,
t.leaf_blocks,
t.distinct_keys
from dba_indexes t
where t.status = 'INVALID';
--分区索引失效
select t2.owner,
t1.blevel,
t1.leaf_blocks,
t1.index_name,
t2.table_name,
t1.partition_name,
t1.status,
t2.owner
from dba_ind_partitions t1, dba_indexes t2
where t1.index_name = t2.index_name
and t1.status = 'UNUSABLE'
and t2.owner in ('SYS');
--高水位表
select table_name, blocks, num_rows
from user_tables
where blocks / num_rows >= 0.2
and num_rows is not null
and num_rows <> 0
and blocks >= 10000;
--未完待续