Mas del 4031 y sub-pools

Se va a hacer de rogar el post de CPU Costing de Ricardo, pero es que tengo un pequeño detalle que intenté dejarle a un comentario a Tanel Poder del post que puso hace unos días, pero al final no pude. Así que primero, incentivarlos leer el blog de Tanel Poder que me parece genial, y decirles que allí hay un buen post sobre los errores 4031 y de la shared pool y los sub-pools en:

ORA-04031 errors and monitoring shared pool subpool memory utilization with sgastatx.sql

El caso es que se habla de una sub-pool 0, y que es memoria que no se le da al resto de las sub-pools de la shared pool. Bueno solo añadir que este espacio de memoria que describe Tanel en su blog no existe cuando está desactivado el manejo automático de memoria conocido como ASMM (Automatic Shared Memory Management), que pasa cuando tienes el parámetro SGA_TARGET a 0 ó el parametro STATISTICS_LEVEL en “BASIC”. Realicé una pequeña prueba y este fue el resultado:

SQL> select banner
  2  from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for HPUX: Version 10.2.0.2.0 - Production NLSRTL Version 10.2.0.2.0 - Production

SQL> select ksppstvl
  2  from x$ksppi a,x$ksppcv b
  3  where a.indx=b.indx
  4  and ksppinm ='_kghdsidx_count';

KSPPSTVL
--------------------------------------------------------------------------------
2

SQL> show parameter sga_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 2400M
sga_target                           big integer 0

SQL> SELECT
  2      'shared pool ('||NVL(DECODE(TO_CHAR(ksmdsidx),'0','0 - Unused',ksmdsidx), 'Total')||'):'  subpool
  3    , SUM(ksmsslen) bytes
  4    , ROUND(SUM(ksmsslen)/1048576,2) MB
  5  FROM
  6      x$ksmss
  7  WHERE
  8      ksmsslen > 0
  9  --AND ksmdsidx > 0
 10  GROUP BY ROLLUP
 11     ( ksmdsidx )
 12  ORDER BY
 13      subpool ASC
 14  /

SUBPOOL                                                      BYTES         MB
------------------------------------------------------- ---------- ----------
shared pool (1):                                         687866224        656
shared pool (2):                                         486543704        464
shared pool (Total):                                    1174409928       1120

SQL> show parameter shared_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_shared_pool_reserved_pct            integer     10
shared_pool_reserved_size            big integer 168M
shared_pool_size                     big integer 1120M

SQL> select kghlushrpool, kghlurcr, kghlutrn, kghlufsh, kghluops, 
SQL> kghlunfu, kghlunfs
  2    from x$kghlu
  3   where inst_id = userenv('Instance');

                                              PINS AND   ORA-4031 LAST ERROR
   SUBPOOL   KGHLURCR   KGHLUTRN   KGHLUFSH   RELEASES     ERRORS       SIZE
---------- ---------- ---------- ---------- ---------- ---------- ----------
         1      21358      24815     185512    6312081          0          0
         1       7237      28757     253230    7288093          0          0

SQL> SELECT
  2      subpool
  3    , name
  4    , SUM(bytes)
  5    , ROUND(SUM(bytes)/1048576,2) MB
  6  FROM (
  7      SELECT
  8          'shared pool ('||DECODE(TO_CHAR(ksmdsidx),'0','0 - Unused',ksmdsidx)||'):'      subpool
  9        , ksmssnam      name
 10        , ksmsslen      bytes
 11      FROM
 12          x$ksmss
 13      WHERE
 14          ksmsslen > 0
 15      AND LOWER(ksmssnam) LIKE LOWER('%&1%')
 16  )
 17  GROUP BY
 18      subpool
 19    , name
 20  ORDER BY
 21      subpool    ASC
 22    , SUM(bytes) DESC
 23  /
Enter value for 1: free memo
old  15:     AND LOWER(ksmssnam) LIKE LOWER('%&1%')
new  15:     AND LOWER(ksmssnam) LIKE LOWER('%free memo%')

SUBPOOL                        NAME                       SUM(BYTES)         MB
------------------------------ -------------------------- ---------- ----------
shared pool (1):               free memory                 350156448     333.94
shared pool (2):               free memory                 218046328     207.95

Estas consultas que lancé, son de Tanel Poder sacadas de su blog, quiero hacer esta aclaración. Y las utilizo mostrarles que también hay otro comportamiento, una pequeña variante además del que explica él en su blog. Solo quería aumentar la información que él muestra, con esta pequeña prueba.