Oracle数据库优化设计

【摘要】电子计算机技术推动着人们生活快速的发展，在企业生产上越来越多的应用到计算机技术。计算机技术几乎涵盖了人们生活的方方面面，目前正使得我们的生活节奏变得更快、更便捷。本文则主要针对Oracle数据库相关问题进行研究，探讨Oracle数据库优化设计在企业中的应用情况以及如何对其进行优化作为主要讨论点，为企业在进行Oracle数据库维护和优化的时候提供借鉴。同时希望可以通过此篇文章实现对Oracle数据库优化方案进行总结，以便以后学习研究。

【关键词】计算机;数据库;Oracle;优化

一、前言

数据库是承载数据的载体，存放和提供数据的“库房”，为我们进行数据查询、修改、管理等操作提供便利。建立数据库可以帮我们提升工作效率，通常适合较为庞大的系统数据存储。

例如，国网新疆电力目前覆盖全疆14个地州（市），涉及用户达2000多万人口，管理40多个部门和下属单位。这么复杂的机构需要高效稳定的IT系统支撑国网新疆电力公司。国网新疆电力目前有多个IT系统，比较重要的系统有综合管理数据库、营销系统数据库、ODS系统、财务系统。这么多系统数据日增长量超过2TB，这需要有效的优化手段解决数据库的性能问题。

目前通用的方式为采用Oracle数据库来对这些数据进行存储管理，面对厂里人员的变动则需要进行数据更新，随着系统长期运行、用户数和数据量不断增大以及业务不断变化，系统运行期间就会涉及到数据库优化。本文就从Oracle数据库优化进行简单的讨论，针对在优化过程中的一些注意事项、优化事项进行分析，为我们在工作中能够熟练的掌握优化技术。

二、Oracle数据库介绍

Oracle数据库，英文全名为Oracle Data-base，又被称为Oracle RDBMS或者直接简称为Oracle。目前最流行的B/S和C/S架构的系统中均应用到了数据库，由于它们的架构设计中都具有自己的服务器，而数据存在这些服务器中，则需要数据库对其进行储存。目前对于数据库的使用越来越广泛，随着人们对于数据库的研究越来越深入，逐渐出现了数据库云，将计算机的云计算应用到了数据库之中，这样使得多个数据库联合组成了更加庞大的数据库网，它们之间实现了数据共享，因此对于知识、信息的涵盖将会变得更广。云计算的实现，带给计算机网络发展巨大的空间，使得将世界的计算机联合起来形成一层一层的网络，与此同时也将数据库采用云管理，为数据库的发展提供更加广阔的空间[1]。

三、数据库优化方案介绍

多数研究者在面对Oracle数据库优化课题时，都会存在这样的思想误区，即认为只有在系统出现运行问题时才需要进行系统性能调整。而事实上，对Oracle数据库的性能进行调整和优化是一个漫长而复杂的过程，是贯穿于整个系统运行周期的。因此，在进行系统性能优化时，应按照以下流程来进行：对系统各功能组件和硬件设备进行正确的配置对数据库结构进行调整对SQL语句进行优化调整参数进行磁盘I/O与服务器网络性能的调整。以上流程是一个密切联系的整体，只有保证严格按照这一流程进行Oracle数据库系统性能调整，才能确保系统的性能达到最佳状态，真正实现Oracle数据库的优化设计。

图1所示是进行数据库优化时需要考虑的内容。优化是数据库体系的延续，数据库的结构和运行的机制决定了数据库的优化模式，所以说数据库的体系结构是优化的基石。如果对数据库的体系结构有深刻的理解，优化便水到渠成。反过来，通过优化数据库，可以更深入的了解数据库体系结构。数据库各个方面都有优化的余地，主要的优化方向分为实例的优化、数据库的优化、SQL语句的优化。其中SQL优化是重中之重。

对Oracle数据库进行优化，应该遵循优化SQL查询语句――索引优化――合理分布数据库物理文件――分析及优化Oracle内存分配原则。具体如下：

（一）SQL查询优化

