借花敬佛  数据库优化内容 
1. ORACLE系统的准备知识 
1) ORACLE数据库系统的数据存储的物理结构和逻辑结构构成 
2) 模式对象的组成 
3) ORACLE数据库系统的进程以及内存结构构成 
4) ORACLE锁的概念介绍 
5) 二阶段提交的概念 
6) 用户、角色、权限的概念的介绍 
7) 举例介绍ORACLE是如何处理一个事务 
a 首先必须有一台主机或数据库服务器运行一个ORACLE INSTANCE。 
b 一台本地机器或客户端工作站运行一个应用,它试图通过适当的SQLNET驱动同服务器取得联系。 
c 如果该服务器也正在运行适当的SQLNET驱动。服务器检测到应用的连接请求,开始为此用户进程创建一个专用的服务器进程。 
d 客户端的用户执行一个SQL语句并提交此进程。 
e 服务器进程收到此SQL语句,并开始检验在ORACLE的共享池中是否存在同样的SQL语句。如果在共享池中发现该SQL语句,服务器进程开始检验该用户是否对请求的数据有操作的权限,然后使用在共享池中的SQL语句去执行该语句。如果该SQL语句在共享池中不存在,就为此语句分配一个新的共享池区以便它能够被解析、执行。 
f 服务器进程从实际的数据文件或共享池中取回必须的数据。 
g 服务器进程在在共享池中修改数据。在上述所作的生效之后,DBWR后台进程把修改后的数据块永久的写入硬盘。在此事务提交成功之后,LGWR进程立即把此事务记录到在线的redo log file。 
h 如果此事务成功,服务器进程通过网络返回一个成功的信息给应用程序。如果该事务不成功,将返回一个适当的信息。 
i 在上述的事务过程中,其余的后台进程同样在运行,等待着条件符合而被触发。此外,数据库服务器还管理着其他用户的事务,并且在不同事务之间提供数据一致性,防止不同事务对相同数据操作。 
2. 在安装数据库时作的优化 
在数据库安装时作的优化工作主要是关于DB_BLOCK_SIZE参数的设置,该参数决定了ORACLE每次操作多少的数据。该参数在安装时一经确认就不能修改,除非重新安装数 据库。对于一个应用而言,一般对于一个中型的应用系统,它的DB_BLOCK_SIZE大小为设为4K,而对于一个较大型的应用而言,它的DB_BLOCK_SIZE一般设为8K或更大一点为16K。 
对于一个较大的DB_BLOCK_SIZE,不仅可以加快系统的运行速度,(因为从系统的I/O吞吐能力来说,一次性读取较多的数据可以比一次性读取较少的数据的的过程减少I/O的读取次数)而且可以有较大的系统扩展能力。因为对于一个系统而言,在它的DB_BLOCK_SIZE确认之后,它的最大EXTENT的数目其实也已经确认下来。如果一个系统的扩展能力有限的话,则系统容易发生显示终止的事情。而就是说,发生ORA错误,导致系统无法正常运转。截止至目前,在ORACLE7.3之后的版本中,ORACLE在建表空间时,有一个参数autoextent,如果此参数设置为ON时,ORACLE在达到最大的扩展值时,ORACLE就自动扩展,不再受最大扩展数的限制。现就把DB_BLOCK_SIZE和MAX EXTENTS的关系罗列如下: 
DB_BLOCK_SIZE(数据块数目) MAX EXTENTS(最大扩展数) 
512BYTES 25 
1K 57 
2K 121 
4K 249 
8K 505 3. 在安装之后,在数据库初始化时对INITXXX.ORA文件作的优化 
对于SHARE_POOL_SIZE的设置:对于不同的系统根据用户对于内存区的要求,考虑用户是否需要多少的内存空间存放用户的存储过程或要多少空间存放用户要编译的程序。 
对于需要进行大量数据操作的用户可考虑增大用户的DB_BLOCK_BUFFERS的数目,该参数可以使用户在缓冲区中的数据较大,使用户查找的数据尽可能的在缓冲区中,不要到表中去再次查找。 
根据用户的实际需要,设置较好的PROCESS该参数决定能够有多少个用户在系统中运行,如果该参数设置不当会导致用户无法正常运行。并且该参数与操作系统的有些参数(如Digital unix的max_proc_per_users)有关,该类型的参数限制了每个用户允许最大多少用户登录的限制,因为对于我们而言,每一个用户最终都体现为一个ORACLE用户,如果此参数开的不够大的话,则会造成后登录的用户无法登录,应用终止。 
根据用户实际使用系统的SQL语句的多少,决定最终要开的OPEN_CURSORS数目的多少,因为一个SQL的DML语句就是一个隐含的CURSOR,如果上述参数的数目开的不够大的话,系统会提示用户的SGA区不足,导致系统出错。 
对于要进行大量数据分组和排序工作的应用要加大系统的SORT_AREA_SIZE的大小,该参数决定分配给每一位用户的排序空间,该参数用到系统的内存空间。 
为保证系统能够正常运转,要保证系统有足够的DML_LOCKS,如果该值不够的话,会导致系统发生中断,半途终止系统。 
为保证系统能够有足够的数据库链路可用,要保证OPEN_LINKS的数目足够大。 
对于会发生CORE DUMP的用户的机器,可考虑设置使该CORE DUMP最终不要形成文件,(在ORACLE的init参数中为shadow_core_dump=none,默认为full)。因为系统在许多时候由于文件系统满的缘故,导致系统无法正常运转,最终会导致数据库系统崩溃。 
4. 在进行空间设计时作的优化 
在一个数据库安装完毕之后,系统中已存在如下表空间,它们分别是:SYSTEM,TOOLS,RBS,USERS,TEMP等,上述表空间在安装时用户可根据当地的系统的实际情况进行系统表空间的划分,使它们尽可能分离。 
在系统安装时,还应该考虑控制文件和可重作日志文件要尽可能的分配在不经常使用的盘上。 
表空间设计的原则为:把由用户创建的其余表空间同SYSTEM表空间进行分离,把系统的数据表空间同索引表空间分离,把操作频繁和不经常操作的表划分在不同的表空间中。对于表空间的设计来说,大体上又可细划分为以下原则: 
把用户数据与数据字典数据分开。 
把不同应用组的数据分别放在不同的表空间中。 
为了减少I/O的竞争,把不同表空间的数据文件放在不同的硬盘上。 
分离用户数据和回滚段的数据以防止某个磁盘出现故障丢失数据。 
控制数据的可用性,例如使一些表空间联机(online)而另一些表空间脱机(offline)。 
为特殊类型的数据库使用保留某个表空间。例如,高频率的更新操作,只读操作或临时段的存储等。 
在上述原则中,对日常系统最有用的原则是把索引空间同数据表空间分离开来。把它们分离出来的理由如下:与表相关联的索引会导致I/O和表增长/碎片问题的出现。碎片问题同样要求将数据段从系统表空间中分离出来。同时对索引的数据操纵和查询时将会由大量的输入/输出。索引段同样因为不恰当的大小或无法预料的表增长问题而导致碎片。将应用索引分离到一个单独的表空间中,将大大减少管理数据空间和索引空间碎片问题的工作量。 
在数据库的表空间设计时,建议每一个表空间对应的数据物理文件的大小应该小于1G大小,因为许多系统的操作系统不支持大于2G大小的文件。同时,从数据库的备份角度考虑,对小文件的备份不仅可以提高备份的速度,也可以提高备份的安全性。 
对表空间建立的时候,有一参数应该注意,它就是在建立TABLESPACE时用到的DEFAULT STORAGE,该参数决定了今后建表或建索引时默认的存储策略(也就是说如果没有修改建表或建索引的参数时,系统就用该默认参数)。 
由于创建表空间要用到数据文件,如果一个系统很大的话,就需要许多的数据文件。而该数据文件的个数会受到初始化参数或操作系统的限制,因此在创建表空间时,应该考虑系统的限制,选择有效的参数。对于一个ORACLE数据库而言,它的最大数目受如下三个方面限制,它们分别是:操作系统能够打开的最大文件数目,db_files的数目,建立数据库时的参数maxdatafiles数目。在上述参数中,以操作系统的限制为主,其次为maxdataflies,最后为db_files。也就是说如果操作系统允许打开的文件数为32,而maxdatafiles设置为48的话,系统认为打开的文件数目最大为32,如果操作系统允许最大的打开文件数目为64,maxdatafiles数目为32,db_files数目为48,那么系统允许打开的文件最大数目为32,而不是48。 
在进行临时表空间的设计时,应该考虑在实际的日常应用中会引起临时表空间扩展的语句,因为临时表空间的扩展同样会导致应用的终止。在我们日常的应用中如下DDL或DML语句会引起临时表空间的扩展: 
创建索引:create index 
在日常的DML语句中存在如下关键字会引起临时表空间扩展:SELECT…DISTINCT,ORDER BY,GROUP BY,UNION,INTERSECT,MINUS 
没有索引的连接:none-indexed joins 
包含关联的子查询:certain correlated subqueries  

