Oracle Automatic Shared Memory Management (ASMM)
Oracle Automatic Shared Memory Management (ASMM) is a new 10g great functionality but beyond just setting the parameters in initialization parameter file you could be disappointed with what you can easily see with Grid Control.

The memory parameters of the above database have been set like:
statistics_level = typical db_cache_advice = ready sga_target = 10G pga_aggregate_target = 2000M log_buffer = 10485760
You can confirm what you see in Grid Control with the below query:
SQL> select component, current_size from v$sga_dynamic_components; COMPONENT CURRENT_SIZE ---------------------------------------------------------------- ------------ shared pool 6274678784 large pool 16777216 java pool 16777216 streams pool 0 DEFAULT buffer cache 4412407808 KEEP buffer cache 0 RECYCLE buffer cache 0 DEFAULT 2K buffer cache 0 DEFAULT 4K buffer cache 0 DEFAULT 8K buffer cache 0 DEFAULT 16K buffer cache 0 DEFAULT 32K buffer cache 0 ASM Buffer Cache 0 13 rows selected.
So ? Shared pool is equal to 6GB and database cache is only equal to 4GB ??!! We are far from the standard recommendations when setting database cache and shared pool (80% 20%).
There are few bugs where resize operations of the dynamic components are consuming lots of CPU and lost Oracle itself, are we hitting those bugs ?
select parameter, oper_type, oper_mode, initial_size, target_size, status, start_time, end_time from v$sga_resize_ops where rownum<=20; PARAMETER OPER_TYPE OPER_MODE INITIAL_SIZE TARGET_SIZE STATUS START_TIM END_TIME ---------------------- ------------- --------- ------------ ----------- --------- --------- --------- db_cache_size STATIC IMMEDIATE 9177137152 9160359936 COMPLETE 24-MAR-10 24-MAR-10 large_pool_size GROW IMMEDIATE 0 16777216 COMPLETE 24-MAR-10 24-MAR-10 db_cache_size INITIALIZING IMMEDIATE 9177137152 9160359936 COMPLETE 24-MAR-10 24-MAR-10 db_cache_size STATIC 0 0 COMPLETE 24-MAR-10 24-MAR-10 db_32k_cache_size STATIC 0 0 COMPLETE 24-MAR-10 24-MAR-10 db_16k_cache_size STATIC 0 0 COMPLETE 24-MAR-10 24-MAR-10 db_8k_cache_size STATIC 0 0 COMPLETE 24-MAR-10 24-MAR-10 db_4k_cache_size STATIC 0 0 COMPLETE 24-MAR-10 24-MAR-10 db_2k_cache_size STATIC 0 0 COMPLETE 24-MAR-10 24-MAR-10 db_recycle_cache_size STATIC 0 0 COMPLETE 24-MAR-10 24-MAR-10 db_keep_cache_size STATIC 0 0 COMPLETE 24-MAR-10 24-MAR-10 shared_pool_size STATIC 0 1526726656 COMPLETE 24-MAR-10 24-MAR-10 large_pool_size STATIC 0 16777216 COMPLETE 24-MAR-10 24-MAR-10 java_pool_size STATIC 0 16777216 COMPLETE 24-MAR-10 24-MAR-10 streams_pool_size STATIC 0 0 COMPLETE 24-MAR-10 24-MAR-10 db_cache_size STATIC 0 9160359936 COMPLETE 24-MAR-10 24-MAR-10 shared_pool_size GROW IMMEDIATE 1526726656 1543503872 COMPLETE 24-MAR-10 24-MAR-10 db_cache_size SHRINK IMMEDIATE 9160359936 9143582720 COMPLETE 24-MAR-10 24-MAR-10 db_cache_size SHRINK IMMEDIATE 9143582720 9126805504 COMPLETE 25-MAR-10 25-MAR-10 shared_pool_size GROW IMMEDIATE 1543503872 1560281088 COMPLETE 25-MAR-10 25-MAR-10
SELECT parameter, oper_type, TO_CHAR(start_time,'yyyymmdd') as start_time, COUNT(*) FROM v$sga_resize_ops GROUP BY parameter, oper_type, TO_CHAR(start_time,'yyyymmdd') order by 3 desc PARAMETER OPER_TYPE START_TI COUNT(*) ------------------------- ------------- -------- ---------- db_cache_size GROW 20100409 3 shared_pool_size GROW 20100409 8 db_cache_size SHRINK 20100409 8 shared_pool_size SHRINK 20100409 3 db_cache_size GROW 20100408 9 shared_pool_size GROW 20100408 2 db_cache_size SHRINK 20100408 2 shared_pool_size SHRINK 20100408 9 shared_pool_size GROW 20100329 1 db_cache_size SHRINK 20100329 1 shared_pool_size GROW 20100326 2 db_cache_size SHRINK 20100326 2 shared_pool_size GROW 20100325 40 db_cache_size SHRINK 20100325 40 large_pool_size GROW 20100324 1 shared_pool_size GROW 20100324 1 db_cache_size INITIALIZING 20100324 1 db_cache_size SHRINK 20100324 1 db_16k_cache_size STATIC 20100324 1 db_2k_cache_size STATIC 20100324 1 db_32k_cache_size STATIC 20100324 1 db_4k_cache_size STATIC 20100324 1 db_8k_cache_size STATIC 20100324 1 db_cache_size STATIC 20100324 3 db_keep_cache_size STATIC 20100324 1 db_recycle_cache_size STATIC 20100324 1 java_pool_size STATIC 20100324 1 large_pool_size STATIC 20100324 1 shared_pool_size STATIC 20100324 1 streams_pool_size STATIC 20100324 1 30 rows selected.
So except the 25th of March not much resize operations...
So then do we have free memory in the shared pool or it is 100% use:
SQL> select owner,type,sum(sharable_mem) from v$db_object_cache group by owner, type order by 3 desc;
OWNER TYPE SUM(SHARABLE_MEM)
---------------------------------------------------------------- ---------------------------- -----------------
CURSOR 1549134231
SYS JAVA CLASS 8416217
SYS PACKAGE BODY 1922654
SYS PACKAGE 1669290
ABPP_OWNER3 TABLE 1652995
SYS VIEW 1169342
ABPP_OWNER3 SUMMARY 977659
SYS TABLE 879018
ABPP_OWNER3 PACKAGE BODY 487022
PERSIST_OWNER3 TABLE 426983
PUBLIC SYNONYM 394513
SYS TYPE 147198
ABPP_OWNER3 PROCEDURE 124022
DBSNMP PACKAGE BODY 123988
SYS INVALID TYPE 116849
ABPP_OWNER3 PACKAGE 109590
SYS INDEX 76002
DMNGST2STE SUMMARY 75255
SYS JAVA SHARED DATA 65999
DBSNMP PACKAGE 45508
DMNGST2STE TABLE 40716
SYS TRIGGER 33384
EXFSYS TRIGGER 27574
DBSNMP TABLE 27320
SYS FUNCTION 22940
ABPP_OWNER3 INDEX 22051
SYSTEM TABLE 19645
DMNGST2STE SYNONYM 18470
SYS CLUSTER 17888
SYS SEQUENCE 15144
SYS TYPE BODY 13785
DBSNMP NON-EXISTENT 13444
SYS PROCEDURE 10984
SYS QUEUE 10967
WMSYS TABLE 10151
ABPP_OWNER3 VIEW 6740
ABPP_OWNER3 NON-EXISTENT 6735
SYS NON-EXISTENT 6727
WMSYS VIEW 6712
SYS RSRC CONSUMER GROUP 5117
DMNGST2STE INVALID TYPE 4192
SYS PUB_SUB 3660
PM_DW_OWNER3 TABLE 3405
SYS LIBRARY 3368
WMSYS NON-EXISTENT 3357
PUBLIC NON-EXISTENT 3351
SYSTEM VIEW 2796
SYS RULESET 2791
DMNGST2STE PUB_SUB 2149
ABPP_OWNER3 PUB_SUB 1799
DBSNMP PUB_SUB 1789
OVO PUB_SUB 1783
DBSNMP SEQUENCE 1695
PERSIST_OWNER3 PUB_SUB 1693
SYS DIRECTORY 1689
YJAQUIER PUB_SUB 1681
OUTLN TABLE 1673
ABPP_OWNER3 NOT LOADED 0
YJAQUIER NOT LOADED 0
OVO NOT LOADED 0
DBSNMP NOT LOADED 0
M NOT LOADED 0
EXFSYS NOT LOADED 0
PERSIST_OWNER3 NOT LOADED 0
SYS NOT LOADED 0
TOAD NOT LOADED 0
ADDRESS1 NOT LOADED 0
PUBLIC NOT LOADED 0
SYSTEM NOT LOADED 0
NOT LOADED 0
B1 NOT LOADED 0
DMNGST2STE NOT LOADED 0
MD NOT LOADED 0
WMSYS NOT LOADED 0
DMNGSTE NOT LOADED 0
75 rows selected.
Remark:
The NULL value with CURSORS kept in memory (first row of the query) is in fact BUG:4577168 (see Metalink note ID 726589.1). The suggested Oracle workaround is to often flush the shared pool...
SQL> alter system flush shared_pool; System altered.
Just to have the global usage of the shared pool:
select sum(sharable_mem) from v$db_object_cache;
SUM(SHARABLE_MEM)
-----------------
1477505020
So over the more than 6GB allocated to the shared pool only 1.5GB is used... You could also confirm it with:
select * from v$sgastat where name like '%free memory%'; POOL NAME BYTES ------------ -------------------------- ---------- shared pool free memory 4097863320 large pool free memory 16285672 java pool free memory 9261568
To have all the memory advisors figures in below queries you may need to issue:
alter system set db_cache_advice = on;
select name,block_size,size_for_estimate,size_factor,estd_physical_reads,estd_physical_read_time from v$db_cache_advice; NAME BLOCK_SIZE SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READS ESTD_PHYSICAL_READ_TIME -------------------- ---------- ----------------- ----------- ------------------- ----------------------- DEFAULT 32768 480 .0974 126555967 858529 DEFAULT 32768 960 .1948 95527592 639556 DEFAULT 32768 1440 .2922 70053332 459780 DEFAULT 32768 1920 .3896 47950614 303797 DEFAULT 32768 2400 .487 32936234 197838 DEFAULT 32768 2880 .5844 24313901 136989 DEFAULT 32768 3360 .6818 19389652 102238 DEFAULT 32768 3840 .7792 16685771 83156 DEFAULT 32768 4320 .8766 15174652 72492 DEFAULT 32768 4800 .974 14334225 66561 DEFAULT 32768 4928 1 14181473 65483 DEFAULT 32768 5280 1.0714 13835582 63042 DEFAULT 32768 5760 1.1688 13514407 60775 DEFAULT 32768 6240 1.2662 13273420 59075 DEFAULT 32768 6720 1.3636 13062867 57589 DEFAULT 32768 7200 1.461 12847647 56070 DEFAULT 32768 7680 1.5584 12610469 54396 DEFAULT 32768 8160 1.6558 12353767 52584 DEFAULT 32768 8640 1.7532 12094386 50754 DEFAULT 32768 9120 1.8506 11850326 49032 DEFAULT 32768 9600 1.9481 11592749 47214 21 rows selected.
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
---------- --------------- ------------ ------------------- -------------------
2560 .25 4816655 1.0694 16831134
5120 .5 4631087 1.0282 6197391
7680 .75 4559472 1.0123 6197391
10240 1 4504072 1 4470777
12800 1.25 4501370 .9994 4240085
15360 1.5 4495064 .998 3747852
17920 1.75 4493713 .9977 3647707
20480 2 4493713 .9977 3647707
8 rows selected.
So we can see that increasing the database cache would not decrease the number of physical reads and apparently Oracle is keeping the free memory in the shared pool. In this particular situation we are "wasting" a bit server memory...
The good buffer cache hit ratio can be confirmed with (Metalink note ID 33883.1):
SELECT name, 100*(1-(physical_reads / (consistent_gets + db_block_gets))) "Hit Ratio" FROM v$buffer_pool_statistics WHERE (consistent_gets + db_block_gets) != 0; NAME Hit Ratio -------------------- ---------- DEFAULT 95.006824
Remark:
If you have set SGA_TARGET and, for example, db_16k_cache_size then the above query is not working as the different block size pools will have the same name !!!, so better use:
SELECT a.name, b.block_size, 100*(1-(physical_reads / (consistent_gets + db_block_gets ))) "Hit Ratio" FROM v$buffer_pool_statistics a, v$buffer_pool b WHERE a.id=b.id AND (consistent_gets + db_block_gets) != 0; NAME BLOCK_SIZE Hit Ratio -------------------- ---------- ---------- RECYCLE 4096 100 DEFAULT 16384 61.2491019 DEFAULT 4096 92.2253106




