oracle调优的一点心得

时间:2023-01-16 11:11:59

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';