系统中，shared_pool_size 的开销通常应该维持在300M 以内。除非系统使用了大量的存储过程、函数、包，比如oracle erp 这样的应用，可能会达到500M甚至更高。于是我们假定一个1G内存的系统，可能考虑设置该参数为100M，2G 的系统考虑设置为150M,8G 的系统可以考虑设置为200—300M。
对于一个没有充分使用或者没有使用绑定变量系统，这可能给我们带来一个严重的问题。所谓没有使用bind var 的SQL，我们称为Literal SQL。也就是比如这样的两句SQL我们认为是不同的SQL,需要进行2 次硬解析：
select * from EMP where name = ‘TOM’;
select * from EMP where name = ‘JERRY’;
假如把 ’TOM’ 和 ’JERRY’ 换做变量V，那就是使用了bind var，我们可以认为是同样的SQL 从而能很好地共享。共享SQL 本来就是shared_pool_size 这部分内存存在的本意，oracle的目的也在于此，而我们不使用bind var 就是违背了oracle 的初衷，这样将给我们的系统带来严重的问题。当然，如果通过在操作系统监控，没有发现严重的cpu问题，我们如果发现该共享池命中率不高可以适当的增加 shred_pool_size。但是通常我们不主张这部分内存超过800M（特殊情况下可以更大）。
事实上，可能的话我们甚至要想办法避免软分析，这在不同的程序语言中实现方式有差异。我们也可能通过设置session_cached_cursors 参数来获得帮助（这将增大PGA）
关于使用绑定变量的话题，在下面的应用优化中继续讨论。Data buffer
现在我们来谈数据缓冲区，在确定了SGA 的大小并分配完了前面部分的内存后，其余的，都分配给这部分内存。通常，在允许的情况下，我们都尝试使得这部分内存更大。这部分内存的作用主要是缓存 DB BLOCK，减少甚至避免从磁盘上获取数据，在8i中通常是由db_block_buffers*db_block_size 来决定大小的。如果我们设置了buffer_pool_keep 和buffer_pool_recycle，则应该加上后面这两部分内存的大小。 可以看出，设置SGA时基本上应该掌握的原则是：
data buffer 一般可以尽可能的大
shared_pool_size 应该适度
log buffer 在 1MB 以内就可以了 假定oracle是 32 bit ,服务器RAM大于2G ，注意你的PGA的情况，,则建议
shared_pool_size + data buffer +large_pool_size + java_pool_size < 1.6G 再具体化，如果512M RAM
建议 shared_pool_size = 50M, data buffer = 200M 如果1G RAM
shared_pool_size = 100M , data buffer = 500M 如果2G RAM
shared_pool_size = 150M ,data buffer = 1.2G 物理内存再大已经跟参数没有关系了 假定64 bit ORACLE
内存4G
shared_pool_size = 200M , data buffer = 2.5G 内存8G
shared_pool_size = 300M , data buffer = 5G 内存 12G
shared_pool_size = 300M-----800M , data buffer = 8G 1.3 32bit 与 64bit 对SGA的影响
为什么在上面SGA大小设置的经验规则中要分 32bit Oracle 和 64bit Oracle 呢，是因为这关系到SGA大小的上限问题。在32bit的数据库下，通常oracle只能使用不超过1.7G的内存，即使我们拥有12G的内存，但是我们却只能使用1.7G，这是一个莫大的遗憾。假如我们安装64bit的数据库,我们就可以使用很大的内存，几乎不可能达到上限。但是64bit 的数据库必须安装在64bit 的操作系统上，可惜目前windows上只能安装32bit的数据库，我们通过下面的方式可以查看数据库是 32bit 还是 64bit ：
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 – Production 在UNIX平台下的显示有所不同，明显可以看出是 64bit Oracle ，比如在HP-UX平台上：
SQL> select * from v$version; BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
PL/SQL Release 8.1.7.4.0 - Production
CORE 8.1.7.0.0 Production
TNS for HPUX: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 – Production 32bit的oracle无论跑在32bit或者64bit的平台都有SGA的限制的，而对于32bit的平台只能跑32bit的oracle，但是在特定的操作系统下，可能提供了一定的手段，使得我们可以使用超过1.7G 的内存，达到2G 以上甚至更多。由于我们现在一般都使用64bit Oracle，因此关于如何在32bit平台上扩展SGA大小的问题不再赘述。
1.4 9i中相关参数的变化
oracle的版本的更新，总是伴随着参数的变化，并且越来越趋向于使得参数的设置更简单，因为复杂的参数设置使得DBA们经常焦头烂额。关于内存这部分的变化，我们可以考察下面的参数。事实上在9i中数据库本身可以给出一组适合当前运行系统的SGA相关部分的参数调整值（参考V$ DB_CACHE_ADVICE、V$SHARED_POOL_ADVICE），关于PGA也有相关视图V$PGA_TARGET_ADVICE 等。
Data buffer
9i 中保留了8i中的参数，如设置了新的参数，则忽略旧的参数。9i中用db_cache_size来取代db_block_buffers ， 用db_keep_cache_size 取代buffer_pool_keep, 用db_recycle_cache_size 取代buffer_pool_recycle；这里要注意9i 中设置的是实际的缓存大小而不再是块的数量。另外9i新增加了db_nk_cache_size，这是为了支持在同一个数据库中使用不同的块大小而设置的。对于不同的表空间，可以定义不同的数据块的大小，而缓冲区的定义则依靠该参数的支持。其中n 可以为2、4、6、8、16 等不同的值。在这里顺便提及的一个参数就是db_block_lru_latches，该参数在9i中已经成为了保留参数，不推荐手工设置。 PGA
在9i 里面这部分也有了很大的变化。在独立模式下，9i已经不再主张使用原来的UGA相关的参数设置，而代之以新的参数。假如 workarea_size_policy=AUTO（缺省），则所有的会话的UGA 共用一大块内存，该内存由 pga_aggregate_target 设置。在我们根据前面介绍的方法评估了所有进程可能使用的最大PGA 内存之后，我们可以通过在初始化参数中设置这个参数，从而不再关心其他 ”*_area_size” 参数。 SGA_MAX_SIZE
在9i中若设置了SGA_MAX_SIZE，则在总和小于等于这个值内，可以动态的调整数据缓冲区和共享池的大小
SQL> show parameters sga_max_size
NAME TYPE VALUE
---------------- -------------------- ------- -------------
sga_max_size unknown 193752940
SQL>
SQL> alter system set db_cache_size = 30000000;
System altered.
SQL> alter system set shared_pool_size = 20480000;
System altered.
1.5 lock_sga = true 的问题
由于几乎所有的操作系统都支持虚拟内存，所以即使我们使用的内存小于物理内存，也不能避免操作系统将SGA 换到虚拟内存（SWAP）。所以我们可以尝试使得SGA 锁定在物理内存中不被换到虚拟内存中，这样减少页面的换入和换出，从而提高性能。但在这里遗憾的是，windows 是无法避免这种情况的。下面我们来参考在不同的几个系统下怎么实现lock_sga
AIX 5L（AIX 4.3.3 以上）
logon aix as root
cd /usr/samples/kernel
./vmtune (信息如下) v_pingshm已经是1
./vmtune -S 1
然后oracle用户修改initSID.ora 中 lock_sga = true
重新启动数据库
HP UNIX
Root身份登陆
Create the file "/etc/privgroup": vi /etc/privgroup
Add line "dba MLOCK" to file
As root, run the command "/etc/setprivgrp -f /etc/privgroup":
$/etc/setprivgrp -f /etc/privgroup
oracle用户修改initSID.ora中lock_sga=true
重新启动数据库 SOLARIS (solaris2.6以上)
8i版本以上数据库默认使用隐藏参数 use_ism = true ，自动锁定SGA于内存中,不用设置lock_sga, 如果设置 lock_sga =true 使用非 root 用户启动数据库将返回错误。 WINDOWS
不能设置lock_sga=true,可以通过设置pre_page_sga=true,使得数据库启动的时候就把所有内存页装载，这样可能起到一定的作用。
2. 应用优化
下面我们从技术的角度入手，来探讨数据库优化方面的问题。通常作为优化Oracle系统的人，或者是DBA，其实很多时候对应用并不很了解甚至可以说是完全不了解，更不要说对应用程序代码的了解。事实上呢，一个系统运行的快或者慢相信大家都明白，第一重要的是数据库的设计，然后是应用的设计， SQL语句的编写，最后才是数据库参数的调整和硬件、网络的问题，等等。所以在我们不了解一个系统的时候来优化数据库应用不是一个轻松的容易的事情。那么我们第一步应该怎么做呢？
通常有两类方法：
其中一个方法就是我们常用的，使用statspack来进行诊断系统的瓶颈所在。在statspack中oracle给出了几乎涵盖oracle大部分重要内容的信息。
另外一种方式，就是trace session。假如某个session运行很慢或者某个用户的某个查询很慢，那么这个时候我们可以通过trace session的方式来诊断到底是慢在哪里，看究竟执行计划是怎样的，然后在user_dump_dest下根据该session的进程号或者线程号可以找到一个产生的trace文件。通过使用tkprof格式化文件之后我们就可以看见很多的统计信息，这里包括了执行计划、parse/fetch等步骤消耗cpu的时间。通常我们是观察query模式下的consistent gets来首先看sql是否使用了索引，然后看执行计划是不是正常，是不是有调整的余地。当然如果您没有实际做过的话，这些内容说起来很抽象。这是在不了解应用和程序下针对特定session的诊断和调整过程。
trace session的方式是一种自下而上的方法，从sql入手；而statspack是自顶向下的方法，也就是从宏观上先诊断数据库的瓶颈在哪里，然后从瓶颈入手来做调整，这个习惯上又可以称为通过等待事件（wait event）入手的方法。
2.1 使用statspack
statspack是一个性能诊断工具，首先发布于Oracle8.1.6版本，在8.1.7版本中功能得到加强。Statspack除了查找实例中的性能问题外，还可以查找应用程序中高负荷的SQL语句，很容易确定Oracle 数据库的瓶颈所在，并且记录数据库性能状态。
在数据库中Statspack 的脚本位于$ORACLE_HOME/RDBMS/ADMIN 目录下，对于ORACLE8.1.6,是一组以stat 开头的文件；对于ORACLE8.1.7,是一组以sp 开头的文件。
在Statspack 发布之前，我们通常能够使用诊断数据库的工具是两个脚本UTLBSTAT.SQL 和UTLESTAT.SQL，BSTAT/ESTAT 是一个非常简单的性能诊断工具。UTLBSTAT 获得开始时很多V$视图的快照，UTLESTAT 通过先前的快照和当前视图生成一个报表。
该报表实际上相当于statspack 中的两个采样点。
Statspack 通过连续的采样，能够给我们提供至关重要的趋势分析数据。这是一个巨大的进步。能够使用Statspack 的环境我们就尽量不要使用BSTAT/ESTAT 的方式来诊断数据库问题。 2.1.1 安装statapack
§ 步骤一：
为了能够顺利安装和运行Statspack ，首先需要设置以下两个系统参数：
1. job_queue_processes
为了能够建立自动任务，执行数据收集，该参数需要大于0。你可以在初试化参数文件中修改该参数(使该参数在重起后以然有效)。
该参数可以在系统级动态修改(重起后失效)。 SQL> alter system set job_queue_processes = 6;
System altered 在Oracle9i 当中，可以指定范围，如 both,这样该修改在当前及之后保持有效(仅当你使用spfile 时，如果在9i 中仍然使用pfile，那么更改方法同8i 相同): SQL> alter system set job_queue_processes = 6 scope=both;
System altered
2. timed_statistics
收集操作系统的计时信息，这些信息可被用来显示时间等统计信息、优化数据库和 SQL 语句。要防止因从操作系统请求时间而引起的开销，请将该值设置为False。
使用statspack 收集统计信息时建议将该值设置为 TRUE，否则收集的统计信息大约只能起到10%的作用，将timed_statistics 设置为True 所带来的性能影响与好处相比是微不足道的。
该参数使收集的时间信息存储在在V$SESSTATS 和V$SYSSTATS 等动态性能视图中。
timed_statistics 参数也可以在实例级进行更改
SQL> alter system set timed_statistics = true;
System altered 如果你担心一直启用timed_statistics 对于性能的影响，你可以在使用statspack 之前在system 更改，采样过后把该参数动态修改成false。 § 步骤二：
需要单独为statspack创建一个存储数据的表空间，如果采样间隔较短，周期较长，打算长期使用，那么可能需要一个大一点的表空间，如果每个半个小时采样一次，连续采样一周，数据量是很大的。下面的例子中创建了一个500M 的测试表空间。
注意: 这里创建的表空间不能太小，如果太小的话创建对象会失败，建议至少建立100M 表空间。 SQL> create tablespace perfstat
2 datafile ''''/oracle/oradata/oradata/res/perfstat.dbf''''
3 size 500M;
Tablespace created。 § 步骤三：
在 sqlplus 中用internal 身份登陆，或者拥有SYSDBA(connect / as sysdba)权限的用户登陆。
注: 在Oracle9i 中，不存在internal 用户，可以使用sys 用户以sysdba 身份连接。
先转到$ORACLE_HOME/RDBMS/ADMIN 目录，检查安装脚本是否存在，同时我们执行脚本也可以方便些。 $ cd $ORACLE_HOME/rdbms/admin
$ ls -l sp*.sql
-rw-r--r-- 1 oracle other 1774 Feb 18 2000 spauto.sql
-rw-r--r-- 1 oracle other 62545 Jun 15 2000 spcpkg.sql
-rw-r--r-- 1 oracle other 877 Feb 18 2000 spcreate.sql
-rw-r--r-- 1 oracle other 31193 Jun 15 2000 spctab.sql
-rw-r--r-- 1 oracle other 6414 Jun 15 2000 spcusr.sql
-rw-r--r-- 1 oracle other 758 Jun 15 2000 spdrop.sql
-rw-r--r-- 1 oracle other 3615 Jun 15 2000 spdtab.sql
-rw-r--r-- 1 oracle other 1274 Jun 15 2000 spdusr.sql
-rw-r--r-- 1 oracle other 6760 Jun 15 2000 sppurge.sql
-rw-r--r-- 1 oracle other 71034 Jul 12 2000 spreport.sql
-rw-r--r-- 1 oracle other 2191 Jun 15 2000 sptrunc.sql
-rw-r--r-- 1 oracle other 30133 Jun 15 2000 spup816.sql
$ 接下来我们就可以开始安装Statspack 了。在Oracle8.1.6 版本中运行statscre.sql; 在Oracle8.1.7 版本中运行spcreate.sql。
这期间会提示你输入缺省表空间和临时表空间的位置,输入我们为 per 上一页 [1] [2] [3] [4] [5] [6] [7] 下一页 | 
