性能调优学习笔记（三）

一、共享池
二、Library cache
三、Cached Execution Plans
四、Global Space Allocation
五、Large Memory Requirements
六、Keeping Large Objects
七、Anonymous PL/SQL Blocks
八、影响Library Cache的其它因素
九、Tuning The Data Dictionary Cache
十、Measuring the Dictionary Cache Statistics
十一、UGA(user global area)
十二、Large Pool[@more@]

这章写得比较乱，做个标记，以后重新整理一下

一、共享池
1、大小
取决于SHARED_POOL_SIZE
2、组成部分
. Library cache：保存SQL和PL/SQL代码
the fully parsed or compiled representations of PL/SQL blocks and SQL statements.
PL/SQL blocks include:Procedures and functions、Packages、Triggers、Anonymous PL/SQL blocks
. 数据字典缓存：最近使用的数据字典的集合，它包括的信息有：数据库文件，表，索引，列，用户，权限和其他数据对象
. 用户全局区(UGA)－共享服务器模式：保存用户会话状态.
If the shared pool is too large performance can decrease because:
There is less memory for other memory structures (thus reducing performance)
Many entries can make finding current contents slower than in a smaller pool.
3、Shared Pool Advisory
SQL> SELECT shared_pool_size_for_estimate AS
2 pool_size, estd_lc_size,
3 estd_lc_time_saved
4 FROM v$shared_pool_advice;
SQL> select *
2 from v$library_cache_memory;

