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

Question

下面是一些影响ORALCE系统性能的初始化参数，在系统安装与设置时请充分考虑这些值的设置；这些参数不应太大也不应太小。
1、系统全局区（SGA）是一个分配给Oracle 的包含一个 Oracle 实例的数据库的控制信息内存段。
　 主要包括数据库高速缓存(the database buffer cache)，
　 重演日志缓存（the redo log buffer），
　 共享池（the shared pool），
　 数据字典缓存（the data dictionary cache）以及其它各方面的信息。db_block_buffers 
1、数据高速缓冲区
2、访问过的数据都放在这一片内存区域，该参数越大，Oracle在内存中找到相同数据的可能性就越大，也即加快了查询速度。
3、db_block_buffers以块为单位，假如DB_BLOCK_SIZE=2K，db_block_buffers=3200，则占用内存=3200*2K=6400K。share_pool_size 
1、SQL共享缓冲池
2、该参数是库高速缓存和数据字典的高速缓存。Log_buffer 
1、重演日志缓冲区sort_area_size 
1、排序区processes 
1、同时连接的进程数global_names 
1、如果“数据库链路名”和它所要连接的“数据库”拥有相同的名字，则设置global_names = TRUE，否则，设置global_names = FALSEdb_block_size 
1、数据库块大小
2、Oracle默认块为2KB，太小了，因为如果我们有一个8KB的数据，则2KB块的数据库要读4次盘，才能读完，而8KB块的数据库只要1次就读完了，大大减少了I/O操作。
3、数据库安装完成后，就不能再改变db_block_size的值了，只能重新建立数据库
　 并且建库时，要选择手工安装数据库。open_links 
1、同时打开的链接数dml_locks 
1、用户一次可对表设定锁的最大数目
2、如果有三个用户修改６个表，则需１８个DML锁来实现并行操作，如果设定DML_LOCKS不够大，操作时执行将中断，你可以通过你的应用程序的操作规模和最大的并行使用的用户数来估算系统所需要的DML_LOCKS的值，但该值的大小对数据库的其他性能没有影响，所以一般都把它设得很大，远超过实际中可能达到的值，如果在系统运行中经常发生表锁死的现象，就应该考虑加大该值。open_cursors 
1、打开光标数
2、这个参数可以同时被每个用户进程拥有，这个参数的最大值依操作系统不同而不同。
3、在创建一个包含很多表的合成视图时，如果此参数很小，则视图创建会不成功。dbwr_io_slaves 
1、后台写进程数 
ORACLE内存设置
Oracle很有意思，内存越大，效果越明显，所以有必要好好调整一下SGA区，也就是主要配置ininorcl.ora参数文件。下面是一些可修改设置选项
1. 在 Oracle 首次安装时，需要设置好共享池的大小，以确保共享池中有足够大的空间以便使 Oracle 能在共享池中很好地调整高速缓存。2. 在多处理器的计算机上，初始化参数文件项 LOG_SIMULTANEOUS_COPIES 应被设置为 CPU 数的两倍，这将有助于减少潜在的 redo copy latch 争用。3. 当使用 MTS 时，为了 SHARED_POOL_SIZE每个用户存在一个 1K 的额外需求，    该额外空间用于存放关于用户进程、调度程序和服务器之间连接的信息。下面前四点指出了初始化参数文件的五个关键项，按建议调整这些项，有助于 CPU 的调整运行。 分配尽可能多的实存给共享池和数据库缓冲区（初始化参数文件中的SHARED_POOL_SIZE项和DB_BLOCK_BUFFERS项）以便允许在内存中运作尽可能多的工作，与在磁盘工作相比，在内存中工作使用的CPU较少。 将初始化参数文件项 SEQUENCE_CACHE_ENTRIES 设为高值（缺省值为10，可设为1,000）。 分配大于缺省值的内存以便执行排序操作（初始化参数文件中的 SORT_AREA_SIZE项），不请求I/O的内存排序使用较少的CPU。 在多CPU的机器上，增大初始化参数文件项LOG_SIMULTANEOUS_COPIES的值，以便允许每个CPU的进程把项拷贝到重演日志缓冲区中。 为了释放占用的CPU，尽一切可能使I/O最小化。 通过合理分配工作日白天和夜间的负载来使得CPU工作能力最大化。

