ORACLE数据库性能优化之--)内存磁盘
1，内存结构优化概述
1.1 缓冲区
影响数据库运行性能的缓冲区包括可以共享的SGA和服务器进程私有的pga两大类，其中sga又包括共享池、大型池、java池、数据缓冲区、流池、redo log缓冲区。
1.2 自动内存管理
oracle一般采用自动内存管理来管理系统内存，由oracle自动管理和调整数据库实例的内存大小。在自动管理模式下，首先对初始化参数MEMORY_TARGET（目标内存大小）和MEMORY_MAX_TARGET（最大内存大小）进行配置，数据库调整目标内存大小，根据需要不断重新调整sga和pga的缓冲区大小。
1.3 共享内存sga的自动管理
如果数据库实例启动时候动态将初始化参数SGA_TARGET设置为0，则自动共享内存管理被禁止使用。每个缓冲区的大小由初始化参数文件中的相应参数来决定。如果有必要，可以通过手动设置初始化参数DB_CACHE_SIZE,SHARED_POOL_SIZE,LARGE_POOL_SIZE,JAVA_POOL_SIZE,STREAMS_POOL_SIZE,来调整共享内存区各个缓冲区的大小。一般通常有一些内存组件大小需要管理员手动设置，不受自动内存管理的影响，比如重做日志缓冲区；KEEP、RECYCLE以及基于非标准数据块的缓冲区；固定的SGA和其他内部分配的内存区。手动调整这些内存区时候，需要设置的初始化参数有DB_KEEP_CACHE_SIZE,DB_RECYCLE_CACHE_SIZE,DB_nK_CACHE_SIZE,LOG_BUFFER，这些内存区的分配都是从SGA_TARGET指定内存中获取，SGA_TARGET的余下部分供自动共享内存管理的各个缓冲区使用。
1.4 动态改变缓冲区大小
如果系统没有采用自动内存管理或者自动内存共享内存管理功能，可以使用ALTER SYSTEM语句配置初始化参数DB_ACHE_SIZE,JAVA_POOL_SIZE,LARGE_POOL_SIZE,LOG_BUFFER,SHARED_POOL_SIZE等。
2，数据缓冲区调整
通过下列两种方式检查数据缓冲区设置是否合理：
查看数据库缓冲区的命中率，执行命令：
select 1 - ((physical.value - direct.value -lobs.value) / logical.value)
"Buffer Cache Hit Ratio"
from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstatlogical
where physical.name = 'physical reads'
anddirect.name='physical reads direct'
andlobs.name='physical reads direct (lob)'
andlogical.name='session logical reads';
运行结果：
SQL> select 1 - ((physical.value - direct.value - lobs.value) /logical.value)
2 "Buffer Cache HitRatio"
3 from v$sysstatphysical,v$sysstat direct,v$sysstat lobs,v$sysstat logical
4 where physical.name ='physical reads'
5 and direct.name='physicalreads direct'
6 and lobs.name='physical readsdirect (lob)'
7 and logical.name='sessionlogical reads';
Buffer Cache Hit Ratio
----------------------
0.958117758479709
SQL>
惯例来说，上述语句当>0.9实说明调整充分的。命中率是高的，这里已经为0.95，所以命中率还是比较可观的。对于数据库系统而言，数据缓冲区中包括的DEFAULT缓冲池就足够应用需要了。
查询数据缓冲区中每个缓冲区的逻辑读和物理读的信息，可以查询动态性能视图v$buffer_pool_statistics，例如：
SQL> selectt1.name,t1.PHYSICAL_READS,t1.DB_BLOCK_GETS,t1.CONSISTENT_GETS,1-(t1.PHYSICAL_READS/(t1.DB_BLOCK_GETS+t1.CONSISTENT_GETS))"Hit Ratio"
2 from v$buffer_pool_statisticst1
3 ;
NAME PHYSICAL_READS DB_BLOCK_GETSCONSISTENT_GETS Hit Ratio
-------------------- --------------------------- --------------- ----------
DEFAULT 44480354387 571096618 33595740186 -0.3018575
SQL>
调整数据缓冲区大小
如果数据缓冲区小于90%，就要考虑调整数据缓冲区大小。增大数据缓冲区之前，先看看V$DB_CACHE_SIZE（select * from v$db_cache_advice），确定数据缓冲区增大到多少能够显著降低物理I/O的数量，然后动态修改初始化参数DB_CACHE_SIZE。初始化参数DB_CACHE_SIZE对应的是标准数据缓冲区大小，如果要修改非标准的数据缓冲区大小，需要修改初始化参数DB_nK_CACHE_SIZE(n取值为2、4、8、16、32，且n不能是标准数据块大小)
3，共享池调整
3.1 库缓存命中率
可以通过查询动态性能视图v$librarycache（select * from v$librarycache;），该视图保存了数据库最近一次启动以来库缓存活动的统计信息，每一行记录反映了库缓存中一个条目类型的统计信息，通过namespace列值来识别每个条目类型。例如：
selectt1.NAMESPACE,t1.PINS,t1.PINHITS,t1.RELOADS,t1.INVALIDATIONS from v$librarycachet1 order by t1.NAMESPACE;
SQL> selectt1.NAMESPACE,t1.PINS,t1.PINHITS,t1.RELOADS,t1.INVALIDATIONS from v$librarycachet1 order by t1.NAMESPACE;
NAMESPACE PINS PINHITS RELOADS INVALIDATIONS
-------------------------------------------------------------------------- ---------- ---------- -------------
APP CONTEXT 2 1 0 0
BODY 28468616 28458242 1818 46
CLUSTER 114902 114198 0 0
DBINSTANCE 0 0 0 0
DBLINK 0 0 0 0
DIRECTORY 371 81 0 0
EDITION 10577860 10577856 0 0
INDEX 403451 315690 11194 0
JAVA DATA 3161 1876 0 0
JAVA RESOURCE 3413 2124 0 0
JAVA SOURCE 3413 2121 0 0
OBJECT ID 0 0 0 0
PIPE 2940 2936 0 0
QUEUE 755296 754776 218 0
RULE 732 365 82 0
RULESET 18358 17693 26 0
SCHEMA 0 0 0 0
SQL AREA 1252576540 1223849814 1430167 926624
SUBSCRIPTION 50182 49948 6 0
TABLE/PROCEDURE 162038767 161228629 429539 263
NAMESPACE PINS PINHITS RELOADS INVALIDATIONS
-------------------------------------------------------------------------- ---------- ---------- -------------
TEMPORARY INDEX 25602 0 7514 0
TEMPORARY TABLE 70046 0 55020 0
TRIGGER 12753767 12747803 1146 0
XDB CONFIG 1 0 0 0
XML SCHEMA 27 6 4 0
25 rows selected
SQL>
PINS：针对特定NAMESPACE的对象请求次数
PINHITS：针对特定NAMESPACE的对象请求次数，在库缓存中存在的次数
RELOADS：需要从磁盘中加载对象的次数
INVALIDATIONS：针对特定的namespace，由于依赖对象的改变而被标识为失败的对象的次数。
库缓存命中率的计算公式(sum(t1.PINHITS)/sum(pins)计算)，例如：
SQL> select sum(t1.PINHITS)/sum(pins)"Lib Ratio" from v$librarycache t1;
LibRatio
----------
0.97973692
SQL>
也可以查询动态性能视图v$sgastat来获取共享池空闲内存的数量。例如：
SQL> select t1.* from v$sgastat t1 wheret1.NAME='free money' and pool='shared pool';
POOL NAME BYTES
------------ ------------------------------------
SQL>
看到awr报表中
3.2 数据字典缓存命中率
通常，如果共享池中库缓存空间充足，那么数据字典缓存也会充足，在某些情况下，数据缓会失败，比如在数据库实例刚启动的时候，数据库字典缓存不包含任何数据，任何sql语句的执行都会导致数据字典缓存失败，而随着数据缓存字典的增多，缓存失败将较少，最后将达到一个稳定的命中率，此时数据字典缓存失败率将非常低。
可以通过查询数据库动态性能视图v$rowcache（select * from v$rowcache;）获取不同类型数据字典条目的统计信息。这里反应了从最近一次数据库启动以来数据字典的使用情况。
SQL> selectt1.PARAMETER,sum(t1.GETS),sum(t1.GETMISSES),100*sum(t1.GETS-t1.GETMISSES)/sum(t1.GETS)pct_succ_gets,sum(t1.MODIFICATIONS)updates from v$rowcache t1 where t1.GETS!=0group by t1.PARAMETER;
PARAMETER SUM(T1.GETS)SUM(T1.GETMISSES) PCT_SUCC_GETS UPDATES
-------------------------------------------- ----------------- ------------- ----------
dc_constraints 39950 16050 59.8247809762 39942
qmc_app_cache_entries 1 1 0 0
sch_lj_oids 33696 1452 95.6908831908 0
dc_tablespaces 994784326 74 99.9999925612 2
dc_awr_control 271427 3 99.9988947304 6322
dc_object_grants 41203543 6097 99.9852027288 0
dc_histogram_data 649621988 1065618 99.8359633725 1069946
dc_rollback_segments 25496538 235 99.9990783062 825
dc_sequences 156506 1536 99.0185679782 156506
sch_lj_objs 4018 796 80.1891488302 0
dc_segments 130394426 139507 99.8930115310 77938
dc_objects 225599535 310991 99.8621490952 96415
dc_histogram_defs 217299674 1852549 99.1474681181 646908
dc_table_scns 4801 4801 0 0
dc_users 1300331635 10744 99.9991737492 789
qmtmrcin_cache_entries 1 1 0 0
outstanding_alerts 413246 2893 99.2999327277 6092
dc_files 36345 49 99.8651809052 6
dc_global_oids 28777369 6959 99.9758178032 235
dc_profiles 5282207 1 99.9999810685 0
PARAMETER SUM(T1.GETS)SUM(T1.GETMISSES) PCT_SUCC_GETS UPDATES
-------------------------------------------- ----------------- ------------- ----------
global database name 12620366 16 99.9998732207 0
qmtmrctn_cache_entries 13 13 0 0
qmtmrciq_cache_entries 1 1 0 0
qmtmrctq_cache_entries 586 585 0.17064846416 0
qmrc_cache_entries 7 7 0 0
25 rows selected
SQL>
PARAMETER：标识一个特定类型的数据字典条目，以dc_为前缀。
GETS：对特性数据字典条目请求的数据总数量
GETMISSES：显示由于数据字典缓存失败，需要进行磁盘I/O操作的数据的数量
MODIFICATIONS：显示数据字典缓存中数据被更新的次数
3.3 共享池大小的调整
通常情况下，库缓存命中率应该高于99%，而数据字典缓存命中率应该高于90%，否则需要调整共享池大小了。因为库缓存的大小和数据字典缓存大小没有单独设置，而是oracle自动按照一定的算法在共享池中分配，按照oracle中的内存空间分配算法，如果库缓存命中率高，那么数据字典缓存命中率也会很高。
3.4 重做日志缓冲区调整
重做日志缓冲区用于存放数据的修改信息，重做日志首先写入重做日志缓冲区，在一定条件下由LGWR进程将重做缓冲区的信息写入重做日志文件；如果重做日志缓冲区容量较大，即可以保证有足够空间存储新产生的重做记录，又可以为LGWR进程高效写入重做日志文件提供了条件；如果重做日志缓冲区已经满了，没有空间容纳新的重做日志记录，新产生的重做日志记录处于waiting状态，称为重做日志缓冲区写入失败。过多的重做日志写入失败，说明重做日志缓冲区偏小，影响数据库性能。
可以通过如下方式检查重做日志缓冲区写入失败率。
select t1.NAME"request",t2.NAME,t1.VALUE/t2.VALUE "Fail Ratio"
from v$sysstat t1,v$sysstat t2
where t1.name='redo log space requests' andt2.NAME='redo entries';
结果如下：
SQL> select t1.NAME"request",t2.NAME,t1.VALUE/t2.VALUE "Fail Ratio"
2 from v$sysstat t1,v$sysstat t2
3 where t1.name='redo log spacerequests' and t2.NAME='redo entries';
request NAME Fail Ratio
-------------------------------------------------------------------------------------------------------------------------------- ----------
redo log space requests redoentries 0.00355035
SQL>
通常日志缓冲区的写入失败率应该接近于0，如果失败率大于1%，则说明日志缓冲区太小，应该增加LOG_BUFFER的大小。
看到写入失败率为0.003，远远小于1，证明日志缓冲区还算比较合适的。
4，PGA调整
PGA是存放服务器进程私有的数据和控制信息的内存区域，每个服务器进程只能访问自己的PGA区。在PGA区中可以包括一下这些工作区。
通常情况下，可以先将参数PGA_AGGREGATE_TARGET的值设置为SGA的20%，然后再数据库中运行有代表性的工作负荷、统计、检查PGA运行情况，并进行适当调整。
在oracle数据库中，排序可以在pga的排序区和临时表空间的临时段中进行排序。由于使用临时段需要对磁盘进行io操作，会降低排序的效率，因此oracle建议尽量在排序区进行排序操作。
根据数据在排序区的排序量和磁盘排序量，可以计算出排序区排序命中率，
select memory1.NAME "memorysort",disk1.name "disk sort", (1-disk1.value/memory1.value)*100"HitRatio"
from v$sysstat memory1, v$sysstat disk1
where memory1.NAME='sorts (memory)' anddisk1.NAME='sorts (disk)';
结果如下：
SQL> select memory1.NAME "memorysort",disk1.name "disk sort", (1-disk1.value/memory1.value)*100"HitRatio"
2 from v$sysstat memory1,v$sysstat disk1
3 where memory1.NAME='sorts(memory)' and disk1.NAME='sorts (disk)'
4 ;
memory sort disk sort HitRatio
-------------------------------------------------------------------------------------------------------------------------------- ----------
sorts (memory) sorts (disk) 99.9999420
SQL>
正常情况下，排序区的命中率应该高于95%，即保证绝大多数的排序操作在内存中进行，否则，就应该调整排序区大小，即增加SORT_AREA_SIZE的大小。从这里看到命中率为99.99还是合适的。
5，数据库碎片整理
5.1 表空间碎片整理
查看每个表空间的大小：SelectTablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name
查看剩余表空间大小：SELECTtablespace_name 表空间,sum(blocks*8192/1000000) 剩余空间M FROMdba_free_space GROUP BY tablespace_name;
表空间级碎片是由于段的建立、扩展和删除引起的。
可以采用2种方案来操作
1是altertablespace PLCRM coalesce命令来。
SQL> alter tablespace PLCRM coalesce;
Tablespacealtered.
SQL>
2是先通过export程序将数据导出，然后利用truncate删除表中的数据，最后用import将数据导入即可。
5.2 整理表的碎片空闲
分配给表的物理空间数量：SelectSegment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name;
实际使用的空间：selectnum_rows * avg_row_len from user_tables where table_name = 'EMP';
回收表的空闲空间：altertable tablename deallocate unused;
5.3 索引碎片整理
（1）. recreateindex
（2）. alterindex skate.idx_test rebuild nologging parallel 4 online ;
（3）. 如果叶块是half-emptyor near empty，可以运行“alter index coalesce”来合并
6 磁盘I/O优化与调整
可以按照以下规律进行整理
（1）数据库的物理文件，包括数据文件日志文件以及控制文件，尽量分散到不同磁盘上，避免相互之间的磁盘竞争，同时还可以实现均衡磁盘负载。
（2）将同一个表空间的数据文件尽量平均分配到不同的磁盘上，实现磁盘之间的负载均衡。
（3）尽量将所有的日志文件分散到不同的磁盘上面，减少日志文件对磁盘的竞争。
（4）尽量将表和索引分散到不同的表空间，将表数据和索引数据存储到不同的磁盘，减少数据文件和索引文件对磁盘的竞争。
（5）为不同的应用创建不同的表空间，并将表空间所对应的数据文件存放到不同的磁盘，减少不同应用之间的磁盘竞争。
（6）系统表空间system不要分配给其它应用使用，建设db系统与ing呀之间的磁盘竞争。
（7）创建撤销表空间用于非系统回滚段的管理，防止磁盘竞争影响事务的完成。
（8）表空间的管理尽量采用本地管理方式，存储空间的分配采用自动管理，尽量避免碎片的产生以及行连接、行迁移的出现。
（9）根据表的特点以及数据量大小等，采用分区表、分区索引、索引优化表、聚簇等结构，合理地将数据分散到不同的数据文件中，提供系统的I/O性能。