Oracle调优参考

xiaoxiao2021-03-01  25

一、查Oracle参数并进行调整

1. 查看OracleInit.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

一般情况

内存为<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="2" unitname="g"><span lang="EN-US">2G</span></chmetcnv>: db_block_buffers=9600

内存为<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="1" unitname="g"><span lang="EN-US">1G</span></chmetcnv>: db_block_buffers=6400

内存为<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="512" unitname="m"><span lang="EN-US">512M</span></chmetcnv>: db_block_buffers=3200

内存为<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="2" unitname="g"><span lang="EN-US"><font face="Times New Roman">2G</font></span></chmetcnv>:shared_pool_size=600(M)*1024*1024=629145600

内存为<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="1" unitname="g"><span lang="EN-US"><font face="Times New Roman">1G</font></span></chmetcnv>:shared_pool_size=350(M)*1024*1024=367001600

内存为<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="512" unitname="m"><span lang="EN-US"><font face="Times New Roman">512M</font></span></chmetcnv>:shared_pool_size=170(M)*1024*1024=178257920

2. 查看Oracle在线日志:建议在线日志为五个,每个至少为<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="5" unitname="m"><span lang="EN-US"><font face="Times New Roman">5M</font></span></chmetcnv>以上;

增加在线日志:

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;

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

重建在线日志组123

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 group by tablespace_name) df,

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

from dba_free_space

group by tablespace_name) fs

where df.tablespace_name=fs.tablespace_name;

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

查看Temp表空间大小,将其改为<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="300" unitname="m"><span lang="EN-US">300M</span></chmetcnv>—<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="500" unitname="m"><span lang="EN-US">500M</span></chmetcnv>;

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

数据表的归属

select table_name,tablespace_name from tabs order by tablespace_name;

索引文件的归属

select index_name,tablespace_name from user_indexes order by 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)))) FSFI from dba_free_space group 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

order by 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 order by 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个并发任务需要使用一个回退段;

相关资源:Oracle性能调优书籍;共8本
转载请注明原文地址: https://www.6miu.com/read-3099979.html

最新回复(0)