根据v$SGA_TARGET_ADVICE设置SGA SIZE

2013-04-10

view V$SGA_TARGET_ADVICE provides information about the SGA_TARGET initialization parameter.

1、表结构
Column Datatype Description
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SGA_SIZE NUMBER Size of the SGA
SGA_SIZE_FACTOR NUMBER Ratio between the SGA_SIZE and the current size of the SGA
ESTD_DB_TIME NUMBER Estimated DB_TIME for this SGA_SIZE
ESTD_DB_TIME_FACTOR NUMBER Ratio between ESTD_DB_TIME and DB_TIME for the current size of the SGA
ESTD_PHYSICAL_READS NUMBER Estimated number of physical reads

2、v$sga_target_advice：该视图可用于建议SGA大小设置是否合理。

SELECT a.sga_size, --sga期望大小
a.sga_size_factor, --期望sga大小与实际sga大小的百分比
a.estd_db_time, --sga设置为期望的大小后，其dbtime消耗期望的变化
a.estd_db_time_factor, --修改sga为期望大小后，dbtime消耗的变化与修改前的变化百分比
a.estd_physical_reads --修改前后物理读的差值
FROM v$sga_target_advice a;

2、根据v$SGA_TARGET_ADVICE 设置SGA Size实例1：

SQL> select * from v$sga_target_advice order by sga_size;

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
384 .25 4093301 1.009 2817991027
768 .5 4067743 1.0027 1067813961
1152 .75 4057601 1.0002 1009562221
1536 1 4056790 1 1009562221
1920 1.25 4056384 .9999 856714501
2304 1.5 4055573 .9997 856714501
2688 1.75 4055167 .9996 856714501
3072 2 4055167 .9996 856714501

8 rows selected.

对硬盘读写要求不高，说明当前sga足够了。你如果继续增大sga对db_time和pio并没有特别大的性能提升

SGA_SIZE_FACTOR=1的SGA_SIZE 是现在的SGA_TARGET设置值1536。如果提高2倍，设置为3072M会怎么样呢？
ESTD_DB_TIME 基本没什么变化
ESTD_DB_TIME_FACTOR 会降低一点点
ESTD_PHYSICAL_READS 会降低一些

4、根据v$SGA_TARGET_ADVICE 设置SGA Size实例2：

SQL> select * from v$sga_target_advice order by sga_size;

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
2048 .5 7044718 1.2907 1.6650E+10
3072 .75 5575954 1.0216 1.2220E+10
4096 1 5458060 1 1.1848E+10
5120 1.25 5455331 .9995 1.1848E+10
6144 1.5 5454785 .9994 1.1848E+10
7168 1.75 5454785 .9994 9836109386
8192 2 5454785 .9994 8666723700

7 rows selected.

如果对硬盘读写非常频繁，通过上面查询结果SGA从4096M提升到8192M，读硬盘次数会降低10%左右。

分类：数据库 | 标签： |

相关日志

评论被关闭！