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 |
About Post Author