SQL查询，主要针对数据库的信息进行搜索，寻找自己的需求信息。数据库内的一切操作都是经由SQL语句进行执行，因此SQL语句的执行效率很大程度上决定了Oracle数据库的性能。进行SQL语句的优化，首先应该构建原始数据库BASICPROJECT，其中包含了与生产数据库基本一致的数据库对象;其次，应该充分的利用SQL Trace、awrsqrpt、sqlplus中的autotrace、explain等跟踪技术对语句进行优化重写 [4]。我们在建立SQL语句的时候要尽量的避免出现相关子查询，以及选择语句的使用，这样就能从数据建立的时候减轻查询的负担。针对联合查询连接遇到5张或者5张表单以上的选择时，建议采用优化器对SQL语句中所包含的表单进行物理大小排序，建立起一定的查询顺序，来提升查询的效率。

（二）索引优化分析

索引技术是提升检索速度和系统性能的主要技术，对于数据查询来说，合理使用索引可以极大的提高查询的命中率和效率。索引是将表中数据的逻辑值映射到rowid中，所以在查询时使用索引功能能够快速的定位出查询数据的物理地址，从而找出数据。

索引对数据库的性能影响是巨大的，但索引不是万能的，数据库对索引的使用是有选择的，我们可以强制使用索引，也可以强制不使用索引。一般的情况下数据库会自动的判断是否使用索引，除非你明确的在SQL语句中指定。

所有索引的原形都是树状结构，由根、枝干和叶子组成。根和枝干中存放键值范围的导引指针，叶子中存放的是条目，条目中存放的是索引的键值和该数据行ROWID。索引的叶子间通过指针横向的联系在一起，前一个叶子指向下一片叶子，这样的目的是数据库在找到一个叶子后就可以查找相临近的叶子，而不必再次去查找根和枝干的数据块。

有的DBA发现了索引并不能提高查询速度，反而对整个数据库的性能有较大的不良影响，出现该问题主要是和SGA数据管理方式有关。当Oracle进行数据高速缓存管理时，普通数据的驻留权限要比索引数据的权限要低，当两者在空间上竞争时，索引数据往往会驻留;如果是大型表建立索引时，索引数据占了大部分的缓存空间，使Oracle只能通过磁盘读写来获得数据，所以在大型表分区后，伴随索引也得进行相应分区，索引的使用应该有一个指定的合适路径[5]。

（三）分布表空间

在整个数据库工作过程中，各相关进程会将数据库中的事务分别写到联机日志文件、归档日志文件和数据文件当中，这会不可避免的造成这三类文件之间的I/O冲突;并且归档日志文件因其特殊性，无法同系统、业务和索引这些表空间共存，这就需要一个独立的磁盘来完成合理分布表空间的功能，对各项数据进行合理的分配，以避免文件之间的I/O冲突。

（四）数据缓冲区的调整分析

数据库的缓冲区是SGA不可缺少的组成部分，它的作用是对磁盘的读入数据进行存储，存储的数据为用户共享。如果需要修改数据时，首先要从数据文件中将数据读取出来存储在数据缓冲区;如果用户对数据缓冲区的设置太小，那么数据的操作性能将会受到很大的影响。用户越多，该问题越突出，该问题的出现使得很多人去关心如何判断数据缓冲区大小，如何确定缓冲区的效率，该类问题可以通过计算命中率来进行确认。

数据缓冲区V$sysstat中的consistent_gets、db_block_gets是consistent mode和current mode模式下的数据读取总量，physical reads是整个磁盘物理数据读取总量，这两个数据的读取总量的比值就是所谓的命中率，如果两个数据比值<90%，那么就需要对该缓冲区大小进行调整[2]。

（五）共享池调整分析

共享池同样也是SGA的重要组成部分，它主要包含了数据字典高速缓存与库高速缓存，这两者的作用是对整个SQL程序进行语法分析、编译以及执行。

