--share Pool adjustment and Optimization __oracle of Oracle Performance tuning

--=======================================

--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