二、Library cache
1、监控
A、查看Unshared SQL:
SQL> SELECT sql_text
FROM v$sqlarea
WHERE executions < 5
ORDER BY UPPER(sql_text);
根据结果看是否相似
B、查看Reparsed Sharable SQL:
SQL> SELECT sql_text, parse_calls, executions
FROM v$sqlarea
ORDER BY parse_calls;
看parse_calls, executions这两个值是否接近
2、相关视图
A、v$librarycache
理想状态：gethitratio > 90%，否则增大共享池
. GETS: Shows the total number of requests for information on the corresponding item
. PINS: Shows the number of executions of SQL statements or procedures
. RELOADS: Shows the number of times, during the execution phase, that the shared
SQL area containing the parsed representation of the statement is aged out of the
library cache to make room for another statement. The Oracle server implicitly
reloads the statement and parses it again.
. INVALIDATIONS: Shows the number of statements that have been made invalid
due to the modification of a dependent object. Invalidations also cause reloads.
B、v$sgastat
displays the sizes of all SGA structures. The contents of the
shared pool are not aged out as long as free memory is available in the shared pool. To
assist you in diagnosing performance issues related to the library cache use the following
dynamic views:
C、v$librarycache:
Statistics on library cache management
D、v$sqlarea:
Full statistics about all shared cursors and the first 1,000 characters of the SQL statement
E、v$sql
Lists statistics on shared SQL area and contains one row for each child of
the original SQL text entered. The v$sql view is a similar view to v$sqlarea,
except that it does not include a GROUP BY clause that can make the v$sqlarea
view expensive to query.
F、v$sqltext: The full SQL text without truncation, in multiple rows
G、v$db_object_cache: Database objects cached, including packages; also objects
such as tables and synonyms, where these are referenced in SQL statements
3、Sharing Cursors
this parameter determines the level at which SQL statements will share cursors:
取值
. Exact: This is the default value. With the CURSOR_SHARING parameter set to
Exact then SQL statements must be identical to share cursors.
. Similar: SQL statements that are similar will share cursors, provided their
respective execution plans are the same. SQL statements will not share a cursor if
the execution plan is not optimal for both statements.
. Force: SQL statements that are similar will share cursors regardless of the impact on the execution plan.
4、Guidelines: Library Cache Reloads
. Reloads should be less than 1% of the pins:
SQL> SELECT SUM(pins) "Executions",
2 SUM(reloads) "Cache Misses",
3 SUM(reloads)/SUM(pins)
4 FROM v$librarycache;
. If the reloads-to-pins ratio is greater than 1%,increase the value of the SHARED_POOL_SIZE parameter.
5、Invalidations
The number of times objects of the namespace were marked invalid, causing reloads:
eg. 查询时表结构更改，索引重建等
6、Sizing the Library Cache
三、Cached Execution Plans
1、
. With this feature, the Oracle server preserves the
actual execution plan of a cached SQL statement in memory.
. When the SQL statement ages out of the library
cache, the corresponding cached execution plan is removed.
. The main benefit of this feature is better diagnosis of query performance.
2、Views to Support Cached Execution Plans
SQL> SELECT operation, object_owner,
2 object_name, cost
3 FROM v$sql_plan
4 ORDER BY hash_value;
说明：
. The v$sql view has a column, plan_hash_value,
which references the hash_value column 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.
四、Global Space Allocation
1、Stored objects such as packages and views:
SQL statements:
SQL> SELECT SUM(sharable_mem)
2 FROM v$db_object_cache;
SQL> SELECT SUM(sharable_mem)
2 FROM v$sqlarea WHERE executions > 5;
2、Computation of Sharable Memory Used
SQL> SELECT SUM(sharable_mem)
2 FROM v$db_object_cache
3 WHERE type = 'PACKAGE' OR type = 'PACKAGE BODY'
4 OR type = 'FUNCTION' OR type = 'PROCEDURE';
SQL> SELECT SUM(sharable_mem)
2 FROM v$sqlarea
3 WHERE executions > 5;
SQL> SELECT SUM(250 * users_opening)
2 FROM v$sqlarea;
SQL> SELECT 250 * value bytes_per_user
2 FROM v$sesstat s, v$statname n
3 WHERE s.statistic# = n.statistic#
4 AND n.name = 'opened cursors current'
5 AND s.sid = 15;
五、Large Memory Requirements
1、v$shared_pool_reserved：
This view helps in tuning the reserved pool within the shared pool.
列说明：
. FREE_SPACE is the total free space in the reserved list
. AVG_FREE_SIZE is the average size of the free memory on the reserved list
. MAX_FREE_SIZE is the size of the largest free piece of memory on the reserved list
. REQUEST_MISSES is the number of times the served list did not have a free piece
of memory to satisfy the request and proceeded to flush objects from the LRU list
. REQUEST_FAILURES is the number of times that no memory was found to satisfy a request
. LAST_FAILURE_SIZE is the size of the last failed request
2、相关数据库参数
The size of the Shared Pool Reserved Area, as well as the minimum size of the objects that
can be allocated from the reserved list, are controlled by the
SHARED_POOL_RESERVED_SIZE initialization parameter, which controls the amount
of SHARED_POOL_SIZE reserved for large allocations. Set the initial value to 10% of
the SHARED_POOL_SIZE. If SHARED_POOL_RESERVED_SIZE is greater that half of
SHARED_POOL_SIZE, then the server signals an error.
3、Tuning the Shared Pool Reserved Space
A、Diagnostics with the v$shared_pool_reserved View
the goal is to have REQUEST_MISSES equal 0, not to have any request failures, or at least to prevent this value from increasing.
B、Diagnostics with ABORTED_REQUEST_THRESHOLD Procedure
dbms_shared_pool.aborted_request_threshold
C、Guidelines When SHARED_POOL_RESERVED_SIZE Is Too Small
相应地调大SHARED_POOL_RESERVED_SIZE和SHARED_POOL_SIZE
D、Guidelines When SHARED_POOL_RESERVED_SIZE Is Too Large
发生情形：
. REQUEST_MISS = 0 or not increasing. The goal is to have this value remain constant using the smallest amount of memory for the shared pool reserved area.
. FREE_MEMORY = > 50% of the SHARED_POOL_RESERVED_SIZE minimum
解决途径：减小SHARED_POOL_RESERVED_SIZE
E、Guidelines When SHARED_POOL_SIZE Is Too Small
The v$shared_pool_reserved fixed table can also indicate when the value for
SHARED_POOL_SIZE is too small. This may be the case if REQUEST_FAILURES > 0
and increasing.
Then, if you have enabled the reserved list, decrease the value for
SHARED_POOL_RESERVED_SIZE. If you have not enabled the reserved list, then you
can increase SHARED_POOL_SIZE.
六、Keeping Large Objects
1、Find those PL/SQL objects that are not kept in the library cache:
SQL> SELECT * FROM v$db_object_cache
2 WHERE sharable_mem > 10000
3 AND (type='PACKAGE' OR type='PACKAGE BODY' OR
4 type='FUNCTION' OR type='PROCEDURE')
5 AND kept='NO';
2、Pin large packages in the library cache:
SQL> EXECUTE dbms_shared_pool.keep(‘package_name’);
Use the supplied dbms_shared_pool package and the keep procedure to keep objects.
To create the package, run the dbmspool.sql script. The prvtpool.plb script is
automatically executed at the end of the dbmspool.sql script. These scripts are not run
by catproc.sql.
Use the unkeep procedure to unpin objects from the shared pool.
七、Anonymous PL/SQL Blocks
Find the anonymous PL/SQL blocks and convert them into small anonymous PL/SQL blocks that call packaged functions:
SQL> SELECT sql_text FROM v$sqlarea
2 WHERE command_type = 47
3 AND length(sql_text) > 500;
SQL> DECLARE /* KEEP_ME */ x number;--注意
2 BEGIN x := 5;
3 END;
SQL> EXECUTE dbms_shared_pool.keep(’address,hash_value’); --注意两值来源
八、影响Library Cache的其它因素
--初始化参数
. OPEN_CURSORS
. CURSOR_SPACE_FOR_TIME
. SESSION_CACHED_CURSORS
. CURSOR_SHARING
九、Tuning The Data Dictionary Cache
Use v$rowcache to obtain information about the data dictionary cache.
1、内容：Definitions of dictionary objects
2、关键列说明：
. Parameter: Gives the name of the data dictionary cache that is being reported
. gets: Shows the total number of requests for information on the corresponding
item. For example, in the row that contains statistics for file descriptions, this column
has the total number of requests for file description data.
. getmisses: Shows the number of data requests resulting in cache misses
3、调优方法
. Keep the percentage of the sum of getmisses to the sum of gets less than 15%:
. If there are too many cache misses, increase the SHARED_POOL_SIZE parameter.
十、Measuring the Dictionary Cache Statistics
. Percent misses should be very low:
– < 2% for most data dictionary objects
– < 15% for the entire data dictionary cache
. Cache Usage is the number of cache entries being used.
. Pct SGA is a percentage of usage to allocated size for that cache.
. Examine the Statspack report for values of PCT SGA that have a high value and a high
miss rate. For example, dc_free_extents shows a PCT SGA value of 57% and a
miss ratio of 26.5%. This could indicate that the shared pool is too small or that there are
too many requests for free extent information.
十一、UGA(user global area)
1、UGA space used by your connection:
SQL> SELECT SUM(value) ||'bytes' "Total session memory"
2 FROM v$mystat, v$statname
3 WHERE name = 'session uga memory'
4 AND v$mystat.statistic# = v$statname.statistic#;
2、UGA space used by all Oracle Shared Server users:
SQL> SELECT SUM(value) ||'bytes' "Total session memory"
2 FROM v$sesstat, v$statname
3 WHERE name = 'session uga memory'
4 AND v$sesstat.statistic# = v$statname.statistic#;
3、Maximum UGA space used by all users:
SQL> SELECT SUM(value) ||'bytes' "Total max memory"
2 FROM v$sesstat, v$statname
3 WHERE name = 'session uga memory max'
4 AND v$sesstat.statistic# = v$statname.statistic#;
十二、Large Pool
. Can be configured as a separate memory area in the SGA, used for memory with:
– I/O server processes: DBWR_IO_SLAVES
– Backup and restore operations
– Session memory for the shared servers
– Parallel query messaging
. Used to avoid performance overhead caused by
shrinking the shared SQL cache
. Sized by the parameter LARGE_POOL_SIZE

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/594892/viewspace-928150/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/594892/viewspace-928150/