ITPub博客

首页 > Linux操作系统 > Linux操作系统 > performance tunning --shared pool

performance tunning --shared pool

原创 Linux操作系统 作者:oracle_db 时间:2012-05-26 13:57:38 0 删除 编辑
SHARED POOL都有那些东西?
LIBRARY CACHE--发给ORACLE的SQL,PLSQL代码都需要解析编译,对于那些已经编译好的,随时可以执行的代码都放在LIBRARY CACHE中,执行计划也放在这这里边。如果每次执行SQL都需要去解析编译,那么效率非常低,
DATA DICTIONARY CACHE--数据字典缓存区,当实例启动时这里边是空的,当在使用过程中直接或间接的使用了数据字典信息时,逐渐这个区域被填充满
USR GLOBAL AREA--当ORACLE处于共享服务器模式时,这个东西才有用,这个不推荐,及时是共享模式也要指定LARGE_POOL
SHARED POOL大小由谁来定?
参数SHARED_POOL_SIZE,10G以后内存管理都是自动管理,以减少管理员工作。只需要设置一个总的大小就行,至于具体怎么细分由ORACLE自动根据低层算法,完成分配。SGA_TARGET参数指定SGA总大小其它的让ORACLE自己干。如果不想自动管理,就需要手动指定,如果是SHARED POOL大小需要手动指定就需要设置SHARED_POOL_SIZE参数
SQL> show parameter shared_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 0
SQL> 
SQL> alter system set shared_pool_size=100m scope=spfile;

System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             117441216 bytes
Database Buffers          322961408 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> show parameter shared_pool_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 100M
SQL> 
LIBRARY CACHE都是干嘛用的?
1.存储SQL语句,PLSQL代码,这引起语句代码可以被用户共享
2.它的管理算法是LRU,最近最少算法.对于那些不常用或者没用的东西会从这个区域中淘汰掉。
3.它的最大作用就是防止SQL语句的重复解析,主要针对OLTP系统,对于OLAP系统不需要操心SQL语句的重复解析,因为空用户就少,查询时间通常较长。OLTP就相反,并发用户多,操作密集,查询时间相对短很多。
注:如果SHARED POOL不够用,通常会报ORA-04031的错误,这时候加内存,要么减少内存使用

如何查看SHARED POOL大小,它又是如何细分的?
SQL> show parameter shared

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
max_shared_servers                   integer
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 5452595
shared_pool_size                     big integer 100M
shared_server_sessions               integer
shared_servers                       integer     1
SQL> 



SQL> select * from v$sgastat where pool='shared pool';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  obj htab chun                  121924
shared pool  Cleanup state objects             272
shared pool  slave class sga anchor             48
shared pool  resize operation history        28804
shared pool  simulator hash latch             3200
shared pool  time manager context               36
shared pool  file # to first dba, exte        2412
shared pool  KEWS statistic name              1624
shared pool  kgllk hash table                34816
shared pool  kscdnfyinitflags                    4
shared pool  kxfpdp pointers                 14400

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  KFG state obj                    2696
shared pool  kzsrs filename                    532
shared pool  distributed_transactions-        7896
shared pool  KCK type array                    468
shared pool  KGSK scheduler                  38288
shared pool  KTI latches                       288
shared pool  KKJ WRK LAT                       300
shared pool  kfkhsh_kfdsg                     2052
shared pool  event statistics ptr arra         680
shared pool  KGKP randnum                    40000

593 rows selected.

SQL> 
里边竟然有500多条细分出来的记录!

注:LIBRARY CACHE调整的一个重要原则是保证软解析多,硬解析少,尤其是对OLTP系统。ORACLE判断语句是否解析是通过一个HASH值来进行比较,如果有这个值那么解析过的,没有的就会产生硬解析,它的比较很严格区分大小写空格等字符。如:
select * from emp;
select * from Emp;
select *    from emp;
这3条语句不能使用同一个解析过的代码,虽然在我们看来这根本没区别。查看硬解析可以通达V$SYSSTAT查看。
SQL> select * from v$sysstat where name like '%parse%';

STATISTIC# NAME                                CLASS      VALUE    STAT_ID
---------- ------------------------------ ---------- ---------- ----------
       328 parse time cpu                         64        240  206905303
       329 parse time elapsed                     64        236 1431595225
       330 parse count (total)                    64       4033   63887964
       331 parse count (hard)                     64        712  143509059
       332 parse count (failures)                 64          1 1118776443


关于参数cursor_sharing?
它用来指定ORACLE内部是按何种条件来判断用户发出的语句是不是一致的,是严格匹配一字不差符号完全一样视为同一语句,还是类似模糊匹配。
SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     300
session_cached_cursors               integer     20
SQL> alter system set cursor_sharing='similar';

System altered.

SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      similar
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     300
session_cached_cursors               integer     20
SQL> 

如果语句中用的对象发生了变化,比如SCHEMA变了,两用户执行同一语句,但是这两语句是不一样的,不能共享已经解析的代码。

