ORACLE数据库性能优化之-->内存磁盘

时间:2022-06-07 06:00:07

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不能是标准数据块大小)

 

原blog地址为:http://blog.csdn.net/mchdba/article/details/48552679,未经原作者mchdba允许,谢绝转载

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报表中ORACLE数据库性能优化之-->内存磁盘

 

 

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,证明日志缓冲区还算比较合适的。

 

 

4PGA调整

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性能。