性能调优笔记之Library cache

Ｉ.latches解释及调优目标及总体原则

１. 一种锁，一个串型化设备     
　　　　分share pool 和library cache 两种 后面章节，将会详细介绍　
　　　　如何调优library cache？，总体原则
　　　　　使得parse工作尽可能少
　　　　1)确认用户使用共享的sql
        2)分配较大空间，以确保已经存sql不被aged out
        3)避免重新repase编译SQL
        4)碎片化：对一些大空间请求，要保留空间
　　　　　　　　　恒定大对象固化在内存里
　　　　　　　　　消化大的匿名大的pl/sql块
                  配置大对象缓冲池，避免资源损耗 

２.如何解读library cache?
     1)free_memory：
     2)reloads  ：. Reloads should be less than 1% of the pins:
　　SQL> SELECT SUM(pins) "Executions",
　　　SUM(reloads) "Cache Misses",
　　　SUM(reloads)/SUM(pins)
　　　FROM v$librarycache;
. If the reloads-to-pins ratio is greater than 1%,increase the value of the 　SHARED_POOL_SIZE parameter. 

     4)invalidat ：The number of times objects of the namespace were marked invalid, causing reloads:

     3)命中率

ＩＩ.share cursors 处理

(一)　共享share cursors:如果不能共享，cursors，可以通过v$sql_shared_curors
    针对oltp系统，如何优化share cursors：
    1.少使用动态SQL,使用普通SQL;
    2.连接用户，不要改变优先设置，单个SQL优化器应该相同
    3.开人员应注意事项：
      1）对于动态变量要有一套命名规范，包括空格
      2)尽可能使用存储过程，因为:存储过程已经存在了oracle里，加大了用户复用的可能
　　4 使用绑定变量
   
    (二)　使用单用户登陆
　　(三)使用PL/SQL
    (四) 避免业务高锋期使用DDL

 

取值
. 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.

 

ＩＩＩ。相关视图

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

 

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

转载于:http://blog.itpub.net/13824386/viewspace-696023/