本文共 16593 字，大约阅读时间需要 55 分钟。
LEO1@LEO1>select * from v$version; -------------------------------------------------------------------------------- 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 ----------------------------------------------- ------------------------------ memory_max_target big integer 652M LEO1@LEO1> showparameter memory_target ----------------------------------------------- ------------------------------ 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的关系 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 ----------------------------------------------- ------------------------------ 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和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 ----------------------------- ------------ ------------------- ---------- 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; LEO1@LEO1>alter system set memory_target=489m scope=spfile; LEO1@LEO1> showparameter target ----------------------------------------------- ------------------------------ 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 现在只是修改了spfile参数文件的内容，需要重启数据库才生效 LEO1@LEO1>shutdown immediate ORA-01031:insufficient privileges 权限不足 LEO1@LEO1> conn/ as sysdba 切换sys用户 SYS@LEO1>shutdown immediate 关闭实例 ORACLE instanceshut down. Total SystemGlobal Area 513585152 bytes Variable Size 314573880 bytes DatabaseBuffers 188743680 bytes Redo Buffers 8052736 bytes SYS@LEO1> showparameter target ----------------------------------------------- ------------------------------ 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 小结：我们可以根据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; ----------------------------------------------- ------------------------------ optimizer_index_cost_adj integer 100 LEO1@LEO1> droptable leo1 purge; 清理环境 LEO1@LEO1>create table leo1 as select * from dba_objects where rownum<200; 创建leo1表199条记录 LEO1@LEO1>create index idx_leo1 on leo1(object_id); 创建idx_leo1 B-tree索引 我们来看一下当optimizer_index_cost_adj=100时执行计划 LEO1@LEO1> setautotrace trace explain LEO1@LEO1>select * from leo1; ---------------------------------------------------------- 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次一致性读，只扫描数据块没有扫描索引块 ---------------------------------------------------------- 19 consistent gets 产生19个一致性读 20823 bytes sent via SQL*Net to client 667 bytes received via SQL*Net from client 15 SQL*Net roundtrips to/from client LEO1@LEO1>alter session set optimizer_index_cost_adj=10; 从100修改成10 LEO1@LEO1>select * from leo1 where object_id<=800; ---------------------------------------------------------- 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扫描数据块 ---------------------------------------------------------- 32 consistent gets 产生32个一致性读 20823 bytes sent via SQL*Net to client 667 bytes received via SQL*Net from client 15 SQL*Net roundtrips to/from client 小结：相同结果集我们在走索引的时候比全表扫描产生的一致性读要多，说明发生的逻辑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）：索引并行读取的时候也支持多块读 LEO1@LEO1> droptable leo2 purge; 清理环境 LEO1@LEO1> droptable leo3 purge; LEO1@LEO1>create table leo2 as select * from dba_objects; 创建leo2表 LEO1@LEO1>create table leo3 as select * from dba_objects; 创建leo3表 LEO1@LEO1>create index idx_leo3 on leo3(object_id); 创建idx_leo3索引 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 ------------------------------------------------------------------------------------------- LEO2表大小是9M（段头+数据），LEO3表大小9+2=11M（表+索引） LEO1@LEO1>alter session set db_file_multiblock_read_count=16; 设置一次多块读可以读16个数据块 LEO1@LEO1> showparameter db_file_multiblock_read_count 16块*8k=128k ----------------------------------------------- ------------------------------------------ db_file_multiblock_read_count integer 16 LEO1@LEO1>select count(*) from leo2; ---------------------------------------------------------- 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 | ------------------------------------------------------------------- ---------------------------------------------------------- 528 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client LEO1@LEO1>select count(*) from leo2; ---------------------------------------------------------- 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 | ------------------------------------------------------------------- ---------------------------------------------------------- 1030 consistent gets 1030块*8K=8240k约等于9M 528 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 我们在用1030/16=64.375次，oracle需要读取64.375次IO，才能把所有记录读取完。 LEO1@LEO1> selectcount(object_id) from leo3; ---------------------------------------------------------- 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 | ---------------------------------------------------------------------------------- ---------------------------------------------------------- 536 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client LEO1@LEO1> selectcount(object_id) from leo3; ---------------------------------------------------------- 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 | ---------------------------------------------------------------------------------- ---------------------------------------------------------- 168 consistent gets 168块*8k=1344k约等于2M 536 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 我们在用168/16=10.5次，oracle需要读取10.5次IO，才能计算出最后结果 LEO1@LEO1> setautotrace off LEO1@LEO1>alter session set db_file_multiblock_read_count=128;设置一次多块读可以读128个数据块 LEO1@LEO1> showparameter db_file_multiblock_read_count 128块*8k=1M ----------------------------------------------- ------------------------------ db_file_multiblock_read_count integer 128 LEO1@LEO1> setautotrace traceonly LEO1@LEO1>select count(*) from leo2; ---------------------------------------------------------- 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代价减少了，说明参数生效了 ---------------------------------------------------------- 1030 consistent gets 1030/128=8.04次IO 528 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 我们在用1030/128=8.04次，oracle从读取64.375次减少到8.04次，IO资源消耗大大降低，SQL效率提高不少。 LEO1@LEO1> selectcount(object_id) from leo3; ---------------------------------------------------------- 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代价也减少了，说明参数生效了 ---------------------------------------------------------- 168 consistent gets 168/128=1.3125次 536 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 小结：oracle走索引从10.5次减少到1.3125次，IO次数大大降低，SQL读取的效率自然就提高了。 转载地址：http://iqkax.baihongyu.com/