|
Dynamic SGA and automatic memory management
|
Hello, everyone. We would like to continue the topic "Dynamic SGA and automatic memory management."
Environment
Windows XP Pro + SP1
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
Question
Dynamic SGA feature in Oracle Database 10g can allocate the space of default buffer cache to the insufficient shared pool, Java pool and large pool.
However, when and how does the system determine if the shared pool, Java pool or large pool is insufficient?
Let's focus on the shared pool to investigate the timing and the value that is referred to.
There are several possible timings such as:
(1) when library cache hit rate is low
(2) when dictionary cache hit rate is low
(3) when it is necessary to obtain a continuous range larger than shared_pool_reserved
(4) when it is necessary to expand UGA that is obtained in the shared pool in MTS environment
We would like to test a case when library cache hit rate is low by checking the values in the internal dynamic view.
When library cache hit rate is low
(1) Clear shared pool (alter system flush shared_pool)
(2) Confirm that library cache hit rate is good
select sum(pins) total_pins, sum(reloads) total_reloads,
to_char((1-sum(reloads)/sum(pins))*100,'990.99') || '%' "hit Lib"
from v$librarycache;
TOTAL_PINS TOTAL_RELOADS hit Lib
---------- ------------- --------
12989 204 98.43%
|
(3) Execute a lot of SQL statements without using bind variables
(4) Library cache hit rate has become low
TOTAL_PINS TOTAL_RELOADS hit Lib
---------- ------------- --------
52429 8970 82.89%
|
(5) Confirm that dictionary cache hit rate is good.
select sum(gets) total_gets, sum(getmisses) total_misses,
to_char((1-sum(getmisses)/sum(gets))*100,'990.99') || '%' "hit Dic"
from v$rowcache;
TOTAL_GETS TOTAL_MISSES hit Dic
---------- ------------ --------
64511 6543 89.86%
|
(6) Shared pool is expanded.
select to_char(START_TIME,'YYYY/MM/DD HH24:MI:SS') as Time,
COMPONENT,OPER_TYPE,(FINAL_SIZE-INITIAL_SIZE)/1024/1024 as Gap,
FINAL_SIZE/1024/1024 as "Final Size",STATUS
from V$SGA_RESIZE_OPS;
TIME COMPONENT OPER_TYPE GAP Final Size STATUS
------------------- -------------------- --------- ---- ---------- --------
2004/08/31 14:40:56 DEFAULT buffer cache SHRINK -4 76 COMPLETE
2004/08/31 14:40:56 shared pool GROW 4 40 COMPLETE
2004/08/31 14:42:00 DEFAULT buffer cache SHRINK -4 72 COMPLETE
2004/08/31 14:42:00 shared pool GROW 4 44 COMPLETE
|
We have introduced the processes shown above for the past few weeks.
The fact that library cache hit rate is low seems to be one of the indicator to determine shared pool should be expanded. However, we do not know exactly how many percent is the threshold.
In order to investigate the threshold, let's check v$shared_pool_advice dynamic view.
It includes the information about the estimated analysis time of shared pool. (The history is kept in dba_hist_shared_pool_advice.)
SQL> desc v$shared_pool_advice
NAME NULL? PATTERN
----------------------------- ----- ------
SHARED_POOL_SIZE_FOR_ESTIMATE NUMBER
SHARED_POOL_SIZE_FACTOR NUMBER
ESTD_LC_SIZE NUMBER
ESTD_LC_MEMORY_OBJECTS NUMBER
ESTD_LC_TIME_SAVED NUMBER
ESTD_LC_TIME_SAVED_FACTOR NUMBER
ESTD_LC_LOAD_TIME NUMBER@*Added since Oracle 10g
ESTD_LC_LOAD_TIME_FACTOR NUMBER@*Added since Oracle 10g
ESTD_LC_MEMORY_OBJECT_HITS NUMBER
|
Titles are organized in the order from A to I.
(Initial status: shared pool 36MB)
A B C D E F G H I
---- ------- ---- ----- ---- ------ ---- ------- ------
32 .8889 4 780 6 1 6 1 1444
* 36 1 4 780 6 1 6 1 1444
40 1.1111 4 780 6 1 6 1 1444
44 1.2222 4 780 6 1 6 1 1444
48 1.3333 4 780 6 1 6 1 1444
52 1.4444 4 780 6 1 6 1 1444
56 1.5556 4 780 6 1 6 1 1444
60 1.6667 4 780 6 1 6 1 1444
64 1.7778 4 780 6 1 6 1 1444
68 1.8889 4 780 6 1 6 1 1444
72 2 4 780 6 1 6 1 1444
|
(First expansion: shared pool 40MB)
A B C D E F G H I
---- ------- ---- ----- ---- ------ ---- ------- ------
32 .8 4 1067 7 1 8 1 1646
36 .9 7 1788 7 1 8 1 1658
* 40 1 8 2145 7 1 8 1 1658
44 1.1 8 2145 7 1 8 1 1658
48 1.2 8 2145 7 1 8 1 1658
52 1.3 8 2145 7 1 8 1 1658
56 1.4 8 2145 7 1 8 1 1658
60 1.5 8 2145 7 1 8 1 1658
64 1.6 8 2145 7 1 8 1 1658
68 1.7 8 2145 7 1 8 1 1658
72 1.8 8 2145 7 1 8 1 1658
76 1.9 8 2145 7 1 8 1 1658
80 2 8 2145 7 1 8 1 1658
|
(Second expansion: shared pool 44MB)
A B C D E F G H I
---- ------- ---- ----- ---- ------ ---- ------- ------
36 .8182 8 2544 13 .9286 25 1.0417 30467
* 44 1 15 4693 14 1 24 1 34032
52 1.1818 22 6761 14 1 24 1 34150
60 1.3636 22 6810 14 1 24 1 34150
68 1.5455 22 6810 14 1 24 1 34150
76 1.7273 22 6810 14 1 24 1 34150
84 1.9091 22 6810 14 1 24 1 34150
92 2.0909 22 6810 14 1 24 1 34150
|
The number and interval of record depend on the size of shared pool.
(A) is the estimated size of shared pool. For the record whose estimated shared pool size matches the current size, its size factor.
(B) will become 1. Other distribution records will be created according to that record. These are items which have increased obviously.
(C) is the estimated occupied memory size in library cache;
(D) is the estimated number of library cache memory object;
(E) is the estimated required time (in second) for the object deleted from the memory to reload;
(G) is the estimated elapsed time (in second) to execute analysis in shared pool; and
(I) is the estimated number of time that library cache memory object is detected.
The dynamic view is really helpful to have a grasp of shared pool.
This is the last time for this topic. We will start a new topic next week. Pleae look foward to it!
Hideaki Oishi
|
|
