Tunning Sga Oracle 10g

Oracle 10g, dynamic SGA and automatic memory management. http://www.insight-tec.com/en/mailmagazine/vol194.html This dynamic SGA, as a new feature since Oracle 9i, allows changing the SGA configuration without stopping the instance. Expansion and shrinkage # Default buffer cache, shared pool, large pool and process private memory can be changed dynamically. Expansion: up to SGA_MAX_SIZE Shrinkage: down to the minimum size defined by Oracle (the limit suggested according to the O/S) It can be altered by ALTER SYSTEM SET statement. # The expansion and the shrinkage of buffer pool and SGA pool are executed by Oracle internal management. If the component takes memory, the internal automatic tuning will transmit memory from other components. Note: The buffer pool will be shrinked and the shared pool will be expanded. Some points that look different in Oracle 10g # Java pool and streams pool can be changed dynamically. Note: The SGA component Streams pool is available since Oracle 10g. # The memory managament method has changed very much. Initial parameters are simplified, SGA_TARGET is added, and we do not have to specify SGA memory parameters anymore. SGA configuration can be determined without specifying SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE,DB_CACHE_SIZE, etc. They can still be defined seperately, but automatic management will not be executed then. # Be carefule that STATISTICS_LEVEL parameter must be either TYPICAL or ALL. # The SGA configuration memory parameters are organized and can be checked in the newly added V$SGAINFO dynamic view. Granule size and other information are included in this view. # Changes in SGA size are reflected synchronously in Oracle 9i but with some delay in Oracle 10g. Therefore, a new flag value is defined in the dynamic view. Ex ﾁ jValues added or deleted in V$SGA_RESIZE_OPS OPER_TYPE: Operation type STATIC (ADDED) INITIALIZING (ADDED) DISABLED (ADDED) SHRINK_CANCEL (ADDED) OPER_MODE:Operation mode DISABLED (ADDED) IMMEDIATE (ADDED) AUTO (DELETED) STATUS:Job-end INACTIVE PENDING COMPLETE

status (ADDED) (ADDED) (ADDED)

CANCELLED NORMAL CANCEL

(ADDED) (DELETED) (DELETED)

Let's expand and shrink the memory dynamically. We will expand the shared pool, and the default buffer pool will shrink or expand accordingly. In Oracle 10g, SGA_TARGET is also an important value as the trigger of expansion or shrinkage. Environment Linux RAC10g1 2.4.9-e.40smp Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod SGA is defined as 176MB. NAME -----------------------------------sga_max_size sga_target

TYPE ----------big integer big integer

VALUE ----------176M 176M

We can see the size of SGA by referring to the newly added dynamic view. One granule is 4MB in this environment.

SQL> select * from V$SGAINFO; NAME BYTES -------------------------------- ---------Fixed SGA Size 778016 Redo Buffers 524288 Buffer Cache Size 117440512 Shared Pool Size 54525952 Large Pool Size 4194304 Java Pool Size 4194304 Streams Pool Size 0 Granule Size 4194304 Maximum SGA Size 184549376 Startup overhead in Shared Pool 33554432 Free SGA Memory Available 0 11 rows are selected.

RES --No No Yes Yes Yes Yes Yes No ->‚Pgranule No No

Expansion of shared pool We will expand the shared pool from 52MB to 56MB. NAME -----------------------------------__shared_pool_size streams_pool_size __db_cache_size

TYPE ----------big integer big integer big integer

VALUE ----------52M 0 112M

SQL> ALTER SYSTEM SET shared_pool_size ='56M' SCOPE=MEMORY SID='ora101'; System is changed. The time this process takes depends on the specified size. Let's confirm that the size is expanded.

NAME -----------------------------------__shared_pool_size shared_pool_size __db_cache_size

TYPE ----------big integer big integer big integer

VALUE ----------56M 56M 108M

The expansion size of shared pool is equal to the shrinkage size of default buffer cache. Let's check the dynamic view that is related with SGA. It is shown that db_cache_size SHRINKS to 113246208 (108MB) and shared_pool_size GROWS to 58720256 (56MB). Note: FINAL_SIZE shows the final size.

SQL> select COMPONENT,OPER_TYPE,OPER_MODE,PARAMETER, INITIAL_SIZE,TARGET_SIZE,FINAL_SIZE,STATUS, to_char(START_TIME,'YYYY/MM/DD HH24:MI:SS')as START_TIME, to_char(END_TIME, 'YYYY/MM/DD HH24:MI:SS')as END_TIME from V$SGA_RESIZE_OPS ; COMPONENT OPER_TYPE ------------------------ --------------------------DEFAULT buffer cache SHRINK db_cache_size shared pool GROW shared_pool_size