Answer 1

想到了的是这几个：
shared_pool_size
db_block_buffers
log_buffer
session_cached_cursors
db_file_multiblock_read_count

Answer 2

监控oracl的性能并调整：
Oracle Database Monitoring and Tuning
Version information
SELECT * FROM product_component_version ;
 
List free and used space in database
SELECT sum(bytes)/1024 "free space in KB"
FROM dba_free_space;
SELECT sum(bytes)/1024 "used space in KB"
FROM dba_segments;
 
List session information
SELECT * FROM V$SESSION ;
 
List names and default storage parameters for all tablespaces
SELECT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS,
  PCT_INCREASE, MIN_EXTLEN
FROM DBA_TABLESPACES;
 
Tablespace types, and availability of data files
SELECT TABLESPACE_NAME, CONTENTS, STATUS
FROM DBA_TABLESPACES;
 
List information about tablespace to which datafiles belong
SELECT FILE_NAME,TABLESPACE_NAME,BYTES,AUTOEXTENSIBLE,
       MAXBYTES,INCREMENT_BY
FROM DBA_DATA_FILES;
 
List data file information
SELECT FILE#,T1.NAME,STATUS,ENABLED,BYTES,CREATE_BYTES,T2.NAME
FROM   V$DATAFILE T1, V$TABLESPACE T2
WHERE  T1.TS# = T2.TS# ;
 
List tablespace fragmentation information
SELECT tablespace_name,COUNT(*) AS fragments,
   SUM(bytes) AS total,
   MAX(bytes) AS largest
FROM dba_free_space
GROUP BY tablespace_name;
 
Check the current number of extents and blocks allocated to a segment
SELECT SEGMENT_NAME,TABLESPACE_NAME,EXTENTS,BLOCKS
FROM DBA_SEGMENTS;
 
Check the extents for a given segment
SELECT TABLESPACE_NAME, COUNT(*), MAX(BLOCKS), SUM(BLOCKS)
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NUMBER ;

Answer 3

Extent information
SELECT segment_name, extent_id, blocks, bytes
      FROM dba_extents
      WHERE segment_name = TNAME ;
 
Extent information for a table
SELECT segment_name, extent_id, blocks, bytes
      FROM dba_extents
      WHERE segment_name = TNAME ;
 
List segments with fewer than 5 extents remaining
SELECT segment_name,segment_type,
       max_extents, extents
FROM dba_segments
WHERE extents+5 > max_extents
AND segment_type<>'CACHE';
 
List segments reaching extent limits
SELECT s.segment_name,s.segment_type,s.tablespace_name,s.next_extent
FROM dba_segments s
WHERE NOT EXISTS (SELECT 1
   FROM dba_free_space f
   WHERE s.tablespace_name=f.tablespace_name
   HAVING max(f.bytes) > s.next_extent);
 
List table blocks, empty blocks, extent count, and chain block count
SELECT blocks as BLOCKS_USED, empty_blocks
FROM dba_tables
WHERE table_name=TNAME;SELECT chain_cnt AS CHAINED_BLOCKS
FROM dba_tables
WHERE table_name=TNAME;SELECT COUNT(*) AS EXTENT_COUNT
FROM dba_extents
WHERE segment_name=TNAME;
 
Information about all rollback segments in the databaseSELECT SEGMENT_NAME,TABLESPACE_NAME,OWNER,STATUS
FROM DBA_ROLLBACK_SEGS;/* General Rollback Segment Information */SELECT t1.name, t2.extents, t2.rssize, t2.optsize, t2.hwmsize, t2.xacts, t2.status
FROM   v$rollname t1, v$rollstat t2
WHERE  t2.usn = t1.usn ;/* Rollback Segment Information - Active Sessions */select t2.username, t1.xidusn, t1.ubafil, t1.ubablk, t2.used_ublk
from v$session t2, v$transaction t1
where t2.saddr = t1.ses_addr 
 
Statistics of the rollback segments currently used by instance
SELECT T1.NAME, T2.EXTENTS, T2.RSSIZE, T2.OPTSIZE, T2.HWMSIZE,
          T2.XACTS, T2.STATUS
