哪些初始化参数最影响Oracle系统性能

废话大仙

<ul type="disc"><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l10 level1 lfo1; tab-stops: list 36.0pt;"><font size="3"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">什么是</span><span lang="EN-US"><font face="Times New Roman">SGA</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">（系统全局区）</span></font></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l10 level1 lfo1; tab-stops: list 36.0pt;"><font size="3"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"></span></font><font size="3"><span lang="EN-US"><font face="Times New Roman">1</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、系统全局区（</span><span lang="EN-US"><font face="Times New Roman">SGA</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">）是一个分配给</span><span lang="EN-US"><font face="Times New Roman">Oracle </font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">的包含一个</span><span lang="EN-US"><font face="Times New Roman"> Oracle </font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">实例的数据库的控制信息内存段。</span></font><span lang="EN-US"><br/></span><font size="3"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">　</span><font face="Times New Roman"> </font><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">主要包括数据库高速缓存</span><span lang="EN-US"><font face="Times New Roman">(the database buffer cache)</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">，</span></font><span lang="EN-US"><br/></span><font size="3"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">　</span><font face="Times New Roman"> </font><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">重演日志缓存（</span><span lang="EN-US"><font face="Times New Roman">the redo log buffer</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">），</span></font><span lang="EN-US"><br/></span><font size="3"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">　</span><font face="Times New Roman"> </font><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">共享池（</span><span lang="EN-US"><font face="Times New Roman">the shared pool</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">），</span></font><span lang="EN-US"><br/></span><font size="3"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">　</span><font face="Times New Roman"> </font><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">数据字典缓存（</span><span lang="EN-US"><font face="Times New Roman">the data dictionary cache</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">）以及其它各方面的信息。</span></font></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l5 level1 lfo2; tab-stops: list 36.0pt;"><span lang="EN-US"><font face="Times New Roman" size="3">db_block_buffers </font></span></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l5 level1 lfo2; tab-stops: list 36.0pt;"><span lang="EN-US"></span><font size="3"><span lang="EN-US"><font face="Times New Roman">1</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、数据高速缓冲区</span></font><span lang="EN-US"><br/><font face="Times New Roman" size="3">2</font></span><font size="3"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、访问过的数据都放在这一片内存区域，该参数越大，</span><span lang="EN-US"><font face="Times New Roman">Oracle</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">在内存中找到相同数据的可能性就越大，也即加快了查询速度。</span></font><span lang="EN-US"><br/><font face="Times New Roman" size="3">3</font></span><font size="3"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、</span><span lang="EN-US"><font face="Times New Roman">db_block_buffers</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">以块为单位，假如</span><span lang="EN-US"><font face="Times New Roman">DB_BLOCK_SIZE=2K</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">，</span><span lang="EN-US"><font face="Times New Roman">db_block_buffers=3200</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">，则占用内存</span><span lang="EN-US"><font face="Times New Roman">=3200*2K=6400K</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">。</span></font></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l8 level1 lfo3; tab-stops: list 36.0pt;"><span lang="EN-US"><font face="Times New Roman" size="3">share_pool_size </font></span></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l8 level1 lfo3; tab-stops: list 36.0pt;"><span lang="EN-US"></span><font size="3"><span lang="EN-US"><font face="Times New Roman">1</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、</span><span lang="EN-US"><font face="Times New Roman">SQL</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">共享缓冲池</span></font><span lang="EN-US"><br/><font face="Times New Roman" size="3">2</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"><font size="3">、该参数是库高速缓存和数据字典的高速缓存。</font></span></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l7 level1 lfo4; tab-stops: list 36.0pt;"><span lang="EN-US"><font face="Times New Roman" size="3">Log_buffer </font></span></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l7 level1 lfo4; tab-stops: list 36.0pt;"><span lang="EN-US"></span><font size="3"><span lang="EN-US"><font face="Times New Roman">1</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、重演日志缓冲区</span></font></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l9 level1 lfo5; tab-stops: list 36.0pt;"><span lang="EN-US"><font face="Times New Roman" size="3">sort_area_size </font></span></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l9 level1 lfo5; tab-stops: list 36.0pt;"><span lang="EN-US"></span><font size="3"><span lang="EN-US"><font face="Times New Roman">1</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、排序区</span></font></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l4 level1 lfo6; tab-stops: list 36.0pt;"><span lang="EN-US"><font face="Times New Roman" size="3">processes </font></span></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l4 level1 lfo6; tab-stops: list 36.0pt;"><span lang="EN-US"></span><font size="3"><span lang="EN-US"><font face="Times New Roman">1</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、同时连接的进程数</span></font></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l0 level1 lfo7; tab-stops: list 36.0pt;"><span lang="EN-US"><font face="Times New Roman" size="3">global_names </font></span></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l0 level1 lfo7; tab-stops: list 36.0pt;"><span lang="EN-US"></span><font size="3"><span lang="EN-US"><font face="Times New Roman">1</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、如果</span><span lang="EN-US"><font face="Times New Roman">“</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">数据库链路名</span><span lang="EN-US"><font face="Times New Roman">”</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">和它所要连接的</span><span lang="EN-US"><font face="Times New Roman">“</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">数据库</span><span lang="EN-US"><font face="Times New Roman">”</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">拥有相同的名字，则设置</span><span lang="EN-US"><font face="Times New Roman">global_names = TRUE</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">，</span></font><span lang="EN-US"><br/></span><font size="3"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">　</span><font face="Times New Roman"> </font><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">否则，设置</span><span lang="EN-US"><font face="Times New Roman">global_names = FALSE</font></span></font></li></ul>

