oracle 常用的查询语句
有几个主要的v$统计表显示系统的各个方面的等待事件,
v$waitstat 显示系统的主要的等待时间的
v$system_event 系统各个事件的等待情况, 我一般从这个表入手查看系统的等待
v$session_event 某个具体session的等待事件的统计信息
v$event_name 与v$session_event连接显示每个事件的具体名称.
v$session_wait 当前的系统session具体在等待什么
1、查询表的大小
select segment_name, round(blocks*8/1024, 0) table_size from user_segments where segment_type='TABLE';
2、搜索出哪些会话正在执行全表、快速索引扫描
select sid,event,total_waits,time_waited from v$session_event where event='db file scattered read' and total_waits>0 order by 3,2;
3、对表进行分析
analyze table CASE_GA_AJZLZ compute statistics;
4、用下列SQL工具找出低效SQL:
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 ;
5、查询表的使用频繁程度
select o.owner,o.object_name,count(*)
from dba_objects o,v$bh bh
where o.object_id=bh.objd
and o.owner !='SYS'
group by o.owner,o.object_name
having count(*)>100
order by count(*) desc;
6、察看keep池的命中率：
select name,physical_reads,db_block_gets,consistent_gets,
2 1-(physical_reads / (db_block_gets + consistent_gets)) "hit ratio"
3 from v$buffer_pool_statistics
4 where name='KEEP';
7、对数据库用户的所有表、索引进行分析：
set pagesize 0
spool d:\analyze_tables.sql;
select 'analyze table '||owner||'.'||table_name||' compute statistics;' from dba_tables where owner='CTMADM';
spool off
@d:\analyze_tables.sql
spool d:\analyze_indexes.sql;
select 'analyze index '||owner||'.'||index_name||' compute statistics;' from dba_indexes where owner='CTMADM';
spool off
@d:\analyze_indexes.sql
1.jpg 
123.jpg 
无标题.bmp 
11106080.jpg 
new_lion_2005121118574176212960_2.jpg 
