LoginSignup
3
2

More than 5 years have passed since last update.

@ora_gonsuke777in日本オラクル株式会社

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

Last updated at Posted at 2016-06-22

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

存続期間による分割(サブヒープ、従属ヒープ)数 って何ぞや？てな方は、
共有プールの構造を解説した名著、↓を参照するんとエエやで彡(^)(^)

オラクル・コンサルが語る！共有プール管理の極意
http://www.oracle.com/webfolder/technetwork/jp/ondemand/ddd2013/C-4.pdf

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

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;

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

★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 ★サブヒープ数が一つ

★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 ★４つ

★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 ★サブヒープ数が一つ

★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でもサブヒープ数が２つになったやで彡(ﾟ)(ﾟ)

★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
※2016/6/27追記		 自動SGA 2
12cR1 手動SGA 1
12cR1 自動SGA 2

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

3
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
2