Objectives
1.Determine the size of an object and pin it in the shared pool
2.Tune the shared pool reserved space
3.Describe the user global area(UGA) and session memory considerations
4.Measure the library cache hit ratio
5.List other tuning issues related to the shared pool
6.Measure the dictionary cache hit ratio
7.set the large pool
Shared pool Contents
The Library Cache
1.Used to store SQL statements and PL/SQL blocks that are no be shared by users
2.Managed by a least recently used(LRU) algrithm
3.Used to prevent statements reparsing
4.Reports error ORA-04031 if the shared pool is not of free memory
select * from v$sgastat;
SQL Sharing Criteria
Oracle automatically determines whether a SQL statement or PL/SQL block being issued is identical to another statement currently in the shared pool
1.The text of the statement issued is compared to existing statements in the shared pool
2.The text of the statement is hashed . If there is no matching hash value. then the SQL statement does not currently exist in the shared pool. and a hard parse is performed
3.if there is a matching hash value for an existing SQL statement in the shared pool, then Oracle compares the text of the matched statement to the text of the statement hashed to see if they are identical . The text of the SL statements or PL/SQL blocks must be identical , character for character, including spaces,case, and comments .
Usually , SQL statements that differ only in literals cannot use the same shared SQL area.For example, the following SQL statements do not resolve to the same SQL area:
SELECT COUNT(1) FROM employees WHERE manager_id=121;
SELECT COUNT(1) FROM employees WHERE manager_id=247;
The only exception to this rule is when the parameter CURSOR_SHARING has been set to SIMILAR or FORCE.
Similar statements can share SQL areas when the CURSOR_SHARING parameter is set to SIMILAR or FORCE.The cost and benefits involved in using CURSOR_SARING are explained later in this section.
4.The objects referenced in the issued statement are compared to the referenced objects of all existing satements in the shared pool to ensure that they are identical.
References to scema objects in the SQL statements or PL/SQL blocks must resolve to the same object in the same schema.For example if two users eac issue the following SQL statement:
SELECT * FROM employees
and they each have their own employees table ,then this statements is not considered identical ,because the statement references different tables for each user.
5.Blind variables in the SQL statements must match in name, datatype, and length.
For example , the following statements cannot use the same shared SQ erea, bacause the bind variable names differ:
SELECT * FROM employees where department_id=:department_id;
SELECT * FROM employees where department_id=:dept_id;
Many Oracle products ,such as ORACLE Forms and the precompilers ,convert the SQL before passing statements to the database. Characters are uniformly changed to uppercase, white space is compressed, and bind variables are renamed so that a consistent set of SQL statements is produced.
6.The session's environment must be identical .For example, SQL statements must be optimized using te same optimization goal.
Use Bind Variables
QL> show parameter cursor_sharing;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> select * from v$sysstat where name like '%parse count (hard)%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------------------ ---------- ---------- ----------
331 parse count (hard) 64 1904 143509059
SQL> select * from ta where id=2;
no rows selected
SQL> select * from v$sysstat where name like '%parse count (hard)%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------------------ ---------- ---------- ----------
331 parse count (hard) 64 1905 143509059
SQL> select * from ta where id=3;
no rows selected
SQL> select * from v$sysstat where name like '%parse count (hard)%';
STATISTIC# NAME CLASS VALUE STAT_ID
---------- ------------------------------ ---------- ---------- ----------
331 parse count (hard) 64 1906 143509059
</pre><pre class="sql" name="code"> select namespace, gets, gethits,pins, pinhits,reloads, invalidations from v$librarycache;
NAMESPAC GETS GETHITS PINS PINHITS RELOADS INVALIDATIONS
-------- ---------- ---------- ---------- ---------- ---------- -------------
SQL AREA 6665 3078 68118 63401 1090 14
TABLE/PR 6939 4414 19214 14724 972 0
OCEDURE
BODY 751 700 977 866 57 0
TRIGGER 86 62 323 268 31 0
INDEX 207 37 1295 1009 22 0
CLUSTER 244 235 674 656 9 0
OBJECT 0 0 0 0 0 0
PIPE 0 0 0 0 0 0
JAVA SOU 0 0 0 0 0 0
NAMESPAC GETS GETHITS PINS PINHITS RELOADS INVALIDATIONS
-------- ---------- ---------- ---------- ---------- ---------- -------------
RCE
JAVA RES 0 0 0 0 0 0
OURCE
JAVA DAT 0 0 0 0 0 0
A
11 rows selected.
Diagnostic Tools
SQL>SELECT SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS<5 ORDER BY UPPER(SQL_TEXT);
SELECT SQL_TEXT, PARSE_CALLS,EXECUTIONS FROM v$SQLAREA ORDER BY PARSE_CALLS; --PARSE_CALLS 如果和EXECUTIONS相差不大 ,解析次数多
Using SP Effectively
Shared Cursors
Library Cache Staticstics
SQL> select namespace,gethitratio,pinhitratio,reloads,invalidations from v$librarycache;
NAMESPACE GETHITRATIO PINHITRATIO RELOADS INVALIDATIONS
--------------- ----------- ----------- ---------- -------------
SQL AREA .366623711 .925849382 661 0
TABLE/PROCEDURE .5681594 .655021834 644 0
BODY .961231471 .949468085 21 0
TRIGGER .8 .974110032 4 0
INDEX .25974026 .037974684 19 0
CLUSTER .935483871 .961165049 4 0
OBJECT 1 1 0 0
PIPE 1 1 0 0
JAVA SOURCE 1 1 0 0
JAVA RESOURCE 1 1 0 0
JAVA DATA 1 1 0 0
11 rows selected.
查询正在跑的SQL
SQL> select sql_text,users_executing,executions,loads from v$sqlarea;
Sharing Cursors:
Values for CURSOR_SHARING are: Exact Similar, Force
Library Cache Reloads
pins, reloads 这些都是累计值,不要直接用。隔一段时间执行两次 取增量值
Sizeing Library Cache
1.Define the global space necessary for stored objects;
2.Define the amount of memory used by the usual SQL statements
3.Resesrve space for large memory requirements to avoid misses and fragmentation.
4.Pin frequently used objects
5.Covert large anonymous PL/SQL blocks into small anonymous blocks calling packaged functions
Shared Pool Advisory
SQL> select shared_pool_size_for_estimate as pool_size ,estd_lc_size,estd_lc_time_saved from v$shared_pool_advice;
POOL_SIZE ESTD_LC_SIZE ESTD_LC_TIME_SAVED
---------- ------------ ------------------
64 8 308
72 15 314
80 22 314
88 29 314
96 34 314
104 34 314
112 34 314
120 34 314
128 34 314
136 34 314
144 34 314
Cached Execution Plans
1.The Oracle server preservers the execution plan of a cached SQL statement in memory
2.When the SQL statement ages out of the library cache, the corresponding cached execution plan is removed
3.You can use the cached plans to idagnose query performance.
Views sql_plan
You can use the v$sql_plan dynamic performance view to view the catual execution plan information for cached cursors
select operation , object_owner, object_name, cost FROM v$sql_plan ORDER BY hash_value;
The v$sql view has a column, plan_hash_value which references the hash_value columen of v$sql_plan.
The column information is a hash value built from the corresponding execution plan
The column can be used to compare cursor plans the same way the hash_value column is used to compare cursor SQL texts.
local Space Allocation
SQL> select sum(sharable_mem) from v$db_object_cache;
SUM(SHARABLE_MEM)
-----------------
8483468
SQL> select sum(sharable_mem) from v$sqlarea where executions>5;
SUM(SHARABLE_MEM)
-----------------
2764410
The Reserved Pool
Although Oracle breaks down very large requets for memory into smaller chunks , on some systems there might till be a requirement to find a contiguous chunk (for example , over 5KB) of memory(The default minimum reservded pool allocation is 4,400 bytes)
If there is not enough free space in the shared pool , then Oracle must searc for and free enough memory to satify this request . This operation could conceivably hold the latch resource for detectable periods of time, causing minor disruption to other concurrent attempts at memory alloction.
Hence, Oracle internally reserves a samll memory area in the shared pool that can be used if the shaared pool does not have enough space. This reserved pool makes allocation of arge chunks more efficient
By default, Oracle onfigures a small reserved pool.This memory can be used for operatins such a PL/SQL and trigger compilation or for temporary sapce while laoding Java objects.Afte the memory allocated form reserved pool is freed ,it returns to the reserved pool
You probably will not need to change the default amount of space oracle reserves
However if necessary, the reserved pool size can ben changed by setting the SHARED_POOL_RESERVED_SIZE initialization parameter.This parameter sets aside space in the shared pool for unusually large allocations
For large allocations , Oracle attempts to allocate space in the shared pool in the following order:
1.from the unreserved part of the shared pool.
2.From the reserved pool.If there is not enough space in the unreserved part of the shared pool,then Oracle checks whether the reserved pool has enoug space.
3.From memory.If there is not enough space i the unreserved and reserved parts of the shared pool,then Oracle attempts to free enough memory for the allocation.It thn retries the unreserved and reserved parts of the shared pool.
Large Mem Requirements
SQL> select free_space, requests, request_misses, request_failures from v$shared_pool_reserved;
FREE_SPACE REQUESTS REQUEST_MISSES REQUEST_FAILURES
---------- ---------- -------------- ----------------
3619096 0 0 0
Tuning SP Reserved Space
1.Diagnostic tools for tuning:
a.The v$shared_pool_reserved dictionary view
b.The supplied aborted_request_threshold procedure in the dbms_shared_pool package
2.Guidelines : Set the SHARED_POOL_RESERVED_SIZE parameter
Tuning SP Reserved Space
1.When SHARED_POOL_RESERVED SIZE IS Too Small
The reserved pool is too small when the value for REQUEST_FAILURES is more than zero and increasing.To resolve this, increase the value for the SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE accordingly.The settings you select for these parameters deped on your system's SGA size consraints
Increasing the value of SHARED_POOL_RESERVED_SIZE increases the amount of memory available on the reserved list without having an effect on users who do not allocate memory from the reserved list
2.When SHARED_POOL_SIZE is Too SMALL
The v$SHARED_POOL_RESERVED fixed view can also indeicate when the value for SHARED_POOL)SIZE is too small. This can ben case if REQUEST_FAILURES is greater than zero and increasing
If you have enabled the reserved list, then decrease the value for SHARD_POOL_RESERVED_SIZE, If you have not enabled the reserved list, then you could increase SHARED_POOL_SIZE.
3.When SHARED_POOL_RESERVED_SIZE is Too Large
Too much memory migt have been allocated to the reserved list if :
a.REQUEST_MISSES is zero or not increasing
b.FREE_MEMORY is greater than or equal to 50% of SHARED_POOL_RESERVED_SIZE minimum
if either of these conditions is ture, then decrease the value for SHARED_POOL_RESERVED_SIZE
Keeping Large Objects
ALTER SYSTEM FLUSH SHARED_POOL; //清空SHARED_POOL ,已PIN的不影响
Oter Parameters
1.OPEN_CURSORS
2.CURSOR_SPACE_FOR_TIME
3. SESSION_CACHED_CURSORS
4.CURSOR_SHARING
Tuning the DD Cache
1.Use v$rowcache to obtain information about the data dictionary cache.
a.Content:Definitions of dictionary objects
b.Terminology:
gets:Number of requests on objects
getmisses: Number of requests resulting in cache misses
c.Tuning:Avoid dictionary cache misses
Measuring the Statistics
Large Pool
SQL> select * from v$sgastat where POOL like 'large%';
POOL NAME BYTES
------------ -------------------------- ----------
large pool PX msg pool 1074000
large pool free memory 3120304