FROM   V$ROLLNAME T1, V$ROLLSTAT T2
WHERE  T1.USN = T2.USN AND
       T1.NAME LIKE '%RBS%';
 
List sessions with active transactions
SELECT s.sid, s.serial#
 FROM v$session s
 WHERE s.saddr in
  (SELECT t.ses_addr
    FROM V$transaction t, dba_rollback_segs r
    WHERE t.xidusn=r.segment_id
    AND r.tablespace_name='RBS');
 
Active sorts in instance
SELECT T1.USERNAME, T2.TABLESPACE, T2.CONTENTS, T2.EXTENTS, T2.BLOCKS
FROM V$SESSION T1, V$SORT_USAGE T2
WHERE T1.SADDR = T2.SESSION_ADDR ;
 
Index & constraint information
SELECT index_name,table_name,uniqueness
FROM dba_indexes
WHERE index_name in
  (SELECT constraint_name
   FROM dba_constraints
   WHERE table_name = TNAME
    AND constraint_type in ('P','U')) ;
 
Constraint columns
SELECT constraint_name,table_name, column_name
FROM dba_cons_columns
WHERE table_name = TNAME
ORDER BY table_name, constraint_name, position
END IF;
 
Constraint listing
SELECT constraint_name, table_name,
     constraint_type, validated, status
FROM dba_constraints;
 
Trigger listing
SELECT trigger_name, status
 FROM dba_triggers ;

Answer 4

Tuning: library cache
Glossary: 
pins = # of time an item in the library cache was executed
reloads = # of library cache misses on execution
Goal: 
get hitratio to be less than 1 
Tuning parm: 
adjust SHARED_POOL_SIZE in the initxx.ora file, increasing by small increments 
SELECT    SUM(PINS) EXECS,
          SUM(RELOADS)MISSES,
          SUM(RELOADS)/SUM(PINS) HITRATIO
FROM      V$LIBRARYCACHE ; 
Tuning: data dictionary cache
Glossary: 
gets = # of requests for the item 
getmisses = # of requests for items in cache which missed
Goal: 
get rcratio to be less than 1 
Tuning parm: 
adjust SHARED_POOL_SIZE in the initxx.ora file, increasing by small increments 
SELECT    SUM(GETS) HITS,
          SUM(GETMISSES) LIBMISS,
          SUM(GETMISSES)/SUM(GETS) RCRATIO
FROM      V$ROWCACHE ; 
Tuning: buffer cache
Calculation:
buffer cache hit ratio = 1 - (phy reads/(db_block_gets + consistent_gets))
Goal:
get hit ratio in the range 85 - 90%
Tuning parm:
adjust DB_BLOCK_BUFFERS in the initxx.ora file, increasing by small increments 
SELECT NAME, VALUE
FROM   V$SYSSTAT WHERE NAME IN
   ('DB BLOCK GETS','CONSISTENT GETS','PHYSICAL READS'); 
Tuning: sorts
Goal: 
Increase number of memory sorts vs disk sorts 
Tuning parm:
adjust SORT_AREA_SIZE in the initxx.ora file, increasing by small increments 
SELECT NAME, VALUE
FROM   V$SYSTAT
WHERE NAME LIKE '%SORT%'; 
Tuning: dynamic extension
An informational query. 
SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME='RECURSIVE CALLS' ;
 
Tuning: rollback segments
Goal: 
Try to avoid increasing 'undo header' counts
Tuning method: 
Create more rollback segments, try to reduce counts
SELECT CLASS,COUNT
FROM V$WAITSTAT
WHERE CLASS LIKE '%UNDO%' ;
 
Tuning: physical file placement
Informational in checking relative usages of the physical data files. 
SELECT NAME, PHYRDS,PHYWRTS
FROM V$DATAFILE DF, V$FILESTAT FS
WHERE DF.FILE#=FS.FILE# ; 
Killing Sessions
Runaway processes can be killed on the UNIX side, or within server manager. 
/* Kill a session, specified by the returned sess-id / serial number */
SELECT sid, serial#, username from v$session
ALTER SYSTEM KILL SESSION 'sessid,ser#' 
Archive Log Mode Status
/* Status of Archive Log Subsystem */
ARCHIVE LOG LIST
/* log mode of databases */
SELECT name, log_mode FROM v$database;
/* log mode of instance */SELECT archiver FROM v$instance;
 
