oracle调优的一点心得

1、数据缓冲区的调整
SGA区内存参数大小查询
select name,value from v$parameter where name in('db_block_buffers','db_cache_size','db_block_size','shared_pool_size','sort_area_size');

查询从内存读取数据和从磁盘读取数据的量
select name,value from v$sysstat where name in('db block gets','consistent gets','physical reads');
db block gets 表示从内存读数据
consistent gets表示读一致性数据
physical reads表示从磁盘读数据
最理想的情况是physical reads接近于0

查询数据字典v$latch_children可以检测数据缓冲区是否有空闲，得到的空闲冲突比例应该接近于0
select child#,sleeps/gets ratio from v$latch_children where name='cache buffers lru chain';

查询数据缓冲区的命中率，一般要求命中率在90%以上
select name,physical_reads,db_block_gets,consistent_gets,
1 - (physical_reads/(db_block_gets+consistent_gets))
from v$buffer_pool_statistics;
该语句只能应用于oracle9i以上

select a.value "db_block_gets",b.value "consistent_gets",c.value "physical_reads",
round(100*(1-c.value/(a.value+b.value)))"buffer_hit_ratio"
from v$sysstat a,v$sysstat b,v$sysstat c where a.name='db block gets'
and b.name='consistent gets' and c.name='physical reads';
该语句适用于任何版本

可以在创建表的时候，让访问率比较高的表放在缓冲区的keep池，不常访问的表放在recycle池，这两个池又以下参数决定：
db_keep_cache_size,db_recycle_cache_size
默认情况下是所有的表都放在default池，它的大小就是数据缓冲池
show parameter db_keep_cache_size
show parameter db_recycle_cache_size
create table test(id number) storage(buffer_pool default);
create table test(id number) storage(buffer_pool keep);
create table test(id number) storage(buffer_pool recycle);

用户可以通过v$bh视图来找到经常被使用的表
select o.object_name,count(*) from dba_objects o,v$bh b
where o.object_id=b.objd and o.owner !='SYS' group by o.object_name having count(*)>100 order by count(*) desc;

2、共享池调整
查询共享池的使用情况
select * from v$sgastat where name='free memory';

查询sql语句使用内存的情况：
select disk_reads,buffer_gets,sql_text from v$sqlarea where disk_reads >1000 order by disk_reads desc;

查询共享池内存空闲率：
col value for 999,999,999,999 heading "shared pool size"
col bytes for 999,999,999,999 heading "free bytes"
select to_number(v$parameter.value) value,v$sgastat.bytes,
(v$sgastat.bytes/v$parameter.value)*100 "percent free"
from v$sgastat,v$parameter
where v$sgastat.name='free memory'
and v$parameter.name='shared_pool_size'
and v$sgastat.pool='shared pool';

计算结果小于20%，应该增大共享池的大小

显示已经运行过的部分SQL语句：
select sql_text from v$sqlarea
where rownum<=10 and
sql_text not like '%:%' and
parsing_user_id != 0;

查询共享池中sql语句代码以及运行该代码的用户信息：
select osuser,username,sql_text from v$session a,v$sqltext b
where a.sql_address = b.address;

确定缓存的sql语句的数目，以及它使用了多少的内存和每个sql语句平均消耗的内存：
select bytes,sql_count,bytes/sql_count
from(select count(*) sql_count from v$sqlarea),v$sgastat
where name='sql area';

查询共享池库缓冲区的失败率：
select sum(pins)"Total Pins",sum(reloads)"Total Reloads",sum(reloads)/sum(pins)*100 libcache from v$librarycache;

Total Pins表示驻留内存次数
Total Reloads表示重新加载到内存的次数
libcache表示失败率
库缓冲区的失败率必须小于1%,如果该值大于1%则需要增加shared_pool_size的值

 

3、日志缓冲区的调整
查询日志缓冲区的成功与失败的次数：
select name,sum(gets),sum(misses)
from v$latch
where name like 'redo%' group by name;

gets表示成功取得日志缓冲区的次数；
misses表示在等待日志缓冲区时没有得到而再次等待，直到等待成功的次数

查询立即得到日志缓冲区的成功与失败的次数：
select b.name,gets,misses,immediate_gets,immediate_misses
from v$latch a,v$latchname b
where b.name in('redo allocation','redo copy')
and b.latch# = a.latch#;

