Oracle Database の 共有プール(Shared Pool) の 存続期間による分割(サブヒープ、従属ヒープ)数 は、11gR2⇔12cR1 及び 手動SGA⇔自動SGA で 異なる。※2016/06/27追記

表題の通り、共有プール(Shared Pool) の 存続期間による
分割(サブヒープ、従属ヒープ)数 は、11gR2⇔12cR1 及び
手動SGA⇔自動SGA で 異なるんやで。彡(ﾟ)(ﾟ)

存続期間による分割(サブヒープ、従属ヒープ)数 って何ぞや？てな方は、
共有プールの構造を解説した名著、↓を参照するんとエエやで彡(^)(^)
> オラクル・コンサルが語る！共有プール管理の極意
> http://www.oracle.com/webfolder/technetwork/jp/ondemand/ddd2013/C-4.pdf

存続期間による分割数を確認するスクリプトは以下の通り。

```sql
SET LINESIZE 100;
SET PAGESIZE 100;
COLUMN SUBPOOL FORMAT 99999;
COLUMN SUBHEAP FORMAT 99999;
SELECT * FROM V$VERSION;
SHOW PARAMETER SGA_TARGET;
SHOW PARAMETER DB_CACHE_SIZE;
SHOW PARAMETER SHARED_POOL_SIZE;
SELECT DISTINCT
       KSMCHIDX   AS SUBPOOL
     , KSMCHDUR-1 AS SUBHEAP
  FROM X$KSMSP
 ORDER BY 1, 2;
```

それぞれの環境での実行結果は以下の通り。

```sql
★11gR2：手動SGA★
SQL> SET LINESIZE 100;
SQL> SET PAGESIZE 100;
SQL> COLUMN SUBPOOL FORMAT 99999;
SQL> COLUMN SUBHEAP FORMAT 99999;
SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> SHOW PARAMETER SGA_TARGET;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 0 ★手動SGA
SQL> SHOW PARAMETER DB_CACHE_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 768M
SQL> SHOW PARAMETER SHARED_POOL_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 768M
SQL> SELECT DISTINCT
  2         KSMCHIDX   AS SUBPOOL
  3       , KSMCHDUR-1 AS SUBHEAP
  4    FROM X$KSMSP
  5   ORDER BY 1, 2;

SUBPOOL SUBHEAP
------- -------
      1       0 ★サブヒープ数が一つ
```

```sql
★11gR2：自動SGA★
SQL> SET LINESIZE 100;
SQL> SET PAGESIZE 100;
SQL> COLUMN SUBPOOL FORMAT 99999;
SQL> COLUMN SUBHEAP FORMAT 99999;
SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> SHOW PARAMETER SGA_TARGET;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 1536M ★自動SGA
SQL> SHOW PARAMETER DB_CACHE_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 112M
SQL> SHOW PARAMETER SHARED_POOL_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 0
SQL> SELECT DISTINCT
  2         KSMCHIDX   AS SUBPOOL
  3       , KSMCHDUR-1 AS SUBHEAP
  4    FROM X$KSMSP
  5   ORDER BY 1, 2;

SUBPOOL SUBHEAP
------- -------
      1       0 ★
      1       1 ★
      1       2 ★サブヒープ数が
      1       3 ★４つ
```

```sql
★12cR1：手動SGA★

SQL> SET LINESIZE 100;
SQL> SET PAGESIZE 100;
SQL> COLUMN SUBPOOL FORMAT 99999;
SQL> COLUMN SUBHEAP FORMAT 99999;
SQL> SELECT * FROM V$VERSION;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                                0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

SQL> SHOW PARAMETER SGA_TARGET;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 0 ★手動SGA
SQL> SHOW PARAMETER DB_CACHE_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 372M
SQL> SHOW PARAMETER SHARED_POOL_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 400M
SQL> SELECT DISTINCT
  2         KSMCHIDX   AS SUBPOOL
  3       , KSMCHDUR-1 AS SUBHEAP
  4    FROM X$KSMSP
  5   ORDER BY 1, 2;

SUBPOOL SUBHEAP
------- -------
      1       0 ★サブヒープ数が一つ
```

```sql
★12cR1：自動SGA★
SQL> SET LINESIZE 100;
SQL> SET PAGESIZE 100;
SQL> COLUMN SUBPOOL FORMAT 99999;
SQL> COLUMN SUBHEAP FORMAT 99999;
SQL> SELECT * FROM V$VERSION;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                                0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

SQL> SHOW PARAMETER SGA_TARGET;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 800M ★自動SGA
SQL> SHOW PARAMETER DB_CACHE_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 0
SQL> SHOW PARAMETER SHARED_POOL_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 0
SQL> SELECT DISTINCT
  2         KSMCHIDX   AS SUBPOOL
  3       , KSMCHDUR-1 AS SUBHEAP
  4    FROM X$KSMSP
  5   ORDER BY 1, 2;

SUBPOOL SUBHEAP
------- -------
      1       0 ★サブヒープ数が
      1       3 ★２つ
```

※2016/06/27追記：ER(改善対応)のPatch 8857940を適用すると、
11gR2の自動SGAでもサブヒープ数が２つになったやで彡(ﾟ)(ﾟ)

```sql
★11gR2：自動SGA + Patch 8857940を適用★
/u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2016, Oracle Corporation.  All rights reserved.
：
Interim patches (1) :

Patch  8857940      : applied on Mon Jun 27 18:26:08 JST 2016 ★Patch 8857940 が適用されている。
：
SQL> SET LINESIZE 100;
SQL> SET PAGESIZE 100;
SQL> COLUMN SUBPOOL FORMAT 99999;
SQL> COLUMN SUBHEAP FORMAT 99999;
SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> SHOW PARAMETER SGA_TARGET;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 1G ★自動SGA
SQL> SHOW PARAMETER DB_CACHE_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 0
SQL> SHOW PARAMETER SHARED_POOL_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 0
SQL> SELECT DISTINCT
  2         KSMCHIDX   AS SUBPOOL
  3       , KSMCHDUR-1 AS SUBHEAP
  4    FROM X$KSMSP
  5   ORDER BY 1, 2;

SUBPOOL SUBHEAP
------- -------
      1       0 ★サブヒープ数が
      1       3 ★２つ
```

まとめると、、、

| バージョン | SGA管理 | 存続期間による分割数 |
|:---|:---|---:|
| 11gR2| 手動SGA | 1 |
| 11gR2| 自動SGA | 4 |
|11gR2 + Patch 8857940<BR>※2016/6/27追記|自動SGA|2|
| 12cR1| 手動SGA | 1 |
| 12cR1| 自動SGA | 2 |

以上、一部の方々に向けたエントリですた。彡(-)(-)