【高分求助】哪位有Oracle调优的相关资料，请扔一个，谢谢！！

[AllanJD]

如题！

[libolibo888]

我还是第一次进行调优数据库。
学习学习。

[yaomin65]

关注

[xingkongmori]

没调过，路过看看

[dawugui]

1) 对于2G 内存的机器，采用以下设置：
alter system set sga_max_size = 1073741824 scope = spfile;
alter system set java_pool_size = 16777216 scope = spfile;
alter system set log_buffer= 2097152 scope=spfile;
alter system set large_pool_size = 25165824 scope=spfile;
alter system set db_keep_cache_size = 268435456 scope = spfile;
alter system set db_cache_size = 637534208 scope = spfile;
alter system set shared_pool_size = 134217728 scope = spfile;
alter system set pga_aggregate_target = 419430400 scope = spfile;
alter system set workarea_size_policy = AUTO scope = spfile;
alter system set processes=1000 scope = spfile;
alter system set sessions=1200 scope = spfile;
alter system set open_cursors=2000 scope = spfile;
alter system set PARALLEL_AUTOMATIC_TUNING=TRUE scope=spfile;
alter system set undo_management='AUTO' SCOPE=spfile;
alter system set undo_retention = 3600 scope = spfile;
2) 对于4G 内存的机器，采用以下设置：
alter system set sga_max_size = 1258291200 scope = spfile;
alter system set java_pool_size = 16777216 scope = spfile;
alter system set log_buffer= 2097152 scope=spfile;
alter system set large_pool_size = 25165824 scope=spfile;
alter system set db_keep_cache_size = 268435456 scope = spfile;
alter system set db_cache_size = 771751936 scope = spfile;
alter system set shared_pool_size = 134217728 scope = spfile;
alter system set pga_aggregate_target = 524288000 scope = spfile;
alter system set workarea_size_policy = AUTO scope = spfile;
alter system set processes=1500 scope = spfile;
alter system set sessions=1200 scope = spfile;
alter system set open_cursors=2000 scope = spfile;
alter system set PARALLEL_AUTOMATIC_TUNING=TRUE scope=spfile;
alter system set undo_management='AUTO' SCOPE=spfile;
alter system set undo_retention = 3600 scope = spfile;
3) 为改善log file sync 的等待事件，就该隐含参数_log_io_size，修改方法为：
先创建PFILE：
create pfile='c:\backup.ora' from spfile;
打开创建的PFILE，加上一行：
*._log_io_size = 65536
重启数据库：
shutdown immediate;
采用刚才修改过的PFILE 开启数据库：
startup pfile='c:\backup.ora';
根据修改过的PFILE 产生SPFILE：
create spfile from pfile='c:\backup.ora';
4) 说明：此次通过在测试期间对于系统状况进行监控，调整和优化了以下几项参数：
a) 加大了shared_pool_size 保证了存储程序和分析过的SQL 在缓存中的命中率，并保留了一定
的冗余；
b) 通过对于系统实际环境中KEEP 池中TABLE 和INDEX 占用空间的计算， 加大了
db_keep_cache_size；
c) 减小了log_buffer，以有利于改善log file sync 的等待事件；
d) 通过定义隐含参数_log_io_size，改善log file sync 的等待事件；
2. 加大重做日志组：
通过监控，发现之前的重做日志组切换的频率稍微过于频繁，因此将之前的500M 扩大到768M，方法
如下：
1) 新增3 个768M 大小的重做日志组：其中的路径要根据实际情况进行设置。
ALTER DATABASE ADD LOGFILE GROUP 4 ('C:\ORACLE\LOG\log3.ora') SIZE 768M;
ALTER DATABASE ADD LOGFILE GROUP 5 ('C:\ORACLE\LOG\log3.ora') SIZE 768M;
ALTER DATABASE ADD LOGFILE GROUP 6 ('C:\ORACLE\LOG\log3.ora') SIZE 768M;
2) 查看系统缺省的重做日志的状态：
select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
1 1 26 104857600 1 NO INACTIVE 2576465 23-4 月-07
2 1 27 104857600 1 NO INACTIVE 2948385 24-4 月-07
3 1 28 104857600 1 NO CURRENT 3080089 25-4 月-07
3) 对日志文件进行手工切换，直到GROUP#为1，2，3 的三个缺省重做日志的状态都变成INACTIVE：
alter system switch logfile;
注意：如果GROUP#为1，2，3 的三个缺省重做日志的状态不是INACTIVE，执行下面的删除步
骤就会出错。
4) 删除掉系统缺省的重做日志组：
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
3. 将使用频率高的TABLE 和INDEX 放入内存：用户根据具体情况选择使用；一种：使用SQLPLUS 执
行Script_PoolKeep.sql 和Script_PoolDefault.sql 语句；二种：参考<<网上评卷Oracle 培训讲义>>。
ALTER TABLE TB_TASK_QUEUE STORAGE ( BUFFER_POOL KEEP);
ALTER INDEX INDX_QUEUE STORAGE ( BUFFER_POOL KEEP);
ALTER INDEX INDX_TASK_ITEM STORAGE ( BUFFER_POOL KEEP);
ALTER INDEX PK_QUEUE STORAGE ( BUFFER_POOL KEEP);
ALTER TABLE TB_TASKINFO STORAGE ( BUFFER_POOL KEEP);
ALTER INDEX PK_LI STORAGE ( BUFFER_POOL KEEP);
ALTER TABLE TB_SN1 STORAGE ( BUFFER_POOL KEEP);
ALTER INDEX IND_SN STORAGE ( BUFFER_POOL KEEP);
ALTER TABLE TB_SN2 STORAGE ( BUFFER_POOL KEEP);
ALTER INDEX INDX_REDBY STORAGE ( BUFFER_POOL KEEP);
ALTER INDEX INDX_SN2 STORAGE ( BUFFER_POOL KEEP);
ALTER TABLE TB_TASKINFO_DAY STORAGE ( BUFFER_POOL KEEP);
ALTER INDEX PK_TASKINFO_DAY STORAGE ( BUFFER_POOL KEEP);
ALTER INDEX UK_TASKINFO_DAY_LOGIN STORAGE ( BUFFER_POOL KEEP);
ALTER TABLE TB_TASKINFO_DAY_DIFF STORAGE ( BUFFER_POOL KEEP);
ALTER INDEX PK_TASKINFO_DAY_DIFF STORAGE ( BUFFER_POOL KEEP);
ALTER INDEX TB_TASKINFO_DAY_DIFF_INDEX STORAGE ( BUFFER_POOL KEEP);
ALTER TABLE TB_TASKINFO_DAY_NOVALID STORAGE ( BUFFER_POOL KEEP);
ALTER INDEX PK_TASKINFO_DAY_NOVALID STORAGE ( BUFFER_POOL KEEP);
ALTER INDEX TB_TASKINFO_DAY_NOVALID_INDEX STORAGE ( BUFFER_POOL KEEP);
ALTER TABLE TB_CONFIG STORAGE ( BUFFER_POOL KEEP);
ALTER INDEX IND_CONFIG STORAGE ( BUFFER_POOL KEEP);
ALTER TABLE TB_ITEM STORAGE ( BUFFER_POOL KEEP);
ALTER INDEX PK_ITEM STORAGE ( BUFFER_POOL KEEP);
ALTER TABLE TB_RESULT_DISTRIBUTE STORAGE ( BUFFER_POOL KEEP);
ALTER INDEX INDX_RESULT_DIST STORAGE ( BUFFER_POOL KEEP);
ALTER TABLE TB_SN3 STORAGE ( BUFFER_POOL KEEP);
ALTER INDEX INDX_SN3 STORAGE ( BUFFER_POOL KEEP);
4. 修改TABLE 的参数提高并行
ALTER TABLE TB_CONFIG PCTFREE 40 INITRANS 5;
ALTER TABLE TB_ITEM PCTFREE 40 INITRANS 5;
ALTER TABLE TB_RESULT PCTFREE 40 INITRANS 5;
ALTER TABLE TB_ITEMLOG PCTFREE 40 INITRANS 5;
ALTER TABLE TB_SN1 PCTFREE 40 INITRANS 5;
ALTER TABLE TB_SN2 PCTFREE 40 INITRANS 5;
ALTER TABLE TB_SN3 PCTFREE 40 INITRANS 5;
ALTER TABLE TB_TASK_QUEUE PCTFREE 40 INITRANS 5;
ALTER TABLE TB_TASKINFO PCTFREE 40 INITRANS 5;
ALTER TABLE TB_TASKINFO_DAY PCTFREE 40 INITRANS 5;
ALTER TABLE TB_TASKINFO_DAY_DIFF PCTFREE 40 INITRANS 5;
ALTER TABLE TB_TASKINFO_DAY_NOVALID PCTFREE 40 INITRANS 5;
ALTER TABLE TB_RESULT_DISTRIBUTE PCTFREE 40 INITRANS 5;
5. 修改INDEX 的参数提高并行
ALTER INDEX INDXRESULT2 REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS 5);
ALTER INDEX INDXRESULT1 REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS 5);
ALTER INDEX IND_CONFIG REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS 5);
ALTER INDEX PK_ITEM REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS 5);
ALTER INDEX INDX_RESULT REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS 5);
ALTER INDEX INDX_ITEMLOG REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS 5);
ALTER INDEX INDX_QUEUE REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS 5);
ALTER INDEX PK_QUEUE REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS 5);
ALTER INDEX IND_SN REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS 5);
ALTER INDEX INDX_REDBY REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS 5);
ALTER INDEX INDX_SN2 REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS 5);
ALTER INDEX INDX_SN3 REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS 5);
ALTER INDEX PK_LI REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS 5);
ALTER INDEX IND_SN REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS 5);
ALTER INDEX INDX_REDBY REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS 5);
ALTER INDEX INDX_SN2 REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS 5);
ALTER INDEX PK_TASKINFO_DAY REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS 5);
ALTER INDEX UK_TASKINFO_DAY_LOGIN REBUILD PCTFREE 40 INITRANS 5 STORAGE
( FREELISTS 5);
ALTER INDEX PK_TASKINFO_DAY_DIFF REBUILD PCTFREE 40 INITRANS 5 STORAGE
( FREELISTS 5);
ALTER INDEX TB_TASKINFO_DAY_DIFF_INDEX REBUILD PCTFREE 40 INITRANS 5 STORAGE
( FREELISTS 5);
ALTER INDEX PK_TASKINFO_DAY_NOVALID REBUILD PCTFREE 40 INITRANS 5 STORAGE
( FREELISTS 5);
ALTER INDEX TB_TASKINFO_DAY_NOVALID_INDEX REBUILD PCTFREE 40 INITRANS 5
STORAGE ( FREELISTS 5);
ALTER INDEX INDX_RESULT_DIST REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS
5);
--ALTER INDEX INDX REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS 5);
--ALTER INDEX IINDX_TASK_ITEM REBUILD PCTFREE 40 INITRANS 5 STORAGE ( FREELISTS
5);
6．移动或重建索引