失败率的计算方法：
select name,gets,misses,immediate_gets,immediate_misses,
decode(gets,0,0,misses/(gets+misses)*100) ratio1,
decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
from v$latch where name in('redo allocation','redo copy');
计算结果应该小于1%如果，大于该值，应该增加log_buffer的大小

查询用户进程使用日志缓冲区的次数，当用户没有修改数据时，查询结果是0。
select name ,value from v$sysstat
where name = 'redo buffer alocation retries';

 

4、排序区调整

查询排序区的使用情况：
select name,value from v$sysstat where name like '%sort%';
失败率=sorts(disks)/sorts(memory)*100%

失败率必须小于5%，否则就要增加sort_area_size的大小

5、磁盘I/O调整

查询数据文件的分配及磁盘读取状况：
select f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw,f.phyblkwrt pbw,
df.tablespace_name name,df.file_name"FILE_NAME"
from v$filestat f,dba_data_files df
where f.file#=df.file_id order by df.tablespace_name;

或者：
select substr(a.file#,1,2)"File#",a.status,
a.bytes,b.phyrds,b.phywrts,substr(a.name,1,50)"File Name"
from v$datafile a,v$filestat b where a.file#=b.file#;

其中PYR、PYW、PHYRDS、PHYWRTS分别表示物理磁盘读写的数据量。调整的目标是是各个表空间的物理读写在各个磁盘间的平衡

6、CPU调整
查询数据库中每个用户的命中率：
select username,consistent_gets,block_gets,physical_reads,
100*(consistent_gets + block_gets - physical_reads)/(consistent_gets + block_gets) hiratio
from v$session,v$sess_io
where v$session.sid = v$sess_io.sid and (consistent_gets + block_gets) > 0
and username is not null;

命中率应该超过90%

7、调整数据库快存cache
select state,count(*) from x$bh group by state;

查询出来的数据中，前一条表示使用的，后一条表示空闲的，两个数据加起来就是数据缓冲区的大小

创建基表时，可以使用参数cache将该表放在内存区域，这样全表扫描的结果会自动存储与most recently used区域，而不象普通表一样被存储于LRU区域
create table test(id number(2)) tablespace test cache;
该参数cache表示该表被驻留内存，可以又用户在创建表是指定，系统默认是nocache，要了解哪些表具有cache查询方法如下：
select table_name,cache,tablespace_name from user_tables;

alter table test nocache;
alter table test cache;

如果增加了cache功能，查询的时候可以使用/* + cache */ 指出使用cache查询

select /* + cache(test) */ id from test;

select /* + full(emp) nocache(test) */ id from test;

查询sql语句使用内存的情况：
select disk_reads,buffer_gets,sql_text from v$sqlarea where disk_reads >1000 order by disk_reads desc;

对于查询到使用内存过大的语句，采取以下方法修改：
（1）增加full选项，强制全表扫描
select /* + full(表) + */ 列 from 表;
（2）增加index选项，强制使用索引
select /* + index(表名,索引名) 列 from 表;
（3）增加ordered关键字，在多表联合查询时，强制驱动表进行查询
select /* + ordered */ 列 from 表1,表2;
（4）增加all_rows,按照基于成本(cost_based)优化方法进行查询。
select /* + all_rows */ 列 from 表;

（5）增加parallel选项,在多cpu中使用并行查询方式，有效发挥多cpu的作用
select /* + full(表) parallel(表,2) */ 列 from 表;

8、调整PGA
在8i以前是用sort_area_size和hash_area_size参数来控制的，9i用pga_aggregate_target参数来控制一共使用PGA的大小，并且还提供了
workarea_size_policy参数用于开关PGA，auto为自动，manual为手工
show parameter pga_aggregate_target
show parameter workarea_size_policy

每一个session不能使用大于pga_aggregate_target的5%，并行操作不能超过pga_aggregate_target的30%，也不能超过_pga_max_size的50%，该值默认为200M，如果要超过100M必

须修改_pga_max_size的值
alter system set "_pga_max_size"=500M

select sql_text,operation_type,policy,last_memory_used/1024/1024,last_execution,last_tempseg_size from v$sql a,v$sql_workarea b where

a.hash_value=b.hash_value and sql_text = 'select * from test';