oracle调优笔记

在sqlplus中输入

desc v$libraycache

v$sgastat

v$sql

v$sqlarea

v$sqltext

v$db_object_cache

 

select * from v$librarycache;

col namespace format a10  设定格式长度

查询执行次数小于5次的语句

select sql_text from v$sqlarea where executions<5 order by upper(sql_text);

查询相关语句的解析次数和执行次数

select sql_text(SQL语句),parse_calls(解析次数),execute(执行的次数)  from v$sqlarea order by parse_calls;

如果说parse_calls和execute的次数差不多,表示说这个语句效率很低,需要考虑优化.

result_cache

最好使用同名的绑定变量

 

访问表的时候最好前面加上用户名

 

查询sql语句命中率

select namespace,gathiratio,pinhitratio,reloads,invalidations from v$libray_cache;

 

 

找出那些sql语句是被用户经常执行的

select sql_text,users_executing,executions,loads from v$sqlarea;

select * from v$sqltext where sql_text like ' select * from hr.employees where %';

 

查看空余内存

select * from v$sgastat;

 

Library cache reloads

select sum(pins) "executions", sum(reloads )"cache misses",sum(reloads)/sum(pins) from v$libraycache;

 

 

select parameter,gets,getmisses from v$rowcache;

select parameter,gets,getmisses from v$rowcache;

 

select parameter,sum(gets),sum(getmisses),100*sum(gets-getmisses)/sum(gets)  pct_succ_gets,sum(modifications) updates

from v$rowcache where gets>0 group by parameter;

 查询largepool使用情况

select * from v$sgastat where pool='large pool';

lesson4 purpose

size shared SQL and PL/SQL areas(libray cache)

size data dictionary cache or row cache

size the large pool

allow for the user global area,if using Oracle Shared Server connections.

 

 shared pool放的是查询的SQL语句

buffered cache 存放的是查询结果

DB_BLOCK_CHECKSUM,给每个酷块加上较验 .