This forum posting is another example of oracle version mess-up.
This case is upgrading database from Oracle 10.1 to Oracle 10.2. And the problem is that listener on version 10.1 does not work well with new version of database (oracle 10.2).
Solution is easy: change to oracle 10.2 listener.
http://forums.oracle.com/forums/thread.jspa?threadID=548218&tstart=30
Popularity: 2% [?]
Tags:none
In Oracle,
“The shared pool is subdivided into a number of other structures, among which library cache and the data dictionary cache are main parts of shared pool. . The library cache is a memory area for storing recently executed code, in its parsed form. Parsing is the conversion of code written by programmers into something executable, and it is a slow process that Oracle does on demand. By caching parsed code in the shared pool so that it can be reused without reparsing, performance can be greatly improved. The data dictionary cache stores recently used object definitions: descriptions of tables, indexes, users, and other metadata definitions. Keeping such definitions in memory, rather than having to read them repeatedly from the data dictionary on disk, enhances performance.”
Shared pool is complicated area of SGA and we need to do the followings to optimize that area.
1. calculate used area in shared pool
2. calculate miss ratio in library cache
3. calculate miss ratio in dictionary cache
At first we do calculation on used area in shared pool.
/*******************************************
This script is used to calculate used area of shared pool.
Please be noted that the percentage is not exactly accurate.
Also, plese consider missing ratio of library cache and
data dictionary ratio to adjust the size of shared pool.
*********************************************/
(Script to “Calculate Used Area in Shared Pool”)
Next, do calculation on miss ratio of library cache.
/*******************************************
This script is used to calculate the missing ratio of library cache.
*********************************************/
(Script to “Calculate Missing Ratio of Library Cache”)
At last, do calculation on miss ratio of data dictionary cache.
/********************************8
This script is used to calculate missing ratio of data dictionary cache.
***************************************************/
(Script to “Calculate Missing Ratio of Data Dictionary Cache”)
Conclusion:
To get optimal configuration of shared pool, we’d better to consider the above three measures and get them banlanced.
Note: Comparing to oracle 9i, the significant changes on init parameter with Oracle 10g is the introduction of “SGA_TARGET”. “SGA_TARGET” specifies the total size of all SGA components. If the SGA_TARGET is set to a non-zero value and the STATISTICS_LEVEL initialization parameter is set to TYPICAL or ALL, then the following memory pools are automatically sized:
· Buffer cache (DB_CACHE_SIZE)
· Shared pool (SHARED_POOL_SIZE)
· Large pool (LARGE_POOL_SIZE)
· Java pool (JAVA_POOL_SIZE)
· Streams pool (STREAMS_POOL_SIZE)
If these automatically tuned memory pools are set to non-zero values, then those values are used as minimum levels by Automatic Shared Memory. That means, you as DBA need to set customized minimum values if your application has experienced value about SGA components. Therefore, the above scripts are also helpful for DBAs to set the experienced value about SGA components.
ALSO, see Oracle Metalink Note 1019934.6 (Script: Library Cache Info). This script can be used to get info. about library cache. But, that script only apply till to Oracle 9.2. I made some changes on that script and test it successfully on Oracle 10g.
See Updated Script below:
/***************************************************
This report provides information of the shared pool’s library cache.
It’s originally published as Oracle metalink note: 1019934.6
The original one doesn’t applied to Oracle 10G.
The updated script was tested on Oracle 10g successfully.
Updated by: R.wang
Blog: http://www.oraclepoint.com/oralife
********************************************************/
(Script to “check shared pool’s library cache information”)
In addition,
(script of “Investigate reserved area in shared pool”)
Popularity: 6% [?]
Tags:none
