ORACLE性能优化--调整共享池

1、ORACLE性能优化-调整共享池一一.简介简介:数据库调整中最为重要的一部分是重写运行效率差的SQL代码,重写之后的SQL代码在运行效率方面可能会与之前的产生极大的差别！但是当遇到重写之后性能还是未能突破瓶颈或者你是用户(无法访问SQL代码)的时候,可以通过调整ORACLE的共享内存结构SGA(System Global Area),最大限度地提高性能！ SGA中比较重要的组件就是Shared Pool(共享池),它的作用就是高速缓存SQL语句!共享池由一个最近最少使用(LRU,Least Recently Used)算法来管理! 共享池的好处:1.select cust_id,cust_na

2、me from col_cust2.上列语句的算法被转换成ASCII码,然后通过一个散列算法产生一个单独的散列值接着Process会查看该散列值在Shared Pool中是否存在,如果存在,就执行高速缓存中语句3.如果不存在,就必须对该语句进行语法分析,这些分析步骤会产生额外的系统开销,该操作是高代价的!4.查找到匹配的SQL叫做一次高速缓冲区命中(Cache Hit) 5.反之叫做高速缓冲区脱靶(Cache Miss)6.注意是区分大小写的 SELECT CUST_ID,CUST_NAME FROM COL_CUST 二二.共享池有三个组件组成：共享池有三个组件组成：Library Cach

3、e(库高速缓存区)Data Dictionary Cache(数据目录高速缓存区)User Global Area(用户全局区)1.Library Cache(库高速缓存区)是用来缓存SQL语句的场所.可以通过下面这句话对动态视图进行查询,检查Library Cache的内容 select p.username,l.sql_text, lpad( ,4*(LEVEL-2) | operation | | options | | object_name as Execution Plan from ( select .username,p.address,p.hash_value,p.opera

4、tion,p.options,p.object_name,p.id,p.parent_id from v$sql_plan p,v$session s where (p.address=s.sql_address and p.hash_value=s.sql_hash_value) and s.username=citictest ) p,v$sql l where(l.address=p.address and l.hash_value=p.hash_value) start with id=0 connect by prior id = parent_id 2.Data Dictionar

5、y Cache(数据目录高速缓存区):数据目录是用来检查SQL语句所引用的那些表是否已经存在,列名和数据类型是否也正确! Library Cache和Data Dictionary Cache使用互相独立的LRU机制,好处是后续用户发布的语句与先前用户所发布的语句类似但不一致,虽然在Library Cache中无法找到匹配的,但是在数据目录中会存在,也会有性能上的提高。三三.(1)测量测量Library Cache(库高速缓存区库高速缓存区)的性能的性能 select namespace,gethitratio,pinhitratio,reloads,invalidations from v$

6、librarycache where namespace in (SQL_AREA,TABLE/PROCEDURE,BODY,TRIGGER);主要看gethitratio,pinhitratio90%说明调整充分 select sum(reloads)/sum(pins) RELOADRATIO from v$librarycache当85%时说明调整充分四.通过改进Library Cache和Data Dictionary Cache来提高shared pool性能(1).得到当前shared pool的大小 select pool,sum(bytes) SIZE from v$sgast

7、at where pool=shared pool group by pool;(2).得到推荐的shared pool大小 set echo off set feedback off set serveroutput on declare v_total_plsql_mem number := 0; v_total_sql_mem number := 0; v_total_sharable_mem number := 0; begin select sum(sharable_mem) into v_total_plsql_mem from v$db_object_cache; select

8、sum(sharable_mem) into v_total_sql_mem from v$sqlarea where executions 10; v_total_sharable_mem := v_total_plsql_mem + v_total_sql_mem; Dbms_Output.put_line(Estimated required shared pool size is: | to_char(v_total_sharable_mem,fm9,999,999,999,999) | bytes); end; /(3).动态加大shared pool大小alert system s

9、et shared_pool_size = 200M;*大小不能超过SGA_MAX_SIZE的值(4).初始SGA大小的计算(TSGA)SGA总的大小=服务器物理内存*0.55 (1G以上物理内存的话可以相应60%-75%)(TSGAI)每个实例的总SGA大小=TSGA/oracle上实例的个数shared pool的总内存=TSGAI*0.45(5).上述四点是其实完成的是同一个做法，就是使Shared Pool更大(6).可以将PL/SQL程序包装入Shared Pool Reserved Area(共享池保留区)Shared_Pool_Reserved_Size用来设置这一区域的大小，默

10、认是5%，这是不够的select owner,name,sharable_mem from v$db_object_cachewhere type in(PACKAGE,PACKAGE BODY) order by sharable_mem desc;上述这句语句可以查看当前缓存区中的PL/SQL程序包的名称和大小，当发现大小 Shared_Pool_Reserved_Size时说明保留区的大小不够，你需要增加Shared_Pool_Reserved_Size的值(7).把重要的PL/SQL代码保持在内存中你可以把常用的PROCEDURE销定(Pinning)在Shared_Pool_Rese

11、rved_Size中做法如下: a)sys用户登录 b)运行%ORACLE_HOME%/rdbmsadmindbmspool.sql c)SQLexecute DBMS_SHARED_POOL.KEEP(PROCEDURENAME) (销定,必须用sys完成) d)找到销定的对象select owner,name,type from v$db_object_cache where kept=YES; e)销定对单独的SQL语句无法操作,尽可能的把大语句做成PROCEDURE，可以用一下语句寻找出比较大的语句select substr(sql_text,1,45),length(sql_text

12、) STMT_SIZE from v$sqlarea where command_type=47 order by length(sql_text) desc; f)可以编写一个脚本，当实例启动之后运行，把所有需要销定的语句执行一下 h)只有使用UNKEEP或者实例关闭时才会取消销定(8).其他的一些调整Library Cache参数a)open_cursors :默认50b)cursor_space_for_time 默认falsec)session_cached_cursors 默认0(无游标高速缓存)d)cursor_sharing 默认EXACT -2条SQL语句必须完全匹配才能共享shared pool中所缓存的已分析代码. SIMILAR -允许2条仅在字面上不同的SQL语句共