Recovering an Instance
An incomplete recovery is the only option if backups are run periodically on a cold instance. Complete recovery is possible if archive logging is enabled, and backups are run while the database is active. 
/* diagnose data file problem */
select * from v$recover_file ;/* diagnose data file problem, by displaying tablespace info */
select file_id, file_name, tablespace_name, status
from dba_data_files ;/* find archive log files */
select * from v$recovery_log ;/* incomplete recovery #1 */
svrmgrl> shutdown abort[[ In Unix copy data files from backup area to data directory(s). ]]svrmgrl> connect;
svrmgrl> startup;/* incomplete recovery #2 */svrmgrl> shutdown abort;
svrmgrl> connect;
svrmgrl> startup mount;
svrmgrl> alter database rename file '/data2/ts05.dbf' to '/backups/ts05.dbf'
svrmgrl> alter database open;
/* incomplete recovery #3, for user error (i.e. drop table ) */
Note:  archive logs must exist in LOG_ARCHIVE_DESTsvrmgrl> shutdown abort [[ backup all files ]]
 [[ restore required data file(s), using OS commands ]]svrmgrl> connect;
svrmgrl> startup mount;
svrmgrl> recover database until time '2001-03-04:15:00:00' ;
svrmgrl> alter database open resetlogs;
/* complete recovery #1, for major recovery operations, closed instance */
Note:  archive logs must exist in LOG_ARCHIVE_DESTsvrmgrl> shutdown abort [[ backup all files ]]svrmgrl> connect;
svrmgrl> startup mount;
svrmgrl> recover database ;
  < or >
svrmgrl> recover datafile '/data4/ts03.dbf'
svrmgrl> startup open;
/* complete recovery #2, for major/minor recovery operations, open instance */
Note:  archive logs must exist in LOG_ARCHIVE_DESTsvrmgrl> shutdown abort [[ backup all files ]]
 [[ restore corrupted data files, using OS commands ]]svrmgrl> connect;
svrmgrl> startup mount;
svrmgrl> set autorecovery on ;
svrmgrl> recover tablespace ts03 ;
  < or >
svrmgrl> recover datafile 4 ;
svrmgrl> startup open; 
List log file information
These queries list the status / locations of the redo log files. 
select group#, member, status from v$logfile ;select group#,thread#,archived,status from v$log ;

Answer 5

请问高手门如果我要该参数是否可以直接在init.ora里改就可以了，还是装完数据库后就不能改了，只有在安装时配置好参数？

Answer 6

在系统安装与设置时请充分考虑这些值的设置；这些参数不应太大也不应太小
这个话不完全正确。
对于db_block_buffers尽量给的大，当然要保证OS足够的内存。
对于share_pool_size则应该是不应太大也不应太小，一般在100M-200M左右（特殊的应用，如ORACLE ERP可适当放大到300M-500M），如果这样设置后还有性能问题，那更多的应该从应用程序去调整，而不是从share_pool_size来调整了。

Answer 7

z816hot293(雪中火) ：这些参数除了DB_BLOCK_SIZE是在安装完数据库后不能改了，其余基本都可以在参数文件中修改。但修改后必须重新启动数据库，才可以使修改生效。

Answer 8

呵呵，楼上的大峡：
在Oracle9i中，SGA各参数中，只有Large_pool不是动态的。期于都可以动态修改（Block是
不能改）。

Answer 9

其实在Oracle9i中DB_BLOCK_SIZE也是相对动态，也就是对每个表空间可以不同，但表空间建立后也是不能改了。
另，我不是什么大峡，ORACLE的高手太多了，我知道一点皮毛都算不上。

Answer 10

如果非要用一本书才能解释的清楚的问题在此提出，我觉得有必要考虑这个即使得到答案
是否能明白呢？我个人觉得提问要提出一些实际的，值得探讨的问题，这么大的问题，很
返，而且。算了，我觉得斑竹还是置顶一个问问题的规矩吧，呵呵，就象ITPUB

Answer 11

samenzhou(崂山道士)说的有道理。
提问也要讲方法的。

Answer 12

去买本书看看吧，要时光听人说就昏了，机械工业出版社的<oracle性能优化技术内幕很不错的〉，深入浅出哦：）