Oracle数据库系统-性能优化
Oracle数据库性能优化要提高应用系统的性能，在维持现有硬件平台不变的情况下，主要采用的方法由以下几种：调整操作系统参数，提高操作系统的管理性能；调整数据库管理系统的参数，提高数据库管理系统的性能；修改应用程序，提高应用程序的运行效率。一、ORACLE数据库的内存结构本文将着重对SGA的结构进行介绍：SGA 通常又被称作 Shared Global Area,包括以下几个共享区域：数据缓存（the Database buffer cache）数据缓存存放从数据库文件中读取的数据，可分为剩余缓存（Free buffer）、正在使用的缓存（Pinned buffer）、已使用缓存（Dirty buffer）。日志缓存（redo log buffer）存放数据库已改变的信息。共享存储池（shared pool）共享存储池主要由三个方面构成:Library cacheLibrary 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 应重点检查以下几个指标：剩余内存（free memory）select * from v$sgastat where name=free memory;一般来说，当您在数据库启动并投入使用相当长时间后，系统尚有剩余内存空间，说明您的数据库的SGA设置是足够的，无需增加SGA空间。内存击中率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$statnameWHERE name=session uga memory AND v$sesstat.statistic#=v$statname.statistic#;该查询返回的是目前所有用户进程所占用的SHARED_POOL 字节数；SELECT SUM(value) FROM v$sesstat,v$statnameWHERE 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_time2执行效率最差的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视图提供了执行的细节。（执行、读取磁盘和读取缓冲区的次数）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运行时间较长。
