(转)Oracle性能优化 之 共享池

转载作品，原始出去如下：

http://blog.chinaunix.net/space.php?uid=26762723&do=blog&id=3247692 

 

 

一、共享池简介：

共享池的位置、各个部分及作用；

二、设定、查看共享池大小：

     在9i中我们用参数Shared_pool_size设置共享池的大小，10g中的设置我们下面再讲。另需注意，无论在9i还是10g中，Shared_pool_size参数的值都不一定代表共享池的真正大小，实际共享池大小会和此参数的值有一些出入。如果要查看共享池的大小，可以使用如下两种方式：

1）、使用Show sga

下面是Show sga的运行结果：

SQL> show sga

Total System Global Area  448790528 bytes

Fixed Size                  1249488 bytes

Variable Size              79695664 bytes

Database Buffers          360710144 bytes

Redo Buffers                7135232 bytes

     此命令显示了SGA各部分的大小。这其中Fixed size是固定区域，这块区域用于存贮一些管理他内存结构的管理性信息。DBA是不需要调节这块内存的。Database buffers是Buffer cache的大小。Redo buffers是重做缓存的大小。Variable Size是可变区域，共享池占了它约百分之八、九十的大小，它其中还包括Java池、大池等SGA除固定区、Buffer cache和重做缓存之外的所有其他池。另外，也有一些管理性信息在此可变区域中。比如我们经常使用的V$系统动态性能视图，就源自此可变区域中的管理性信息（V$的信息来自于X$，而X$中的数据来自于可变区域中的一些结构）。因为共享池占了可变区域的大部分，因此我们一般可以通过它来大概念的了解共享的大小。

     除Show Sga外，我们还可以用V$sgastat视图显示SGA各内存结构的大小，在此视图中，我们可以精确的看到共享池的大小，下面是这个视图的显示信息的样式：

SQL> select * from v$sgastat where rownum<=10;

POOL         NAME                            BYTES

------------ -------------------------- ----------

             fixed_sga                     1249488

             buffer_cache                360710144

             log_buffer                    7135232

shared pool  dpslut_kfdsg                      256

shared pool  hot latch diagnostics              80

shared pool  ENQUEUE STATS                    8360

shared pool  transaction                    264528

shared pool  KCB buffer wait statistic        3352

shared pool  invalid low rba queue             320

shared pool  KQF optimizer stats table        2396

……………………………………………………………………

……………………………………………………………………

     在10G中，这个视图显示600多行，所有POOL列不为空的行，就是可变区域的各个部分。POOL为空的行，从NAME列可以看到，分别是固定区域、Buffer cache和重做缓存。这三行的大小和Show sga中显示的大小是一样的。下面我们按POOL分组，看看可变区域中各内存池的大小：

SQL> select pool,sum(bytes)/1024/1024||' MB' from v$sgastat where pool is not null group by pool;

POOL         SUM(BYTES)/1024/1024||'MB'

------------ -------------------------------------------

java pool    4 MB

shared pool  64.00447845458984375 MB

large pool   4 MB

     可以看到可变区域中，有Java pool和Large pool，大小都是4MB，还有共享池大小为64MB多一点。将这三个池加起来，仍不到Show sga中显示的可变区域大小。那是因为除这三个池外，可变区域还有著如X$结构这样的管理性信息。

     这个视图可以用在9i和10g中，不过在10g中另有一个视图可以详细更准确的共享池大小方面的信息。下面我们来了解一下在10g中，对内存管理做了什么改进。

 

三、10g中设置共享池的大小：

     在10g中，共享池的简单设置更加方便，我们只需要定义SGA_TARGET参数的值，也就是目标SGA大小，Oracle就会根据此目标值自动设置共享池的大小。而且，如果发现共享池内存不够了，如果系统还有空闲的内存，Oracle将可以自动扩展共享池的大小。注意，我们刚才说了，这是共享池的简单设置，这种简单设置其实是根本不用设置。你只要告诉它SGA有多大，它可以自动设置SGA中的所有内存组件。这是10g相比9i的一大新特色，它使DBA的工作更加简单化了。Oracle本身越来越智能，再往下发展下去，很多人担心DBA会不会失业。因为软件越来越智能、越来越简单，总有一天将会淘汰掉DBA。持这种观点的人，大多数是对计算机了解的不够深入，当深入的学习下去之后，就会发现我们现在的人工智能水平是多么的幼稚可笑，这个话题我们就不在这里讨论了。有兴趣的同学可以去看看各种人工智能算法方面的书，如神经网络、遗传算法等等，自然就会明白我们当前的人工智能发展水平。回到我们本身的话题，不可否认，软件是向着智能化、自动化的方面发展，但是智能化程度越高，其本身也越复杂。就像人脑，复杂的多少科学家多少代的努力都无法完全了解其运作原理。这么复杂的系统一旦出现问题，需要程度更高的人，才可解决。比如汽车，由你自己驾驶，汽车出了毛病，很多地方都可以修。而我们的神舟火箭呢，它的运行轨道早就设置好的，它不需要飞行员亲自驾驶，自动化程度比汽车高了一大截。但是，神舟火箭要是出了问题呢，谁能修？我们数据库将来也是这样发展，随着智能化程度的提高，必将淘汰掉大批的初级DBA，但对高级DBA的需求，只会比以前更大。而且高级DBA的薪资水平，也会比以前更高。因为刚才我们已经说了，智能化水平越高，软件本身越复杂。越来越复杂的软件，对DBA的要求也一定会越来越高。就像我们正要讲的共享池，它的简单设置简单到不必再设置。但是，在确定了SGA_TARGET大小后，如果Oracle自动定义的共享池大小不合适怎么办，有很多时候我们不能完全依赖软件的智能，显然Oracle公司也清楚的明白这一点，因此，它还是保留了一些让DBA手动调整共享池等SGA内存组件的权利，10G下的调整比9i理解起来稍微复杂一点，下面我们说一下10g下共享池的调整注意事项。