[范佩西_11]

[Quote=引用楼主 AllanJD 的帖子:]
如题！
[/Quote]调优是很艰巨的任务，提供的参数只能参照不能完全拷贝，要考虑业务系统，操作系统具体情况，
一般情况
建议sga
1、sga_max_size 为可用内存的45%
2、shared_pool_size 可用内存的10%左右；
DB_CACHE_SIZE 物理内存的25% 左右
3、如果是共享服务器uga在sga中所以一定要设置large_pool_size
建议large_pool_size为物理内存的5%，如果使用rman，可以设置更大一些。
4、java_pool_size 15M-20M
5、重做日志缓冲区建议设定为1M-2M之间
建议pga
1、pga管理设置为auto
workarea_size_policy = AUTO
2、pga_aggregate_target是pga的使用上限，专用服务器uga是在pga中。建议将
pga_aggregate_target值设定为可用内存的40%
如果用户数量不是很多的情况下，可以适当增加
排序区及散列区的值
sort_area_size 系统默认是64k建议设置为1M或更高。
hash_area_size为sort_area_size两倍

[范佩西_11]

[Quote=引用楼主 AllanJD 的帖子:]
如题！
[/Quote]楼主调优时一定要记得先备份

[zengqinghu2007]

没调过，路过看看

[onlycto]

怎么上传不了附件？

[inthirties]

[Quote=引用楼主 AllanJD 的帖子:]
如题！
[/Quote]


oracle9i的查询优化.pdf白皮书

http://inthirties.googlegroups.com/web/oracle9i的查询优化.pdf?hl=zh-CN&gda=-dgP4WwAAAChAyltsYJDDXWpQPPUmbM_gfUuUP36fQnz7hPDis7oz_PACCpiZzMXMnxhtG0S3TOOMkux6RnbDsMwKRVMFlgnTGZNgjnpxVqyVfzy-Jm73cTRNrijNT6dSBOOx-iHTrL9Wm-ajmzVoAFUlE7c_fAt

或者

http://groups.google.com/group/inthirties/files?hl=zh-CN