A brief introduction to Shared pool pool and result set caching technology

Subpool Technology and Advantages:

Starting with Oracle 9i, Shared pool can be split into multiple child buffer pools (Subpool) for management to increase concurrency and reduce competition.

Each subpool of shared pool can be viewed as a mini Shared pool with its own independent free List, memory structure, and LRU list, shared pool latch. At the same time, Oracle provides multiple latch to manage each child buffer pool, thus avoiding the competition of individual latch (shared Pool Reserved area is also split-managed). Starting from 10G, each Subpool consists of 4 sub partition.

Number and size of Subpool

Each four CPUs are allocated one subpool, up to 7. The Shared Pool latch also increased from one original to 7 now.

In Oracle 9i, each subpool is at least 128MB.

10g-10.2.0.3, at least 256MB for each subpool

After the 10.2.0.3, the minimum is 512M.

_kghdsidx_count implied parameter: When Oracle starts, optimization sets the Subpool quantity according to this parameter.

Subpool Disadvantages:

Starting with the Oracl 10G, the Oracle process has failed to request memory in a subpool, and the request to the next subpool---too small subpool fragmentation problem may be more serious--ora-4031 more likely to occur.

Too much subpool can also lead to higher management coordination costs.

For example, the following error is related to Subpool:

ora-04031:unable to allocate 4216 bytes of shared memory

("Shared Pool", "Unknown Object", "SGA Heap (2,0)", "Library cache")

Shared Pool Size judgment

LRU chain table is divided into two: transient LRU (instantaneous LRU), recurrent (cycle LRU). Chunk is placed in the instantaneous LRU when used for the first time, and is moved to the cycle LRU when used for the second time.

The DSI recommends the following SQL statistics LRU and related information:

Select Kghlutrn TRANSIENT,KGHLURCR Recurrent, Kghlufsh Flush_chunk_number, Kghluops pin_or_release_operations from X$ Kghlu;

--Queries out of the database since the start of the execution alter SYSTEM FLUSH Shared_pool does not clear zero.

which

TRANSIENT, instantaneous LRU chain length.

Recurrent, cycle LRU chain length.

Number of Flush_chunk_number,flush CHUNK.

The number of times the chunk operation was Pin_or_release_operations,pin or released.

If the length of the instantaneous LRU chain is more than 3 times times the length of the cycle LRU chain, a large amount of only one chunk is stacked in LRU, indicating that the shared pool is too large.

If the flush chunk number and pin, and the number of chunk operations are less than 1:20, the shared pool is too small. This ratio, in simple words, Oracle believes that every 20 operations chunk, only one flush chunk operation, or acceptable. If it's any lower, the shared pool is a little small.

The ##### #Chunk数和Pin, the number of free chunk operations is less than 1:20, indicating that the shared pool is too small.

This is wrong, it should be greater than 20 per 1 (once every 20 operations need flush CHUNK), such as very 1 (every 10 operations need to flush CHUNK), indicating that the shared pool is too small.

Shared pools generally do not exceed 10g,5, 6G. Large shared pools require greater management costs

#####################################################################

Result set caching--result cache

Db_buffer can only cache visited blocks, which partially solves the problem of physical reading, and queries still require a lot of logical reading.

Materialized views provide the function of query rewriting, for the most part, it simplifies the complexity of SQL, even if it satisfies the condition of query, the scan of materialized view is unavoidable.

Cache result set--result cache is very different, it caches the results of the query. Does not require a large amount of logical reads, does not require any complex computations, but directly returns the cached results. Almost all of the cost of---SQL can be avoided. These costs include parsing time, logical reading, physical reading, and arbitrary contention that may normally be encountered.

Result cache results are visible to all sessions.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/storage/

The result cache satisfies the consistency read, and when the cached table data is changed, Oracle automatically invalidate the cached results set:

Whether to use Result_cache is not related to Oracle's execution plan:

Result cache ignores the execution plan of the SQL statement, even if the execution plan changes, and Oracle will still use the results cache if the resulting outcome is the same.

Note that Oracle using result cache may get incorrect results when the execution plan is different and results are not the same.

Using result cache for SQL intermediate results must be enforced using the Result_cache hint.

Result cache functions are not valid for the following conditions: system tables and temporary tables, nextval and currval of sequences, Sysdate, Systimestamp, and all non-deterministic pl/sql functions.

Result_cache and No_result_cache

Oracle has added two Hint,result_cache and No_result_cache. With these two hints, you can clearly indicate whether the following statement is a result CACHE.

Oracle also added several initialization parameters to manage result CACHE functions, such as Result_cache_mode, Result_cache_max_size, and so on.

Result_cache_max_size indicates the maximum memory capacity that can be used by the result CACHE feature in the SGA. If this parameter is set to 0, the result cache function is turned off.

The Result_cache_mode parameter sets how Oracle uses result CACHE, with three values: MANUAL, AUTO, FORCE.

When the parameter value is set to Manual, only SQL that is explicitly prompted by hint reads the cached result set. If not prompted, Oracle will not take advantage of the cached results.

For Auto mode, Oracle will use if it finds that the buffered result set already exists. However, if the buffer result set does not exist, Oracle does not automatically buffer. Oracle will cache the result set that is executed only with hint.

For the force parameter, all SQL is cached unless the NO_RESULT_CACHE hint is explicitly used:

Result_cache_max_result sets the percentage of a single SQL statement that occupies the entire result cache buffer, default 5%.

Result_cache_max_size is used to set the overall size of the result CACHE--no more than 75% of the shared_pool_size

In 11.2.0.4, the default is: MANUAL

bys@ bys3>show parameter Result_cache_mode

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Result_cache_mode string MANUAL

Oracle provides a Dbms_result_cache package to manage and maintain the result CACHE.

Oracle has also added several system views on result cache, where users can see a variety of information related to result cache, including: V$result_cache_dependency, V$result_cache_memory, V$result_cache_objects and V$result_cache_statistics and so on.

About result set caching see:

Http://www.itpub.net/thread-846890-1-1.html

Http://database.ctocio.com.cn/tips/365/8273865.shtml