shared_pool——解決ORA-4031錯誤

~]$ oerr ora 4031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause:  More shared memory is needed than was allocated in the shared
//          pool.
// *Action: If the shared pool is out of memory, either use the
//          dbms_shared_pool package to pin large packages,
//          reduce your use of shared memory, or increase the amount of
//          available shared memory by increasing the value of the
//          INIT.ORA parameters "shared_pool_reserved_size" and
//          "shared_pool_size".
//          If the large pool is out of memory, increase the INIT.ORA
//          parameter "large_pool_size". 

 

產生ORA-4031的原因：shared  pool不夠用

         可能由於：1、硬解析太多

                             2、大SQL

 

解決方法：

            1、alter system flush shared_pool；     不能從根本上解決

                   說明：執行後會把library cache、row cache所有內容清空，

                               全部塊都回到free中
                               接下來會產生大量的硬解析

             2、使用共享sql

                    a、使用繫結變數

                    b、統一書寫風格

             3、將常見物件到共享池keep到shared pool中

                    select * from v$db_object_cache where sharable_mem >1000
                    and (type='PACKAGE' or type='PACKAGE BODY' or type='FUNCTION'     or type='PROCEDURE')  and kept='NO';

                      --查詢消耗記憶體量大於1000的物件
                     執行dbms_shared_pool.keep('物件名'); 強行快取到sql裡面
                      DBMS_SHARED_POOL
                      @?/rdbms/admin/dbmspool.sql

              4、保留區

                     預設大小是共享池的5%。

                     它的大小可以透過引數SHARED_POOL_RESERVED_SIZE 來調整

                     Shared Pool 中記憶體大於4400 位元組的SQL就會被存放在共享池的保留部分。而這個大小限制是透過隱含參_SHARED_POOL_RESERVED_MIN_ALLOC 來設定的（隱含引數不要去修改它）
                      select REQUEST_MISSES from v$shared_pool_reserved;
                                      --保留區中請求失敗的次數，不為0,就意味著產生4031錯誤
                     增大shared_pool_reserved_size的值

               5、增加shared pool空間
              select COMPONENT,CURRENT_SIZE from V$SGA_DYNAMIC_COMPONENTS; 實際shared pool的大小
               show parameter sga_target
               show parameter sga_max_size                    限制sga_tartget的最大值
                alter system set shared_pool_size=150M scope=both; 若set小於oracle分配的值，會保留oracel欄位分配的值