Oracle 初始化参数&性能视图
摘要： 《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 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，如需转载请自行联系原作者
版权声明：本文内容由互联网用户自发贡献，版权归作者所有，本社区不拥有所有权，也不承担相关法律责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件至：yqgroup@service.aliyun.com 进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容。
