--=======================================
--The adjustment and optimization of shared pool (Sharedpool tuning)
--=======================================
Shared pool is the most critical memory fragment in the SGA, which consists primarily of the library cache (shared SQL and Pl/sql) and the data dictionary cache. Where the function of the library cache is to save
Put frequently used sql,pl/sql code and execution plan. The data field cache is used to cache the data dictionary. In the capacity of limited memory space, database system according to a certain algorithm to determine what
Releases the Sql,pl/sql Code and data dictionary information from the shared pool. Each part is explained below and the adjustment scheme is given.
The composition of a shared pool
Library Cache--store SQL, pl/sql Code, command block, parse code, execute plan
Data dictionary cache (data dictionary caching)--information that holds data dictionaries
User Global Area (UGA) for Sharedserver sessions-for shared mode, you can move the module to Laregpool for processing. Private mode is not considered.
Second, the Library cache function and composition
The Library Cache consists of the following four components
Shared SQL Areas
Private SQL Areas
Pl/sql Proceduresand Packages
Various controlstructures
Library Cache function
Store SQL commands or Pl/sql blocks for sharing
Using the LRU algorithm (least recently used algorithm)
Used to prevent the same code from being parsed again
ORA-04031 indicates that the shared pool is not sufficient
Three, Data dictionary cache composition and function
Composition
Row Cache
Library Cache
Role
Store definition and permission information for data files, tables, indexes, columns, users, and other data objects in a database
Four, Shared pool size
The Library cache and the data Dictionarycache together form the size of the shared pool, which is determined by the parameter shared_pool_size
View: Show Parametershared_pool_size
Modification: Alter system set shared_pool_size=120m;
Sys@orcl> SELECT * from V$version where rownum < 2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-prod
Sys@orcl> Show Parameter Shared_pool_
NAME TYPE VALUE
----------------------------------------------- ------------------------------
Shared_pool_reserved_size BigInteger 3M
Shared_pool_size BigInteger 0--0, indicating automatic distribution by system
Sys@orcl> Show Parameter Sga_
NAME TYPE VALUE
----------------------------------------------- ------------------------------
Sga_max_size BigInteger 176M
Sga_target BigInteger 176M--Non 0 value, indicating automatic SGA adjustment by System
V. Sga_max_size and Sga_target
Sga_max_size determines the maximum amount of memory allocated for Oracle
Sga_target determines the automatic allocation of memory based on the size of the sga_max_size, Sga_target <= sga_max_size
Sga_target will automatically allocate memory for the following components
Buffer Cache
Shared Pool
Larege Pool
Jave Pool
Streams Pool
When the set Sga_target parameter is a value other than 0, and the five components that are set separately for Sga_target are not 0 values, in which case, the values set by the components must be
The minimum assigned value.
The following SGA components are not managed and affected by sga_target, that is, the need to allocate size separately for the following components
Log buffer (logging buffering)
Other buffer Caches,such as KEEP, recycle, and other blocks sizes (reserved pool, recycle pool, NK pool)
Fixed SGA and Otherinternal allocations
For more information on the automatic management of SGA, please refer to the following: automated management of oracle10g SGA
Vi. Librarypool shared Sql,pl/sql code standards
When a SQL or Pl/sql command is published, Oracle automatically finds that the command exists in the shared pool to determine whether to use hard resolution or soft resolution on the current statement.
The SQL statement is executed as follows:
Syntax of A.sql code (syntax correctness) and semantic check (object's existence and permission)
B. Hash the text of the SQL code to hash the value
C. If the same hash value exists in the shared pool, the command is further judged for soft resolution, or to the E step.
D. For a new command line with the same hash value, the text is compared to the text of the command line that already exists. These comparisons include case, string consistency, spaces, annotations, and so on, and if they are consistent, soften them and go to step f. Otherwise to D step.
E. Hard parsing to generate an execution plan.
F. Execute the SQL code and return the result.
For hard parsing and soft resolution please refer to: Oracle hard resolution and soft resolution
Vii. the competition of the latch in the shared pool
Competition on the latch in a shared pool or the library's cache latch indicates that the following conditions exist
Non-shared SQL requires hard parsing
Reparse shared SQL (due to insufficient librarycache size causes shared SQL to be eliminated by LRU algorithm)
Excessive load causes insufficient librarycache size
Eight, V$librarycache view
SCOTT@ORCL > Desc v$librarycache;
Name Null? Type
----------------------------- ----------------------
NAMESPACE VARCHAR2 (15)-the object type stored in the library cache, with a value of Sqlarea,table/procedure,body,trigger
GETS number-Displays the count of entries in the request library cache (or the number of statement handles)
Gethits number-Shows how many times the requested entry exists in the cache (the number of handles obtained)
Gethitratio number--the ratio of the first two
PINS number--in the execution phase, shows how many entries are executed in the library cache
Pinhits number--at the execution stage, showing how many times the entry has been executed after the library cache
Pinhitratio number--the ratio of the first two
Reloads number-Shows how many times an entry was overloaded in the library cache because it was obsolete or invalid
Invalidations number--it needs to be parsed again because the object has been modified to cause all references to the object's execution plan to be invalid
Dlm_lock_requests number