关于数据库的调整（不只是调优）问题

lunar2000

最初由 jiang 发布
[B]

若不想升级， 就把下列参数加到init 文件中， 可能会影响性能：
# ORA-4031 / SGA memory leak of PERMANENT memory occurs
# for buffer handles.
_db_handles_cached = 0

# INSERT AS SELECT statements may not be shared when
# they should be if  TIMED_STATISTICS. It can lead to ORA-4031
_SQLEXEC_PROGRESSION_COST=0

先加一个， 看能够解决问题， 不行换另外一个。

还有两个可能导致ORA-4031 , 但不升级无法解决， 所以你最好升级, Oracle 有上千个Bug需要Fix [/B]

同意，这个意见好，先加_db_handles_cached = 0 ，以便判断是否是遭遇了Bug:1397603；
然后再设置_SQLEXEC_PROGRESSION_COST=0 ，以便判断是否是遭遇了Bug:1318267；

但是，还有个问题，我是否应该在三个实例上都按照这个步骤做这样的改动呢？

如果现在只有一个实例报错，那又说明什么？如何应对呢？

lunar2000

最初由 ggf0626 发布
[B]1,在OPS 8.1.5，8.1.6和8.1.7中，一定要在init<sid>.ora文件中设置
_SQLEXEC_PROGRESSION_COST=0

2,do u need to set gc_files_to_locks
3,lm_ress and lm_locks ,query v$resource_limit
4,  _lm_direct_sends         = lkmgr
5,why not set log_checkpoint_interval=1000000 [/B]

你能说说log_checkpoint_interval=1000000 这样设置的原因么？
关于LOG_CHECKPOINT_INTERVAL我只知道 ，
在低于ORACLE 8.1.X 的版本中，LGWR 一写入参数LOG_CHECKPOINT_INTERVAL 指定的块数就启动了检查点；在ORACLE 8.1.x Enterprise Edition 版本及其后续版本内，当指定了LOG_CHECKPOINT_INTERVAL 后，检查点位置目标相对于日志尾的滞后不能大于该参数指定的重做日志块数。
但是对于具体的量化他，不知道因该如何考虑（比如说，为什么set log_checkpoint_interval=1000000 而不是set log_checkpoint_interval=2000000 ）

还请指点

oldblackwolfdog

our system use ops 8.1.7.2
1  
before upgrade from 8.1.7.0 .ops system often slow,
oracle support advise set
_direct_sends=lkmgr  parameter

it really do work.