关于绑定变量?
为什么程序开发中经常提到绑定变量,目的减少硬解析。绑定变量就是把具体的值用变量代替。比如:
SELECT * FROM EMP WHERE EMPID=1变成
SELECT * FROM EMP WHERE EMPID=:EMPID这样做会提高共享的成功率。

会话的环境也对会解析有影响,不同的环境看到的结果也不一样

绑定变量是否使用得当对性能有重要影响。能使用绑定变量就使用它。
测试TOM绑定变量

SQL> create table m (x int);

Table created.

SQL> create or replace procedure proc1--这个绑定变量
  2  as
  3  begin
  4    for i in 1..10000
  5    loop
  6      execute immediate 'insert into m values (:x)' using i;
  7    end loop;
  8  end;
  9  /

Procedure created.
SQL> create or replace procedure proc2---这个没有绑定变量
  2  as
  3  begin 
  4    for i in 1..10000
  5    loop 
  6      execute immediate
  7        'insert into m values ('||i||')';
  8    end loop;
  9  end;
 10  /

Procedure created.


执行它们看时间
SQL> set timing on;
SQL> exec proc2;

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.59--不使用绑定变量的执行时间
SQL> select count(*) from m;

  COUNT(*)
----------
     10000

Elapsed: 00:00:00.04
SQL> truncate table m;

Table truncated.

Elapsed: 00:00:01.42
SQL> exec proc1;

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.26---使用绑定变量的执行时间
SQL> 

LATCH是什么?
它是一种轻量级锁是一种串形设备,你写内存时得到它,然后锁住,别人要写等着。主要有两类LIBRARARY CATCH--定位SQL SHARED CATCH--protect memmory allocation in the shared pool 引起LATCH高的原因主要有末使用绑定变量,语句重复解析,LIBRARY CATCH大小不合适。

怎么调节LATCH?
总原则:让解析次数最小化,防止已经解析过的语句重复解析可以通过加在LABRARY CATCH减少重复解析,avoid invalidations that include reparsing不知道啥意思!,避免粹片化【可以把一些东西固定在LIBRARY CATCH中永远不淘汰】
与LIBRARY CATCH相关的视图v$librarycache
SQL> desc v$librarycache
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAMESPACE                                          VARCHAR2(15)
 GETS                                               NUMBER
 GETHITS                                            NUMBER
 GETHITRATIO                                        NUMBER
 PINS                                               NUMBER
 PINHITS                                            NUMBER
 PINHITRATIO                                        NUMBER
 RELOADS                                            NUMBER
 INVALIDATIONS                                      NUMBER
 DLM_LOCK_REQUESTS                                  NUMBER
 DLM_PIN_REQUESTS                                   NUMBER
 DLM_PIN_RELEASES                                   NUMBER
 DLM_INVALIDATION_REQUESTS                          NUMBER
 DLM_INVALIDATIONS                                  NUMBER

SQL> 

SQL> select namespace,gets,gethits,pins,pinhits,reloads from v$librarycache;

NAMESPACE             GETS    GETHITS       PINS    PINHITS    RELOADS
--------------- ---------- ---------- ---------- ---------- ----------
SQL AREA             11820        165      62859      40898        222
TABLE/PROCEDURE       3561       1289      47457      44228        163
BODY                    40         13        163        127          9
TRIGGER                 28         13         32         17          0
INDEX                   77          1         79          3          0
CLUSTER                103         93        286        275          1
OBJECT                   0          0          0          0          0
PIPE                     0          0          0          0          0
JAVA SOURCE              0          0          0          0          0
JAVA RESOURCE            0          0          0          0          0
JAVA DATA                0          0          0          0          0

11 rows selected.

Elapsed: 00:00:00.02
SQL> 
调节LIBRARY CATCH的工具有那些?
v$sgastat---查看SGA的各类状态,里边有一个重要指标FREEMEMORY
SQL> select * from v$sgastat where name like '%free%';

POOL         NAME                                BYTES
------------ ------------------------------ ----------
shared pool  ksuloi: long op free list               8
shared pool  message pool freequeue             698460
shared pool  kghx free lists                     17856
shared pool  free memory                      36420400
shared pool  kglsim free obj list                   24
shared pool  sim kghx free lists                     4
shared pool  kglsim free heap list                  24
large pool   free memory                       3988096
java pool    free memory                       4194304
v$librarycatch--
v$sql
v$sqlarea
v$sqltext
v$db_object_cache--描述LIBRARY CATCH中的数据库对象信息

以及一些初始化参数 :shared_pool_size,open_cursors,session_cached_cursors,cursor_space_for_time,cursor_sharing,shared_pool_reserved_size,

如:select sql_text from v$sqlarea where executions<5 order by upper(sql_text)可以查看LIBRARY CATCH中那些语句执行次数比较少

 select sql_text,parse_calls,executions  from v$sqlarea order by parse_calls
可以查看语句的解析次数