OPER_MODE PARAMETER --------MANUAL MANUAL

INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS ------------ ----------- ---------- --------117440512 113246208 113246208 COMPLETE ^^^(108MB) 54525952 58720256 58720256 COMPLETE ^^(56MB) START_TIME ------------------2004/07/11 15:32:13 2004/07/11 15:32:13

END_TIME ------------------2004/07/11 15:32:13 2004/07/11 15:32:13

Although it seems easy this time, sometimes it takes long for ALTER statement to get response after we execute the same process several times. Be carefule when you want to apply it to the real system

We examined the expansion of the shared pool last week. Next, let's check how the shrinkage changes the SGA component. Environment Linux RAC10g1 2.4.9-e.40smp

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod Expansion of shared pool The shared pool is expanded from 52MB to 56MB. NAME -----------------------------------__shared_pool_size streams_pool_size __db_cache_size

TYPE ----------big integer big integer big integer

VALUE ----------52M 0 112M

SQL> ALTER SYSTEM SET shared_pool_size ='56M' SCOPE=MEMORY SID='ora101'; System is changed. NAME -----------------------------------__shared_pool_size shared_pool_size __db_cache_size

TYPE ----------big integer big integer big integer

VALUE ----------56M 56M 108M

shared_pool_size GROWS to 58720256 (56MB). db_cache_size SHRINKS to 113246208 (108MB) and In Oracle 9i, when memory size is changed dynamically, the size changes will be reflected synchronously in MANUAL mode. However, in Oracle 10g, such changes are reflected with some delay in INTERNAL mode. SGA_TARGET is an important value as the trigger of expansion or shrinkage. Shrinkgae of the shared pool The shared pool was once expanded to 56MB, and we now want to shrink it to 52MB. SQL> ALTER SYSTEM SET shared_pool_size ='52M' SCOPE=MEMORY SID='ora101'; System is changed. Even if the shared pool parameter seems to be back, the buffer cache does not change. When will resizing start? NAME TYPE VALUE ------------------------------------ ----------- ----------__shared_pool_size big integer 56M shared_pool_size big integer 52M __db_cache_size big integer 108M ﾁ@ db_cache_size big integer 0 In the dynamic view, it is not recognized as SGA blank space. SQL> select * from V$SGA_DYNAMIC_FREE_MEMORY; CURRENT_SIZE -----------0 Nothing is different in V$SGA_RESIZE_OPS. We don't really feel like waiting anymore.... Push the button.....

By changing SGA_TARGET, which is newly added in Orcle 10g, recounting will be executed due to automatic memory management. Since it is 176MB, as large as SGA_MAX_SIZE, let's change it to 168MB. Be carefule it can not exceed SGA_MAX_SIZE. NAME -----------------------------------lock_sga pre_page_sga sga_max_size sga_target

TYPE VALUE ----------- ----------boolean FALSE boolean FALSE big integer 176M big integer 176M

SQL> ALTER SYSTEM SET sga_target ='168M' SCOPE=MEMORY SID='ora101'; System is changed. NAME -----------------------------------sga_max_size sga_target

TYPE ----------big integer big integer

VALUE ----------176M 168M

176MB-168MB=8MB blank space is recognized. SQL> select * from V$SGA_DYNAMIC_FREE_MEMORY; CURRENT_SIZE -----------8388608 The result of resizing is shown in V$SGA_RESIZE_OPS. Default buffer cache is adjusted to 100MB according to SGA_TARGET. Moreover, DEFERRED mode, which is not available in Oracle 9i, is displayed now.

COMPONENT OPER_TYPE OPER_MODE PARAMETER ------------------------ ------------- -------------------DEFAULT buffer cache SHRINK DEFERRED db_cache_size ^^^^^^^^ INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS ------------ ----------- ---------- --------113246208 104857600 104857600 COMPLETE ^^^^^^^^^ START_TIME END_TIME ------------------- ------------------2004/07/11 15:35:23 2004/07/11 15:35:23 NAME TYPE VALUE ------------------------------------ ----------- -----------

__db_cache_size db_cache_size

big integer 100M big integer 0

After a while... NAME -----------------------------------__db_cache_size db_cache_size

TYPE ----------big integer big integer

VALUE ----------96M 0

