memory_target是11g引入的新特性,我们生产库用的就是这个,一直算是正常
SQL> show parameter memory
memory_max_target big integer 10G
memory_target big integer 8G
自从调整过后,一直没问题
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 0
SQL> show sga;
Total System Global Area 1.0689E+10 bytes
Fixed Size 2147392 bytes
Variable Size 8388611008 bytes
Database Buffers 2281701376 bytes
Redo Buffers 17014784 bytes
SQL>
最近闲下来分析发现一个问题
SQL> select sga_size, sga_size_factor, estd_db_time, estd_db_time_factor, estd_physical_reads
from v$sga_target_advice order by sga_size_factor;
2
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
2048 .5 1622819 6.012 218345628
3072 .75 315548 1.169 3933137
4096 1 269930 1 1463166
5120 1.25 264774 .9809 1012657
6144 1.5 264019 .9781 888873
7168 1.75 264019 .9781 888873
8192 2 264019 .9781 888873
7 rows selected.
SQL> desc v$memory_target_advice;
Name Null? Type
----------------------------------------- -------- ----------------------------
MEMORY_SIZE NUMBER
MEMORY_SIZE_FACTOR NUMBER
ESTD_DB_TIME NUMBER
ESTD_DB_TIME_FACTOR NUMBER
VERSION NUMBER
SQL> select * from v$memory_target_advice;
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
----------- ------------------ ------------ ------------------- ----------
8192 1 270772 1 0
6144 .75 270826 1.0002 0
10240 1.25 270772 1 0
12288 1.5 270772 1 0
14336 1.75 270772 1 0
16384 2 270772 1 0
6 rows selected.
有没有朋友遇到过类似的问题?
[本帖最后由 coldwater 于 2010-4-24 10:09 编辑]