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.

sga-breakdown
sga-breakdown

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>