对Oracle数据库性能优化一点总结

    要提高应用系统的性能，在维持现有硬件平台不变的情况下，主要采用的方法由以下几种：

l        调整操作系统参数，提高操作系统的管理性能；

l        调整数据库管理系统的参数，提高数据库管理系统的性能；

l        修改应用程序，提高应用程序的运行效率。

一、ORACLE数据库的内存结构

本文将着重对SGA的结构进行介绍：

SGA 通常又被称作 Shared Global Area,包括以下几个共享区域：

l        数据缓存（the Database buffer cache）

数据缓存存放从数据库文件中读取的数据，可分为剩余缓存（Free buffer）、正在使用的缓存（Pinned buffer）、已使用缓存（Dirty buffer）。

l        日志缓存（redo log buffer）

存放数据库已改变的信息。

l        共享存储池（shared pool）

共享存储池主要由三个方面构成:

Library cache

Library cache 包括共享SQL区、用户自有SQL区、PL/SQL过程和程序包及控制结构；

Dictionary Cache

存放数据库字典信息，如：表及视图名、列名及数据类型、各用户的存取权限；

Control structures   

   l        请求及响应队列（request and response queues）（仅用于多线索模式）

l        其他（other miscellaneous information）

 

二、ORACLE 内存的工作机制

当用户请求被接受后，ORACLE 的内存分配将分以下几个步骤进行：

1．首先检查共享SQL区有无该SQL语句，如有在使用该SQL区执行用户的SQL语句（称作一次library cache hit），否则为该SQL语句分配共享SQL 区(称作一次library cache miss)，同时为该语句分配自有SQL 区。

2．检查Dictionary cache 中有无要访问的表/视图信息，若无则将其读入Dictionary cache 中(称作一次rowcache miss)。

3．检查数据缓存（Database buffer cache）,有无要操作的数据，如有则使用当前的缓存(称作一次 data buffer hit)，否则，将按下列步骤为该数据请求新的缓存(称作一次 data buffer miss)：

1） 搜索least-recently-used(LRU) list，若发现dirty buffer 则写入dirty list 并继续搜索，如发现free buffer则将其分配给该用户，同时将该buffer移至most-recently-used(MRU) list，若未能搜索到free buffer,则触发DBWR进程将一些dirty buffer 写入磁盘，并将这部分dirty buffer 释放为free buffer。

2） 从数据文件中将要操作的数据读入buffer cache 中；

4．如果用户执行的是INSERT、DELETE、UPDATE等操作，系统将为其分配redo log buffer,用于记录数据的变更情况，当redo log buffer中无free buffer时触发LGWR进程，将redo log buffer中的一些信息写如数据库的LOG FILE 中。

 

三、内存使用情况分析

要确定一个数据库管理系统中的内存配置的优劣，首先应掌握系统中当前内存的使用情况。ORACLE数据库为数据库管理员（DBA）提供了相应的查询方法，用于查询数据库的内存使用情况。要提高系统的性能，DBA 应重点检查以下几个指标：

l        剩余内存（free memory）

select * from v$sgastat where name=’free memory’;

一般来说，当您在数据库启动并投入使用相当长时间后，系统尚有剩余内存空间，说明您的数据库的SGA设置是足够的，无需增加SGA空间。

l        内存击中率

library cache 的击中率

   select sum(pins-reloads)/sum(pins) from v$librarycache;

数据字典的内存击中率

   select sum(gets-getmisses-usage-fixed)/sum(gets) from v$rowcache;

数据共享区的击中率

  select name,value from v$sysstat where name in (‘db block gets’,’consistent gets’,’physical reads’);

击中率=1-(physical reads)/(db block gets + consistent gets)

注意：以上几个指标均应在系统运行足够长时间后进行检查。

 

ORACLE 数据库的初始化参数文件通常存放在$ORACLE_HOME/dbs路径下,其文件名为init+数据库sid、后缀为.ora,如initorcl.ora。其中,影响数据库内存大小的参数主要有：

DB_BLOCK_SIZE：每个数据库块的字节数,在数据库建立时已确定,为1024的整数倍,如2048、4096；

DB_BLOCK_BUFFERS：数据库数据缓存区的数据块数；

SHARE_POOL_SIZE：共享存储区的字节数；

SORT_AREA_SIZE：数据排序区的字节数；

 

当剩余内存（free memory）过少、内存击中率过低，当library cache 的击中率或数据字典的内存击中率低于0.95时，考虑调整SHARED_POOL_SIZE，DB_BLOCK_BUFFER的大小。

此外，我们可以根据下面的算法，估算SHARED_POOL_SIZE 的大小:

SELECT SUM(value) FROM v$sesstat,v$statname

WHERE name=’session uga memory’

  AND v$sesstat.statistic#=v$statname.statistic#;

该查询返回的是目前所有用户进程所占用的SHARED_POOL 字节数；

SELECT SUM(value) FROM v$sesstat,v$statname

WHERE name=’session uga memory max’

  AND v$sesstat.statistic#=v$statname.statistic#;

该查询返回的是目前所有用户进程所需占用的SHARED_POOL 最大字节数；   我们可据此确定SHARED_POOL_SIZE 的初始大小。

 

四、SQL语句执行效率问题

1．检查占用CPU时间比较长的sql语句:

select sql_text,cpu_time from v$sql where cpu_time >1e7 order by cpu_time

2．执行效率最差的10条sql语句

SELECT * FROM  (   SELECT PARSING_USER_ID  EXECUTIONS   SORTS,
          COMMAND_TYPE, DISK_READS, sql_text  FROM  v$sqlarea
     ORDER BY disk_reads DESC)   WHERE ROWNUM<10 ;

3．利用V_$SQLAREA视图提供了执行的细节。（执行、读取磁盘和读取缓冲区的次数）

s    select SQL_TEXT,EXECUTIONS,DISK_READS,COMMAND_TYPE,OPTIMIZER_MODE, SHARABLE_MEM,BUFFER_GETS from v$sqlarea

•       

数 数据列

EXECUTIONS：执行次数

DISK_READS：读盘次数

COMMAND_TYPE：命令类型（3:select,2:insert;6:update;7delete;47:pl/sql程序单元）

OPTIMIZER_MODE：优化方式

SQL_TEXT：Sql语句

SHARABLE_MEM：占用shared pool的内存多少

BUFFER_GETS：读取缓冲区的次数

•        用途

1、帮忙找出性能较差的SQL语句

2、帮忙找出最高频率的SQL

3、帮忙分析是否需要索引或改善联接

4. 监控当前Oracle的session，如出现时钟的标志，表示此进程中的sql运行时间较长。