废话大仙

<li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l3 level1 lfo8; tab-stops: list 36.0pt;"><span lang="EN-US"><font face="Times New Roman" size="3">db_block_size </font></span></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l3 level1 lfo8; tab-stops: list 36.0pt;"><span lang="EN-US"></span><font size="3"><span lang="EN-US"><font face="Times New Roman">1</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、数据库块大小</span></font><span lang="EN-US"><br/><font face="Times New Roman" size="3">2</font></span><font size="3"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、</span><span lang="EN-US"><font face="Times New Roman">Oracle</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">默认块为</span><span lang="EN-US"><font face="Times New Roman">2KB</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">，太小了，因为如果我们有一个</span><span lang="EN-US"><font face="Times New Roman">8KB</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">的数据，则</span><span lang="EN-US"><font face="Times New Roman">2KB</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">块的数据库要读</span><span lang="EN-US"><font face="Times New Roman">4</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">次盘，才能读完，</span></font><font size="3"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">而</span><span lang="EN-US"><font face="Times New Roman">8KB</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">块的数据库只要</span><span lang="EN-US"><font face="Times New Roman">1</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">次就读完了，大大减少了</span><span lang="EN-US"><font face="Times New Roman">I/O</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">操作。</span></font><span lang="EN-US"><br/><font face="Times New Roman" size="3">3</font></span><font size="3"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、数据库安装完成后，就不能再改变</span><span lang="EN-US"><font face="Times New Roman">db_block_size</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">的值了，只能重新建立数据库</span></font><span lang="EN-US"><br/></span><font size="3"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">　</span><font face="Times New Roman"> </font><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">并且建库时，要选择手工安装数据库。</span></font></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l1 level1 lfo9; tab-stops: list 36.0pt;"><span lang="EN-US"><font face="Times New Roman" size="3">open_links </font></span></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l1 level1 lfo9; tab-stops: list 36.0pt;"><span lang="EN-US"></span><font size="3"><span lang="EN-US"><font face="Times New Roman">1</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、同时打开的链接数</span></font><span lang="EN-US"><font face="Times New Roman" size="3">dml_locks </font></span></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l11 level1 lfo10; tab-stops: list 36.0pt;"><span lang="EN-US"></span><font size="3"><span lang="EN-US"><font face="Times New Roman">1</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、用户一次可对表设定锁的最大数目</span></font><span lang="EN-US"><br/><font face="Times New Roman" size="3">2</font></span><font size="3"><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、如果有三个用户修改６个表，则需１８个</span><span lang="EN-US"><font face="Times New Roman">DML</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">锁来实现并行操作，如果设定</span><span lang="EN-US"><font face="Times New Roman">DML_LOCKS</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">不够大，操作时执行将中断，你可以通过你的应用程序的操作规模和最大的并行使用的用户数来估算系统所需要的</span><span lang="EN-US"><font face="Times New Roman">DML_LOCKS</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">的值，但该值的大小对数据库的其他性能没有影响，所以一般都把它设得很大，远超过实际中可能达到的值，如果在系统运行中经常发生表锁死的现象，就应该考虑加大该值。</span></font><span lang="EN-US"><font face="Times New Roman" size="3">open_cursors </font></span></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l11 level1 lfo10; tab-stops: list 36.0pt;"><span lang="EN-US"></span><font size="3"><span lang="EN-US"><font face="Times New Roman">1</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';">、打开光标数</span></font><span lang="EN-US"><br/><font face="Times New Roman" size="3">2</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"><font size="3">、这个参数可以同时被每个用户进程拥有，这个参数的最大值依操作系统不同而不同。</font></span><span lang="EN-US"><br/><font face="Times New Roman" size="3">3</font></span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman';"><font size="3">、在创建一个包含很多表的合成视图时，如果此参数很小，则视图创建会不成功。</font></span></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l6 level1 lfo12; tab-stops: list 36.0pt;"><span lang="EN-US"><font face="Times New Roman" size="3">dbwr_io_slaves </font></span></li><li class="MsoNormal" style="MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-list: l6 level1 lfo12; tab-stops: list 36.0pt;"><span lang="EN-US"></span><span lang="EN-US" style="FONT-SIZE: 12pt; FONT-FAMILY: 'Times New Roman'; mso-fareast-font-family: 宋体; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA;">1</span><span style="FONT-SIZE: 12pt; FONT-FAMILY: 宋体; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA; mso-ascii-font-family: 'Times New Roman'; mso-hansi-font-family: 'Times New Roman'; mso-bidi-font-family: 'Times New Roman';">、后台写进程数</span></li>

ydiandian

都是些最基本的知识要点,到处都有哈!oracle的权威资料集中地方是<a href="http://www.oracle.com.cn">www.oracle.com.cn</a>那里的论坛

翔羽

看来大仙是研究<span lang="EN-US"><font face="Times New Roman">Oracle的高人了。我对<span lang="EN-US"><font face="Times New Roman">Oracle可是一窍不通的。</font></span></font></span>

废话大仙

呵呵，老大不要寒碜我了，这些东西都是找的资料，懂一些，但是还没有这么高的武艺啊

海边疯

顶一下吧~ 很少见的好帖了