Oracle数据库系统-性能优化

Oracle数据库性能优化要提高应用系统的性能，在维持现有硬件平台不变的情况下，主要采用的方法由以下几种：调整操作系统参数，提高操作系统的管理性能；调整数据库管理系统的参数，提高数据库管理系统的性能；修改应用程序，提高应用程序的运行效率。一、ORACLE数据库的内存结构本文将着重对SGA的结构进行介绍：SGA通常又被称作SharedGlobalArea,包括以下几个共享区域：数据缓存（theDatabasebuffercache）数据缓存存放从数据库文件中读取的数据，可分为剩余缓存（Freebuffer）、正在使用的缓存（Pinnedbuffer）、已使用缓存（Dirtybuffer）。日志缓存（redologbuffer）存放数据库已改变的信息。共享存储池（sharedpool）共享存储池主要由三个方面构成:LibrarycacheLibrarycache包括共享SQL区、用户自有SQL区、PL/SQL过程和程序包及控制结构；DictionaryCache存放数据库字典信息，如：表及视图名、列名及数据类型、各用户的存取权限；Controlstructures

请求及响应队列（requestandresponsequeues）（仅用于多线索模式）其他（othermiscellaneousinformation）二、ORACLE内存的工作机制当用户请求被接受后，ORACLE的内存分配将分以下几个步骤进行：1．首先检查共享SQL区有无该SQL语句，如有在使用该SQL区执行用户的SQL语句（称作一次librarycachehit），否则为该SQL语句分配共享SQL区(称作一次librarycachemiss)，同时为该语句分配自有SQL区。2．检查Dictionarycache中有无要访问的表/视图信息，若无则将其读入Dictionarycache中(称作一次rowcachemiss)。3．检查数据缓存（Databasebuffercache）,有无要操作的数据，如有则使用当前的缓存(称作一次databufferhit)，否则，将按下列步骤为该数据请求新的缓存(称作一次databuffermiss)：1）搜索least-recently-used(LRU)list，若发现dirtybuffer则写入dirtylist并继续搜索，如发现freebuffer则将其分配给该用户，同时将该buffer移至most-recently-used(MRU)list，若未能搜索到freebuffer,则触发DBWR进程将一些dirtybuffer写入磁盘，并将这部分dirtybuffer释放为freebuffer。2）从数据文件中将要操作的数据读入buffercache中；4．如果用户执行的是INSERT、DELETE、UPDATE等操作，系统将为其分配redologbuffer,用于记录数据的变更情况，当redologbuffer中无freebuffer时触发LGWR进程，将redologbuffer中的一些信息写如数据库的LOGFILE中。三、内存使用情况分析要确定一个数据库管理系统中的内存配置的优劣，首先应掌握系统中当前内存的使用情况。ORACLE数据库为数据库管理员（DBA）提供了相应的查询方法，用于查询数据库的内存使用情况。要提高系统的性能，DBA应重点检查以下几个指标：剩余内存（freememory）select*fromv$sgastatwherename=’freememory’;一般来说，当您在数据库启动并投入使用相当长时间后，系统尚有剩余内存空间，说明您的数据库的SGA设置是足够的，无需增加SGA空间。内存击中率librarycache的击中率selectsum(pins-reloads)/sum(pins)fromv$librarycache;数据字典的内存击中率

selectsum(gets-getmisses-usage-fixed)/sum(gets)fromv$rowcache;数据共享区的击中率

selectname,valuefromv$sysstatwherenamein(‘dbblockgets’,’consistentgets’,’physicalreads’);击中率=1-(physicalreads)/(dbblockgets+consistentgets)注意：以上几个指标均应在系统运行足够长时间后进行检查。ORACLE数据库的初始化参数文件通常存放在$ORACLE_HOME/dbs路径下,其文件名为init+数据库sid、后缀为.ora,如initorcl.ora。其中,影响数据库内存大小的参数主要有：DB_BLOCK_SIZE：每个数据库块的字节数,在数据库建立时已确定,为1024的整数倍,如2048、4096；DB_BLOCK_BUFFERS：数据库数据缓存区的数据块数；SHARE_POOL_SIZE：共享存储区的字节数；SORT_AREA_SIZE：数据排序区的字节数；当剩余内存（freememory）过少、内存击中率过低，当librarycache的击中率或数据字典的内存击中率低于0.95时，考虑调整SHARED_POOL_SIZE，DB_BLOCK_BUFFER的大小。此外，我们可以根据下面的算法，估算SHARED_POOL_SIZE的大小:SELECTSUM(value)FROMv$sesstat,v$statnameWHEREname=’sessionugamemory’

ANDv$sesstat.statistic#=v$statname.statistic#;该查询返回的是目前所有用户进程所占用的SHARED_POOL字节数；SELECTSUM(value)FROMv$sesstat,v$statnameWHEREname=’sessionugamemorymax’

ANDv$sesstat.statistic#=v$statname.statistic#;该查询返回的是目前所有用户进程所需占用的SHARED_POOL最大字节数；

我们可据此确定SHARED_POOL_SIZE的初始大小。四、SQL语句执行效率问题1．检查占用CPU时间比较长的sql语句:selectsql_text,cpu_timefromv$sqlwherecpu_time>1e7orderbycpu_time2．执行效率最差的10条sql语句SELECT*FROM

(

SELECTPARSING_USER_ID

EXECUTIONS

SORTS,

COMMAND_TYPE,DISK_READS,sql_text

FROM

v$sqlarea

ORDERBYdisk_readsDESC)

WHEREROWNUM<10;3．利用V_$SQLAREA视图提供了执行的细节。（执行、读取磁盘和读取缓冲区的次数）selectSQL_TEXT,EXECUTIONS,DISK_READS,COMMAND_TYPE,OPTIMIZER_MODE,SHARABLE_MEM,BUFFER_GETSfromv$sqlarea数据列EXECUTIONS：执行次数DISK_READS：读盘次数COMMAND_TYPE：命令类型（3:select,2:insert;6:update;7d