Oracle调优参考- -

 

                                      

1：查看Oracle的Init.ora文件

参数一：db_block_Buffers=
参数二：shared_pool_size=
参数三：process=500
参数四：distributed_stransactions=200
参数五：sort_area_size=131072

计算方法：(shared_pool_size+db_block_buffers*16384)/1024/1024(M)<内存(M)*0.3

一般情况

内存为2G: db_block_buffers=9600
内存为1G: db_block_buffers=6400
内存为512M: db_block_buffers=3200
内存为2G：shared_pool_size=600(M)*1024*1024=629145600
内存为1G：shared_pool_size=350(M)*1024*1024=367001600
内存为512M：shared_pool_size=170(M)*1024*1024=178257920

2： 查看Oracle在线日志：建议在线日志为五个，每个至少为5M以上

增加在线日志：

Alter database add logfile group 4 (‘d:\crds\log\redo4.log’) size 5120k;
Alter database add logfile group 5 (‘d:\crds\log\redo5.log’) size 5120k;

切换当前日志组：

Alter system switch logfile;//每运行一次切换一个日志组
Alter system switch logfile;//每运行一次切换一个日志组

删除旧的日志组：

Alter database drop logfile group 1;
Alter database drop logfile group 2;
Alter database drop logfile group 3;

删除操作系统下的日志文件：直接把文件拉进回收站；

重建在线日志组1，2，3：

Alter database add logfile group 1(‘d:\crds\log\redo1.log’) size 5120k;
Alter database add logfile group 2 (‘d:\crds\log\redo2.log’) size 5120k;
Alter database add logfile group 3 (‘d:\crds\log\redo3.log’) size 5120k;

 

3： 查看数据表和索引的Increase Size by 是否设为0及无限制使用表空间

对表进行处理

SQL> set pagesize 5000;

SQL> set heading off;

SQL> spool c:\aa.sql

SQL> select 'alter table '||table_name||' storage(pctincrease 0);' from tabs where pct_increase<>0;

SQL> spool off;

SQL> @c:\aa; //将自动增长为0；

对索引进行处理

SQL>spool c:\index.sql

SQL>select 'alter index '||index_name||' storage(pctincrease 0);' from user_indexes where pct_increase<>0;

SQL> spool off;

SQL> @c:\index;//将自动增长为0；

 

4： 查看Oracle表空间使用比例；正常要保留有30%的空闲度

select df.tablespace_name "表空间名",totalspace "总空间M",freespace "剩余空间M",round((1-freespace/totalspace)*100,2) "使用率%"

from(select tablespace_name,round(sum(bytes)/1024/1024) totalspace

from dba_data_files groupby tablespace_name) df,

(select tablespace_name,round(sum(bytes)/1024/1024) freespace

from dba_free_space

groupby tablespace_name) fs

where df.tablespace_name=fs.tablespace_name;

如果使用率达到80以上，考虑要增加表空间大小；

查看Temp表空间大小，将其改为300M—500M；

 

5： 查看数据表和索引的归属表空间是否正确

数据表的归属

select table_name,tablespace_name from tabs orderby tablespace_name;

索引文件的归属

select index_name,tablespace_name from user_indexes orderby tablespace_name,index_name;

 

6： 整理表空间的碎片

首先查看碎片表空间

SQL>column FSFI format 999,99

SQL> select tablespace_name,sqrt(max(blocks)/sum(blocks))* (100/sqrt(sqrt(count(blocks)))) FSFIfrom dba_free_spacegroup by tablespace_name order by 1;

准备输出后，SQL语句进行整理；

SQL> set pagesize 5000;

SQL> set heading off;

SQL> spool c:\tablespace.sql

SQL>column FSFI format 999,99

SQL> select 'alter tablespace '||tablespace_name||' default storage(pctincrease 1);'

from dba_free_space group by tablespace_name;

//先把自动增长改为1

SQL> select 'alter tablespace '||tablespace_name||' coalesce;' from dba_free_space

group by tablespace_name;

//整理碎片

SQL> select 'alter tablespace '||tablespace_name||' default storage(pctincrease 0);'

from dba_free_space

group by tablespace_name;

//整理完后再把自动增长改为0；

SQL>spool off;

SQL>@c:\tablespace.sql;

 

7：查看不优化的SQL语句:可以从disk_reads,buffer_gets,sharable_mem进行查看

select buffer_gets,first_load_time,disk_reads,SHARABLE_MEM,sql_text from v$sql

orderby disk_reads;//根据disk_reads/ SHARABLE_MEM/ buffer_gets;

//找出其中较大者，进行优化；

 

8： 查看死锁用户ID，并将其杀死

select * From v$session where TADDR is not Null;

记下Sid,serial#,使用命令把其杀死,释放资源;

alter system kill session ‘sid,serial#’;

 

9：查看所有表的索引字

select a.table_name bm,a.index_name sym,b.tablespace_name bkj,substr(a.column_name,1,100) syzd,a.column_position syh

from user_ind_columns a,user_indexes b

where a.index_name=b.index_name orderby bm,sym,syh

 

10：查看回滚段命中率情

select rn.name,rs.gets 被访问次数,rs.waits 等待回退段块的次数,(rs.waits/rs.gets)*100 命中率 from v$rollstat rs,v$rollname rn where rs.usn=rn.usn；

//命中率>2%时存在回退段竞争情况，必须增加回退段个数，一般至少4个并发任务需要使用一个回退段;