The buffer cache is shrinked. In V$SGA_RESIZE_OPS, IMMEDIATE is shown, Java pool is expanded, and the default buffer pool is shrinked from 100MB to 96MB. COMPONENT -----------------------java pool DEFAULT buffer cache

OPER_TYPE ------------GROW SHRINK

OPER_MODE --------IMMEDIATE IMMEDIATE ^^^^^^^^^

PARAMETER -------------java_pool_size db_cache_size

INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS ------------ ----------- ---------- --------4194304 8388608 8388608 COMPLETE 104857600 100663296 100663296 COMPLETE ^^^^^^^^^ START_TIME ------------------2004/07/11 16:00:17 2004/07/11 16:00:17

END_TIME ------------------2004/07/11 16:00:17 2004/07/11 16:00:17

As we have shown above, once SGA_TARGET parameter is changed, the optimal size will be determined to fit the workload according to internal tuning algorithm. SGA component size will be changed dynamically. Memory expansion and shrinkage of SGA components In Oracle 10g, it is not required anymore to set up the sizes of all SGA components, which include shared pool, Java pool, large pool and default buffer cache. By specifying only the total size of SGA with the initial parameter sga_target, the size of each component will be modified automatically. (Even if we do not set up the initial parameter, the size of each component will still be modified automatically according to the system load. In fact, the initial parameter assigns the minimum size of each component. The shared pool, Java pool, large pool and default buffer cache work as source of memory; therefore, whenever the system detects insufficiency of space after modifying the components automatically, the expansion of the space will be executed automatically. Furthermore, the memory space will not move among the components such as shared pool, Java pool and large pool. Let's start the examination. In order to make the system recognize the insufficiency of shared pool size is insufficient, we executed a lot of SQL statements to decrease the library cache hit rate. (After creating a table, we execute a lot of SQL statements in PL/SQL without using any bind variable. This time we execute 3000 rows of insert statements.) BEGIN INSERT INTO TBL1(t1,t2,t3,t4) VALUES (i,(i*100),(i*100),(i*100));

INSERT INTO TBL1(t1,t2,t3,t4) VALUES (i,(i*100),(i*100),(i*101)); INSERT INTO TBL1(t1,t2,t3,t4) VALUES (i,(i*100),(i*100),(i*102)); ﾁ@ﾁ@ﾁ idetails unwritten....ﾁ j END; / Confirm the expansion or shrinkage of the components. SQL> select to_char(START_TIME,'YYYY/MM/DD HH24:MI:SS') as "ProcessTime", COMPONENT as "Component", OPER_TYPE as "Operation", (FINAL_SIZE-INITIAL_SIZE)/1024/1024 as "Difference(MB)", FINAL_SIZE/1024/1024 as "Size(MB)" from V$SGA_RESIZE_OPS; ProcessTime Size(MB) ---------------------------2004/08/10 23:00:04 76 2004/08/10 23:00:04 40 2004/08/10 23:23:03 72 2004/08/10 23:23:03 44

Component

Operation

Difference(MB)

-------------------- ------------ ------------DEFAULT buffer cache SHRINK shared pool

GROW

DEFAULT buffer cache SHRINK shared pool

GROW

-4 4 -4 4

It looks like the memory space allocation to the shared pool is reflected and the space is always allocated from buffer cache. The size increases or decreases by the unit of 4KB (1 granule). When the space is allocated from the default buffer cache If the default buffer cache shrinks to its lower limit, what will happen? Confirm the expansion or shrinkage of the components. SQL> select to_char(START_TIME,'YYYY/MM/DD HH24:MI:SS') as "ProcessTime", COMPONENT as "Component", OPER_TYPE as "Operation", (FINAL_SIZE-INITIAL_SIZE)/1024/1024 as "Difference(MB)", FINAL_SIZE/1024/1024 as "Size(MB)" from V$SGA_RESIZE_OPS; ProcessTime Size(MB) ---------------------------2004/08/10 23:00:04 76 2004/08/10 23:00:04 40 2004/08/10 23:23:03 72 2004/08/10 23:23:03 44

Component

Operation

Difference(MB)

-------------------- ------------ ------------DEFAULT buffer cache SHRINK shared pool

GROW

DEFAULT buffer cache SHRINK shared pool

GROW

-4 4 -4 4

It looks like the memory space allocation to the shared pool is reflected here and the space is always allocated from buffer cache. The size increases or decreases by the unit of ‚SKB (1 granule). When the space is allocated from the default buffer cache If the default buffer cache shrinks to its lower limit, what will happen? Current parameters (Specified by the NAME -----------------------------------sga_max_size sga_target db_cache_size

initial parameter) TYPE VALUE ----------- ------big integer 140M big integer 128M big integer 68M ﾁ©The lower limit

Current value of buffer pool SQL> select name,bytes/1024/1024 as "Byte(MB)" from v$SGASTAT where name = 'buffer_cache'; NAME Byte(MB) -------------------------- ---------buffer_cache 80 We executed a lot of SQL statements to decrease the library cache hit rate. This is the result of shrinkage. ProcessTime Size(MB) ---------------------------2004/08/11 16:22:42 2004/08/11 16:22:42 2004/08/11 16:22:49 2004/08/11 16:22:49 2004/08/11 16:24:14 68 ﾁ@* 2004/08/11 16:24:14

Component

Operation

Difference(MB)

-------------------- ---------- -------------DEFAULT buffer cache SHRINK shared pool GROW DEFAULT buffer cache SHRINK shared pool GROW DEFAULT buffer cache SHRINK shared pool

GROW

-4 4 -4 4 -4

76 40 72 44

4

48

RELOADS of the library cache increased 6 times. NAMESPACE GETS GETHIT PINS PINHIT RELOADS -------------- ------ ------ ------ ------ ------SQL AREA 18471 0.50 74567 0.61 6091 The size of buffer cache shrinked to 68MB. SQL> select name,bytes/1024/1024 as "Byte(MB)" 2 from v$SGASTAT where name = 'buffer_cache'; NAME Byte(MB) -------------------------- ---------buffer_cache 68 Let's alter it dynamically. We will increase the shared pool. SQL> alter system set shared_pool_size=49M; alter system set shared_pool_size=49M * Errors occurred at row 1. ORA-02097: parameter cannot be modified because specified value is

invalid ORA-04033: Insufficient memory to grow pool It does not work. It seems like the lower limit is taken as the stopper. The key point is the upper limit of sga_target. SQL> select trunc(sum(bytes)/1024/1024) from v$SGASTAT ; TRUNC(SUM(BYTES)/1024/1024) --------------------------128 Increase sga_target to sga_max_size. SQL> alter system set sga_target=140M; System changed. NAME -----------------------------------sga_max_size sga_target

TYPE ----------big integer big integer

VALUE ---------140M 140M

Expand the shared pool again. SQL> alter system set shared_pool_size=49M; System changed. It worked well this time. Judging from the result of expansion, the size of default buffer cache has been expanded. Due to the automatic modification, the lower limit of default buffer cache is always the upper limit of sga_target (maximum sga_max_size). ProcessTime Size(MB) ---------------------------2004/08/11 16:22:42 2004/08/11 16:22:42 2004/08/11 16:22:49 2004/08/11 16:22:49 2004/08/11 16:24:14 2004/08/11 16:24:14 2004/08/11 16:26:10 * 2004/08/11 16:26:10

Component

Operation

Difference(MB)

-------------------- ---------- -------------DEFAULT buffer shared pool DEFAULT buffer shared pool DEFAULT buffer shared pool DEFAULT buffer shared pool

cache SHRINK GROW cache SHRINK GROW cache SHRINK GROW cache SHRINK GROW

-4 4 -4 4 -4 4 -4

76 40 72 44 68 48 76

4

52

Moreover, even if the size of default buffer cache is insufficient, the system does not shrink the shared pool or other components to expand the default buffer cache. We guess that # when the size of buffer cache is insufficient, the status becomes standby and errors do not occur. # insufficiency of shared pool or other components is a more serious problem than buffer cache standby. Allocating space from the shared pool or other components might deteriorate the situation. # when the size of shared pool, large pool and Java pool is insufficient, ORA-4031(unable to allocate string bytes of shared memory) and ORA-29554(Unhanded Java out of memory condition) occur and user processing will fail. Review We have confirmed a dynamic SGA action. When the size of shared pool, Java pool or large pool is

insufficient, the default buffer cache will shrink automatically;on the other hand, the shared pool, Java pool and large pool will expand. Moreover, memory space will not be adjusted among these three components. (shared pool, large pool, java pool) After the sizes of components are modified automatically, will the space return back to the default buffer cache? (1)shared pool-< default buffer cache (2) Java pool-< default buffer cache (3) large pool-< default buffer cache. Test 1 (1)Clear the shared pool. (alter system flush shared_pool) (2)Confirm that the library cache hit rate is good. (3)Decrease the buffer cache hit rate (In this examination, we lower it from 98% to 83%.) select round(100 * ( ( max(decode(name,'db block gets',value)) + max(decode(name,'consistent gets',value)) - max(decode(name,'physical reads',value))) / ( max(decode(name,'db block gets',value)) + max(decode(name,'consistent gets',value)))),2) HIT_RATIO from v$sysstat / HIT_RATIO ---------83.01 The sizes of SGA components have not changed at all. TIME -------------------------2004/08/23 15:03:44 COMPLETE 2004/08/23 15:03:44 COMPLETE 2004/08/23 15:03:58 COMPLETE 2004/08/23 15:03:58 COMPLETE

COMPONENT OPER_TYPE GAP Final Size STATUS -------------------- --------- --- ---------DEFAULT buffer cache SHRINK shared pool

GROW

DEFAULT buffer cache SHRINK shared pool

GROW

-4

76

4

40

-4

72

4

44

The manual does not mention about the logic of the expansion of default buffer cache. In this case, is it possible to alter it manually, then? Test 2 Let's expand the default buffer cache manually. shared pool (from 44MB to 40MB) * shrinked default buffer cache (from 72MB to 76MB) *expanded sga_target (from 128MB to 140MB: sga_max_size) TIME -------------------------2004/08/23 15:03:58 COMPLETE 2004/08/23 15:03:58 COMPLETE

COMPONENT OPER_TYPE GAP Final Size STATUS -------------------- --------- --- ---------DEFAULT buffer cache SHRINK shared pool

GROW

-4

72

4

44

SQL> alter system set sga_target=140M; System changed. SQL> alter system set shared_pool_size=40M; System changed. SQL> alter system set db_cache_size=76M; System changed. The sizes of SGA components have not change at all. It seems that default buffer cache cannot even be executed manually. Moreover, we also failed to shrink the shared pool manually. (In this case, should we take specifying "expansion" of shared pool as the trigger of recalculation?) * In Oracle 9.2 , it is possible to shrink the shared pool manually. We expand the shared pool since sga_target seems to have some more margin. SQL> alter system set shared_pool_size=48M; System changed. TIME -------------------------2004/08/23 15:09:03 COMPLETE 2004/08/23 15:09:03 COMPLETE

COMPONENT OPER_TYPE GAP Final Size STATUS -------------------- --------- --- ---------DEFAULT buffer cache SHRINK shared pool

GROW

-4

80

4

48

The shared pool has been expanded. The remaining space in the sga_target range is allocated to default buffer cache and recalculated. (from 72MB to 80MB) As a result, default buffer cache is also expanded. Results Although there is some ramaining space in the shared pool, Java pool and large pool and the space of default buffer cache is insufficient, size change does not occur in dynamic SGA. Therefore, when the application enters standby mode due to insufficient space of buffer cache, we cannot expect dynamic SGA to solve this problem. If you really want to increase the size of default buffer cache, it is possible to increase sga_target manually and modify the space of shared pool, Java pool and larege pool (when sga_max_size>=sga_target). (The remaining space will be allocated to default buffer cache and the difference may be more than the current default buffer cache.) It might be difficult to determine automatically that there is remaining space in the shared pool, Java pooland large pool, but it is surprising that it is also impossible to shrink the components manually. There is still another case. When sga_target<=total size of SGA components, and an expansion error occurrs after we execute alter system set db_cache_size="size to be expanded" (it is not surprising since there is no remaining space in SGA), the following information is recorded in V$SGA_RESIZE_OPS dynamic view. TIME ------------------2004/08/23 15:06:13 2004/08/23 15:06:13

COMPONENT -------------------shared pool DEFAULT buffer cache

OPER_TYPE GAP Final Size STATUS --------- --- ---------- -----SHRINK 0 44 ERROR GROW 0 72 ERROR

It can be observed that we have tried to shrink the shared pool and expand the default buffer cache(GROW). It means that such a kind of logic does exist. 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 STATUS -------------------------2004/08/31 14:40:56 COMPLETE 2004/08/31 14:40:56 COMPLETE

COMPONENT

OPER_TYPE

GAP Final Size

-------------------- --------- ---- ---------DEFAULT buffer cache SHRINK shared pool

GROW

2004/08/31 14:42:00 DEFAULT buffer cache SHRINK COMPLETE

-4

76

4

40

-4

72

2004/08/31 14:42:00 shared pool COMPLETE

GROW

4

44

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.