2.
later ,according the patch cd (oracle corp send to us)
's document.myself do the patch upgrade .that time (2001.9)
i am enen not familor oracle very much , so i think upgrade is
not so difficult and dangeous. (but before you do ,do a full database backup

3.
the gc_file_to_locks also need to tunning ops system
since our tunning all over province .the oracle runing good than before.
a few days ago ,we enlarge a city's database's tablespace,but forget add these datafile's fileno to gc_file_to_locks,then
user complain their system often congest for about five minuts
every two half.after we set these files gc_file parameter.
the system again very good.

4.other parameter ggf said also recommended

we never meet 04031 before .may be you really need upgrade

lunar2000

有服务真好

ggf0626

to lunar2000
但是对于具体的量化他，不知道因该如何考虑（比如说，为什么set log_checkpoint_interval=1000000 而不是set log_checkpoint_interval=2000000 ）

其实没有具体量化的指标，只是让LOG_CHECKPOINT_INTERVAL足够大，而一般不发挥作用。
you can query v$instance_recovery to find which parameter really affect the period of checkpoint.
理想的情况下是，30分钟左右CHECKPOINT一次。

如果LOG_CHECKPOINT_INTERVAL=10000，在事务高峰时，可能1-2分钟就会CHECKPOINT，所以。。。

Cluster

I don't know why you setting up you init.ora this way,
here is my system init.ora file for you referance, this is 32 CPU and 128GB memory.
8 node (NUMA) system

AEFcommon.ora
#=============================================================
=====
control_files = (?/dbs/aef_disks/control_001)
compatible = 8.1.5
db_name = aef
db_files = 748
timed_statistics = FALSE
db_writer_processes = 1
db_block_lru_latches = 18
cpu_count = 4
parallel_server = TRUE
db_block_buffers = 5491744 # 10.47gig
buffer_pool_recycle = (buffers:13180,lru_latches:5)
buffer_pool_keep =(buffers:3844220,lru_latches:5)
log_buffer = 8192000
enqueue_resources = 600
_db_writer_max_writes = 256 # 640
_db_writer_chunk_writes = 64 # 100
db_block_max_dirty_target = 0
fast_start_io_target = 0
shared_pool_size = 200000000
shared_pool_reserved_size = 2500000
cursor_space_for_time = TRUE
max_dump_file_size = 900000
db_block_checking = FALSE
replication_dependency_tracking = FALSE
transaction_auditing = FALSE
recovery_parallelism = 80
parallel_max_servers = 80
dml_locks = 300
hash_join_enabled = FALSE
log_archive_start = FALSE
log_checkpoint_timeout = 0
log_checkpoint_interval = 0
log_checkpoints_to_alert = TRUE
sort_area_size = 1048576
hash_area_size = 1048576
hash_multiblock_io_count = 1
open_cursors = 140
processes = 140
db_file_multiblock_read_count = 1
_db_file_noncontig_mblock_read_count=1
distributed_transactions = 0
max_rollback_segments = 140
lm_ress = 1431024
lm_locks = 2660000
gc_releasable_locks = 5000
gc_rollback_locks = "0-1631=50EACH"
gc_files_to_locks = "1=3000EACH:" # system
# cust 7000
gc_files_to_locks = "9,91-97=56000:105-112=56000:123-130=56000:"
gc_files_to_locks = "131-133,138-142=56000:149-156=56000:"
gc_files_to_locks = "252-259=56000:270-277=56000:278,289-295=56000:296=1:"
# stock 12000
gc_files_to_locks = "88-90,98-104=120000:113=12000:114-
122=96000:134=12000:"
gc_files_to_locks = "135-137,143-148=96000:"
gc_files_to_locks = "157=12000:158-159,200,246-251=96000:"
gc_files_to_locks = "260=12000:261-269=96000:279=12000:280-288=96000:"
gc_files_to_locks = "297=12000:298-306=96000:307=12000:308-
316=96000:317=8:"
# all others ************
gc_files_to_locks = "2-8,10-87,160-199,201-245=8EACH:" # all others
gc_files_to_locks = "327,328=20EACH" # temp
gc_files_to_locks = "326=20EACH" # temperm2 bench tables
#
java_pool_size = 4K
mts_max_servers = 0
_db_aging_stay_count = 1
_spin_count = 3000
_lm_rcv_buffer_size = 1048576
_interconnect_checksum = FALSE
_lm_cache_res_cleanup = 100
#_lm_direct_sends = lkmgr # needed for tcp version
#_lm_sync_timeout = 120000 # in centi secs

run_1.ora
# ---------------------------------------------------
# run_1 for node1 of 8 node aef
# ---------------------------------------------------
ifile=$ORACLE_HOME/dbs/common.ora
#_ipc_net = alt8
#_ipc_net = mc0
_ipc_net = lo0
instance_number = 1
thread = 1
rollback_segments =
(t_0_1,t_0_2,t_0_3,t_0_4,t_0_5,t_0_6,t_0_7,t_0_8,t_0_9,t_0_10,t_0_11,t_0_12,t_0_13
,t_0_14,t_0_15,t_0_16,t_0_17,t_0_18,t_0_19,t_0_20,t_0_21,t_0_22,t_0_23,t_0_24,t_0
_25,t_0_26,t_0_27,t_0_28,t_0_29,
t_0_30,t_0_31,t_0_32,t_0_33,t_0_34,t_0_35,t_0_36,t_0_37,t_0_38,t_0_39,t_0_40,
t_0_41,t_0_42,t_0_43,t_0_44,t_0_45,t_0_46,t_0_47,t_0_48,t_0_49,t_0_50,t_0_51,t_0
_52,t_0_53,t_0_54,t_0_55,t_0_56,t_0_57,t_0_58,t_0_59,t_0_60,t_0_61,t_0_62,
t_0_63,t_0_64,t_0_65,t_0_66,t_0_67,t_0_68,t_0_69,t_0_70,t_0_71,t_0_72,t_0_73,t_0
_74,t_0_75,t_0_76,t_0_77,t_0_78,t_0_79,t_0_80,t_0_81,t_0_82,t_0_83,t_0_84,t_0_85
,t_0_86,t_0_87,t_0_88,t_0_89,t_0_90,t_0_91,t_0_92,t_0_93,t_0_94,t_0_95,t_0_96,t_0
_97,t_0_98,t_0_99,t_0_100)

run_2.ora
# ---------------------------------------------------
# run_2 for node2 of 8 node aef
# ---------------------------------------------------
ifile=$ORACLE_HOME/dbs/common.ora
#_ipc_net = alt9
_ipc_net = lo0
instance_number = 2
thread = 2
rollback_segments =
(t_1_1,t_1_2,t_1_3,t_1_4,t_1_5,t_1_6,t_1_7,t_1_8,t_1_9,t_1_10,t_1_11,t_1_12,t_1_13
,t_1_14,t_1_15,t_1_16,t_1_17,t_1_18,t_1_19,t_1_20,t_1_21,t_1_22,t_1_23,t_1_24,t_1
_25,t_1_26,t_1_27,t_1_28,t_1_29,
t_1_30,t_1_31,t_1_32,t_1_33,t_1_34,t_1_35,t_1_36,t_1_37,t_1_38,t_1_39,t_1_40,
t_1_41,t_1_42,t_1_43,t_1_44,t_1_45,t_1_46,t_1_47,t_1_48,t_1_49,t_1_50,t_1_51,t_1
_52,t_1_53,t_1_54,t_1_55,t_1_56,t_1_57,t_1_58,t_1_59,t_1_60,t_1_61,t_1_62,
t_1_63,t_1_64,t_1_65,t_1_66,t_1_67,t_1_68,t_1_69,t_1_70,t_1_71,t_1_72,t_1_73,t_1
_74,t_1_75,t_1_76,t_1_77,t_1_78,t_1_79,t_1_80,t_1_81,t_1_82,t_1_83,t_1_84,t_1_85
,t_1_86,t_1_87,t_1_88,t_1_89,t_1_90,t_1_91,t_1_92,t_1_93,t_1_94,t_1_95,t_1_96,t_1
_97,t_1_98,t_1_99,t_1_100)
#rollback_segments =
(t_1_101,t_1_102,t_1_103,t_1_104,t_1_105,t_1_106,t_1_107,t_1_108,t_1_109,t_1_11
0,t_1_111,t_1_112,t_1_113,t_1_114,t_1_115,t_1_116,t_1_117,t_1_118,t_1_119,t_1_1
20,t_1_121,t_1_122,t_1_123,t_1_124,t_1_125,t_1_126,t_1_127,t_1_128,t_1_129,
t_1_130)
#rollback_segments =
(t_1_131,t_1_132,t_1_133,t_1_134,t_1_135,t_1_136,t_1_137,t_1_138,t_1_139,t_1_14
0,
t_1_141,t_1_142,t_1_143,t_1_144,t_1_145,t_1_146,t_1_147,t_1_148,t_1_149,t_1_15
0,t_1_151,t_1_152,t_1_153,t_1_154,t_1_155,t_1_156,t_1_157,t_1_158,t_1_159,t_1_1
60,t_1_161,t_1_162,
t_1_163,t_1_164,t_1_165,t_1_166,t_1_167,t_1_168,t_1_169,t_1_170,t_1_171,t_1_17
2,t_1_173,t_1_174,t_1_175,t_1_176,t_1_177,t_1_178,t_1_179,t_1_180,t_1_181,t_1_1
82,t_1_183,t_1_184,t_1_185,t_1_186,t_1_187,t_1_188,t_1_189,t_1_190,t_1_191,t_1_
192,t_1_193,t_1_194,t_1_195,t_1_196,t_1_197,t_1_198,t_1_199,t_1_200)

p_run_3.ora
# ---------------------------------------------------
# run_3 for node3 of 8 node aef
# ---------------------------------------------------
ifile=$ORACLE_HOME/dbs/common.ora
#_ipc_net = alt10
_ipc_net = lo0
instance_number = 3
thread = 3
rollback_segments =
(t_2_1,t_2_2,t_2_3,t_2_4,t_2_5,t_2_6,t_2_7,t_2_8,t_2_9,t_2_10,t_2_11,t_2_12,t_2_13
,t_2_14,t_2_15,t_2_16,t_2_17,t_2_18,t_2_19,t_2_20,t_2_21,t_2_22,t_2_23,t_2_24,t_2
_25,t_2_26,t_2_27,t_2_28,t_2_29,
t_2_30,t_2_31,t_2_32,t_2_33,t_2_34,t_2_35,t_2_36,t_2_37,t_2_38,t_2_39,t_2_40,
t_2_41,t_2_42,t_2_43,t_2_44,t_2_45,t_2_46,t_2_47,t_2_48,t_2_49,t_2_50,t_2_51,t_2
_52,t_2_53,t_2_54,t_2_55,t_2_56,t_2_57,t_2_58,t_2_59,t_2_60,t_2_61,t_2_62,
t_2_63,t_2_64,t_2_65,t_2_66,t_2_67,t_2_68,t_2_69,t_2_70,t_2_71,t_2_72,t_2_73,t_2
_74,t_2_75,t_2_76,t_2_77,t_2_78,t_2_79,t_2_80,t_2_81,t_2_82,t_2_83,t_2_84,t_2_85
,t_2_86,t_2_87,t_2_88,t_2_89,t_2_90,t_2_91,t_2_92,t_2_93,t_2_94,t_2_95,t_2_96,t_2
_97,t_2_98,t_2_99,t_2_100)


.......
......


Could you show me your sysconfigtab file?

chao_ping

对Numa体系结构不熟悉，不知道究竟是怎么实现的。从来没有接触过。好像也是Compaq的高端系列
这里看来，实际上是8个节点的OPS应用了。
8i 也支持CLuster 文件系统了么？我看controlfile是放在文件系统上面（或者只是符号连接？）
看样子是一个极其标准的OLTP应用，都是为性能最大化做的优化，
只是有一点不太明白：parallel_max_servers = 80，既然是纯粹OLTP，为什么设置这么大的这个参数，仅仅是为了parallel recovery 么？

还有一个就是感觉Log_buffer太大了，可能对于log file sync这个不太有利。
请指点。

Cluster

You answer yourself questions already.

parallel_max_servers = 80 for less indexes usage and increase the CPU usage
log_buffer for reduce the LGWR activity.
If the system never down, do you think I should worry about the log file sync?

ggf0626

log_checkpoint_timeout = 0
log_checkpoint_interval = 0
fast_start_io_target = 0
log_checkpoints_to_alert = TRUE
CHECKPOINT应该只受LOGFILE SWITCH的影响，这种系统一般多久做一次CHECKPOINT?

db_writer_processes = 1
db_file_multiblock_read_count = 1
我觉得最慢的肯定是磁盘I/O，为什么这样设啊

lunar2000

to  Cluster

谢谢你共享你们的配置，虽然我不懂你说的Numa体系结构。

还有， 不好意思，我们没有root口令，所以取不到sysconfigtab文件。