《Oracle 初始化参数&性能视图》
1.数据库版本
LEO1@LEO1>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux:Version 11.2.0.1.0 - Production
NLSRTL Version11.2.0.1.0 - Production
2.设置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_target NAME TYPE VALUE ----------------------------------------------- ------------------------------ memory_max_target big integer 652M LEO1@LEO1> showparameter memory_target NAME TYPE VALUE ----------------------------------------------- ------------------------------ memory_target big integer 652M 5. 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_target sga_target 没有设置大小， pga_aggregate_target 设置大小 那么 sga_target 初始化值 =memory_target-pga_aggregate_target sga_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 target NAME TYPE VALUE ----------------------------------------------- ------------------------------ archive_lag_target integer 0 db_flashback_retention_target integer 1440 fast_start_io_target integer 0 fast_start_mttr_target integer 0 memory_max_target big integer 652M memory_target big integer 652M parallel_servers_target integer 8 pga_aggregate_target big integer 0 sga_target big integer 0 现在我们看到 sga_target和 pga_aggregate_target的值都是 0，由 oracle自动调整大小， memory_target和 memory_max_target的大小是 652M LEO1@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 0 MEMORY_SIZE： oracle建议的内存大小 MEMORY_SIZE_FACTOR：内存基线因子， 0.25, 0.5, 0.75, 1, 1.5, 1.75, and 2 ESTD_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 view v$memory_target_advice这个视图快照的版本号 1.当 memory_target=489M时， ESTD_DB_TIME=5522 2.当 memory_target=652M时， ESTD_DB_TIME=5521 当前值 3.当 memory_target=815M时， ESTD_DB_TIME=5518 4.当 memory_target=978M时， ESTD_DB_TIME=5517 5.当 memory_target=1141M时， ESTD_DB_TIME=5517 6.当 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 target NAME TYPE VALUE ----------------------------------------------- ------------------------------ archive_lag_target integer 0 db_flashback_retention_target integer 1440 fast_start_io_target integer 0 fast_start_mttr_target integer 0 memory_max_target big integer 652M memory_target big integer 652M parallel_servers_target integer 8 pga_aggregate_target big integer 0 sga_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>startup ORACLE instancestarted. Total SystemGlobal Area 513585152 bytes Fixed Size 2214856 bytes Variable Size 314573880 bytes DatabaseBuffers 188743680 bytes Redo Buffers 8052736 bytes Database mounted. Database opened. SYS@LEO1> showparameter target NAME TYPE VALUE ----------------------------------------------- ------------------------------ archive_lag_target integer 0 db_flashback_retention_target integer 1440 fast_start_io_target integer 0 fast_start_mttr_target integer 0 memory_max_target big integer 492M oracle做了一点点修正 memory_target big integer 492M parallel_servers_target integer 8 pga_aggregate_target big integer 0 sga_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默认值为 100 SYS@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 explain LEO1@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 processed LEO1@LEO1>alter session set optimizer_index_cost_adj=10; 从 100修改成 10 Session 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 =8k 128*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 2 LEO3 TABLE 9 LEO2 TABLE 9 LEO2表大小是 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=128k NAME TYPE VALUE ----------------------------------------------- ------------------------------------------ db_file_multiblock_read_count integer 16 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 ---------------------------------------------------------- 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 off LEO1@LEO1>alter session set db_file_multiblock_read_count=128;设置一次多块读可以读 128个数据块 Session altered. LEO1@LEO1> showparameter db_file_multiblock_read_count 128块 *8k=1M NAME TYPE VALUE ----------------------------------------------- ------------------------------ db_file_multiblock_read_count integer 128 LEO1@LEO1> setautotrace traceonly LEO1@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，如需转载请自行联系原作者
2.设置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_target NAME TYPE VALUE ----------------------------------------------- ------------------------------ memory_max_target big integer 652M LEO1@LEO1> showparameter memory_target NAME TYPE VALUE ----------------------------------------------- ------------------------------ memory_target big integer 652M 5. 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_target sga_target 没有设置大小， pga_aggregate_target 设置大小 那么 sga_target 初始化值 =memory_target-pga_aggregate_target sga_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 target NAME TYPE VALUE ----------------------------------------------- ------------------------------ archive_lag_target integer 0 db_flashback_retention_target integer 1440 fast_start_io_target integer 0 fast_start_mttr_target integer 0 memory_max_target big integer 652M memory_target big integer 652M parallel_servers_target integer 8 pga_aggregate_target big integer 0 sga_target big integer 0 现在我们看到 sga_target和 pga_aggregate_target的值都是 0，由 oracle自动调整大小， memory_target和 memory_max_target的大小是 652M LEO1@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 0 MEMORY_SIZE： oracle建议的内存大小 MEMORY_SIZE_FACTOR：内存基线因子， 0.25, 0.5, 0.75, 1, 1.5, 1.75, and 2 ESTD_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 view v$memory_target_advice这个视图快照的版本号 1.当 memory_target=489M时， ESTD_DB_TIME=5522 2.当 memory_target=652M时， ESTD_DB_TIME=5521 当前值 3.当 memory_target=815M时， ESTD_DB_TIME=5518 4.当 memory_target=978M时， ESTD_DB_TIME=5517 5.当 memory_target=1141M时， ESTD_DB_TIME=5517 6.当 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 target NAME TYPE VALUE ----------------------------------------------- ------------------------------ archive_lag_target integer 0 db_flashback_retention_target integer 1440 fast_start_io_target integer 0 fast_start_mttr_target integer 0 memory_max_target big integer 652M memory_target big integer 652M parallel_servers_target integer 8 pga_aggregate_target big integer 0 sga_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>startup ORACLE instancestarted. Total SystemGlobal Area 513585152 bytes Fixed Size 2214856 bytes Variable Size 314573880 bytes DatabaseBuffers 188743680 bytes Redo Buffers 8052736 bytes Database mounted. Database opened. SYS@LEO1> showparameter target NAME TYPE VALUE ----------------------------------------------- ------------------------------ archive_lag_target integer 0 db_flashback_retention_target integer 1440 fast_start_io_target integer 0 fast_start_mttr_target integer 0 memory_max_target big integer 492M oracle做了一点点修正 memory_target big integer 492M parallel_servers_target integer 8 pga_aggregate_target big integer 0 sga_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默认值为 100 SYS@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 explain LEO1@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 processed LEO1@LEO1>alter session set optimizer_index_cost_adj=10; 从 100修改成 10 Session 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 =8k 128*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 2 LEO3 TABLE 9 LEO2 TABLE 9 LEO2表大小是 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=128k NAME TYPE VALUE ----------------------------------------------- ------------------------------------------ db_file_multiblock_read_count integer 16 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 ---------------------------------------------------------- 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 off LEO1@LEO1>alter session set db_file_multiblock_read_count=128;设置一次多块读可以读 128个数据块 Session altered. LEO1@LEO1> showparameter db_file_multiblock_read_count 128块 *8k=1M NAME TYPE VALUE ----------------------------------------------- ------------------------------ db_file_multiblock_read_count integer 128 LEO1@LEO1> setautotrace traceonly LEO1@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，如需转载请自行联系原作者
