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

 

要提高应用系统的性能，在维持现有硬件平台不变的情况下，主要采用的方法由以下几种：
　　l 调整操作系统参数，提高操作系统的管理性能；
　　l 调整数据库管理系统的参数，提高数据库管理系统的性能；
　　l 修改应用程序，提高应用程序的运行效率。
一、ORACLE数据库的内存结构
　　本文将着重对SGA的结构进行介绍：
　　SGA 通常又被称作 Shared Global Area,包括以下几个共享区域：
　　l 数据缓存（the Database buffer cache）
　　数据缓存存放从数据库文件中读取的数据，可分为剩余缓存（Free buffer）、正在使用的缓存（Pinned buffer）、已使用缓存（Dirty buffer）。
　　2 日志缓存（redo log buffer）
　　存放数据库已改变的信息。
　　3 共享存储池（shared pool）
　　共享存储池主要由三个方面构成:
　　Library cache
　　    Library cache 包括共享SQL区、用户自有SQL区、PL/SQL过程和程序包及控制结构；
　　Dictionary Cache
　　    存放数据库字典信息，如：表及视图名、列名及数据类型、各用户的存取权限；
　　Control structures
　　    请求及响应队列（request and response queues）（仅用于多线索模式）
　　    其他（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空间。
　　2 内存击中率
　　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运行时间较长