具体的提高LIBRARY CATCH的处理办法通常有那些?
绑定变量,减少动态SQL的使用,最大化使用存储过程,v$sql_shared_cursor对于那些一模一样的SQL可能也不会共用LIBRARY CATCH中已经编译后的代码,通过这个视图,可以把它们找出来。这有可能是绑定变量不匹配,或者会话环境不一致引起,使用同一用户连接上来100个用户都用同一用户!实际环境中好像不太可能。。。,还有就是多使用PLSQL这个时候可以使用多个不同的用户连接,业务高峰时间要避免做改变表DDL的操作。

怎么阅读LIBRARY CACHE信息?
V$LIBRARYCACHE中
指标RELOADS--它要尽量小最好是0,如果这值很高那肯定有问题。
指标INVALIDATIONS,这个值也最好为0,它代表数据库对象无效了
在V$SYSSTAT中的指标FREE MEMORY,
还有LIBRARY CACHE命中率--
查看LIBRARY CACHE命中率
QL> select gethitratio
  2  from v$librarycache
  3  where namespace='SQL AREA';

GETHITRATIO
-----------
 .013833333

Elapsed: 00:00:00.03
SQL> 
SQL> SELECT NAMESPACE,GETHITRATIO,PINHITRATIO,RELOADS,INVALIDATIONS
  2  FROM V$LIBRARYCACHE;

NAMESPACE       GETHITRATIO PINHITRATIO    RELOADS INVALIDATIONS
--------------- ----------- ----------- ---------- -------------
SQL AREA         .024878491  .702822527        412           799
TABLE/PROCEDURE  .763014093  .971904679        448             0
BODY             .436363636  .814049587         14             0
TRIGGER          .464285714      .53125          0             0
INDEX            .216494845   .04040404         19             0
CLUSTER          .908333333  .962264151          1             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.

Elapsed: 00:00:00.03
SQL> 
查看当前用户执行的SQL

select sql_text,users_executing,executions,loads  from v$sqlarea

RELOAD的值应该<1%
SQL> select sum(pins) as executions,
  2  sum(reloads) as cachemisses,
  3  sum(reloads)/sum(pins)
  4  from v$librarycache;

EXECUTIONS CACHEMISSES SUM(RELOADS)/SUM(PINS)
---------- ----------- ----------------------
    214432         894             .004169154

Elapsed: 00:00:00.01
SQL> 
如果>1%有可能就要扩大LIBRARY CACHE。

关于指标INVALIDATION?
如果执行同样的语句间发生了其它DDL操作,那么会发生INVALIDATION

如何决定LIBRARY CACEH大小?

这个和负荷有和SQL类型有关系,不能单独调整,只能调节SHARED POOL来调节,要计算它的大小,要计算它当中的对象的大小,决定通常的SQL所暂用的内存,在SHARED POOL定义保留空间避免粹片化,把一些常用的东西固定在内存中,把一些大的块拆分成小的。
v$shared_pool_advice可以帮助调节,它显示估算出来的解析时间
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
---------- ------------ ------------------
        72           12               1228
        84           23               1228
        96           34               1228
       108           45               1228
       120           56               1228
       132           67               1228
       144           78               1228
       156           89               1228
       168          100               1228
       180          111               1228
       192          122               1228

 POOL_SIZE ESTD_LC_SIZE ESTD_LC_TIME_SAVED
---------- ------------ ------------------
       204          133               1228
       216          144               1228

13 rows selected.
这说明增加SHARED POOL SIZE的大小对性能没什么作用72M,192M效果都一样,除非说最后一列值有变化,则可以参考这个记录改变一个合理的SHARED POOL的大小。

关于缓存执行计划?

ORACLE会把SQL,块代码,编译后的和执行计划放在内存。

估算LIBRARY CACHE大小?
1.计算所有非SQL东西所占用的内存大小
SQL> select sum(sharable_mem)
  2  from v$db_object_cache;

SUM(SHARABLE_MEM)
-----------------
         18529228

SQL> 
2.计算SQL对象所占内存大小
SQL> select sum(sharable_mem)
  2  from v$sqlarea where executions >5;

SUM(SHARABLE_MEM)
-----------------
          3078738

SQL> 

这两部分加起来约等于LIBRARY CACHE大小

保留池是什么?
它主要是用来处理内存粹片化的情况,当SHARED POOL时不够用时,会用保留池,来满足对大对象的分配,提高大对象的分配效率,PLSQL块,JAVA对象都属于大对象。与它相关的参数shared_pool_reserved_size,可以通过它来改变保留池的大小
SQL> show parameter shared

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
hi_shared_memory_address             integer                          0
max_shared_servers                   integer
shared_memory_address                integer                          0
shared_pool_reserved_size            big integer                      5452595
shared_pool_size                     big integer                      100M
shared_server_sessions               integer
shared_servers                       integer                          1
SQL> 


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

请登录后发表评论 登录
全部评论

注册时间:2008-11-13

  • 博文量
    158
  • 访问量
    305910