Oracle 初始化参数&性能视图 暖夏未眠丶 2018-02-01 247 阅读10分钟 摘要： 《Oracle 初始化参数&性能视图》 1.数据库版本 LEO1@LEO1>select * from v$version; BANNER -------------------------------------------------------------------------------...《Oracle 初始化参数&性能视图》1.数据库版本LEO1@LEO1>select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionPL/SQL Release11.2.0.1.0 - ProductionCORE 11.2.0.1.0 ProductionTNS for Linux:Version 11.2.0.1.0 - ProductionNLSRTL Version11.2.0.1.0 - Production2.设置memory_target参数，并通过v$memory_target_advice分析数据库的最佳内存大小Memory_target：1.是oracle11g中的一个内存调整参数，11g对自动化管理内存方面又继续加强了，原来10g中可以对SGA进行自动管理与分配，11g即可以自动管理SGA，又可以自动管理PGA，对这两部分进行综合管理，自动调整所有内存区的大小。11g中默认为0现在把这几个参数语法列举一下，这是静态参数需要重启数据库生效alter systemset memory_max_target= 1000m scope=spfile;alter system set memory_target= 1000m scope=spfile;alter system set sga_max_size=600m scope=spfile;alter system set pga_aggregate_target=400m scope=spfile;2.memory_max_target 是设定 Oracle 能占物理内存多大空间，一个是 Oracle SGA 区最大能占多大内存空间+PGA区多大空间，memory_max_target是memory_target上限值，如果只设置了memory_max_target没有设置memory_target，则Oracle认为memory_target=0不使用内存自动管理。3.如果只设置memory_target，没有设置memory_max_target，则Oracle自动将memory_max_target设置为memory_target。4.如果同时设置这两个值，则memory_target的上限值为memory_max_target。这是我的数据库上的参数值LEO1@LEO1> showparameter memory_max_targetNAME TYPE VALUE----------------------------------------------- ------------------------------memory_max_target big integer 652MLEO1@LEO1> showparameter memory_targetNAME TYPE VALUE----------------------------------------------- ------------------------------memory_target big integer 652M5. 10g 的sga_max_size 是动态分配 Shared Pool Size,database buffer cache,largepool,java pool，redo log buffer 大小的，根据 Oracle 运行状态来重新分配 SGA 各内存区大小。 PGA 在 10g 中需要单独设定（即手工管理）。实验下面我们通过以下的几个命令来让大家清楚memory_target 的设置与PGA和SGA的关系（1）memory_target设置为非0值Memory_Target=SGA_TARGET+PGA_AGGREGATE_TARGET ，大小等于memory_max_size 一致。sga_target和pga_aggregate_target都设置了大小，则这两个参数将做为最小起始值sga_target 设置大小， pga_aggregate_target 没有设置大小 那么 pga_aggregate_target 初始化值 =memory_target-sga_targetsga_target 没有设置大小， pga_aggregate_target 设置大小 那么 sga_target 初始化值 =memory_target-pga_aggregate_targetsga_target 和pga_aggregate_target都没有设置大小 Oracle 11g 将根据数据库运行状态自动分配大小。但在数据库启动时会有一个固定比例来分配： sga_target =memory_target *60% pga_aggregate_target=memory_target *40%（2）memory_target没有设置或等于0（11g中默认为0）11g中默认为0则初始状态下取消了 memory_target 的作用，完全和10g在内存管理上一致，完全向下兼容。 （也有三种情况来对 SGA 和 PGA 的大小进行分配）SGA_TARGET 设置值，则自动调节 SGA 中的 shared pool,buffer cache,redo logbuffer,java pool,larger pool内存区，PGA 则依赖 pga_aggregate_target 的大小单独设置。 sga和 pga不能自动增长和自动缩小。SGA_target 和PGA_AGGREGATE_TARGET 都没有设置，SGA 中的各内存区大小都要明确设定，不能自动调整各内存区大小。PGA 不能自动增长和收缩。memory_max_target设置而memory_target =0 这种情况和10g 一样不使用内存自动管理LEO1@LEO1> showparameter targetNAME TYPE VALUE----------------------------------------------- ------------------------------archive_lag_target integer 0db_flashback_retention_target integer 1440fast_start_io_target integer 0fast_start_mttr_target integer 0memory_max_target big integer 652Mmemory_target big integer 652Mparallel_servers_target integer 8pga_aggregate_target big integer 0sga_target big integer 0现在我们看到sga_target和pga_aggregate_target的值都是0，由oracle自动调整大小，memory_target和memory_max_target的大小是652MLEO1@LEO1>select * from v$memory_target_advice; 分析数据库最佳内存大小MEMORY_SIZE MEMORY_SIZE_FACTORESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION----------------------------- ------------ ------------------- ---------- 489 .75 5522 1.0002 0 652 1 5521 1 0 815 1.25 5518 .9994 0 978 1.5 5517 .9993 0 1141 1.75 5517 .9992 0 1304 2 5517 .9992 0MEMORY_SIZE：oracle建议的内存大小MEMORY_SIZE_FACTOR：内存基线因子，0.25, 0.5, 0.75, 1, 1.5, 1.75, and 2ESTD_DB_TIME：Forcurrent memory size (MEMORY_SIZE_FACTOR = 1), the amount of database time requiredto complete the current workload. For a proposed memory size, the estimatedamount of database time that would be required if the MEMORY_TARGET parameterwere changed to the proposed size.（官方文档解释）当内存基线因子为1时，完成当前数据库工作量所需要的所有数据库时间（即所有用户消耗的数据库时间），这是一个建议值，它会根据memory_target参数的改变而改变ESTD_DB_TIME_FACTOR：Fora proposed memory size, ratio of estimated database time to current databasetime（官方文档解释）消耗数据库时间的比例因子VERSION：Versionnumber of this recommendation (this snapshot of the V$MEMORY_TARGET_ADVICE viewv$memory_target_advice这个视图快照的版本号1.当memory_target=489M时，ESTD_DB_TIME=55222.当memory_target=652M时，ESTD_DB_TIME=5521 当前值3.当memory_target=815M时，ESTD_DB_TIME=55184.当memory_target=978M时，ESTD_DB_TIME=55175.当memory_target=1141M时，ESTD_DB_TIME=55176.当memory_target=1304M时，ESTD_DB_TIME=5517从如上的系统资源消耗情况来看，memory_target=489M是之前652M的四分之三，但ESTD_DB_TIME才增加了1性价比非常高，我们可以把memory_target=修改成489M，节约我们的内存资源LEO1@LEO1>alter system set memory_max_target=489m scope=spfile;System altered.LEO1@LEO1>alter system set memory_target=489m scope=spfile;System altered.LEO1@LEO1> showparameter targetNAME TYPE VALUE----------------------------------------------- ------------------------------archive_lag_target integer 0db_flashback_retention_target integer 1440fast_start_io_target integer 0fast_start_mttr_target integer 0memory_max_target big integer 652Mmemory_target big integer 652Mparallel_servers_target integer 8pga_aggregate_target big integer 0sga_target big integer 0现在只是修改了spfile参数文件的内容，需要重启数据库才生效LEO1@LEO1>shutdown immediate ORA-01031:insufficient privileges 权限不足LEO1@LEO1> conn/ as sysdba 切换sys用户Connected.SYS@LEO1>shutdown immediate 关闭实例Database closed.Databasedismounted.ORACLE instanceshut down.SYS@LEO1>startupORACLE instancestarted.Total SystemGlobal Area 513585152 bytesFixed Size 2214856 bytesVariable Size 314573880 bytesDatabaseBuffers 188743680 bytesRedo Buffers 8052736 bytesDatabase mounted.Database opened.SYS@LEO1> showparameter targetNAME TYPE VALUE----------------------------------------------- ------------------------------archive_lag_target integer 0db_flashback_retention_target integer 1440fast_start_io_target integer 0fast_start_mttr_target integer 0memory_max_target big integer 492M oracle做了一点点修正memory_target big integer 492M parallel_servers_target integer 8pga_aggregate_target big integer 0sga_target big integer 0小结：我们可以根据v$memory_target_advice视图来合理的调整memory_target的内存值，提高资源利用率。3.通过调整参数optimizer_index_cost_adj的大小，演示SQL产生不同执行计划Optimizer_index_cost_adj：这个参数是用于CBO在计算索引成本时的权重修正值Optimizer_index_cost_adj的值越高，使用索引的几率越低，CBO倾向于全表扫描Optimizer_index_cost_adj的值越低，使用索引的几率越高，CBO倾向于走索引Optimizer_index_cost_adj默认值为100SYS@LEO1> showparameter optimizer_index_cost_adj; NAME TYPE VALUE----------------------------------------------- ------------------------------optimizer_index_cost_adj integer 100实验LEO1@LEO1> droptable leo1 purge; 清理环境Table dropped.LEO1@LEO1>create table leo1 as select * from dba_objects where rownum<200; 创建leo1表199条记录Table created.LEO1@LEO1>create index idx_leo1 on leo1(object_id); 创建idx_leo1 B-tree索引Index created.我们来看一下当optimizer_index_cost_adj=100时执行计划LEO1@LEO1> setautotrace trace explainLEO1@LEO1>select * from leo1;Execution Plan----------------------------------------------------------Plan hash value:2716644435--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 199 | 41193 | 3 (0)| 00:00:01 || 1 | TABLE ACCESS FULL | LEO1 | 199 | 41193 | 3 (0)| 00:00:01 |--------------------------------------------------------------------------走的是全表扫描，此时我们只发生了19次一致性读，只扫描数据块没有扫描索引块Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 19 consistent gets 产生19个一致性读 0 physical reads 0 redo size 20823 bytes sent via SQL*Net to client 667 bytes received via SQL*Net from client 15 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 199 rows processedLEO1@LEO1>alter session set optimizer_index_cost_adj=10; 从100修改成10Session altered.当这个参数越小时，CBO更倾向于走索引LEO1@LEO1>select * from leo1 where object_id<=800;Execution Plan----------------------------------------------------------Plan hash value:1434365503----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 199 | 41193 | 1 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID | LEO1 | 199| 41193 | 1 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN |IDX_LEO1 | 199 | | 1 (0)| 00:00:01 |----------------------------------------------------------------------------------------走的是索引，此时我们发生了32次一致性读，先扫描索引块然后根据rowid扫描数据块Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 32 consistent gets 产生32个一致性读 0 physical reads 0 redo size 20823 bytes sent via SQL*Net to client 667 bytes received via SQL*Net from client 15 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts(disk) 199 rows processed小结：相同结果集我们在走索引的时候比全表扫描产生的一致性读要多，说明发生的逻辑IO次数更多了，消耗的系统IO资源更多了，这是不合理的，我们应该在生产中进行避免。4.通过设置参数DB_FILE_MULTIBLOCK_READ_COUNT不同的值，演示对SQL效率的影响db_file_multiblock_read_count：这个初始化参数叫做“一次读多少个数据块or一次多块读可以读几个数据块”。这个参数值并不是无限大的，大多数平台下的oracle都是128。一般oracle block size =8k128*8=1M，也就是说1M是大多数操作系统一次最大IO的限制，如果还有其他限制要从这1M里面扣除，初始化参数db_file_multiblock_read_count的最大值之所以定为128，也是为了保守策略。场景：（1）全表扫描FTS（FULL TABLE SCAN）：这时oracle支持多块读（2）索引快速全扫描IFFS（INDEX FAST FULL SCAN）：索引并行读取的时候也支持多块读（3）OLAP：可以设置的大一些，但不是越大越好（4）还会受到操作系统IO本身的限制实验LEO1@LEO1> droptable leo2 purge; 清理环境Table dropped.LEO1@LEO1> droptable leo3 purge;Table dropped.LEO1@LEO1>create table leo2 as select * from dba_objects; 创建leo2表Table created.LEO1@LEO1>create table leo3 as select * from dba_objects; 创建leo3表Table created.LEO1@LEO1>create index idx_leo3 on leo3(object_id); 创建idx_leo3索引Index created.LEO1@LEO1>select segment_name,segment_type,bytes/1024/1024 from user_segments where segment_namein ('LEO2','LEO3','IDX_LEO3');SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024-------------------------------------------------------------------------------------------IDX_LEO3 INDEX 2LEO3 TABLE 9LEO2 TABLE 9LEO2表大小是9M（段头+数据），LEO3表大小9+2=11M（表+索引）LEO1@LEO1>alter session set db_file_multiblock_read_count=16; 设置一次多块读可以读16个数据块Session altered.LEO1@LEO1> showparameter db_file_multiblock_read_count 16块*8k=128kNAME TYPE VALUE----------------------------------------------- ------------------------------------------db_file_multiblock_read_count integer 16LEO1@LEO1>select count(*) from leo2;Execution Plan----------------------------------------------------------Plan hash value:3963694794-------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 233 (1)| 00:00:03 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL | LEO2 | 73470 | 233 (1)| 00:00:03 |-------------------------------------------------------------------Statistics---------------------------------------------------------- 210 recursive calls 0 db block gets 1119 consistent gets 1025 physical reads 0 redo size 528 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 1 rows processed一般执行2遍，统计信息会稳定下来LEO1@LEO1>select count(*) from leo2;Execution Plan----------------------------------------------------------Plan hash value:3963694794-------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 233 (1)| 00:00:03 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL | LEO2 | 73470 | 233 (1)| 00:00:03 |-------------------------------------------------------------------全表扫描会使用多块读Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 1030 consistent gets 1030块*8K=8240k约等于9M 0 physical reads 0 redo size 528 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed我们在用1030/16=64.375次，oracle需要读取64.375次IO，才能把所有记录读取完。LEO1@LEO1> selectcount(object_id) from leo3;Execution Plan----------------------------------------------------------Plan hash value:3677630522----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 40 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | INDEX FAST FULL SCAN| IDX_LEO3 | 71702 | 910K| 40 (0)| 00:00:01 |----------------------------------------------------------------------------------Statistics---------------------------------------------------------- 4 recursive calls 0 db block gets 236 consistent gets 160 physical reads 0 redo size 536 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed执行2遍LEO1@LEO1> selectcount(object_id) from leo3;Execution Plan----------------------------------------------------------Plan hash value:3677630522----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 40 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | INDEX FAST FULL SCAN| IDX_LEO3 | 71702 | 910K| 40 (0)| 00:00:01 |----------------------------------------------------------------------------------索引快速全扫描会使用多块读Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 168 consistent gets 168块*8k=1344k约等于2M 0 physical reads 0 redo size 536 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed我们在用168/16=10.5次，oracle需要读取10.5次IO，才能计算出最后结果LEO1@LEO1> setautotrace offLEO1@LEO1>alter session set db_file_multiblock_read_count=128;设置一次多块读可以读128个数据块Session altered.LEO1@LEO1> showparameter db_file_multiblock_read_count 128块*8k=1MNAME TYPE VALUE----------------------------------------------- ------------------------------db_file_multiblock_read_count integer 128LEO1@LEO1> setautotrace traceonlyLEO1@LEO1>select count(*) from leo2;Execution Plan----------------------------------------------------------Plan hash value:3963694794-------------------------------------------------------------------| Id | Operation | Name | Rows | Cost (%CPU)| Time |-------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 185 (1)| 00:00:03 || 1 | SORT AGGREGATE | | 1 | | || 2 | TABLE ACCESS FULL| LEO2 | 73470 | 185 (1)| 00:00:03 |-------------------------------------------------------------------参数调整后cost从原来233减少到185，IO代价减少了，说明参数生效了Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 1030 consistent gets 1030/128=8.04次IO 0 physical reads 0 redo size 528 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed我们在用1030/128=8.04次，oracle从读取64.375次减少到8.04次，IO资源消耗大大降低，SQL效率提高不少。LEO1@LEO1> selectcount(object_id) from leo3;Execution Plan----------------------------------------------------------Plan hash value:3677630522----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13| 32 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | INDEX FAST FULL SCAN |IDX_LEO3 | 71702 | 910K| 32 (0)| 00:00:01 |----------------------------------------------------------------------------------索引快速全扫描cost从原来40减少到32，IO代价也减少了，说明参数生效了Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 168 consistent gets 168/128=1.3125次 0 physical reads 0 redo size 536 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rowsprocessed小结：oracle走索引从10.5次减少到1.3125次，IO次数大大降低，SQL读取的效率自然就提高了。 本文转自 ztfriend 51CTO博客，原文链接：http://blog.51cto.com/leonarding/1143571，如需转载请自行联系原作者版权声明：本文内容由互联网用户自发贡献，版权归作者所有，本社区不拥有所有权，也不承担相关法律责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件至：yqgroup@service.aliyun.com 进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容。原文链接