库高速缓存中会将解析过的SQL语句、PL/SQL（存储过程、函数、包）进行缓存。如果为了工作的需要，将解析过的SQL信息重用会提高整个数据库的性能，可以将解析过的SQL信息存储在共享池中，这就需要共享池的设置要足够的大。通过对V$librarycache查询实例来观察整个库高速度缓存的活动情况，其中的reloads和pins，它们分别是库高速缓存执行阶段的未命中数目和库高速缓存中被执行的次数，如果库缓冲区的失败率超过的1%，那么就需要对其进行调整。除此之外，还有些情况也需要共享池的设置要大，字典数据高速缓存的总丢失数和总的存取数的比值应该接近零，当这个数值超过10%，那么就需要对其进行调整。如将表定义的详细信息长期的存储在共享池中，将其进行重用，提高数据库的整体性能[3]。

通过V$rowcache来对数据字典高速缓存活动进行详细查询，其中的get misses和gets分别代表的是字典数据读取的失败和成功次数，通常要求的比值小于10%，若超过则需要进行及时的调整。

（六）日志缓冲区优化方案

日志缓冲区主要是保存了对数据库修改信息，设置的大小一般为2兆以内的内存，最小为500K。日志缓冲区也不能过小，否则会增加日志的写盘次数，从而为I/O接口增加负担。日志缓冲区中的常见指令为：immediate gets表示成功立即得到日志缓冲区次数;immediate misses 则表示未成功立即获取日志缓冲区次数。V$latch中的gets和misses表示成功获得缓冲日志次数以及未成功获得日志缓冲区次数，其失败率要小于1%，如果超出则需要对数据库进行调整。

（七）合理的使用工具

有时候想直接在SQLPLUS中看ASH/ADDM/AWR报告，用下面方法比较方便，因为AWR数据在数据库中默认只保留7天，当我们进行性能对比分析需要保留时段之前的AWR时，可以采用脚本定时将AWR报告输出保存。

ASH （Active Session History）

ASH以V$SESSION为基础，每秒采样一次，记录活动会话等待的事件。不活动的会话不会采样，采样工作由新引入的后台进程MMNL来完成。

生成ASH报告：

SQLPLUS>@？/rdbms/ashrpt.sql

ASH内存记录数据始终是有限的，为了保存历史数据，引入了自动负载信息库（Autom-atic Workload Repository ，AWR） 由后台进程MMON完成。ASH信息同样被采集写出到AWR负载库中。由于内存不是足够的，所以MMNL进程在ASH写满后会将信息写出到AWR负载库中。ASH全部写出是不可接受的，所以一般只写入收集的10%的数据量，而且使用direct-path insert完成，尽量减少日志的生成，从而最小化数据库性能影响。

写出到AWR负载库的ASH信息记录在AWR的基础表wrh$active_session_hist中，wrh$active_session_hist是一个分区表，Oracle会自动进行数据清理。

AWR（Automatic Workload Repository）自动工作负载信息库

AWR是Oracle 10g中的一个新特性，类似于10g以前的statspack。不过在使用上要比statspack简单，提供的性能指标要比statspack多很多，能更好的帮助DBA来发现数据库的性能瓶颈。

AWR 是Oracle安装好后自动启动的，不需要特别的设置。收集的统计信息存储在SYSAUX表空间SYS模式下，以WRM$_*和WRH$_*的格式命名， 默认会保留最近7天收集的统计信息。每个小时将收集到的信息写到数据库中，这一系列操作是由一个叫MMON的进程来完成的。

AWR存储的数据分类：

WRM$表存储AWR的元数据（awrinfo.sql脚本）

WRH$表存储采样快照的历史数据（awrrpt.sql脚本）

WRI$表存储同数据库建议功能相关的数据（ADDM相关数据）

生成AWR报告：

SQL>@？/rdbms/admin/awrrpt

根据向导来完成AWR报告的生成。需要注意的是，在选择时间范围的时候，中间不能有停机（如果显示的中间有空白行，表示有停机情况）。在选择报告类型的时候一般使用默认的HTML，方便查看。

查看数据库的AWR的设置：

SQL> select snap_interval， retention from dba_hist_wr_control;

SNAP_INTERVAL

RETENTION

--------------------------------- ----------------------------------

+00000 01：00：00.0（每小时收集一次） +00007 00：00：00.0（保留7天）

修改默认设置：

begin

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS（interval => 20，

retention => 2*24*60）;

end;

修改成每20分钟收集一次统计量，保留最近的2天统计量信息。

