一、查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
一般情况
内存为<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;
删除操作系统下的日志文件：直接把文件拉进回收站；
重建在线日志组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 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本