在10g中还是要用Shared_pool_size参数调整共享池，但它已经不再是共享池大小的决定因素。在10g中它只决定了共享池最小有多大，也就是它是共享大小的下限。如果你将它设置为1G，那么Oracle绝不会分配低于1G内存的共享池。它可能正好按你的要求分配1G，也可能分配多于1G内存的共享池。通常，如果你使用此参数将共享池设置的比以前更大，那么这个设置将会马上生效。而如果你发现共享池有些大了，应该设置的更小些才合适，你用此参数将共享池设置的小了一些，那么这个设置将不会马上生效。为什么呢？因为此参数只是共享池大小的下限。你的共享池本来有1.5G，你将此参数设为了1G，Oracle会认为你只是想让共享池最小不要低于1G，那么当前共享池的大小1.5G满足你的要求，因此，Oracle不会调低共享池大小。什时候它自己也认为1.5G太大了，应该小一些，这时它才会将共享池调小。也就是说在10G中，Oracle加强了自身对共享池的掌控权，只保留给DBA设置下限的权力。

我们可以通过一个视图V$SGA_DYNAMIC_COMPONENTS来了解SGA中各内存组件的大小：

     步骤1：我先手动将shared_pool_size参数值设为50M：

SQL> alter system set shared_pool_size=50m;

系统已更改。

     步骤2：显示V$SGA_DYNAMIC_COMPONENTS视图：

SQL> SELECT COMPONENT,CURRENT_SIZE,MIN_SIZE,USER_SPECIFIED_SIZE from V$SGA_DYNAMIC_COMPONENTS;

COMPONENT                      CURRENT_SIZE   MIN_SIZE USER_SPECIFIED_SIZE

------------------------------ ------------ ---------- -------------------

shared pool                        92274688   92274688            54525952

large pool                          8388608    8388608             8388608

java pool                           4194304    4194304                   0

streams pool                              0          0                   0

DEFAULT buffer cache              247463936  243269632           209715200

KEEP buffer cache                         0          0                   0

RECYCLE buffer cache                      0          0                   0

DEFAULT 2K buffer cache                   0          0                   0

DEFAULT 4K buffer cache             8388608    8388608             8388608

DEFAULT 8K buffer cache                   0          0                   0

DEFAULT 16K buffer cache                  0          0                   0

 

COMPONENT                      CURRENT_SIZE   MIN_SIZE USER_SPECIFIED_SIZE

------------------------------ ------------ ---------- -------------------

DEFAULT 32K buffer cache                  0          0                   0

ASM Buffer Cache                          0          0           209715200

 

已选择13行。

这些列的意义我们简单介绍如下：

COMPONENT：SGA中内存组件的名称

CURRENT_SIZE：当前所占用内存大小

MIN_SIZE：通过分析资料，Oracle认为的该内存组件的最小大小

USER_SPECIFIED_SIZE：用户指定的大小。也就是DBA通过设置Shared_pool_size这些参数设置的大小。

上面是我在将shared_pool_size定为50M后的显示结果，从上面的结果可以看出来，USER_SPECIFIED_SIZE为52MB，因为内存是按块分配的，在10g中是4MB一个块，52M正好是13个内存块，Oracle没办法分配50M，因为50不能被4整除。从MIN_SIZE列可以看出，Oracle认为88M的共享池应该最小的共享池大小，因此CURRENT_SIZE列中记录的当前大小就是88MB。

 

     步骤3：继续实验，如果我调高共享池内存会怎样呢：

SQL> alter system set shared_pool_size=100m;

系统已更改。

将共享池内存设为100MB

 

     步骤4：显示结果

SQL> SELECT COMPONENT,CURRENT_SIZE,MIN_SIZE,USER_SPECIFIED_SIZE from V$SGA_DYNAMIC_COMPONENTS where COMPONENT='shared pool';

COMPONENT                      CURRENT_SIZE   MIN_SIZE USER_SPECIFIED_SIZE

------------------------------ ------------ ---------- -------------------

shared pool                       104857600   92274688           104857600

 

     这一次我只显示共享池，可以看到用户指定大小（USER_SPECIFIED_SIZE）已经是100MB了，当前大小也是100MB。Oracle认为的最小大小MIN_SIZE并没有变，还是88M，这是因为我们并没有进行太多的操作，Oracle认为数据库的负载并不重，最小大小因此没有增加。

     现在的共享池大小是100M，如果你将共享池大设定为88M，当前大小也不会马上变为88MB。因为shared_pool_size只是用来告诉Oracle用户希望共享池的最小大小是多少，但Oracle不会立即减小共享池。Oracle会在它认为合适的时候，减少共享池的大小。

 

四、共享池的调优：

共享池Miss比其他池大，应该调优库缓存