解决方案 »

  1.   

    再转贴一篇原作者:Bert Scalzo  目前,HP,Compaq,Dell,IBM 以及 Oracle 都在加快速度拥抱 Linux ,这个开放源码的操作系统。根据 eWeek 的统计,去年 Linux 服务器的销售量大约占据了 Compaq 的 30%,Dell 的 13.7%,IBM 的 13.5%。而且 IBM 2001年度在 Linux 上的投入有 10 个亿。 Intel 最新的 64 位的 Itanium CPU 只支持四种操作系统:Windows, Linux, AIX 和 HP-UX。我们也不要忘记 Oracle 的 9i 数据库 Linux 版本要比 Windows 版本早一个月。  尽管 Linux 能跑在从 IBM S/390 到 Sun SPARC 结构的服务器,但是对于大多数人来说,Intel 还是 Linux 跑得最多的平台。本文就是要讲述通过简单的性能调正,使 Oracle 的性能提升 1000% 的办法。  本文采用的测试环境是一台 Compaq 4 CPU,512 MB ,8 部 7200 rpm SCSI 磁盘的服务器,然后在几乎同样的单 CPU Athlon 系统上作了测试,内存一样,但是只有一部 7200 rpm 的 Ultra 100 IDE 磁盘。尽管最后的结果和得到的百分比不一样,但是观测得到的性能提升是一致的。  为了简单起见,我们的测试环境采用 TPC 基准测试,它广泛地用于 OLTP 的负荷测试。Quest 公司有一个叫做 Bench Factory 的工具,使测试工作变得就像发送电子邮件一样简单。  下面我们将分别通过 DB 的调整和 OS 的调整来看测试的结果。  DB1 的初始化参数一般不常见,为了说明问题,我们使用这些参数并作为基准。  DB1: Initial Database   Database Block Size 2K   SGA Buffer Cache 64M   SGA Shared Pool 64M   SGA Redo Cache 4M   Redo Log Files 4M   Tablespaces Dictionary   TPC Results Load Time (Seconds) 49.41   Transactions / Second 8.152   显然需要加大 SGA 大小,我们来看 DB2 的结果:  DB2: Cache & Pool   Database Block Size 2K   SGA Buffer Cache 128M   SGA Shared Pool 128M   SGA Redo Cache 4M   Redo Log Files 4M   Tablespaces Dictionary   TPC Results Load Time (Seconds) 48.57   Transactions / Second 9.147   增大 SGA 已经缓冲看来对于性能的提升并不显著,加载时间只提升了 1.73%。下面我们增加 SGA 重做日志的大小:  DB3: Log Buffer   Database Block Size 2K   SGA Buffer Cache 128M   SGA Shared Pool 128M   SGA Redo Cache 16M   Redo Log Files 16M   Tablespaces Dictionary   TPC Results Load Time (Seconds) 41.39   Transactions / Second 10.088   我们可以看到加载时间提升了 17.35%,TPS 也提升了 9.33%。因为加载和同时插入,更新,删除需要比 8M 大的空间,但是看起来增加内存性能并没有显著提升,我们加大块大小:  DB4: 4K Block   Database Block Size 4K   SGA Buffer Cache 128M   SGA Shared Pool 128M   SGA Redo Cache 16M   Redo Log Files 16M   Tablespaces Dictionary   TPC Results Load Time (Seconds) 17.35   Transactions / Second 10.179   我们看到加载时间提升了 138%!而对 TPS 值没有很大的影响。下面一个简单的念头是表空间的管理从目录切换为本地:  DB5: Local Tablespaces   Database Block Size 4K   SGA Buffer Cache 128M   SGA Shared Pool 128M   SGA Redo Cache 16M   Redo Log Files 16M   Tablespaces Local   TPC Results Load Time (Seconds) 15.07   Transactions / Second 10.425   下面我们把数据库块加大到 8K 来看结果:  DB6: 8K Block   Database Block Size 8K   SGA Buffer Cache 128M   SGA Shared Pool 128M   SGA Redo Cache 16M   Redo Log Files 16M   Tablespaces Local   TPC Results Load Time (Seconds) 11.42   Transactions / Second 10.683   看来结果并不坏,我们没有理由继续增加块大小了,我们还没有根据 CPU 个数调整相应的参数,这次我们设置 I/O 的进程数来继续调整:  DB7: I/O Slaves   Database Block Size 8K   SGA Buffer Cache 128M   SGA Shared Pool 128M   SGA Redo Cache 16M   Redo Log Files 16M   Tablespaces Local   dbwr_io_slaves 4   lgwr_io_slaves (derived) 4   TPC Results   Load Time (Seconds) 10.48   Transactions / Second 10.717   我们的测试是基于 Red Hat 6.2 进行的,内核版本为 2.2.14-5smp。对于 Linux 的内核而言,有将近几百个参数可以调整,包括对 CPU 类型,SMP 支持,APIC 支持,DMA 支持,IDE DMA 缺省参数的使用以磁盘限额支持。根据 Oracle 的文档,我们要做的主要调整是共享内存和信号量的大小,SHMMAX 最少配置 0x13000000,SEMMNI, SEMMSL 和 SEMOPN 分别至少设置 100, 512, 100。这些参数的设置可以通过下面的命令实现:  # echo 0x13000000 >/proc/sys/kernel/shmmax   # echo 512 32000 100 100 >/proc/sys/kernel/sem   OS1: 单内核和 IPC   TPC Results   Load Time (Seconds) 9.54   Transactions / Second 11.511   我们有理由相信采用新的内核版本(2.2.16-3smp)也应该有性能的提升:   OS2: Newer minor version kernel TPC Results   Load Time (Seconds) 9.40   Transactions / Second 11.522   目前已经有 2.4 版本的内核,和 2.2 相比,性能上有了很大的提升,我们采用 2.4.1smp:   OS3: Newer major version kernel TPC Results   Load Time (Seconds) 8.32   Transactions / Second 12.815   Linux 缺省读操作时更新最后一次读的时间,但是这个对我们来说并不重要,因此我们关闭这个选项,通过设置 noatime 的文件属性来实现。(对于 Win NT 和 2000 有相似的设置)   如果只是相对 Oracle 的数据文件设置,我们的命令是   chattr +A file_name  对整个目录的实施办法:chattr -R +A directory_name   最好的办法是修改 /etc/fstab ,针对每个文件系统入口,添加 noatime 关键字。   OS4: noatime file attribute   TPC Results   Load Time (Seconds) 5.58   Transactions / Second 13.884   另外一个调整 Linux I/O 的办法是虚拟内存子系统的调整,修改 /ect/sysctl.cong 文件,增加下面一行:   vm.bdflush = 100 1200 128 512 15 5000 500 1884 2   根据 /usr/src/Linux/Documentation/sysctl/vm.txt 的说法:   第一个参数100 %:控制缓冲区中最大的脏缓冲数据,增加这个值意味着 Linux 可以延迟磁盘写。   第二个参数 1200 ndirty:给出 bdflush 一次能够写入磁盘的最大脏缓冲。   第三个参数 128 nrefill:当调用 refill_freelist() 时,bdflush 添加到自由缓冲区中的最大缓冲数目。   refill_freelist() 512:当这个数目超过 nref_dirt 脏缓冲时,将唤醒 bdflush。   第五个 15 和最后两个参数 1884 和 2,系统未使用,我们不做修改。   age_buffer 50*HZ, age_super 参数 5*HZ:控制 Linux 把脏缓冲写到磁盘的最多等待时间。数值用时钟滴答数(jiffies)表示,每秒为 100 个 jiffies 。   OS5: bdflush settings TPC Results   Load Time (Seconds) 4.43   Transactions / Second 14.988   经过以上一系列调整后,我们得到的最终加载时间减少了 1015.35%,TPS 增加了 45.61%。。(FreeLAMP:徐永久 提供本文)
      

  2.   

    不过最好还是找一些书看看,如机工出的《Oracle8i性能优化与管理手册》
      

  3.   

    还应该考虑I/OF方面的问题,当多个进程都要存取数据的时候,可能出现
    cpu较为空闲,而I/O很忙,造成进程处于等待的状态,这个时候就需要调整
    操作系统的I/O了,具体的情况在oracle的网站上有说明。
      

  4.   

    多谢各位捧场,我想要的是从各个方面入手定位数据库性能问题的方法以及详细的指导。
    例如:如果CPU占用很高,我可以从操作系统的命令入手,例如HP-UX的top或者是IBM-AIX的topas命令查出最耗CPU的进程,进而找到其执行的SQL语句或者存储过程,然后进行优化。
    等等,这类优化指导。