手动收集一次数据库的统计信息：

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

我们还可以通过DBMS_WORKLOAD_REPOSITORY包完成对基线、默认设置的修改等操作。

ADDM （Automatic Database Diagnostic Monitor AWR）

是Oracle内部的一个顾问系统，能够自动的完成最数据库的一些优化的建议，给出SQL 的优化、索引的创建、统计量的收集等建议。

ADDM报告生成：

SQLPLUS>@？/rdbms/addmrpt.sql

Oracle 性能调整最重要的就是对最影响性能的SQL的调整。在一个应用中，能够影响到数据库的只有SQL，也只能是SQL。我们不能一味依靠增强硬件，修改系统、数据库参数来提高数据库的性能，更多的应该关注那些最影响性能的SQL语句。ASH报告、AWR报告、ADDM报告都是能够找出最影响性能的SQL语句的工具。 在分析ASH报告、AWR报告的时候，最重要的就是关注SQL Statistics，SQL Statistics中最应该关注的是SQL ordered by Gets和SQL ordered by Reads两个指标。大量的Gets（逻辑读）会占用大量的CPU时间，大量的Reads（物理读）会引起IO的瓶颈出现。一般情况下，大量的Gets会伴随着大量的Reads出现。当然，我们可以通过增大SGA的大小来减少Reads的量。通过这两个指标找到了最影响性能的SQL，这是首要的，也是必要的。下一步就可以通过创建索引，调整SQL来提高SQL单独执行的性能，减少SQL执行时出现的高Gets，Reads。当然整体的性能影响还和excutions有关，如果这条SQL执行的次数过多，累加起来的量很大，那么就可以考虑通过在应用上缓存等手段来减少SQL执行的次数。另外还有一个需要注意的问题就是在开发过程中SQL一定要使用绑定变量，来减少硬解析（大量的硬解析也会消耗大量的CPU时间，占用大量的Latch）。在开发过程中有个原则就是：小事务操作完成及时提交。

我们使用这么多种方式、报告只有一个目的：找出最影响系统性能的SQL语句。找到SQL下一步就是对它进行调整了。

我们在监控数据库时，如果是当前正在发生的问题，我们可以通过v$session+v$sqlarea来找出性能最差的SQL语句。如果在一个小时以内发生的我们可以通过生成ASH报告来找出SQL。如果是1小时以上或几天我们可以通过AWR报告来找出几小时，几天以来最影响系统的SQL语句。ADDM报告基于AWR库，默认可以保存30天的ADDM报告。

我们也可以直接查询试图：

v$session （当前正在发生）

v$session_wait （当前正在发生）

v$session_wait_history

（会话最近的10次等待事件）

v$active_session_history

（内存中的ASH采集信息，理论为1小时）

wrh$_active_session_history （写入AWR库中的ASH信息，理论为1小时以上）

dba_hist_active_sess_history （根据wrh$_active_session_history生成的视图）

四、总结语

企业中使用Orcale数据库来储存数据，有效的改善了以前对于企业信息数据处理的问题。有效的节约了信息处理资源，且采用Orcale数据库将所有的员工等信息进行备份，方便以后查询，若数据库的数据不丢失，那么则能够通过查询进行查询。本文主要针对Oracle数据库进行介绍，然后根据数据库中的内容进行优化，为以后企业在数据库的优化上提供借鉴。

参考文献

[1]温创新.电视广告商务平台中Oracle数据库的ADICI设计与优化研究[D].中南大学，2011.

[2]王斌.基于Oracle数据库技术的航行情报系统（CNMS）性能优化[D].电子科技大学，2010.

[3]张舒.超大型Oracle数据库的基础设计和优化设计[J].价值工程，2011，10：178.

[4]励文杰.大型数据库ORACLE数据库的优化设计方案[J].科技风，2011，19：145.

[5]覃艳.Oracle数据库高性能优化设计方法初探[J].电脑知识与技术，2012，23：5505-5507.

作者简介：张建业（1972―），男，浙江浦江人，1995年毕业于武汉水利电力大学计算机应用专业，高工，现供职于新疆电力公司，研究方向：信息化建设管理。