Oracle数据库SGA优化与参数设置详解

    系统全局区 (SGA) 是一组共享内存结构, 其中包含一个 Oracle 数据库的数据和控制信息。启动 Oracle 数据库实例时, 将在内存中分配 SGA。

    本例以solaris中的oracle为例。


1.显示初始参数位置。
SQL> show parameter spfile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /oracle/u01/app/oracle/product
                                                 /10g/dbs/spfilezxminos.ora
SQL> show parameter sga;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 17179869184
sga_target                           big integer 17179869184
SQL>

2.创建出文本初始化参数文件，文件位置如上查询。
SQL>create pfile from spfile;

3.备份pfile文件
cd $ORACLE_HOME/dbs
cp initzxminos.ora initzxminos.bak

4.修改initSID.ora文件中初始化参数。

 sga_target=17179869184  ///sga_max_size=419430400  
 optimizer_mode='RULE'
 processes=250
5.修改spfile文件
SQL>create spfile from pfile;
6.关闭数据库重启数据库




一．设置合适的SGA 

    常常有人抱怨服务器硬件很好，但是Oracle就是很慢。很可能是内存分配不合理造成的。(1)假设内存有512M，这通常是小型应用。建议Oracle的SGA大约240M，其中：共享池（SHARED_POOL_SIZE）可以设置60M到80M，根据实际的用户数、查询等来定。数据块缓冲区可以大致分配120M-150M，8i下需要设置DB_BLOCK_BUFFERS，DB_BLOCK_BUFFER*DB_BLOCK_SIZE等于数据块缓冲区大小。9i 下的数据缓冲区可以用db_cache_size来直接分配。 

    (2)假设内存有1G，Oracle 的SGA可以考虑分配500M：共享池分配100M到150M，数据缓冲区分配300M到400M。 

    (3)内存2G，SGA可以考虑分配1.2G，共享池300M到500M，剩下的给数据块缓冲区。 

    (4)内存2G以上：共享池300M到500M就足够啦，再多也没有太大帮助；(Biti_rainy有专述)数据缓冲区是尽可能的大，但是一定要注意两个问题：一是要给操作系统和其他应用留够内存，二是对于32位的操作系统，Oracle的SGA有1.75G的限制。有的32位操作系统上可以突破这个限制，方法还请看Biti的大作吧。

二．分析表和索引，更改优化模式 

    Oracle默认优化模式是CHOOSE，在这种情况下，如果表没有经过分析，经常导致查询使用全表扫描，而不使用索引。这通常导致磁盘I/O太多，而导致查询很慢。如果没有使用执行计划稳定性，则应该把表和索引都分析一下，这样可能直接会使查询速度大幅提升。分析表命令可以用ANALYZE TABLE 分析索引可以用ANALYZE INDEX命令。对于少于100万的表，可以考虑分析整个表，对于很大的表，可以按百分比来分析，但是百分比不能过低，否则生成的统计信息可能不准确。可以通过DBA_TABLES的LAST_ANALYZED列来查看表是否经过分析或分析时间，索引可以通过DBA_INDEXES的LAST_ANALYZED列。 

    下面通过例子来说明分析前后的速度对比。（表CASE_GA_AJZLZ大约有35万数据，有主键）首先在SQLPLUS中打开自动查询执行计划功能。(第一次要执行\RDBMS\ADMIN\utlxplan.sql来创建PLAN_TABLE这个表) 

    SQL> SET AUTOTRACE ON
    SQL>SET TIMING ON

    通过SET AUTOTRACE ON 来查看语句的执行计划，通过SET TIMING ON 来查看语句运行时间。